Normalizer Transformation Error - Informatica

Normalizer transformation is used to convert the data in multiple columns into different rows. Basically the normalizer transformation converts the denormalized data in a table in to a normalized table.

Normalizer Transformation Error

Getting the following Error for the Normalizer transformation in mapping when pivoting the columns in to Rows

TT_11054 Normalizer Transformation: Initialization Error: [Cannot match OFOSid with IFOTid.]

How to fix the Normalizer Transformation Error?

Solution:

Follow the below steps to avoid this error.

1. There should be no unconnected input ports to the Normalizer transformation.
2. If the Normalizer has an OCCURS in it, make sure number of input ports matches the number of OCCURS.

Creating a Non Reusable Object from Reusable Object

Q) How to create a non-reusable transformation or session or task from a reusable transformation or session or task?

I still remember my first project in which i created so many reusable transformations and developed a mapping. My project lead reviewed the code and told me that you created unnecessary reusable transformation change them to non reusable transformations. I created non reusable transformations and re-implemented the entire logic. It took almost one day for me to implement the code. Still so many new informatica developers will do the same mistake and re implement the entire logic.
I found an easy way to create a non-reusable transformation from a reusable transformation. Follow the below steps to create a non-reusable transformation or session or task from a reusable transformation or session or task in informatica is

1. Go to the Navigator which is on the left side.
2. Select the reusable transformation or session or task which you want to convert to non resuable with the mouse.
3. Drag the object (transformation/session/task) to the work-space and just before leaving the object on the work-space hold the ctrl key and then release the object.

Now you are done with creating a non-reusable transformation or session or task.

Incremental Aggregation in Informatica

Incremental Aggregation is the process of capturing the changes in the source and calculating the aggregations in a session. This process makes the integration service to update the target incrementally and avoids the process of calculating the aggregations on the entire source. Consider the below sales table as an example and see how the incremental aggregation works.

Source:

YEAR PRICE
----------
2010 100
2010 200
2010 300
2011 500
2011 600
2012 700

For simplicity, I have used only the year and price columns of sales table. We need to do aggregation and find the total price in each year.

When you run the session for the first time using the incremental aggregation, then integration service process the entire source and stores the data in two file, index and data file. The integration service creates the files in the cache directory specified in the aggregator transformation properties.
After the aggregation, the target table will have the below data.

Target:

YEAR PRICE
----------
2010 600
2011 1100
2012 700

Now assume that the next day few more rows are added into the source table.

Source:

YEAR PRICE
----------
2010 100
2010 200
2010 300
2011 500
2011 600
2012 700

2010 400
2011 100
2012 200
2013 800

Now for the second run, you have to pass only the new data changes to the incremental aggregation. So, the source will contain the last four records. The incremental aggregation uses the data stored in the cache and calculates the aggregation. Once the aggregation is done, the integration service writes the changes to the target and the cache. The target table will contains the below data.

Target:

YEAR PRICE
----------
2010 1000
2011 1200
2012 900
2013 800

Points to remember

1. When you use incremental aggregation, first time you have to run the session with complete source data and in the subsequent runs you have to pass only the changes in the source data.
2. Use incremental aggregation only if the target is not going to change significantly. If the incremental aggregation process changes more than hhalf of the data in target, then the session perfromance many not benfit. In this case go for normal aggregation.

Note: The integration service creates a new aggregate cache when

• A new version of mapping is saved
• Configure the session to reinitialize the aggregate cache
• Moving or deleting the aggregate files
• Decreasing the number of partitions

Configuring the mapping for incremental aggregation

Before enabling the incremental aggregation option, make sure that you capture the changes in the source data. You can use lookup transformation or stored procedure transformation to remove the data which is already processed. You can also create a trigger on the source database and can read only the source changes in the mapping.

Constraint Based Loading in Informatica

Constraint based load ordering is used to load the data first in to a parent table and then in to the child tables. You can specify the constraint based load ordering option in the Config Object tab of the session. When the constraint based load ordering option is checked, the integration service order the target load order on a row by row basis.

For every row generated by the active source, the integration service first loads the row into the primary key table and then to the foreign key tables. The constraint based loading is helpful to normalize the data from a denormalized source data.

• The constraint based load ordering option applies for only insert operations.
• You cannot update or delete the rows using the constraint base load ordering.
• You have to define the primary key and foreign key relationships for the targets in the warehouse or target designer.
• The target tables must be in the same Target connection group.

Complete Constraint based load ordering

There is a work around to do updates and deletes using the constraint based load ordering. The informatica powercenter provides an option called complete constraint-based loading for inserts, updates and deletes in the target tables. To enable complete constraint based loading, specify FullCBLOSupport=Yes in the Custom Properties attribute on the Config Object tab of session. This is shown in the below image.

When you enable complete constraint based loading, the change data (inserts, updates and deletes) is loaded in the same transaction control unit by using the row ID assigned to the data by the CDC reader. As a result the data is applied to the target in the same order in which it was applied to the sources.

You can also set this property in the integration service, which makes it applicable for all the sessions and workflows. When you use complete constraint based load ordering, mapping should not contain active transformations which change the row ID generated by the CDC reader.

The following transformations can change the row ID value

• Aggregator Transformation
• Custom Transformation configured as an active
• Joiner Transformation
• Normalizer Transformation
• Rank Transformation
• Sorter Transformation

Mapping Implementation of constraint based load ordering

As an example, consider the following source table with data to be loaded into the target tables using the custom transformation.

Table Name: EMP_DEPT

Create table emp_dept
(
 dept_id   number,
 dept_name varchar2(30),
 emp_id    number,
 emp_name  varchar2(30)
);

dept_id dept_name emp_id emp_name
---------------------------------
10      Finance   1      Mark
10      Finance   2      Henry
20      Hr        3      Christy
20      Hr        4      Tailor

The target tables should contain the below data.

Target Table 1: Dept

Create table dept
(
 dept_id   number primary key,
 dept_name varchar2(30)
);

dept_id dept_name
-----------------
10      Finance
20      Hr

Target Table 2: Emp

create table emp
(
 dept_id  number,
 emp_id   number,
 emp_name varchar2(30),
 foreign key (dept_id) references dept(dept_id)
);

dept_id emp_id emp_name
---------------------------------
10      1      Mark
10      2      Henry
20      3      Christy
20      4      Tailor

Follow the below steps for creating the mapping using constraint based load ordering option.

• Create the source and target tables in the oracle database
• Go to the mapping designer, source analyzer and import the source definition from the oracle database.
• Now go to the warehouse designer or target designer and import the target definitions from the oracle database.
• Make sure that the foreign key relationship exists between the dept and emp targets. Otherwise create the relationship as shown in the below images.

• Now create a new mapping. Drag the source and targets into the mapping.
• Connect the appropriate ports of source qualifier transformation to the target definition as shown in the below image.

• Go to the workflow manager tool, create a new workflow and then session.
• Go to the Config object tab of session and check the option of constraint based load ordering.
• Go to the mapping tab and enter the connections for source and targets.
• Save the mapping and run the workflow.

Load Source File Name in Target - Informatica

Q) How to load the name of the current processing flat file along with the data into the target using informatica mapping?

We will create a simple pass through mapping to load the data and "file name" from a flat file into the target. Assume that we have a source file "customers" and want to load this data into the target "customers_tgt". The structures of source and target are

Source file name: customers.dat
Customer_Id
Location

Target: Customers_TBL
Customer_Id
Location
FileName

The steps involved are:

• Login to the powercenter mapping designer and go to the source analyzer.
• You can create the flat file or import the flat file.
• Once you created a flat file, edit the source and go to the properties tab. Check the option "Add Currently Processed Flat File Name Port". This option is shown in the below image.

• A new port, "CurrentlyProcessedFileName" is created in the ports tab.
• Now go to the Target Designer or Warehouse Designer and create or import the target definition. Create a "Filename" port in the target.
• Go to the Mapping designer tab and create new mapping.
• Drag the source and target into the mapping. Connect the appropriate ports of source qualifier transformation to the target.
• Now create a workflow and session. Edit the session and enter the appropriate values for source and target connections.
• The mapping flow is shown in the below image

The loading of the filename works for both Direct and Indirect Source filetype. After running the workflow, the data and the filename will be loaded in to the target. The important point to note is the complete path of the file will be loaded into the target. This means that the directory path and the filename will be loaded(example: /informatica/9.1/SrcFiles/Customers.dat).

If you don’t want the directory path and just want the filename to be loaded in to the target, then follow the below steps:

• Create an expression transformation and drag the ports of source qualifier transformation into it.
• Edit the expression transformation, go to the ports tab, create an output port and assign the below expression to it.

REVERSE
(
  SUBSTR
  (
     REVERSE(CurrentlyProcessedFileName),
     1,
     INSTR(REVERSE(CurrentlyProcessedFileName), '/') - 1
  )
)

• Now connect the appropriate ports of expression transformation to the target definition.

Mapping Variable Usage Example in Informatica

The variables in informatica can be used to store intermediate values and can be used in calculations. We will see how to use the mapping variables with an example.

Q) I want to load the data from a flat file into a target. The flat file has n number of records. How the load should happen is: In the first run i want to load the first 50 records, in the second run the next 20 records, in the third run, the next 20 records and so on?

We will solve this problem with the help of mapping variables. Follow the below steps to implement this logic:

• Login to the mapping designer. Create a new mapping.
• Create a mapping variable. call it as $$Rec_Var.
• Drag the flat file source into the mapping.
• Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.
• In the expression transformtion, create the below ports.

variable port: v_cnt = v_cnt+1
output port:   o_cnt = v_cnt
variable port  v_num_rec = IIF($$Rec_Var is null OR $$Rec_Var=0 , 50, 20)
output port   o_check_rec = SETVARIABLE($$Rec_Var,v_num_rec+$$Rec_Var)

• Now create a filter transformtion and drag the ports of expression transformation into it. In the filter transformation specfiy the contition as

IIF(v_check_rec=50, 
  IIF(o_cnt <= o_check_rec,TRUE,FALSE),
  IIF(o_cnt<=o_check_rec AND o_cnt>o_ceck_rec-20,TRUE,FALSE)
)

• Drag the target definition into the mapping and connect the appropriate ports of filter transformation to the target.
• Create a workflow and run the workflow multiple times to see the effect.

Target Load Order/ Target Load Plan in Informatica

Target Load Order:

Target load order (or) Target load plan is used to specify the order in which the integration service loads the targets. You can specify a target load order based on the source qualifier transformations in a mapping. If you have multiple source qualifier transformations connected to multiple targets, you can specify the order in which the integration service loads the data into the targets.

Target Load Order Group:

A target load order group is the collection of source qualifiers, transformations and targets linked in a mapping. The integration service reads the target load order group concurrently and it processes the target load order group sequentially. The following figure shows the two target load order groups in a single mapping:

Use of Target Load Order:

Target load order will be useful when the data of one target depends on the data of another target. For example, the employees table data depends on the departments data because of the primary-key and foreign-key relationship. So, the departments table should be loaded first and then the employees table. Target load order is useful when you want to maintain referential integrity when inserting, deleting or updating tables that have the primary key and foreign key constraints.

Target Load Order Setting:

You can set the target load order or plan in the mapping designer. Follow the below steps to configure the target load order:

1. Login to the powercenter designer and create a mapping that contains multiple target load order groups.
2. Click on the Mappings in the toolbar and then on Target Load Plan. The following dialog box will pop up listing all the source qualifier transformations in the mapping and the targets that receive data from each source qualifier.

3. Select a source qualifier from the list.
4. Click the Up and Down buttons to move the source qualifier within the load order.
5. Repeat steps 3 and 4 for other source qualifiers you want to reorder.
6. Click OK.