Mixed grain fact data

View previous topic View next topic Go down

Mixed grain fact data

Post  vr23 on Thu Feb 28, 2013 1:25 pm

We are getting data at different levels from the source system.The data could be
at different grains.
For instance

The three dimensions that we have are PRODUCT/STORE/DATE. We have hierarchies across these
three dimensions

product -> style -> sub class -> class -> dept

day -> week -> month -> qtr -> season -> year

store -> district -> region

The source data could be at product/day/store level or style/region/week level.or..so far and so forth.

The users want to report on this data across multiple levels(roll up) using BI tool..How do i approach this from a modeling perspective?
Have multiple fact tables representing each level...The problem with that is ..if the user wants to get the report at dept/week level, the BI tool
needs to rollup data from different fact tables and present the data..Any thoughs?




vr23

Posts : 2
Join date : 2013-02-28

View user profile

Back to top Go down

Re: Mixed grain fact data

Post  vr23 on Thu Feb 28, 2013 9:26 pm

anybody?

vr23

Posts : 2
Join date : 2013-02-28

View user profile

Back to top Go down

Re: Mixed grain fact data

Post  BoxesAndLines on Thu Feb 28, 2013 10:04 pm

One of the fundamental concepts is not to mix grains in the fact table. Another concept is to store the facts at the lowest grain. When faced with metrics at various levels, I start at the lowest level and then create aggregate fact tables until I have satisfied all of the metrics. For example, if I have a metric at the Order Line level, I can aggregate the Order line metrics to the Order level. At the Order level, I can now introduce Order level metrics in conjunction with the aggregated Order Line metrics. The BI tool will need to understand what metrics exist at what level.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Mixed grain fact data

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