Before and after
One PowerCenter mapping and the Hop pipeline the importer made from it, without any edit. The mapping reads an employee's current earnings for a pay period, where each row holds up to three earnings (name, hours and amount), turns them into one row per earning, drops the blank ones, numbers the rest, and loads them into a table.
The mapping is m_LES_Load_LES_EMP_DETAIL_CURR_EARN_TBL, from the US Department of Health and
Human Services' PowerCenter estate, which HHS published in the public domain. It is the mapping
Get started walks through.
The PowerCenter mapping
Drawn from the LES repository export, the file the importer reads: the transformations, their
ports and the port-to-port connectors are exactly what the export holds. A repository export has
no canvas positions, so the layout is ours. It is not a screenshot of PowerCenter Designer.
The Hop pipeline
Drawn by Apache Hop from the generated m_LES_Load_LES_EMP_DETAIL_CURR_EARN_TBL.hpl: this is what
you see when you open it in Hop GUI.
Transformation by transformation
| PowerCenter | Hop | What carried across |
|---|---|---|
LES_EMP_DETAIL_RECTYPE_C_TBL (Source Definition) and SQ_… (Source Qualifier) | Table input | The SQL override, on the connection INFA_SOURCE_DB. The mapping parameters $$MAP_PP_END_YEAR and $$MAP_PP_NUM become the Hop variables ${MAP_PP_END_YEAR} and ${MAP_PP_NUM} |
exp_Initial (Expression) | User Defined Java Class | Every port passes through, converted to the port's PowerCenter datatype |
nrm_EMP_CURR_EARN (Normalizer) | Row normaliser | The three sets of CURR_EARN_NAME…, …_HOURS and …_AMT ports become three rows with CURR_EARN_NAME, CURR_EARN_HOURS and CURR_EARN_AMT |
fil_Remove_Blank_Current_Earnings (Filter) | User Defined Java Class | The condition NOT ISNULL(CURR_EARN_NAME) AND LTRIM(RTRIM(CURR_EARN_NAME)) != ''. It uses functions Hop's Filter rows cannot express, so it becomes a Java filter |
exp_Convert (Expression) | User Defined Java Class | The variable port v_CURR_EARN_ID = IIF(v_CURR_EARN_ID = 0, 1, v_CURR_EARN_ID + 1) and the output port that returns it. The variable keeps its value from row to row, as in PowerCenter, so the rows are numbered 1, 2, 3, … |
exp_Final (Expression) | User Defined Java Class | Every port passes through; o_CURR_EARN_ID comes in as its port CURR_EARN_ID |
LES_EMP_DETAIL_CURR_EARN_TBL (Target Definition) | Table output | The target table on the connection INFA_TARGET_DB, writing the eight connected ports to their columns |
The running number in exp_Convert looks like this in the generated class. Each statement has the
Informatica expression it came from above it:
// IIF(v_CURR_EARN_ID = 0,
// 1,
// v_CURR_EARN_ID + 1)
var_v_CURR_EARN_ID = Infa.iif(Infa.eq(var_v_CURR_EARN_ID, new java.math.BigDecimal("0")),
new java.math.BigDecimal("1"), Infa.add(var_v_CURR_EARN_ID, new java.math.BigDecimal("1")));
Infa is a small runtime the importer writes into every class: PowerCenter's functions, with its
rules for NULL, numbers and dates.
PowerCenter connects ports; Hop connects transforms and passes every field along. The 71
port-to-port connectors become six hops, and the fields arrive by name. Where a connector joins two
ports with different names (o_CURR_EARN_ID into exp_Final's CURR_EARN_ID, PP_END_YEAR into
the Normalizer's PP_END_YEAR_in), the Hop transform reads the upstream field and carries on with
the name of its own port, as PowerCenter does.
Does it do the same thing?
The importer's test PowerCenterCorpusExecutionTest rebuilds this estate's tables in an in-memory
database, writes a few rows into each source and runs the generated pipeline unmodified: this
one runs to the end. That shows the pipeline is complete and its expressions run. It does not show
that it writes the rows PowerCenter would: that takes the real data and a
parallel run.
What this example does not show
This mapping has no placeholder. In the HHS estate, six mappings keep one, all of them for PowerExchange targets. The report flags every Expression translated to Java, and every Lookup, Filter, Joiner and Aggregator, for a person to check. Get started shows the report for the whole estate, and the importer's current limits list what still needs a person.