none
Performance issue with MDX Dynamic time set in multi-select requirement

    Question

  • Greetings all! I tried to tack on to this old thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55981f4f-09b8-4958-bd8b-820f12d3d583/mdx-calculation-for-multiselect-in-excel?forum=sqlanalysisservices because it is sooo close to what I am looking for! Alas, nobody looked at it. So I am reposting my issue here.

    I am fairly new to MDX and have been fumbling around with different ways to get a YTD calculation with parallel period in as simple fashion that also allows the end user to slice and dice after the time selection is made.  Although I found a nifty example of using a Dynamic Named Set with the Now() function it only works for just YTD and all the slicers are grayed out when I apply it.

    What I want is to be able to select any time range in a calendar hierarchy for Year-Quarter-Month-Day, including skipping months or days and end up with a  YTD, Prior YTD and Year over Prior in dollars and percent.  To achieve that in my limited knowledge of MDX I modified another example to traverse down to the Day level using parallel period and looks like this:

    CREATE
    DYNAMIC SET CurrentCube.[Days] as
    (existing unorder([Date].[Calendar].[Date]));
     
    CREATE
    MEMBER CURRENTCUBE.[Measures].[PY$]
    AS
    Aggregate
    (
    Generate
    (
    (
    existing nonempty(unorder([Days]))),
    {
    parallelperiod([Date].[Calendar].[Year],1,[Date].[Calendar].currentmember)}
    )
    , [Measures].[Sales]
    ),
    FORMAT_STRING = "$#,##0.00;($#,##0.00)", 
    NON_EMPTY_BEHAVIOR = { [Sales] }, 
    VISIBLE = 1 ,   ASSOCIATED_MEASURE_GROUP= 'Sales';   

    However it added 10 seconds to every adjustment made in the Excel pivot table accessing this cube!  It used to be almost instant before that and when that attribute is not used it goes back to being instant again.  Then I found the example mention above that handled the multi-select issue for a Dynamic YTD and added it to my calculations.  When I tried it, it was only a 4 to 5 second lag which is so much better!  However, I have been fumbling around with trying to modify it for a Prior YTD and can't get it to work!  Can somebody help this neophyte MDX'er with how to do that?  Her is the code from the pervious post that works as a YTD:

    -- Dynamic set for selected dates:
    
    Create dynamic set Currentcube.[SelectedDates] as
     [Date].[Calendar].[Date].Members;
    
    -- YTD Sales based on selected dates:
    
    Create Member Currentcube.[Measures].[YTDSales] as
     Aggregate(YTD(Tail([SelectedDates]).item(0)),
     [Measures].[Sales Amount]);

     

    Thank you in advance for any help and time you have to offer and thanks to Deepak Puri for the original post as well.


    Retail Services Developer


    Monday, April 15, 2019 3:45 PM

All replies

  • Hi  Cory Bonallo,

    You could try to use below calculated measure and use date hierarchy as slicer to see whether it works or not.

    This is YTD

    sum( PeriodsToDate(  [Date].[Calendar Date].[Calendar Year]
                ,   
                 [Date].[Calendar Date].CurrentMember
            ) ,[Measures].[Internet Sales Count]
    		 )
    
    

    This is PREVIOS YTD

    SUM(
                PeriodsToDate([Date].[Calendar Date].[Calendar Year],
                    ParallelPeriod(
                        [Date].[Calendar Date].[Calendar Year], 
                        1, 
                        [Date].[Calendar Date].CurrentMember)                
    ),
    [Measures].[Internet Sales Count]
                )

    And Calendar Date   is my date hierarchy with Year-Quarter-Month-Date, you could use this in Slicer to see whether it works or not.

    Best Regards,
    Zoe Zhi


    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.

    Tuesday, April 16, 2019 3:13 AM
  • Thanks for your reply!  I have already tried that and it returns nothing when used in multi-select mode level.  Say if I select 2019 first quarter and then several days from April 2019.

    Retail Services Developer

    Tuesday, April 16, 2019 7:04 PM
  • Hi  Cory

    I test this in my environment(with Excel), I find that it should work. At first April 2019 is Q2, When you use date hierarchy in slicer like below, it will automatically change it to correct month or quarter 

    Best Regards,
    Zoe Zhi


    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.

    Thursday, April 18, 2019 7:57 AM
  • I think the issue is likely my SSAS calendar hierarchy setup.  I have the Month as a slicer and I am using Calendar hierarchy as filter.  When I setup Year - Quarter - Month - Day relationships properly my month slicer ends up with duplicate months.  If I just leave them all related only to dimension DateKey I don't get duplicates.  I am going to guess that PriorPeriods relies on the relationships between hierarchy levels to work?

    Retail Services Developer

    Monday, April 22, 2019 7:00 PM
  • Hi Cory,

    Yes, For Period  function will calculated based on level your choose. You could refer to PeriodsToDate (MDX) for details.

    Best Regards,
    Zoe Zhi


    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.

    Tuesday, April 23, 2019 7:37 AM
  • So do you know how to use the Month in a slicer and in a hierarchy at the same time? When I tried to do that I got duplicates in the slicer because 2018 + Q1 + January is different than 2019 + Q1 + January when you create the proper unique Key for the hierarchy.

    Thanks for your help Zoe!


    Retail Services Developer

    Tuesday, April 23, 2019 3:10 PM
  • So I have found a workaround to my last question.  I am using Month Name as the slicer, allowing Month to be used in the Calendar hierarchy.  I have fixed the relationships for the hierarchy.

    I have tried your suggestion and it did not work.  Your calculation returns nothing in PREVIOS YTD if I select January and March of 2019 from the Calendar Hierarchy?  Also, if I select just 2019 it does work but gives me all of 2018 for Prior.  I need to either get selected dates or up to today for Prior year.  The above example does work because it uses a dynamic set of selected date but I couldn't figure out how to adapt it to get Prior year.


    Retail Services Developer

    Tuesday, April 23, 2019 9:38 PM
  • Hi Cory

    My above expression is used to calculate YTD(first day of selected date's year: selected date ), if you want to calculate date range ( same period year of selected date :selected date), you could try to use expression to see whether it works or not

    sum((ParallelPeriod ([Date].[Calendar Date].[Calendar Year], 1 ,[Date].[Calendar Date].currentmember):([Date].[Calendar Date].currentmember),[Measures].[Internet Sales Count]))

    Best Regards,
    Zoe Zhi


    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.


    Thursday, April 25, 2019 6:36 AM
  • I don't believe that address say selecting January and March. I don't want sales from 1/1 to 3/31. I want sales from 1/1 - 1/31 and 3/1 - 3/31. I think that is why this works, it uses the TAIL of each selected date, regardless of level, start with All Years item(0).

    Aggregate(YTD(Tail([SelectedDates]).item(0)),[Measures].[Sales Amount]);

    Is there a way to insert Parallel Period into this calculation?


    Retail Services Developer

    Thursday, April 25, 2019 3:16 PM
  • Hi  Cory,

    If you want to use parallel period, you could try below expression to see whether it works or not

    SUM(
                YTD(
                    ParallelPeriod(
                        [Date].[Calendar Date].[Calendar Year], 
                        1, 
                        tail([Date].[Calendar Date].CurrentMember).item(0))                
    ),
    [Measures].[Internet Sales Count]
                )
    Best Regards,
    Zoe Zhi


    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.

    Friday, April 26, 2019 8:52 AM
  • That did not work either.  Oh well, thanks for trying!  Must have to do with the multi select or it can't be pre-calculated.  This is what works if just a little slow (10 sec.)

     
    CREATE DYNAMIC SET CurrentCube.[Days] as
        (existing unorder([Date].[Calendar].[Date]));  

    Aggregate ( Generate ( (existing unorder([Days])), {parallelperiod([Date].[Calendar].[Year],1,[Date].[Calendar].currentmember)} ) , [Measures].[Sales] )



    Retail Services Developer


    Friday, April 26, 2019 5:45 PM
  • Hi  Cory Bonallo,

    Thank you for your sharing!  I will try to find and test to see whether there is other expressions  for multiple-selected. And I will inform you as soon  as  possible.

    Best Regards,
    Zoe Zhi


    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.

    Monday, April 29, 2019 8:27 AM