Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

slowly changing dimensions

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

Matillion Agent  

Harpreet Singh —

Hi Bryan,

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.


Bryan Boutwell —

That is what it is already doing. Here is the link to the sample on the Matillion website.

https://redshiftsupport.matillion.com/customer/en/portal/articles/2122023-type-6-slowly-changing-dimensions-example

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.


Matillion Agent  

Laura Malins —

Hi Bryan

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.

Thanks
Laura


Bryan Boutwell —

no prob.
ya, if there is more than 1 record with the same start_date then there is probably a data issue since only 1 record should be applicable at any given time throughout history.

Post Your Community Answer

To add an answer please login