switching grain of snapshot fact table

View previous topic View next topic Go down

switching grain of snapshot fact table

Post  scabral on Thu Mar 06, 2014 11:11 am

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

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: switching grain of snapshot fact table

Post  nick_white on Thu Mar 06, 2014 11:51 am

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,

nick_white

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

View user profile

Back to top Go down

Re: switching grain of snapshot fact table

Post  BoxesAndLines on Thu Mar 06, 2014 11:57 am

No problem.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re:switching grain of snapshot fact table

Post  hkandpal on Thu Mar 06, 2014 12:18 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: switching grain of snapshot fact table

Post  ngalemmo on Thu Mar 06, 2014 12:19 pm

Where are the atomic facts to support the aggregate fact?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: switching grain of snapshot fact table

Post  scabral on Thu Mar 06, 2014 12:52 pm

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.

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: switching grain of snapshot fact table

Post  nick_white on Fri Mar 07, 2014 4:42 am

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."

nick_white

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

View user profile

Back to top Go down

Re: switching grain of snapshot fact table

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