Planning Analytics

 View Only
  • 1.  ViewCreatebyMDX as TI datasource

    IBM Champion
    Posted Mon July 26, 2021 03:57 PM
    We've just started preparing our model for the 2022 budget and I was looking through our Bill of Materials load process to see if it could be optimised.

    As our business is primarly make-to-order, we need to create a calculation cube that contains only the valid customer/material combinations.  This step is critical to avoid blowing out the model (ie hundreds of thousand customers x hundreds of thousand materials).

    The current approach reads through the customer material sales cube and extracts the relevant BOMs by calling a subprocess that builds temporary subsets and attaches them to a temporary view. This process takes around 0.20 second per customer/material combination and runs over several hours.  

    My proposed approach was to shortcut this by using the ViewCreatebyMDX, as per the code below.  Once I worked around the known issue with empty MDX, I was pleasantly suprised when each combination was only taking 0.02 second. 


    This should have been the end of it, but two things happened:

    1. While running the process the memory increased by around 80gb ( not a big deal, but suprising given I was creating a temporary view, albeit with public subsets ).
    2. The process got stuck and could not be cancelled.  Even after 8 hours the only way out was to restart the model.

    Does anybody have any suggestions on what I might be doing wrong here?  Of course the traditional approach works, but prospect of making the process 10x faster remains tantalising!

    Cheers
    Errol









    #PlanningAnalyticswithWatson


  • 2.  RE: ViewCreatebyMDX as TI datasource

    Posted Tue July 27, 2021 03:49 AM
    Hi Errol,

    Perhaps you could apply the following parameters. They can 'limit' the size of the view and ignore certain cells (when set to 1). The last one, which ignores cells with zeroes in them, might be the most relevant for you.

    ViewExtractSkipCalcsSet
    ViewExtractSkipRuleValuesSet
    ViewExtractSkipZeroesSet


    ------------------------------
    Amarins van de Voorde
    ------------------------------



  • 3.  RE: ViewCreatebyMDX as TI datasource

    Posted Tue July 27, 2021 01:06 PM
    Hi, 

    how do you get the value of variables? 
    sScenario
    sSalesPlant_from
    sMaterial_from? 

    you can test your MDX query in subset editor by puting the MDX query in "expression window" and replace the variable with constant value just to test the query.


    Regards,

    ------------------------------
    Veronika Gultom
    ------------------------------



  • 4.  RE: ViewCreatebyMDX as TI datasource

    Posted Tue July 27, 2021 02:02 PM
    On item 1, I believe this is due to the difference between how TI normally creates and traverses a view versus how an MDX query works (Caching and Stargates). If correct, you should see the following behaviors as well.
    1) Adding DisableMTQViewConstruct() to the top of your TI should reduce the memory used, but could slow the TI down.
    2) Adding ViewConstruct to the end of Non-MDX version of the view create will create a similar spike in memory.

    ------------------------------
    Ryan Clapp
    ------------------------------



  • 5.  RE: ViewCreatebyMDX as TI datasource

    IBM Champion
    Posted Wed July 28, 2021 06:31 AM
    Thank you everbody for your suggestions.  I was somehow thinking the 'NON EMPTY' clause within the MDX was replacing what the ViewExtractSkip* commands were trying to  achieve.  I guess it makes sense that they're still required to limit the amount of work that the process needs to do.

    I also assumed DisableMTQViewConstruct() wouldn't be required as it would be less than the MTQ Threshold.

    The process seems to be working a little better after making these changes
    , but I still notice that there's a long delay in the process actually finishing and the process ending.   

    If you see below, I ran this process for one of the smaller manufacturing plants. The log reports the elapsed time as 267 seconds, but 239 of those seconds were consumed after the file had been processed and archived.  The processes that built the MDX View was finished at 8:33:47:55.  There's no transaction logging on this cube and I'm not doing a CubeSaveData, so I'm a bit confused why the process would be hanging right at the end.

    This was a 4 minute delay on one plant and there are 120 plants to process = 480 minutes (8 hours), which is consistent with the long delay I found when trying to run all plants sequentially. 

    Maybe there is some kind of cleanup going when using ViewCreatebyMDX that is not required when using the traditional method of building temporary subsets and assigning them to a view.   


    #PlanningAnalyticswithWatson