Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. New Features and Behavior Changes
  4. Repositories
  5. XML Data Source
  6. Tests for XML Data Sources
  7. Connections
  8. Expressions
  9. Table Pairs
  10. Tests for Table Pairs
  11. Single-Table Constraints
  12. Tests for Single-Table Constraints
  13. Examples of Tests from Spreadsheets
  14. SQL Views
  15. Lookup Views
  16. Join Views
  17. Aggregate Views
  18. Business Intelligence and Reporting Tools Reports
  19. Dashboards
  20. DVOCmd Command Line Program
  21. Troubleshooting
  22. Datatype Reference
  23. Reporting Views
  24. Metadata Import Syntax
  25. Jasper Reports
  26. 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.