Monday, 24 February 2014

Scenario 1: How to load single source data in four different target based upon session run through single mapping.

Scenario 1: How to load single source data in four different target based upon session run through single mapping.

 Ex: One Source (Emp Table)
      1st run of Session-->EMP_TGT1
      2nd run of Session-->EMP_TGT2
      3rd run of Session-->EMP_TGT3
      4th run of Session-->EMP_TGT4
      5th run of Session-->EMP_TGT1

Solution:

 Theoretical 
We need 
AA) Mapping Variable to keep counter of mapping run.
A)    Two Instance of Source: First instance treat as actual source for loading data. Second   instance of source for Aggregator Transformation.
B)    Aggregator  to find total number of records in source. As we are not selecting any port as group by so, it will return last record with Total Number of Records.
C)    Joiner to Join both Source Qualifier pipeline based upon EMPNO as It's MASTER OUTER   type of join so, it will return matched record from master and all records from outer table.
D)    In Expression keep counting each and every records passed through it. When matching of   Count in Expression is matched with Total Number of Records then increment Mapping   Variable Counter with 1.
E)     Route records based upon Mapping Variable counter value.

 Practical
1. Create Mapping Variable $$TGT_HIT (Integer) Aggregation (Count) -->Initialize with 1 (Initial Value=1)

Step 1: Drop your Source twice.

Step 2: Create Aggregator and connect Second Source Qualifier to it.
Aggregator
A) Create one port Total_Record (Output Port) COUNT(EMPNO)
B) Don't select any port as Group By port

Step 3: Create Joiner and connect First Source Qualifier to it.
Joiner
A) Connect all column from First Source Qualifier to Joiner
B) Connect Total_Record and EMPNO column from Aggregator
C) Set Join Type=MASTER OUTER JOIN
D) Condition: EMPNO1=EMPNO

Step 4: Create Expression and Connect all columns from Joiner to it.
Expression
A) Create Four ports
v_rec (Number) (Variable Port)-->v_rec_count + 1
v_rec_count (Number) (Variable Port)-->v_rec
Var_Assign (Number) (Variable Port)-->
IIF(Total_Record = v_rec_count AND $$TGT_HIT = 1, SETVARIABLE($$TGT_HIT, 2), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 2, SETVARIABLE($$TGT_HIT, 3), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 3, SETVARIABLE($$TGT_HIT, 4), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 4, SETVARIABLE($$TGT_HIT, 1)))))

NEXTVAL (Number) (Output Port)-->$$TGT_HIT

Step 5: Create Router and Connect all columns from Expression.
Router
A) Create Four Groups
FIRST_TARGET=NEXTVAL=1
SECOND_TARGET=NEXTVAL=2
THIRD_TARGET=NEXTVAL=3
FOURTH_TARGET=NEXTVAL=4

Step 6: Connect each group to each target.

Create Session and Workflow and 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



Posted by at
Labels:

23 comments:

  1. Unknown4 November 2014 at 19:56

    Beautifully explained the scenario. Screenshots and proper explanation in detail. Hats off to you big bros. Keep up the good work.
    Mayur

    ReplyDelete
  2. Unknown15 November 2014 at 04:17

    two doubts
    1- isn't the nested IIF missing and else cluase at the end?
    2- (more important)
    I understand that in Expr transformation, Variables ports are evaluated before output ports and in order of appearance, and setvariable() sets variable to new value and return new value.

    Based on that, for the last record i.e. when Total_Record = v_rec_count becomes true, whatever be the $$TGT_HIT value it get set to nextvalue. Then output port NEXTVAL is set to $$TGT_HIT ( which now contains the new incremented value)

    As a result the last record should flow to next target.

    let me know if i am mistaken. would love to hear your thoughts soon.

    ReplyDelete
    Replies
    1. Unknown16 November 2014 at 00:40

      Hello Richi Rox,

      Let me explain your doubt one by one.

      1- isn't the nested IIF missing and else clause at the end?

      Yes, it's missing here because we don't need it. Is it mandatory that we should else part always? No, so, here as per the requirement no need.
      Is there any case in your mind where we may need else part as well as per the requirement.. Let us know Plz.

      SECOND
      2- (more important)
      I understand that in Expr transformation, Variables ports are evaluated before output ports and in order of appearance, and setvariable() sets variable to new value and return new value.

      Based on that, for the last record i.e. when Total_Record = v_rec_count becomes true, whatever be the $$TGT_HIT value it get set to nextvalue. Then output port NEXTVAL is set to $$TGT_HIT ( which now contains the new incremented value)

      As a result the last record should flow to next target

      Your understanding about evalution of port and how the expression evaluated and assinged to variable in this solution is correct...(Theoratically it's perfect)
      BUT you are missing one point. :-)

      You need to understand how the worklfows/mapping run?
      We all know that whenever workflow started first it's expanding the value for Variable/Parameter.
      In this Parameter value remains constant through out the program run but variable value may get change.
      (BUT I WISH INFORMATICA DOCUMENTATION SHOULD EXPLAIN OR WRITE FEW LINES ABOUT THIS. WHICH I AM EXPLAINING NOW)
      ---//VARIABLE value may get change during program run but changed value can't be utilized in the current run.---//
      This is the rule of variable value. Actually when workflow starts it's lock the value of variable which it's going to use and though the value get's change during program run but that value will be assigned (means stored in Repository once workflow execution completes)

      ---Don't believe me, sit on the system and execute workflow analyse assigning and utilizing the value of VARIABLE. :-)

      Let us know your analysis if you found some thing different. :-)

      --NO VERBAL SOLUTION.....DO IT PRACTICALLY :-)

      Hope now you got your answer.
      Let me know if still you have doubt.

      Thanks.

      Delete
    2. Unknown28 January 2015 at 10:37

      You are awesome at explaining buddy...:)

      Delete
    3. Suneetha28 September 2017 at 10:11

      This comment has been removed by the author.

      Delete
  3. vicky3 March 2015 at 11:10

    You are explaining awesome

    ReplyDelete
  4. Unknown21 March 2015 at 00:16

    Hi, i was wondering how even if i opt for the truncate table option on the target tables and run the session after the 5th time or more the target table doestnt have twice the data

    ReplyDelete
  5. Unknown21 March 2015 at 08:25

    Hi, i was wondering how even if i opt for the truncate table option on the target tables and run the session after the 5th time or more the target table doestnt have twice the data

    ReplyDelete
    Replies
    1. Unknown22 March 2015 at 13:27

      Hi, Nisha

      You doubt not clear to us. Are you saying that you didn't choose truncate target table and still you are not getting duplicates after 5 and more run.

      Is this your question? Please Clarify then may be I can help you.

      Thanks.

      Delete
    2. Unknown23 March 2015 at 22:34

      Yes i didnt choose the truncate table option and still when i run the session the 5th time onwards, im not getting duplicates.

      Delete
    3. Unknown24 March 2015 at 09:14

      Now it's very confusing for me. What do you mean by not getting duplicate?

      1. Is the data is not loading on 5th run and so on.?
      2. Are you seeing only old data in table 1?
      3. How you are determining the data you see is gets loaded after truncation?

      Could you please drop me mail at hpandey.harsh@gmail.com lets figure this out.

      Delete
  6. Unknown5 May 2015 at 11:29

    this mapping is not working fince since its loading only first target table during first,second, third n fourth session run ....n total record is not getting fetched in aggregator since its totol_rec=count(emp) which gives nothing ...plz clear this doubt
    Thanks

    ReplyDelete
  7. Unknown12 January 2016 at 06:42

    wonderful explanation and it works perfectly

    ReplyDelete
  8. Unknown24 February 2016 at 23:45

    This comment has been removed by the author.

    ReplyDelete
  9. Unknown24 February 2016 at 23:47

    Hi Harsh,

    Could not understand the condition in expression.Please correct me if i am wrong
    Suppose i have 4 records in source.So In expression, first time, i will have value of
    v_rec_count=1, then second run=2, then 3rd run= 3, then 4th run with value =4, means 4 runs, then only the first IIF condition will meet rite?
    and next val at that time would be set to 2?

    ReplyDelete
  10. manjula7 March 2016 at 21:37

    This comment has been removed by the author.

    ReplyDelete
  11. manjula7 March 2016 at 21:40

    Can this be implemented by modulo function?
    Modulo on v_rec_count.
    tgt 1- mod(v_rec_count,0)
    tgt 2- mod(v_rec_count,1)
    tgt 3- mod(v_rec_count,2)
    tgt 4- mod(v_rec_count,3)
    Kindly suggest

    ReplyDelete
  12. Unknown29 April 2016 at 04:13

    Hi Harsha - During the first run of mapping, what is the value of v_rec_count going to be ?

    ReplyDelete
  13. SUN Trainings6 October 2016 at 10:32

    Very Good Blog. The way of explanation is very good. i.e., introducton, therotical explanation and explain with code. blogger responds very good about the topic issues. Keep posting scenarios. Informatica Online Training in Hyderabad Informatica Online Training in USA Informatica Online Training in INDIA

    ReplyDelete
  14. Unknown1 February 2017 at 16:04

    thank you buddy, this is very useful senario .
    i have one dought , each time session run the current varable value source data loaded target , previous loaded file is truncat why this file is truncated and how to overcome this problem . please tel me

    ReplyDelete
  15. Suneetha28 September 2017 at 11:24

    Hi,Harsha.I tried this without using joiner and aggrigator trns i just used exp tr like this Hi, i just used this code i exp tr SETVARIABLE($$TGT_HIT, iif($$TGT_HIT = 4, 0, $$TGT_HIT) + 1)
    its very simple. after this router i feel above one is bit complecated

    ReplyDelete
  16. Twinky29 August 2019 at 02:33

    WHEN I TRIED THIS, I GOT ERROR. PLEASE SUGGEST.

    IIF(O_TOT_COUNT= V_COUNT_NOW AND $$TGT_HIT = 1, SETVARIABLE($$TGT_HIT, 2),
    IIF(O_TOT_COUNT = V_COUNT_NOW AND $$TGT_HIT = 2, SETVARIABLE($$TGT_HIT, 3),
    IIF(O_TOT_COUNT = V_COUNT_NOW AND $$TGT_HIT = 3, SETVARIABLE($$TGT_HIT, 4),
    IIF(O_TOT_COUNT= V_COUNT_NOW AND $$TGT_HIT = 4, SETVARIABLE($$TGT_HIT, 1)))))

    ERROR MESSAGE:MAPPING TE_7002 Transformation Parse Fatal Error; transformation stopped...
    TE_7002 [<> [SETVARIABLE]: first argument can reference to user-defined mapping variable only.
    ... IIF(O_TOT_COUNT= V_COUNT_NOW AND 1 = 1, SETVARIABLE(>>>>1<<<<, 2),
    IIF(O_TOT_COUNT = V_COUNT_NOW AND 1 = 2, SETVARIABLE(1, 3),
    IIF(O_TOT_COUNT = V_COUNT_NOW AND 1 = 3, SETVARIABLE(1, 4),
    IIF(O_TOT_COUNT= V_COUNT_NOW AND 1 = 4, SETVARIABLE(1, 1)))))]

    ReplyDelete
  17. Unknown13 December 2019 at 09:20

    Response from the Integration Service: TE_7002 Transformation Parse Fatal Error; transformation stopped...
    TE_7002 [IIF(Total_Record=V_rec_count AND 1 = 1,SETVARIABLE(1,2), IIF(Total_Record=V_rec_count AND 1 = 2,SETVARIABLE(1,3),IIF(Total_Record=V_rec_count AND 1 = 3,SETVARIABLE(1,4),IIF(Total_Record=V_rec_count AND 1 = 4,SETVARIABLE(1 ,1))))) I am getting above while running the session. Kindly help me.

    ReplyDelete

Subscribe to: Post Comments (Atom)