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.
Informatica online training - Informatica Jobs

Design/Implement/Create SCD Type 2 Effective Date Mapping in Informatica

Q) How to create or implement slowly changing dimension (SCD) Type 2 Effective Date mapping in informatica?

SCD type 2 will store the entire history in the dimension table. In SCD type 2 effective date, the dimension table will have Start_Date (Begin_Date) and End_Date as the fields. If the End_Date is Null, then it indicates the current row.
We will see how to implement the SCD Type 2 Effective Date in informatica. As an example consider the customer dimension. The source and target table structures are shown below:

--Source Table

Create Table Customers
(
  Customer_Id Number Primary Key,
  Location    Varchar2(30)
);

--Target Dimension Table

Create Table Customers_Dim
(
  Cust_Key Number Primary Key,
  Customer_Id   Number,
  Location      Varchar2(30),
  Begin_Date    Date,
  End_Date      Date
);

The basic steps involved in creating a SCD Type 2 Effective Date mapping are

• Identifying the new records and inserting into the dimension table with Begin_Date as the Current date (SYSDATE) and End_Date as NULL.
• Identifying the changed record and inserting into the dimension table with Begin_Date as the Current date (SYSDATE) and End_Date as NULL.
• Identify the changed record and update the existing record in dimension table with End_Date as Curren date.

We will divide the steps to implement the SCD type 2 Effective Date mapping into four parts.

SCD Type 2 Effective Date implementation - Part 1

Here we will see the basic set up and mapping flow require for SCD type 2 Effective Date. The steps involved are:

• Create the source and dimension tables in the database.
• Open the mapping designer tool, source analyzer and either create or import the source definition.
• Go to the Warehouse designer or Target designer and import the target definition.
• Go to the mapping designer tab and create new mapping.
• Drag the source into the mapping.
• Go to the toolbar, Transformation and then Create.
• Select the lookup Transformation, enter a name and click on create. You will get a window as shown in the below image.

• Select the customer dimension table and click on OK.
• Edit the lookup transformation, go to the ports tab and remove unnecessary ports. Just keep only Cust_key, customer_id and location ports in the lookup transformation. Create a new port (IN_Customer_Id) in the lookup transformation. This new port needs to be connected to the customer_id port of the source qualifier transformation.

• Go to the conditions tab of the lookup transformation and enter the condition as Customer_Id = IN_Customer_Id
• Go to the properties tab of the LKP transformation and enter the below query in Lookup SQL Override. Alternatively you can generate the SQL query by connecting the database in the Lookup SQL Override expression editor and then add the WHERE clause.

SELECT  Customers_Dim.Cust_Key as Cust_Key,
        Customers_Dim.Location as Location,
        Customers_Dim.Customer_Id as Customer_Id
FROM    Customers_Dim
WHERE   Customers_Dim.End_Date IS NULL

• Click on Ok in the lookup transformation. Connect the customer_id port of source qualifier transformation to the In_Customer_Id port of the LKP transformation.
• Create an expression transformation with input/output ports as Cust_Key, LKP_Location, Src_Location and output ports as New_Flag, Changed_Flag. Enter the below expressions for output ports.

New_Flag = IIF(ISNULL(Cust_Key), 1,0)
Changed_Flag = IIF( NOT ISNULL(Cust_Key) AND
               LKP_Location != SRC_Location, 1, 0)

• The part of the mapping flow is shown below.

SCD Type 2 Effective Date implementation - Part 2

In this part, we will identify the new records and insert them into the target with Begin Date as the current date. The steps involved are:

• Now create a filter transformation to identify and insert new record in to the dimension table. Drag the ports of expression transformation (New_Flag) and source qualifier transformation (Customer_Id, Location) into the filter transformation.
• Go the properties tab of filter transformation and enter the filter condition as New_Flag=1
• Now create a update strategy transformation and connect the ports of filter transformation (Customer_Id, Location). Go to the properties tab and enter the update strategy expression as DD_INSERT.
• Now drag the target definition into the mapping and connect the appropriate ports of update strategy transformation to the target definition.
• Create a sequence generator and an expression transformation. Call this expression transformation as "Expr_Date".
• Drag and connect the NextVal port of sequence generator to the Expression transformation. In the expression transformation create a new output port (Begin_Date with date/time data type) and assign value SYSDATE to it.
• Now connect the ports of expression transformation (Nextval, Begin_Date) to the Target definition ports (Cust_Key, Begin_Date). The part of the mapping flow is shown in the below image.

SCD Type 2 Effective Date implementation - Part 3

In this part, we will identify the changed records and insert them into the target with Begin Date as the current date. The steps involved are:

• Create a filter transformation. Call this filter transformation as FIL_Changed. This is used to find the changed records. Now drag the ports from expression transformation (changed_flag), source qualifier transformation (customer_id, location), LKP transformation (Cust_Key) into the filter transformation.
• Go to the filter transformation properties and enter the filter condition as changed_flag =1.
• Now create an update strategy transformation and drag the ports of Filter transformation (customer_id, location) into the update strategy transformation. Go to the properties tab and enter the update strategy expression as DD_INSERT.
• Now drag the target definition into the mapping and connect the appropriate ports of update strategy transformation to the target definition.
• Now connect the Next_Val, Begin_Date ports of expression transformation (Expr_Date created in part 2) to the cust_key, Begin_Date ports of the target definition respectively. The part of the mapping diagram is shown below.

SCD Type 2 Effective Date implementation - Part 4

In this part, we will update the changed records in the dimension table with End Date as current date.

• Create an expression transformation and drag the Cust_Key port of filter transformation (FIL_Changed created in part 3) into the expression transformation.
• Go to the ports tab of expression transformation and create a new output port (End_Date with date/time data type). Assign a value SYSDATE to this port.
• Now create an update strategy transformation and drag the ports of the expression transformation into it. Go to the properties tab and enter the update strategy expression as DD_UPDATE.
• Drag the target definition into the mapping and connect the appropriate ports of update strategy to it. The complete mapping image is shown below.

For more Visit  http://www.quontrasolutions.com/blog/category/informatica/