Surrogate key for different granularities

View previous topic View next topic Go down

Surrogate key for different granularities

Post  saeidziaei on Sat Nov 21, 2009 9:28 pm

Hi
In a life insurance context, I have products and benefits tables and also another table that specifies their relation. I thought combining them all in a single dimension is not a bad idea but I will have different facts which should point to either benefit or product. Which surrogate key should I use in my fact tables?

I suppose this most be very common problem but have not been able to find an answer on the net. Like if you had sale fact on day grain and budget on the month grain, which surrogate key would you use in the budget fact table?
avatar
saeidziaei

Posts : 2
Join date : 2009-11-20

View user profile

Back to top Go down

Re: Surrogate key for different granularities

Post  ngalemmo on Mon Nov 23, 2009 1:42 pm

"I will have different facts which should point to either benefit or product. " confuses me... it sounds like you are trying to fit different types (or grains) of facts in the same table. If you have facts that only reference a product or facts that only reference a benefit this is information at a different grain and should reside in a separate fact tables.

To your day/month question, my preference is to have a single date dimension table with one row in a month designated as the 'month' row for monthly level facts (you can use the first day of the month, last day of the month or whichever date make sense). Same for quarterly and yearly summaries. There are others who suggest separate dimension tables (a date dimension, month dimension, etc...) but I find this cumbersome and makes it difficult to combine the two facts along common attributes in some BI tools.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Surrogate key for different granularities

Post  saeidziaei on Mon Nov 23, 2009 6:18 pm

I should have said "I will have 2 groups of fact tables; one at the product level and the other at the benefit level". Sorry for not being precise enough. Needless to say that I got my answer by reading your comments on the day-month issue because my problem is of exactly the same nature.

-Thanks a lot
--Saeid
avatar
saeidziaei

Posts : 2
Join date : 2009-11-20

View user profile

Back to top Go down

Re: Surrogate key for different granularities

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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