Preface
Introduction to Data Validation Option
- Data Validation Option Overview
  - Data Validation Guidelines
- Data Validation Option Architecture
- Data Validation Test Components
- Data Validation Process
- Data Validation Views
- Data Validation Table Objects
- Data Validation Tests
- Data Validation Reports
- Data Validation Example
- Data Validation Client
Repositories
- Repositories Overview
- Adding a Repository
- Editing Repositories
- Deleting Repositories
- Refreshing Repositories
  - Troubleshooting a Repository Refresh
- Folders
- Exporting Repository Metadata
- Metadata Export and Import
  - Exporting Metadata
  - Importing Metadata
- Metadata Manager Integration
XML Data Source
- XML Data Source Overview
- XML Groups
- XML Data Conversion to a Relational Structure
  - Join View Properties for an XML File
  - Automatically Generating a Join View for an XML File
- Rules and Guidelines for XML Files
Tests for XML Data Sources
- Test for XML Data Sources Overview
- Importing XML Definitions and Viewing them in Data Validation Option
- Using XML Sources Directly in Table Pair or Single Table Tests
- Flattening XML using a Join View
- Editing Table and Column Names in the Join View
- Overriding XML Source File Information
- Troubleshooting
  - Maximum Row Size Limit
Connections
- Connections Overview
- File Connections
- Relational Connection Properties
  - Owner Name and Table Name Override
- SAP Connection Properties
Expressions
- Expressions Overview
- Expression Types
- Expression Editor
  - Data Type, Precision, and Scale of Field Expressions
  - Creating an Expression
- Expression Syntax
- Functions
- Operators
- Parameters
- Test Logic Processing in the Data Source
- Expression Examples
- Reusable Expressions
  - Rules and Guidelines for User-Defined Functions
  - Using PowerCenter User-Defined Functions in Data Validation Option
Table Pairs
- Table Pairs Overview
- Basic Properties for a Table Pair
- Advanced Properties for a Table Pair
- Rules and Guidelines for WHERE Clauses in a Table Pair
- Table Joins
  - Rules and Guidelines for Table Joins
  - Table Join Optimization
- Bad Records Configuration for a Table Pair
  - Bad Records in Flat File
  - Bad Records in Database Schema Mode
- Data Sampling for a Table Pair
  - Rules and Guidelines for Data Sampling
- PowerCenter Cache for a Table Pair
- Adding a Table Pair
- Table Pair Generation from Table Comparisons
- Editing Table Pairs
- Deleting Table Pairs
- Viewing Overall Test Results for a Table Pair
Tests for Table Pairs
- Tests for Table Pairs Overview
- Table-Pair Tests
- Test Properties for a Table Pair
- Adding a Table-Pair Test
- Automatic Test Generation for Table Pairs
  - Column Comparison by Position
- Generating Tests for a Table Pair
- Test Generation for a Table Pair from a Spreadsheet
  - Spreadsheet Requirements for Table-Pair Tests
  - Generating Tests for a Table Pair from a Spreadsheet
- Editing a Table-Pair Test
- Deleting a Table-Pair Test
- Running Table-Pair Tests
- Bad Records for a Table-Pair Test
- Viewing the Details of a Bad Record
- Table Pair Test Example
- Troubleshooting Table-Pair Tests
Single-Table Constraints
- Single-Table Constraints Overview
- Basic Properties for a Single-Table Constraint
- Advanced Properties for a Single-Table Constraint
- Bad Records Configuration for a Single-Table Constraint
  - Bad Records in Database Schema Mode
  - Bad Records in Flat File
- Data Sampling for a Single-Table Constraint
  - Rules and Guidelines for Data Sampling
- PowerCenter Cache for a Single-Table Constraint
- Adding a Single Table
- Editing Single Tables
- Deleting Single Tables
- Viewing Overall Test Results for a Single-Table Constraint
Tests for Single-Table Constraints
- Tests for Single-Table Constraints Overview
- Single-Table Constraint Tests
- Test Properties for a Single-Table Constraint
- Adding a Single-Table Constraint Test
- Test Generation for a Single-Table Constraint from a Spreadsheet
  - Spreadsheet Requirements for Single-Table Tests
  - Generating Tests for a Single-Table Constraint from a Spreadsheet
- Editing a Single-Table Constraint Test
- Deleting a Single-Table Constraint Test
- Running Single-Table Constraint Tests
- Bad Records for Single-Table Constraint Tests
- Viewing the Details of a Bad Record
Examples of Tests from Spreadsheets
- Examples of Tests from Spreadsheets Overview
- Spreadsheet Import and Export of Table Pair Tests
- Spreadsheet Import for Compare Tables
  - Scenario 1 - Add tables to the Compare Tables Import Spreadsheet
    - Adding Tables to the Compare Tables Import Spreadsheet
  - Scenario 2 - Importing the Compare Tables Spreadsheet into DVO
SQL Views
- SQL Views Overview
- SQL View Properties
- Rules and Guidelines for SQL Views
- Adding an SQL View
- Editing SQL Views
- Deleting SQL Views
- SQL View Example
Lookup Views
- Lookup Views Overview
- Lookup View Properties
- Adding Lookup Views
- Editing Lookup Views
- Deleting Lookup Views
- Lookup Views Example
Join Views
- Join Views Overview
- Join View Data Sources
- Join View Properties
- Output Field Properties
  - Column Aliases
- Adding a Join View
- Join View Example
Aggregate Views
- Aggregate Views Overview
- Aggregate View Editor
- Aggregate View Properties
  - Group By Columns
  - Using Sorted Input
- Rules and Guidelines for Aggregate Views
- Adding an Aggregate View
- Editing Aggregate Views
- Deleting Aggregate Views
- Aggregate View Example
Business Intelligence and Reporting Tools Reports
- Business Intelligence and Reporting Tools Reports Overview
- BIRT Report Examples
Dashboards
- Dashboards Overview
- Dashboard Types
DVOCmd Command Line Program
- DVOCmd Command Line Program Overview
- Rules and Guidelines for Running DVOCmd Commands
- CopyFolder
- CreateUserConfig
- DisableInformaticaAuthentication
- EncryptPassword
- ExportMetadata
- ImportMetadata
- InstallTests
  - Cache Settings
- LinkDVOUsersToInformatica
- PurgeRuns
- RefreshRepository
- RunTests
  - Running Multiple Table Pair Objects in Parallel
  - Cache Settings
- UpdateInformaticaAuthenticationConfiguration
- UpgradeRepository
- Rules and Guidelines for Special Characters
Troubleshooting
- Troubleshooting Overview
- Troubleshooting Initial Errors
- Troubleshooting Ongoing Errors
- Troubleshooting Command Line Errors
Appendix A: Datatype Reference
- Test, Operator, and Datatypes Matrix for Table Pair Tests
- Test, Operator, and Datatypes Matrix for Single-Table Constraints
- Selecting a Valid PowerCenter Transformation Data Type for an Expression
- Properties of PowerCenter Transformation Data Types
Appendix B: Reporting Views
- Reporting Views Overview
- Using the Reporting Views
  - Sample Queries for Custom Reports
- results_summary_view
- rs_bad_records_view
- results_id_view
- meta_sv_view
- meta_lv_view
- meta_jv_view
- meta_ds_view
- meta_tp_view
- meta_av_view
- rs_jv_id_view
- rs_lv_id_view
- rs_sv_id_view
- rs_av_id_view
Appendix C: Metadata Import Syntax
- Metadata Import Syntax Overview
- Table Pair with One Test
- Table Pair with an SQL View as a Source
- Table Pair with Two Flat Files
- Table Pair with XML File and Relational Table
- Single-Table Constraint
- SQL View
- Lookup View
Appendix D: Jasper Reports
- Jasper Reports Overview
  - Status in Jasper Reports
  - Generating a Report
- Jasper Report Types
- Jasper Report Examples
Appendix E: Glossary
- aggregate tests
- bad records
- constraint value
- count test
- data validation
- Data Validation Option repository
- Data Validation Option user
- Data Validation Option folder
- DVOCmd
- format test
- inner join
- join view
- lookup view
- outer join
- single table
- SQL view
- table comparison
- table pair
- threshold
- unique test
- value test

Data Validation Option User Guide

10.5
- 10.5.2
- 10.5.1
- 10.4.0

Back Next

Data Validation Tests

After you create a table object, you can create one or more tests to validate the data. You can configure test functions and conditions when you create a test. The type of test that you can create depends on the table object that you use. For example, you can test for null values on a single table object, but not on a table pair object.

When you create a test, you choose the type of test that you want to perform and the type of operation, such as equals or does not equal. If you want to set a threshold for a margin of error, you can choose the approximate operator. You can also configure functionality such as case sensitivity or an allowance for the maximum number of bad records. You can create multiple tests on a table object. When you run a test on a table object, you run all tests that you created on that object.

You can create the following types of data validation tests:

Aggregate tests: Retrieves summarized information about data contained in the data source. Use an aggregate test to verify whether all records were moved or to identify incorrect logic in WHERE clauses.; An aggregate test can detect the following problems:
Lack of referential integrity in the source
Rejected rows by the target system
Incorrect logic in the mapping WHERE clauses

The following table describes the aggregate test functions:

Aggregate Test Functions
Description

Count
Counts non-null values.

Count Distinct
Counts distinct non-null values.

Count Rows
Counts all values including nulls.

Min
Calculates the minimum value for a field.

Max
Calculates the maximum value for a field.

Average
Calculates the average of a numeric field.

Sum
Calculates the total of a numeric field.; Perform aggregate functions in the following order:
COUNT and COUNT ROWS to count the number of rows
SUM to sum numeric fields
COUNT DISTINCT to compare detail and aggregate tables

For example, the target data for daily sales contains shows only total sales of $15,000. You know that your company sells at least 1,000 items a day and that the total sales should be between $100,000 and $200,000. You configure the following test functions:
COUNT_ROWS (any fld) > 1000 SUM (Amount) Between 100000, 200000
Set tests: Compares the distinct values of one field to the distinct values of another field. Determines if the set of values is equal in both data sources or if values are missing in one data source. You might use a set test to identify records in a fact table that are not in the dimension table. You can check target tables to find a lack of referential integrity in the target, either a child without a parent, or a fact record without a corresponding dimension table.; For example, you configure the fact table to be Table A and you configure the dimension table to be Table B. You configure the test
Set A in B
to perform the validation test. This test will verify that the fact foreign keys are in the parent table. You create an expression that concatenates composite keys, and you run the tests on that expression.; The following table describes the set test functions:

Set Test Functions
Description

Set A in B
Determines whether all distinct values of A are in B.

Set B in A
Determines whether all distinct values of B are in A.

Set A Equal B
Determines whether A and B have the same distinct values.

Set A Not In B
Determines whether A and B share no common values.
Value tests: Evaluates data based on values. Errors in complex transformations can manifest themselves in simple ways such as NULLs in the target, missing rows, or incorrect formats. To test for these types of errors, you can enforce constraints on target tables.; You might use an outer value test type to check the lack of referential integrity in the source and identify orphan records.; The following table describes the value and expression test functions:

Value Test Functions
Description

Value
Tests individual values across data sets. For example,
Value (FldA) Between 10,50
.

Outer Value
Compares values across tables row-by-row to identify orphans across data sets.

Not Null
Determines whether values in fields are not null. For example,
NOT_NULL (FldD)
.

Not Blank
Determines whether values in fields are not blank.

Unique
Determines whether values in fields are unique. For example,
UNIQUE (PK)
.

Format
Determines whether the values in the field match a pattern or constraint value.

Aggregate Test Functions	Description
Count	Counts non-null values.
Count Distinct	Counts distinct non-null values.
Count Rows	Counts all values including nulls.
Min	Calculates the minimum value for a field.
Max	Calculates the maximum value for a field.
Average	Calculates the average of a numeric field.
Sum	Calculates the total of a numeric field.

Set Test Functions	Description
Set A in B	Determines whether all distinct values of A are in B.
Set B in A	Determines whether all distinct values of B are in A.
Set A Equal B	Determines whether A and B have the same distinct values.
Set A Not In B	Determines whether A and B share no common values.

Value Test Functions	Description
Value	Tests individual values across data sets. For example, Value (FldA) Between 10,50 .
Outer Value	Compares values across tables row-by-row to identify orphans across data sets.
Not Null	Determines whether values in fields are not null. For example, NOT_NULL (FldD) .
Not Blank	Determines whether values in fields are not blank.
Unique	Determines whether values in fields are unique. For example, UNIQUE (PK) .
Format	Determines whether the values in the field match a pattern or constraint value.

Rename Saved Search

Table of Contents

Data Validation Option User Guide

Data Validation Option User Guide

Data Validation Tests

Data Validation Tests