MDX: Sum, ignoring members with duplicate name

Question

I'm trying to sum the sales amount of all products in a category and its sub categories, adding the sales amount for products with the same product dimension key only once.

Each category can have any number of sub categories and each sub category can have any number of sub categories of its own, up to five levels of sub categories. The number of sub categories can vary.
So the category_hierarchy in the category_dimension has these levels:

main_category, sub_category_1, sub_category_2, ..., sub_category_5

and could look like this:

id    main_category     sub_category_1    sub_category_2     sub_category_3   ...
1     Electronics
2     Electronics       Camera & Photo
3     Electronics       Camera & Photo    Accessories
4     Electronics       Camera & Photo    Digital Cameras
5     Electronics       Camera & Photo    Film Photography   Film Cameras
6     Electronics       Camera & Photo    Film Photography   Film
7     Electronics       Headphones
8     Home & Kitchen
...

In the fact table there are sales numbers for each product per category per day and each product can belong to more than one category (if a product belongs to more than one category it has the same sales numbers in each of these categories), e.g.

id    product_id    category_id    date_id      sales   other_information
1     1             1              2016-06-30   4       AAA
2     1             2              2016-06-30   4       BBB
3     1             6              2016-06-30   4       CCC
4     2             2              2016-06-30   5       DDD
5     2             4              2016-06-30   5       EEE
...

If I now run a simple MDX Query to show me the number of sales for each category it will sum the sales number multiple times if the product is in multiple categories:

SELECT 
  {[DATE_DIMENSION].[DATE_HIERARCHY].ALLMEMBERS} ON COLUMNS
 ,NON EMPTY 
      {[CATEGORY_DIMENSION].[CATEGORY_HIERARCHY].ALLMEMBERS}
    * 
      {[PRODUCT_DIMENSION].[PRODUCT_HIERARCHY].ALLMEMBERS} ON ROWS
FROM [Cube]
WHERE [MEASURES].[SALES];

returns

                                             2016-06-30  2016-06-29  ...
CATEGORY                    PRODUCT_ID       SALES       SALES
All Categories              All Products     22          ...
                              1              12
                              2              10
  Electronics               All Products     22
                              1              12
                              2              10
    Camera & Photo          All Products     18
                              1               8
                              2              10
       Digital Cameras      All Products      5
                              2               5
       Film Photography     All Products      4
                              1               4
         Film               All Products      4
                              1               4

I'd like to always get sales 4 for product 1 and sales 5 for product 2 on 2016-06-30 (but still be able to aggregate the sales by week/month/year):

                                             2016-06-30  2016-06-29  ...
CATEGORY                    PRODUCT_ID       SALES       SALES
All Categories              All Products      9          ...
                              1               4
                              2               5
  Electronics               All Products      9
                              1               4
                              2               5
    Camera & Photo          All Products      9
                              1               4
                              2               5
       Digital Cameras      All Products      5
                              2               5
       Film Photography     All Products      4
                              1               4
         Film               All Products      4
                              1               4

What if Sales for (ProductA/Subcategory 1) = 5 and (ProductA/Subcategory 2) = 3 ? — whytheq
Sales for (ProductA/Subcategory 1) is always = (ProductA/Subcategory 2) — Christian Welsch

whytheq whytheq · Accepted Answer · 2016-06-10T22:54:44

This is not finished or tested - it is just the initial idea: I'll need to test against AdvWrks once I have access to it.

To adjust the All member to your needs is quite involved.

WITH 
SET [All_tuples] AS
    ORDER(
      {[CATEGORY_DIMENSION].[CATEGORY_HIERARCHY].[CATEGORY_HIERARCHY].MEMBERS}
    * {[PRODUCT_DIMENSION].[PRODUCT_HIERARCHY].MEMBERS}
    , [PRODUCT_DIMENSION].CURRENTMEMBER.MEMBER_CAPTION
    , BDESC
    )
SET [NoDupes] AS
  FILTER(
     [All_tuples] AS X,
     X.CURRENT.ITEM(1) NOT IS X.CURRENT.LAG(1).ITEM(1)
  )
MEMBER [CATEGORY_DIMENSION].[CATEGORY_HIERARCHY].[All].[Category I NEW] AS
  AGGREGATE(
    [NoDupes]
  ) 
SELECT 
  {[Measures].[Sales]} ON COLUMNS
 ,NON EMPTY 
   [CATEGORY_DIMENSION].[CATEGORY_HIERARCHY].[All].[Category I NEW] ON ROWS
FROM [Cube];

MDX: Sum, ignoring members with duplicate name

1 Answers