none
AGGREGATE takes too long

    Question

  • Hi guys,

    I have a performance issue using AGGREGATE.

    The user will drag and drop this measure attribute [Reporting Date].[Month Name].

    Then based on this selection, he wants 2 calculations for other date dimention, one is before current selected month, and other is after.

    I've created 2 calculated MEMBERS on a that dimension, like this:

    CREATE MEMBER CURRENTCUBE.[Expire Date].[Date].[Expired]
     AS AGGREGATE(NULL:STRTOMEMBER('[Expire Date].[Date].&[' 
    + [Reporting Date].[Month Name].CURRENTMEMBER.Properties("Key0") 
    + RIGHT("0" + [Reporting Date].[Month Name].CURRENTMEMBER.Properties("Key1"), 2) 
    + "01" + '].PREVMEMBER')), 
    FORMAT_STRING = "#,##0;-#,##0", 
    VISIBLE = 0;  

    CREATE MEMBER CURRENTCUBE.[Expire Date].[Date].[Not Expired]
     AS AGGREGATE(STRTOMEMBER('[Expire Date].[Date].&[' 
    + [Reporting Date].[Month Name].CURRENTMEMBER.Properties("Key0") 
    + RIGHT("0" + [Reporting Date].[Month Name].CURRENTMEMBER.Properties("Key1"), 2) 
    + "01" + ']'):NULL), 
    FORMAT_STRING = "#,##0;-#,##0", 
    VISIBLE = 0; 

    Then use this member for calculations, like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Distinct Computer Count - Expired]
     AS ([Expire Date].[Date].[Expired]
        , [Measures].[Distinct Computer Count])
    , FORMAT_STRING = "#,##0;-#,##0", 
    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact Distinct Computers'; 

    CREATE MEMBER CURRENTCUBE.[Measures].[Distinct Computer Count - Not Expired]
     AS ([Expire Date].[Date].[Not Expired]
        , [Measures].[Distinct Computer Count])
    , FORMAT_STRING = "#,##0;-#,##0", 
    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact Distinct Computers'; 

    If I select a few members of [Reporting Date].[Month Name], it perform well, but when I select more then it takes minutes

    Please advise how to make it perform better!

    Thanx!
    • Edited by Radzyck Tuesday, July 10, 2018 8:09 AM
    Tuesday, July 10, 2018 8:09 AM

All replies

  • Hi Radzyck,

    Thanks for your question.

    >>>Please advise how to make it perform better!
    Based on the MDX query provided by you, one of the reason is the distinct count measure. Did you know that distinct counts in the cube store all the distinct values in each cell and aggregation? SSAS Multidimensional Model distinct count measure need to scan over the leaf level data for each cell to calculate them properly. This can mean that the cells are very large, making it a very large cube.

    You can also refer to below blogs about the best Distinct Count practice in SSAS:
    http://blog.soft-prestidigitation.com/the-best-distinctcount-practice-in-ssas.html
    http://blog.soft-prestidigitation.com/the-distinctcount-and-its-rough-edges.html

    >>>If I select a few members of [Reporting Date].[Month Name], it perform well, but when I select more then it takes minutes
    If you want to select multiple members of [Reporting Date].[Month Name], I would suggest you to create a dynamic set to get the min date and max date based on the [Reporting Date].[Month Name]. Something like below:

    Create Dynamic Set CurrentCube.[SelectedDates] As
    [Reporting Date].[Month Name].[Date]
    
    Or
    
    Create Dynamic Set CurrentCube.[SelectedDates] As
    [Reporting Date].[Date].[Date]

    Then you can get the min date as below:
    Head([SelectedDates]).Item(0)

    you can get the max date as below:
    Tail([SelectedDates]).Item(0)

    CREATE MEMBER CURRENTCUBE.[Expire Date].[Date].[Expired] AS
    AGGREGATE(NULL:STRTOMEMBER("[Expire Date].[Date].&[" 
    + Head([SelectedDates]).Item(0).Member_key + "]").PREVMEMBER)
    
    CREATE MEMBER CURRENTCUBE.[Expire Date].[Date].[Not Expired] AS 
    AGGREGATE(STRTOMEMBER("[Expire Date].[Date].&[" + 
    Tail([SelectedDates]).Item(0).Member_key + "]").NEXTMEMBER:NULL)

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, July 11, 2018 11:06 PM
    Wednesday, July 11, 2018 4:51 AM
    Moderator