Design Fact Table in Dimensional Modeling with Multiple Grain

View previous topic View next topic Go down

Design Fact Table in Dimensional Modeling with Multiple Grain

Post  abibets on Sun Dec 13, 2015 9:59 pm

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

abibets

Posts : 1
Join date : 2015-12-13

View user profile

Back to top Go down

Re: Design Fact Table in Dimensional Modeling with Multiple Grain

Post  nick_white on Mon Dec 14, 2015 5:51 am

Hi,
Act_Eff_Date and Act_End_Date should not be dates in your fact table they should be surrogate keys to your date dimension. Your data dimension will have a month attribute so summarising by month is a simple Group By SQL query

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Design Fact Table in Dimensional Modeling with Multiple Grain

Post  nick_white on Mon Dec 14, 2015 7:52 am

Also, what's the difference between Act_SKey and Act_ID_Skey? If Act_Id is an attribute of the Activity then shouldn't it be an attribute on the Activity Dim and be referenced by Act_SKey?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Design Fact Table in Dimensional Modeling with Multiple Grain

Post  zoom on Mon Dec 14, 2015 9:56 am

I think your question is more on how to write a SQL then to design a dim table. Please provide more explanation if you need help on table design.
You did not mention which RDBMS you are using so I am using Oracle to explain. In Oracle you can count months between dates by using MONTHS_BETWEEN function.
Using your example of 1000|21|8/10/2012|10/10/2012|1, the MONTHS_BETWEEN function result is 2. This is what you want , right?

Select MONTHS_BETWEEN(Act_End_Date,Act_Eff_Date)
FROM "activity fact table"
WHERE Act_SKey = 1000

Based on your RDBMS, do a search on how to count months between dates and you can get your answer .

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Design Fact Table in Dimensional Modeling with Multiple Grain

Post  ngalemmo on Mon Dec 14, 2015 10:49 am

Yes, the table is fine. It is a matter of how to construct the query.

If you need a report of # activities by month, it is best if you have a month dimension table that contains the first and last date of the month.

Your query would join to this table using the dates to locate any month that is contained in the effective range of the activity. In other words:

act_eff_date < month_end_date and act_end_date > month_start_date

This will return a joined row for every month the activity occurred.

You can do a similar thing with a date level dimension, but the query gets complicated as you need to average (or max) by act_skey and month before you can sum the results.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Design Fact Table in Dimensional Modeling with Multiple Grain

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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