Skip to main content

Get started with the Informatica importer

Coming soon

The Informatica importer is about to be released as open source (Apache License 2.0). Until then, we run it for you: contact us.

In this tutorial you install the importer in Apache Hop, import a real PowerCenter estate of 108 mappings, and learn to read the migration report: which mappings converted, which ones need work, and where in each pipeline the work is. It takes about twenty minutes.

The estate is the US Department of Health and Human Services' HR and payroll mappings, which HHS published on GitHub in the public domain (The Unlicense). Nothing you need comes from Informatica.

Before you start​

  • Apache Hop 2.19.0 or later. See Install Apache Hop.
  • Java 21, which Hop 2.19 needs anyway.
  • git.

In the commands below, <hop> is the folder Hop is installed in.

1. Install the plugin​

Download importer-informatica-<version>.zip from the importer's releases, once published, and unzip it into the Hop folder:

unzip importer-informatica-<version>.zip -d <hop>

Start Hop GUI (or restart it). The File menu now has Import from Informatica….

2. Get the PowerCenter estate​

git clone https://github.com/HHS/Informatica.git hhs

hhs/XML/ holds 11 repository exports, one per PowerCenter folder, named after the folder and without a file extension. The importer reads them as they are.

3. Import it​

<hop>/hop-import.sh -t informatica -i hhs/XML -o informatica-to-hop

Expected result: the log reports Found 108 PowerCenter mapping(s) and 11 workflow(s) in 11 XML file(s), and informatica-to-hop/ holds 108 pipelines in pipelines/, 11 workflows in workflows/, informatica-migration-report.md and conversion-metadata.json.

4. Read the report​

Open informatica-to-hop/informatica-migration-report.md. It has two kinds of figure, and they answer different questions.

Status counts say what each transformation became in the generated pipeline. With importer 0.1.0:

MetricCount
Jobs119
Components1196
AUTO1012
PARTIAL151
MANUAL33
UNSUPPORTED0
review-required184

The jobs are the 108 mappings and the 11 workflows; the components are their transformations and tasks. They include the targets: a repository export defines them at folder level, and each target instance in a mapping counts as a transformation of its own. They also include the transformations inside Mapplets, once per Mapplet instance: the importer expands each instance into the mapping.

Structural functionality says whether each pipeline holds together as converted: here 28 functional, 80 need review and 0 non-functional. For a non-functional pipeline, the report lists the transforms that break it and why.

"Need review" is by design: every Lookup, Filter, Joiner and Aggregator, and every Expression the importer translated to Java, is flagged for a person to check, however well it converted. One of them is enough to put a pipeline under review.

Holding together is not the same as running. The importer's own test suite rebuilds this estate's tables in an in-memory database, puts five rows in each source and runs every mapping it can: 98 of the 102 it can set up run to the end. The others stop on Oracle SQL that the in-memory database does not accept, or on test strings put into numeric columns. The Components table at the end of the report lists every transformation of every mapping with its status and the Hop transform it became.

5. Look at a converted mapping​

Open informatica-to-hop/pipelines/m_LES_Load_LES_EMP_DETAIL_CURR_EARN_TBL.hpl in Hop GUI. It loads current earnings into LES_EMP_DETAIL_CURR_EARN_TBL.

  • SQ_LES_EMP_DETAIL_RECTYPE_C_TBL is a Table input with the Source Qualifier's SQL override. Its mapping parameters, $$MAP_PP_END_YEAR and $$MAP_PP_NUM, are now the Hop variables ${MAP_PP_END_YEAR} and ${MAP_PP_NUM}: set them in your environment. Where a Source Qualifier has no override, the importer writes the query PowerCenter builds instead, from the connected ports, the source tables, the User Defined Join and the Source Filter.

  • nrm_EMP_CURR_EARN is a Row normaliser: it turns the three sets of earnings columns of each row (name, hours, amount) into three rows, as the Normalizer does.

  • exp_Initial, exp_Convert and exp_Final are User Defined Java Class transforms. Open one: each output port is one Java statement, with the Informatica expression it came from as a comment above it.

  • fil_Remove_Blank_Current_Earnings is also Java, because its condition uses functions Filter rows cannot express:

    // NOT ISNULL(CURR_EARN_NAME) AND
    // LTRIM(RTRIM(CURR_EARN_NAME)) != ''
    if (Infa.isTrue(Infa.and(Infa.not(Infa.isnull(in_CURR_EARN_NAME)),
        Infa.ne(Infa.ltrim(Infa.rtrim(in_CURR_EARN_NAME, null), null), "")))) {
      putRow(data.outputRowMeta, r);
    }

    Infa is the runtime the importer puts in every class: PowerCenter's functions, with its NULL rules.

  • LES_EMP_DETAIL_CURR_EARN_TBL is a Table output that writes only the ports connected to it, each to its own column.

Set the two variables and a connection, and the pipeline runs. What is left is checking that it computes what PowerCenter did: see Validate a migration.

These figures describe this estate, US federal HR and payroll, and this version of the importer. Your export will have its own; see Assess an Informatica export.

Next​