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

Lookup Views Overview

Lookup Views Overview

Data Validation Option lookup views allow you to test the validity of the lookup logic in your transformation layer.
Lookup views allow you to validate the process of looking up a primary key value in a lookup or reference table based on a text value from a source, and then storing the lookup table primary key in the target fact table. For example, a product name in the source system might be in a dimension that serves as the lookup table. The data transformation process involves looking up the product name and placing the primary key from the lookup table in the target fact table as a foreign key. You must validate the product name in the source table against the foreign key in the target table.
The following table lists the keys used in the example:
Source Table
Lookup Table
Target Table
source_id
product_name
lookup_id
product_name
target_id
source_id
lookup_id
The source table product name field is found in the lookup table. After the product name is found, the primary key from the lookup table is stored in the target table as a foreign key.
To test the validity of the lookup table foreign key in the target table, complete the following tasks:
  1. Create the lookup view. Add the source table and the lookup table to the lookup view. Then create a relationship between the product name in the source and lookup tables.
  2. Create a table pair with the lookup view and the table that is the target of the data transformation process. Join the tables on the source table primary key, which is stored in the target table as a foreign key.
  3. Create an OUTER_VALUE test that compares the primary key of the lookup table to the lookup ID that is stored as a foreign key in the target table.
The OUTER_VALUE test checks the validity of the lookup table primary key stored in the target table against the contents of the source table. The test also finds any orphans, which are records in the target table that do not match any records in the lookup table.