Help for custom SCD component

[gorotman]

Hi all,
I would try to create a custom SCD component (for MSSQL, derived from current tMSSqlSCD).
I need to add some features (these are examples of my needs : audit column, fixed end date ).
Anyone knows how I can modify the "Editor SCD" form? I'm talking about the form where I set fields type, surrogate key column, etc etc.....
I added custom code to component source, but I can't find how to modify this form to use custom properties.
Any help please?
Thank's in advance.

[bkar81]

Hi,
Please correct me if I am wrong...
I saw the link https://jira.talendforge.org/browse/TDI-13479
If these are the columns which will insert/update irrespective of the SCD, then just let me know about this...
If the other fields are into SCD types, then definitely either there will be a row insert / update. So if you put these columns in Type 1, then when a new row is inserted / updated, the latest data will be present right.
I don't know whether I understood your requirement or not. Its just my thought.

[gorotman]

I'll try to better explain my needs (first of all: sorry for my incorrect english ).
The audit column may be the job run id. I use it to know when a row was inserted or modified (it refers to an history table of jobs); it is useful in case of errors, analyze, etc..etc..
So this filed must be upgraded only when this row is inserted or modified in according to versioning rules (type2, type1 and so).
But job run id changes every times (it is an auto increment data), so if I use type1, this filed would be upgraded by every execution, if data source was changed or not. Also it changes all history or I have to set ONLY_UPDATE_ACTIVE check but I lost the history correction feature (male/female or birthdate....).
thank's and bye.

[bkar81]

Sorry, If I were in the situation to handle this kind of scenario, I would have a job_config table /config file something like that which would store the last job_run_id+1 in reference to each job. And everytime while the job runs, I will update this value to job_run_id column in tMap and keep it as Type 1 column in the SCD component, so even if other columns wouldn't change, this will get updated.
Sorry, I couldn't think of any other work around as of now and I don't know whether there is a straight forward possibility available in a SCD component.

[Anonymous]

@gorotman: You audit column is a very common pattern in DWH scenarios. The current SCD component can handle this well. You read your source table and lead the flow to a tMap. In the tMap you add your audit column and in the SCD column you use the audit column for Type 0 columns. This way every new dataset gets it value for the audit column.
We usually create a long (bigint) value for every single job run and put this ID to every dataset created by the this job. This is our way to support data lineage.
By the way I have published the components which does the job for us in Talend Exchange:
tJobInstanceStart, tJobInstanceEnd and tJobDataRangeScanner
Conclusion: From my point of view you do not need to create a new SCD component.

[gorotman]

hi bkar81, hi jlolling,
Sorry but I'm not sure, I think that neither type0 nor type1 are correct.
I've attached an example with mixed type fields (I've also attached the scd form).
After the second run, only two rows have "dw_id_loading" (my audit column)=2: rows added by type2 rule.
Really job updated rows 2,3 and 4, but it didn't changed "dw_id_loading" field.
So type0 does not change all rows inserted/updated.
Even, if you see field "note" (type1) in source row "02": I modified only this value, but it is changed in target table.
Because job run id changes at every execution, all rows would be upgraded with new "dw_id_loading" .
That's incorrect for my field 😕
Thank's all, but I still thinking that a custom (or official? ) component is required.
P.S. @jlolling: I also maked similar jobs for my project, but yours are very very intersting.

[gorotman]

An update:
the "Editor SCD" form code is in "org.talend.designer.scd_5.4.1.r111943.jar" (for 5.4.1), located in the "plugins" folder.
First impression is that creating a custom SCD component will be very very expansive..... =(

[gorotman]

Update:
I created my custom component for Slowly Changing Dimension. I added it in talend exchange.
If it's useful to someone this is the link to it.


bye