How to design Benchmark's (Target's) in dimensional model

Need suggestion in designing - Actual's vs. Benchmark's (or target's).

Design overview -

We are creating a start schema with 2 facts and 4 major dimensions - one a Transaction fact and other is Aggregate fact storing all "Counts Measures" which are rolled up from base transaction fact. Base fact is connected to more dimensions (as its Transaction fact) while aggregate fact is connected with 4 major dimensions

Design question -

We have a requirement to store "Target values" for some measures stored in aggregate. For example - Total revenue. The Target value of revenue will be different for all dimensions i.e. Total revenue for Product will be different from Total revenue for Retail store etc. This data will be used to provide "Target vs. Actuals" analysis. And Actual's will be derived from Aggregate fact (or base fact table if needed).

This "Target vs. Actuals" analysis will be performed monthly.

Key points -

- There is no history requirements for change in Target values. Targets will change overtime but latest Target is of importance.
- They cant be rolled up with hierarchy (if there is any).
- Each Target measure will be independent of each other. That is Target measures at Retail store level will have no dependency on Product level.

Design options ( your suggestions will be helpful in validating them) -

1. Add these Target measures to respective dimensions and treat them as attributes of Dimension table. Ex - Addition of Target Revenue column in Retail Store Dimension, Product Dimension etc. (Please not all these Dimension tables are SCD 1.)

2. Create an extension table to each dimension and store this target related measures in that extension table. This new extension table will be 1:1 relationship with base dimension. Advantage of this approach is that it can handle the history requirement for such measures (if it comes in future) by adding date related columns. (Something like SCD 2).

Please provide your suggestions on designing.

Regards,
Abhiraizada

Don't you need a Product level aggregate and a Store level aggregate? You really have 2 different benchmarks - the benchmark facts are at the store level and product level. Trying to put it at the Store/Product level will cause problems - you have to have a "dummy" product with each stores benchmark and a "dummy" store with the product level benchmarks, which will cause the overall benchmark to be double the actual overall benchmark.

An alternative is to allocate the benchmarks to the store/product level. If a store represented 1% of the overall benchmark, then the product level benchmarks for the store could be 1% of the overall benchmark for each product. But if you do that, the table has to include a row for every store/product combination. if you have 1000 products and a 1000 stores, you will have 1 million rows for each reporting period. Also, it runs the risk of people using the store/product level benchmark to assess store/product level sales.

We don't need an Product and Store level aggregate as we can role up the data from Store level to Product level ( or to any other level if needed).

And Targets (or Benchmarks) cant be broken into lower level - they are hard fixed values. For ex - If Retail store has set the value of "Total revenue", this revenue includes revenue generated for all Products sold at store. And reports which will be using these Actuals and Targets will be specific to one dimension only. So to make it more clear report will have 2 data components - "Actuals" which will be derived from Aggregate fact by rolling the data at required dimension (say Retail Store) and other data component would be "Target" which is fixed value for that dimension (Retail Store).