Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Use Case: Performing Incremental Validation

Use Case: Performing Incremental Validation

You can use parameters to perform incremental validation. Incremental validation is when you validate a subset of the records that are loaded into a target.
Every day, you run a PowerCenter session that loads approximately 50,000 records into a target. The target currently has 300 million records. You want to create a table pair and tests to validate that all records are loaded into the target every day. However, you do not want to validate the source records against the 300 million target records.
To increase Data Validation Option performance, you perform incremental validation. Each time you run the PowerCenter session, you validate the source records with the latest records that are loaded into the target. You use a $$batchID parameter in the WHERE clause of the table pair to dynamically filter the target records based on the batch ID of the latest load. The PowerCenter session generates a unique batch ID for all records in each load and writes the batch ID of the latest load to the Data Validation Option parameter file.
You perform the following tasks to implement incremental validation:
  1. Create the PowerCenter session that loads the source records into the target and populates the BATCH_ID target column with the batch ID of each record.
  2. Add a post-session command to the PowerCenter session to write the current batch ID to the parameter file.
  3. Create a PowerCenter workflow and add the PowerCenter session to the workflow.
  4. Add a Command task to the PowerCenter workflow to run the table pair tests from the DVOCmd command line after the load completes.
  5. Add a Decision task and additional logic to the PowerCenter workflow to determine what to do based on the results of the table pair tests. If all tests pass, you configure the workflow to run the s_PASS_AND_LOAD session. Otherwise, the workflow runs the s_FAIL_AND_RECOVER task, and then sends an email notification.
  6. Place the parameter file in a location that is accessible by the Data Validation Client that runs the tests.
  7. Specify the location of the parameter file in the table pair definition in the Data Validation Client.
  8. Add the following WHERE clause in the table pair definition in the Data Validation Client:
    BATCH_ID=$$batchID
    .
    BATCH_ID
    is the column in the target table that stores the batch ID of each record.
The following figure shows the PowerCenter workflow:
The diagram shows a PowerCenter workflow and a parameter file. The PowerCenter workflow contains two sessions, a Command task, a Decision task, and an email task.