Informatica DVO

What is Data Validation Option ?

What can be tested ?

To answer above two questions, Data Validation Option is a ETL Testing tool which comes along with power center and enables to test and validate data

Data Validation Option reads table definitions from Power Center metadata repositories, and checks the data for inconsistencies

Verify that moved or transformed data is complete and accurate and has not been changed because of errors in the movement or transformation process

Will not check the correctness of transformations or mappings designed in Informatica

Data Validation stages

A typical workflow or stages for data validation consists of multiple tasks-

1. Data Validation Option reads one or more Power Center metadata repositories
2. We define the validation rules(creation of tests) in Data Validation Option
3. We run the rules(tests) to ensure the data conforms to the validation rules. When we do this, Data  Validation Option performs the following tasks:

1.Creates and executes all tests through PowerCenter.
2.Loads results into the Data Validation Option results database and displays them in the Data Validation Option Client.

We examine the results and identify data inconsistencies in the ETL process

We repeat this process for new records.

Data Validation Option Client Layout Overview

The Data Validation Option Client contains multiple areas that allow us to perform different tasks.
Working with Repositories

We can perform below operations on repositories in DVO

Add  
Modify
Delete
Refresh
Exporting Repository Metadata

Table pair

A table pair is the basis for all tests that compare one table to another. We can select a relational
table, flat file, lookup view, or SQL view as one or both tables in a table pair.

We can perform below operations on Table Pairs in DVO

Add
Modify
Delete

Tests for Table Pairs

We can create\run the following types of tests on table pairs:

Aggregate -
•Includes COUNT, COUNT_DISTINCT, COUNT_ROWS, MIN, MAX, AVG, and SUM.

Set -
•Includes AinB, BinA, and AeqB.

Value -
•Includes VALUE and OUTER_VALUE.

Note: When we run tests, the target folder must be closed in the Designer and Workflow Manager. If the target folder is open, Data Validation Option cannot write to the folder, and the tests return an error

Single Table Constraints Pairs

single-table pair will be used to run tests on a single table. We can enforce valid values, aggregates, formats, and uniqueness
To run single-table constraints, we must create a single table. You can select a relational table, flat file, lookup view, or SQL view as a single table

SQL Views

SQL views facilitate the use of more complex functionality for single tables and table pairs.
An SQL view allows to use several tables and several calculations in a query to produce a set of fields that you can use as a table in a single table or table pair.

This functionality is similar to the SQL override in PowerCenter or a view in a relational database. You can use any valid SQL statement to create an SQL view.

Lookup Views

Data Validation Option lookup views allows to test the validity of the lookup logic in transformation layer
One disadvantage of the SQL view is that it does not allow the use of flat files or heterogeneous database tables

Join two heterogeneous sources with a lookup view

Import and export of Tests

Data Validation Option allows you to export and import test metadata.

Metadata import and export allows users to share tests and allows rapid generation of tests through scripting.

You can import and export the following metadata:

•Table Pairs
•Single Tables
•PowerCenter Sources
•SQL views
•Lookup views
•Join views

Reports Generation

Data Validation Option stores all test definitions and test results in the Data Validation Option repository. We can run reports to display test definitions and results.
Types of reports:

Summary of Testing Activities
Table Pair Summary
Detailed Test Results

Run tests through Command prompt

Invoke Data Validation Option capabilities at the command line.

Running tests at the command line allows you to schedule test execution. It also allows you to embed a specific test as part of the ETL workflow or as part of another process. For example, you can create an ETL process that moves data from source to staging, runs validation, and then moves data into the target or an error table based on the validation results.

Send Emails
Data Validation Option sends an email when the tests are complete.
You can provide multiple email addresses separated by commas.
The email specifies whether the test has passed or failed and provides a link to the test results.

Benefits of ETL Testing with Data Validation Option

• Production Reconciliation: Informatica Data Validation Option provides automation and visibility for ETL testing, to ensure that you deliver trusted data in your production system updates.
• IT Developer Productivity:  50% to 90% less time and resources required to do ETL testing
• Data Integrity:  Comprehensive ETL testing coverage means lower business risk and greater confidence in the data.

HOW TO: Enable debug logging in DVO
================================

To enable debug logging in Data Validator (DVO), edit the log4j.properties file under the DVO installation directory.

To increase the level of debugging, change the parameters from info to debug.

Steps:

====

1. Go to C:\Program Files (x86)\DVOSoft914\config directory



2. Edit the log4j.properties file



3. Change log4j.rootLogger from info to debug;log4j.rootLogger=debug, stdout



4. Save and close the file