Surrogate key for different granularities
2 posters
Page 1 of 1
Surrogate key for different granularities
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?
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- Posts : 2
Join date : 2009-11-20
Re: Surrogate key for different granularities
"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.
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.
Re: Surrogate key for different granularities
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
-Thanks a lot
--Saeid
saeidziaei- Posts : 2
Join date : 2009-11-20
Similar topics
» Snowflake a dimension if facts are at different granularities?
» Partially vs Fully denormalized dimension and different facts granularities
» Should I use the surrogate key?
» Fact Indexing -SQL Server 2008
» No Surrogate keys
» Partially vs Fully denormalized dimension and different facts granularities
» Should I use the surrogate key?
» Fact Indexing -SQL Server 2008
» No Surrogate keys
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|