Notes on Tables
This section describes some things to note about tables.
Load_ tables contain a straight extract from their source system. Most of the BI extracts come directly from the QAD ERP system’s Progress databases. The only change or transformation that should occur for these columns is to clean up any blank spaces that may have been inadvertently left in the Progress table or to remove the ‘|’ symbol from the data as it disrupts the data load.
Note: There are some exceptions to this rule.
The following is what the transformation looks like:
REPLACE(REPLACE(REPLACE(idh_hist.idh_type,'|',' '),CHR(10),' '),CHR(13),' ')
Use this logic for any source table character (char,varchar) type column that gets added to the load table.
Stage tables are used to do various things that transform the data in some way. The stage tables that come right after load tables are typically used to rename the column names into something more readable as they pass the data up to the perm or dim tables or other staging tables. Any later in the flow, stage tables are doing something with the data to make it more usable.
In many instances, there are perm tables that hold data that is permanently maintained in the source system because it would put a heavy load on the system to be extracted each run. There is a one stage table before the perm where the column names change from the load table; no transformations should occur before a perm table. The data in the perm table should essentially represent a like image of what is contained in its source system. Perm tables are one of the three types of tables with which great care must be taken to not inadvertently drop or delete data.
These tables are a type of stage_ table used to check the perm tables to see if any data has changed as per the date requirements set for that work table. This might mean that the work table is looking for any records that have been updated in the perm tables in the last ten days. For a historic load, all the records are pulled from the perm table. The determination of what date values to use is derived from parameters, such as PO_HISTORY_DATE, which is the day to start looking back from, and PO_PROCESS_DAYS, which is the number of days that should be looked back from PO_HISTORY_DATE.
Custom paths may reference the existing work_ table infrastructure.
Perm_ tables should have at least one and in some cases multiple extract tables. These tables use the records as determined by the work_ tables to extract those records from the correlating perm_ table. Then they prepare them to be picked up by the staging tables and fed on to fact_ tables of that particular module’s Job.
A custom path referencing a perm_ table needs its own extractx_ table, which contains the business key and the required additional columns.
Dim_tables (dimension tables) are where non-calculated potentially redundant information is stored in the data warehouse. Dim_tables are the points of a star schema. It is critical that dim_ tables are not accidentally dropped or that data is deleted from them. Because they hold the keys that the fact uses to link to them, there is no guarantee that a dim_ table creates keys in the same sequence if it repopulates. Dropping and re-creating an existing dimension from a populated warehouse likely forces you to repopulate all the fact tables that reference that dim_ table.
The fact_tables are where the calculated values of the data warehouse are stored. Fact_tables are the center of a star schema. It is important that fact_ table data is not inadvertently deleted or the table re-created, but recovering from accidentally doing so is not as painful as it is for perm_ or dim_ tables.