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

Join View Example

You need to validate the inventory sales done by the employees and partners to cross-check with the annual sales report.

Account table in an SAP system holds the information of an employee account. Partner table is a Salesforce table that contains the information of the inventory sold to a partner associated with an employee. Inventory is a flat file that contains the details of the inventory sold. Account_History is an Oracle table that contains the history of activities done by the account.

Current requirement is to validate data across the tables based on the inventory sales of an account. You also need to validate the account details with the historic account details to check for discrepancies. You can create a join view with the tables so that you can run single table tests to validate data.

Tables and Fields

The following table lists the join view tables and their columns:

Table	Columns
Account (SAP)	Account table contains the following columns: Account ID Account Name Collection Inventory
Partner (Salesforce)	Partner contains the following columns: Partner ID Partner Name Inventory Cost Associated Account ID
Inventory (Flat file)	Inventory table contains the following columns: Inventory ID Quantity Associated Partner ID Associated Account ID
Account_History (Oracle)	Account_History contains the following columns: Historic Account ID Account Name Total Inventory Total Collection

Creating the Join View

Enter Account_Cumulative as the description.

Add Account as the first table in the join view.

Add Partner, Inventory, and Account_History tables in that order.

Configure the table definitions with the required join types.

Create join conditions for Partner, Inventory, and Account_History.

Table Definition Configuration

The following list describes the tables and their join types when you configure the table definitions:

Partner: You want to capture the details of partners associated with each account. Configure an inner join for the Partner table so that Data Validation Option adds the details of the partners for which there are corresponding accounts to the join view.
Inventory: You want to capture the details of the inventory sold by the partners. Configure an inner join for the Inventory table so that Data Validation Option adds the details of the inventory for which there are corresponding partners to the join view.
Account_History: You want to capture the historic details of an account. Configure a left outer join for the Account_History table so that Data Validation Option adds all the historic account details to the join view.

Adding Join Conditions

Configure the following join conditions for the tables:

Partner: Select Account as the join table. Select Account ID output field from the Account table as the left field and Associated Account ID output field from the Partner table as the right field of the join.
Inventory: Select Partner as the join table. Select Partner ID output field from the Partner table as the left field and Associated Partner ID output field from the Inventory table as the right field of the join.
Account_History: Select Account as the join table. Select Account ID output field from the Account table as the left field and Historic Account ID output field from the Account_History table as the right field of the join.

The following figure shows the formation of the join view with the table relationship:

The Account, Partner, Inventory, and Account_History tables are joined together to create the join view.

After you create the join view, create a single table with the join view. Generate and run tests on the single table to validate the data in the join view.

Removing Table from the Join View

After you run the required tests for the join view with all the tables, you may want to remove the partner information and run tests solely for the account. If you remove the table Partner from the join view, the join condition for the table Inventory is no longer valid. You need to create another join condition for the table Inventory to save the join view.

The following figure shows the broken join view:

The Account and Account_History tables are joined together to create the join view. The Inventory table is not connected in the join view because it is not included in a join condition.

Add a join condition to the table Inventory with Account as the join table. Join Account ID field in the Account table with Associated Account ID field in the Inventory table.

The following figure shows the join view without the Partner table:

The Account, Inventory, and Account_History tables are joined together to create the join view.

Rename Saved Search

Table of Contents

Data Validation Option User Guide

Data Validation Option User Guide

Join View Example

Join View Example

Tables and Fields

Creating the Join View

Table Definition Configuration

Adding Join Conditions

Removing Table from the Join View