CSV Data in a Database

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:

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:

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 fields.