topic Re: Set scd_end of tOracleSCD manually in Talend Studio

Set scd_end of tOracleSCD manually

Anonymous — Wed, 22 Jan 2014 13:17:47 GMT

Hi folks,
I have the following problem:
We are about to migrate a database which is used for reporting to a new DWH, which should have a slightly different structure. Until now, one of the Dimension Tables has VALID_FROM and VALID_TO Columns.
Now, while filling the new SCD Table, I'd like to use these columns to fill scd_start and scd_end.
Is there any way to use a input field for scd_end? Can you provide an alternative solution?
Regards!

Re: Set scd_end of tOracleSCD manually

Anonymous — Wed, 22 Jan 2014 21:03:10 GMT

Yes of course, please take not there are comboboxes next to the field scd_start and scd_end.
Next to scd_start (or what ever your field name is) you can choose "Input field".
The component fulfills exactly your needs.

Re: Set scd_end of tOracleSCD manually

Anonymous — Thu, 23 Jan 2014 08:20:10 GMT

Yes of course, please take not there are comboboxes next to the field scd_start and scd_end.
Next to scd_start (or what ever your field name is) you can choose "Input field".
The component fulfills exactly your needs.

Hello jlolling,
sadly that's only true for scd_start. For scd_end one can only choose between NULL or FIXED YEAR. This applies for tOracleSCD and tOracleSCDELT. It is not possible to drag a incoming field into the complement row next to scd_end

Re: Set scd_end of tOracleSCD manually

Anonymous — Thu, 23 Jan 2014 09:42:07 GMT

If you actually have all time slices in your source, you do not need the tOracleSCD component. I guess you have 2 task:
Initial loading of your data (with the already existing time ranges) --> use tOracleOutput
incremental loading with SCD because you detect the change date in your job or in your sources --> tOracleSCD
It does not make sense to get the scd_end date also from the source because everything what the tOracleSCD component supports is already done.

Re: Set scd_end of tOracleSCD manually

Anonymous — Thu, 23 Jan 2014 09:56:32 GMT

If you actually have all time slices in your source, you do not need the tOracleSCD component. I guess you have 2 task:
Initial loading of your data (with the already existing time ranges) --> use tOracleOutput
incremental loading with SCD because you detect the change date in your job or in your sources --> tOracleSCD
It does not make sense to get the scd_end date also from the source because everything what the tOracleSCD component supports is already done.

You are right here, I will do the initial load with a standard tOracleOutput. What is the best way to generate the SurrogateKeys with a tOracleOutput. Saying, using TableMax+1 as a sequence for a Key Field which is not in the input rows?

Re: Set scd_end of tOracleSCD manually

Anonymous — Thu, 23 Jan 2014 15:57:16 GMT

Hi,
If you want to add surrogate keys, you can do so by using a sequence function which is part of Numeric functions. Here you could add the table max as the starting value. The table max could be stored in a variable before being used in the sequence function.

Re: Set scd_end of tOracleSCD manually

Anonymous — Thu, 23 Jan 2014 19:58:31 GMT

The problem with the mentioned sequence function is, every run of the job starts with the same value, that is probably not what some one want to have for a surrogate key.
There are different ways: The easiest one is to define the table column as serial (MySQL) or as an identity column (IBM DB2). The other way is to use a sequence. You could read the sequence value before with a combination if tMap + tOracleInput (as lookup and using a separate connection) or you can define the surrogate key in the SCD editor.

Re: Set scd_end of tOracleSCD manually

Anonymous — Fri, 24 Jan 2014 08:03:12 GMT

Ok, I know how to do it now, thanks. I think for future releases it would be nice, if in an tDbOutput component one would have the possibility to add an PK field which then is automatically mapped to a DB Sequence, Tablemax or whatever. I'll suggest that in Jira
EDIT: Done https://jira.talendforge.org/browse/TDI-28644

Re: Set scd_end of tOracleSCD manually

Anonymous — Fri, 24 Jan 2014 08:17:43 GMT

Hi,
If you want to add surrogate keys, you can do so by using a sequence function which is part of Numeric functions. Here you could add the table max as the starting value. The table max could be stored in a variable before being used in the sequence function.

Here, I was thinking that we could set the max table value of the key in a variable and then use that variable plus one as the initial number in the sequence.
Like : sequence("S1", <variable with the max table key value t be incremented>, 1 <value by which to be incremented>)
So it will start with new value every time table max changes.
It would have to use tmap, and using a variable is when not looking up on the target table.