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

Examples of Tests from Spreadsheets Overview

Examples of Tests from Spreadsheets Overview

You can generate tests for table pairs and single tables based on test definitions in a spreadsheet. Define the test properties as columns in the spreadsheet and then import the test definition into Data Validation Option.
You might use spreadsheets to define test for the following reasons:
  • Data Validation Option users can work offline when creating tests.
  • Analysts who are familiar with the data and testing requirements but are not familiar with Data Validation Option can define the tests. A Data Validation Option user can then import the test definitions and create the tests in Data Validation Option.
  • Data extraction, transformation, and loading (ETL) processes and testing specification documents are often defined as a source-to-target spreadsheet. Some of the definitions in those spreadsheets can often be repurposed for test creation.
  • Large numbers of tests, particularly those that follow certain patterns, can be defined quickly in a spreadsheet with copy and paste and other basic spreadsheet features. Importing multiple tests into Data Validation Option can save a significant amount of time and effort over creating multiple tests in the Data Validation Client.
For example, a flat file contains 200 columns. Each column is defined as a string in the source definition. During the ETL, about 90 of the fields are converted to decimal and written to the final target table.
Testing this scenario would require creating 200 value tests, one for each column that is read and written to the target table. The value tests for the columns that are converted to decimal must contain the expression TO_DECIMAL (<fieldname>).
On the Data Validation Client, it would be tedious to manually create the 90 tests that include an expression. But by using basic cut and paste or search and replace capabilities within a spreadsheet, you can define these 90 tests in about one minute or less. And if, instead of 90 tests, there were 200 or even 900 similar tests, the time to create them would be minutes at most. This is a big productivity increase over using the Data Validation Client.
Importing from a spreadsheet provides an alternative to the Data Validation Client that is very effective when a large number of similar tests must be generated.
The following examples show the how you can import tests from spreadsheets in different scenarios:
  • Import and export of table pair tests
  • Import of table pair tests for compare tables
Each of the scenarios uses a table pair as an example, but analogous functionality and processes can be used for single tables as well.