Combining Low Level Data and 3rd Party Aggregated Data

View previous topic View next topic Go down

Combining Low Level Data and 3rd Party Aggregated Data

Post  diz_420 on Thu Feb 28, 2013 5:35 pm

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

diz_420

Posts : 2
Join date : 2013-02-28

View user profile

Back to top Go down

RE: Combining Low Level Data and 3rd Party Aggregated Data

Post  rathjeevesh on Thu Feb 28, 2013 9:33 pm

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

rathjeevesh

Posts : 15
Join date : 2013-02-16

View user profile

Back to top Go down

Re: Combining Low Level Data and 3rd Party Aggregated Data

Post  diz_420 on Fri Mar 01, 2013 9:03 am

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

diz_420

Posts : 2
Join date : 2013-02-28

View user profile

Back to top Go down

Re: Combining Low Level Data and 3rd Party Aggregated Data

Post  BoxesAndLines on Fri Mar 01, 2013 9:51 am

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/
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Combining Low Level Data and 3rd Party Aggregated 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