XML Data in a Database

For every load process, the unique identifier Batch Id is assigned. For example, let's say Batch Id will be = 1.

The data is loaded to the following DB tables described below. Also the View created by workflow is described.

Step 1 – defines types and load header, required step

Task of workflow: raw_data_header_load

DB tables:
DM2DBO.DM2_LOAD_MASTER_TBL – table with list of Batch Ids
DM2DBO.DM2_LOAD_STRUCTURE_TBL – table with Structure (Model) Name and Id
DM2DBO.DM2_MASTER_COL_TBL – table with list of Column's Ids and Model Id
DM2DBO.DM2_LOAD_COL_TBL – table with list of column's Ids, column's number in the header, Batch Id and data format
DM2DBO.DM2_MASTER_COL_VAL_TBL – table with Ids, names, types and formats of columns

Step 2 – load records, of data files, required step

Task of workflow for CSV files load: eagle_ml20_default_in_xml_warehouse

DB Tables:
DM2DBO.DM2_LOAD_CH_TBL – table for data of CHAR columns
DM2DBO.DM2_LOAD_NM_TBL – table for data of NUMBER columns
DM2DBO.DM2_LOAD_DT_TBL – table for data of DATE columns
DM2DBO.DM2_LOAD_VALUE_ERRORS_TBL – the table for errors if the process has been completed unsuccessfully.

Step 3 – load global columns, optional step (WAS MOVED TO STEP 1)

This step will be started only if the global values exist in a data file.

Task of workflow: raw_common_values_load

DB tables:
All DB tables described in the workflow steps 1 and 2. The columns and data are just added to every record.

Step 4 – created View in a database, optional step

This step is run if the attribute VIEWIDX exists for one or more columns or global columns in a profile.

DB Views:
DM2DBO.DM2_LOAD_<batchId> – view displayed all loaded data with columns. All DB headers will be the header of a data file'.

Example of view: DM2DBO.DM2_LOAD_1. This view is for data file 'XML_test.xml'.

SQL query of View (example for model TopLevelVolatilityRows):

CREATE OR REPLACE VIEW DM2_LOAD_1 AS

SELECT DM2_ID, SOURCE_ROW_NUM,

DM2_GET_PKG.GET_NM(DM2_ID, 1) "TotalVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 2) "SystematicVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 3) "SpecificVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 4) "TaR",

DM2_GET_PKG.GET_NM(DM2_ID, 5) "SystematicPercentageVol",

DM2_GET_PKG.GET_NM(DM2_ID, 6) "SpecificPercentageVol",

DM2_GET_PKG.GET_CH(DM2_ID, 7) "CompositionType",

DM2_GET_PKG.GET_NM(DM2_ID, 8) "Id"

FROM DM2_LOAD_TBL WHERE BATCH_ID = 1;

DM2DBO.DM2_LOAD_<batchId>_ERRORS – view displayed errors:

CREATE OR REPLACE VIEW DM2_LOAD_1_ERRORS AS

SELECT SOURCE_ROW_NUM FROM (SELECT DISTINCT SOURCE_ROW_NUM FROM DM2_LOAD_ERROR_VIEW WHERE BATCH_ID = 1);

Step 5 – create Dimension Table in a database, optional step

This step is run if parameters Table Name and Key Fields are not null in a profile.

DB tables:
3 tables are created: table with specified name, table with specified name + '_TMP', table with specified name + '_MAP'.

Example: specified the name of Dimension table 'DM2D_VOLTST' in the tab in Raw Service:
DM2DBO.DM2D_VOLTST – will be the main table, contains Key Fields only + Last Batch Id + Id of Key Fields.
DM2DBO.DM2D_VOLTST_TMP – will be the additional DB table, contains Key Fields + Id of Key Fields + Batch Id.
DM2DBO.DM2D_VOLTST_MAP – will be the map DB table, contains Id of Key Fields + Batch Id + Id of Dimension Tables.

DB view:
The views are created after the creating dimension tables. These views are also contains all headers + all data but the key fields are selected from the dimension table.

Example:
DM2DBO.DM2D_VOLTST_VIEW – the View is building by Dimension Tables

SQL query of view (example for model TopLevelVolatilityRows):

CREATE OR REPLACE VIEW DM2D_VOLTST_VIEW AS

SELECT T1.BATCH_ID, T0.DM2D_VOLTST_ID,

"Id",

"CompositionType",

DM2_GET_PKG.GET_NM(DM2_ID, 1) "TotalVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 2) "SystematicVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 3) "SpecificVolatility",

DM2_GET_PKG.GET_NM(DM2_ID, 4) "TaR",

DM2_GET_PKG.GET_NM(DM2_ID, 5) "SystematicPercentageVol",

DM2_GET_PKG.GET_NM(DM2_ID, 6) "SpecificPercentageVol",

T1.DM2_ID, T1.SOURCE_ROW_NUM

FROM DM2D_VOLTST T0, DM2D_VOLTST_MAP T1 WHERE T0.DM2D_VOLTST_ID = T1.DM2D_VOLTST_ID;

DB Table:
DM2DBO.DM2_DIMENSION_TBL – table contains name of Dimension table, Structure Id and list of key fields.