theplanual

The definitive set of standards for Anaplan model building.

Read It

Let's talk about it

Discuss what you learned from these best practices and your own experiences in the Forums.

Visit Forums
Learn how small changes can lead to dramtic improvements in model calculations
View full article
What are the benefits and drawbacks of using Versions instead a General List
View full article
Filters can be very useful in model building and are widely used, but they can come at the expense of performance—often very visible to users through their use on dashboards. Performance can also hit imports and exports, which in turn may lead to the blocking of other activity, causing a poor perception of the model. There are some very simple guidelines to design well-performing filters: Using a Single Boolean Filter on a Line Item That Does Not Have Time or Versions Applied and Does Not Have a Summary Is Fastest Try to create a Boolean line item that incorporates all the filter criteria you want to apply. This allows you to re-use the line item and combine a series of Boolean line items into a single Boolean for use in the filter. For example, you may want to filter on three data points: Volume, Product Category, and Active Status. Volume is numeric, Product Category is a list formatted line item matching a user selection, and Active Status is a Boolean. Create a line item called Filter with the following formula: Volume > Min Vol AND Product Cat = User Selection.Category AND Active Status Here’s a very simple example module to demonstrate this: A Filter line item is added to represent all the filters we need on the view. Only the Filter line needs to be dimensioned by Users. A User Selection module dimension only by Users is created to capture user-specific filter choices: Here’s the data before we apply the filter:  Here's the data with the filter applied: A best practice suggestion would be to create a filter module and line items for each filter part. You may want other filters and you can then combine each filter as needed from this system module. This should reduce repetition and give you control over the filters to ensure they can all be Boolean. What Can Make a Filter Slow? The Biggest Performance Hit for Filters Is When Nesting Dimensions on Rows. The performance loss is significantly increased by the number of nested dimensions and the number of levels they contain. With a flat list versus nested dimensions (filtering on the same number of items) the nested filter will be slower. This was tested with a 10,000,000 list versus 2 lists of 10 and 1,000,000 items as nested rows; the nested dimension filter was 40% slower. Filtering on Line Items With a Line Item Summary Will Be Slow. A numeric filter on 10,000,000 items can take less than a second, but with a summary will take at least five seconds. Multiple Filters Will Increase Time. This is especially significant if any of the preceding filters do not lower the load because they will take additional time to evaluate. If you do use multiple filter conditions, try to order them so the most effective filters are first. If a filter doesn’t often match on anything, evaluate whether it's even needed. Hidden Levels Act as a Filter. If you hide levels on a composite list, this acts like a filter before any other filter is applied. The hiding does take time to process and will impact more depending on the number of levels and the size of the list. Avoid Nested Rows for Export Views Using nested rows can be a useful way to filter a complex set of data for export, but, as discussed above, the filter performance here can be poor. The best way around this is to pivot the dimensions so there is only one dimension on rows and use the Tabular Multi Column export option with a Filter Row based on Boolean option. Some extra filter tips include the following:  Filter duration will affect saved views used in imports, so check the saved view open time to see the impact. This view open time will be on every use of the view, including imports or exports. If you need to filter on a specific list, create a subset of those items and create a new module dimensioned by the subset to view that data.
View full article
What happens to History when I delete a user from a workspace?
View full article
The Planual provides a systematic set of standards for model building on the Anaplan platform. The rules in it are designed produce the most efficient, usable, and scalable Anaplan models, while dramatically increasing performance for models in all contexts. We highly recommend that all model builders familiarize themselves with these standards, and start incorporating them into their model-building practices. (The results will be significant!)
View full article
As a business operations manager on the Anaplan on Anaplan (AoA) team—an internal team, focused on bringing Connected Planning to life within Anaplan—I help to oversee our internal Anaplan model ecosystem and assist in the solutioning and development of Anaplan models across all of our functional business groups.  As Anaplan's largest customer, one of the numerous requirements we must address is user access and security. Utilizing Anaplan's user roles functionality typically gets the job done for granting users access to specific models. Occasionally, we must go one step further and leverage Anaplan's selective access feature. Roles and selective access are powerful tools and address our needs nearly all of the time. However, as we scale our own use of Anaplan, we have begun to encounter the need to provision user's access to lists based on multiple criteria, rather than just a single condition.  In Real Life A real-life user provisioning challenge we’ve encountered is in our headcount planning model. As this model provides real-time reporting on our employees, there are inherent sensitivities and considerations around who can see information for specific employees—taking into consideration visibility to things like compensation and personally identifiable information (PII). We have multiple use cases built out within the model, including recruiting capacity and analysis, attrition reporting, hiring reporting, etc., and the access to specific employee data depends on the end user of the model. Sample employee roster: Joey manages Usain, Eluid, and Meb; Americas Geo; HR Cost Center. In this example model, we have our complete employee roster included. If an HR business partner accesses the model, we want them to see only employees that are tagged to the functional area they support (e.g. finance, sales). Additionally, if a business manager goes into the model, they should only see information for employees where they are the manager, or employees downstream on their management chain. But wait! If the HR business partner is in Europe, they shouldn’t be able to see PII fields for their employees. Do you see how this could get complicated quickly? Additionally, some dashboards that contain non-sensitive employee information are perfectly fine to open up broadly to all users, while others contain sensitive data we need to provision. What’s Next So, how do we handle this? We can’t provision access by roles because all of the aforementioned users need access to the same modules/dashboards as it relates to the employees they manage. Additionally, no single user should be able to see all data for all employees. Selective access could be considered as a solution, but given the levels of complexity and multiple logical drivers—as well as the requirement to not hide reporting of non-sensitive data for employees—that option also has limitations. Enter Dynamic Cell Access (DCA). Since DCA allows us to base read/write access off of formulae logic, it offers us the ability to layer on multiple levels of logic ahead of deciding whether or not someone should be able to read or write on a particular item in a list. It’s dynamic (who would have thought with that name?), which means it adjusts live as data within the model changes. Additionally, it offers us the flexibility to apply the provisioning logic to the exact modules we want to, rather than blanket provision users across the model. DCA In Action The following is a high-level example of how to leverage the power of DCA: Load employee roster data into Anaplan, ensuring the data contains the employee email—the same email that is used to log in to Anaplan. This allows for the mapping of Anaplan users to the employee roster. Set up a System module with the ‘applies-to’ list of the user list. User meta-data staging module: Rows represent model users (Joey, in this example) and the line items represent meta-data off of the roster module. Within this module, we can join the employee roster data and the user list to map the employee’s meta-data to their Anaplan user profile (e.g. cost center, location, management chain, etc.) Using a series of Boolean line items, we can write whatever logic we want to base our DCA on. In our example, this could include: Is HR business partner? Is Euro? Basically, this is a staging module for all of the employee meta-data we want to leverage to create our DCA drivers. Set up a second System module with the ‘applies-to’ list of whatever list you want to apply DCA against, as well as the user list. In our case, this would also be our employee roster list. Create a series of Boolean line items, testing different attributes of the User System module we just set up against the meta-data of the employees. An example would be (Employee Cost Center = User’s Cost Center). DCA logic module for the employee roster list (rows in this module): Line items represent the logic used to determine whether the user (Joey— in the page selector) can see the employee. The key here is to consolidate all of your logic into a single “Master” line item, which is on the far right. Daisy chain your conditions together as desired, with the end result being a master Boolean line item, which is the driver for whether or not a particular user has read or write access to a particular item within the list. In this dashboard you can see that the information is masked for those employees that did not meet all of the criteria identified in the master DCA line item. Select which modules you’d like to apply DCA to. The nice thing about DCA is you can go down to the line item level to map the master Boolean driver against. The incredible power of the process described above is not only the complete control over and ability to customize your user provisioning, but also that as new roster data is loaded into Anaplan, the DCA automatically adjusts itself to account for changes. So, if someone changed cost centers or a manager on an employee changed, the formulas that we set up above would be referencing the new employee meta-data, and would automatically adjust the DCA drivers, allowing for a much more hands-off, sustainable approach to user provisioning. Another inadvertent benefit we discovered with using this methodology is that Anaplan treats cells that are blank as a result of DCA drivers as being blank for filtering purposes. So, if you want to set up a dashboard that auto-filtered employees for the end user based on the logic above, you just have to add a line item hardcoded to contain values for every list item, and then filter that line item for not-blanks on your dashboards. Then you have a dynamic filter based on the user that is viewing the model…pretty slick! Take this one step further and filter for not-blanks on a line item that will always contain data for an employee, and you get completely custom reporting based on which end user is viewing the dashboards.
View full article
Line item subsets are one of the most powerful and efficient features in Anaplan, yet one of the least understood. The COLLECT() function is probably the only “black box” function within Anaplan as is it not immediately apparent what it is doing or where the source values are coming from. In the following article, I will cover how to understand line item subsets more easily, and also explain their many uses, some of which do not need COLLECT() at all. For more information on creating line item subsets see Line Item Subsets in Anapedia.  A line-item subset is a list of items drawn from one or more line items from one or more modules. Put simply, it converts line items into a list on which calculations can be performed. There are some restrictions: Line item subsets can only contain numeric formatted line items. Only one line item subset can be used as a dimension in a module. Although line items can contain formulas, the items in a line item subset can only aggregate to a simple subtotal.  Styles on the line items are not transferred over to the line item subset. Line item subsets can be used for many different areas of functionality. For the examples used, I have based them on the final model from the new Level 1 training. Download the model and follow the instructions to practice on the same structures.  These examples are deliberately simplified, but I hope you find these insightful and easy to transfer into your models to simplify the formulae and provide more flexibility to your users. Table of Contents: Calculations on Calculations This is the classic use of line item subsets. A source module contains line items, and subsequently, you need to perform additional calculations on these line items. While in some cases this can be managed through complex formulae, normally these workarounds break most of the best practice guidelines and should be avoided. Use Case example: The source module contains forecast data with line items for the profit and loss lines in U.S. dollars. We need to convert these values into local currency based on the Country dimension. The source modules are as follows: The first step is to create the line item subset, and for this report, we only want summary values. In the settings tab, choose Line Item Subsets and click insert. We recommend prefixing with LIS: the name of the module and simple description. Clicking on the Line Item Subset header item (in settings) will display the Line Item Subsets screen. Click on the newly created line item subset and the … and select the module(s) required; in this case, it is REP03. Select which line items you wish to include in the line item subset. Now that the line item subset has been created, it is available to be used in a module. Create a module with the following dimensions: LIS: REP03 Currency G2 Country Time (Years) Add the following line items: Base Currency Exchange Rate Local Currency In the Base Currency line item, enter the formula: COLLECT() Note that the values are the same as those in REP03 and the line items are now shown in list format (no formatting). Also note that these values are from the Forecast version, as the target module does not have versions, so the Current Version is used as the source automatically. Add the following formulae to the remaining line items to complete the calculation. Exchange Rate = 'DATA02 Exchange Rates'.Rate[LOOKUP: 'SYS03 Country Details'.Currency Code] Local Currency = Base Currency / Exchange Rate Note that the Exchange Rate line item should be set as a Subsidiary view (excluding the line item subset from the applies to) because we are showing it on the report for clarity. If this display was not required, the calculation could be combined with the Local Currency formula. Transformation You can also use a line item subset to help with the transformation between source and target modules. Use Case example: We want to summarize costs (from the reporting P&L) into Central and Locally controlled costs. Create a list (Controllable Costs) containing two members. Central Local Create a line item subset (as before) using just REP03 as the source module. Create a staging module with the following dimensions: LIS: REP03 Cost Reporting G2 Country Time (Years) Add a line item (Data) and enter COLLECT() as the formula. Set the Summary method to None; we do not need subtotals in this module. Create a mapping module, dimensioned by LIS: REP03 Cost Reporting. Add a line item (Mapping) formatted as the Controllable Costs list. Map the lines as applicable. Create a reporting module with the following dimensions. Controllable Costs G2 Country Time (Years) Add a line item called Costs. Add the formula: 'REP07 Cost Reporting Staging'.Data[SUM: 'SYS14 Cost Mapping'.Mapping] We use the SUM formula because the source dimension and the mapping dimension are the same. So, “If the source is the same, it’s a SUM.” Multiple Source Modules Line item subsets can contain line items from multiple modules. There is a caveat though; all modules must share at least one common dimension/hierarchy and/or have a Top Level set for non-matching dimensions. Use case example: Based on user-entered settings, we want to compare the values from two time periods for metrics from three different modules and calculate the absolute and % variances. The source modules all share a common dimension: REV03 Margin Calculation: G2 Countries, P2 Products, Month EMP03 Employee Expenses by Country: G2 Countries, Month OTH01 Non-Employee Expenses: G3 Location, E1 Departments, Month Note: G3 Location has a G2 Country as a parent The module for the user parameters is: And the metrics required are: Margin Salary Bonus Rent Utilities We could solve this problem without using a line item subset: Create a list (Reporting Metrics) containing the list items above. Create a module with the following dimensions. Reporting Metrics G2 Country Users The formula for Month 1 is: IF ITEM(Reporting Metrics) = Reporting Metrics.Margin THEN 'REV03 Margin Calculation'.Margin[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Salary THEN 'EMP03 Employee Expenses by Country'.Salary[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Bonus THEN 'EMP03 Employee Expenses by Country'.Bonus[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Rent THEN 'OTH01 Non Employee Expenses'.Rent[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Utilities THEN 'OTH01 Non Employee Expenses'.Utilities[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE 0 I won’t repeat the formula for Month 2, as it is effectively the same, just referencing the Month 2 line item in the source. You can see, that even for a small set of metrics, this is a large complex formula, going against best practices. So, let’s not do that. Create the line item subset as before. For multi-module line item subsets, it is best practice to use Multi> to represent the various modules. Open the line item subset and chose the three modules. Create a staging module (this is best practice following the DISCO principle), with the following dimensions. LIS: Multi>Variance Reporting G2 Country Time (Months) Add a line item (Data) and enter COLLECT() as the formula. Set the Summary method to None; we do not need subtotals in this module. Create a mapping module, dimensioned by Reporting Metrics. Add a line item formatted LIS: Multi>Variance Reporting. Map the lines accordingly. In the reporting module from above, change the Month 1 and Month 2 line item formulae to. 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1', LOOKUP: 'SYS12a Reporting Metrics Mapping'.Mapping] 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 2', LOOKUP: 'SYS12a Reporting Metrics Mapping'.Mapping] Note, this time we are using LOOKUP rather than SUM because the source dimension doesn’t match the dimension of the mapping module. I think you’ll agree that the formula is much easier to read and it is more efficient. However, we can do even better; but note that there now are two ‘lookups’ in the formula. The more “transformations” there are in the formulae, the more work the engine needs to do. We can remove one of these by changing the target module dimensionality. Copy the reporting module from above. Remove the formulae for Month 1 and Month 2. Replace Reporting Metrics with LIS: Multi>Variance Reporting as the dimension (applies to). Add the following formulae for Month 1 and Month 2 respectively. Month 1 = 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] Month 2 = 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 2'] Note, only one lookup is needed in the formula. Filters Another use case that line item subsets can be used for is filtering. And this functionality has nothing to do with staging data or mapping modules. It is possible to filter line items and these can also be filtered based on other dimensions too. Use Case example: Based on user-entered settings, for the reporting module (REP03) we want to show different line items for each year and version. We already have set up the Years to Versions filter module We now want to set up the user-driven parameters. To ensure that the users’ settings do not affect each other, we need to use the system generated Users’ list. Create a line item subset based on REP03 Select all line items Create a new module with the following dimensions: LIS: REP03 Filters Users Versions Add a single line item (Show?) formatted as a Boolean Enter values as you wish Note that Employee expenses and Other Costs are not available to check. This is because, in REP03, they are a simple aggregation and are shown as Parents of the other line items.  So, how do we resolve this? You can “trick” the model by turning these setting off. The subtotals are now available to check in the filter module. It is worth noting, be careful when doing this. If you are using the line item subsets as a dimension in a data entry module, the totals will not calculate correctly. See Final Thoughts for more details. To set up the filter In REP03, set the following filters The module will now filter line items and years when the version page selector is changed. Note the subtotals work correctly in this module because it is not data entry. Dynamic Cell Access Line item subsets can be used in conjunction with Dynamic Cell Access to provide very fine-grained control over data; again, without any mapping modules or COLLECT() statements Use Case Example: In the following module The following rules apply: Bonus % is set by the central team so it needs to be read only. All metrics for Exec are not allowed to be edited. Car Allowances are not applicable for Production. Phone Allowances are not applicable for Production, Finance or HR, and the allowances for Sales should be read only. To set up the access: Create a line item subset based on EMP01 Select all line items Create an Access Driver module with the following dimensions: LIS: EMP01 DCA G2 Country E1 Departments Add two Boolean formatted line items Read? Write? Enter the values as below  Now in EMP01 assign the Read Access and Write Access drivers to the module The module now looks like this: Line Items Subsets with Line Item Subsets I mentioned at the outset that you can lose formatting when using a line item subset. However, in some cases, it is possible to keep formatting along with calculations Use Case Example: Using the values from REP03, we want to classify Sales and Costs and then calculate a cost % to Sales. Yes, we could do this in the module itself as a separate line item, but we also want to be able to reclassify the source line items from a dashboard using mappings rather than change the blueprint formula. We also want to maintain formatting.  For this example, I have just changed the styles to illustrate the point Create a line item subset based on REP03. Create a staging module with the following dimensions: LIS: REP03 Cost% G2 Country Time (Years) Add a line item call Data and enter COLLECT() as the formula and set the Summary method to None. Create a second line item subset based on REP10 (the target module). Create a mapping module dimensioned by the LIS: REP03 Cost% Create a line item formatted as LIS: REP10 Map the lines accordingly In the target module set following formula for both Sales and Costs line items (Yes, it is the same formula for both!) 'REP09 LISS vs LISS - Staging'.Data[SUM: 'SYS20 Cost% Mapping'.Mapping]  Note the formatting is preserved. Version Formula Finally, I want to mention a piece of functionality that is not well known but very powerful; Version Formula. Utilizing line item subsets in conjunction with versions, Version Formula extends the ‘Formula scope” functionality. It is possible to control formulae using Formula Scope, but there are limited options. Use Case example: Let’s assume that we have actuals data in one module, the budget data in another and we want to enable the forecast to be writeable. The current version (in the versions setting) is set to Forecast For this example, there is only one line item in the target module, but this functionality allows the flexibility to set different rules per version for each line item Create a line item subset based on the above and select the line item(s). Now in the blueprint view of the target module click Edit>Add Version Formula. Now choose the Version to which the formula applies. You will now see a different formula bar at the top of the blueprint view. Enter the following formula: 'DATA01 P&L Actuals & Budget'.Revenue Repeat the above for Budget with the following formula: 'REV03 Margin Calculation'.Revenue  Note that now at the top, you can see that there is a Version Formula set. Final Thoughts We mentioned the aggregation behavior and the ‘Is Summary’ setting earlier. Let me show you how this and the construction of the formulas affect the behavior of the line item subset We will use the following module as an example. This module is only used to set up the line item subset, so no dimensions are needed. Note that the subtotal formulae are simple aggregations. This means the subtotal lines: Calculate correctly when used as a dimension in a module. Are not available for data entry. The following module is dimensioned by the line item subset to highlight 1. and 2. above. If we decide we don’t want the Employee costs in the line item subset, two things happen: The indentation changes for the detailed cost lines because they are now not part of a parent hierarchy on display. The Costs subtotal doesn’t calculate. This is because the Costs subtotal needs the intermediate subtotals to exist within the line item subset. To mitigate the latter point there are two remedies. Include the subtotals and hide them – The lines are still calculating and taking space. If possible, adjust the formula structure. Remove the subtotals formula. Add in the Costs formula as to use the detailed items; no intermediate totals. Re-add the subtotal formulas. Note the 'Parent' and 'Is Summary' settings, the Costs subtotal now calculates correctly. If we change the formulae to be something other than simple addition, you will see that calculation is fine in the source module, but not in the line item subset module. Why is this? Remember the 'Is Summary' setting we changed in the Filters section when we adjusted the formula the 'Is Summary' is now unchecked This means that the line item subset doesn’t treat the line as a calculation, hence the data entry 0 shown instead. If your costs need to be positive (as in this example), it is possible to calculate correctly using a ratio formula. This works for normal line items/lists as well as line item subsets. See Changing the sign for Aggregation for more details
View full article
Learn how using ratio can solve the problem of showing costs as positive numbers whilst subtracting them from totals
View full article