Informatica Online Training - Quontra Solutions

[informaticatrainingclasses-blog]

Steps to install Informatica Power Center Server 8.6 - QuontraSolutions

Steps to install Informatica Power Center Server 8.6

Pre-requisites:

1)Window xp sp2 ( You can also install the same on any high end OS ). 2)At least one database(here we assume you to have Oracle XE pre installed ). 3)2 GB Ram Recommended Informatica 8.6 Server Installation: 1) Select the sever folder from the informatica installation CD. 2)There will find set up, click on the set up file.

3)click on Next button.

 4)Select the license key by using browse button.It will be available the fallowing location:…\Informatica 8.6\RAWANA

5)Click on Next button

  6)Click on Next button.

 7)Select complete radio button and click on Next button.

8)Leave the defaults and select next button.

9)Click on Install button

10) Choose create new domain radio button, and click on Next button.

Select database type

Database URL: ComputerName:portno

Eg:windows-4dc4631:1521

To find the computer name.. Click on Start->Right click on My computer->Properties->Computer Name.

Provide the database credentials along with the Service name.

Find database service name:

Control Panel–Administrative tools–Services

Here XE is the service name.

For Oracle 10g users you will find the service name like :OracleServiceORCL then service name would be ORCL.

Note:SQL Server users they can give the name of the server name in the above field instead of service name.

Click on Test connection:If this succeed then it will take you to the further step.

11. Leave the defaults, enter password and confirm password and click on next button.

12.Uncheck the check box and click on next button.

13. Click on Done.

14.After finishing installation need to run the services

Run the Informatica services:

windows+r->services.msc-> Locate the Informatica services –> Start them.

Configure the repository service and Integration service:

To create these two above, please follow the below steps.

Give your Credentials

Select administrative Console. Make sure Domain is running.

Make sure node is also running.From create drop down select repository service.

1)Enter any service name.

2)Select License.

3)Select database type as oracle (if your sys contains oracle db)

4)Select node for which you want to create repository.

5)Leave the code page as default.

6)Connection string if ORCL for oracle users(Make sure connection string should be same as the service name what is given while installing server)

7)Enter Database Credentials

8)Leave other defaults as well.

9)Click on create button.

10)Enable the repository service.

Now Create content for the repository by selecting drop down shown below.

Note:Make sure Processes are enabled and repository service is enabled.

Creation of Integration service:

1)Give any name for Integration sevice.

2)Assign node to the IS.

3)Assign Repository to the IS.

4)Enter the rep.username and password.

Note:make this username and password should same as username and password of the Repository service.

5.Select data movement mode as ASCII.

In the process tab of the IS, enable the process.

Before enable, edit this section and select “Code page” as shown above.

Now Install client.

Open Informatica Repository Manager.

Select the RS from the RHS pane and click on OK.

Connect to the repository service.

Enter the repository username and password.

[informaticatrainingclasses-blog]

Mapping Variable Usage Example in Informatica - QuontraSolutions

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.

[informaticatrainingclasses-blog]

Target Load Order/ Target Load Plan in Informatica - Quontra Solutions

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.

[informaticatrainingclasses-blog]

Parameterizing the Flat File Names – Informatica

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.

Note: You can even specify the source and target directories as parameters.

[informaticatrainingclasses-blog]

TE_7073: Expecting Keys to be ascending – Informatica

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:

Do not place transformations which change the sorting order before the aggregator transformation.

[informaticatrainingclasses-blog]

Generate Date / Time Dimension in Informatica

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.

Note1: I have used relational table as my source. You can use a flat file instead. Note2: In the expression transformation, create the additional output ports and apply date functions on the Start_Date to derive the data required for date dimension.

[informaticatrainingclasses-blog]

Informatica Interview Questions on Sorter Transformation – Quontra Solutions

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?

Sort the data using sorter transformation before passing in to aggregator or joiner transformation. As the data is sorted, the integration service uses the memory to do aggregate and join operations and does not use cache files to process the data.

[informaticatrainingclasses-blog]

Generate rows based on a column value – Informatica

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.

Save the mapping, create a workflow and run the workflow.

[informaticatrainingclasses-blog]

Flat file header row, footer row and detail rows to multiple tables

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).

Now pass the data from expression and aggregator to joiner transformation. In the joiner select the ports from aggregator as master and the ports from expression as details. Give the join condition on the count ports and select the join type as master outer join. Pass the joiner output to a router transformation and create two groups in the router. For the first group give the condtion as exp_count=1, which gives header row. For the second group give the condition as exp_count=agg_count, which gives the footer row. The default group will give the detail rows.

[informaticatrainingclasses-blog]

Reverse the Contents of Flat File – Informatica

Q1) I have a flat file, want to reverse the contents of the flat file which means the first record should come as last record and last record should come as first record and load into the target file.

As an example consider the source flat file data as

Informatica Enterprise Solution Informatica Power center Informatica Power exchange Informatica Data quality

The target flat file data should look as

Informatica Data quality Informatica Power exchange Informatica Power center Informatica Enterprise Solution

Solution:

Follow the below steps for creating the mapping logic

Create a new mapping.

Drag the flat file source into the mapping.

Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.

Create the below additional ports in the expression transformation and assign the corresponding expressions

Variable port: v_count = v_count+1 Output port o_count = v_count

Now create a sorter transformation and drag the ports of expression transformation into it.

In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.

Drag the target definition into the mapping and connect the ports of sorter transformation to the target.

Q2) Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.

The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.

Solution:

Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.

Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.

Sort the data in descending order on o_count port.

Connect the output of sorter transformation to expression transformation (don’t connect o_count port).

Again in the expression transformation create the same additional ports mentioned above.

Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.

[informaticatrainingclasses-blog]

Dynamic Target Flat File Name Generation in Informatica

Informatica 8.x or later versions provides a feature for generating the target files dynamically. This feature allows you to

Create a new file for every session run

create a new file for each transaction.

Informatica provides a special port,”FileName” in the Target file definition. This port you have to add explicitly. See the below diagram for adding the “FileName” port.

Go to the Target Designer or Warehouse builder and edit the file definition. You have to click on the button indicated in red color circle to add the special port.

Now we will see some informatica mapping examples for creating the target file name dynamically and load the data.

1. Generate a new file for every session run.

Whenever the session runs you need to create a new file dynamically and load the source data into that file. To do this just follow the below steps:

STEP1: Connect the source qualifier to an expression transformation. In the expression transformation create an output port (call it as File_Name) and assign the expression as ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDDHH24MISS’)||’.dat’

STPE2: Now connect the expression transformation to the target and connect eh File_Name port of expression transformation to the FileName port of the target file definition.

STEP3: Create a workflow and run the workflow.

Here I have used sessstarttime, as it is constant throughout the session run. If you have used sysdate, a new file will be created whenever a new transaction occurs in the session run.

The target file names created would look like EMP_20120101125040.dat.

2. Create a new file for every session run. The file name should contain suffix as numbers (EMP_n.dat)

In the above mapping scenario, the target flat file name contains the suffix as ‘timestamp.dat’. Here we have to create the suffix as a number. So, the file names should looks as EMP_1.dat, EMP_2.dat and so on. Follow the below steps:

STPE1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer

STPE2: Connect the source Qualifier to the expression transformation. In the expression transformation create the following new ports and assign the expressions.

v_count (variable port) = v_count+1 v_file_count (variable port) = IIF(v_count = 1, SETVARIABLE($$COUNT_VAR,$$COUNT_VAR+1),$$COUNT_VAR) o_file_name (output port) = 'EMP_'||v_file_count||'.dat'

STEP3: Now connect the expression transformation to the target and connect the o_file_name port of expression transformation to the FileName port of the target.

3. Create a new file once a day.

You can create a new file only once in a day and can run the session multiple times in the day to load the data. You can either overwrite the file or append the new data.

This is similar to the first problem. Just change the expression in expression transformation to ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDD’)||’.dat’. To avoid overwriting the file, use Append If Exists option in the session properties.

4. Create a flat file based on the values in a port.

You can create a new file for each distinct values in a port. As an example consider the employees table as the source. I want to create a file for each department id and load the appropriate data into the files.

STEP1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.

STEP2: Connect to the expression transformation. In the expression transformation create the below ports and assign expressions.

v_curr_dept_id (variable port) = dept_id v_flag (variable port) = IIF(v_curr_dept_id=v_prev_dept_id,0,1) v_prev_dept_id (variable port) = dept_id o_flag (output port) = v_flag o_file_name (output port) = dept_id||'.dat'

STEP4: Now connect the expression transformation to the transaction control transformation and specify the transaction control condition as

IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

STEP5: Now connect to the target file definition.

[informaticatrainingclasses-blog]

Informatica Real Time Scenarios – Solutions

Informatica Real Time Scenarios:

Q1) Alternate Target Loading

My source is a flat file which contains N number of records. I want to load the source data into two targets such that first five records should loaded into the first target, next five records into the second target table. Again the next source five records into the first target table and so on. How to implement a Informatica mapping logic for this?

Solution:

Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports:

v_cnt (variable port) = v_cnt+1 o_cnt (output port) = v_cnt

Connect the expression transformation to the router transformation. Create two output groups in the router transformation and specify the following filter conditions:

--Filter condition for first output group DECODE(substr(o_cnt,-1,1),1,TRUE,2,TRUE,3,TRUE,4,TRUE,5,TRUE,FALSE) --Filter condition for second output group DECODE(substr(o_cnt,-1,1),6,TRUE,7,TRUE,8,TRUE,9,TRUE,0,TRUE,FALSE)

Connect the router transformation output groups to the appropriate targets.

Q2) Load source data in multiple session run.

I have flat file as a source which contains N number of records. My requirement is to load half of the source data into the target table in the first session run and the remaining half of the records in the second session run. Create Informatica mapping to implement this logic? Assume that the source data does not change between session runs.

Solution:

Create a mapping to find out the number of records in the source and write the count to a parameter file. Let call this parameter as $$SOURCE_COUNT.

Create another mapping. Go to the mapping parameters and variables, create a mapping variable ($$VAR_SESSION_RUNS) with integer data type.

Connect the source qualifier transformation to the expression transformation. In the expression transformation, create the below additional ports.

v_Count (variable port) = v_Count+1 O_Run_flag (output port) = IIF($$vAR_SESSION_RUNS=0, setvariable($$vAR_SESSION_RUNS,1), IIF( !ISNULL($$vAR_SESSION_RUNS) and v_Count=1, 2, $$vAR_SESSION_RUNS) ) O_count (output port) = V_Count

Connect the expression transformation to the filter transformation and specify the following filter condition:

IIF (O_Run_Flag =1, v_count<= $$SOURCE_COUNT/2, IIF (O_Run_Flag =2, v_count > $$SOURCE_COUNT/2))

Connect the filter transformation to the target.

Here i am assuming that you know how to use a parameter file. That is why I did not specify the complete details.

[informaticatrainingclasses-blog]

Informatica Problems With Solutions – Part 1

1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica.

Consider the below sales table as an example?

Table name: Sales

product, prod_quantity, price , Year A         , 10                 , 100  , 2010 B         , 15                 , 150  , 2010 A         , 8                   , 80    , 2011 B         , 26                 , 260  , 2011

Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year.

Here in this example, Product A sold less in 2011 when compared with the sales in 2010.

This problem can be easily implemented with the help of SQL query as shown below

SELECT  cy.* FROM    SALES cy, SALES py WHERE   cy.product = py.product AND        cy.year=2011 AND        py.year=2010 AND       cy.prod_quantity < py.prod_quantity;

In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica.

Solution:

STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy  (cy means current year) and the other as sq_py  (py means previous year).

STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010

STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail.

STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port.

STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition.

2. How to implement the not exists operator in informatica which is available in database?

Solution:

Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table.

Connect the joiner to a filter transformation and specify the filter condition as B_port is NULL. This will give the records which are in A and not in B. Then connect the filter to the target definition.

[informaticatrainingclasses-blog]

Informatica Scenario Based Questions – Part 2

1. Consider the following employees data as source

employee_id, salary 10, 1000 20, 2000 30, 3000 40, 5000

Q1. Design a mapping to load the cumulative sum of salaries of employees into target table? The target table data should look like as

employee_id, salary, cumulative_sum 10, 1000, 1000 20, 2000, 3000 30, 3000, 6000 40, 5000, 11000

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_cum_sal and in the expression editor write V_cum_sal+salary. Create an output port O_cum_sal and assign V_cum_sal to it.

Q2. Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null. The output should look like as

employee_id, salary, pre_row_salary 10, 1000, Null 20, 2000, 1000 30, 3000, 2000 40, 5000, 3000

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_count and increment it by one for each row entering the expression transformation. Also create V_salary variable port and assign the expression IIF(V_count=1,NULL,V_prev_salary) to it . Then create one more variable port V_prev_salary and assign Salary to it. Now create output port O_prev_salary and assign V_salary to it. Connect the expression transformation to the target ports.

In the expression transformation, the ports will be

employee_id salary V_count=V_count+1 V_salary=IIF(V_count=1,NULL,V_prev_salary) V_prev_salary=salary O_prev_salary=V_salary

Q3. Design a mapping to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null. The output should look like as

employee_id, salary, next_row_salary 10, 1000, 2000 20, 2000, 3000 30, 3000, 5000 40, 5000, Null

Solution:

Step1: Connect the source qualifier to two expression transformation. In each expression transformation, create a variable port V_count and in the expression editor write V_count+1. Now create an output port O_count in each expression transformation. In the first expression transformation, assign V_count to O_count. In the second expression transformation assign V_count-1 to O_count.

In the first expression transformation, the ports will be

employee_id salary V_count=V_count+1 O_count=V_count

In the second expression transformation, the ports will be

employee_id salary V_count=V_count+1 O_count=V_count-1

Step2: Connect both the expression transformations to joiner transformation and join them on the port O_count. Consider the first expression transformation as Master and second one as detail. In the joiner specify the join type as Detail Outer Join. In the joiner transformation check the property sorted input, then only you can connect both expression transformations to joiner transformation.

Step3: Pass the output of joiner transformation to a target table. From the joiner, connect the employee_id, salary which are obtained from the first expression transformation to the employee_id, salary ports in target table. Then from the joiner, connect the salary which is obtained from the second expression transformaiton to the next_row_salary port in the target table.

Q4. Design a mapping to find the sum of salaries of all employees and this sum should repeat for all the rows. The output should look like as

employee_id, salary, salary_sum 10, 1000, 11000 20, 2000, 11000 30, 3000, 11000 40, 5000, 11000

Solution:

Step1: Connect the source qualifier to the expression transformation. In the expression transformation, create a dummy port and assign value 1 to it.

In the expression transformation, the ports will be

employee_id salary O_dummy=1

Step2: Pass the output of expression transformation to aggregator. Create a new port O_sum_salary and in the expression editor write SUM(salary). Do not specify group by on any port.

In the aggregator transformation, the ports will be

salary O_dummy O_sum_salary=SUM(salary)

Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

Step4: Pass the output of joiner to the target table.

2. Consider the following employees table as source

department_no, employee_name 20, R 10, A 10, D 20, P 10, B 10, C 20, Q 20, S

Q1. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list 10, A 10, A,B 10, A,B,C 10, A,B,C,D 20, A,B,C,D,P 20, A,B,C,D,P,Q 20, A,B,C,D,P,Q,R 20, A,B,C,D,P,Q,R,S

Solution:

Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be

department_no employee_name V_employee_list = IIF(ISNULL(V_employee_list),employee_name,V_employee_list||’,’||employee_name) O_employee_list = V_employee_list

Step2: Now connect the expression transformation to a target table.

Q2. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list 10, A 10, A,B 10, A,B,C 10, A,B,C,D 20, P 20, P,Q 20, P,Q,R 20, P,Q,R,S

Solution:

Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be

department_no employee_name V_curr_deptno=department_no V_employee_list = IIF(V_curr_deptno! = V_prev_deptno,employee_name,V_employee_list||’,’||employee_name) V_prev_deptno=department_no O_employee_list = V_employee_list

Step2: Now connect the expression transformation to a target table.

Q3. Design a mapping to load a target table with the following values from the above source?

department_no, employee_names 10, A,B,C,D 20, P,Q,R,S

Solution:

The first step is same as the above problem. Pass the output of expression to an aggregator transformation and specify the group by as department_no. Now connect the aggregator transformation to a target table.

[informaticatrainingclasses-blog]

Flat file header row, footer row and detail rows to multiple tables

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).

Now pass the data from expression and aggregator to joiner transformation. In the joiner select the ports from aggregator as master and the ports from expression as details. Give the join condition on the count ports and select the join type as master outer join. Pass the joiner output to a router transformation and create two groups in the router. For the first group give the condtion as exp_count=1, which gives header row. For the second group give the condition as exp_count=agg_count, which gives the footer row. The default group will give the detail rows.

[informaticatrainingclasses-blog]

SQL Transformation Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products PRODUCT ------- SAMSUNG LG IPhone Table Name: Sales PRODUCT QUANTITY PRICE ---------------------- SAMSUNG 2 100 LG 3 80 IPhone 5 200 SAMSUNG 5 500

Create a mapping to join the products and sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE ---------------------- SAMSUNG 2 100 SAMSUNG 5 500 IPhone 5 200 LG 3 80

Solution:

Just follow the below steps for creating the SQL transformation to solve the example

Create a new mapping, drag the products source definition to the mapping.

Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.

Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

    Edit the sql transformation, go to the “SQL Ports” tab and add the input and output ports as shown in the below image. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

    In the same “SQL Ports” Tab, go to the SQL query and enter the below sql in the SQL editor.

  select product, quantity, price from sales where product = ?product?

  Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

    Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG' select product, quantity, price from sales where product ='LG' select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.

SQL Transformation Example Using Full Dynamic query

Q2) I have the below source table which contains the below data.

Table Name: Del_Tab Del_statement ------------------------------------------ Delete FROM Sales WHERE Product = 'LG' Delete FROM products WHERE Product = 'LG'

Solution:

Just follow the same steps for creating the sql transformation in the example 1.

Now go to the “SQL Ports” tab of SQL transformation and create the input port as “Query_Port”. Connect this input port to the Source Qualifier Transformation.

In the “SQL Ports” tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.

As we don’t need any output, just connect the SQLError port to the target.

Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query

Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab Tab_Names ---------- sales products

Solution:

Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

[informaticatrainingclasses-blog]

SQL Transformation in Informatica

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.

The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.

The following SQL statements can be used in the SQL transformation.

Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)

DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)

DATA Retrieval Statement (SELECT)

DATA Control Language Statements (GRANT, REVOKE)

Transaction Control Statements (COMMIT, ROLLBACK)

  Configuring SQL Transformation

The following options can be used to configure an SQL transformation

Mode: SQL transformation runs either in script mode or query mode.

Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.

Database Type: The type of database that the SQL transformation connects to.

Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

You can create the following types of SQL queries

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows. Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.