Transparency in the design and building of models is key to creating a model that is easy to understand and maintain. Adhering to these practices enables a new Workspace Administrator to quickly gain an understanding of the flow of data from calculations through to the user interface.
Alongside our recommended naming conventions, these guidelines provide a foundation to underpin your model building.
At the highest level, model building requires you to consider:
- lists and hierarchy planning
- module structure
- the use of the Model Map
- how you will maintain your model
Lists are fundamental to a model and planning your core dimensions can simplify the build and make the model more easily understood.
Consideration should be given to the structure of:
When developing lists, the first step to is to consider how the elements of your business fit together. For example, what are the relationships between employees, cost centers, business units, sub-regions and regions? This information often suggests the need for a composite hierarchy that clearly represents the organization’s structure.
The value of a hierarchical relationship lies in the ability to drive assumptions down, from the highest to the lowest planning level. This is supported by a multi-level hierarchy, so you can roll up data or drill into subtotals at any level.
Use the PARENT and LOOKUP formulas to change assumptions at different planning levels.
Let’s investigate the values contributing to the total for APAC in this Strategic Regional Plan:
We can use LOOKUP function to drill down into the model to see the breakdown of values that contribute to the APAC Strategic Target by referencing the G1 Region line item in the ATT01 G3 Business Unit module:
Using the PARENT function, you can identify the child items of the G1 Region:
And here, the information is displayed in Blueprint.
Using Anaplan to generate a hierarchy
Anaplan can generate hierarchies quite easily using a module with a numbered list as a dimension. To do this, follow the process described here.
The data source should be a simple table with a row for each leaf item. It can also include the table row ID or a unique code for each leaf item to use as a code for the initial numbered list.
The child items within a hierarchy may require different calculations. In this case, it may make more sense to build your hierarchy out of line items within a module (e.g. P&L Statement) and make use of a Line Item Subset. The line items that represent the child items of the hierarchy can each have separate calculations applied to them or they can require manual data entry.
To create the hierarchy, the line items that form the parent items must be the simple sum of the child items (causing Is Summary to be automatically selected in Blueprint).
Here, you can see the child items contributing to Total Cash from Operations:
And here, in Blueprint, Total Cash from Operations is marked Is Summary:
The COLLECT() function will then collect the original values from the line item subset to populate further modules:
Numbered Lists, in contrast to simple lists, contain a unique system-generated ID number and an optional display name. The unique ID, which is never reused, even where items are deleted or reinserted, enables multiple items in the list to have the same display name.
A numbered list is more flexible than a simple list as it can make use of a wider range of Actions – this gives end users the ability to maintain hierarchies themselves. For example, they can add items via a button or copy branches.
Be aware that when importing, Anaplan can use the unique ID or the list item Code to map the data. Having a code is often essential. Where a numbered list doesn't have a code, the only way to import into a module that has a Numbered List as a dimension is for the data source to use the Anaplan ID number for mapping — this is often impossible to do!
In the Code column, you can see the unique ID assigned to each list item and how the code changes for the parent item in the list.
There are a number of issues to think about when planning how modules will be structured and how they will interact. The structure of lists and modules and the use of subsets and subsidiary views should be carefully considered before building a module.
Use lists wherever possible to reduce the number of line items in a model. If similar calculations are repeated in a module for different items, there may be a simpler way to build the calculations using lists instead.
Create a list called Pick Lists which holds all pick lists as subsets of the master list. This helps keep the number of lists down and aids navigation.
Attributes of a list can be held in the list Properties or in a two-dimensional module. Both options occupy the same amount of space.
In most cases it is easier to hold list attributes in a module. The exception to this is where you need to export an item as a label in a module or you need a display name for a numbered list. In these two cases, it is essential you use the list’s Properties.
Suggested List Attributes
The attributes suggested here are very useful when model building, regardless of where attributes are held. Begin by creating these in a module rather than the list Properties – it will make formulae writing easier.
- List Item
Create an attribute, called Item, formatted with the list itself using the ITEM() formula. This attribute can be referenced directly in other formulae.
Attributes that represent parent items should bear the name of the parent list to make writing LOOKUP and SUM formulae easier. Formulae will be easier to understand if you avoid nesting the PARENT() function — create separate attributes for each of the parent levels and refer them to each other. In this example, PARENT() shows that the parent of G2 Sub Region is G1 Region.
You can also see this in Blueprint:
List attributes in a module
When importing attributes into an attribute module, two import actions are required:
- one to create the items and
- one to import the attributes into the module
The benefits of using an attributes module are:
- Formula Writing
Writing formulas against line items, rather than list properties, is easier because you’re able to use the point and click approach. This is useful when entering long conditional formulae. If you make any syntax errors in a property formula within a list, the formula will be erased and you will have to start again!
Attribute modules can be used as a source for filtering other modules in the model — this is not possible for a list property.
- Drill Down
When using Drill Down, you can't see the information stored in the list properties. However, if you refer to data stored in modules, you can drill down and see actual values. This is useful from both a model building and an end user perspective.
When published to a dashboard, attribute modules are more flexible than an equivalent list. When using an attribute module, all the properties and subsets of a list are published and you can Show or Hide relevant module line items. By doing this, users are able to maintain attributes themselves. Conversely, if you publish a list to a dashboard, you will see the properties and subsets but there is no way to Hide columns or change the individual column width of a property.
- Model Map
Attribute modules display on the Model Map. Lists do not. Consequently, attribute modules help you better understand the source and destination of data flows.
There are two situations where using list Properties is essential.
- Numbered List Display Names
For a numbered list to have a display name, create a Property with either Text or List format. Display name properties can either be calculated within the list property itself or fed from a module.
- Data Exports
Some properties need to be exported alongside, or in place of, a list item. This is easily achieved by using a list property but can’t be achieved using a module. The ERP Employee ID, in the image above, is a property of the list GCS Employees #.
When creating a new module, check whether there is an existing module, with the same dimensionality, that could hold the line items instead. Consider:
- where do similar modules fall within the data flow?
- how will the model map be affected by creating new line items in this module?
You can create modules to hold assumptions for a model. These provide a single source of information for model builders and make model maintenance easier.
- Create a module to control all filters. This module can have different line items with different dimensionality for each filter. This enables you to control filters for all dashboards in one location.
- Create a Time Assumptions module to control the current period and all associated assumptions. Current Period in Settings > Time > Model Calendar can only be accessed by a Workspace Administrator. If monthly maintenance is to be controlled by an end user, create a dashboard with instructions for the user.
- A Time Calculations module is also useful to consolidate all time-dependent assumptions. This module can then drive calculations throughout the rest of the model.
- Finally, create a module (with no dimensionality) to hold global assumptions for the model.
Structure of a module
Create a line item called Documentation, with a short explanation of the purpose of the module and how it fits into the overall business process (Format: No Data; Style: Heading 1). Enter the description in the formula bar using quote marks (") to prevent non-model builders from accidentally deleting this information.
Indicate which line items are intended to be seen in the user interface, and which are intended for internal calculations. This can be achieved by creating line items to represent headings (Format: No Data; Style: Heading 1) — this takes the individual cell count to zero. Often, these headings are called User Interface and Calculations.
Don’t be afraid to take your module organization to the next level: if you require a heading called HR Payroll Data, then create Calculations: HR Payroll Data.
Direct all formulae to the original source and avoid duplicating data in multiple locations.
Hard-coded Values in Formulae
Where possible, hard-coded values used within a SELECT statement should be replaced with a LOOKUP.
Before – with a SELECT statement
After — with a LOOKUP statement
The LOOKUP statement is accessing the values in the Time Assumptions module.
If subsets are to be maintained within a model, consider creating modules to manage them in the same way we use modules to hold list attributes. There are many benefits to this approach.
Subsets are easily documented with additional explanatory line items.
- Formulae & Filters
Boolean-formatted line items, used to control subsets, can be referred to in formulae and also used for filtering. Subsets cannot be used in this way.
- Model Management
It is easier to debug and troubleshoot how a subset is maintained because all the logic is in a common format.
- End User Control
Subsets can be managed in one location and, if required, you can allow end users to manage them.
To set up subsets in a module
A subsidiary view is created when a line item uses different dimensionality to the module default (the Applies To, Time, or Versions column in the module Blueprint). The dimensionality of a subsidiary view can be varied by any list in the model, Time, or Version.
Subsidiary views can be used to reduce the number of cells required for a line item and make the model more efficient.
When to consider using a subsidiary view:
- If a line item has the same result across every item of a single dimension, it is likely that this dimension could be removed and any dependencies or references to this line item will be unaffected.
- If end users don’t need to see a line item.
If a line item needs to be viewed on a dashboard by an end user, rather than creating a subsidiary view, it should be created in a separate module.
- Subsidiary views cannot be edited if they are published to a dashboard as selected line items.
- The space occupied by subsidiary views on a dashboard can vary and cause issues when trying to align dashboard elements.
- When published to a dashboard, it’s not possible to hide the module name of a subsidiary view without hiding the line item itself.
- Multiple subsidiary views with the same dimensionality cannot be published as one subsidiary view.
- Filters can only be applied to a subsidiary view using its own data. The filter cannot be based on another line item.
There are two cases where publishing modules that have subsidiary views to a dashboard is acceptable. In both cases, the module must be displayed in its default dimensionality.
The difference between the dimensionality of the subsidiary view and the default is acceptable when:
- The subsidiary view has a dimension which is the parent of a list used in the default dimensionality. In this case if the list in question is pivoted to rows or columns, the line item using the subsidiary view can be edited in the list parent item instead of the child item.
- The subsidiary view is entirely contained within the default dimensionality. This happens when a list subset is used as a dimension of a subsidiary view and the full list is used in the default dimensionality. In this case, when the page selector being viewed exists in the list subset, the data in the cells will be viewable by end users. When the page being viewed is not part of the list subset, the cells will be viewable and blank.
This image shows a dashboard with two grids, exported from different page selections of the same module. Because Japan is not included in the list subset, the data cells are blank. US West is included in the list subset and consequently the values are displayed.
Here, we can see the change in dimensionality in the Applies To field in Blueprint.
When you start connecting modules, think about how that translates to the Model Map. You should always consider the implications for the Model Map when creating a new module or formula and try to construct a one-way data flow.
For example, when you create a new line item, ensure that it follows the data flow you have created in the model so far. Try to avoid situations where a line item refers to a module which is further along the data flow as this will cause circular connections in the Model Map. Sometimes this can’t be avoided but it should only be used if absolutely necessary.
It can be beneficial to illustrate model-to-model imports. To do this start by creating a Functional Area for data sources. Then, create modules to represent the external data sources. They don’t need to have any dimensionality — just a single line item called Data. In the module that holds the import data, create a line item with no dimensionality, and reference the data source module line item.
To visualize what the Model Map will look like if a connection is removed, change the target line item’s format to No Data (the formula will be left intact but the link between the modules is removed). You can quickly see what happens to the model map if some formulae do not exist without having to actually delete the formula. While the format remains as No Data, the source line item(s) can be deleted and, consequently, the target line item formula will be deleted as well.
A line item’s format cannot be No Data if it is referenced by another line item!
Dashboards can be used to provide a simple interface for model administrators to maintain their models. Use buttons and text boxes to provide access to Processes and instructions to guide administrators through model maintenance.
If you apply these principles, maintaining a model can be as simple as a few button clicks (depending on the complexity of the model). A clearly-documented update process will lead to faster adoption, and the ability for anyone to step in and maintain or update the model when needed.
The processes and instructions on these dashboards could include:
- Processes and Imports to run integration actions.
- Maintaining model lists (along with Processes to do this).
- How to perform a model rollover.
- Reminders to add new Versions or update Version Switchover.
- Reminders to copy and archive the model.