For every load process, the unique identifier Batch Id is assigned. For example, 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: csv_raw_data_load
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
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 'csv_test.csv'.
SQL query of view:
| Code Block |
|---|
CREATE OR REPLACE VIEW DM2_LOAD_1 AS
SELECT DM2_ID, SOURCE_ROW_NUM,
DM2_GET_PKG.GET_DT(DM2_ID, 502) "REPORT_DATE",
DM2_GET_PKG.GET_CH(DM2_ID, 503) "ENTITY_ID",
DM2_GET_PKG.GET_DT(DM2_ID, 504) "USER_DATE1",
DM2_GET_PKG.GET_CH(DM2_ID, 505) "USER_CHAR1",
DM2_GET_PKG.GET_NM(DM2_ID, 506) "USER_NUM1",
DM2_GET_PKG.GET_DT(DM2_ID, 507) "RUNDATE",
DM2_GET_PKG.GET_CH(DM2_ID, 508) "REPLYFILENAME",
DM2_GET_PKG.GET_CH(DM2_ID, 509) "COLUMNHEADER",
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:
DM2DBO.DM2D_TEST – main table, contains Key Fields only + Last Batch Id + Id of Key Fields.
DM2DBO.DM2D_TEST_TMP – additional DB table, contains Key Fields + Id of Key Fields + Batch Id.
DM2DBO.DM2D_TEST_MAP – 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.
DM2DBO.DM2D_TEST_VIEW – the View is building by Dimension Tables
SQL query of view:
| Code Block |
|---|
CREATE OR REPLACE VIEW DM2D_TEST_VIEW AS
SELECT T1.BATCH_ID, T0.DM2D_TEST_ID,
"REPORT_DATE",
"ENTITY_ID",
DM2_GET_PKG.GET_DT(T1.DM2_ID, 504) "USER_DATE1",
DM2_GET_PKG.GET_CH(T1.DM2_ID, 505) "USER_CHAR1",
DM2_GET_PKG.GET_NM(T1.DM2_ID, 506) "USER_NUM1",
DM2_GET_PKG.GET_DT(T1.DM2_ID, 507) "RUNDATE",
DM2_GET_PKG.GET_CH(T1.DM2_ID, 508) "REPLYFILENAME",
DM2_GET_PKG.GET_CH(T1.DM2_ID, 509) "COLUMNHEADER",
T1.DM2_ID, T1.SOURCE_ROW_NUM
FROM DM2D_TEST T0, DM2D_TEST_MAP T1 WHERE T0.DM2D_TEST_ID = T1.DM2D_TEST_ID; |
DB Table:
DM2DBO.DM2_DIMENSION_TBL – table contains name of Dimension table, Structure Id and list of key fieldsTo view the results of the data load process, see the corresponding page of the EJM Monitoring Tool documentation, Monitor Ingested Data in Raw Service Loads.