INFORMATICA: July 2020

Introduction

Unlike other Transformations in Informatica, we cannot create ports in the Normalizer by simply dragging ports form other Transformation. Whenever we try to copy a port from other Transformation to Normalizer we’ll get a message “Can not copy a column to this Transformation”.

The only way to create Normalizer port is to manually create them with appropriate name and datatype, which is not only a time consuming but also an error prone process if there are huge number of ports to be created.

Now if we have one mapping where we have to create one Normalizer transformation with 100 ports. Normally we would require to create each and every port manually within the Normalizer. This can be time consuming as well as error prone.

Solution

What we try to achieve here is to automatically generate the Normalizer with those ports with correct datatype and length.

We did it by manipulating the xml file of the mapping where we want to create the Normalizer transformation. We will use a dummy Expression transformation (since Expression transformation can be created very easily dragging ports from other transformations) to hold all the necessary ports for the Normalizer and then convert the xml file for the Expression transformation to another xml file which when imported will appear as Normalizer transformation.

We will use a separate reusable mapping which converts the xml of Expression to xml of Normalizer. Below are the steps to follow to create that mapping and then use that to convert Expression to Normalizer.

1. Mapping to convert Expression to Normalizer

To create a mapping that can manipulate mapping xml (having only an Expression transformation), we need to follow the following steps:

a) Creating Source
b) Creating Target
c) Implementing Mapping logic

a) Creating Source

We’ll generate the xml source definition from DTD.

First we’ll paste the following DTD in a text file called Test_Source.dtd and save it in a location accessible to the designer.

<!ELEMENT POWERMART (REPOSITORY*)>
<!ATTLIST POWERMART
CREATION_DATE CDATA #IMPLIED
REPOSITORY_VERSION CDATA #IMPLIED
>
<!ELEMENT REPOSITORY (FOLDER*)>
<!ATTLIST REPOSITORY
NAME CDATA #REQUIRED
DATABASETYPE CDATA #IMPLIED
CODEPAGE CDATA #IMPLIED
VERSION CDATA #IMPLIED
PARENTREPOSITORY CDATA #IMPLIED
>
<!ELEMENT FOLDER (TRANSFORMATION | MAPPING)*>
<!ATTLIST FOLDER
NAME CDATA #REQUIRED
DESCRIPTION CDATA #IMPLIED
SHARED (SHARED | NOTSHARED) #IMPLIED
OWNER CDATA #IMPLIED
GROUP CDATA #IMPLIED
PERMISSIONS CDATA #IMPLIED
UUID CDATA #IMPLIED
>
<!ELEMENT MAPPING (TRANSFORMATION*, INSTANCE*)>
<!ATTLIST MAPPING
NAME CDATA #REQUIRED
DESCRIPTION CDATA #IMPLIED
OBJECTVERSION CDATA #IMPLIED
ISVALID CDATA #IMPLIED
ISPROFILEMAPPING (YES | NO) #IMPLIED
VERSIONNUMBER CDATA #IMPLIED
CRCVALUE CDATA #IMPLIED
>
<!ELEMENT TRANSFORMATION (TRANSFORMFIELD*, TABLEATTRIBUTE*)>
<!ATTLIST TRANSFORMATION
NAME CDATA #REQUIRED
DESCRIPTION CDATA #IMPLIED
TYPE CDATA #REQUIRED
OBJECTVERSION CDATA #IMPLIED
REUSABLE CDATA #IMPLIED
ISVSAM_NORMALIZER CDATA #IMPLIED
REF_SOURCE_NAME CDATA #IMPLIED
REF_DBD_NAME CDATA #IMPLIED
TEMPLATEID CDATA #IMPLIED
TEMPLATENAME CDATA #IMPLIED
PARENT CDATA #IMPLIED
PARENT_TYPE (MAPPING | MAPPLET) #IMPLIED
VERSIONNUMBER CDATA #IMPLIED
COMPONENTVERSION CDATA #IMPLIED
CRCVALUE CDATA #IMPLIED
>
<!ELEMENT INSTANCE (TABLEATTRIBUTE*)>
<!ATTLIST INSTANCE
NAME CDATA #REQUIRED
DESCRIPTION CDATA #IMPLIED
TYPE (SOURCE | TARGET | TRANSFORMATION | MAPPLET) #IMPLIED
REUSABLE (YES|NO) #IMPLIED
TRANSFORMATION_TYPE CDATA #IMPLIED
TRANSFORMATION_NAME CDATA #IMPLIED
DBDNAME CDATA #IMPLIED
ASSOCIATED_DSQ CDATA #IMPLIED
ASSOCIATED_DSQ_TYPE CDATA #IMPLIED
>
<!ELEMENT TRANSFORMFIELD EMPTY>
<!ATTLIST TRANSFORMFIELD
NAME CDATA #REQUIRED
DESCRIPTION CDATA #IMPLIED
DATATYPE CDATA #REQUIRED
PORTTYPE CDATA #REQUIRED
PRECISION CDATA #REQUIRED
SCALE CDATA #IMPLIED
PICTURETEXT CDATA #IMPLIED
DEFAULTVALUE CDATA #IMPLIED
EXPRESSION CDATA #IMPLIED
EXPRESSIONTYPE CDATA #IMPLIED
EXPRESSION_DESCRIPTION CDATA #IMPLIED
FIELDNUMBER CDATA #IMPLIED
REF_SOURCE_FIELD CDATA #IMPLIED
MAPPLETGROUP CDATA #IMPLIED
REF_FIELD CDATA #IMPLIED
REF_TRANSFORMATION CDATA #IMPLIED
REF_INSTANCETYPE CDATA #IMPLIED
SEQUENCE_GENERATOR_VALUE CDATA #IMPLIED
GROUP CDATA #IMPLIED
OUTPUTGROUP CDATA #IMPLIED
ISSORTKEY (YES | NO) #IMPLIED
SORTDIRECTION (ASCENDING | DESCENDING) #IMPLIED
IGNORE_NULL_INPUTS (YES | NO) #IMPLIED
IGNORE_IN_COMPARE (YES | NO) #IMPLIED
>
<!ELEMENT TABLEATTRIBUTE EMPTY>
<!ATTLIST TABLEATTRIBUTE
NAME CDATA #REQUIRED
VALUE CDATA #REQUIRED
>

Next, we’ll go to the source analyzer of the designer and use the option “Import XML Definition” to import the file Test_Source.dtd as a source.

Please note while importing we need to

Set the “Override all infinite lengths with value” as 1000 using the “Advanced Options”

The Xml views need to be set as “Entity Relationships”

b) Creating Target

Like source we’ll also generate the target definition from the following DTD which we’ll save in a text file called Test_Target.dtd in a location accessible to the designer.

Next we’ll import the Test_Target.dtd file in the “Entity Relationships” mode to create the target.

After the target has been imported, we need to edit (not edit xml definition) the target and set the property “DTD Reference” as “powrmart.dtd”.

c) Implementing Mapping Logic

In the mapping (say m_Expression_to_Normalizer), first we’ll connect all the ports of all the groups of xml source qualifier with corresponding port of the corresponding group of target except the ports of the source groups “X_INSTANCE”, “X_TRANSFORMATION” and “X_TRANSFORMFIELD”.

Next we’ll create an Expression Transformation inside the mapping which will take the “TRANSFORMATION_TYPE” field of the source xml group X_INSTANCE as input and pass the hard coded value “Normalizer” to the “TRANSFORMATION_TYPE” field of the target xml group X_INSTANCE.

We’ll connect all the other ports of source group “X_INSTANCE” to the corresponding ports of the target group “X_INSTANCE”.

Similarly, we’ll create another Expression Transformation and connect the source and target group “X_TRANSFORMATION” as follows.

Next we’ll create an Expression Transformation with one input port “DATATYPE” (string: 1000) and two output ports DATATYPE_OUT (string: 1000) and USAGE (string: 10).

We’ll put the following expression to the port DATATYPE_OUT :
DECODE(DATATYPE,'string','string','text','string','date/time','nstring','nstring','nstring','ntext','nstring','number')

Similarly we’ll put the hard coded value “DISPLAY” to the expression of the output port USAGE.
Finally we’ll connect the fields of source xml group X_TRANSFORMFIELD with the fields of target xml group X_SOURCEFIELD as follows.

2. Using the conversion mapping to generate Normalizer

After the Normalizer creation mapping and its corresponding session and workflow have been created, we have to follow the following steps for creating the Normalizer Transformation.

First we’ll create a dummy mapping (say m_Dummy_Transformation) and put only one Expression Transformation (say exp_test) containing the necessary ports which will be required in the Normalizer.

Next we’ll export the mapping into any location accessible from the Informatica session (preferably inside the $PMSourceFileDir).

Next, we’ll specify the newly exported filename and its location as a source in the session corresponding to m_Expression_to_Normalizer created previously. Similarly we’ll specify the desired name and location of the target xml file. And then execute the workflow.
Finally after the completion of the workflow execution, we’ll import the generated target xml file from the Informatica designer.

Though the target xml file generated can be of different name, actually it points to a mapping of same name (m_Dummy_Transformation) internally. So while importing we need to choose the “Replace” option as a conflict resolution or else rename the imported mapping.

In this imported mapping, we will find a normalizer transformation (exp_test) with the same ports, datatype and length as was in the expression. We can now copy this Normalizer to our original mapping where we intend to use it.

Conclusion

The above mapping to convert Expression to Nomalizer works only with the xml of an expression transformation. Similar mapping can be created to convert other transformations to Normalizer too, but since expression is most easily created one, we have taken that as example. Also, creating the expression transformation within a dummy mapping is recommended rather within transformation developer as in that case it will be reusable one and so the Normalizer and hence can’t be edited after copying to our original mapping.

INFORMATICA

Friday, July 24, 2020

How To Automatically Create Normalizer Ports In Informatica

Popular Posts