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
Thanks for your quick response. I have tried it but the LoadNo remains unchanged (Still gives the old LoadNo) where the Customer name has changed (this is a type 1 field).
Dosnt look like the SCD components support this type of update. Since you're doing type 1 updates, if your dimension table is not very large, you can replace your SCD component with a tMap that accomplishes the same logic (two output tables-- one for updates one for inserts) by joining your input data to the dim table.
How can I achieve this? By using the additional columns in the tmssqloutput and adding the LoadNo column there?
I tried using the field options and disabling Loadno from updating but that doesn't seem to be the way to do it.
Or do I have to do this in tMap?
I have the same problem - just with the difference that I work with a load_date instead of a load_no. Obviously, the SCD components are not able to handle this situation. It is not a proper solution but work-around when you try to achieve your goal with a tMap instead of a SCD component. I'll open a bug report.
Is it for example possible to know all the rows that were updated and then change the loadno after they were updated? (when using a tmssqloutput because i'm having the same problem for my product groups who don't use SCD)
Try to work with the versioning attribute of type "version" in the SCD Editor: 1) Create a attribute called LOAD_NO (Integer) in your input schema. 2) In the SCD Editor: Write LOAD_NO into the name field of the versioning attribute of type "version" and check the check box on the left hand side (see attached screenshot)
