none
leveraging SCOPE calculations for multiple dimensions/measures RRS feed

  • Question

  • hi all,
    we have a very large and complex cube where I want to leverage Time Intelligence functions for various dimensions and/or measures without significantly duplicating the code.
    As an example, taking Adventure works, a [Quarter To Date] function below, how can I leverage the same function to work the same on [Delivery Date] and [Ship Date]  as well as, depending on which dimension is involved, to apply on a specific measure or another **without reusing/retyping** most of the code below.   I understand that any possible semi-dynamic solution and/or involving IS operands might be somewhat slower that replicated code, but I'm willing to pay that price, if I can reduce the amount of code significantly and the performance is not that bad.

    Is this doable in MDX scripting? 
    thx much for any feedback!!
    Cos

    /*
      Begin Time Intelligence script for the [Date].[Calendar] hierarchy.
    */

    Create Member
      CurrentCube.[Date].[Calendar Date Calculations].[Quarter to Date]
      As "NA" ;
     
    Scope(
           {
             [Measures].[Internet Sales Amount],
             [Measures].[Internet Order Quantity]
           }
    ) ;
    // Quarter to Date
      (
        [Date].[Calendar Date Calculations].[Quarter to Date],
        [Date].[Calendar Quarter].[Calendar Quarter].Members,
        [Date].[Date].Members
      )  
      =
      Aggregate(
                 { [Date].[Calendar Date Calculations].[Current Date] }
                 *
                 PeriodsToDate(
                                [Date].[Calendar].[Calendar Quarter],
                                [Date].[Calendar].CurrentMember
                 )
      ) ;

    End Scope ;
    /*
      End Time Intelligence script for the [Date].[Calendar] hierarchy.
    */
     

    Tuesday, August 9, 2011 6:06 PM

Answers

  • Hey Cos!

    I'm not saying I have spare time for solving this puzzle, but I'd like to give you some input if I can :)

    Generally speaking this is not possible as there is no way to determine which date dimension members should be aggregated over inside the calculation - my preference here is Marco Russo's datetool utility dimension, so it's a separate dimension instead of what you have here - a separate attribute. Just to clarify this consider you have 2 date dimensions  and both of them are present in the slicer with a single month selected - for example Order date and Ship Date. Should the YTD calculation return YTD for Order date or Ship date? I don't think YTD(Order Date) * YTD(Ship Date) would be the correct answer also.

    But maybe you have a specific usage in mind that would simplify this ambiguity so for example to only work on a single date dimension that is present on one of the axis. This could be achieved using the universal calculated measures approach (by Tomislav Piasevoli) but I'm afraid it would add much more complexity to your project.

    To be honest, i really think this kind of calculations are responsibility of the client application the same way as Excel provides different display modes of values and relative calculations. Trying to create overly smart mdx scripts that account for everything is the wrong approach IMHO. For example, I've created a demo cube that has all kinds of detectors regarding subselects, visual totals etc, but it is only useful as an example that it wouldn't scale and we have to live with familiar current member errors, wrong totals and all other consequences of not having complete control. There are many examples where a bit of client application coding could replace a huge amount of complex mdx calculations - take for example something so simple as previous row (not member), row number, current coordinate - you get all of that pretty simple in an Excel macro using the pivot table object model.

    Let me know if you can provide more clarification to your planned usage regarding my comments - maybe there is a nice workable solution.

    Something just came to mind, would you be OK with having another helper dimension that would serve the purpose of selecting over which dimension should the calculations aggregate?

    Best,

    Hrvoje Piasevoli

     


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    Thursday, August 18, 2011 1:58 PM

All replies

  • Hi,

    I am currently looking into this issue and will give you an update as soon as possible.

    thank you for your understanding and support,

    Regards,
    Jerry

    TechNet Subscriber Supportin forum
    If you have any feedback on our support, please contact  tnmff@microsoft.com

    Friday, August 12, 2011 7:34 AM
    Moderator
  • thx Jerry, looking forward to your reply!  thx much for any suggestions!
    Sunday, August 14, 2011 5:32 PM
  • Hey Cos!

    I'm not saying I have spare time for solving this puzzle, but I'd like to give you some input if I can :)

    Generally speaking this is not possible as there is no way to determine which date dimension members should be aggregated over inside the calculation - my preference here is Marco Russo's datetool utility dimension, so it's a separate dimension instead of what you have here - a separate attribute. Just to clarify this consider you have 2 date dimensions  and both of them are present in the slicer with a single month selected - for example Order date and Ship Date. Should the YTD calculation return YTD for Order date or Ship date? I don't think YTD(Order Date) * YTD(Ship Date) would be the correct answer also.

    But maybe you have a specific usage in mind that would simplify this ambiguity so for example to only work on a single date dimension that is present on one of the axis. This could be achieved using the universal calculated measures approach (by Tomislav Piasevoli) but I'm afraid it would add much more complexity to your project.

    To be honest, i really think this kind of calculations are responsibility of the client application the same way as Excel provides different display modes of values and relative calculations. Trying to create overly smart mdx scripts that account for everything is the wrong approach IMHO. For example, I've created a demo cube that has all kinds of detectors regarding subselects, visual totals etc, but it is only useful as an example that it wouldn't scale and we have to live with familiar current member errors, wrong totals and all other consequences of not having complete control. There are many examples where a bit of client application coding could replace a huge amount of complex mdx calculations - take for example something so simple as previous row (not member), row number, current coordinate - you get all of that pretty simple in an Excel macro using the pivot table object model.

    Let me know if you can provide more clarification to your planned usage regarding my comments - maybe there is a nice workable solution.

    Something just came to mind, would you be OK with having another helper dimension that would serve the purpose of selecting over which dimension should the calculations aggregate?

    Best,

    Hrvoje Piasevoli

     


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    Thursday, August 18, 2011 1:58 PM
  • thx a bunch Hrvoje, for the informative feedback.
    I need to then go back to the drawing board and assess my options, in light of your feedback!  thx again

    Thursday, August 18, 2011 2:11 PM