topic Re: Help for custom SCD component in Talend Studio

Help for custom SCD component

gorotman — Sat, 16 Nov 2024 11:43:02 GMT

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.

Re: Help for custom SCD component

bkar81 — Wed, 19 Mar 2014 10:50:37 GMT

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.

Re: Help for custom SCD component

gorotman — Wed, 19 Mar 2014 16:06:24 GMT

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.

Re: Help for custom SCD component

bkar81 — Wed, 19 Mar 2014 16:20:52 GMT

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.

Re: Help for custom SCD component

Anonymous — Wed, 19 Mar 2014 18:38:15 GMT

@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.

Re: Help for custom SCD component

gorotman — Thu, 20 Mar 2014 09:17:08 GMT

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.

Re: Help for custom SCD component

gorotman — Mon, 24 Mar 2014 09:32:56 GMT

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..... =(

Re: Help for custom SCD component

gorotman — Thu, 08 May 2014 10:28:07 GMT

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