One Star

Set scd_end of tOracleSCD manually

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!
8 REPLIES
Seventeen Stars

Re: Set scd_end of tOracleSCD manually

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.
One Star

Re: Set scd_end of tOracleSCD manually

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 Smiley Sad
Seventeen Stars

Re: Set scd_end of tOracleSCD manually

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.
One Star

Re: Set scd_end of tOracleSCD manually

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?
Six Stars

Re: Set scd_end of tOracleSCD manually

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.
Seventeen Stars

Re: Set scd_end of tOracleSCD manually

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.
One Star

Re: Set scd_end of tOracleSCD manually

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 Smiley Wink
EDIT: Done https://jira.talendforge.org/browse/TDI-28644
Six Stars

Re: Set scd_end of tOracleSCD manually

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.