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

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

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

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