MDX query runs for more than an hour

Question

I am new to MDX queries. I have written a query which uses lead function to get values for (Current Week plus 1) as a new column for each of the metrics. Basically comparing current week value with last week's value. Without the new members the query runs within seconds. After adding the new members it runs forever. Pls suggest ways to optimize this query Thanks for help.

WITH
SET [Range] as strtomember
(" [Time].[Week].&["+ Format(DateAdd('d', - DatePart('w', Now(), 2), Now()+7), "yyyy-MM-ddT00:00:00")
           +"]")

:strtomember
(" [Time].[Week].&["+ Format(DateAdd('d', - DatePart('w', Now(), 2), Now()+14), "yyyy-MM-ddT00:00:00")
           +"]")

MEMBER [Measures].[SalesNew] as
CASE   WHEN [Time].[Year].CURRENTMEMBER IS [Time].[Year].&[2019] THEN
sum([Time].[Day].CURRENTMEMBER.LEAD(7),[Measures].[Sales Prev])
ELSE null
END,FORMAT_STRING = "$#,###.00"

MEMBER [Measures].[Order UnitsNew] as
CASE   WHEN [Time].[Year].CURRENTMEMBER IS [Time].[Year].&[2019] THEN
sum([Time].[Day].CURRENTMEMBER.LEAD(7),[Measures].[Order Units Prev])
ELSE null
END,FORMAT_STRING = "#,##0"

MEMBER [Measures].[Count of OrdersNew] as
CASE   WHEN [Time].[Year].CURRENTMEMBER IS [Time].[Year].&[2019] THEN
sum([Time].[Day].CURRENTMEMBER.LEAD(7),[Measures].[Count of Orders Prev])
ELSE null
END,FORMAT_STRING = "#,##0"

SELECT
    {  [Measures].[Sales],[Measures].[Sales Prev],[Measures].[SalesNew],[Measures].[Order Units],[Measures].[Order Units Prev],
     [Measures].[Order UnitsNew], [Measures].[Count of Orders],[Measures].[Count of Orders Prev], [Measures].[Count of OrdersNew]} ON COLUMNS ,
      [Range] *
      [Time].[Day].[Day].ALLMEMBERS * -- 4
      [Time].[Hour].ALLMEMBERS *
      [Product].[Merch Dept].ALLMEMBERS *
      [Product].[Class].ALLMEMBERS ON ROWS
FROM [Cube]

whytheq whytheq · Accepted Answer · 2019-03-31T15:37:48

Your MDX looks pretty cool. Optimizing MDX is a little bit black magic - you need to just try alternatives.

Initially I'd try:

1.Swapping in strToSet rather than the two strToMember functions.
2.Do you need the condition [Time].[Year].CURRENTMEMBER IS [Time].[Year].&[2019] ? Reason I ask is that [Range] seems to already be 2019 and you cross-join to that set so do you need to worry about 2019?
3.You can use a simple Tuple instead of the aggregate SUM
4.Maybe while testing it might be worth removing the format_strings - shouldn't make a difference but if so you can add back.

I've not tested the following so please excuse typos:

WITH
SET [DateSet] AS
strtoSet
(
   "[Time].[Week].&[" 
  + Format(DateAdd('d', - DatePart('w', Now(), 2), Now()+7), "yyyy-MM-ddT00:00:00")
  + "]:[Time].[Week].&["
  + Format(DateAdd('d', - DatePart('w', Now(), 2), Now()+14), "yyyy-MM-ddT00:00:00")
  +"]"
)
MEMBER [Measures].[SalesNew] AS
(
   [Time].[Day].CURRENTMEMBER.LEAD(7)
  ,[Measures].[Sales Prev]
)
MEMBER [Measures].[Order UnitsNew] AS
(
   [Time].[Day].CURRENTMEMBER.LEAD(7)
  ,[Measures].[Order Units Prev]
)
MEMBER [Measures].[Count of OrdersNew] AS
(
    [Time].[Day].CURRENTMEMBER.LEAD(7)
   ,[Measures].[Count of Orders Prev]
)
SELECT
    {  
       [Measures].[Sales]
      ,[Measures].[Sales Prev]
      ,[Measures].[SalesNew]
      ,[Measures].[Order Units]
      ,[Measures].[Order Units Prev]
      ,[Measures].[Order UnitsNew]
      ,[Measures].[Count of Orders]
      ,[Measures].[Count of Orders Prev]
      ,[Measures].[Count of OrdersNew]
    } ON COLUMNS ,
      [DateSet] *
      [Time].[Day].[Day].ALLMEMBERS * 
      [Time].[Hour].ALLMEMBERS *
      [Product].[Merch Dept].ALLMEMBERS *
      [Product].[Class].ALLMEMBERS ON ROWS
FROM [Cube];

MDX query runs for more than an hour

1 Answers