Slowly changing dimensions: Only update field when another changes.
Hi,
I'm having the following problem in one of my jobs at the moment.
For my customer table I have been using slowly changing dimensions for a while now.
Recently I have been asked to add a loadno to my table. (to keep track of when a customer was uploaded and in what file)
What I want is for the loadno to change if something changes in the record. For example if a customer name changes I want the LoadNo to change to the current Loadno. And if nothing has changed to the file I want the LoadNo to be unchanged.
What I'm doing right now:
- I'm reading in customer files and i'm doing a couple of lookups to see if all the data is correct. (existing Sales Representative etc.)
Job Looks Like: tFileinputdelimited --> tMap --> tMssqlSCD
- I add the Loadno in the tmap component using a context variable. Because of this the LoadNo is always different then what I have in my table.
I have tried following methods, but both unsuccesfull:
? When I put my LoadNo column into my type 1 fields: All customers that were included in today's file have their LoadNo changed to todays Loadno. (even though most of them were unchanged)
? When I leave the LoadNo column into unused: The record gets updates if a type 1 field was changed. But the LoadNo doesn't get updated.
Is there a way to do this using the tMssqlSCD settings? Or do I have to change my tMap component and add my LoadNo on another way so SCD doesn't think the record is changed?
I hope I have been clear. If something isn't clear feel free too ask and i'll try to be more clear.
Thanks in advance,
Mario De Pauw
I've implemented SCD logic in Talend using the tMap in cases where I needed to pass certain fields through the SCD component without checking their status i.e. untouched.
Though this is a different example (for updating a fact table), the logic is same - using a flag to indicate what records should be updated or updated. The only thing I don't show in this example is the changing of the SCD flags (active, start and end dates)...
http://www.talendforge.org/forum/viewtopic.php?pid=128716#p128716
can any one help me to solve the following scenario ? There is an Employee dimension with Emp id, first name, last name, manager name and department. You should have 2 schema or database in Postgres. First will be your source, 2nd will be your target (DWH). So initially you will have let’s say 10 rows in the source. You will load those 10 rows. Now let’s say there is a change in the source and 2 employee’s manager is changed. My target table should be able to capture these change, along with the time it was changed. So basically it is called SCD2 implementation. At the end my Source will have 10 rows and target 12 rows.
