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 Example

Lookup Views Example

Use a lookup view to test the validity of the foreign key stored in the target or fact table, and to confirm that there are no orphans.
The following table displays sample data from a source table:
ORDER_ID
PRODUCT_NAME
AMOUNT
101
iPod
100
102
Laptop
500
103
iPod
120
The following table displays sample data from a lookup table:
LKP_PRODUCT_ID
LKP_PRODUCT_NAME
21
iPod
22
Laptop
The following table displays sample data from a target table:
TARGET_ID
ORDER_ID
LKP_PRODUCT_ID
AMOUNT
1
101
21
100
2
102
22
500
3
103
21
120
To test the validity of the lookup table foreign key in the target table, perform the following steps:
Create the lookup view.
Create a lookup view with the source and lookup tables. The lookup relationship uses the product name fields in both the source and the lookup tables. The lookup view includes the following fields:
  • S_ORDER_ID
  • S_PRODUCT_NAME
  • S_AMOUNT
  • LKP_PRODUCT_ID
  • LKP_PRODUCT_NAME
The columns that originate in the source have the prefix "S_."
Create the table pair.
Create a table pair using the lookup view and the target table. Create a join relationship between the primary key of the source table and the corresponding foreign key in the target table as follows:
S_ORDER_ID and ORDER_ID
Create an OUTER_VALUE test.
Create an OUTER_VALUE test. Compare LKP_PRODUCT_ID in both the lookup table and the target table as follows:
LKP_PRODUCT_ID and LKP_PRODUCT_ID