Understanding MDW Jobs
This topic provides an overview of MDW fact and dimension load jobs.
MDW Dimension Load Jobs
A MDW dimension job loads data from an OWS source table (or in some cases, an OWE table) to a target MDW dimension table. The basic flow of a MDW dimension job starts with a DRS source stage and includes transformation stages with data validation lookups, when necessary, using the SID.
SID validations work in the same manner as the data validations described in the Understanding Data Validation and Error Handling in the ETL Process topic, except the SID is the unique key identifier used. The job will also contain lookups for attribute values, such as description fields.
The following is an example of a typical MDW dimension load job.
Image: Sample MDW Dimension Load Job (J_DIM_PS_D_RECRTR)
This example illustrates the Sample MDW Dimension Load Job (J_DIM_PS_D_RECRTR).
Next the job performs a lookup on the target dimension table hash file to check if equivalent business keys are already present for each record. If the record is present, the existing SID is used. If the record is not present, a new SID is generated. The job loads valid data into the target DRS stage and updates the hash file used for incremental loading.
MDW Fact Load Jobs
A MDW fact job loads data from an OWS source table (or in some cases, an OWE table) to a target MDW fact table. The basic flow of a MDW fact job starts with a DRS source stage and includes transformation stages to validate values for SID lookup dimension tables.
Image: Sample MDW Fact Load Job (J_Fact_PS_F_CAMPUS_EVENT)
This example illustrates the Sample MDW Fact Load Job (J_Fact_PS_F_CAMPUS_EVENT).
Image: Sample MDW Fact Load Job (J_Fact_PS_F_ADM_FUNNEL)
This example illustrates the Sample MDW Fact Load Job (J_Fact_PS_F_ADM_FUNNEL).
Because transaction tables are based on business unit and some dimension tables are SETID based, sometimes a SETID indirection lookup must be performed against the SETCTRL table to obtain the corresponding SETID for the business unit, and then use the value for the lookup. These lookups provide the values for the SID columns in the fact tables. The MDW fact job performs data validation lookups and diverts records that fail the lookup to an OWS error table (in this case, the PS_ECAMPUS_EVENT error table).
Image: Data Validation and Error Handling in the J_Fact_PS_F_CAMPUS_EVENT Job.
This example illustrates the Data Validation and Error Handling in the J_Fact_PS_F_CAMPUS_EVENT Job.
Next, data transformations are sometimes performed in transformation stages, such as aggregation of values or string manipulation.
The HASH_PS_F_ADM_FUNNEL lookup is the final validation in this job and it is required for incremental loading of the MDW target fact table (PS_F_ADM_FUNNEL). This lookup fetches the CREATED_EW_DTTM value for records in the hashed file and determines whether equivalent business keys are already present. If a matching record exists in the hashed file, the same created date time is extracted from this lookup. The record is then updated in the target fact table. If the record is not present, a new record is inserted in the target fact table.
Image: Update and Insert Data to Target Fact Table in the J_Fact_PS_F_ADM_FUNNEL Job.
This example illustrates the Update and Insert Data to Target Fact Table in the J_Fact_PS_F_ADM_FUNNEL Job..
The job also updates the hash file used for incremental loads. A very small number of MDW fact load jobs use destructive loading, in which case the server job truncates the target table prior to loading data.