• Status: Solved
• Priority: Medium
• Security: Public
• Views: 804

# 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
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].[Product].Children On Columns
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.

0
NewOLAPUser
• 5
• 5
1 Solution

Commented:
Use the following code..,

SELECT
SUM( [ProductDimension].[Product].Children) On Columns
FROM
TheCube
WHERE
[Measures].[Revenue]
GROUP BY
[ProductDimension].[Product].Children
0

Author Commented:
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.
0

Commented:
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.
0

Commented:
you can try this use name field and not the ProductGroup from
Dimension Table

SELECT
[Prod Dimn].[Name].Children On Columns
FROM
Stuff
WHERE
[Measures].[Revenue]
0

Author Commented:
Hi Ermes,

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.
0

Commented:
what does you cube design look like.

take a snap shot of it. from management studio.

image.bmp
0

Author Commented:
Hi Emes,

Thanks for your reply.  I've uploaded snapshots as requested.  I noticed that I don't have the ProductBusinessKey listed as you do in you example.

1.jpg
2.jpg
3.jpg
0

Commented:
Try

SELECT
[Product].[Product Group].allmembers Columns
FROM
TheCube
WHERE
[Measures].[Revenue]
0

Author Commented:
Hi Emes,

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.
0

Author Commented:
I solved this problem by modifying my Product dimension to include the ProductBuinessKey

Now when I create a query I can use the ProductBusinessKey which correctly sums for a product regardless of the product group.
0

Commented:
Glad you were able to get the solution you needed.
0

## Featured Post

• 5
• 5
Tackle projects and never again get stuck behind a technical roadblock.