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

29 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 Bru,


      You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

      Thank you for your helpful sugestions.
      I tried the option IS_EXPR_VAR and it is treating the string as a expression.
      But my requirement is that I have this expression logic changing for each record.
      so I need to lookup for each record, fetch the logic and set this to mapping variable.
      But when i try to set it , it gives me below error






      Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.


      Best Regards,

      Delete
    3. Hi Nel,

      Thank you for making your blogs an embodiment of perfection and simplicity. You make everything so easy to follow.


      Command Executed : /apps/informatica/951/isp/bin/infasetup.sh  defineDomain -du infa_dom -dp ***** -dt oracle -dn Dom_Amazon -cs jdbc:informatica:oracle://172.30.32.11:1522;ServiceName=AWSQA;MaxPooledStatements=20;CatalogOptions=0;BatchPerformanceWorkaround=true -nn node01_localHost -na awsppvupocinf01:6005 -ad Administrator -pd ***** -rf /apps/informatica/951/isp/bin/nodeoptions.xml -sp 6006 -ap 6008 -mi 6013 -ma 6113 -sv 6007 -asp 6009 -hs 8443 -kf /apps/informatica/951/tomcat/conf/Default.keystore -kp ***** -f -ld /apps/informatica/951/isp/logs -bd /apps/informatica/951/server/infa_shared/Backup -tls false.
      Once the data has been cleansed and standardized, duplicate records can be identified using various different matching techniquesInformatica Data quality training. This is also a developer task performed in the Developer Tool.

      I look forward to see your next updates.

      Cheers,
      Kevin

      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. 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 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
  7. 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
  8. 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

    ReplyDelete
  9. Can you provide some documents for installing DVO

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

    ReplyDelete
  11. 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
  12. Is that possible to have a small demo on DVO?

    ReplyDelete
  13. Hi,

    I have imported data from Salesforce to Informatica.The DVO tool is connected to Informatica.I have created a single table pair in DVO to validate null records for the imported salesforce data.Now if i run this single table in DVO,i get workflow and session errors.Please help.


    Regards
    Yeshwanth

    ReplyDelete
  14. Thank you for sharing an inspiring blog for Informatica learners, keep share more content on Informatica Online Training

    ReplyDelete
  15. Can anyone help me with how DVO Test cases look like.. please

    ReplyDelete
  16. Hello ,

    My name is ajay . I have few questions on dvo .

    1. My req is I have to compare data between two databases let’s say it A and B . For a Data is coming from table valued function and for b data is coming from view . How can I compare data in informatica dvo .

    2 . How does sql view and aggregate view works ? What’s their main usuage ??

    3. How I can perform minus query in informatica DVO .

    Can anyone help me with these questions .its urgent for me .
    Please email me answers at :
    ajay.great.kumar@gmail.com

    ReplyDelete
  17. Szia,


    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    Please see, if you have all the required pre-requisites ready before creating the domain.
    Hope the Informatica setup is done with an admin user.
    This error has occurred while running the key encryption process of domain creation.
    Its possible the required configuration to run the generateEncryptionKey command is missing or not accessible.

    Great effort, I wish I saw it earlier. Would have saved my day :)

    Shukran,
    Irene Hynes

    ReplyDelete
  18. Hi There,

    Thank you for update. From now onward I start to use this blog in my training practice. Thank you for explaining each step in screen shots. I use blogs for my easy reference which were quite useful to get started with.


    i found new issues

    as example below .. From the source i see that in lookup table both records are overlapping so i need to return both of the records from the lookup Informatica MDM Training

    How can this be achieved ? Is there any way i can mention in the where clause of the Lookup Overide query


    But great job man, do keep posted with the new updates.
    Best Regards,
    Irene Hynes

    ReplyDelete
  19. Hello There,

    Amaze! I have been looking bing for hours because of this and i also in the end think it is in this article! Maybe I recommend you something helps me all the time?

    At present we are using PC 10.1.0 on windows 2012. We want to migrate to new environment and upgrade to 10.1.1. It is not a big change on PC side but on the windows environment we are wondering if we can set up environment with a specific configuration to run Informatica better. Apar tfrom the minimum requirements found in the Installation guide I am unable to find any documentation in KB or online. Wondering if anyone have a specific advice on this.
    Our environment at present have Powercenter but we want to install Power exchange, Metadata manager after that. Any suggestions are really appreciated. I started using Informatica Data quality training blog for my training practice.

    But great job man, do keep posted with the new updates.

    Obrigado,
    Morgan lee

    ReplyDelete
  20. DVO Guru's. We have target system as SAP BW. Learnt from Informatica that they don't support SAP BW. From DVO we can't see BW Objects.
    Is there a way we can perform testing without SAP BW object view?
    This is urgent. Please advise.
    Thank you

    ReplyDelete
  21. Hi,
    Have created table pair test, the data in table A is same as Table A, the only difference is that data in table is swapping its location due to which the test case is getting failed. For eg:- table A has columns control number=12 and brcode=001,control number=12 and brcode=002 while table B data is control number=12 and brcode=002, control number=12 and brcode=001

    ReplyDelete
  22. How can I limit the cache size files created in informatica DVO using DVO preference

    ReplyDelete