none
Recursive inventory computations in MDX

    Question

  • Hi,

     I need help with efficient MDX for the following computation. Four measures of interest, over time

    Replenishment, TargetInventory, Demand, BeginningOnHand, EndingOnHand.

    Demand and TargetInventory are measures which are defined elsewhere, and available at the point where the below computation needs to take place.

    At time t=0, BeginningOnHand(t=0) = 50, Demand(t=0) = 150, TargetInventory(t=0) = 100

    Replenishment(t=0) = Demand(t=0) + TargetInventory(t=0) - BeginningOnHand(t=0) = 150 + 100 - 50 = 200

    EndingOnHand(t=0) = BeginningOnHand(t=0) + Replenishment(t=0) - Demand(t=0) = 50+200-150 = 100.

     

    For future time buckets t > 1, the computation is defined as below:

    Replenishment(t) = Demand(t) + TargetInventory(t) - BeginningOnHand(t)

    BeginningOnHand(t) = EndingOnHand(t-1)

    EndingOnHand(t) = BeginningOnHand(t) + Replenishment(t) - Demand(t)

    I need to solve the above computation in time, with the recursion in time stopping at t=0, effectively in MDX. Any help will be greatly appreciated.

    Rajeev


    RK
    Thursday, October 21, 2010 3:43 AM

All replies

  • Hmmm, a couple of questions really...

    So what defines (t)? The date the user has selected (if so, what happens if multiple dates are selected), the current day or something else? Effectively, what defines the moment (t)?

    Also, the calculation for BeginningOnHand uses EndingOnHand and vice-versa (hence the "recursive" nature?) but the one calculation uses(t-1) and the other (t). As such, when you say stop when t = 0, is that based on the measure being calculated or something else? Can you give an example of what should happen?

    I am suspecting that this is probably best implemented using an AS Stored Procedure although you may be able to do this in pure MDX. Performance is a concern though...

    Thursday, October 21, 2010 8:26 AM
  • t=0 is actually "current week" . These computations are not needed in the past weeks, can be safely set to 0.

    At t=0, BeginningOnHand(0) = ActualInventory(0) - another measure which is readily available.

    I started implementing this in MDX, in the following fashion, but I am stuck.

    CREATE STATIC SET CURRENTCUBE.[CurrentWeek]  AS {LINKMEMBER([Current Time].[Reporting Week].FirstChild, [Date].[Reporting Week])};  

     

    CREATE STATIC SET CURRENTCUBE.[PastWeeks] AS Filter([Date].[Reporting Week].[Reporting Week].Members,        [Date].[Reporting Week].CurrentMember.Member_Value < CurrentWeek.Item(0).Member_Value);  

    CREATE STATIC SET CURRENTCUBE.[FutureWeeks] AS Filter([Date].[Reporting Week].[Reporting Week].Members,   [Date].[Reporting Week].CurrentMember.Member_Value > CurrentWeek.Item(0).Member_Value);

     

    // We can assume that the measures Demand and TargetInventory are available at this time for all time buckets.

    SCOPE(PastWeeks);

         [Measures].[Replenishment] = 0;

        [Measures].[BeginningOnHand] = 0;

        [Measures].[EndingOnHand] = 0;

    END SCOPE;

    SCOPE(CurrentWeek);

     [Measures].[BeginningOnHand] = 50; // For illustration only., actually will get it from another measure which is available.

     [Measures].[Replenishment] = IIF( [Measures].[Demand] + [Measures].[TargetInventory] < [Measures].[BeginningOnHand], 0,

                                                     [Measures].[Demand] + [Measures].[TargetInventory] - [Measures].[BeginningOnHand]);

    [Measures].[EndingOnHand] = [Measures].[BeginningOnHand] + [Measures].[Replenishment] - [Measures].[Demand];

    END SCOPE;

     

    SCOPE(FutureWeeks); // Not sure on how to set up the recursion here

    [Measures].[Replenishment]  = IIF( [Measures].[Demand] + [Measures].[TargetInventory] < [Measures].[BeginningOnHand], 0,

                                                     [Measures].[Demand] + [Measures].[TargetInventory] - [Measures].[BeginningOnHand]); // This depends on the next equation

    [Measures].[BeginningOnHand] = ([Date].[Reporting Week].CurrentMember.PrevMember, [Measures].[EndingOnHand]); 

    [Measures].[EndingOnHand] = [Measures].[BeginningOnHand] + [Measures].[Replenishment] - [Measures].[Demand];

    END SCOPE;


     


    RK
    Thursday, October 21, 2010 1:38 PM
  • Also, it will be great if someone in this forum can help me with how best to implement this logic above in AS stored procedure. Any pointers to the relevant documentation will be helpful.

     

     

     

     


    RK
    Thursday, October 21, 2010 1:41 PM
  • An AS Stored Procedure is not likely to be the best answer here. There is a lot of overhead in marshalling data between the unmanaged code in SSAS and the managed .Net code in a stored procedure. They are not really designed to be used in place of MDX in calculated measures.

    The best approach is likely to be creating a snapshot table in your ETL and then creating a cube with a LastChild or LastNonEmpty style of measure. It means the size of your fact table will blow out, but it will give you better query speed than the alternatives.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, October 22, 2010 2:59 AM
    Moderator
  • Of course, this logic could be written externally.

    The reason I want to script this in MDX is - user can change Demand values, using writeback, and I want the calculation for the other measures to happen immediately. In effect, by changing some values, the user can see the ripple-effect in time for the other values. Cannot ask the user to wait for an ETL to happen before he gets his answer.

     

     

     


    RK
    Friday, October 22, 2010 3:23 AM
  • Of course, this logic could be written externally.

    The reason I want to script this in MDX is - user can change Demand values, using writeback, and I want the calculation for the other measures to happen immediately. In effect, by changing some values, the user can see the ripple-effect in time for the other values. Cannot ask the user to wait for an ETL to happen before he gets his answer.

    Well that's new information, but I would still be looking to push as much of the work externally as you can. It does not sound like Stored Procs would be suitable in this situation, recursive MDX would work, but there is a good chance that performance would be a challenge.

    Are you saying people update historic Demand figures, or do they just adjust those for the current period?


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, October 22, 2010 6:45 AM
    Moderator
  • My reasoning behind using an AS Stored Procedure was because I anticipated some form of updates taking place, though re-reading the question again I'm not sure why, and figured that the marshalling of the code may well be worth the overhead over recursive MDX in that scenario. That may well be complete rubbish but hey...

    You could have a look at the AS Stored Procedure project (i.e. http://asstoredprocedures.codeplex.com/ ) and go from there. The project that may be best suited for customisation in your case may be the "TimeToCalculate" one but I haven't looked in any great detail.

    Still, best take what I am saying with a "pinch of salt" and all that as recursive MDX may well perform better. Testing of all options is naturally always a recommendation, though time is usually an issue.

    Friday, October 22, 2010 9:04 AM
  • The demand is not historical, it is a forecast, and user can change it in any time bucket starting from current time.

    Basically, when the demand forecast changes, all the related signals (which have been setup in the computation) should change, and the change in the replenishment numbers are of primary interest. 

    The recursive MDX I have written seems to work now, but is slow.

    Let me look at the stored procedure option too.

     

     

     


    RK
    Friday, October 22, 2010 12:06 PM
  • This is a little bit of an aside, but the "best practices" in terms of the use of stored procedures are:

    • for administrative functions
    • for static sets, scopes, LHS of assignment and other early-binding things
    • It is usually OK to use sprocs for query axes
    • It is usually OK to use sprocs if they don’t need to get cell data (but only look at metadata and members)
    • inside calculations if there is good NEB that can be defined on the calculation
    • inside calculations without NEB if sproc is executed over small amount of cells, i.e. inside WHERE clause etc

    I'm one of the contributors to the AS Stored Procedure project on codeplex and these guidelines came out of discussions that we had with a member of the product team.

    You are welcome to do your own tests, but my expectation would be that a stored proc solution with the same logic would be many times slower than the equivalent MDX in this situation.

    I'm wondering If there is someway to calculate this differently, maybe adding up cumulative movements and cumulative demand to calculate a position at a point in time. You would still be "touching" the same amount of cells to calculate a given value, but there were some specific performance enahancements made in SSAS 2008 to try to optimize running sums which possibly may lead to slightly better performance if an approach like this is possible.


     


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Saturday, October 23, 2010 1:37 AM
    Moderator
  • Thanks for the suggestion Darren. I tried re-writing MDX using the cumulative approach you suggested.

    Basically, Replenishment(t) = max(0, Demand(t) + TargetInventory(t) + CumulativeReplenishment(t-1) - CumulativeDemand(t-1) - BeginningOnHand(0)),

    where

    CumulativeReplenishment(t) = SUM( time from 0 to t, Replenishment).

    Ignoring the max (not sure how to take the max of 0 and the recursive sub-computation in MDX), here is the MDX which seems much faster than before. I need help on incorporating max into the cumulative computation, so I avoid negative values.

     

    CREATE MEMBER CURRENTCUBE.[Measures].[First On Hand] AS

     (CurrentWeek.Item(0), [Measures].[OnHand  Inventory]);

    SCOPE(FutureWeeks);

    [Measures].[Replenishment] =   [Measures].[Demand] + [Measures].[Target Inventory]  - [Measures].[First On Hand]   -SUM({CurrentWeek.Item(0):[Date].[Reporting Week].CurrentMember.PrevMember},  [Measures].[Replenishment] - [Measures].[Demand]);

    END SCOPE;

    Good so far, the computation is much faster.

    Now, I want to avoid negative replenishment, for which I need to modify the equation above to:

    [Measures].[Replenishment] =   MAX(0,[Measures].[Demand] + [Measures].[Target Inventory]  - [Measures].[First On Hand]   -SUM({CurrentWeek.Item(0):[Date].[Reporting Week].CurrentMember.PrevMember},  [Measures].[Replenishment] - [Measures].[Demand])};

    But the above MAX usage seems incorrect. 
    CellOrdinal 6
    VALUE #Error MdxScript(BusinessPlanner) (198, 12) The  function expects a tuple set expression for the 2 argument. A string or numeric expression was used.



    RK
    Saturday, October 23, 2010 4:23 AM
  • I think that's because you are using the "Max" function as you would in a programming language. In AS the Max function is an aggregation function and thus expects a set followed by a numeric expression. So you need to rewrite that to use "less than".

    Something like:
    IIF([Measures].[x] < 0, 0, [Measures].[x])

    Saturday, October 23, 2010 8:33 AM