Design Fact Table in Dimensional Modeling with Multiple Grain

Question

Design Fact Table in Dimensional Modeling with Multiple Grain

abraham.sse

SSC Journeyman

Points: 79
More actions
December 13, 2015 at 8:49 pm

#323072

I have got a situation to create dimensional data model to handle multiple business analysis question. 1)activity count 2)activity count on month level
We have designed activity fact table following way, I have simplified to get idea around this use case
Activity Fact Table
Act_SKey|Act_ID_Skey|Act_Eff_Date|Act_End_Date|Activity_Count
1000|21|8/10/2012|10/10/2012|1
1001|23|7/10/2012|8/16/2012|1
1002|25|5/10/2012|6/10/2012|1
This fact satisfies the business query to calculate activity count. with out changing the grain of the above table I want to get activity count on a month level. here there is a trick, the count specified above is same for each month between Act_Eff_Date_Skey and Act_End_Date_Skey. Read specifics below
Act_ID|5/12|6/12|7/12|8/12|9/12|10/12
21|0|0|0|1|1|1
23|0|0|1|1|0|0
25|1|1|0|0|0|0
Tot |1|1|1|2|1|1
Please help me with How I can design dimensional model to satisfy the business case to calculate activity on month level?
With the above data I should create a dimensional model to answer Activity count for the month 8/12 as 2 and etc

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

I guess the first question is why your fact table has Effective/Termination dates instead of the actual date of the activity?

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

Kimball recommends that facts of different grain, like event level facts rolled up to month level, should be contained in a separate fact table. But it seems this month rollup can be accomplished using a simple aggregate sql query with GROUP BY clause or drilling up in an OLAP viewer.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Eric M Russell (12/15/2015)
Kimball recommends that facts of different grain, like event level facts rolled up to month level, should be contained in a separate fact table. But it seems this month rollup can be accomplished using a simple aggregate sql query with GROUP BY clause or drilling up in an OLAP viewer.

Agree

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]