CODEWITHSUNDEEP
performancequery-optimizationssasmdxsubquery
mshparber
mshparber

Reputation: 113

MDX SUB-SELECT vs WHERE PERFORMANCE ISSUES

I've built SSRS report that gets CustomerID as parameter and runs MDX query with it.

With WHERE clause it only takes 1 second to run, while if I pass it to SUBSELECT clause it takes 13 seconds! And I have to use SUBSELECT because I want to show the member's name in the results

The syntax of the long query is:

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS,
NON EMPTY { ([CUBE DIM DATE].[Month CD].[Month CD].ALLMEMBERS *
[CUBE DIM CUSTOMER].[Account MNG].[Account MNG].ALLMEMBERS * 
[CUBE DIM PRODUCT].[Product CD].[Product CD].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_KEY ON ROWS FROM 
( SELECT ({ [CUBE DIM CUSTOMER].[Customer No].&[111111]})   on 0 from   [CUBE_Prod] )

So if instead of the last line I use:

[CUBE_Prod]  WHERE [CUBE DIM CUSTOMER].[Customer No].&[111111]

...leaving all the rest the same then it only takes 1 second. Obviously, I am missing something...

Upvotes: 1

Views: 1715

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

Couple of options come to mind...

Option1: use WHERE-clause version and create a calculated member to display the slicer member value in the resultset...

WITH MEMBER SlicerValue AS
    IIF(
        IsEmpty([Measures].[Revenue])
        ,NULL
        ,[CUBE DIM CUSTOMER].[Customer No].CurrentMember.MemberValue
    )
SELECT 
    NON EMPTY { 
         SlicerValue
        ,[Measures].[Revenue] 
    } ON COLUMNS, 
    NON EMPTY {
        ( 
            [CUBE DIM DATE].[Month CD].[Month CD].AllMembers * 
            [CUBE DIM CUSTOMER].[Account MNG].[Account MNG].AllMembers * 
            [CUBE DIM PRODUCT].[Product CD].[Product CD].AllMembers 
        )
    } Dimension Properties MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_KEY ON ROWS
FROM    [CUBE_Prod]
WHERE   [CUBE DIM CUSTOMER].[Customer No].&[111111]

Option2: use SSRS expression to construct perfect MDX. in the code below, you'd reference the parameter value so that the "[CUBE DIM CUSTOMER].[Customer No].&[111111]" is dynamic.

SELECT 
    NON EMPTY { 
         [Measures].[Revenue] 
    } ON COLUMNS, 
    NON EMPTY {
        ( 
            [CUBE DIM DATE].[Month CD].[Month CD].AllMembers * 
            [CUBE DIM CUSTOMER].[Account MNG].[Account MNG].AllMembers * 
            [CUBE DIM PRODUCT].[Product CD].[Product CD].AllMembers *
            [CUBE DIM CUSTOMER].[Customer No].&[111111]
        )
    } Dimension Properties MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_KEY ON ROWS
FROM    [CUBE_Prod]

Upvotes: 1

Related Questions