Design Fact Table in Dimensional Modeling with Multiple Grain

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

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

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?

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 .

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.