For every load process, the unique identifier Batch Id is assigned. For For example, let's say Batch Id will be = 1.
...
Step 1 – defines types and load header, required step
Task of workflow: raw_data_header_load
DB 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
...
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.
...
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'.
...
SQL query of View (example for model TopLevelVolatilityRows):
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
DM2DBO.DM2_LOAD_<batchId>_ERRORS – view displayed errors:
...
|
...
|
...
|
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.
...
SQL query of view (example for model TopLevelVolatilityRows):
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
DB Table:
DM2DBO.DM2_DIMENSION_TBL – table contains name of Dimension table, Structure Id and list of key fields.