Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Surrogate key for different granularities

2 posters

Go down

Surrogate key for different granularities Empty Surrogate key for different granularities

Post  saeidziaei 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?
saeidziaei
saeidziaei

Posts : 2
Join date : 2009-11-20

Back to top Go down

Surrogate key for different granularities Empty Re: Surrogate key for different granularities

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

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

http://aginity.com

Back to top Go down

Surrogate key for different granularities Empty Re: Surrogate key for different granularities

Post  saeidziaei 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
saeidziaei
saeidziaei

Posts : 2
Join date : 2009-11-20

Back to top Go down

Surrogate key for different granularities Empty Re: Surrogate key for different granularities

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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