March 2, 2006 at 5:48 am
I am a newbie in BI and have the following design question:
I have a scenario where I have a Sales fact table linked to a customer dimension. The customers some customers attributes might change and I would like to keep history of those attributes, such as Marital Status.
One way is to store the Marital Status as attribute in the customer dimension and enter multiple records for each customer whenever the marital status changes, but I should then add date and status fields to the customer dimension.
Another way is to store the Marital Status as a separate dimension and link it directly to the fact table. While maintaining only one record for each unique customer in the customer dimension, and in this case no Date and Status fields are needed on customer.
What is the best way of implementing this scenario? Are there any pros cons for each one, Or Maybe there is a better way of solving it?
And suppose I use the first method where multiple customer records are kept, while processing to get the sales done for people that are married, some processing should be done to only consider each customer once (Only Status is Active). This is through MDX I suppose.
I am sure this issue is typical and basic but just wanted to get some guidelines.
Appreciate your help,
Grace
March 3, 2006 at 9:48 am
Have you looked at Kimball's "Slowly Changing Dimension" techniques? Its in "The Data Warehouse Toolkit".
Hope this helps
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply