ETLs

Informatica PowerCenter Connector

Informatica PowerCenter is an ETL tool that extracts, transforms, and loads data from sources which allows you to read data from or write data to various data sources, including databases, file systems, cloud storage, and applications such as Salesforce, SAP, and Microsoft Dynamics. By integrating with these data sources, PowerCenter can extract, transform, and load data (ETL) into a centralized data warehouse or data lake.

OvalEdge uses the JDBC driver to connect to the data source, which allows you to crawl (Workflows, Tasks, Mapping, Transformations), and build Lineage. 

Informaticapowercenter_arch

Connector Capabilities

The following is the list of objects and data types supported by the Informatica PowerCenter connector.

Functionality Supported Data Objects
Crawling Workflows, Tasks, Worklets, Mappings, Mapplets, Transformations.
Crawling Parameter Files Informatica uses parameter files to override connection and source/target objects in runtime. We require access to read parameter files to link the correct source or target during the lineage-building process.

Prerequisite

The following are the prerequisites required for establishing a connection between the connector and the OvalEdge application. 

  1. Supported PowerCenter Version
  2. Set up a Service account with proper permissions
  3. Configure environment variables (Optional)

Establish Environment Variables (Optional)

This section describes the settings or instructions that you should be aware of prior to establishing a connection. If your environments have been configured, skip this step.

Configure Environment Names

The Environment Names allow you to select the environment configured for the specific connector from the dropdown list in the Add Connector pop-up window.

You might want to consider crawling the same schema in both stage and production environments for consistency. The typical environments for crawling are PROD, STG, or Temporary, and may also include QA or other environments. Additionally, crawling a temporary environment can be useful for schema comparisons, which can later be deleted, especially during application upgrade assistance. 

Steps to Configure the Environment

  1. Navigate to Administration > System Settings.
  2. Select the Connector tab.
  3. Find the Key name “connector.environment”.
  4. Enter the desired environment values (PROD, STG) in the value column. 
  5. Click ✔ to save.

Supported PowerCenter Version

To use the connector, connection details as specified in the following table are required.

Edition

Version

Support

PowerCenter

9.6 and 10.4

Supported

JDBC Driver

Oracle, PostGreSQL, DB2 and Microsoft SQL Server compliant versions with PowerCenter Repository Schema

Supported

Service Account with Minimum Read Permissions

A service account with read-only privileges on PowerCenter repository schema is required for crawling.

Operation 

Minimum Access Permission 

Connection validation

Select All Tables and views on the repository schema

Crawl Folders

Select All Tables and views on the repository schema

Crawl Workflows, and Mappings

Select All Tables and views on the repository schema

Establish a Connection


To establish a connection, complete the following steps:

  1. Log in to the OvalEdge application, navigate to the Administration module, and click on Connectors.
  2. Click on the + icon (New Connector ) the Add Connector pop-up is displayed.
  3. Search/ click on the desired connector and the Add Connector pop-up with the selected connector details is displayed.
     

    Field Name

    Description

    Connector Type

    By default the selected connection type is displayed as the Informatica PowerCenter. 

    If required, the drop-down list allows the user to change the connector type and based on the selection of the connection type, the fields associated with the selected connection type are displayed.

    Credential Manager

    The purpose of a credential manager is to enhance the security that stores the API keys, passwords, certificates, and other sensitive data securely and helps to manage access, rotates and audit secrets. 

    OE Credential Manager: Informatica PowerCenter connection is configured with the basic Username and Password of the service account in real-time when OvalEdge establishes a connection to the Informatica PowerCenter database. Users need to add the credentials manually if the OE Credential Manager option is selected.

    HashiCorp: The credentials are stored in the HashiCorp database server and fetched from HashiCorp to OvalEdge.

    AWS Secrets Manager: The credentials are stored in the AWS Secrets Manager database server and fetched from the AWS Secrets Manager to OvalEdge.

    Refer to Azure Key Vault Integration 

    For more information on Credential Manager, refer to Credential Manager

    License Add-Ons

    All the connectors will have a Base Connector License by default that allows you to crawl and profile to obtain the metadata and statistical information from a data source. 

    OvalEdge supports various License Add-Ons based on the connector’s functionality requirements.

    • Select the Auto Lineage Add-On license that enables the automatic construction of the Lineage of data objects for a connector with the Lineage feature. 
    • Select the Data Quality Add-On license to identify, report, and resolve the data quality issues for a connector whose data supports data quality, using DQ Rules/functions, Anomaly detection, Reports, and more.
    • Select the Data Access Add-On license that will enforce connector access via OvalEdge with Remote Data Access Management (RDAM) feature enabled. 

    Connector Name*

    Select a connection name for Informatica PowerCenter. You can specify a connection name to identify the Informatica PowerCenter connection in OvalEdge.

    Example: Informatica PowerCenter_test

    Connector Environment

    The environment dropdown menu allows you to select the environment configured for the connector from the dropdown list. For example, PROD, or STG (based on the configured items in the OvalEdge configuration for the connector.environment).
    The purpose of the environment field is to help you identify which connector is connecting what type of system environment (Production, STG, or QA).  
    Note: The steps to set up environment variables in explained in the prerequisite section.

    Informatica Database

    Informatica has four databases in order to store metadata. They are: DB2, PostgreSQL, Oracle, and SQL Server. Select the database from the drop-down list. Based on the database selection, user needs to provide the respective details.

    However, the SQL server will authenticate the connection based on either SQL server authentication or Windows Authentication.

    Click here for SQL Server Database Authentication information

    Server*

    This is the Hostname or IP address of the Informatica PowerCenter.

    Port*

    Enter the port number to connect to the database.

    Database*

    Provide the name of the database that is associated with the Informatica PowerCenter connection, if necessary.

    Repository Schema

    The Repository schema contains all metadata related to a mapping, source, or target stored. Enter the name of the repository schema.

    Driver*

    The driver is defaulted based on the Informatica database selected from the dropdown. By Default is “com.microsoft.sqlserver.jdbc.SQLServerDriver”

    Username*

    Username required to connect to the Informatica PowerCenter Repository Database. Enter the Service Account Name established to access the Informatica PowerCenter Repository. 

    Note: Sometimes, this field is autofill filled by the web browser with the current OvalEdge user login. Please enter the Informatica Power Center Service Account name 

    Password*

    Password for Informatica PowerCenter Repository User account

    Connection String

    Set the Connection string toggle button to automatic to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.

    If the connection string is set to:

    • Auto: jdbc: oracle://{server}:{port};database={sid}
    • Manual: Please enter a valid connection string

    Parameter Files Directory

    It is important to provide the correct path to the parameter file that is used in the mapping. This is because the parameter file contains information that is necessary for the lineage to correctly identify the source and target objects used in the mapping.

    Example: https://ovaledge.s3.us-west-1.amazonaws.com/OvalEdge+Clients//Documents/Parameters.txt

    Default Governance Roles*

    Users can select a specific user or a  team from the governance roles (Steward, Custodian, Owner) that get assigned for managing the data asset. 

    Note: The drop-down list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security | Governance Roles section.  

    Admin Roles*

    Select the required admin roles for this connector.

    • To add Integration Admin Roles, search for or select one or more roles from the Integration Admin options, and then click on the Apply button. 
      The responsibility of the Integration Admin includes configuring crawling and profiling settings for the connector, as well as deleting connectors, schemas, or data objects.
    • To add Security and Governance Admin roles, search for or select one or more roles from the list, and then click on the Apply button. 
      The security and Governance Admin is responsible for:
      • Configure role permissions for the connector and its associated data objects.
      • Add admins to set permissions for roles on the connector and its associated data objects.
      • Update governance roles.
      • Create custom fields.
      • Develop Service Request templates for the connector.
      • Create Approval workflows for the templates.

    No Of Archive Objects*

    It is the number of last modifications made in the metadata data of a dataset at Remote/source. By default, the number of archive objects is set to disable mode.
    Click on the Archive toggle button and enter the number of objects you wish to archive.

    Select Bridge

    With the OvalEdge Bridge component, any cloud-hosted server can connect with any on-premise or public cloud data sources without modifying firewall rules. A bridge provides real-time control that makes it easy to manage data movement between any source and destination.

    For more information, refer to Bridge Overview.

  4. Click on the Validate button to validate the connection details. 
  5. Click on the Save button to save the connection.  Alternatively, you can also directly click on the Save & Configure button that displays the Connection Settings pop-up window to configure the settings for the selected Connector. The Save & Configure button is displayed only for the Connectors for which the settings configuration is required.

    Note: * (asterisk) indicates the mandatory field required to create a connection. Once the connection is validated and saved, it will be displayed on the Connectors home page. 
    Note: You can either save the connection details first, or you can validate the connection first and then save it. 

    Connection Validation Errors  

    S.No.

    Error Messages

    Description

    1

    Failed to establish a connection, Please check the credentials(User name, Password, Server name, etc.,)

    In case of an invalid user name, wrong password, or invalid Server name 

    Note: If you have any issues creating a connection, please contact your assigned OvalEdge Customer Success Management (CSM) team.

    Connection Settings 

    Lineage

    For the Informatica PowerCenter Connector, only the Lineage setting option is available, and it is enabled when the Auto Lineage License option is selected.

    The purpose of the lineage setting is to serve the option of changing the server/source connection to build the lineage. You can configure multiple servers simultaneously in the  Selecting Source Server Type for lineage and also set the connection priority lists to pick the tables in the Connections Priority to pick the source table for lineage building.

    Note: For more information, refer to the Connector Settings.

    The Crawling of Schema(s)

    You can use the Crawl/Profile option, which allows you to select the specific schemas for the following operations: crawl, profile, crawl & profile, or profile unprofiled. For any scheduled crawlers and profilers, the defined run date and time are displayed to set.

    1. Navigate to the Connectors page, and click on the Crawl/Profile option.
    2. Select the required Schema(s).
    3. Click on the Run button that gathers all metadata from the connected source into OvalEdge Data Catalog. 

    Note: For more information on Scheduling, refer to Scheduling Connector

    Additional Information

    SQL Server Database Authentications

    OvalEdge connector integrates with authentication mechanisms for crawling databases and schemas from an Informatica PowerCenter. The users must enter the source credentials and database information for each type of connectivity.

    When a user selects a SQL Server database it requires the following types of authentication.

    1. SQL Server Authentication
    2. Windows Authentication

    SQL Server Authentication

    Enter the following details to establish an Informatica Power Center connection using the SQL Server Authentication

    Field Name

    Description

    Server*

    This is the Hostname or IP address of the Informatica Power Center Repository Database

    Port*

    Enter the port number to connect to the database

    Database*

    Provide the name of the database that is associated with the Informatica PowerCenter connection, if necessary.

    Repository Schema

    The Repository schema contains all metadata related to a mapping, source, or target stored. Enter the name of the repository schema. 

    Driver*

    The driver is defaulted based on the Informatica database selected from the dropdown. By Default it is “com.microsoft.sqlserver.jdbc.SQLServerDriver”

    Username*

    A username is required to connect to the Informatica PowerCenter Repository Database. Enter the Service Account Name established to access the Informatica PowerCenter Repository. 

    Note: Sometimes, this field is autofill filled by the web browser with the current OvalEdge user login. Please enter the Informatica Power Center Service Account name 

    Password*

    Password for  Informatica PowerCenter Server account

    Connection String

    Set the Connection string toggle button to automatic to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.

    If the connection string is set to 

    • Auto: jdbc: oracle://{server}:{port};database={sid}
    • Manual: Please enter a valid connection string

    Parameter Files Directory

    It is important to provide the correct path to the parameter file that is used in the mapping. This is because the parameter file contains information that is necessary for the lineage to correctly identify the source and target objects used in the mapping.

    Example: https://ovaledge.s3.us-west-1.amazonaws.com/OvalEdge+Clients+Poc/Documents/Parameters.txt

    Windows Authentication

    Enter the following details to establish an Informatica PowerCenter connection using Windows Authentication.

    Field Name

    Description

    OvalEdge Installed Environment

    Select Windows or Linux/Unix from the OvalEdge Installed Environment drop-down list based on the requirement.

    Server*

    This is the Hostname or IP address of the Informatica PowerCenter Repository Database. 

    Port*

    Enter the port number to connect to the database

    Database*

    Provide the name of the database that is associated with the Informatica PowerCenter connection, if necessary.

    Repository Schema

    The Repository schema contains all metadata related to a mapping, source, or target stored. Enter the name of the repository schema. 

    Driver*

    The driver is defaulted based on the Informatica database selected from the dropdown. By Default is “com.microsoft.sqlserver.jdbc.SQLServerDriver”

    Connection String

    Set the Connection string toggle button to automatic to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.

    If the connection string is set to:

    • Auto: jdbc: oracle://{server}:{port};database={sid}
    • Manual: Please enter a valid connection string

    Parameter Files Directory

    It is important to provide the correct path to the parameter file that is used in the mapping. This is because the parameter file contains information that is necessary for the lineage to correctly identify the source and target objects used in the mapping.

    Example: https://ovaledge.s3.us-west-1.amazonaws.com/OvalEdge+Clients+Poc/Documents/Parameters.txt


    Copyright © 2023, OvalEdge LLC, Peachtree Corners GA USA