Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Aggregate data

2 posters

Go down

Aggregate data Empty Aggregate data

Post  zsazsagab Wed Apr 14, 2010 3:47 pm


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.


Posts : 1
Join date : 2010-04-14

Back to top Go down

Aggregate data Empty Re: Aggregate data

Post  ngalemmo 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.

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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum