the Type 6 Slowly Changing Dimensions example does not appear to work correctly. I was able to create a similar one that takes care of getting rid of the cartesian product, but the issue that still remains is that every record that is not the current record gets an updated end_date to match the most recent update. Any thoughts on how to only update the record that is becoming not current? In my scenario, I do not have a current record column. Instead I just derive it based on a NULL end date.
4 Community Answers
Harpreet Singh —
You can use 2 filter after detect changes to handle changed and new records separately. You can keep the Updated end_date as null for new records and only update the end_dt in the filter where you get the changed records.Let me know if that helps.
You can download the sample job and replicate the issue. The solution will most likely be to add the original start_date to the update table component to correctly identify the row that needs to be updated.
You’re absolutely right. This is occurring because the update is only looking at the key which will obviously pick up the not current records. I would recommend adding a separate key into the table which is unique, so maybe an identity primary key and update based on that value.
You’re also right in that you could update based on the start date, providing you know that is not going to be equal to another non current value in the table. If you filter out the rows which are not current at the start of the Transact changes job, I think that you should also be able to add the current flag into the unique key and that would work.
I’ll ensure the documentation is updated. Thanks for alerting us on this one.