Author: Pranjal Agrawal is a Certified Master Anaplanner and a Senior Anaplan Consultant at Hinduja Global Solutions Limited (HGS).
The challenge
I was working for a client who at the start of their fiscal year introduced some new functionalities in Anaplan. They initially estimated that the additional data flowing in for the new functionalities would be below the maximum standard model size limit permitted by Anaplan, but the model started to reach close to that limit and there were still four more months to go. Simultaneously, as the data started to grow period by period, the model actions became cumbersome and started taking more time with each additional period. They approached me for a solution.
The techniques I used to fix the problem
I first asked the client about their license. As the client was on Enterprise License, Anaplan provides a free MAPS Report to its Enterprise License customers, so I asked them to get that report. MAPS Report is a highly efficient report to understand model’s deviation from Anaplan Best Practices and can be highly leveraged in any optimization scenario in question. The MAPS report provided a detailed break-up of the line items and modules that can be optimized, and what should be optimized in it. But it should just be used as a reference guide to identify the line items and not to be taken as a concrete optimization methodology as many of the details mentioned in it might not be suitable with the client’s requirements.
Like at many places it suggested to remove the summary settings as that line item is not referenced anywhere. But those were used in the Dashboards for reporting at hierarchy levels, so it was not feasible to remove the summary settings from them.
I first categorized all the line items mentioned in the MAPS Report separately and started investigating each of them one by one categorically to understand whether that line item can be optimized or not keeping the client’s requirements intact.
Once I had identified the line items that can be easily optimized, here is the main technique I followed for a fast-paced optimization:
I prioritized the optimizations based on efforts required vs outcome on a 4D matrix
Based on the set priorities I started working on Low Effort – High Impact optimizations first to get quick results. These were use cases like:
- Split the formula for line items that are using Sum-Lookup together: This is easy to split into an intermediary line item to hold the sum values and then update the target line item to just have lookup on the added intermediary line item. It although adds to the size of the model but helps save lot of processing time.
- Performing text concatenations in thin modules:
There were instances where we had to create TRIDs, and the TRID concatenations were taking place in the module with all the dimensions. For Example, there was a case where we were creating an Employee & Customer TRID in a module with dimensions – Employee, and Customer, where we had 3,000 employees and 1,000,000 Customers. Existing Formula:
Employee|Customer TRID: Employee Code & ”|” & Customer Code
I optimized it by shifting the code concatenation for 1st part into the Employee system module and the final output looked like:
Module 1: Employee System Module
Dimension: Employee
Line item: Code to Use
Formula: Employee Code & “|”
Module 2: Employee Customer TRID Creation
Dimension: Employee, Customer
Line Item: Employee|Customer TRID
Formula: ‘Employee System Module’.’Code to Use’ & Customer Code
This optimization helped reduce the model calculation efforts for TRID calculation where earlier each individual concatenation had to run 3000*1000000 times therefore 2 concatenations = 3000*10000002 = 6,000,000,000 calculations
After optimization it got reduced to 3000 (concatenations in Employee Module) + 3000*1000000 (Concatenations in TRID Creation Module) = 3,000,003,000 calculations.
- Remove additional dimensions if not required: There were places where a dimension is used although was not required, so I removed the dimensions from those line items and where possible placed it into its own separate module to avoid subsidiary views. Like, a line item named Employees was only used to filter the contents at the front end, the module and so the line item had 5 different dimensions including Employee. Here only the Employee dimension was relevant, so I removed the rest resulting in massive size savings. Initially the line item had 500 million populated cells, which post optimization dropped to just 3000 cells.
- Leverage Calculation Efforts column in model blueprint view:
There is a Calculation Efforts column in the Model Blueprint view initially a native feature of Polaris but recently added by Anaplan to the classic engine as well. This column tells us about the % time the line items took to process in last 10 minutes. To use it at its best, close the model from the Model Management Tab – Tasks section, and then once closed, re-open the model from home tab to get accurate details of the line items that takes the most time to process during initial model load. The higher the percentage, more the time that line-item took to process. These will be your key line items that you should look to optimize to help reduce model load times.
- Perform calculations in the low dimension modules and refer it in highly dense modules:
I identified some line items which were calculating sum in high density modules for the line items present in low density modules. For Example:
Module 1: Sales & Quota Data by Customer
Dimensions: Customer and Time
Line Items: Sales, Quota, Sales Representative
Module 2: Sales & Quota Data by Sales Rep and Customer
Dimension: Customer, Sales Representative, Time
Only Line Item: Sales & Quota
Formula: Module 1.Sales[SUM: Module 1.Sales Representative] + Module 1.Quota[SUM: Module 1.Sales Representative]
Here both the Sales and Quota values are present and referred from Module 1 only, still the calculation was performed in Module 2.
To optimize this, I added a new line-item Sales & Quota in Module 1 and performed the calculation in it (Sales + Quota) and then updated the formula in Module 2 as:
Sales & Quota: Module 1.’Sales & Quota’[SUM: Module 1.Sales Representative]
This saved the model calculations from earlier 300 million to now just 3000 calculations.
- Look at opportunities to optimize the actions taking most of the time to process:
I took an export of the Actions Tab of the client’s model. I sorted it by the processing time to identify which actions are taking most of the time to process. This helped me identify and act first on the actions that are time consuming, lowering the performance efficiency of the processes. Things to consider for actions optimization:
- Filter source data: There were actions in which a lot of data use to be imported from the source out of which most of it is irrelevant as already imported in the prior periods. So, I worked on filtering the source content to limit it to just the current period and removing other periods where it was feasible to limit the data from 12 billion cells earlier, scattered across multiple line items, to now just 1/12th of it viz. 1 billion cells.
- Keep single filter in the source saved view: During investigations I also found that some source saved views had multi-level filters applied, causing it to slow down as the engine had to filter data one-by-one for each of the applied filter first before getting the final view for the data import. I created a new BOOLEAN formatted line item inside the source module and merged all the filter conditions together in it and then used this newly created line item as the single filter on the saved views saving action load times.
- Optimize module calculations for list population actions: I noticed that there was an action that is used to populate the subsets and was taking exceptional amount of time to process. I checked that there were 3 different modules on which one of those subsets was applied, and there are some large nested if-else formulas used in those modules which calculate right after the subset population. The action was taking longer than expected times as the model starts calculating these specific formulas after the subset load which is also counted towards the action processing time. I looked for opportunities to optimize those calculations, and it resulted in savings in the action processing time.
- Replace the List and Text comparisons with BOOLEANs where possible:
There were instances where in some large IF-ELSE statements, lot of list-vs-list comparisons were present. For Example, Formula:
IF Module 1.P = List M.A THEN XXX ELSE IF Module 1.P = List M.B THEN YYY ELSE ZZZ
This had the model to compare the same line item to every list member multiple time in a highly dense module of 3 billion cells. As List M contained only 20 members, the IF-ELSE condition had 20 nested conditions, one for each member.
To optimize this, I replaced these IF ELSE conditions with BOOLEAN formatted line items.
Step 1: Create a system module with dimension List M – Module 2
Step 2: Create 20 separate line items one each for every list member as BOOLEAN formatted
Step 3: For Example: line item name: List Item = A. Formula: ITEM(List M) = List M.A or you can also make it BOOLEAN formatted to avoid hardcoding and keeping the options open for future updates directly in the production environment without having a need to push a change via development using revision tag.
Step 4: In the dense module, I updated the formula as:
Before:
IF Module 1.P = List M.A THEN XXX ELSE
IF Module 1.P = List M.B THEN YYY ELSE ZZZ
After:
IF Module 2.’List Item = A’[LOOKUP: Module 1.P] THEN XXX ELSE
IF Module 2.’List Item = B’[LOOKUP: Module 1.P] THEN YYY ELSE ZZZ
This helped reduce the list-item vs list-item calculations from 5 billion*20 list members to just 20*20 list members. As this module is using subsets, the change had major impact on the processing time of the actions attached.
The action to populate subsets which earlier took 90 minutes to process, got drastically reduced to just 15 minutes post optimization. It helped save action processing time by 75 mins making the action 80% faster than before.
- Use subsets in the final reporting modules:
There were three high density reporting modules that had full lists applied to it and had multiple dimensions. Due to this every single one of the modules had size of about 5 GB each. I checked to find that 80% of these modules were sparse data and only 20% cells were populated. I identified the source modules and leveraged them to create subsets for each of those dimensions to include only those list items that do have data associated with them instead of taking all the list items. These subsets were used to replace the existing dimensions. This helped me save 15 GB in model size for the three biggest reporting modules.
The outcome
Post these optimizations mentioned above, we asked for another Anaplan MAPS Report for an apples-to-apples comparison. And here are the results:
- Model size reduced by up to 35%, from 105 GB to 70 GB.
- Model actions processing time reduced by up to 80%, from 42 hours to 8 hours, if all the actions are processed simultaneously.
- Model availability increased for the end users as the daily process, which runs twice a day, earlier took 90 minutes per run, later got reduced to 30 minutes per run, thereby adding 480 hours of additional productivity for the client in a year. (About 2 hours a day in a 5 days’ work week.)
- Earlier, the daily load used to lock-out the end users from the model while the actions take place, this also got optimized and now the end users can access the model with minimal delay, even during the daily loads without getting locked-out from Anaplan.
- Some ad-hoc data load which used to take 90 minutes to process got reduced to 15 minutes. 90% productive efficiency achieved.
- It also helped with huge cost savings for the client, as earlier due to un-optimized model, the client was planning to either move to the Anaplan Hyperblock engine with model capacity of 720 GB or moving out of Anaplan to some other planning tool which will be less costly to maintain for a large model size. But now, as the size got reduced to just 70 GB, they still got 60 GB buffer left for additional data and new functionalities to be developed in the future.
Questions? Leave a comment!
