Unsure about relationship with Slowly Changing dimensions.

View previous topic View next topic Go down

Unsure about relationship with Slowly Changing dimensions.

Post  PugMaster on Wed Jul 07, 2010 6:04 am

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

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Unsure about relationship with Slowly Changing dimensions.

Post  BoxesAndLines on Wed Jul 07, 2010 8:54 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Unsure about relationship with Slowly Changing dimensions.

Post  PugMaster on Wed Jul 07, 2010 9:18 am

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.


PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Unsure about relationship with Slowly Changing dimensions.

Post  ngalemmo on Wed Jul 07, 2010 10:53 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Unsure about relationship with Slowly Changing dimensions.

Post  PugMaster on Wed Jul 07, 2010 11:59 am

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.

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Unsure about relationship with Slowly Changing dimensions.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum