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

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

0 COMMENTS

We’d like to hear from you!