Slowly resizing fact table updates
This is a bit difficult to find by keywords, so I apologize if this question exists.
Let's say I have a standard size of 2 slowly resizing, modeled by the Startdate and Enddate columns. Record with NULL Enddate is the current version of the measurement record.
I get it pretty simple when I find myself inserting a fact table from the original data. The new fact table record is simply inserted into the most recent dimension record based on the match between the business key AND the dimension record, where Enddate = NULL.
I have a little problem with what to do when there is an update to a measure in the source system, which would equal an update rather than an insert in my fact table. It seems I only have a business key and an existing record in the fact table might point to a previous version of the dimension record. I don't know how to take the correct surrogate key from the dimension and update the fact table.
I can provide more details if needed.
Thanks in advance.
source to share
Do you have a date insert
or create
in the original table? You can use this for
select * from dim where src_dt
between dim.startdate and dim.enddate and keys = src.keys
and return the correct measurement string. if you are using SCD.
When you do a search, you should use dates along with natural keys to get the correct size row, rather than selecting the most recent dim row for both inserts and updates.
source to share