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