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

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].[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
Asked:
NewOLAPUser
  • 5
  • 5
1 Solution
 
waltersnowslinarnoldCommented:
Use the following code..,

SELECT
    SUM( [ProductDimension].[Product].Children) On Columns
FROM
    TheCube
WHERE
     [Measures].[Revenue]
GROUP BY
     [ProductDimension].[Product].Children
0
 
NewOLAPUserAuthor 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
 
EmesCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
EmesCommented:
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]
0
 
NewOLAPUserAuthor Commented:
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.
0
 
EmesCommented:
what does you cube design look like.

take a snap shot of it. from management studio.


image.bmp
0
 
NewOLAPUserAuthor 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
 
EmesCommented:
Try

SELECT
    [Product].[Product Group].allmembers Columns
FROM
    TheCube
WHERE
     [Measures].[Revenue]
0
 
NewOLAPUserAuthor Commented:
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.
0
 
NewOLAPUserAuthor 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
 
EmesCommented:
Glad you were able to get the solution you needed.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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