NewOLAPUser
asked on
Aggregating results for all versions of a slowly changing dimension
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 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
Dimension Table
ProductKey ProductBusinessKey Name ProductGroup
1 1 Product 1 ProductGroup1
2 1 Product 1 ProductGroup2
3 2 Product 2 ProductGroup1
Fact t Table
ProductKey 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.
Product1 Product2
200 100
The the moment he mdx I am using is
SELECT
[ProductDimension].[Produc
FROM
TheCube
WHERE
[Measures].[Revenue]
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
Product1 Product2
200 100
Any help is much appreciated.
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
Dimension Table
ProductKey ProductBusinessKey Name ProductGroup
1 1 Product 1 ProductGroup1
2 1 Product 1 ProductGroup2
3 2 Product 2 ProductGroup1
Fact t Table
ProductKey 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.
Product1 Product2
200 100
The the moment he mdx I am using is
SELECT
[ProductDimension].[Produc
FROM
TheCube
WHERE
[Measures].[Revenue]
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
Product1 Product2
200 100
Any help is much appreciated.
ASKER
Hi waltersnowslinarnold,
Thanks for your relpy. I couldn't get your suggestion to work. I am not that familiar with mdx so I excuse my ignorance but i am getting the following error
The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used.
Thanks for your relpy. I couldn't get your suggestion to work. I am not that familiar with mdx so I excuse my ignorance but i am getting the following error
The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used.
In order to understand how the MDX code is working
can you list the values under the ProductDimension from the cube.
The cube may not be built correctly.
you should get the values you wish.
can you list the values under the ProductDimension from the cube.
The cube may not be built correctly.
you should get the values you wish.
you can try this use name field and not the ProductGroup from
Dimension Table
ProductKey ProductBusinessKey Name ProductGroup
SELECT
[Prod Dimn].[Name].Children On Columns
FROM
Stuff
WHERE
[Measures].[Revenue]
Dimension Table
ProductKey ProductBusinessKey Name ProductGroup
SELECT
[Prod Dimn].[Name].Children On Columns
FROM
Stuff
WHERE
[Measures].[Revenue]
ASKER
Hi Ermes,
i tried your suggestion. The query executed but returns no results.
The cube has been throughly tested and works...except in this one instance.
This is the first cube i'be built so I have no experience with mdx. I only record the ProductKey (the unique dimension key) in the fact table. Could I add the ProductBusinessKey to the fact table also and link the Product Dimension to the ProductBusinessKey? This to me would seem to solve the problem as it would not return duplicate product names as in my example above. I can still run a query against ProductGroup if I need to look at the information from that perspective.
As I said above the cube has been throughly tested against existing results and we are confident that it is working correctly so I thought I'd ask your opinion before changing the structure of the cube.
I appreciate you time.
i tried your suggestion. The query executed but returns no results.
The cube has been throughly tested and works...except in this one instance.
This is the first cube i'be built so I have no experience with mdx. I only record the ProductKey (the unique dimension key) in the fact table. Could I add the ProductBusinessKey to the fact table also and link the Product Dimension to the ProductBusinessKey? This to me would seem to solve the problem as it would not return duplicate product names as in my example above. I can still run a query against ProductGroup if I need to look at the information from that perspective.
As I said above the cube has been throughly tested against existing results and we are confident that it is working correctly so I thought I'd ask your opinion before changing the structure of the cube.
I appreciate you time.
ASKER
Try
SELECT
[Product].[Product Group].allmembers Columns
FROM
TheCube
WHERE
[Measures].[Revenue]
SELECT
[Product].[Product Group].allmembers Columns
FROM
TheCube
WHERE
[Measures].[Revenue]
ASKER
Hi Emes,
Sorry about not replying sooner.
I don't think that you last solution will give me the result that I am looking for.
I am going to try and add ProductBusinessKey to the fact table and link the Product Dimension to the ProductBusinessKey if I understand things correctly this should sum products accross product groups.
Sorry about not replying sooner.
I don't think that you last solution will give me the result that I am looking for.
I am going to try and add ProductBusinessKey to the fact table and link the Product Dimension to the ProductBusinessKey if I understand things correctly this should sum products accross product groups.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you were able to get the solution you needed.
SELECT
SUM( [ProductDimension].[Produc
FROM
TheCube
WHERE
[Measures].[Revenue]
GROUP BY
[ProductDimension].[Produc