switching grain of snapshot fact table

We have built a star schema around a fact table that is currently setup as a Quarterly snapshot.

We have also loaded each quarter starting from Q4 2012 to Q4 2013 (5 loads) into the fact table.

Recently, the business has asked us if we can change the snapshot to monthly rather than quarterly. The only issue is that we cannot go back and get the monthly data before Jan 2014. So we would be able to load the monthly data starting at Jan 2014 going forward.

My question is can it be possible to leave the quarterly data that we already loaded in the fact table and then just begin loading the monthly data starting at Jan 2014 and going forward?


thanks

If you mean load monthly data into a quarterly snapshot table then assuming you have a "quarter" dimension on the fact table the answer is no - as the grain of your new measure doesn't match the grain of your fact table. If instead you have a date dimension at the day level associated to this fact table and are using "last day of the quarter" with a plan to switch to "last day of the month" then technically you could do this: but it's probably a bad idea.

I would continue to load your quarterly snapshot fact (because if you stop you can be sure your users will ask for it back in a few months) and add a new monthly snapshot table. As the ETL process will be very similar to to quarterly load it shouldn't take much effort to build.

Regards,

No problem.

Hi,

if the requirement is to have data captured on a monthly basis and seeing that you have laoded data only for 5 quarters you can do the following.

Change the grain for the fact table to monthly .
Tell the users that the data prior to Jan 2014 will not give a correct picture and should avoid using that data for monthly (if that works for them).


thanks

Himanshu

Where are the atomic facts to support the aggregate fact?

thank you for the replies so far.

The Fact table is loaded from a Quarterly snapshot from the source system, which is a Policy system that has total premium. So I guess it's not a truly additive fact by quarter, but instead a view of what the total premium was at each quarter.

We use a day Date dimension and load the last day of each quarter into the fact table. I can see where we could create a new table for the monthly load, but then we would have to do some joining on the reporting side since the users will want to see both sets of data. My guess is that we will tell them anything prior to Jan 2014 will be quarterly only, anything Jan 2014 going forward can be reported on monthly.

This just my opinion so feel free to ignore or disagree with it...
You shouldn't need to tell your users the time period for data as this should be obvious from the design and the objects exposed to them. I know that people often seem to to use a Date Dimension (with a day grain) when creating weekly/monthly/... aggregate fact tables - picking some arbitrary date such as the last day in the week/month to assign to the fact - but I've never liked this as a design, as you are associating a dimension to a fact that has a different grain to the fact. You end up having a number of date attributes connected to your fact that are incorrect or incompatible with the fact.
I've always thought it is a much cleaner design to roll up you date dimension to week/month/... dimensions and then associate the correctly grained "date" dimension to each fact.
To quote from Kimball's DM book:
"Obviously, you can't simply use the same date dimension table for daily and monthly fact tables because of the difference in rollup granularity."