Tuesday 24 June 2014

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

Wednesday 11 June 2014

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

Monday 9 June 2014

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

Wednesday 4 June 2014

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

Tuesday 27 May 2014

Exercise 9: How to Convert Column to Row

Exercise 9: How to Convert Column to Row and achieve target below?

Source
CITY,NAME
BNG,JOY
BNG,LABNITA
CHE,SRINIVAS
CHE,JOYDEEP
CHE,SHRUTHI
PUNE,PRIYA

Target
CITY,NAME
BNG,JOY,LABNITA
CHE,SRINIVAS,JOYDEEP,SHRUTHI
PUNE,PRIYA

Let's Chat: Though I have covered approx 2-3 scenarios that how to convert column to row. This scenario is too basic so, I am covering it in exercise.

Theory: Yes, you are thinking right, first we need to sort the data then we have to concatenate name until the next City arrived then we need to group records and select max(name) based upon city.

Practically

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

Step 2: 
Sorter
Sort the data based upon City.

CITY (Key Port)

Step 3:
Expression

CITY_COMPARE_FLAG (Variable Port)= IIF(CITY=OLD_CITY, 1, 0)

CITY_COMPARE (Variable Port) = IIF(CITY=OLD_CITY, OLD_CITY, CITY)

OLD_CITY (Variable Port) = CITY_COMPARE

NAME_CONCAT (Variable Port) = IIF(CITY_COMPARE_FLAG=1, OLD_CONCAT||','||NAME, NAME)

OLD_CONCAT (Variable Port) = NAME_CONCAT

OUT_NAME (Output Port) = NAME_CONCAT

Step 4
Aggregator
Connect CITY and OUT_NAME column from expression to Aggregator and create one more port

NAME (Output Port) = MAX(OUT_NAME)

Check GROUP port for CITY

Step 5
Connect NAME and CITY from 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 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 ConditionOUT_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

Sunday 4 May 2014

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

Monday 21 April 2014

Scenario 18: How to restrict total no. of records to load into target based upon session run.

Scenario 18: How to restrict total no. of records to load into target based upon session run.

CASE: In source EMP table we have 14 records and we have to load only two records for each run. Means when first time session ran then load first two records then upon second run next two records. So, finally on seventh run of session all records will be loaded to target.

Theory: By analyzing the case we can easily conclude that we need to keep a counter of session run which will be incremented by 2 for every run and If the record id is greater than previous load id and less than equal to session counter then we are done.

Practically:
Step 1: Drag and Drop your source and Target

Step 2: Create two mapping variable
$$Sess_Counter (Integer)
$$Prev_load_ID (Integer)

Step 3: 
Expression
Create Three Ports
Rec_ID (Variable Port)= Old_Rec_ID + 1
Old_Rec_ID (Variable Port)= Rec_ID
Out_Rec_ID (Output Port)= Rec_ID

Step 4:
Filter 
Filter Condition: OUT_REC_ID > $$Prev_load_ID AND OUT_REC_ID <= $$Sess_Counter

Step 5:
Expression
Create one Port
Prev_load_value_Assign (Variable Port) = SETMAXVARIABLE($$Prev_load_ID, OUT_REC_ID)

Step 6
Connect your actual source columns to target.

Step 7
Create Non-Reusable session

Step 8
Create Workflow Variable
$$Sess_Counter_Value (Integer) Persistent (Check box should be checked)
Default Value=2

Step 9
Create Assignment Task
Develop Expression
$$Sess_Counter_Value = $$Sess_Counter_Value + 2

Step 10
Double Click on your session-->Components Tab
Pre_Session_Variable_Assignment
$$Sess_Counter = $$Sess_Counter_Value

Step 11
Create a Link condition which connects from session to assignment task.
$S_RESTIRCT_LOAD.PrevTaskStatus=Succeeded

What are you waiting for Run it Analyze 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

Tuesday 15 April 2014

Scenario 17: How to assign same id for specific group of records

Scenario 17: How to assign an id for specified group of records and achieve target below.

Source
Column1
A
B
C
*
D
E
F
G
*
H
I
J

Target
Column1
D
E
F
G

Analysis: As we have to load only those data which are in between two stars (*). If we can assign a same id for all records which are appearing after occurrence of first star till next star then filter it based upon id then we are done.

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

Step 2:
Expression
Create Four ports
START (Variable Port) = IIF(COLUMN1='*',1)

PASS (Variable Port)= IIF(START=1, OLD_START+1, IIF(START=0 AND OLD_START=0, 0, IIF(START=0 AND OLD_START=1, OLD_START)))

OLD_START (Variable Port)= PASS
PASS_OUT (Output Port)= PASS

Step 3:
Filter
Filter Condition =  COLUMN1!='*' AND  PASS_OUT = 1

Step 4:
Connect to Target from Filter Transformation.

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

Tuesday 8 April 2014

Scenario 13 B: How to Convert Row to Column

Scenario 13 B: How to four column data to eight column data.

(Source)
COL1,COL2,COL3,COL4
7369, SMITH, CLERK, 7902
17-DEC-1980, 800, 400, 20
7499, ALLEN, SALESMAN, 7698
20-FEB-1981, 1600, 300, 30
7521, WARD, SALESMAN, 7698
22-FEB-1981, 1250, 500, 30
7566, JONES, MANAGER, 7839
2-APR-1981, 2975, 400, 20
7654, MARTIN, SALESMAN, 7698
28-SEP-1981, 1250, 1400, 30

(Target)
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-Dec-80,800,400,20
7499,ALLEN,SALESMAN,7698,20-Feb-81,1600,300,30
7521,WARD,SALESMAN,7698,22-Feb-81,1250,500,30
7566,JONES,MANAGER,7839,02-Apr-81,2975,400,20
7654,MARTIN,SALESMAN,7698,28-Sep-81,1250,1400,30

Theory/Analysis
After analyzing source and target we can ascertain that we are getting HIREDATE till DEPTNO column value in second row so, we have to send second row value to HIREDATE, SAL, COMM and DEPTNO column.

Practically
Step 1:
Drag and drop one source and two target instance.
Note: Make sure in your target EMPNO is Primary Key. If it is not at database level then create at Informatica level.

Step 2
Sequence Generator
Create sequence generator and set properties below...
Start Value=1
End Value=2
Cycle (Check box should be checked)

Step 3
Expression
Create three ports
EMPNO (Variable Port) = IIF(NOT IS_NUMBER(COL1), EMPNO_OLD, COL1)
EMPNO_OLD (Variable Port) = EMPNO
OUT_EMPNO (Output Port) = EMPNO 

Step 4
Router
Create two Groups
First_Row  NEXTVAL=1
Second_Row NEXTVAL=2

Step 5
Connect first target instance from First_Row group (EMPNO,ENAME,JOB,MGR)

Step 6
Expression
Create five ports and connect expression from second group Second_Row of router.
EMPNO_UPD (Output Port)= TO_INTEGER(OUT_EMPNO1)

HIREDATE  (Output Port)= IIF(IS_DATE(COL13, 'DD-MON-YYYY')=1,TO_DATE(COL13,'DD-MON-YYYY'))

SAL  (Output Port)=TO_INTEGER(COL23)
COMM  (Output Port)=TO_INTEGER(COL33)
DEPTNO (Output Port)=TO_INTEGER(COL43)

Step 7
Update Strategy
Connect ports from Step 6 expression to Update Strategy
Update Strategy Expression= DD_UPDATE

Step 8
Connect ports from Update Strategy to second target instance.

BINGO!!!

Note: There is another method also... Could you think about that approach? Yes/No/May be... It's all yours. :-) 


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

Wednesday 2 April 2014

Exercise 8: Implementing SCD1

Exercise 8: How to achieve Target below?

Source
part_number  business_unit  business_group
01yp          unassigned            undefined
02yp          unassigned            undefined
03yp          unassigned            undefined

Target

part_key   part_number  business_unit  business_group
      1           01yp          unassigned            undefined
      2           02yp          HSBC                 undefined
      3           03yp          unassigned           undefined

Note: There are some other process which also runs on same target table and update business unit according to their need. so, in target you can see for part_number 02yp business unit is HSBC. It's not processed by Informatica it's processed by some other application.

Requirement Specification:
Case 1: New Part in Source insert into Target.
Case 2: If business_unit changed in source (Other than unassigned) then only update the business unit in target.


Theory/Analysis: It's a simple requirement of SCD 1 only one thing is need to keep in mind that while updating simple comparison of Target Business_Unit and Source Business_Unit will not work here because some other process has modified this column with value. We need to include one more condition while comparing...

Practically

Step 1
Drag and Drop Source and two Target instance to mapping designer.

Step 2
Lookup
Create Lookup and follow steps below...
A) Drag and drop part_number from source Qualifier to Lookup.

B) Lookup-->Right Click-->Edit-->Condition Tab
     Lookup Table Column          Operator         Transformation Port
                 part_number                =              part_number1
Step 3
Expression
Take Business_unit, Part Key, Part_Number column from lookup and all three columns from source qualifier.
Create Two Ports
New_Record=IIF(ISNULL(PART_NUMBER_TGT), 1, 0)

Upd_Record=IIF(NOT ISNULL(PART_NUMBER_TGT) AND (LOWER(BUSINESS_UNIT)!= 'unassigned' AND (BUSINESS_UNIT_TGT != BUSINESS_UNIT)), 1, 0)

Step 4
Router
Take Part_key column of Lookup and all source column including New_Record and Upd_Record to Router.
Create Two Group
New_Rec=New_Record = 1
Upd_Rec=Upd_Record = 1

Step 5
Connect New_Record group column to first target instance.

Step 6
Sequence Generator
Connect NEXTVAL port to Part_key column of first target instance.

Step 7
Update Strategy
Connect Part_key and Business_unit column from Upd_Rec group of Router to Update Strategy.
B)
Right Click-->Edit-->Properties Tab
Update Strategy Expression = DD_UPDATE

Step 8
connect part_key and business_unit port to second target instance.

Note 1: At the session level "Treat Source rows as: DATA DRIVEN" must be set.
Note 2: At target level you must have a primary key. If not available at database level then create it Informatica level.

Limitation: Solution above will work only when unassigned value is coming in Source Business_unit Column if actual value of Business_unit is missing .

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

Thursday 27 March 2014

Exercise 7: How to create and use Pipeline Lookup or How to create Active Lookup?


Exercise 7: How to Achieve Target below

(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Let's Chat: I know this exercise we have already solved in Exercise 6 CASE 2. Here I am taking same scenario and will resolved it though Active pipeline lookup.

What is Pipeline Lookup?
A pipeline lookup is used do lookup upon an application source that is not a relational table or flat file.
A Source Qualifier has been used as a lookup table for Pipeline Lookup.
A Source Qualifier which has been used as a lookup table remains abandon in mapping.

What is the Use of Pipeline Lookup?
To improve performance when processing relational or flat file lookup sources, create a pipeline Lookup transformation instead of a relational or flat file Lookup Transformation. 

Pipeline Lookup can be created in Connected or Unconnected mode.


Theory/Analysis:
After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and for this we will create a Active Pipeline Lookup to take all rows of ID and Pass only that value where Premium Amount is NOT NULL. If you want to create Active Lookup then simply one Check box you have to click while creating lookup.

Practically:

Step 1:
Drag and Drop your source twice and Target once.

Step 2:
Lookup

While creating Select Source Qualifier and Select check box Return all Values on Multiple Match (See Picture Below)


Connect Ins_ID, Premium_Year from Source qualifier (Upon which you are not doing Lookup) to Lookup Transformation.
Condition Tab
Ins_Id = Ins_Id 1

Step 3
Filter
Connect Ins_ID, Premium_Year (Column of Source) and Premium_Amount Column of Lookup from Lookup to Filter Transformation.

Filter Condition: NOT ISNULL(Premium_Amount)

Step 4
Connect Your Columns from Filter to Target.

Step 5:
Give the Source connection or Location/File_Name for both source qualifier.
No need to give Source connection or Location/File_Name for Lookup Table.

Note: Your Second Source and Source Qualifier upon which you are doing Pipeline Lookup will be abandon in mapping no need to do anything with them. Your mapping will look like Image below...

BINGO!!!

Note: You can also achieve this by doing Active Lookup directly  upon your source. Approach explained above is just a representation that how to create Pipeline Lookup.


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

Wednesday 26 March 2014

Exercise 6: How to replace Null with Value

Exercise 6: How to replace NULL with value and achieve target below?

CASE 1
(Source)
ins_id,premium_year,premium_Amount
101,2010,4500
101,2011,NULL
101,2012,NULL
102,2010,6000
102,2011,NULL
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id So if, some how we can store the Premium_Amount first value for each ID and pass it for each and every row then we are done.

Practically:
Step 1
Drag and Drop your source and Target Instance

Step 2
Expression
Create Three ports

PREMIUM_COMPARE (Variable Port) = IIF(ISNULL(premium_Amount),OLD_PREMIUM,premium_Amount)

OLD_PREMIUM (Variale Port) = PREMIUM_COMPARE

O_PREMIUM_AMOUNT (Output Port) = PREMIUM_COMPARE

Step 3
Connect your target from expression. Only one thing needs to take care while connecting Premium_Amount column connect it through O_PREMIUM_AMOUNT port.

That's it... Wow!!! It's was this much easy completed in only three steps.

BINGO!!!




CASE 2
(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and our CASE 1 solution does not work here. We have to think about some other logic.

Practically:

Step 1
Drag and Drop your source and Target Instance

Step 2
Aggregator
Connect ins_id and Premium_Amount from Source Qualifier
Create One Port

O_Premium_Amount (Output Port) = MAX(Premium_Amount)

Step 3
Sorter
Connect Ins_Id and Premium_Year from Source Qualifier

Select Ins_Id as key port.

Ins_ID (key)

(Clarification: We need sorter here as we are going to join two pipeline of same source qualifier and for this we need to pass sorted input for Joiner Transformation.)

Step 4
Joiner
Connect Ins_Id and Premium_Year from Sorter and Ins_Id, O_Premium_Amount from Aggregator

Set Properties below...

Condition Tab

Ins_Id 1 = Ins_Id

Join Type: Normal

Step 5
Connect Ins_Id and Premium_Year and Premium_Amount from Joiner 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 

Monday 24 March 2014

Exercise 5: How to find sum of negative and sum of positive integer separately.

Exercise 5: How to find sum of negative and sum of positive integer separately.

Ex: 
Source 
Account_Balance
500
600
-300
-200
400
-100

Target
Total_Account_Balance
1500
-600

Theory/Analysis: After analyzing source and target we can conclude that we have to find the sum for positive and negative number separately. Here only thing we need to identify, the positive and negative integer then finding sum is not a big task. That's it. Identifying integer is also very easy :-)

Step 1
Drag and drop one instance of source and two instance of target.

Step 2
Router
Create one Group
Negative_Number=SUBSTR(TO_CHAR(Account_Balance), 1, 1)='-'

Step 3
Aggregator
Create two Aggregator Transformation

Connect Aggregator1 from Negative_Number Group of Router Transformation
Create one port
Total_Balance (Output Port) = Sum(Account_Balance1)

Connect Aggregator2 from Default Group of Router Transformation
Create one port
Total_Balance (Output Port) = Sum(Account_Balance2)

Step 4:
Connect your both target instance from separate Aggregator Transformation.

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