locked
MDX query performance issue RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I am trying to calculate count of records between two dates.
    For the above requirement following query is returning "correct results"

    With
    SET RDB as HEAD( EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1)
    SET CDB as HEAD( EXISTING [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1)
    SET RDE as TAIL( EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1)
    SET CDE as TAIL( EXISTING [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1)

    Member Measures.x as
    AGGREGATE({NULL : (RDB.item(0)) } * { CDB.item(0):NULL} ,[Measures].[Fact Work Item Details Count])

    Member Measures.Y as
    AGGREGATE({NULL : RDE.item(0) } * { CDE.item(0):NULL} ,[Measures].[Fact Work Item Details Count])

    SELECT NON EMPTY {Measures.x,Measures.y} ON COLUMNS,
    NON EMPTY { [DimRequestType].[Department_RequestType].[Request Type].MEMBERS} ON ROWS
    FROM [ServiceRequestPerformance]
    WHERE ( {[Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]},
    {[Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]}
    )

    This MDX query is returning faster in SSMS. (Approx 2-3 Sec)

    But if i create SET for(RDB,CDB,RDE,CDE) & Calculated measures for (Measures.x & Measures.Y) in the cube  and run the below query then it is taking long time to return result. (Nearly 2 minutes).

    SELECT NON EMPTY {[Measures].[X],[Measures].[Y]} ON COLUMNS,
    NON EMPTY { [DimRequestType].[Department_RequestType].[Request Type].MEMBERS} ON ROWS
    FROM [ServiceRequestPerformance]
    WHERE ( {[Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]},
    {[Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]}
    )

    Above Query is similar to first query except that it uses measures created in the cube.

    Any help is appreciated.

    Monday, June 6, 2016 6:23 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi A,

    The query is quite expensive as it has to navigate through every combination of [Requested Date].[Year -  Quarter -  Month -  Date] multiplied by every combination of [Completed Date].[Year -  Quarter -  Month -  Date].[Date]. If there are a 1000 members that your query's looking at, then there are 1,000,000 numbers to add up, and it can't use aggregations for these. Is there any way that you can construct the query to request aggregated numbers (without the multiplication of all the most granular members?) If not, then I can only suggest that you might be able to use recursive MDX (it appears your date dimensions are in a year, quarter, month, day hierarchy). For a quick guide on recursive MDX see http://richardlees.blogspot.com.au/2008/11/recursive-mdx-doesnt-have-same-need-in.html 

    Hope that helps,

    Richard

    Tuesday, June 7, 2016 5:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This looks very much like the "events in progress" problem that I, and various other people, have blogged about. You are unlikely to get a pure MDX approach to perform well and you'll probably need to remodel your cube, for example as shown here:

    https://blog.crossjoin.co.uk/2011/01/21/solving-the-events-in-progress-problem-in-mdx-part-1/

    HTH,

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, June 8, 2016 9:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    Thanks for the response.

    Richard & Christopher, I will try your solution and will share the results.

    But  can you please through some light on why it is taking less time in SSMS and more time when calculated measure created in the cube and used them.

    Wednesday, June 8, 2016 9:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Did you make your named sets dynamic when you defined them on the server? If you didn't, then they would not be picking up the selections you've made in your query and instead an expression like 
    EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members
    will return all dates.

    Generally speaking you should avoid referencing named sets inside calculations. If you are running on a version of SSAS before 2012 then you could be running into this issue:

    https://blog.crossjoin.co.uk/2011/03/16/referencing-named-sets-in-calculations/

    https://blog.crossjoin.co.uk/2012/05/16/named-sets-and-block-computation-in-ssas-2012/

    If you did use dynamic sets, that can also cause problems:

    https://blog.crossjoin.co.uk/2011/03/31/dynamic-named-sets-and-the-formula-cache/

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, June 8, 2016 9:36 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Christopher,

    Thanks for the response.

    1) Did you make your named sets dynamic when you defined them on the server?

    Yes i have created dynamic named set.

    2) If you are running on a version of SSAS before 2012

    I am working on SSAS 2014

    Wednesday, June 8, 2016 10:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I assume both queries give the same, correct results? Did you clear the cache before running each query? If so, it's hard to say what the problem is without being able to see a Profiler trace and doing other tests. Sorry...

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, June 8, 2016 11:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Christopher/All,

    Is there any alternative way to implement the below logic (may be by using descendant) in MDX without changing cube design.

    With

    SET RDB as HEAD( EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1)
    SET CDB as HEAD( EXISTING [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1)
    SET RDE as TAIL( EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1)
    SET CDE as TAIL( EXISTING [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1)

    Member Measures.x as
    AGGREGATE({NULL : (RDB.item(0)) } * { CDB.item(0):NULL} ,[Measures].[Fact Work Item Details Count])

    Member Measures.Y as
    AGGREGATE({NULL : RDE.item(0) } * { CDE.item(0):NULL} ,[Measures].[Fact Work Item Details Count])

    SELECT NON EMPTY {Measures.x,Measures.y} ON COLUMNS,
    NON EMPTY { [DimRequestType].[Department_RequestType].[Request Type].MEMBERS} ON ROWS
    FROM [ServiceRequestPerformance]
    WHERE ( {[Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]},
    {[Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]}
    )

    • Edited by Abdul527 Monday, June 13, 2016 2:49 PM
    Monday, June 13, 2016 2:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    No, I've never found a pure MDX approach that gives you the same performance - sorry. 

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Monday, June 13, 2016 8:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Christopher,

    Thanks for the reply.

    I have made small change in the mdx to overcome performance issue with the below query and need your input. Below MDX is taking more than 1 min to run (it is still slow even if i create a set for "HEAD(EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1)") and use it in calculated measure.

    With 
    Member Measures.X as
     AGGREGATE({NULL : HEAD(EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1).item(0).prevmember } *                                    

    { HEAD(EXISTING  [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1).item(0).prevmember : NULL}   
    ,[Measures].[Fact Work Item Details Count])

    Member Measures.Y as
     AGGREGATE({NULL : TAIL( EXISTING [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members,1).item(0) } *                                    

      { TAIL( EXISTING  [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members, 1).item(0):NULL}   
    ,[Measures].[Fact Work Item Details Count])

    SELECT NON EMPTY 
    {Measures.X,Measures.Y} ON COLUMNS,
    NON EMPTY {     [DimRequestType].[Department_RequestType].[Request Type].MEMBERS    } ON ROWS 
    FROM [ServiceRequestPerformance]
    WHERE ( {[Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Requested Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]},
    {[Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2015-01-01T00:00:00],
    [Completed Date].[Year -  Quarter -  Month -  Date].[Month].&[2016-04-01T00:00:00]}
    ) 

    As i notice, measure.x is the problem, because when i run the above MDX with only measure.y, it is executing in seconds.

    So I made changes to "X" measure, i remove Head function in " Measure.x " measure because i thought since my date dimension is already sorted on date field and we are using item(0), i thought it is not necessary to use Head function, so the changed measure will look like.

    Member Measures.X as
     AGGREGATE({NULL : (EXISTING  [Requested Date].[Year -  Quarter -  Month -  Date] .[Date].members).item(0).prevmember } *  {(EXISTING  [Completed Date].[Year -  Quarter -  Month -  Date].[Date].Members).item(0).prevmember : NULL}   
    ,[Measures].[Fact Work Item Details Count]).

    With the new measure report is rendering in 1 second and results are matching for old measure.x and new measure.x measure.

    Just wanted to check, is it ok to remove Head function or will it have any impact? I am still testing to make sure i cover all the scenario but your input will be very helpful.

    Your Input/help is appreciate and thank you for all the help.


    • Edited by Abdul527 Tuesday, June 14, 2016 7:34 AM
    Tuesday, June 14, 2016 7:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    As far as I can see, it should be fine to remove the Head() function. If you are getting the correct results and it's fast, then that's good news!

    Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Marked as answer by Abdul527 Saturday, June 18, 2016 11:31 AM
    Tuesday, June 14, 2016 8:40 PM