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