Combining Low Level Data and 3rd Party Aggregated Data

Hi,

Say you have a fact table that captures sales value and sales cost, and you compute the margin at the semantic layer. Dimensions are simply time, and customer. In addition, you have 3rd party benchmark data that is aggregated for margin alone (no other data available) for a certain year.

What is the best way to model this so that a user can view actuals vs. benchmarks in OLAP or a report?

Also, how do you make the link between the calculated margin and the 3rd party margin?

Cheers

Its better to store the benchmark data in a separate table. Whenever actual vs benchmark data is to be seen... actual data should be rolled up to the same grain as the benchmark values available. If you are getting benchmark data with varying granularity then add a column in the benchmark tablle to store the level to which it rolls up to. The linking can be done at semantic layer or at the reporting layer too.

-JR

So, you are suggesting to snowflake the star schema to this benchmark table?

You have to aggregate your sales data to the same grain as the 3rd party data (which is in another fact table). Then you can combine on a report. Here's an example, http://www.kimballgroup.com/2005/06/03/design-tip-68-simple-drill-across-in-sql/