calltomadhu

 Profile Answers by calltomadhu 

• Nov 8th, 2006

 

hi,

I am not sure but I hope this would help you.

go through target load plan in session properties.

thanks

madhu

dwh.ramu

 Profile 

• Nov 19th, 2006

 

   Hi this is ram responding to your question .

    You do this by target load order plan at session level, and the data will populate in the order in which u place the target sources in mapping ,ex first if you places the first target then 1st target data populated ,then second and then the third this the sequence.

Srinivas

• Nov 22nd, 2006

 

Hi,

First of all i am appreciating you for posting such a good question.

Target Load order will work only for a single run. But here the we have to control the data flow accross the runs. For that we have cature the iteration number in flat file, and doing look up on that flat file we have to route the data load flow.

You mapping will be like this:

                         Flat File (Look up) ---->Expression to

                              |                          calculate iteration no --> Flat File

                              V

Source   - - >       Router  - - >  Target1

                                              Target2

                                              Target3

If we get the Flat File value as 1 we will load Target1 or if it is 2 we will load Target2 and so on. In Target load Option, we need to take care like Flat File needs to loaded only after the Target1 or 2 or 3. Why because if the process got failed it will not update the flat file content.

I am unable to draw the picture here. I thought this is one of the solution. I can't say this is the best solution. If any one find better solution. Please do let me know.

Thanks

Srinivas

venkat

• Dec 14th, 2006

 

Hi,

You can use mapping parameter for this,pass the value from param file to parameter(like 1,2,3..) and use router trans for direct the records to target.

Regards,

Venkat

Saravanan

• Dec 31st, 2006

 

declare a workflow variable like counter assign default variable =1

each time we run the workflow just increment variable like counter + 1.

 if your are running first time, check the counter value mod 3 you will be getting 1 then load first target.

during the second time we will get (counter mod 3 )=2 then load the data into second target table.

during the thrid time we will get (counter mod 3 )=0 then load the data into third target table.

repository server automatically update the counter value in repository when it is successfully finished. while executing second time repository server read the recent value from repository

i think it may help you.

Tapas

• Oct 6th, 2007

 

This is intesresting. Although it is an old question, I still thought of provide some input:

You can use the 3 target tables as lookup. If an incoming row from the file is in the target, set flags accordingly. Then next step you evaluate the flags and then use a router.

if in target 1, set flag1=Y, else N
if in target2, set flag2=Y else N
if in target3, set flag3=Y else N

Now if flag1=N, route to target 1
if flag1=Y and flag2=N, route to target 2
if flag1=Y, flag2=Y and flag3=N route to target3

Of couse this is only if you are inserting rows into the targets. If you have updates, then of course the logic gets complicated because you have to check for changed values. But the concept would still be the same.

Sanjay Vijayant

• Sep 8th, 2011

 

First create a sequence generator where startwith=1 and maxvalue=3, enable the option "cycle". Make sure cache value is set to 0.

In the data flow use expression to collect dataflow ports and add a new port (iteration_no) to collect sequence.nextval. pass this data to router where you need to create 3 groups, first group condition iteration_no=1, second group condition iteration_no=2 and third group condition iteration_no=3. This way each session run will be loading first, second and third target instance in cyclic mode.

Binitha

• Feb 23rd, 2012

 

• Create mapping variable, say $$Runcount
• Use sequence genereator to create unique values for the records, use reset property in the sequence generator
• Use expression transformation
o Create an outport,
o_Rowcount = IIF($$Runcount >3,SETVARIABLE($$Runcount,0),IIF(ISNULL($$Runcount),SETVARIABLE($$Runcount,0),SETVARIABLE($$Runcount,$$Runcount+1)))
• Use router transformation to group into 4 different targets
o Group1: o_Runcount=1 and NEXTVAL<=5
o Group2: o_Runcount=2 and NEXTVAL<=10
o Group3: o_Runcount=3 and NEXTVAL<=15
o Group4: o_Runcount=4 and NEXTVAL<=20
• Connect each group to 4 different target tables.

Pawan Singh

• Jun 14th, 2013

 

define one parameter with name $$Target_LOAD in parameter file and use that variable in the expression transformation with Target_load, after expression use router transformation , in Router create three groups and then put condition like $$Target_load=1 2 and Target will be updated based on the value of $$Target_Load

rohit

• Jul 4th, 2014

 

Hi You can use the Seq generator and opt for the cyclle option,increment it by one by giving the start value also as one.Then connect the next val port to a expression and then to a router .In router make three ports as NEXTVAL = 1,NEXTVAL = 2 and NEXT VAL = 3 and then connect all the three targets accordingly.this will help u load the targets one at a time.

Mahendra Rajpoot

 Profile Answers by Mahendra Rajpoot 

• Sep 16th, 2014

 

Hi,

If think this approach will load 1st record in 1st table, 2nd record in 2nd table, 3rd record in 3rd table, 4th again in 1st table.

But the question is to load all data in 1st WF run in 1st table not record wise. So I think we need to create mapping level variable and store the WF runs in this variable. And then router to do mod of this variable to load into 3 tables.

Please correct if this is not right?

Known

• Sep 29th, 2014

 

can we use target load plan property in session here?

Venkateswara Rao

• Oct 8th, 2014

 

Have a workflow variable defined and assign the variable as Var = IIF(Var=3,0,Var+1) .. Have a mapping variable also defined and use the pre session assignment to assign the workflow variable to mapping variable. Have filter or outer with map_var = 1, map_var =2, map_var =3... so this is achieved. Each time the workflow variable is incremented till it reaches 3 ...

gaja

• Oct 14th, 2014

 

no , target load plan work for single session only .
it determine the order of targets to load in single session only

SURESH

• Dec 2nd, 2014

 

I hope this is answer is very easy. Using following steps:
1. First create source table and 3 target tables
2. Then create the expression transformation add extra column seq_no and same column add to the 3 target tables also..
3. Third one is generate the sequence transformation then connect the NEXT_value to expression transformation seq_no column.
4. In sequence transformation current value 1 and starting value 1 and end value 3 and click to the cyclic button.
5. Create the router transformation...all the expression records dropped to router and then divide the 3 groups. First and second and third groups.
6. Group filter condition are
first group: seq_no=1
second group:seq_no=2
third group: seq_no=3 and save it..
7. 3 group connect to the three target tables and run the mapping.

jpn

• Dec 12th, 2014

 

Try using session/mapping variable concept to store the number of execution and according to the execution value load the target accordingly

BRAHMAIAH

 Profile Answers by BRAHMAIAH Questions by BRAHMAIAH

• Dec 30th, 2014

 

hI FOLKS,
THROUGH MAPPING VARIABLE WE CAN ACHIEVE THIS SCENARIO,
It is very simple first we have to define the mapping variable $$var (count) default val=0
SQ-->EXP->ROUTER->TGT1,TGT2,TGT3
EXP--->O_FLAG(O) DATATYPE(NUMBER)=SETVARIABLE($$VAR,IIF($$VAR=3,0,$$VAR)+1)
ROUTER-->CREATE MULTIPLE GROUPS--->G1=>$$VAR=0,G2=>$$VAR=1,G3=>$$VAR=2
AFTER COMPLETION OF 3 RUNS WE HAVE TO RESET THE PERSISTENT VALUE AT WORKFLOW LEVEL

Arun

• Jan 11th, 2015

 

we cant use the method u mentioned above . Its for routing the rows inside a file and its not serving the purpose of loading the target table sequentially after every run

Rakesh

• Feb 9th, 2015

 

your above logic is perfect..but instead resting the variable at workflow level you can add these condition in first router group => MOD(o_Flag,3) = 1 2nd router group =>MOD(o_Flag,3) = 2 3rd router group =>MOD(o_Flag,3) = 0
So irrespective of current value your mapping will always load first,second and 3rd table in sequence with each run

sum

• Oct 8th, 2015

 

Define one variable $$CNT in mapping and assign its initial value as 1 in parameter file
Take an expression and do below coding
count =$$CNT
INC_COUNT=$$CNT+1
O_INC_CNT=SETVARIABLE($$CNT,INC_COUNT)
Add router with 3 groups as count=1,count=2,count=3 and join output from router to each target and after session add post script to store this variable value to parameter file and post session assignment to assign this variable to workflow variable.
Each time variable value is increased and stored in parameter file and it will take next increased value.