Scenario 26: How to create and use Active Lookup Transformation?

Scenario 26: How to create and use Active Lookup Transformation?

What is Active Lookup Transformation?
When lookup transformation return all the matching records from the Lookup table then it's called as an Active transformation.
This feature of Lookup has been introduced from 9.X version of Informatica Power Center tool.

Business Requirement: We have two table Product and currency now in the target table we have to populate all products with price after taking the all currency rate from currency table.

Note: There is no common column in both tables.

Source.

Source: Two tables

Product Table

Prod_ID	Prod_Name	Price_USD
2001	Laptop	300
2002	Desktop	200
2003	Mouse	20

 Currency Table

ID	Description	Rate
1	SGR	55
2	INR	62
3	EURO	42

Target Table: 

Prod_ID	Prod_Name	Price_USD	Price_SGR	Price_INR	Price_EURO
2001	Laptop	300	16500	18600	12600
2002	Desktop	200	11000	12400	8400
2003	Mouse	20	1100	1240	840

 
Let's Chat: I know! I know you will be saying hey we can achieve this though joiner transformation easily though we don't have column in both tables. A BIG THANK YOU... You have visited my previous posts and learned it well how to handle.
We all know there will be several way to accomplish business requirement. :-) Here I am opting Active Lookup approach to explain the use. Here we go....

Practically

Step 1: Import Product table. (Import your target table as well.)

Step 2: Expression.
 Create one output port. (This port will be used in condition tab of Lookup)
 SRC_DUMMY_ID (Output Port) = 1

Step 3: Lookup 
a) Create Lookup transformation.... Wait. while creating don't forget to check the option which is shown in Picture below.

b) Add a port manually
   DUMMY_ID (Decimal) Output & Lookup port should be checked.

c) Set condition Tab.

DUMMY_ID = SRC_DUMMY_ID

d) Override the SQL in properties tab.

SELECT CURRENCY.DESCRIPTION as DESCRIPTION, CURRENCY.RATE as RATE, 1 as DUMMY_ID, CURRENCY.ID as ID FROM CURRENCY

Step 4: Aggregator
Connect DESCRPTION, RATE and DUMMY_ID to Aggregator
(All port should be checked as GROUP port)

Step 5: Joiner
a) Create Joiner and Selected Sorted Input in Properties tab.
b) Now Drop all ports from Aggregator and Expression (Step2)
c) Select  Ports from Lookup as Master Port in Port Tab.
d) Condition
DUMMY_ID=SRC_DUMMY_ID

Step 6: Expression
Take Prod_ID, Prod_Name, Price, Description, Rate Port from Joiner.
Create three output port for calculating price.
INR_PRICE (Output Port)=IIF(DESCRIPTION='INR', USD_PRICE*RATE)
SGR_PRICE (Output Port)=IIF(DESCRIPTION='SGR', USD_PRICE*RATE)
EURO_PRICE (Output Port)= IIF(DESCRIPTION='EURO', USD_PRICE*RATE)

Step 7: Aggregator
Connect all ports from expression.
Select PROD_ID port as GROUP port.
Create three output port to de-normalize the data.
O_INR_PRICE (Output Port)=MAX(INR_PRICE)
O_SGR_PRICE (Output Port)=MAX(SGR_PRICE)
O_EURO_PRICE (Output Port)= MAX(EURO_PRICE)

Step 8: Connect to your target.

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 25: How to remove duplicate records and load only non duplicate records without using Aggregator Transformation.

Scenario 25: How to remove duplicate records and load only non duplicate records without using Aggregator Transformation.

Source
COL1
1
1
2
3
3
4

Target
COL1
2
4

Theory: The issue is grouping all duplicate records then eliminating. We will split Distinct and Duplicate records and once again we will join those two pipeline and eliminate all records which are matching from both pipeline.

Practically: 
Step 1: 
Drag and Drop your source and target.

Step 2:
Sorter
Sort the data based upon COL1

COL1 (Key Port)

Step 3:
Expression
Create Four Ports

NEW_REC_FLAG (Variable Port)= IIF(OLD_REC=col1, 2, 1)

NEW_REC (Variable Port)= IIF(col1!=OLD_REC, col1, OLD_REC)

OLD_REC (Variable Port)=NEW_REC

OUT_REC_FLAG (Output Port)= NEW_REC_FLAG

Step 4
Router
Create Two Group

New_Rec: OUT_REC_FLAG=1
Dup_Rec: OUT_REC_FLAG=2


Step 5
Sorter
Connect Dup_Rec group.

Select OUT_REC_FLAG3 as KEY Port.

Step 6
Joiner

Connect New_Rec Group to Joiner.
Connect ports from Sorter Transformation to Joiner

Join Type: Master Outer Join (Ports connected from sorter should be master port)

Condition Tab:
MASTER OPERATOR DETAIL
COL11      =                 COL13

Note: In properties tab select Sorted Input as we are joining two pipeline originated from same source qualifier.

Step 7
Filter
Connect all ports from Joiner to Filter

Filter Condition=ISNULL(COL13)

Step 8
Connect COL11 to Target from Filter.

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 24: How to Join two tables EMP and DEPT without using Joiner, Source Qualifier and Lookup. (Level 2)

Scenario 24: How to Join two tables EMP and DEPT without using Joiner, Source Qualifier and Lookup. (Level 2)

CASE 1
 For Employees data you are getting EMPNO, ENAME, JOB, MGR in one table let's say EMP1 and EMPNO, SAL, COMM, DEPTNO in another table let's say EMP2. 
http://informaticachamp.blogspot.in/2014/06/scenario-23-how-to-join-two-tables.html

Theory: 
We need Union to merge two source then sort the data based upon Deptno and will use expression to retain DNAME and LOC value for next row if deptno is same for current row and next row. Confused? Even I am. Let's Do it Practically.

Practically.

I am taking default Oracle Table EMP and DEPT. Hope everyone know the table structure of these two table so, I am not giving structure.

Step 1:
Drag and Drop your source EMP and DEPT.

Step 2:
Expression
Connect from EMP source qualifier to expression.
Create two ports

DNAME (Output Port) = NULL
LOC       (Output Port) = NULL

Note: Take care of Data Type, Precision and Scale while creating. It should match to Dept table.

Step 3
Expression
Connect from DEPT source qualifier to expression
Create seven ports

EMPNO (Output Port) = NULL
ENAME (Output Port) = NULL
JOB        (Output Port) = NULL
MGR       (Output Port) = NULL
HIREDATE  (Output Port) = NULL
SAL         (Output Port) = NULL
COMM  (Output Port) = NULL

Note: Take care of Data Type, Precision and Scale while creating it should match to EMP table.

Step 4
Union
Create two groups and group tab EMP and DEPT
Connect Expression 1 to EMP group and Expression 2 to DEPT group.

Step 5
Sorter
Connect Union to Sorter

Select DEPTNO as Key Port. 

In the Properties tab select check box "NULL treated low"

Step 6
Expression
Connect Sorter to Expression

NEW_REC_FLAG (Variable Port) = IIF (ISNULL(EMPNO),1,0)

DEPTNO_COMPARE_FLAG (Variable Port) = IIF(DEPTNO !=OLD_DEPTNO, 1, 0)

DEPTNO_COMPARE (Variable Port) = IIF(DEPTNO=OLD_DEPTNO, OLD_DEPTNO, DEPTNO)

OLD_DEPTNO (Variable Port) = DEPTNO_COMPARE

DNAME_COMPARE (Variable Port) = IIF(DEPTNO_COMPARE_FLAG=1, DNAME, OLD_DNAME)

OLD_DNAME (Variable Port) = DNAME_COMPARE

LOC_COMPARE (Variable Port) = IIF(DEPTNO_COMPARE_FLAG=1, LOC, OLD_LOC)

OLD_LOC (Variable Port) = LOC_COMPARE

OUT_DNAME (Output Port) = DNAME_COMPARE

OUT_LOC (Output Port) = LOC_COMPARE

NEW_REC_FLAG_OUT (Output  Port) = NEW_REC_FLAG

Step 7
Filter
Filter Condition: NEW_REC_FLAG_OUT=0

Step 8
Connect filter to Target. 

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 23: How to Join two tables without using Joiner, Source Qualifier and Lookup. (Level 1)

Scenario 23: How to Join two tables without using Joiner, Source Qualifier and Lookup. (Level 1)

CASE 1
For Employees data you are getting EMPNO, ENAME, JOB, MGR in one table let's say EMP1 and EMPNO, SAL, COMM, DEPTNO in another table let's say EMP2. 

CASE 2
Joining EMP and DEPT table according to NORMAL Join
http://informaticachamp.blogspot.in/2014/06/scenario-24-how-to-join-two-tables.html

Theory: 
Few days back we saw how to do union without using union transformation. (http://informaticachamp.blogspot.in/2014/06/scenario-21-how-to-do-union-without.html).  We have to reverse that logic. Means here we will do union for both tables then to avoid null we will use Aggregator.

Practically:
For doing Practical I am taking same case which I have explained in example.

Source
TABLE 1 = EMP 1                                                            TABLE 2 = EMP 2

EMPNO   Number                                                            EMPNO    Number
ENAME   Varchar2(10)                                                    SAL           Number
JOB          Varchar2(9)                                                      COMM     Number
MGR         Number                                                            DEPTNO  Number

Target

EMPNO   Number
ENAME   Varchar2(10)
JOB     Varchar2(9)
MGR     Number
SAL     Number
COMM    Number
DEPTNO  Number

Step 1:
Drag and Drop your both source and target table.

Step 2:
Expression
Drag and Drop column from Source Qualifier EMP1
Create three ports
SAL (Output Port) = NULL
COMM  (Output Port) = NULL
DEPTNO  (Output Port) = NULL

Step 3
Expression
Drag and Drop column from Source Qualifier EMP2
Create three ports
ENAME  (Output Port) = NULL
JOB  (Output Port) = NULL
MGR  (Output Port) = NULL

Step 4
Union
Create Two Groups in Group Tab (EMP1 and EMP2)
Connect Ports from Expression 1 and Expression 2 to each group.

Step 5
Aggregator
Create Six Ports

ENAME   (Output Port) = MAX(ENAME)
JOB     (Output Port) = MAX(JOB)
MGR    (Output Port) = MAX(MGR)
SAL    (Output Port) = MAX(SAL)
COMM   (Output Port) = MAX(COMM)
DEPTNO (Output Port) = MAX(DEPTNO)

Select EMPNO as GROUP port. 

Step 6
Connect your Aggregator to Target.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Let's Chat: Normal SCD1 always implemented with only logic of Insert and Update. Means new record inserted in source then insert if any record update in source then update target. What if you have to delete your record in target if any record is delete from source.
There could be multiple approach to achieve this. Here I am opting simple approach with Joiner.

Theory: 
Now it's pretty much clear that we have to implement three operation.
New Record in Source--> Insert
Record Updated in Source-->Update
Record Deleted from Source-->Delete

Ahhh... It's too easy. :-) 

Practically:

My Source Table Name is EMP and Target Table name is EMP_SCD1

Step 1:
Drag and Drop your Source EMP.
Import your target table (EMP_SCD1) as source and drag and drop it in mapping designer.
Drop your target table three times.

Step 2:
Joiner
Drag and Drop all your ports from source 1 EMP to Joiner.
Drag and Drop EMPNO and SAL from Source 2 (EMP_SCD1) to Joiner

Note: For better understanding I have renamed EMPNO and SAL from Source 2 (EMP_SCD1) to EMPNO_TGT and SAL_TGT

Join Condition: EMPNO=EMPNO_TGT

Join Type: Full Outer Join

Step 3
Expression
Create three output ports

New_Rec (Output Port) IIF(NOT ISNULL(EMPNO) AND ISNULL(EMPNO_TGT), 1, 0)

Upd_Rec (Output Port)  IIF(NOT ISNULL(EMPNO) AND NOT ISNULL(EMPNO_TGT) AND SAL != SAL_TGT, 1, 0)

Del_Rec (Output Port)  IIF(NOT ISNULL(EMPNO_TGT) AND ISNULL(EMPNO) , 1, 0)

Step 4
Router
Create three group

Insert_Rec: New_Rec=1
Update_Rec: Upd_Rec=1
Delete_Rec: Del_Rec=1

Step 5
Connect Insert_Rec group to Target Instance 1 for Insert.

Step 6
Update Strategy
Connect EMPNO and SAL from Update_Rec to Update Strategy and from Update Strategy to Target Instance 2.

Update Strategy Expression: DD_UPDATE

Step 7
Update Strategy
Connect EMPNO_TGT from Delete_Rec group to Update Strategy and from Update Strategy to Target Instance 3.

Update Strategy Expression: DD_DELETE

Check List: 

1. Make sure your target table should have a Primary key either Database level or Informatica Level. 
2. Make sure Treat Source Rows as set to DATA DRIVEN at session-->Properties Tab.
3. Make sure Load Type of last two target instance is NORMAL (Though you won't get any error for this. If it is BULK integration service will revert it NORMAL while executing.)

BINGO!!!

Isn't it too easy :-) 

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 21: How to do Union without using Union Transformation

Scenario 21: How to do Union without using Union Transformation?

Let's Chat: Union is horizontal merge of tables whereas Join is Vertical Merge of tables. As we have to merge two tables data without using Union means we have to achieve Horizontal Merge Logically.

Theory: We will join two tables first.. suppose we have 8 columns in table A and 8 columns in table B so, we will get total 16 columns after join. Now if we can transpose or convert the last 8 columns in row then we are done. Yes, you are correct using Normalizer. Few more things we need to take care that we will see practically. I am taking Oracle default EMP table two times for practical.

Practically:
Step 1: Drag and Drop your both source table in Mapping Designer.

Step 2: 
Expression (For First Source Qualifier)
Create four ports.
ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) = ID
OUT_HIREDATE (OutPut Port) = TO_CHAR(HIREDATE, 'MM/DD/YYYY')

Note: We are converting HIREDATE to char because we can't use DATE data type in Normalizer to transpose. 

Step 3: 
Expression (For second Source Qualifier)
Create four ports.
ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) = ID
OUT_HIREDATE (OutPut Port) = TO_CHAR(HIREDATE, 'MM/DD/YYYY')

Note: We are converting HIREDATE to char because we can't use DATE data type in Normalizer to transpose. 

Step 4
Joiner
Connect all columns from Expression 1 and 2
 Join Type :- NORMAL
Join Condition :- ID_OUT1=ID_OUT

Step 5
Normalizer
Create 8 ports in Normalizer Tab
Set OCCURS 2 for each Column and be careful with data type of each column.

COLUMN  OCCURS DATATYPE
EMPNO    2     NUMBER
ENAME    2     STRING
JOB      2     STRING
MGR      2     NUMBER
HIREDATE 2     STRING
SAL      2     NUMBER
COMM     2     NUMBER
DEPTNO   2     NUMBER

Step 6
Expression
Create one output port to convert HIREDATE from string to Date.

OUT_HIREDATE (Output Port) IIF( IS_DATE(HIREDATE, 'MM/DD/YYYY'), TO_DATE(HIREDATE, 'MM/DD/YYYY'))

Step 7
Connect your target. Create session and workflow, Run it and have same output of Union.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 20: How to insert same group of data in single column.

Scenario 20: How to insert same group of data in single column.

Source
ID,NAME,DEPTNO
100,JOHN,10
200,WALTER,20
300,GIBSON,30
400,KERRY,20
500,PHILIP,10

Target
DEPT10,DEPT20,DEPT30
JOHN,WALTER,GIBSON
PHILIP,KERRY,

Theory: It's quite challenging.... Hmmmm For achieving target above we need to treat each unique deptno as one column then we need to join all three columns to create a row.

Practically:

Step 1: Drag and drop your source and target to mapping designer.

Step 2:
Router
Create Three groups in Router
DEPT10 : DEPTNO=10
DEPT20 : DEPTNO=20
DEPT30 : DEPTNO=30

Step 3:
Expression
Create three Expression Transformation and connect each group of Router to one Expression Transformation. Let's say... EXPRESSION 1, EXPRESSION 2, EXPRESSION 3

Now, we need to crate an ID for each and every record based upon which we will join these columns and create a row.

EXPRESSION 1

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) =  ID

EXPRESSION 2

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

EXPRESSION 3

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

Now we have three pipeline which we need to join so, (n-1)2 (Where n=pipeline) = 2 Joiner Transformation Required
As we are joining pipelines which are originated from same source qualifier so as per the rule we need to pass sorted input for both Joiner Transformation. So, let's create Sorter First

Step 4
Sorter
Connect Name and OUT_ID column from EXPRESSION 2 to Sorter.

OUT_ID (Key Port)

Step 5:
Joiner
Connect Name and OUT_ID from expression 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 6
Sorter
Connect Name and OUT_ID column from EXPRESSION 3 to sorter.

OUT_ID (Key Port)

Step 7:
Joiner
Connect Name and OUT_ID from Joiner 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 8
Now you will have total three name column will be there in your Joiner Transformation. Connect each name to target column.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Let's Chat: Populating date dimension table can be done with multiple approaches according to requirements. Here I have taken a Basic one. In which I am taking a date and populating date dimension for complete year. Suppose date is coming '02-Jun-81' so, date dimension would be populated for complete year 1981. Starting from 01-Jan-81 to 31-Dec-81. If any date belongs to 1981 is coming once again in year then discard that date means populate date dimension table only for new calendar year.

Theory: It's too easy with help of procedure and same is used mostly in real time as well.

Date_Dimension Table Structure:
DATEKEY                                 NUMBER 
FULLDATE                                DATE 
DAYOFWEEK                           NUMBER
DAYNAMEOFWEEK                VARCHAR2(15)
DAYOFYEAR                             NUMBER
WEEKDAYWEEKEND            VARCHAR2(15)
WEEKOFYEAR                           NUMBER
MONTHNAME                            VARCHAR2(15)
MONTHOFYEAR                        NUMBER
QTR                                              NUMBER 
CAL_YEAR                              NUMBER

A Procedure Code which will populate Date Dimension.

create or replace PROCEDURE DATE_DIM
(in_date in date)
as
date_count number;
start_date date;
end_date date;
max_date_key number;
new_date_key number;
curr_date date;
Begin
--Check Whether Date Dimension table has been already populated for coming date?
-- If count is 0 then there is no data for coming date.
select count(*) into date_count
from date_dimension
where fulldate=in_date;

--Set Start_Date to first date of year. Ex: (01/01/2013)
select trunc(in_date, 'YYYY')into start_date from dual;

--Set end_date to Last date of year. Ex: (31/12/2013)
select add_months(trunc(in_date,'YYYY'), 12)into end_date from dual;

--Find the last generated DATE_KEY value
select max(datekey)into max_date_key from date_dimension;

--If last generated key value is null then assign 1 else assign last generated key value + 1 to new_date_key
select nvl2(max_date_key,max_date_key+1,1)into new_date_key from dual;

--For Loop set current date as Start Date
curr_date := start_date;

--If date count=0 means we don't have any data for particular year
if date_count = 0
then
--If condition is TURE then Start the Loop and insert record starting from 1st day to last day of year.

While curr_date < end_date loop
insert into date_dimension(datekey,FULLDATE,DATE_DAY,DAYOFWEEK, 
            DAYNAMEOFWEEK,DAYOFYEAR,WEEKDAYWEEKEND,WEEKOFYEAR,
            MONTHNAME,MONTHOFYEAR, qtr, cal_year)
select new_date_key,curr_date, TO_CHAR(curr_date,'DD'),
        TO_CHAR(curr_date,'D'),TO_CHAR(curr_date, 'Day'),
        TO_CHAR(curr_date, 'DDD'), 
        DECODE(TO_CHAR(curr_date, 'D'),  1,'Weekend',
                                      7,'Weekend',
                                      'Weekday'),
        TO_CHAR(curr_date, 'iw') ,TO_CHAR(curr_date, 'Month') ,
        TO_CHAR(curr_date, 'mm'),TO_CHAR(curr_date, 'q') ,
        TO_CHAR(curr_date, 'yyyy') from dual;

 --Generating new id for DATEKEY column       
new_date_key:= new_date_key + 1;

--Increment date by one for next date. 
curr_date := curr_date + 1;
END LOOP; 
end if;
End;

(Above code is tested and executed successfully. Feel free to mail me if you found any error)

Yahoo!!! Everything done by procedure, Now we need to create a simple mapping.

Step 1: We need to create a dummy source. Create a .txt target file with one column with date datatype.

Drag and Drop your source in Mapping Designer.

Step 2:

Stored Procedure Transformation.

Create Stored procedure Transformation.

Set In_Date port as output port as well. 
Connect Hiredate of EMP Table (Source) to In_Date port of Stored Procedure.

Step 3

Connect your Date_In port to Target.

We are done friends, create session and workflow and run it.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com