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

35 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 There,


      Nice to be visiting your blog again, it has been months for me. Well this article that Ive been waited for so long.

      Is there any other study content beyond the information provided by the courses?
      I"m about to schedule my exam (section 4 in the CDI certification), but wanted to ensure (or find out) if the exam will cover more then what is in the course content.

      But nice Article Mate! Great Information! Keep up the good work!


      Many Thanks,
      Reena

      Delete
    3. 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
    4. 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 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. Good artcile, but it would be better if in future you can share more about this subject. Keep posting.
    Datavalidation

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

    ReplyDelete
  14. 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
  15. Thank you for sharing an inspiring blog for Informatica learners, keep share more content on Informatica Online Training

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

    ReplyDelete
  17. 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
  18. Bonjour,
    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

    Good luck on your test and I'd be interested to hear your testing thoughts.
    Almost all function specifications I've seen so far are intellectual property of the respective organisation.
    But you can find some templates in Velocity, the Best Practices collection provided and maintained by Informatica Professional Services.
    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.


    Ciao,

    kevin

    ReplyDelete

  19. I appreciate your work on Informatica. It's such a wonderful read on Informatica.Keep sharing stuffs like this. I am also educating people on similar technologies so if you are interested to know more you can watch this:-
    https://www.youtube.com/watch?v=56vMQ1lG-vc

    ReplyDelete
  20. 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
  21. Hello Buddie

    Awesome article. Thanks for making that available. I've been using your help to build my own POC and will publish the steps in another blog soon.

    Billing has trust score more than sales for one column in BO, so According to my understanding as 1st record has same pkey_src value as 2nd record coming from billing system so 2nd record should go as an update for 1st record but its going for insert only.

    Once again thanks for your tutorial.

    Many Thanks,
    Irene Hynes

    ReplyDelete
  22. Hello There,

    Thanks a trillion mate!
    It works like charm, saved a lot of energy & time.

    I am working MDM 10.2 provisioning tool. Informatica MDM Training USA I need to add a filter to consolidation indicator in provisioning tool. I am able to add the filter. But while publishing change list is blank. PFA for snapshots.
    How can I set filter on consolidation indicator(System variable)? Could anyone please help me.

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Ciao,
    Preethi.

    ReplyDelete
  23. 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
  24. 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
  25. It was really a nice article and i was really impressed by reading this Informatica Online Course India

    ReplyDelete