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.