Aggregate data

View previous topic View next topic Go down

Aggregate data

Post  zsazsagab on Wed Apr 14, 2010 3:47 pm

All,

I am rather new to dimensional modeling and had a quick question. I have a transactional fact table with the grain defined that I want, however I would like to leverage industry benchmarking data that is aggregated as point of reference against a transactional record. For example, I would like to show the spend of a companies transactions using the grain defined, however I would too like to show this against the aggregated industry spend. Is there a standard I should be using to store this properly. Any help is welcome.

zsazsagab

Posts : 1
Join date : 2010-04-14

View user profile

Back to top Go down

Re: Aggregate data

Post  ngalemmo on Wed Apr 14, 2010 5:18 pm

You would have a separate fact table with the industry-wide metrics with the appropriate dimensions.

When you report your companies numbers against the industry numbers you query both facts... the company numbers would be aggregated to match the industry numbers and joined on common dimensions.

All BI tools that support star schema will combine the two facts correctly, as long as there are one or more shared dimensions. You may want to consider building an summary fact table for the transactional data, but if such a summary is built, I would not summarize it to the same level as the industry metrics. Such a summary would have little utility beyond this one query. A summary at a higher grain would have greater utility to support other analysis as well as improve the performance of the industry level analysis.
avatar
ngalemmo

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

View user profile http://aginity.com

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