Improve MDX query performance

0
votes

I have a query that is taking way too long to run.

I have a cube which has the measures [Days Open] and [Days Overdue]. Any query which filters on these measures is what causes the problem. This is the start of the MDX:

WITH  
SET [Measure Filter] 
  AS FILTER( 
       [Event Work Order].[Event Work Order ID].Members, 
     ( ([Measures].[Days Open] >= 1 AND [Measures].[Days Open] <= 250 )  )) 
Member [Measures].[Work Order Measure] AS 
     (SUM([Measure Filter]
        , [Measures].[Work Order Count] ))

The underlying data that the measures a created from has a column for each of these values in the record. Can I create a dimension for days overdue and days open that can then be filtered on? If I do will it speed up the execution of the query?

3
ssasmdx
Well the function FILTER is slow as it is iterative and likely not calculating in block mode so your idea to change the underlying structure is probably worth trying – whytheq

3 Answers

0
votes

Only changes I’d make would be an intermediate step of consolidating the SET and then just using a tuple for the final measure but I think the following are just essentially syntactic changes that won’t help with performance 

WITH  
SET [OrderID set]
  AS FILTER( 
       [Event Work Order].[Event Work Order ID].Members, 
       [Measures].[Days Open] >= 1 
             AND [Measures].[Days Open] <= 250 
       )
MEMBER [Event Work Order].[Event Work Order ID].[All].[OrderID set sum] AS
  SUM([OrderID set])
Member [Measures].[Work Order Measure] AS 
     ([Event Work Order].[Event Work Order ID].[All].[OrderID set sum] 
        , [Measures].[Work Order Count] )
0
votes

Try applying NonEmpty function around the Event Work order dimension and Days open Measure.

Could you also simplfy filter to be just <= and remove the AND.

0
votes

I created a new Dimension called [Work Order Days Open] which contained all the unique days open values.

The original query was:

WITH  SET [Measure Filter] AS (FILTER( [Event Work Order].[Event Work Order ID].Members, ( ([Measures].[Days Open] >= 10 AND [Measures].[Days Open] <= 250 )  ))) Member [Measures].[Work Order Measure] AS (SUM([Measure Filter], [Measures].[Work Order Count] )) SELECT NON EMPTY { [Measures].[Work Order Measure]  } ON 0, NON EMPTY { (  EXCEPT( [Category Type].[Category Type Name].Members  , [Category Type].[Category Type Name].[All] )   ) } ON 1 FROM [Work Order] WHERE ( {  [Event Start Dates].[Date Key].[20160724] :  [Event Start Dates].[Date Key].[20180124]   })

This had an execution time of 2:31

With the new Dimension the new query is:

SELECT NON EMPTY { [Measures].[Work Order Count] } ON 0, 
NON EMPTY { ( EXCEPT( [Category Type].[Category Type Name].Members , [Category Type].[Category Type Name].[All] ) ) } ON 1 
FROM [Work Order] 
WHERE ( { [Event Start Dates].[Date Key].[20160724] : [Event Start Dates].[Date Key].[20180124] }, 
 {[Work Order Days Open].[Days Open].[10] : [Work Order Days Open].[Days Open].[250] } )

This has an execution time of 0:04

As you can see the introduction of the new dimension had a dramatic impact on the query performance.