Aggregating results for all versions of a slowly changing dimension

Question

Hi,

I am looking to aggregate values for all versions of a slowly changing dimension. Here is a simplified explanation of the problem.

I have a Product dimension and this dimension has an attribute called ProductGroup.
Products from time to time change ProductGroups.

The Product dimension is a slowly changing dimension, and the change being when it moves between ProductGroups. The Product dimension has a ProductKey and a ProductBusinessKey.

The ProductKey is the unique key within the dimension and ProductBusinessKey is the key from main booking system. The ProductBusinessKey(PBK) is unique to a product and will never change.

A simplified structure of the dimension and fact tables are

I've used the following abbreviations

PK - ProductKey

PBK - ProductBusinessKey

PG - ProductGroup

Dimension Table

PK PBK Name  PG

1   1      Prod1  ProductGroup1

2   1      Prod1  ProductGroup2

3   2      Prod2  ProductGroup1

Fact Table

PK Revenue

1  100

2  100

3  100

I want to generate a report that will display total revenue for all version of the product dimension i.e.

Prod1 Prod2

200   100

At the the moment he mdx I am using is

SELECT

[ProductDimension].[Product].Children On Columns

FROM

TheCube

WHERE

[Measures].[Revenue]

And this is generating the following results

Prod1 Prod1 Prod2

100   100    100

I was wondering how I could structure a query to return the results as

Prod1 Prod2

200   100

Any help is much appreciated.

Answer 1

If you use the SUM(..) on the [Revenue] measure over time from your time dimension it will show the desired results.

Answer 2

The easiest way would be to create an attribute that has a key of either the ProductBusinessKey, then use that in your query.