MDX query different combinations of different dimensions

1
votes

I have a cube defined with dimensions:

  • Date (Year, Month, Week, Date)
  • Product

And measures:

  • Sales Amount
  • Units

Every week a select group of products are promoted (i.e., "on sale"). The query I'm trying to write is, at a given point in time, what were the Total Sales and Units sold of all promoted items in the last n weeks.

I can easily write a query that gets me these values for one week - it would look something like this:

SELECT
   NON EMPTY { [Measures].[Sales Amount], [Measures].[Units] } ON COLUMNS
FROM
   [Cube]
WHERE
   (
      { [Product].[Product].[Product].&[ProductA], [Product].[Product].[Product].&[ProductB] },
      [Date].[Week].[Week].[Week 8]
   )

What I am unable to express in MDX is, "for week 8, give me the sales for these products, and for week 9 give me the sales for these other products", and so on.

The concept of products being promoted is not modeled in any way in the cube. I have considered doing this but I'm not sure how to achieve it (an SCD, pehaps?).

Any help would be greatly appreciated. Thanks.

1
ssasmdx

1 Answers

0
votes

You can create a set of tuples - as long as each tuple in the set has the same dimensionality:

SELECT 
  NON EMPTY 
    {
      [Measures].[Sales Amount]
     ,[Measures].[Units]
    } ON 0
 ,NON EMPTY 
    {
      (
        [Product].[Product].[Product].&[ProductA]
       ,[Date].[Week].[Week].[Week 8]
      )
     ,(
        [Product].[Product].[Product].&[ProductB]
       ,[Date].[Week].[Week].[Week 9]
      )
    } ON 1
FROM [Cube];

Or just cross join all members from each hierarchy if you want to see all possible existing combinations of each:

SELECT 
  NON EMPTY 
    {
      [Measures].[Sales Amount]
     ,[Measures].[Units]
    } ON 0
 ,NON EMPTY 
    [Product].[Product].MEMBERS * [Date].[Week].MEMBERS ON 1
FROM [Cube];