Unsure about relationship with Slowly Changing dimensions.

Hi

Sorry datawarehouse newbie question! I have many years experience with relational databases but my head is not yet around the dimensional model. I am not sure how to maintain the relationship between Fact and Type 2 Dimension.

i.e if a new row is inserted into a dimension table as an updated version of a previous row how is the relationship to the fact table maintained. Do you need to insert a new row in the fact table also or is there a better way to maintain this relationship via keys?

Thanks in advance, and once again sorry for what is probably a dumb question.

Cheers, Paul

All new facts will get the new version of the dimension. The existing fact rows do not change. This is how history is maintained.

Thanks for your reply, are you saying that i should be reloading all the facts periodically (daily etc) not sure i have my head around this correctly.

in my example i have a Policy Fact table and one of my dimensions is Customer, if the customer address changes then my fact table still relates to the old row with the old address.

I understand that when the fact table is reloaded then the new dimesion row will take effect, should the new dimension row trigger and insert into the fact table or should i be reloading all facts on a schedule, if so that would cretae duplicates for rows with no change to there dimensions.

Hope that makes sense and thankyou for your time.

The purpose of a Type 2 dimension table is to maintain dimensional context at the point in time of the fact. When an updated row is inserted into a Type 2 dimension, only facts added after that point in time will reference the new row. The old facts continue to reference the original row and, if nothing else is done, will report under the old attributes. Fact table keys are not changed, nor are old facts reloaded.

If you have a Type 2 dimension and you wish to report current attributes for all facts, you must do a self-join on the dimension table to retrive the current row based on the natural key, filtering on the most current version. Usually the dimension table includes a current flag column to make this easy.

The self join may be avoided using other techniques. Either maintain both a Type 1 and a Type 2 version of the dimension or implement a 'Type 1 key' in the Type 2 dimension, where the key value remains constant across all versions of the rows. In either case, you maintain two foreign keys in the fact table. In the case of using a type 1 key in a type 2 table, you join on the type 1 key and filter where the current flag is true.

Excellent explaination that makes perfect sense, so if the data in the fact row changes then you insert a new row into the fact table and that will reference the new dimension row. thankyou for your time.