#informatica tutorials

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.

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.

Q) How to load the data from a flat file into the target where the source flat file name changes daily?

Example: I want to load the customers data into the target file on a daily basis. The source file name is in the format customers_yyyymmdd.dat. How to load the data where the filename varies daily?

The solution to this kind of problems is using the parameters. You can specify session parameters for both the source and target flat files. Then create a parameter file and assign the flat file names to the parameters.

Specifying Parameters for File Names:

The steps involved in parameterizing the file names are:

Creating the Parameter File

Specifying the parameters in Session

Specifying the parameter file name

Creating Parameter File:

Assume two session parameters $InputFileName and $OutputFileName for specifying the source and target flat file names respectively. Now create a parameter file in the below format

> cat dynamic_file_names.param [FolderName.SessionName] $InputFileName=customers_20120101.dat $outputFileName=customers_file.dat

Specifying Parameters in Session:

Now you have to specify the parameters in the session. Edit the session and go to the Mapping tab. In the mapping tab, select the source qualifier in the Sources folder and set the file property “Source FileName” as $InputFileName. Similarly, for target file set the “Source FileName” as $OutputFileName.

Specifying Parameter File Name:

The last step is specifying the parameter file name. You can specify the parameter file name either in the session level or workflow level. To specify in the session level, go the properties tab of the session and set the property “Parameter FileName”.

To specify the parameter file at workflow level, click on the “Worfklows” in toolbar and then on Edit. Now go to the properties and set the file property “Parameter FileName”

Thats it you are done with using the parameters as filenames. Now you have to take care of changing the file name in the parameter file daily.

Aggregator Transformation computes calculations on a group of rows and returns a single row for each group. We can improve the performance of aggregator transformation by sorting the data on the group by ports and then specifying the “Sorted Input” option in the aggregator transformation properties.

The aggregator transformation will throw the below error if you do not sort the data and specified the “Sorted Input” option in the properties of the aggregator transformation:

TE_7073: Expecting Keys to be ascending

This error is due to the data sorting issues in the mapping. This is quite obvious as the aggregator transformation expects the data to be in sorted order and however it gets the data in unsorted order. To avoid this error, simply follow the below steps:

Sort on Group By Ports:

Be sure to sort the data using a sorter transformation or source qualifier transformation before passing to the aggregator transformation.

The order of the ports is important while sorting the data. The order of the ports that you specify in the sorter transformation should be exactly same as the order of the ports specified in “Group By” ports of aggregator transformation. If the order of the ports does not match, then you will get this error.

Trim String Ports:

If you are using the string or varchar ports in the “Group By” of aggregator transformation, then remove the trailing, leading spaces in the expression transformation and then pass to sorter transformation to sort the data.

Avoid Transformations that Change Sorting Order:

We use database procedures to generate the date dimension for data warehouse applications. Here i am going to show you how to generate the date dimension in informatica.

Let see how to generate list out all the days between two given dates using oracle sql query.

SELECT to_date('01-JAN-2000','DD-MON-YYYY') + level-1 calendar_date FROM dual connect by level <= ( to_date('31-DEC-2000','DD-MON-YYYY') - to_date('01-JAN-2000','DD-MON-YYYY') + 1 ); Output: CALENDAR_DATE ------------- 1/1/2000 1/2/2000 1/3/2000 . . . 12/31/2000

Now we can apply date functions on the Calendar date field and can derive the rest of the columns required in a date dimension.

We will see how to get the list of days between two given dates in informatica. Follow the below steps for creating the mapping in informatica.

Create a source with two ports ( Start_Date and End_Date) in the source analyzer.

Create a new mapping in the mapping designer Drag the source definition into the mapping.

Create the java transformation in active mode.

Drag the ports of source qualifier transformation in to the java transformation.

Now edit the java transformation by double clicking on the title bar and go to the “Java Code” tab. Here you will again find sub tabs. Go to the “Import Package” tab and enter the below java code:

import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date;

Not all these packages are required. However i included just in case if you want to apply any formatting on dates. Go to the “On Input Row” tab and enter the following java code:

int num_days = (int) ((End_Date - Start_Date) / (1000 * 60 * 60 * 24)); long Start_Seconds = Start_Date; for (int i=1; i <= num_days ; i++) { if (i == 1) { generateRow(); } else { Start_Date = Start_Date + (1000 * 60 * 60 * 24); generateRow(); } } Start_Date = Start_Date + (1000 * 60 * 60 * 24); generateRow();

Compile the java code by clicking on the compile. This will generate the java class files.

Connect only the Start_Date output port from java transformation to expression transformation.

Connect the Start_Date port from expression transformation to target and save the mapping.

Now create a workflow and session. Enter the following oracle sql query in the Source SQL Query option:

SELECT to_date('01-JAN-2000','DD-MON-YYYY') Start_Date, to_date('31-DEC-2000','DD-MON-YYYY') End_Date FROM DUAL;

Save the workflow and run. Now in the target you can see the list of dates loaded between the two given dates.

1. What is a sorter transformation?

Sorter transformation is used to sort the data. You can sort the data either in ascending or descending order according to a specified sort key.

2. Why sorter is an active transformation?

As sorter transformation can suppress the duplicate records in the source, it is called an active transformation.

3. How to improve the performance of a session using sorter transformation?

Q) How to generate or load values in to the target table based on a column value using informatica etl tool.

I have the products table as the source and the data of the products table is shown below.

Table Name: Products Product Quantity ----------------- Samsung NULL Iphone 3 LG 0 Nokia 4

Now i want to duplicate or repeat each product in the source table as many times as the value in the quantity column. The output is

product Quantity ---------------- Iphone 3 Iphone 3 Iphone 3 Nokia 4 Nokia 4 Nokia 4 Nokia 4

The Samsung and LG products should not be loaded as their quantity is NULL, 0 respectively.

Now create informatica workflow to load the data in to the target table?

Solution:

Follow the below steps

Create a new mapping in the mapping designer

Drag the source definition in to the mapping

Create the java transformation in active mode

Drag the ports of source qualifier transformation in to the java transformation.

Now edit the java transformation by double clicking on the title bar of the java transformation and go to the “Java Code” tab.

Enter the below java code in the “Java Code” tab.

if (!isNull("quantity")) { double cnt = quantity; for (int i = 1; i <= quantity; i++) { product = product; quantity = quantity; generateRow(); } }

Now compile the java code. The compile button is shown in red circle in the image.

Connect the ports of the java transformation to the target.

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.

First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don’t check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).