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 move 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 is unique to a product and will never change.
A simplified structure of the dimension and fact tables are
ProductKey ProductBusinessKey Name ProductGroup
1 1 Product 1 ProductGroup1
2 1 Product 1 ProductGroup2
3 2 Product 2 ProductGroup1
Fact t Table
I want to generate a report that will display total revenue for all version of the product dimension
The the moment he mdx I am using is
[ProductDimension].[Product].Children On Columns
And this is generating the following results
Product1 Product1 Product2
100 100 100
I was wondering how I could structure a query to return the results as
Any help is much appreciated.