Saturday, April 5, 2014

Informatica PowerCenter Data Validation Option (DVO) Part-II: Installation and Configuration

The illustration below explains the steps involved to install and configure the Informatica PowerCenter Data Validation Option for usage with Informatica PowerCenter installed on a local machine.


The OS and software versions used for this illustration are as below:
1. Microsoft Windows XP SP3 (32-bit)
2. Microsoft SQL Server 2008
3. Informatica PowerCenter 9.1.0 HotFix2
4. Informatica PowerCenter Data Validation Option 9.1.4.2


The Informatica PowerCenter Server and Clients, PowerCenter DVO Client and Microsoft SQL Server 2008 are all installed on the same machine in this illustration. If the Informatica PowerCenter Server is installed on a machine other than the local machine, install the Data Validation Option on the same machine as the Informatica PowerCenter Clients.

Note: The installation and configuration steps may differ for different versions of the DVO Client and different database types. In this illustration, Microsoft SQL Server 2008 is used as the database to create the Data Validation Option repository tables.


Prerequisite Checks
  • The machine that hosts Informatica Services must be installed and configured on the same local area network as the Data Validation Option Client machine.
  • The Informatica domain must contain at least one PowerCenter Integration Service.
  • Install PowerCenter Client on the same machine where Data Validation Option will be installed.
  • Setup at least one PowerCenter repository.

System Permissions
You require certain system permissions to complete Data Validation Option installation and configuration. To complete Data Validation Option setup, verify that you have the permission to complete the following tasks:
  • Create a database, including the ability to create schemas, tables, indexes, sequences, and views.
  • Create a PowerCenter connection object in the Workflow Manager.
  • Create a folder in a PowerCenter repository in the Repository Manager.
  • Create and associate a PowerCenter Integration Service with the PowerCenter repository that the Data Validation Option user can access. (This task can be performed by the Informatica PowerCenter Administrator).
  • Copy a JAR file onto the machine that hosts Informatica Services. (This task can be performed by the Informatica PowerCenter Administrator).
  • Configure the Administrator tool. (This task can be performed by the Informatica PowerCenter Administrator).
  • Modify the environment variables on the machine where you install Data Validation Option.
  • Read and write on the Data Validation Option installation directory and subdirectories.

The installation and configuration steps are explained below. 

STEP-1 : Creation of SQL user account for DVO repository
  • For this illustration, a new database infa_dvo is created in SQL Server along with a SQL user account "infa". The database role membership assigned for the "infa" user on the infa_dvo database is db_owner as shown below.
  • The infa_dvo database created will contain no database objects initially as shown below.




STEP-2 :  Verify if the PowerCenter Repository and Integration Services are running
  • For this illustration, the Informatica PowerCenter Repository Service PowerCenter_RS and the Integration Service PowerCenter_IS are created and running in the Informatica domain Domain_bi-7984eff67b9e.


STEP-3 : Creation of Informatica PowerCenter User
  • This step can be carried out with the help of the Informatica Administrator. The new Informatica user to be created should have privileges to create a folder in Informatica PowerCenter Repository Manager and to create a relational connection in Informatica Workflow Manager.
  • For this illustration, an Informatica user "nelrick" is created using the Informatica Administrator in the Informatica PowerCenter repository PowerCenter_RS with the below roles.

  • The privileges assigned to the Informatica user "nelrick" on selection of the above roles are shown below.


STEP-4 : Creation of folder in Informatica PowerCenter Repository Manager
  • Login to the Informatica PowerCenter repository from the PowerCenter Repository Manager tool using the Informatica login "nelrick" created in STEP-3. 
  • Create a folder DVO as shown below. This folder is needed for DVO to store the mappings, sessions and workflows that get generated while performing tests and to execute these tests. This folder is to be used specifically to store only the DVO mappings. Every DVO user must have the privileges to use this folder.

  • The user "nelrick" has permissions on the DVO folder as shown below. Grant permissions to other users if needed on the DVO folder.


STEP-5 : Creation of relational connection in Informatica Workflow Manager
  • Login to the Informatica PowerCenter repository from the PowerCenter Workflow Manager tool using the Informatica login "nelrick".
  • Create a relational connection INFA_DVO that points to the DVO repository database infa_dvo created in STEP-1 with the SQL user account "infa" as shown below. This is needed for Informatica PowerCenter Integration Service to connect to the DVO repository to store the test results in the DVO repository tables.


STEP-6 : Verify if domains.infa file is present on the machine where PowerCenter Clients are installed
  • Check if the domains.infa file is available in the following location: \clients \PowerCenterClient\.
  • If the domains.infa file is not available on the PowerCenter Client machine,
    copy the file from the following location on the PowerCenter server machine: installation directory>\

STEP-7 : Creation of environment variable on DVO Client machine
  • Create an environment variable called INFA_HOME and set the value to the location of the domains.infa file as shown below. Enter only the domains.infa file path, excluding the domains.infa filename, for the variable value as shown below. The value is C:\Informatica\9.1.0\clients\PowerCenterClient\ for this illustration.


STEP-8 : Installation of DVO on the PowerCenter Client machine
  • Start the installation of the Informatica PowerCenter DVO client. The initial setup screen us as shown below.
  • Specify the installation directory as shown below.
  • After installation completes, keep the default options checked to launch DVO as shown below.


STEP-9 : Configuration of the DVO repository in the DVO client
  • While DVO is launching, it attempts to connect to the DVO repository as shown below.
  • Since the DVO client has not been configured to connect to the DVO repository, the below message is displayed. Click OK to continue.
  • The DVO client preferences window opens. Select Data Validation Option on the left and enter the Data Validation Option Repository database details as specified in STEP-1. Click Test to verify database connectivity.
  • If connectivity is successful, the below message is displayed. Click OK to proceed.
  • The Data Validation Option Repository schema needs to be created as shown below. Click Yes to continue.
  • After the schema objects are successfully created, the below message is displayed.
  • Verify if the schema objects are successully created in the infa_dvo database as shown below.
  • Next click on the Mapping Properties on the left in the Preferences window. Keep the defaults as shown below. Click Save.
  • The Mapping Properties and Descriptions are explained below.


STEP-10 : Copy the dvoct.jar file from the DVO installation directory to the Informatica Server
  • For this illustration, the dvoct.jar file is created in the D:\Informatica9.1.0\DVO\powercenterlibs directory. This file needs to be copied to a folder in the root directory on the machine that hosts Informatica Services. This activity can be performed by the administrator of the Informatica Server if the Informatica Server is installed on a separate machine. In this illustration the dvoct.jar file is copied to the C:\Informatica\DVO folder on the local machine.

STEP-11 :  Updating the JAVA SDK Classpath for the PowerCenter Integration Service
  • This activity can be performed by the Informatica Administrator. 
  • Login to the Informatica Administrator tool. From the navigator, select the PowerCenter Integration Service PowerCenter_IS and click the Processes tab. 
  • Edit the Service Process Properties > General Properties and edit the JAVA SDK Classpath to enter the path to the dvoct.jar file on the Informatica Server as shown below. If there is a value in JAVA SDK Classpath, add a semi-colon after the classpath before the dvoct.jar file path is added.


STEP-12 :  Adding the Informatica Repository that contains the folder where DVO will create mappings, sessions and workflows for tests to be executed
  • Launch the DVO client as shown below.
  • Right-Click on INFA Repositories and select Add Repository to add the Informatica Repository PowerCenter_RS that contains the DVO folder created in STEP-4.
  • This opens up the Repository Editor window. Enter the details as shown below. If the Security Domain is Native, it can be left blank.
  • Click Test. If all the details entered in the Repository Editor window are correct, the below message will be displayed.
  • If another Informatica Repository is to be added later, ensure that the Contains Target Folder is set to false because only one repository can have a target folder. In this illustration, the target folder is DVO created in the PowerCenter_RS repository.
  • The below message is displayed after the PowerCenter_DVO repository is added. Click OK to refresh the folders and connections in the PowerCenter_RS Informatica PowerCenter repository.
  •  After the refresh is complete, the below message is displayed.
  • The folders in the PowerCenter_RS Informatica PowerCenter repository will be displayed under the PowerCenter_DVO repository in the DVO client as shown below.

The DVO client is setup for testing and validating data loaded by mappings created in the PowerCenter_RS repository.

References
  • Informatica PowerCenter Data Validation Option (Version 9.1.2.0) Installation and User Guide

4 comments:

  1. what version of DVO can be used for informatica 9.5.1 ?

    ReplyDelete
    Replies
    1. Hi Rucha,

      The current version of DVO available is 9.5.2 and it can be used with PowerCenter 8.6.1 HF 11 and above.

      Informatica Marketplace link for DVO: https://community.informatica.com/solutions/informatica_data_validation

      Regards,
      Nelrick

      Delete
  2. Hi, Is there any option to verify/Compare tables which has no primary keys defined.. and consider the number of columns as 100+

    Thanks,
    Ramesh M

    ReplyDelete
  3. Hi,

    I imported Salesforce data to informatica.In my DVO I created a single table pair to validate this imported Salesforce data to validate duplicate record s.now when I run this table pair I get workflow error.Please help.

    ReplyDelete