Best Of

Author: Jessie Zhao is a Solution Architect and Certified Master Anaplanner at Anaplan.

Working with a model in ALM has a lot of benefits. These include building new features in a development model anytime without affecting production users, securely managing production lists by identifying structural data that needs to change in the deployment environment without risking an overwrite, and achieving model synchronization through formal revision control by leveraging the tagging of model changes. But there is one challenge — rolling back the production model to recover lost data is more complex in ALM models because of the potential compatibility issue between the development model and production model.

Recently, it happened that I needed to roll back one production model to restore lost data caused by deletion of ‌list members from a production list. There had been three more revision tags added and a few hundred new inputs added to the production model after the deletion was run. My plan was to roll back to the history ID before the deletion happened to confirm the cause and get a copy of the lost data, and roll forward to the latest user change ID so that the few hundreds user edits between the problematic action and the latest user change wouldn’t be impacted, while I could use the copy to add back the lost data.

Steps I took (hint: don't follow these steps!):

1. Change production model mode from deployed to standard offline.
2. Roll production model back to the history ID before the deletion action with “unlink revision created after this restore point” flagged.
3. Confirm prod model at the ID has lost data and make a copy (I know there'll be a copy revision tag added but will also be removed after Step 4).
4. Roll model forward to the latest user change history ID with ““unlink revision created after this restore point” flagged.
5. Add a new revision tag in Dev model and sync to Prod.
   

Everything looked good as the latest revision tag showed to be compatible to the DEV model initially, but it returned to an error stating that the target model was not compatible to the source model. It was hard to figure out what — except the copy model action — could cause the incompatibility.

For time’s sake, I gave up reconnecting the production model to the original DEV model and started to create a new DEV model from the latest revision tag existing in the production model. But, when I synced the revision tag after DEV model was created, it would return the same incompatibility error. It’s wasn't until I added a new revision tag in the production model first, and created DEV model from this newly created revision tag, did I successfully sync the revision tag between the new DEV and prod model.

To find out what caused the incompatibility, I recreated the issue with a test production model and a test development model and went through the same steps again.

Attempt #2

With more time to compare changes between models, I found that flagging “unlink revision created after this restore point” (revision tag 1) when rolling back and forward the model removed revision tag 2-4 from the production model, but keeping the structural changes made in revision tag 2-4 in production model, which in fact unsaved structural changes in production model. The available “add revision tag” and “revert to last revision” after rolling forward model also confirmed the idea (screenshot 1). At this point, reverting to last revision tag, meaning reverting to revision tag 1 “run delete action for account 7-9", would restore the compatibility (screenshot 2) but changes made in revision tag 2-4 as well as user inputs after the history ID associated with revision tag 1 would be erased. Given that, saving all changes by creating a new revision tag in this production model and creating a new development model would be the solution at this point.

Screenshot 1:

Screenshot 2:

Knowing what "unlink" does to the revision tag, I became curious about what would happen if I unflag “unlink revision after this restore point” when rolling back and forward model. I tested the idea and found that revision tag 2-4 will stay, but a new revision tag of copying model as well as history ID associated with it would be added. The copy model revision tag makes the prod model incompatible again and it would need to take the same solution as able to create a new development model, or use the revert feature which potentially could cause user input after revision tag 4 erased.

After the above two tests, I found that unflagging “unlink revisions created after this restore point” when rolling back the prod model to keep structural revisions while flagging the “unlink revision created after this restore point” when rolling forward the production model to eliminate copy model revision tag would be the best way to keep compatibility between the original Dev and Prod models.

The correct steps should be:

1. Change production model mode from deployed to standard offline.
2. Roll production model back to the history ID before the deletion action with “unlink revision created after this restore point” UN-flagged.
3. Confirm prod model at the ID has lost data and make a copy.
4. Roll model forward to the latest user change history ID with “unlink revision created after this restore point” flagged.
5. Add a new revision tag in Dev model and sync to Prod.

Hope my experience of rolling back production model could be helpful for you when you face the same situation!

Questions? Leave a comment!

@DavidSmith Can some of the images be reloaded and bigger? Even with zooming and clicking on images, text is blurry. Thank you for the post!

Author: Kay Kuang is a Certified Master Anaplanner and Principal Data and Insights Architect at Anaplan.

Actively managing the users list to keep the list small and to keep only users who need access to the model will make the model go a long way. The users list can have a big impact on the model's performance — especially if a model has a lot of users. Setting users who no longer need access to No Access can decrease the model size and make the model run more efficiently. (Caveat: removing a user from the users tab will remove this user from all the models in the same workspace. You want to be very careful to do that.) Besides, if inactive users or other users who no longer need access to the model remain the model, it can compromise the data security in the model. This can be a big issue if the model houses highly confidential data.

However, manually checking inactive users and setting end users to No Access can be tedious especially if there are a lot of user changes regularly. In this post, I want to share the practice of using import action/process to manage users in the model. In this way, you can set up formulas to change people’s access, which provides a lot of flexibility. Then you can set up the process in a recurring schedule to update the users list so that you don’t have to manually do the work.

Steps to take

1. Think about how you plan to decide if a user doesn’t need access to the model.
   You can choose to manually flag users, or you can pull data from somewhere else to support the decision. Depending on the business needs, it could be the termination date (e.g. if you want to remove a user if that user terminated for over 30 days), or cost center (e.g. only users in a certain cost center should have access to), or anything other data that you want to look at to make a decision.
2. Create a data module and prepare the user data set.
   This step is not necessary if you plan to manually flag the users who don’t need access anymore. The employee data set must have accurate employee emails, which will be used to map employees to the users list. You can pull the data from Workday or other systems that house the data that you plan to use. One necessary step is to map the data set’s dimension to the user list. That’s why it's critical to have employee emails included in the data set.
   
3. Create a module dimensioned by users, which will be the output of list of users to set to No Access.
   If you plan to manually flag users, then you can do it in this module. This module can be published to a dashboard and people can come in to tag and mark those who no longer need access to the model. If you decide to use data to drive the process, then this module will be the place you set calculations to check if a user still needs access to the model.
   You can use FISTNONBLANK or LASTNONBLANK to connect the user data set to this calculation module (see calculation snapshot 1). A caveat is the calculation could be inaccurate if there are multiple records with the same email address. You can set up a validation calculation to check if anything should be watched out for (see calculation snapshot 2).
   Another necessary line item is the “No Access” line item. This will be the data that goes into the role field in the users tab. It should be formatted as a text field. It should be the last step to combine all the data logic you want to look at to exclude a user and then populate “No Access” in this field (see calculation snapshot 3).
   Calculation snapshot 1:
   
   Calculation snapshot 2:
   
   Calculation snapshot 3:
   
4. Create saved view.
   With all the calculations set up... Now you just need to set up a saved view with only users marked as “No Access.”
5. Create the action and process.
   After the saved view is set up, then you can go to the users tab. Click “import” then find the saved view you just created. You can follow the mapping below to set up the import action. After you have the action set up, you can bring this action to a scheduled process to let this process run on a recurring basis.
   

Questions? Leave a comment!

Hi! Welcome to the Model Optimization team's monthly blog post. This is an eight-part series, following the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.

Each month, two of our analysts will take you through how to spot and improve these formula structures! We hope these can help with your model performance.

All the best - Cianna Bramley (Model Optimization Manager)

……

This month's blog is brought to you by Kenneth Privaldos and Lip Chean Soh.

Last Month, we discussed the importance of Calculating Once and Reference and avoiding repeated calculations in a model. This month, we’re going to discuss how the combination of two functions can affect the performance of your model, the use of SUM and LOOKUP together.

In P.L.A.N.S., the standard of Anaplan modeling, this falls under Performance, or the use of correct structures and formulas to optimize the Hyperblock. Furthermore, it is specifically mentioned in the Zen of Anaplan —

“Sums and lookups are good, but never together.”

But why?

Why is this bad?

Referencing Planual 2.02-08, the usage of SUM and LOOKUP together can create a large number of formula calculations which can include slow intermediate ‘relationship’ calculations; as the engine creates virtual mapping modules to calculate these functions.
For a [SUM: X, LOOKUP: Y] format, the Anaplan engine would perform the SUM first before doing the LOOKUP. The format seems simple but here’s the caveat, the SUM is repeated for every LOOKUP. This results in poor performance and inefficient calculations.

Now that we know the “Why?” The next question is “How?”. How do we find this in our model?

Looking for SUM and LOOKUP together

There are 3 methods to find these in your model, and these are:

1. Manual inspection
2. MAPs
3. Model optimization

For manual inspection, you can search the blueprint of the module and look for a line item combining the SUM and LOOKUP. Alternatively, you can download the line items and search for SUM and LOOKUP in your csv file.

You can also request MAPs (Model Analysis Pro-Active Solution), from your Business Partner, which can provide Planual infractions — however this is currently not available for all models (please ask your Business Partner to check).

Lastly, if you request a Model Optimization (Model Performance Analysis) with our team, we check Planual infractions such as SUM - LOOKUP as part of our analysis.

How do we fix these instances in models?

We’ll cover three examples of SUM and LOOKUP together and provide an optimized version of each line item. Generally, to fix the issue, we recommend splitting the SUM and LOOKUP function by using an Intermediate line item. Here’s the reason why:

1. By using an intermediate line item, we calculate the functions separately, which stops the engine repeating the functions; like SUM calculating for each LOOKUP.
2. The function calculating in the intermediate can be calculated over different dimensionality; in some cases, this can be a smaller cell count.

In most cases, the SUM and LOOKUP are related, and so the SUM would most likely be the intermediate calculation (as this must be calculated before the LOOKUP can be applied). However, if the SUM and LOOKUP act on different dimensions, the LOOKUP could be extracted first (aim to extract the function which gives you an intermediate line item with the smallest cell count to help save space).

Here are the steps in converting a SUM-LOOKUP:

1. Create an intermediate line item that will perform the SUM First. This line item should not need aggregation (e.g. ‘Sum’ summary method).
2. In the target line item, replace the source with the intermediate line item.

Example 1: SUM and LOOKUP with different mappings and dimensions

Below is an example of a line item that contains SUM and LOOKUP.

Formula Breakdown

Example 1 = 'DAT - Product Quantities'.Qty[SUM: 'SYS P1 Products'.Country, LOOKUP: 'SYS G1 Countries'.Standard Sizing]

Taking a closer look at the formula:

Target Line item = Source [SUM: Mapping 1, LOOKUP: Mapping 2]

To create the intermediate line item, we need to determine the correct dimensions for the SUM. This table contains the dimensions of the line item that we’re going to work with.

Table: Dimensions and format of Intermediate, Source, and Mapping line items in Example 1.

1.1 Create an intermediate module for calculating the SUM

When using the SUM function:

1. Add the list used in formatting of the Mapping line item to the dimensions of the target line item.
2. Remove the dimension of the mapping line item in the target line item dimensions.

Intermediate Line Item Dimension (SUM) = Source Dimensions – Mapping Dimensions + List Used in formatting (Mapping)

Intermediate Line Item Dimension = P1 Products, Products Sizes, Month(Time) - P1 Products + G1 Countries

Since we know the dimensions for the SUM function, we can now create another module that will contain these dimensions.

Inside the module, create an intermediate line item that will calculate the SUM function.

Intermediate SUM = 'DAT - Product Quantites'.Qty[SUM: 'SYS P1 Products'.Country]

1.2 Reference the intermediate line item in the target module

We can reference the intermediate line item. With this, we optimize the original formula by splitting the SUM and LOOKUP functions.

Example 1 = ‘Intermediate Module 1’.Intermediate SUM [LOOKUP: 'SYS G1 Countries'.Standard Sizing]

Example 2: SUM and LOOKUP with more than 2 mappings

For this example, we’re going to optimize a line item that contains two SUM functions and two LOOKUP functions. It’s similar to our previous example and our goal is to split the SUM and LOOKUP by using an intermediate line item.

Formula Breakdown

Example 2 = DAT Product Cost.Shipping Cost[SUM: 'SYS P1 Products'.Country, SUM: 'SYS P1 Products'.Brand, LOOKUP: 'SYS G1 Countries'.Standard Sizing, LOOKUP: 'SYS P1 Products'.Product Type]

Target Line item = Source [SUM: Mapping 1, SUM: Mapping 2, LOOKUP: Mapping 3, LOOKUP: Mapping 4]

Note: the SUMs appear before the LOOKUPs here, however this same suggestions would apply to other formats, e.g. [SUM: 1, LOOKUP: 3, SUM: 2, LOOKUP: 4] can have the 2 SUMs extracted to an intermediate line item together.

This table shows all the formats and dimensions of the line items we’re going to work with.

Table: Dimensions and format of Intermediate, Source, and Mapping line item in Example 2.

1.1 Create an intermediate module that will contain the SUM functions

To determine the dimensions, we can still apply this rule:

Intermediate Line Item Dimension (SUM) = Source Dimensions – Mapping Dimensions + List Used in formatting (Mapping)

The Dimensions for this line item are: G1 Countries, Products Sizes, Brand, Product Type, Month(Time).

Intermediate SUM = DAT Product Cost.Shipping Cost[SUM: 'SYS P1 Products'.Country, SUM: 'SYS P1 Products'.Brand]

1.2 Reference the intermediate line item in the target module and perform the LOOKUP function.

Example 2 = ‘Intermediate Module 3’. Intermediate SUM[LOOKUP: 'SYS G1 Countries'.Standard Sizing, LOOKUP: 'SYS P1 Products'.Product Type]

Example 3: SUM and LOOKUP with similar SUM mapping and source line items with the same Applies To dimensions

If we have two line items with the same Applies To dimensions using SUM and LOOKUP with similar mapping in the SUM function, we can extract the SUM and apply the rules for optimizing SUM and LOOKUP and Calculating Once and Reference.

Line Item 1:

Line Item 2:

Target Line Item 1:

Table: Dimensions and format of Source and Mapping line item in Example 3.

Target Line Item 2:

Table: Dimensions and format of Source and Mapping line item in Example 3.

1.1 Create an intermediate module for calculating the SUM function

Create an intermediate line item in another module for calculating the SUM function. The dimensions are: G1 Countries, Product Sizes, Brand, Product Type.

1.2 Reference the Intermediate line item

For target line item 1:

For target line item 2:

Special cases

While it’s generally recommended to avoid using SUM and LOOKUP together, there are a few exceptions for leaving it unchanged:

• LOOKUP as SELECT: When the LOOKUP function acts like a simple value selection. This happens when the LOOKUP value is a constant, or single cell. Hence, the SUM is only performed once. It can be treated like using SUM and SELECT together.
• Very large intermediate line items: Creating an intermediate line item can sometimes results in much larger cell counts due to the required dimensions for the SUM function. If the size of the intermediate line item created heavily impacts your model size, leave the formula unchanged. However it’s good to note that intermediate line items should not need ‘summary’ (aggregation), so this should help keep the cell count as low as possible.

Conclusion

Combining SUM and LOOKUP function together can cause slow models. To solve this, we recommend splitting the SUM and LOOKUP using an intermediate line item. While exceptions exist, it is generally recommended to avoid using SUM and LOOKUP together. This results in a faster calculation because the SUM is not repeated for every LOOKUP.

Questions? Leave a comment!

Author: Jessie Zhao is a Certified Master Anaplanner and Sr. Data & Insights Architect at Anaplan.

Please note: MAPS reports are dependent on a few key qualifications and may not be available to all customers. Please check with your BP if you have questions.

As a CoE member, model building — either for new user stories or enhancements — takes up most of my time in daily work. However, model optimization, which is usually not as urgent as all those building requests from business every day, is important to keep a model efficient and last long if it’s done periodically. There are many best practice resources in Anaplan Community that we could use to optimize our model, but I like MAPS reports the most, as it provides actionable recommendations specifically to my model and gives sufficient details down to the line item level. In addition, the Priority Matrix chart is very handy for me to decide my optimization plan so that I can maximize the result according to different timeline and resources that I have. If I have only a day to use for model optimization, I would focus only on “quick wins” items. If I have more time (like 1-2 weeks), I would move to the “high impact and effort” bucket to address those Planual infractions.

I’d like to share my experience in using MAPS reports to optimize GTM planning model in two scenarios:

1. When I have a day to optimize model performance.
2. When I have a week to optimize model performance.

Steps I take when I have one day for model optimization

Step 1: Read the Model Report, especially the finding and Priority Matrixes to know the top 2-3 Planual infractions to focus on.

For my model, the top five Planual infractions (from 1-5 descending) are:

1. Try not to use TEXT as a format
2. Unreferenced summaries
3. Avoid using subsidiary views
4. Break up formulas
5. Line item over-dimensionalized
   

However, because TEXT format infractions is in the “High Impact & Effort” bucket, and Subsidiary Views are in the “Minor Improvements” bucket, I’d skip these two and only focus on the Unreferenced summaries, break up formulas, and line item over-dimensionalized given the limited time I have.

(Priority Matrix of GTM planning model as below)

Step 2: Read the line item detail report, decide the reasonable amount of line items need to be revised for selected Planual infractions in Step 1, and make those changes.

Below is what I did, while respecting the three quick win Planual infractions I chose in Step 1 in detail:

• For unreferenced summary, I started with unreferenced summary by filtering unreferenced summary = true and sorting by summary calculation time. I find there are 11 line items with high summary calculation time, over 1s. By revising these 11 line items’ summary method to none, I could potentially reduce calculation time by 27s according to their summary calculation time provided by MAPS. (Keep in mind calculation time is more of a volume rather than experienced calculation time by a user — e.g. If calculation volume drops by 25 seconds, the user may see the calculation time be a few seconds quicker.)
  
• For break formulas, I continued to break formulas by filtering with formula split recommendation = true and sorting by formula calculation time. Line items that are suggested to slip formulas has higher calculation avg. calculation time but for the sake of time, I edited the top 26 line items, whose formula calculation time sum to be 205s. It means, I could potentially reduce total calculation by 205s. I found most are user-filter related. having ISBLANK() calculated in a line item in user filter module with user dimension could save tons of time than having it calculated in target module with user and account dimensions.
  
• For line item over-dimensioned, I filtered with over-dimensioned column = true and BPI = medium/high/very high and sorted the file by descending total calculation time. I focused on the top 15 total calculation time and revised 10 out of 15 line items, which potentially can reduce calculation time by 69s.

Step 3: Make notes on the line item details spreadsheet of changes made above.

Keeping track of changes can help make a comparison of model performance when the next MAPS report is out to adjust optimization focus and plan for further optimization when you have additional time or additional resources to allocate.

Result: after revising around 50 top calculation line items from quick win in one day, model open time is reduced by 5.4s and total calc time is reduced by 10.5 min. I’d say it’s a good result considering how much time required to achieve it. No wonder it’s called the quick win category!

Steps I take when I have 1-2 weeks for optimization

When I can allocate more time in optimizing model calculation, I look into the high impact, high efforts text line items. I’d take similar steps in optimizing text line items. First, I use line item detail report and line item report to filter only the text as a format Planual infraction and rank them by their formula calculation time. In doing so, I could change line item with the most calculation time first. Second, I’d focus on top 20% line items, whose calculation time is over 5s in my case. Third, I’d keep a note for comparison with the new MAPS report. I find text line items can be categorized into 3 types:

1. Code and name for display or calculation: There is not much you can do to optimize except reducing the dimension when over-dimensioned.
2. Text in output module mapped from data module: In this case, a new list can be created and replace the text line item in all modules.
3. Text line item in modules not used anymore: This is where changes have the most impact. Reviewing text line items brought some unused text heavy modules into my attention and deleting those modules is the most efficient way of solving text line item as well as reducing model size. I was able to reduce model size by 3GB.
   

Result: After this round of optimization, the model’s total calculation time is reduced by 50% from 9756s.

Hope my experience of optimizing a model with MAPs reporting is helpful!

Questions? Leave a comment!