Aggregate data

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.

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.