Saturday, March 29, 2014

Informatica PowerCenter Data Validation Option (DVO) Part-I: Introduction


The DataValidator for PowerCenter product was originally developed by a company DVO SOFTWARE. It is now available under the Informatica brand as Informatica PowerCenter Data Validation Option.

DVO is a custom tool built on top of Informatica PowerCenter. DVO integrates with the Informatica PowerCenter Repository and Integration Services and enables developers and business analysts to create rules to test the data being transformed during the data integration process.


DVO Architecture
 Courtesy: Informatica Corp.
  • Data Validation Option requires installation and setup of PowerCenter. 
  • Source and target data table and file definitions are imported from PowerCenter repositories.  
  • You set up table pairs and test rules in Data Validation Option. This test metadata is stored in the Data Validation Option repository.
  • When the tests are run, DVO communicates with PowerCenter through an API to create appropriate mappings, sessions, and workflows, and to execute them.
  • PowerCenter connects to the data being tested instead of Data Validation Option.
  • After the tests are executed, results are stored in the Data Validation Option repository and displayed in the DVO Client.

Typical Data Validation Workflow
  • Data Validation Option reads one or more PowerCenter metadata repositories.
  • You define the validation rules in Data Validation Option.
  • You run the rules to ensure the data conforms to the validation rules. When you do this, Data Validation Option performs the following tasks:
          - Creates and executes all tests through PowerCenter.
          - Loads results into the DVO results database and displays them in the DVO Client.
  • You examine the results and identify sources of inconsistencies in the ETL process or the source systems.
  • You repeat this process for new records.

Benefits of DVO
  • DVO reduces the time required for data validation and production data auditing and verification significantly, eliminating the traditional methods of validating data by visual inspection, data comparison tools or writing SQL scripts viz row counts, minus queries etc. The risk of validating data by traditional methods is particularly high when there is a larger data set to work with and a higher chance of occurence of human errors.
  • Maintaining different test scripts to validate data for different projects is cumbersome. DVO provides a easy-to-use GUI interface to test the rules created for data validations for multiple projects. 
  • No programming skills needed to create validation tests.
  • DVO includes a repository with reporting capabilities to provide a complete audit trail of all tests and their results.
  • It reads data definitions from PowerCenter metadata repositories and can easily deal with data definition changes.

Key Pointers For DVO Testing
  • DVO tests data only, not mappings or workflows. Testing mappings is unit testing, which is different from data validation.
  • DVO only reads table definitions from PowerCenter metadata repositories, and checks the data at either end of the process and will show problems or inconsistencies only. It does not attempt to identify the bug in the ETL process.
  • Do not copy formulas from the ETL mapping into Data Validation Option. If there is an error in the ETL mapping formula, you will replicate it in Data Validation Option, and Data Validation Option will not catch it. Therefore, you must always maintain a proper separation between ETL and testing.
  • Do not try to do everything in Data Validation Option. If you think that a particular step can be accomplished more easily with SQL, use SQL. If you run 95% of your validation in Data Validation Option, and can document it with the audit trail, this is more than enough.

 DVO Usage
  • Validate Data being Transformed - ETL Testing, ETL Reconciliation, Application Migration

  

Courtesy: Informatica Corp.


  • Validate if Data is Identical - ETL Migration, Database Migration, ETL Version Upgrade

                                                 Courtesy: Informatica Corp.

References
  • www.informatica.com
  • Informatica PowerCenter Data Validation Option (Version 9.1.2.0) Installation and User Guide

23 comments:

  1. Thanks nelrick for sharing this knowledge on DVO. Could you please share some knowledge or one example about how to setup one test case to validate data using DVO.

    ReplyDelete
    Replies
    1. Hi Manoj,

      I'll be posting a few articles on how to run tests using DVO soon.

      Regards,
      Nelrick

      Delete
  2. Hi,

    Can you please let us know how we do data validation using this DVO tool.
    I mean the process or the steps we do in Informatica.

    Regards
    Navya

    ReplyDelete
  3. Hi Navya,

    There are a few interesting videos on DVO. Below are the links:
    1. http://www.datavalidation.me/
    2. https://www.youtube.com/watch?v=Dx26IifyeEg
    3. https://www.youtube.com/watch?v=MB3PvIkraXw

    I will try posting a few more articles on DVO testing soon.


    Regards,
    Nelrick

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hello -
      Below link is not working. Do you have more videos.
      http://www.datavalidation.me/

      Delete
  4. Hello Nelrick,

    Thank you for the list of links.

    Regards
    Navya

    ReplyDelete
  5. how to run single or selected test case in the table pair

    ReplyDelete
    Replies
    1. It is not possible to run a single rule inside a single table or table pair. Even if you select a single rule and execute it, all the rules inside the
      single tale or table pair gets kicked off.

      Delete
  6. Verification and validation techniques applied throughout the development process enable you to find errors before they can derail your project.
    they are involved in lot of process improvements which will actually help the clients to deliver best software.

    software validation

    ReplyDelete
    Replies
    1. I also tell my team members to spend qaulity time doing analysis of the work they do. Do not spend the entire day in just doing testing.
      Spending everyday sometime on Analysis will help them
      to move in the right direction in the work and also helps them to see in a bigger and broader perspective of the work.
      Finally, just spend good amount of time in planning, reviews and analysis, the execution will be done like in no time.

      software validation

      Delete
  7. Verification and Validation are the activities performed to improve the quality and reliability of the system and assure the product satisfies the customer needs.
    Verification assures the product of each development phase meets their respective requirements.
    Validation assures the final product meets the client requirements.

    equipment validation
    labview programming
    software validation

    ReplyDelete
  8. I just wanted to comment your blog and say that I really enjoyed reading your blog post here.
    It was very informative and I also digg the way you write!I also provide this service u can visit my site.

    software validation

    ReplyDelete
    Replies

    1. I really like examining and also following ones write-up when i locate them incredibly beneficial and also fascinating.
      That write-up is usually just as beneficial along with fascinating.Verification and Validation both are independent type of testing. Obviously,
      If we look both of these activities as a whole, we can also call it testing.

      software validation

      Delete

  9. This applies not only during the IQ, OQ and PQ validation phases, but far earlier,
    beginning with the first meeting, and continuing through analysis of your process.
    Software validation is a part of the design validation for a finished device,
    but is not separately defined in the Quality System regulation.


    software validation

    ReplyDelete
  10. Can you provide some documents for installing DVO

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. Good artcile, but it would be better if in future you can share more about this subject. Keep posting.
    Datavalidation

    ReplyDelete
  13. can you tell how to display the below result in dvo.
    1-select count(*),max(last_upd_date) from table a
    2-
    select count(*),max(last_upd_date) from table a
    union all
    select count(*),max(last_upd_date) from table b
    union all
    select count(*),max(last_upd_date) from table c

    ReplyDelete
    Replies
    1. you can create a SQL view and call it in the single table or table pair.

      Delete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Is that possible to have a small demo on DVO?

    ReplyDelete