topic Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD] in Talend Studio

[resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Tue, 04 Oct 2016 11:45:09 GMT

Hi devs,
Can someone please help me.
Right now i'm trying to build a job which basically is a custom SCD type 2 for a dimension table. I have my own reason why I'm not using SCD Component, so please stick with this method.

Almost everything looks to be okay, but I have 1 problem. There are two output which are my main concern [look at the picture], Exist_record_new and Exist_record_New_UpdatePrevious. [please don't mind about the weird naming]

Exist_record_new is the line for insert new updated attribute in dimension, and put the Active Flag = 1. The 'Action on Data' is Insert.

Exist_record_New_UpdatePrevious is the line for update the active flag become 0 and put the End Date for the record which has updated attribute. The 'Action on Data' is Update, with PrimaryKey.

The problem is, when the new updated attribute which contain with Active Flag = 1 inserted to the table with txxxOutput, at the same time the other txxxOutput update the active Flag become = 0.
Later in the table there will be 2 data for 1 same BranchID, for the new updated Attribute and old one, and both has Active = 0.

Question: Is there any specific way to set, let say, the UPDATE output RUN FIRST, and INSERT COMES AFTER.
So the new inserted one will not be overwritten by the update component.

Thanks in advance devs!!

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Wed, 05 Oct 2016 05:32:07 GMT

Are you sure that Active Flag output of Exist_record_new is correct?
you are already making sure that actions are happening in the correct order in tMap through Main Order (check the out bound connections of tMap).
Can you please confirm on the output of the tMap function?

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Wed, 05 Oct 2016 05:54:26 GMT

@Verma
Firstly, Thank you for your respond.

So basically the Expression filter of Exist_record_New and Exist_Record_New_UpdatePrev are using the same expression
The expression like : !row1.AttributeABC.equals(row6.AttributeABC), this is to find if there was any changes on the attribute or not.
And for Exist_record_New in tMap, I hardcoded the Active Flag value to 1,
and for Exist_Record_New_UpdatePrev, I hardcoded the active Flag value to 0.

Everything supposed to be fine. My only problem is, since there are 2 outputs from 1 same tMap, how do I set so the A output run first, and B output run after the A.
I need a way (or component maybe), to make the insert component to wait until the update done, and run later.
Please kindly your help.

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Wed, 05 Oct 2016 06:36:21 GMT

you are already making sure that actions are happening in the correct order in tMap through Main Order (check the out bound connections of tMap).

I will advice 2 things:
1. Take the outut of Existing record update and insert in tLowRow to make sure that output is correct (i.e. Active flag)
2. If you are still facing the issue, then put the contents of ExistingInputInsert and InsertNew in a temp table first and then moving them to your target table. You can have component of moving them to your target table on SubjobOK of reading from source.

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Wed, 05 Oct 2016 10:50:45 GMT

@Verma,
1. Yes, I checked it already with using tlogrow. Active Flag for Existing_Record_New is 1, and the other one is 0.

2. Yes actually we can do that way. Put the data first into the stg table. To move it into the real dimension table put subjob ok after the update component.

But my concern will be, if let say I only had 3 dimension tables it might be okay.
What if I have 30 dimension tables? That approach will lead me to build another 30 stg table, because each of dim table has different columns.

Is there any temp or in-memory table component in Talend, so I don't need to create any physical table as the stg?

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

cterenzi — Wed, 05 Oct 2016 17:29:47 GMT

The order of a tMap's outputs are determined by the order they appear in the map editor. You can re-order them using the Up and Down arrows in the map editor (see screenshot).

Re: [resolved] Set Sequence Process between some of tMap output [Custom SCD]

Anonymous — Thu, 06 Oct 2016 03:27:26 GMT

@cterenzi
Wow excellent!! A simple thing to set the ordering.
That is what actually I'm looking for..

Thanks guys.