How to find the Missing Records for the Target Table during Post Load validation at BusinessObjects Data Services

How to find the Missing Records for the Target Table during Post Load validation at BusinessObjects Data Services

Aim :- To find the missing records after the data load at the Target Table during the Post Load validation Phase at BusinessObjects Data Services.

Steps:-

1) Create a Projects say PRJ_TEST

2) Create a Job, Work Flow and Data Flow for Data Transform and Load file.

3) Now create another Job say JOB_POSTLOAD_EXTRACT.

4) At JOB_POSTLOAD_EXTRACT create workflow and data flow to extract the Target Table from the Target Database where the final data load needs to happen.

5) Create a new Job say JOB_POSTLOAD_VALIDATION (For finding the missing records).

6) At JOB_POSTLOAD_VALIDATION create a workflow and dataflow.

7) At the new created dataflow take 2 source files/Tables
a) The final Load Table
b) The Target Table extracted from step 4.

8) Take a Query Transform and join the above tables on the primary key. Also create the Target Temp table which will have the source as the Query Transform.

9) At the new Temp table take all the fields from the final load table and create an additional output column which will be mapped to the primary key of the Target Table extracted from step 4.

10) After the creation of the Temp table, take this temp table as the source to a new validation transform.

11) Within the Validation Transform take apply the validation on the key field taken from the Target table and do the following
a) Click on the Key field.
b) Check the enable validation check box at the bottom section of the validation Transform properties for the key field.
c) Set the validation condition as Key field is not null.

12) Take 2 output Temp tables say Temp_Pass and Temp_Fail as output of the Validation Transform.

13) Temp_Pass will have all the records which have passed the validation condition at the Validation Transform and Temp_Fail will have all the records which have failed the validation condition set at the validation Transform.

RESULT:-
Temp_Fail will capture all the records which have failed to load at the Target Table in the Target Database (These records will be the Missing records)