Understanding Data Validation and Error Handling in the ETL Process

Disabling Data Validation

You can disable error validation in OWS jobs by configuring the value of the $ERR_VALIDATE parameter. By default the value is set to Y, which means that records failing validation are moved an error table. If you set the $ERR_VALIDATE value to N, records failing validation will still pass to the target table.

Data Validation - Job Summary Data

The ETL logic for the new data validation feature is contained in a reusable, common component called SEQ_J_Handle_DATA_VALIDATION, which is a sequencer job:

Since the logic is reusable, the sequencer job is incorporated into all the existing sequencer jobs that load the MDW. Also, because the logic is contained within a sequencer job, there is no need to modify existing server jobs to implement the logic.

Here is the SEQ_J_Handle_DATA_VALIDATION sequencer job incorporated into an existing sequencer job for the Campus Solutions Warehouse, SEQ_J_Fact_PS_F_ADM_APPL_STAT:

The SEQ_J_Handle_DATA_VALIDATION sequencer job consists of the following server jobs:

• J_Load_PS_DATA_VALIDATE_ERRTBL

• J_Load_PS_DATA_VALIDATE_NOERRTBL

• J_Load_DATAVALIDATE_NOTARGET

• J_Load_PS_DATA_VALIDATE_ERRTBL_NOTARGET

A closer look at these server jobs shows that they load the Data Validation Summary (PS_DAT_VAL_SMRY_TBL) table:

The Data Validation Summary (PS_DAT_VAL_SMRY_TBL) table consolidates all your job run statistic and error data, and is used as the foundation for the delivered data validation OBIEE reports (which are discussed in the PeopleSoft EPM Fusion Campus Solutions Intelligence for PeopleSoft documentation).

The following graphic and table provide a more detailed look at the columns included in the Data Validation Summary (PS_DAT_VAL_SMRY_TBL) table:

Data Validation - Job Statistic Data

The SEQ_J_Handle_DATA_VALIDATION sequencer job also includes a mechanism to capture job statistic data. Note that within the sequencer job is the routine GetJobCntrlInfo:

The GetJobCntrlInfo routine retrieves job information (such as error table and exception flag) from the Data Validation Control (HASH_PS_DATVAL_CTRL_TBL) hashed file:

The job information in the hashed file is also used by the server jobs that load the Data Validation Summary (PS_DAT_VAL_SMRY_TBL) table.

The Data Validation Control (PS_DATVAL_CTRL_TBL) table stores job statistic data for each OWS to MDW job run, such as source count, target count, error count, and error table list. The table is delivered prepopulated with the necessary data for the ETL jobs that perform data validation.

The following graphic and table provide a more detailed look at the columns included in the Data Validation Control (PS_DATVAL_CTRL_TBL) table:

Another data validation-job statistic table, the Data Validation Job Error (PS_DATVAL_JOB_ERR) table, is populated from the PS_DATVAL_CTRL_TBL by an in-house ETL job not delivered with DataStage metadata.

The Data Validation Job Error table is used in Error Table OBIEE reports and contains specific error table information and corresponding source table information for each job. The Data Validation Job Error table is populated from the PS_DATVAL_CTRL_TBL by an in-house ETL job not delivered with DataStage metadata.

The following graphic and table provide a more detailed look at the columns included in the Data Validation Job Error (PS_DATVAL_JOB_ERR) table:

Enabling or Disabling the Data Completion Validation Feature

The Data Validation and Error Reporting feature is designed as optional, and you can enable it or disable it for each staging job using the delivered parameter files. For example, you can access the HCM_DATA_VALIDATION_SETUP parameter file and change the DATA_VALIDATION value from 'N' (do not perform data validation) to 'Y' (perform data validation) for each job:

PeopleSoft delivers a separate parameter file for each EPM Warehouse and for Global/Common dimension jobs:

• CS_DATA_VALIDATION_SETUP.txt

• FMS_DATA_VALIDATION_SETUP.txt

• HCM_DATA_VALIDATION_SETUP.txt

• GLOBAL_DATA_VALIDATION_SETUP.txt

Implementing the Data Completeness Validation Feature for Customized Jobs

PeopleSoft provides the DDL Scripts and related data files for the Data Completeness Validation setup tables, but if you have created new jobs or customized delivered jobs, you must perform the following steps to implement the Data Completion Validation feature:

1. Populate the PS_DATVAL_CTRL_TBL.

   For each customized job, you must populate this table using an SQL INSERT statement. Use the following table as a guide to populating the PS_DATVAL_CTRL_TBL fields:

   Column	Description
   JOB_NAME	Populate this field with the server job that loads the OWS and MDW table.
   SEQ_JOB_NAME	Populate this field with the Sequencer job that triggers the customized job.
   SOURCE_TBL_LIST	Populate this field with source tables used in the customized job. Use commas to separate multiple table names.
   TARGET_TBL_LIST	Populate this field with target tables used in the customized job. If target table name is a job parameter, use this sample format: #TargetTable#(PS_FF_STDNT_ENRL/PS_TF_STDNT_ENRL), where TargetTable is the Job Parameter
   ERROR_TBL_LIST	Populate this field with error tables used in the customized job.
   TGT_DEL_STG_LNK	Populate this field with the active stage name that precedes the DRS stage that performs a delete on the target table. If the active stage contains more than one output link, the link name should also be specified using this format: <active_stage_name>@<link_name> where link_name is the link connecting to the Delete DRS stage and active_stage_name is the transformer name. For example: "PS_R_STDNT_ATTR_split@IPC_Del_Out*-" is value for this field in J_Dim_PS_R_STDNT_ATTR job
   SRC_ACT_STG_LNK	For CRC staging jobs, populate this field with the active stage name and link name that writes to the Delta hash file. Use the following format: <active_stage_name>@<link_name> where link_name is the link that updates the delta hash file in the CRC job. For non CRC staging jobs, populate this field with the active stage name that follows the source DRS stage in the customized job.
   ERR_TBL_QRY	Populate this field with the error table query that is used to fetch the count of unique errors rows that failed to load the target. For customized jobs having one error table, use the following query syntax: SELECT DISTINCT <BUSINESS KEYS of source table> FROM #$ERROR_TBL_SCHEMA#<ERROR TABLE NAME>Sample: SELECT DISTINCT SRC_SYS_ID,EFFSEQ,EFFDT,EMPL_RCD,EMPLID FROM #$ERROR_TBL_SCHEMA#PS_E_JOB This field is defaulted to "-" for customized jobs with more than one error table.
   EXCEPTION_FLAG	Set this flag to "Y" for customized jobs with more than one error table. Set this flag to "Y" for customized jobs that include multiple source tables with parent/chid relationships and the error data is captured only at parent table level.
   GEN_SQL_FLAG	Set this flag to “Y” for customized jobs that use generated SQL in the Source DRS stage. Set this flag to “N” when the source DRS stage includes user-defined SQL.
   JOB_CATEGORY	Populate this field with the category (folder) name where the job is placed within the DataStage Project Repository.
   WH_NAME	Populate this field with the EPM Warehouse name related to the customized job.
   MART_NAME	Populate this field with the data mart name that corresponds to the customized job.
   JOB_TYPE	Populate this field with the job type information for the customized job. Job type values include: STAGING DIMENSION FACT
   SUBJECT_NAME	Populate this field with the functional area that corresponds to the customized job.
   LASTUPDOPRID	Populate this field with the user ID associated with the insert or update actions for the customized job.
   LASTUPDDTTM	Populate this field with the current timestamp representing when data was last modified for the customized job.

   Here is a sample of SQL INSERT syntax:

     INSERT INTO "PS_DATVAL_CTRL_TBL"
      ( 
       JOB_NAME, SOURCE_TBL_LIST, TARGET_TBL_LIST, 
       ERROR_TBL_LIST, TGT_DEL_STG_LNK, SRC_ACT_STG_LNK,
       ERR_TBL_QRY,
       EXCEPTION_FLAG, GEN_SQL_FLAG, WH_NAME, MART_NAME,
       SUBJECT_NAME, JOB_TYPE, JOB_CATEGORY,
       LASTUPDOPRID, LASTUPDDTTM, SEQ_JOB_NAME
      )
      VALUES
      (
       'J_Fact_PS_F_KK_EXCEPTION','PS_KK_EXCPTN_TBL','PS_F_KK_EXCEPTION',
       'PS_E_KK_EXCEPTION','Trans_Assign_Values8@Trans_Assign_Values8_out_DEL*-',
       'IPC_SRC.IDENT1',
       'SELECT DISTINCT
       SRC_SYS_ID,EXCPTN_TYPE,LEDGER_GROUP,KK_TRAN_LN,KK_TRAN_DT,KK_TRAN_ID
       FROM #$ERROR_TBL_SCHEMA#PS_E_KK_EXCEPTION',
       'N','Y','FMS','GL_And_Profitability_Mart',
       'Commitment_Control','FACT',
       'Jobs\FMS_E\GL_And_Profitability_Mart\Commitment_Control\OWS_To_MDW\Facts\Base\Lo    ad_Tables\Server',
       'EPM',to_timestamp('01-JAN-53 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),    'SEQ_J_Fact_PS_F_KK_EXCEPTION'
      );

2. Add an entry in Data Validation Parameter File.

   For warehouse specific Jobs, add an entry in the corresponding parameter file. For example, customized jobs specific to Campus Solutions require an entry in the CS_DATA_VALIDATION_SETUP.txt and you could use the following format:

   [J_Dim_PS_D_ACAD_CAR]
   DATA_VALIDATION = N

   For Common Dimensions and Global Dimensions, add an entry in GLOBAL_DATA_VALIDATION_SETUP.txt.

   To enable data load validation (capturing source, target and error row counts for each run), the DATA_VALIDATION flag should be set to ‘Y’.

3. Run J_Hash_PS_DATVAL_CTRL_TBL job.

   Note: It is important that the information for a customized server job is accurately reflected in the PS_DATVAL_CTRL_TBL before running this job.

4. Attach the common component, SEQ_J_Handle_DATA_VALIDATION, sequencer job to the existing sequencer job that corresponds to the customized server job.

   Copy the SEQ_J_Handle_DATA_VALIDATION component and paste it into the existing sequencer job that corresponds to the customized server job. Reestablish the links in the new job sequence, map the Job Name and BatchSID parameter in the Job Activity, and verify that hash file directory path, parameter file directory path, and the database related parameters are mapped correctly. Then compile the job.

   When you complete these steps, the PS_DATVAL_SMRY_TBL captures job run statistics for each job run by the data validation component.

5. Populate the PS_DATVAL_JOB_ERR.

   For each customized job, you must populate this table using an SQL INSERT statement. Use the following table as a guide to populating the PS_DATVAL_JOB_ERR fields:

   Column	Description
   SEQ_NBR	Populate this field with a unique sequence number for each error table. To avoid overlapping SEQ_NBR, use higher SEQ_NBR (values greater than 999999).
   JOB_NAME	Populate this field with the customized server job name.
   ERROR_TABLE	Populate this field with the error table name used in the customized job.
   SOURCE_TABLE	Populate this field with the name of the source table used with the corresponding error table.
   LASTUPDOPRID	Populate this field with the user ID associated with the insert or update actions for the customized job.

   Here is a sample of SQL INSERT syntax:

   INSERT INTO "PS_DATVAL_JOB_ERR" 
   (SEQ_NBR,JOB_NAME,ERROR_TABLE,SOURCE_TABLE,LASTUPDOPRID)
   VALUES
   (236,'J_Dim_PS_D_GM_SPONSOR','PS_E_GM_CUSTOMER','PS_GM_CUSTOMER','dsuser');

6. Populate PS_DATVAL_SRC_TBL.

   For each customized job, you must populate this table using an SQL INSERT statement. Use the following table as a guide to populating the PS_DATVAL_SRC_TBL:

   Column	Description
   SEQ_NBR	Populate this field with a unique sequence number for each error table. To avoid overlapping SEQ_NBR, use higher SEQ_NBR (values greater than 999999).
   ERROR_TABLE	Populate this field with the error table name used in the customized job.
   SOURCE_TABLE	Populate this field with the name of the source table used with the corresponding error table.

   Here is a sample of SQL INSERT syntax:

   INSERT INTO "PS_DATVAL_SRC_TBL" 
   (SEQ_NBR,ERROR_TABLE,SOURCE_TABLE)
   VALUES
   (351,'PS_E_WA_COMP_PIN','PS_WA_COMP_PIN_MAP');