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

Generating Table Pairs and Tests

Run a table comparison to generate table pairs between tables in two specified folders. Data Validation Option also generates count and value tests for each table pair.

In Data Validation Option, click

Action

Compare Tables

The

Compare Tables

dialog box appears.

The dialog box shows table properties for both tables in the table pair, comparison properties, and test properties. The dialog box also contains a section where you can specify a file that maps each source table to a target table.

Select the repositories that contain the tables that you want to compare.

Select the folders that contain the tables that you want to compare.

Select

Sources

Targets

for each subfolder.

Select the default connection for each folder.

If the connection is for IBM DB2, enter the default database owner name in the

Owner

field.

If connection is for flat files, in the

Source Dir

field, enter the default directory that contains the files.

If the connection is for SAP, click

Select SAP Table Parameters

and configure the default SAP source parameters.

Select

Sort in DB

to push the sorting logic for joins in table pairs to the database.

You can select whether to save all the bad records.

If you choose to save all the bad records, select whether to save the bad records in a flat file or in the Data Validation Option schema.

To generate table pairs for tables that have different names, select

Table names differ between source and target

, and then click

Browse

to find the spreadsheet file that contains the table pair definitions.

To generate table pairs for tables that have the same name, select whether to compare columns by name or position.

If you select compare columns by position, if applicable, specify the number of columns to skip in Table A or Table B.

In the

Folder to Place Tests

field, select the folder to store the table pairs and tests.

In the

Tests to Include

field, specify whether to generate count tests or count and value tests.

In the

Trim Trailing Spaces from All Generated Tests

field, select whether to trim the trailing spaces in the values of table columns.

To generate value and outer value tests for tables that do not have primary keys, click

Browse

to select the text file that lists the primary keys for the flat files or tables.

If you select a primary key for the table and specify a primary key file, Data Validation Option ignores the primary key file.

If a primary key does not exist for a table or file, in the

If no PK

field, select whether you want to generate count tests only or skip the test generation.

If you skip the test generation and no primary key is defined, Data Validation Option does not generate the table pair.

Click

The

Compare Tables

dialog box previews table pairs that will be generated.

The following figure shows a preview of the table pairs that will be generated and the errors:

The dialog box shows table pairs that will be created. It also shows the following errors: no joins are defined and the table pair description is already used by an existing table pair.

Selected table pair

Errors for the selected table pair

To not generate a table pair, select the table pair and click

Remove

If there is a problem with a table pair, select the table pair to view the error.

If an error says that no join is defined, select the table pair, and then select the join fields in the

Join Conditions

area.

Click

Mismatched Tables

to view a list of all tables that are not matched in each folder.

Data Validation Option does not generate a table pair or tests for tables that are not matched.

Click

Finish

Data Validation Option generates the table pairs and tests.

If a default setting does not apply to a particular table in a table pair, you can edit the table pair to change the setting after Data Validation Option generates the tables pairs and tests. For example, if a table requires a different connection than the default connection that you specified for all tables in the table comparison, edit the table pair and change the connection.

Rename Saved Search

Table of Contents

Data Validation Option User Guide

Data Validation Option User Guide

Generating Table Pairs and Tests

Generating Table Pairs and Tests