Table of Contents:
Anaplan has had challenges getting our finance, human resources, and sales teams on the same page of what our headcount is and what our headcount is forecasted to be in order to hit our revenue targets. We struggled to understand where requisitions were in the cycle and how many were currently open. As a result, we built a connected Anaplan dashboard to help transform conversations between teams to focus on understanding the different forecasts and strategic planning across those forecasts.
Inconsistency in hitting hiring targets outlined in operating plans
As a hypergrowth software-as-a-service (SaaS) company, the ability to hire quality candidates and stay on track with hiring targets is critical to our success. We had been consistently falling behind our sales force’s net headcount due to not hitting hiring targets, as well as having wrong assumptions on our ability to hire, generating non-achievable net headcount numbers.
Misalignment of roles and responsibilities between the stakeholder group and those closest to the requisition due to lack of transparency and interaction in the process
Non-subject-matter experts were responsible for entering in information regarding open headcount requisitions. This resulted in recruiters having to exceed finance’s budgeted compensation forecasts to hire quality candidates, roles taking longer to staff than forecasted, and perhaps most importantly, a lack of transparency for key stakeholders (i.e. hiring managers had little visibility into how close they were to getting a new hire, the recruiter did not know when they would be getting a batch of new jobs to recruit against, and finance could not see where recruiting was in terms of staffing active requisitions).
Time-consuming and error-prone process in closing out requisition when converting a to-be-hired to an employee
Our finance team was required to do hours of time-consuming manual reconciliation to make sure that each to-be-hired requisition was “deactivated” upon an employee filling that role, rather than focus on value-add activities and analysis.
Inconsistent data across enterprise systems
Between Anaplan, our human capital management (Workday), application tracking system (Greenhouse), and customer relationship management (Salesforce), there were myriad discrepancies for attributes tagged to to-be-hired requisitions. Which office would someone sit in? Which territory would a new sales rep cover? These are questions that oftentimes could not garner a single answer when different teams and systems were asked. We needed a single source of truth, less prone to human error.
The proposed solution to address the above pain points was to bring the entire headcount requisition planning process into Anaplan. This would allow finance, sales, hiring managers, and recruiting to share data freely and provide full transparency into the hiring process while allowing subject-matter experts to provide inputs where needed.
We have broken our workflow down into six different stages:
Anaplan headcount planning systems workflow.
Stage 1: Finance works with their partners in the business to plan and create new to-be-hired people requisitions in their planning, budgeting & forecasting (PBF) model. Using a numbered list, a unique, immutable code is assigned to the requisition, which will follow it through the rest of the below steps (REQ #XXXXXX). A requisition is then created in the PBF model, and either manually "released" by the finance manager overseeing that requisition, or if it is a certain number of days to the forecasted start date, the requisition is automatically "released" into the process.
Stage 1.5: If the requisition is for sales, it is then imported into our sales planning management model (SPM) where our sales ops team tags the relevant assignment data (geography, territory, etc.) based on their planning and forecasting needs.
Stage 2: After that step (and after stage 1, if the requisition is not for a sales rep) the requisition is then imported into the new hiring manager model (HIRE). At this stage, the hiring manager tagged to the requisition tags additional attributes to the requisition that they are best equipped to answer such as the job type, job level, and location of the requisition. Layering in the mappings imported from our Compensation model allows for a nuanced view of the total compensation that supports the req being developed . The hiring manager then clicks a Boolean line item indicating they sign off, and the requisition is "released" to the next stage.
Stage 3: Once the hiring manager signs off, the requisition is imported into our recruiting management model (RECRUIT). This model contains a dashboard for recruiters to go in and enter in the final set of data necessary for the requisition to be written into our applicant tracking system (ATS): information such as the legal entity the requisition will be hired into, a template job to use to make the requisition in our ATS, etc. A series of logic determines if a requisition is ready to be created in our ATS. If all mandatory fields are filled in, as well as finance/hiring manager/recruiting sign-off has been provided, the requisition becomes unfiltered on a saved view that an integration with our ATS picks up, which runs every hour.
Stage 4: Recruiting then goes through their normal hiring activities in our ATS, and eventually a candidate is hired in the requisition position.
Stage 5: We have another integration running from our ATS to our Headcount Management Software (HCM) so that when a candidate is hired, they immediately have an employee profile created in our HCM. One of the attributes is the unique requisition ID mentioned in step one. We have scheduled loads of employee data from our HCM into our Anaplan Data Hub model, as well as the planning, budgeting & forecasting model. When PBF detects an open requisition ID on an employee profile line, the formulas are built to "turn off" the requisition from the forecast, and "turn on" the new employee, creating a seamless, automatic cutover from a to-be-hired to an actual employee.
Anaplan headcount planning model structure and data flow. Anaplan headcount planning model structure and data flow.
Five separate models are involved in the facilitation of this process that all sit in separate workspaces:
Planning, budgeting, and forecasting (PBF)
Sales planning management (SPM)
Hiring manager model (HIRE)
Recruiting management (RECRUIT)
Why did we decide to use multiple models instead of one large model?
The first reason was the ease of user management. Since several of these models are core planning models, they contain sensitive financial and employee data. Breaking up the models mitigates the risk that someone accidentally was given inappropriate access by keeping finance partners in their own planning model (PBF), hiring managers in their own model (this is the largest user base in the process), and human resources and recruiting planning in their existing recruiting management model. That way, for example, a hiring manager has no risk of mistakenly being able to see our financial projects in the PBF model.
The second reason is that smaller use-case-specific models greatly reduce the recalculation time of the imports required given the integration and scheduling requirements (hourly loads from our HCM and ATS systems) . It also allows us to be more tactical with what data is imported into each model, as each model has its own unique data requirements. We have saved views set up for each part of the sign-off process, which only pick up requisitions that have been recently approved and need to be synced, which also helps with the load times between models.
Smaller models also allow for less complex and simpler transition plans in the event a model owner leaves. By breaking up use cases, the models they live in are easier to understand and can be transitioned individually versus needing to transition all 10 use cases together.
User Access Management
We take advantage of each user-provisioning functionality Anaplan provides. We use model roles to segment users by their job role in the model and pair that with selective access and Dynamic Cell Access (DCA):
Selective access: Some requisitions are so sensitive that even their existence is confidential. To deal with this, we use selective access to make sure that only the proper people can see that a requisition the finance team tags as “confidential” even exists.
Dynamic Cell Access: For everything else, we leverage DCA paired with formula drivers to make sure that each role can see only their relevant data. Only hiring managers can view data for requisitions they are tagged to, finance can only view data for requisitions in cost centers under their purview and recruiters can only view data for requisitions they are recruiting on. We prefer to use DCA where applicable, because formulas are easier to audit and tweak than trying to figure out which import actions are automating selective access.
Stakeholders & Responsibilities
Key Stakeholder Groups
This project spanned multiple departments and stakeholder groups. At a high level, the teams involved:
Hiring manager of new to-be-hired requisition
Anaplan on Anaplan partner
Sales operations partner
Description of the type of activities, if applicable:
Finance creates requisition and fills in basic date
Within their PBF model, the finance partners work with their business partners to create new headcount requisitions. They fill in basic information about the requisition in order to be able to generate a forecast (cost center, comp estimate, location, etc.).
When ready, finance clicks a Boolean line item on the requisition to “release” it into the broader process. It’s at this point that it becomes exposed to the next group of stakeholders. We’ve put in time gates here to auto-release requisitions that are a quarter out from their forecasted start date. This helps us hit our hiring targets, so requisitions do not “slip through the cracks” and we make sure that recruiting is given enough time to recruit against.
Sales ops team assigns territory
The sales ops team concurrently works with the requisition list (both unapproved and approved) to tag appropriate territories to each requisition based on their sales planning model’s outputs. This allows sales ops to directly manage the data they care about and know best about each requisition.
Hiring manager fills in data
Once finance approves the requisitions and sales ops fills in their data (if it’s a sales requisition), the requisition is then passed to the hiring manager model where the hiring manager of the requisition goes into their own dashboard and fills in information like job level, location, and job type. With this information, the model uses lookups to generate a refined compensation range for the requisition. This not only helps cut down on back-and-forth with recruiting but also helps finance refine their forecast by receiving more accurate compensation data.
Hiring manager approves requisition
After the hiring manager is done with their inputs and is happy with the compensation range generated (which is also validated against finance’s initial estimate), they approve the requisition by clicking their own Boolean. This reduces the number of emails and back-and-forth trying to get a requisition approved.
Recruiter fills in data
After the hiring manager approves, the requisition is then made available on the recruiter’s Anaplan dashboard. They fill in the remaining information needed to populate a new job in our applicant tracking system. This allows recruiting to automate the Greenhouse (ATS) job creation process by only having to enter in fields they need to create the job, and the integration does the rest!
Recruiter approves requisition
Once the recruiter is done with their inputs, they click their own Boolean line item and the requisition is then made available via a saved view to be written into our applicant tracking system.
Anaplan on Anaplan (AoA)
The AoA team, as our internal Center of Excellence, served as technical architect advisors, as well as project managers. In order to free up time for the business groups to do their own modeling, the AoA team worked cross-functionally to make sure that all groups were heard and hit their deliverable milestones.
Sales Ops Partner
Create New Requisition
Approve New Requisition
R (if applicable)
Assigned Territory to Requisition (if applicable)
Fill in Data Re: Requisition
R = Responsible, A = Accountable, C = Consulted, I = Informed
Data Sources & Integration
Account and Opportunity Data
Model data syncs across involved models
Besides Workday employee data, the above integrations are scheduled via HyperConnect to run every hour. Data created in each model is shared between the models via scheduled model-to-model imports using HyperConnect. Greenhouse to Workday is configured as an event-based webhook, so when a new candidate is hired the candidate’s information is immediately sent to Workday to create a new employee.
Checks and Balances
As information is updated in each model, part of the hourly sync cadence is to sync all requisition data between each model. This is done by running an import process in each model that imports data from the other models. These processes run sequentially before the integration to Greenhouse runs. This allows groups to layer in their own data checks at each part of the process and share those checks with each model. They are consolidated in the recruiting management model where our “master” checks reside. It looks for data completeness, as well as all other models signing off on their own checks. For example, if a hiring manager pushes through data with a salary of $100K and last second decides to change it to $200K after finance and recruiter signs off, the logic finance has built into their checks to flag out of budget compensation would be triggered ahead of the integration. This flag would pull the saved view out of the final staging view and the requisition would not be picked up.
Return on Investment
Increased Speed to Begin Recruiting
When a requisition was released by finance, it would take over a week (if not weeks) to be created. Now with this new process created, our average time from when finance releases a requisition to when recruiting begins recruiting against it is less than six days. In fact, once recruiting gets the information from finance and the hiring manager, the average time to begin recruiting is less than a day! The decreased time allows recruiters to do what they do best: get a quality candidate hired. With more time to have a job posted, candidates interviewed, and offer negotiations completed, they can focus on hiring quality candidates while still hitting the company-wide hiring targets.
Now that each stakeholder group has its own Anaplan model and dashboards, there is no ambiguity for whose court the ball is in (i.e. what part of the process is the requisition stuck in?). Hiring managers can quickly see where each of their hires is along the process, and recruiting can proactively reach out to stakeholders to guide them along in filling out their information.
Automated Conversion of To-Be-Hired to Employee
With a single source of truth for requisition ID numbers, and integrations automatically flowing data between systems, our finance team no longer has to spend hours reconciling which requisitions have been hired. In our planning models, a requisition is automatically “turned off” and converted to an FTE when a new hire fills an open requisition.
Refined Forecasts and More Accurate Data (Within Anaplan and Across Systems)
Prior to this process, a requisition could be released from finance, have its compensation profile drastically changed, or its sales territory altered, etc., without any indication or notification. Only after an offer approval came through to finance and sales ops did they find out that something was wrong, which led to last-minute scrambling and process hang-ups when they mattered most (trying to lock in a quality hire). Now that each group is responsible for and has visibility into the attributes they care about, those problems are brought to light and rectified early on, far before they become issues in crunch time.
It’s clear to see the benefits that moving Anaplan’s headcount planning process into the Anaplan platform has. Those benefits were made possible by the partnership and hard work of the finance, talent acquisition, human resources, and sales teams. If you want to learn more about how Anaplan does headcount planning, reach out to your account representative for more information and a demonstration.
If you’re familiar with Anaplan, you’ve probably heard the buzz about having a data hub and wondered why it’s considered a “best practice” within the Anaplan community. Wonder no more. Below, I will share four reasons why you should spend the time to build a data hub before Anaplan takes your company by storm.
1. Maintain consistent hierarchies
Hierarchies are a common list structure built by Anaplan and come in a variety of options depending on use case, e.g., product hierarchy, cost center hierarchy, and management hierarchy, just to name a few. These hierarchies should be consistent across the business whether you’re doing demand planning or financial planning. With a data hub, your organization has a higher likelihood of keeping hierarchies consistent over time since everyone is pulling the same structure from one source of truth: the data hub.
2. Data Optimization
As you expand the use of Anaplan across multiple departments, you may find that you only need a portion of a list, rather than the entire list. For instance, you may want the full list of employees for workforce planning purposes, but only a portion of the employees for incentive compensation calculations. With a data hub, you can distribute only the pertinent information. You can filter the list of employees to build the employee hierarchy in the incentive compensation model while having the full list of employees in the workforce planning model. Keep them both in sync using the data hub as your source of truth.
3. Separate duties by roles and responsibilities
An increasing number of customers have asked about roles and responsibilities with Anaplan as they expand internally. In Anaplan, we recommend each model have a separate owner. For example, an IT owner for the data hub, an operations owner for the demand planning model, and a finance owner for the financial planning model. The three owners each have roles and responsibilities within their CoE for the development and maintenance in their individual models.
4. Accelerate future builds
One of the main reasons many companies choose Anaplan is for the platform’s flexibility. Its use can easily and quickly expand across an entire organization. Development rarely stops after the first implementation. Model builders are enabled and excited to continue to bring Anaplan into other areas of the business. If you start by building the data hub as your source of truth for data and metadata, you can accelerate the development of future models since you already have defined the foundation of the model, the lists, and dimensions.
As you begin to implement, build, and roll out Anaplan, starting with a data hub is a key consideration. In addition to this, there are many other fundamental Anaplan best practices to consider when rolling out a new technology and driving internal adoption.
*Content reviewed for accuracy June 2020
Making sure that production data lists are correctly marked within a model is a key step to setting up and using Application Lifecycle Management (ALM) . This guide will provide a solution to how someone can make revisions to their model to allow for the tagging of a list as a production data list. Please note: this solution doesn’t work if there are hard-coded references on non-composite summary items. For more information on working with production lists and ragged hierarchies, please visit Production lists and ragged hierarchies logic.
The issue arises as a model administrator needs to tag a production data list, but there are hard-coded references in the model that won’t allow the person to do so. When this occurs and the model administrator tries to tag it as a production list, they will get a warning similar to this:
See Formula Protection for more details.
To fix this issue, all direct formula references to production data lists need to be changed to be indirect references to lists using either LOOKUPs or Boolean formatted conditional logic. Below, you will find a step-by-step guide to replacing these formulas.
Identify formulas with hard-coded references
There is now an easy way to identify all of the formulas which are hard-coded to production data lists.
Check the 'Referenced in Formula' column in the General Lists section.
This will show the line items where the list is used. Check the respective formula for hard-coded references. If there are no hard-coded references, then it is OK to check the list as a production data list. This is the recommended approach, as just setting the lists without prior checking may lead to a rollback error being generated, which could be time-consuming for large models (as well as frustrating).
It is possible to just export the General Lists grid to help where there are multiple references for the same list and then use formulas and filters to identify all offenders in the same effort. This option will save significant amounts of time if there are many line items that would need to be changed.
You are looking for direct references on the list members:
[SELECT: List Name.list member]
ITEM(List Name) =List Name.List member
The following constructs are valid, but not recommended, as any changes to the names or codes could change the result of calculations:
IF CODE(ITEM(List Name))=
IF NAME(ITEM(List Name))=
After following those steps, you should have a list of all of the line items that need to be changed in the model in order for production data list to be open to being checked. Please note: There may still be list properties that have hard-coded references to items. You will need to take note of these as well, but as per D.I.S.C.O., (Best practice for Module design) we recommend that List Properties are replaced with Line Items in System Modules.
Replacing model formulas:
The next step is to replace these formulas within the model. For this, there are two recommended options.
The first option (Option 1 below) is to replace your SELECT statements with a LOOKUP formula that is referencing a list drop-down. Use this option when there are 1:1 mappings between list items and your formula logic. For example, if you were building out a P&L variance report and needed to select from a specific revenue account, you might use this option.
The second option (Option 2 below) for replacing these formulas is to build a logic module that allows you to use Booleans to select list items and reference these Boolean fields in your formulas. Use this option when there is more complex modeling logic than a 1:1 mapping. For example, you might use this option if you are building a variance report by region and you have different logic for all items under Region 1 (ex: budget – actual) than the items under Region 2 (ex: budget – forecast).
(Option 1) Add List Selections module to be used in LOOKUPs for 1:1 mappings:
From here you should make a module called List Selections, with no lists applied to it and a line item for each list item reference that you previously used in the formulas that will be changed. Each of these line items will be formatted as the list that you are selecting to be production data.
Afterward, you should have a module that looks similar to this: An easy and effective way to stay organized is to partition and group your line items of similar list formats into the same sections with a section header line item formatted as No Data and a style of "Heading 1."
After the line items have been created, the model administrator should use the list drop-downs to select the appropriate items which are being referenced. As new line items are created in a standard mode model, the model administrator will need to open the deployed model downstream to reselect or copy and paste the list formatted values in this module since this is considered production data.
Remove hard-coding and replace with LOOKUPs:
Once you have created the List Selections module with all of the correct line items, you will begin replacing old formulas, which you’ve identified in Excel, with new references.
For formulas where there is a SELECT statement, you will replace the entire SELECT section of the formula with a LOOKUP to the correct line item in the list selections.
Old Formula = Full PL.Amount[SELECT: Accounts.Product Sales] New Formula = Full PL.Amount[LOOKUP: List Selections.Select Product Sales]
For formulas where there is an IF ITEM (List Name) = List Name Item, you will replace the second section of the formula after the ‘=’ to directly reference the correct line item in the list selections.
Old Formula = If ITEM(Accounts) = Accounts.Product Sales THEN Full PL.Amount ELSE 0 New Formula = IF ITEM(Accounts) = List Selections.Select Product Sales THEN Full PL.Amount ELSE 0
(Option 2) Modeling for complex logic and many to many relationship:
In the event that you are building more complex modeling logic in your model, you should start by building Boolean references that you can use in your formulas. To accomplish this, you will create a new module with Boolean line items for each logic type that you need. Sticking with the same example as above, if you need to build a variance report where you have different logic depending on the region, start by creating a module by region that has different line items for each different logic that you need similar to the view below:
Once you have the Boolean module set up, you can then change your hard-coded formulas to reference these Boolean formatted line items to write your logic. The formula may look similar to this:
IF Region Logic.Logic 1 THEN logic1 ELSE IF Region Logic.Logic 2 THEN logic2 ELSE IF Region Logic.Logic 3 THEN logic3 ELSE 0
Here is a screenshot of what the end result may look like:
This method can be used across many different use cases and will provide a more efficient way of writing complex formulas while avoiding hard-coding for production data lists.
Selecting production data list:
After all of the hard-coded formulas have been changed in the model, you can navigate back to the Settings tab, and open General Lists. In the Production Data column, check the box for the list that you want to set as a production data list.
Repeat for each list in the model that needs to be a production data list:
For each list in the model that you need to make a production data list, you can repeat the steps throughout this process to successfully remove all hard-coded list references.
I recently posted a Python library for version 1.3 of our API. With the GA announcement of API 2.0, I'm sharing a new library that works with these endpoints. Like the previous library, this supports certificate authentication as well. Both the public certificate and private key must be in PEM format. Additionally, this library supports the use of Java keystore.
Edit: A recent update to the M2Crypto library caused it to become incompatible on certain systems. As a result, I have migrated to the Cryptography package to handle certificate authentication. Many thanks to @christophe_keom for your assistance with the migration to Cryptography!
Note: While all of these scripts have been tested and found to be fully functional, due to the vast amount of potential use cases, Anaplan does not explicitly support custom scripts built by our customers. This article is for information only and does not suggest any future product direction. This library is a work in progress and will be updated with new features once they have been tested.
The attached Python library serves as a wrapper for interacting with the Anaplan API. This article will explain how you can use the library to automate many of the requests that are available in our Apiary, which can be found at https://anaplanbulkapi20.docs.apiary.io/#.
This article assumes you have the requests and M2Crypto modules installed as well as the Python 3.7. Please make sure you are installing these modules with Python 3, and not for an older version of Python. For more information on these modules, please see their respective websites:
Python (If you are using a Python version older or newer than 3.7 we cannot guarantee the validity of the article)
Note: Please read the comments at the top of every script before use, as they more thoroughly detail the assumptions that each script makes.
Gathering the Necessary Information
In order to use this library, the following information is required:
Anaplan model ID
Anaplan workspace ID
Anaplan action ID
CA certificate key-pair (private key and public certificate), or username and password
There are two ways to obtain the model and workspace IDs:
While the model is open, go Help>About:
Select the workspace and model IDs from the URL:
Every API request is required to supply valid authentication. There are two (2) ways to authenticate:
For full details about CA certificates, please refer to our Anapedia article.
Basic authentication uses your Anaplan username and password.
To create a connection with this library, define the authentication type and details, and the Anaplan workspace and model IDs:
conn = AnaplanConnection(anaplan.generate_authorization("Certificate","<path to private key>", "<path to public certificate>"), "<workspace ID>", "<model ID>")
conn = AnaplanConnection(anaplan.generate_authorization("Basic","<Anaplan username>", "<Anaplan password>"), "<workspace ID>", "<model ID>")
from anaplan_auth import get_keystore_pair
key_pair=get_keystore_pair('/Users/jessewilson/Documents/Certificates/my_keystore.jks', '<passphrase>', '<key alias>', '<key passphrase>')
#Instantiate AnaplanConnection without workspace or model IDs
conn = AnaplanConnection(anaplan.generate_authorization("Certificate", privKey, pubCert), "", "")
Note: In the above code, you must import the get_keystore_pair method from the anaplan_auth module in order to pull the private key and public certificate details from the keystore.
Getting Anaplan Resource Information
You can use this library to get the necessary file or action IDs. This library builds a Python key-value dictionary, which you can search to obtain the desired information:
list_of_files = anaplan.get_list(conn, "files")
files_dict = anaplan_resource_dictionary.build_id_dict(list_of_files)
This code will build a dictionary, with the file name as the key. The following code will return the ID of the file:
users_file_id = anaplan_resource_dictionary.get_id(files_dict, "file name")
To build a dictionary of other resources, replace "files" with the desired resource: actions, exports, imports, processes. You can use this functionality to easily refer to objects (workspace, model, action, file) by name, rather than ID.
#Fetch the name of the process to run
process=input("Enter name of process to run: ")
start = datetime.utcnow()
with open('/Users/jessewilson/Desktop/Test results.txt', 'w+') as file:
file.write(anaplan.execute_action(conn, str(ard.get_id(ard.build_id_dict(anaplan.get_list(conn, "processes"), "processes"), process)), 1))
end = datetime.utcnow()
The code above prompts for a process name, queries the Anaplan model for a list of processes, builds a key-value dictionary based on the resource name, then searches that dictionary for the user-provided name, and executes the action, and writes the results to a local file.
You can upload a file of any size and define a chunk size up to 50mb. The library loops through the file or memory buffer, reading chunks of the specified size and uploads to the Anaplan model. Flat file:
upload = anaplan.file_upload(conn, "<file ID>", <chunkSize (1-50)>, "<path to file>")
with open('/Users/jessewilson/Documents/countries.csv', "rt") as f:
print(anaplan.stream_upload(conn, "113000000000", buf))
print(anaplan.stream_upload(conn, "113000000000", "", complete=True))
The above code reads a flat file and saves the data to a buffer (this can be replaced with any data source, it does not necessarily need to read from a file). This data is then passed to the "streaming" upload method. This method does not accept the chunk size input. Instead, it simply ensures that the data in the buffer is less than 50mb before uploading. You are responsible for ensuring that the data you've extracted is appropriately split. Once you've finished uploading the data, you must make one final call to mark the file as complete and ready for use by Anaplan actions.
You can run any Anaplan action with this script and define a number of times to retry the request if there's a problem. In order to execute an Anaplan action, the ID is required. To execute, all that is required is the following:
run_job = execute_action(conn, "<action ID>", "<retryCount>")
This will run the desired action, loop until complete, then print the results to the screen. If failure dump(s) exits, this will also be returned.
Process action 112000000082 completed. Failure: True
Process action 112000000079 completed. Failure: True
Error dump for 112000000082
"E","Test User 2","All employees","","101.1a","1.0","2","Error parsing key for this row; no values"
"W","Jesse Wilson","All employees","a004100000HnINpAAN","","0.0","3","Invalid parent"
"W","Alec","All employees","a004100000HnINzAAN","","0.0","4","Invalid parent"
"E","Alec 2","All employees","","","0.0","5","Error parsing key for this row; no values"
"W","Test 2","All employees","a004100000HnIO9AAN","","0.0","6","Invalid parent"
"E","Jesse Wilson - To Delete","All employees","","","0.0","7","Error parsing key for this row; no values"
"W","#1725","All employees","69001","","0.0","8","Invalid parent"
"W","#2156","All employees","21001","","0.0","439","Invalid parent"
"E","All employees","","","","","440","Error parsing key for this row; no values"
Error dump for 112000000079
"Worker Report","Code","Value 1","_Line_","_Error_1_"
"Jesse Wilson","a004100000HnINpAAN","0","434","Item not located in Worker Report list: Jesse Wilson"
"Alec","a004100000HnINzAAN","0","435","Item not located in Worker Report list: Alec"
"Test 2","a004100000HnIO9AAN","0","436","Item not located in Worker Report list: Test 2
Downloading a File
If the above code is used to execute an export action, the fill will not be downloaded automatically. To get this file, use the following:
download = get_file(conn, "<file ID>", "<path to local file>")
This will save the file to the desired location on the local machine (or mounted network share folder) and alert you once the download is complete, or warn you if there is an error.
Get Available Workspaces and Models
API 2.0 introduced a new means of fetching the workspaces and models available to a given user. You can use this library to build a key-value dictionary (as above) for these resources.
#Instantiate AnaplanConnection without workspace or model IDs
conn = AnaplanConnection(anaplan.generate_authorization("Certificate", privKey, pubCert), "", "")
#Setting session variables
uid = anaplan.get_user_id(conn)
#Fetch models and workspaces the account may access
workspaces = ard.build_id_dict(anaplan.get_workspaces(conn, uid), "workspaces")
models = ard.build_id_dict(anaplan.get_models(conn, uid), "models")
#Select workspace and model to use
workspace_name=input("Enter workspace name to use (Enter ? to list available workspaces): ")
if workspace_name == '?':
for key in workspaces:
model_name=input("Enter model name to use (Enter ? to list available models): ")
if model_name == '?':
for key in models:
#Extract workspace and model IDs from dictionaries
workspace_id = ard.get_id(workspaces, workspace_name)
model_id = ard.get_id(models, model_name)
#Updating AnaplanConnection object
The above code will create an AnaplanConnection instance with only the user authentication defined. It queries the API to return the ID of the user in question, then queries for the available workspaces and models and builds a dictionary with these results. You can then enter the name of the workspace and model you wish to use (or print to screen all available), then finally update the AnaplanConnection instance to be used in all future requests.
It is important to understand what Application Lifecycle Management (ALM) enables clients to do within Anaplan.
In short, ALM enables clients to effectively manage the development, testing, deployment, and ongoing maintenance of applications in Anaplan.
With ALM, it is possible to introduce changes without disrupting business operations by securely and efficiently managing and updating your applications with governance across different environments and quickly and safely deploying changes in your planning cycles as you test and release development changes into production.
Common questions to ask before deploying ALM: How are you going to develop your models? Who is going to develop the models? Do you need to segregate responsibilities for development, test, and production models?
The overall ALM process can be broadly categorized into five stages:
Design an application that meets your business requirements. You will create user stories, schema diagrams, modules, data flows, wireframes, and prototypes.
Build stage, you create the lists and modules that make up the application. At this stage, use sanitized data; don't be concerned about loading production data into your application. Keep the development model small in a separate development workspace.
Test the application for performance and user acceptance. To isolate testing from production, utilize a separate test workspace containing test models, and use mock data or a subset of sanitized production data.
Deployment introduces the application to end users with full production data. Generally, your production application will be separate from your development and test applications. Importing production data from an external system or data hub might be part of your deployment process.
Post-deployment, as you build out an application to address further requirements, the development lifecycle can be repeated as often as necessary. Post-deployment development might include:
Fixes to resolve issues, either discovered in production or deferred in the build or test stage.
Additional functionality provided by new dashboards, modules, lists, or formulas.
New models to support additional business requirements.
We know that change is inevitable. Here are some tips to follow before and during deployment:
Create a structure for change.
Establish central responsibilities for the process.
Establish functional representatives in the business.
Designate a central solutions architect.
Establish a process for changing / creating a new model.
Consider segregation of duties for development and production models/workspaces.
Create a change control process.
Establish a process for collecting change requests.
Clarify requirements with end users.
Calculate development estimates (aka level of effort).
Define a triage process to prioritize developments.
Refer to the Business Owner for approval.
Agree on a development or sprint plan.
Define a communication plan to the end users.
If a data hub (see above) is used, consider the following:
Set Imported hierarchies as "Production lists".
Set up imports from live data hub into development models.
Use saved views and filters to contain the scope of development, test, and production models.
Always use development models as the source for both test and production models.
Once deployed mode is enabled for production models, do not take the model out of the deployed mode.
How do I find out more?
Complete the on-demand training on ALM.
Review technical documentation in Anapedia.
Learn more about the power of ALM: Understanding model synchronization in Anaplan ALM.
How does the UX effect ALM?
We currently have features to enable lifecycle management by building pages based on a development model, and then migrating to a production model in the same tenant. The full guide on how to perform lifecycle management with current capabilities in the UX can be found here . A more unified ALM sync experience for UX is also on the product roadmap, so stay tuned!
Remember to stay up to date on releases and changes to the platform in our platform release blog.
Imagine This Scenario:
You are in the middle of making changes in your development model and have been doing so for the last few weeks. The changes are not complete and are not ready to synchronize. However, you just received a request for an urgent fix from the user community that is critical for the forthcoming monthly submission. What do you do?
What you don’t want to do is take the model out of deployed mode! You also don’t want to lose all the development work you have been doing.
Don’t worry! Following the procedure below will ensure you can apply the hotfix quickly and keep your development work.
The following diagram illustrates the procedure:
It’s a Two-Stage Process:
Roll the development model back to a version that doesn’t contain any changes (is the same as production), and apply the hotfix to that version.
Add a new revision tag to the development model as a temporary placeholder. (Note the History ID of the last structural change as you'll need it later.)
On the development model, use History to restore to a point where development and production were identical (before any changes were made in development).
Apply the hotfix.
Save a new revision of the development model.
Sync the development model with the production model.
Production now has its hotfix.
Restore the changes to development and apply the hotfix.
On the development model, use the History ID from Stage 1 – Step 1 to restore to the version containing all of the development work (minus the hotfix).
Reapply the hotfix to this version of development.
Create a new revision of the development model.
Development is now back to where it was, with the hotfix now applied.
When your development work is complete, you can promote the new version to production using ALM best practice.
The procedure is documented in the Fixing Production Issues Anapedia article.
What is Pre-Allocation in Lists?
Pre-allocation in lists is a mechanism in Anaplan that adds a buffer to list lengths. It is not added by default for lists; it becomes enabled when a role is set on a list.
Please follow 1.03-01 though. Only add roles when needed.
When it is enabled, a 2 percent buffer is added to the list, and this includes all line items where the list is used. This means we create extra space (in memory) for each line item so that when a new list item is added, the line item does not need to be expanded or restructured.
When the buffer is used up (the list has run out of free slots) another 2 percent buffer will be created and any line items using the list will be restructured.
This buffer is not shown in the list settings in Anaplan, meaning if we had a list with 1,000 items, that’s what Anaplan would show as the size. But in the background, that list has an extra 20 hidden and unused items.
Pre-allocation also applies to list deletions but allows for 10 percent of the list to be deleted before any line items using the list get restructured.
The purpose of pre-allocation in lists is to avoid restructuring line items that use frequently updated lists.
What Happens When We Restructure?
Restructuring the model is an expensive task in terms of performance and time. The Anaplan Hyperblock gets its efficiency by holding your data and multi-dimensional structures in memory — memory being the fastest storage space for a computer. Creating the model structures in memory — building the Hyperblock — does take a significant time to complete. But once it's in memory, access is quick.
The initial model opening is when we first build those structures in memory. Once in memory, any further model opens (by other users, for example) are quick.
Restructuring is the process of having to rebuild some parts of the model in memory. In the case of adding an item to a list, that means any line item that uses that list as a dimension.
When restructuring a line item, we have to recalculate it, and this is often where we see the performance hit. This is because line items have references, so there is a calculation chain from any line item changed by that restructuring.
Pre-allocation is there to reduce this extra calculation caused by restructuring.
An example of this was seen in a model that was adding to a list that contained trial products. These products would then have a set of forecasted data calculated from historical data from real products. The list of these new products was small and changed reasonably frequently; it contained around 100 items. Adding an item took around two seconds (except every third item took two minutes).
This happened because of the difference between adding to the pre-allocated buffer and when it had to do the full calculation (and re-adjust the buffer). Without pre-allocation, every list addition would have taken two minutes.
Fortunately, we managed to optimize that calculation down from two minutes to several seconds, so the difference between adding to the pre-allocation buffer and the full calculation was around five seconds, a much more acceptable difference.
In summary, pre-allocation on lists can give us a great performance boost, but it works better with larger lists than small lists.
Small, Frequently Updated Lists
As we’ve seen, the pre-allocation buffer size is 2 percent, so on a large list — say one million items — we have a decent-sized buffer and can add many items.
When we have a small list that is frequently used, then a performance characteristic that is seen is frequently changing calculation times. This is especially the case if that list is heavily used throughout the model. A list with 100 items will restructure and recalculate on every third addition.
This will continue to be noticeable for quite some time. Doubling the list size is still just adding four unused items (2 percent of 200). When we have a small list that is frequently used, you will see the calculation times change from fast to slow while the buffer is frequently filled. In cases like this, it is very important to reduce and optimize the calculations as much as possible.
What Can Be Done?
There are a few options. You could always make the list bigger and increase the buffer so that it restructures less. How?
Option 1: Create a subset of “active” items and ignoring the additional list items used to bulk out the list.
The problem with this would be the size of any line items using that list would increase and so would their calculations. Changing from a 100-item list to a 10,000- or even 1,000-item list (enough to give us a bigger buffer) could greatly increase the model size.
Option 2: Create a new list that is not used in any modules so we avoid any restructuring costs.
This would work but it adds a lot of extra manual steps. You would have this new list used in a single data entry module, which means this data is unconnected with the rest of the model. Being connected is what gives us value. You would then need to create a manual process to push data from this unconnected module to one that is connected to the rest of the model (this way all the changes will happen at once). We do lose the real-time updates and benefits of Connected Planning, though.
Option 3: Reduce the impact of restructuring by optimizing the model and the formulas.
Our best option is optimizing calculations. If we have quick calculations, the difference between buffered and unbuffered list additions could be small.
The best way to achieve this would be through a Model Optimization Success Accelerator. This is a tailored service delivered by Anaplan Professional Services experts who aim to improve model performance through calculation optimizations. Please discuss this service with your Anaplan Business Partner.
You can also follow our best practice advice and reference the Planual to find ways you can optimize your own models.
These dashboards are absolutely critical to good usability of a model. Dashboards are the first contact between the end users and a model.
What SHOULD NOT be done in a landing dashboard:
Display detailed instructions on how to use the model. See "Instruction Dashboard" instead.
Use it for global navigation, built using text boxes and navigation buttons.
It will create maintenance challenges if different roles have different navigation paths.
It's not helpful once users know where to go.
What SHOULD be done in a landing dashboard:
Display KPIs with a chart that highlights where they stand on these KPIs, and highlight gaps/errors/exceptions/warnings.
A summary/aggregated view of data on a grid to support the chart. The chart should be the primary element.
Short instructions on the KPIs.
A link to an instruction-based dashboard that includes guidance and video links.
A generic instruction to indicate that the user should open the left-side sliding panel to discover the different navigation paths.
Users who perform data entry need access to the same KPIs as execs are seeing.
Landing Dashboard Example #1:
Displays the main KPI, which the planning model allows the organization to plan.
Landing Dashboard Example #2:
Provides a view on how the process is progressing against the calendar.
Landing Dashboard Example #3:
Created for executives who need to focus on escalation. Provides context and a call to action (could be a planning dashboard, too).
Deal with Monthly Dashboards
Many FP&A dashboards will need to display all 12 months in the current year, as well as Quarter, Half, and Total Year totals. Doing this is likely to create a very large grid, especially if more than one dimension is nested on the rows.
The grid displayed here is what may be requested when Anaplan is replacing a spreadsheet-based solution. The requirement being "At minimum, do what we could do in the spreadsheets".
Avoid the trap of rebuilding this in Anaplan. Usually, this simply creates an extra requirement to export this into Excel ® , have users work offline, and then import the data back into Anaplan, which kills the value that Anaplan can bring.
Instead, build the dashboard as indicated below:
Have end users view the aggregated values on the Cost center (the first nested dimensions) that will provide an overview on where most OPEX are spent
Have end users highlight a cost center, and enter its detailed sub-accounts
Visualize the monthly trend using a line chart for the selected sub-account
Anaplan API: Communication failure <SSL peer unverified: peer not authenticated>
This is a common error if a Customer Server is behind a proxy or firewall.
The solution is to have the customer whitelist '*.anaplan.com' for firewall blocks. If behind a proxy, use the '-via" or 'viauser" commands in Anaplan Connect.
The other very common cause for this error is that the security certificate isn’t synced up with java. If the whitelist or via command solutions don’t apply or don’t resolve the error, uninstalling and reinstalling Java usually does the trick. Here are the commands available:
Use specified proxy
Pass credentials to
L'application Bring Your Own Key (BYOK) vous permet maintenant de vous approprier les clés de chiffrement de vos données de modèle. Si vous avez accès à l'outil Anaplan Administration, vous pouvez chiffrer et déchiffrer des espaces de travail sélectionnés à l'aide de vos propres clés AES-256. À la différence des clés principales système, les clés créées par BYOK vous appartiennent et vous en assurez la sécurité. Aucun mécanisme ne permet au personnel Anaplan d'accéder à vos clés.
Bring Your Own Key (BYOK) - Guide de l'utilisateur
Bring Your Own Key (BYOK) est un produit complémentaire que votre organisation peut acheter si elle possède l'édition Enterprise.
Personal dashboards are a great new feature that enables end users to save a personalized view of a dashboard. To get the most out of this feature, here are a few tips and tricks.
Tidy Up Dashboards
Any change to a master dashboard (using the Dashboard Designer) will reset all personal views of a dashboard, so before enabling personal dashboards, take some time to ensure that the current dashboards are up to date:
Implement any pending development changes (including menu options).
Turn on the Dashboard Quick Access toolbar (if applicable).
Check and amend all text box headings and comments for size, alignment, spelling, and grammar.
Delete or disable any redundant dashboards to ensure end users don’t create personal views of obsolete dashboards.
Use Filters R ather Th an Show/Hide
It’s best practice to use a filter rather than show and hide for the rows and/or columns on a grid.
This is now more beneficial because amending the items shown or hidden on a master dashboard will reset the personal views. For example, suppose you want to display just the current quarter of a timescale. You could manually show/hide the relevant periods, but, at quarter end when the Current Period is updated, the dashboard will need to be amended, and all those personal views will be reset. If you use a filter, referencing a time module, the filter criteria will update automatically, as will the dashboard. No changes are made to the master dashboard, and all the personal views are preserved.
Create a Communication and Migration Strategy
Inevitably there will be changes that must be made to master dashboards. To minimize the disruption for end users, create a communication plan and follow a structured development program . These can include the following:
Bundle up dashboard revisions into a logical set of changes.
Publish these changes at regular intervals (e.g., on a monthly cycle).
Create a regular communication channel to inform users of changes and the implications of those changes.
Create a new dashboard, and ask end users to migrate to the new dashboard over a period of time before switching off the old dashboard.
Application Lifecycle Management (ALM)
If you are using ALM: any structural changes to master dashboards will reset all personal views of dashboards.
A dashboard with grids that includes large lists that have been filtered and/or sorted can take time to open. The opening action can also become a blocking operation; when this happens, you'll see the blue toaster box showing "Processing....." when the dashboard is opening. This article includes some guidelines to help you avoid this situation.
Rule 1: Filter large lists by creating a Boolean line item.
Avoid the use of filters on text or non-Boolean formatted items for large lists on the dashboard. Instead, create a line item with the format type Boolean and add calculations to the line item so that the results return the same data set as the filter would. Combine multiple conditions into a single Boolean value for each axis.
This is especially helpful if you implement user-based filters, where the Boolean will be by the user and by the list to be filtered.
The memory footprint of a Boolean line item is 8x smaller than other types of line items.
Known issue: On an existing dashboard where a saved view is being modified by replacing the filters with a Boolean line item for filtering, you must republish it to the dashboard. Simply removing the filters from the published dashboard will not improve performance.
Rule 2: Use the default Sort.
Use sort carefully, especially on large lists. Opening a dashboard that has a grid where a large list is sorted on a text formatted line item will likely take 10 seconds or more and may be a blocking operation.
To avoid using the sort: Your list is (by default) sorted by the criteria you need. If it is not sorted, you can still make the grid usable by reducing the items using a user-based filter.
Rule 3: Reduce the number of dashboard components.
There are times when the dashboard includes too many components, which slows performance. Avoid horizontal scrolling and try and keep vertical scrolling to no more than three pages deep. Once you exceed these limits, consider moving the components into multiple dashboards. Doing so will help both performance and usability.
Rule 4: Avoid using large lists as page selectors.
If you have a large list and use it as a page selector on a dashboard, that dashboard will open slowly. It may take 10 seconds or more. The loading of the page selector takes more than 90% of the total time.
Known issue: If a dashboard grid contains list formatted line items, the contents of page selector drop-downs are automatically downloaded until the size of the list meets a certain threshold; once this size is exceeded, the download happens on demand, or in other words when a user clicks the drop down. The issue is that when Anaplan requests the contents of list formatted cell drop-downs, it also requests contents of ALL other drop-downs INCLUDING page selectors.
Recommendation: Limit the page selectors on medium to large lists using the following tips:
a) Make the page selector available in one grid and use the synchronized paging option for all other grids and charts. No need to allow users to edit the page in every dashboard grid or chart.
b) For multi-level hierarchies, consider creating a separate dashboard with multiple modules (just with the list entries) to enable the users to navigate to the desired level. They can then navigate back to the main planning dashboard. This approach also de-clutters the dashboards.
c) If the dashboard elements don't require the use of the list, you should publish them from a module that doesn't contain this list. For example, floating page selectors for time or versions, or grids that are displayed as rows/columns-only should be published from modules that do not include the list.
Why? The view definitions for these elements will contain all the source module's dimensions, even if they are not shown, and so will carry the overhead of populating the large page selector if it was present in the source.
Rule 5: Split formatted line items into separate modules.
Having many line items (that are formatted as lists) in a single module displayed on a dashboard can reduce performance as all of the lists are stored in memory (similar to Rule 4). It is better, if possible, to split the line items into separate modules. Remember from above, try not to have too many components on a dashboard; only include what the users really need and create more dashboards as needed.
Problem to solve:
As an HR manager, I need to enter the salary raise numbers for multiple regions that I'm responsible for.
As a domain best practice, my driver-based model helps me to enter raise guidelines, which will then change at the employee level.
Usability issue addressed: I have ten regions, eight departments in each, with a total of 10,000+ employees. I need to align my bottom-up plan to the down target I received earlier.
I need to quickly identify what region is above/behind target and address the variance. My driver-based raise modeling is fairly advanced, and I need to see what the business rules are. I need to quickly see how it impacts the employee level.
Call to action:
Step 1: Spot what region I need to address.
Step 2: Drill into the variances by department.
Steps 1 & 2 are analytics steps: "As an end user, I focus first on where the biggest issues are." This is a good usability practice that helps users.
Step 3: Adjusting the guidelines (drivers)
There are not excessive instructions on how to build and use guidelines, which would have cluttered the dashboard. Instead, Anaplan added a "view guideline instruction" button. This button should open a dashboard dedicated to detailed instructions or link to a video that explains how guideline works.
The chart above the grid will adjust as guidelines are edited. That is a good practice for impact analysis— no scrolling or clicking needed to view how the changes will impact the plan.
Step 4: Review a summary of the variance after changes are made. Putting steps 1–4 close to each other is a usable way of indicating to a user that he/she needs to iterate through these four steps to achieve their objective, which is to have every region and every department be within the top down target.
Step 5: A detailed impact analysis, which is placed directly below steps 3 and 4. This allows end users to drill into the employee-level details and view the granular impact of the raise guidelines.
Notice the best practices in step 5:
The customer will likely ask to see 20 to 25 employee KPIs across all employees and will be tempted to display these as one large grid. This can quickly lead to an unusable grid made of thousands of rows (employees) across 25 columns.
Instead, we have narrowed the KPI list to only ten that display without left-right scrolling.
Criteria to elect these ten: be able to have a chart that compares employees by these KPIs.
The remaining KPIs are displayed as an info grid, which only displays values for the selected employee. Things like region, zip codes, and dates are removed from the grid as they do not need to be compared side-by-side with other KPIs or between employees.
Details of known issues
Performance issues with long nested formulas
Need to have a long formula on time as a result of nested intermediate calculations.
If the model size does not prevent from adding extra line items, it's a better practice to create multiple intermediate line items and reduce the size of the formula, as opposed to nesting all intermediate calculations into one gigantic formula.
This applies to summary formulae (SUM, LOOKUP, SELECT).
Combining SUM and LOOKUP in the same line item formula can cause performance issues in some cases. If you have noticed a drop in performance after adding a combined SUM and LOOKUP to a single line item, then split it into two line items.
RANKCUMULATE causes slowness
A current issue with the RANKCUMULATE formula can mean that the time to open the model, including rollback, can be up to five times slower than they should be.
There is currently no suitable workaround. Our recommendations are to stay within the constraints defined in Anapedia.
SUM/LOOKUP with large cell count
Separate formulas into different line items to reduce calculation time (fewer cells need to recalculate parts of a formula that would only affect a subset of the data).
A known issue with SUM/LOOKUP combinations within a formula can lead to slow model open and calculation times, particularly if the line item has a large cell count.
All line items do not apply to time or versions.
Y = X[SUM: R, LOOKUP: R]
Y Applies to [A,B]
X Applies to [A,B]
R Applies to [B] list formatted [C]
Add a new line item 'intermediate' that must have 'Applies To' set to the 'Format' of 'R'
intermediate = X[SUM: R]
Y = intermediate[LOOKUP: R]
This issue is currently being worked on by Development and a fix will be available in a future release
Calculations are over non-common dimensions
Anaplan calculates quicker if calculations are over common dimensions. Again, best seen in an example. If you have, List W, X Y = A + B Y Applies To W, X A Applies To W B Applies To W This performs slower than, Y = Intermediate Intermediate = A + B Intermediate Applies To W All other dimensions are the same as above. Similarly, you can substitute A & B above for a formula, e.g. SUM/LOOKUP calculations.
Cell history truncated
Currently, history generation has a time limit of 60 seconds set. The history generation is split into three stages with 1/3 of time allocated to each.
The first stage is to build a list of columns required for the grid. This involves reading all the history. If this takes more than 20 seconds, then the user receives the message "history truncated after x seconds - please modify the date range," where X is how many seconds it took. No history is generated.
If the first stage completes within 20 seconds, it goes on to generate the full list of history.
In the grid only the first 1000 rows are displayed; the user must Export history to get a full history. This can take significant time depending on volume.
The same steps are taken for model and cell history. The cell history is generated from loading the entire model history and searching through the history for the relevant cell information. When the model history gets too large then it is currently truncated to prevent performance issues. Unfortunately, this can make it impossible to retrieve the cell history that is needed.
Make it real time when needed
Do not make it real time unless it needs to be.
By this we mean, do not have line items where users input data being referenced by other line items unless they have to be. A way around this could be to have users have their data input sections, which is not referenced anywhere, or as little as possible, and, say, at the end of the day when no users are in the model, run an import which would update into cells where calculations are then done. This may not always be possible if the end user needs to see resulting calculations from his inputs, but if you can limit these to just do the calculations that he needs to see and use imports during quiet times then this will still help.
We see this often when not all reporting modules need to be recalculated real time. In many cases, many of these modules are good to be calculated the day after.
Don't have line items that are dependent on other line items unnecessarily.This can cause Anaplan to not utilize the maximum number of calculations it can do at once. This happens where a line items formula cannot be calculated because it is waiting on results of other line items. A basic example of this can be seen with line item's A, B, and C having the formulas: A - no formula B= A C = B Here B would be calculated, and then C would be calculated after this. Whereas if the setup was: A - no formula B = A C = A Here B and C can be calculated at the same time. This also helps if line item B is not needed it can then be removed, further reducing the number of calculations and the size of the model. This needs to considered on a case-by-case basis and is a tradeoff between duplicating calculations and utilizing as many threads as possible. If line item B was referenced by a few other line items, it may indeed be quicker to have this line item.
Summary cells often take processing time even if they are not actually recalculated because they must check all the lower level cells.
Reduce summaries to ‘None’ wherever possible. This not only reduces aggregations, but also the size of the model.
Assume the following Non-Composite list, ragged hierarchy, needs to be set to Production Data.
We need to refer to the ultimate parent to define the logic calculation. In the example, we have assumed that children of Parent 1 and Parent 3 need to return the 'logic 1' value from the constants module below, and those under Parent 2 return 'logic 2,' and we apportion the results based on the initial data of the children.
Data / IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' THEN Data[SELECT: 'Non-Composite List'.'Parent 1'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Data[SELECT: 'Non-Composite List'.'Parent 2'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Data[SELECT: 'Non-Composite List'.'Parent 3'] ELSE 0
Select Proportion * IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Parent Logic Constants.'Logic 1' ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Parent Logic Constants.'Logic 2' ELSE 0
These “hard references” will prevent the list from being set as a production list.
Create a Parents Only list (this could be imported from the Non-Composite list). As we don't need the sub-level parents, we do not need to include 'Child 3.1,' even though it is technically a parent.
To calculate the proportion calculation without the SELECT, a couple of intermediate modules are needed:
Parent Mapping Module
This module maps the Non-Composite parents to the Parents Only list. Due to the different levels in the hierarchy, we need to check for sub levels and use the parent of Child 3.1. In this example, the mapping is automatic because the items in the Parents Only list have the same name as those in the Non-Composite list. The mapping could be a manual entry if needed.
The formula and “applies to” are:
Non-Composite Parent: PARENT(ITEM('Non-Composite List')) Applies to: Non-Composite List
Parent of Non-Composite Parent: PARENT(Non-Composite Parent) Applies to: Non-Composite List
Parent to Map: IF ISNOTBLANK(PARENT(Parent of Non Composite Parent)) THEN Parent of Non Composite Parent ELSE Non Composite Parent Applies to: Non-Composite List
Parents Only List FINDITEM(Parents Only List, NAME(Parent to Map)) Applies to: Parents Only List
Parents Only Subtotals
An intermediary module is needed to hold the subtotals.
Parent Logic Calc.Data[SUM: Parent Mapping.Parents Only List]
Parent Logic? Module
We now define the logic for the parents in a separate module.
Add Boolean line items for each of the “logic” types.
Then you can refer to the logic above in the calculations.
Data / Parents Only Subtotals.Calculation[LOOKUP: Parent Mapping.Parents Only List]
Lookup Proportion * IF Parent Logic?.'Logic 1?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 1' ELSE IF Parent Logic?.'Logic 2?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 2' ELSE 0
The list can now be set as a production list as there are no “hard references”. Also, the formulas are smaller, simpler and now more flexible should the logic need to change. If Parent 3 needs to use Logic 2, it is a simple change to the checkbox.
Imports are blocking operations: To maintain a consistent view of the data, the model is locked during the import, and concurrent imports run by end-users will need to run one after the other and will block the model for everyone else. Exports are blocking for data entry while the export data is retrieved, and then the model is released. During the blocking phase, users can still navigate within the model.
Rule #1 Carefully Decide If You Let End-Users Import (And Export) During Business Hours
Imports executed by end-users should be carefully considered, and if possible, executed once or twice a day. Customers more easily accept model updates at scheduled hours for a predefined time—even if it takes 10+ minutes—and are frustrated when these imports are run randomly during business hours.
Your first optimization is to adjust the process and run these imports by an administrator at a scheduled time, and then let the user based know about the schedule.
Rule #2 Use a Saved View
The first part of any import (or export) is retrieving the data. The time it takes to open the view directly affects the time of the import or export.
Always import from a saved view—NEVER from the default view. And use the naming convention for easy maintenance.
Ensure the view is using optimized filters with a single Boolean value per axis.
Hide the line items that are not needed for import; do not bring extra columns that are not needed.
If you have done all of the above, and the view is still taking time to complete, consider using the Tabular Multi Column export and filter "in the way out." This has been proven to improve some sluggish exports.
Rule #3 Mapping Objective = Zero Errors or Warning
Make sure your import executes with no errors or warnings as every error takes processing time. The time to import into a medium-to-large list (>50k) is significantly reduced if no errors are to be processed.
In the import definition, always map all displayed line items (source→target) or use the "ignore" setting. Don't leave any line item unmapped.
Rule #4 Watch the Formulas Recalculated During the Import
If your end-users encounter poor performance when clicking a button that triggers an import or a process, it is likely due to the recalculations that are triggered by the import, especially if the action creates or moves items within a hierarchy.
You will likely need the help of the Anaplan Model Optimization team to identify what formulas are triggered after the import is done and to get a performance check on these formulas to identify which one takes most of the time. Usually, those fetching many cells such as SUM, LOOUKP, ANY, or FINDITEM are likely to be responsible for the performance impact. Speak to your Business Partner for more details on the Model Optimization services available to you.
To solve such situations, you will need to challenge the need for recalculating the formula identified each time a user calls the action.
Often, for actions such as creations, moves, assignments done in WFP or Territory Planning, many calculations used for reporting are triggered in real-time after the hierarchy is modified by the import, and are not necessarily needed by users until later in the process.
The recommendation is to challenge your customer and see if these formulas can be calculated only once a day, instead of each time a user runs the action. If this is acceptable, you'll need to rearchitect your modules and/or formulas so that these heavy formulas get to run through a different process run daily by an administrator and not by each end-users. If not, you will need to look at the formulas more closely to see what improvements can be made. Remember, breaking formulas up often helps performance.
Rule #5 Don't Import List Properties
Importing list properties takes more time than importing these as a module line item. Review your model list impacted by imports, and look to replace list properties with module line items when possible. Use a system module to hold these for the key hierarchies, as per D.I.S.C.O.
Rule #6 Get Your Data Hub
Hub and spoke: Setup a data hub model, which will feed the other production models used by stakeholders.
It will prevent production models to be blocked by a large import from an external data source. But since data hub to production model imports will still be blocking operations, carefully filter what you import, and use the best practices rules listed above.
All import, mapping/transformation modules required to prepare the data to be loaded into planning modules can now be located in a dedicated data hub model and not in the planning model. This model will then be smaller and will work more efficiently.
Try and keep the transaction data history in the data hub with a specific analysis dashboard made available for end users; often, the detail is not needed for planning purposes, and holding this data in the planning model has a negative impact on size, model opening times, and performance.
As a reminder of the other benefits of a data hub not linked to performance:
Better structure, easier maintenance: data hub helps keep all the data organized in a central location.
Better governance: Whenever possible put this Data Hub on a different workspace. That will ease the separation of duties between production models and meta-data management, at least on actual data and production lists. IT departments will love the idea to own the data hub and have no one else be an administrator in the workspace.
Lower implementation costs: A data hub is a way to reduce the implementation time of new projects. Assuming IT can load the data needed by the new project in the data hub, then business users do not have to integrate with complex source systems but with the Anaplan data hub instead.
Rule #7 Incremental Import/Export
This can be the magic bullet in some cases. If you export on a frequent basis (daily or more) from an Anaplan model into a reporting system, or write back to the source system, or simply transfer data from one Anaplan model to another, you have ways to only import/export the data that have changed since the last export.
Use a Boolean line item to identify records that have changed and only import those.
Note that this article uses a planning dashboard as an example, but many of these principles apply to other types of dashboards as well.
Building a useful planning dashboard always starts with getting a set of very clear user stories, which describe how a user should interact with the system.
The user stories need to identify the following:
What the user wants to do.
What data the user needs to see to perform this action.
What data the user wants to change.
How the user will check that changes made have taken effect.
If one or more of the above is missing in a user story, ask the product owner to complete the description. Start the dashboard design, but use it to obtain the answers. It will likely change as more details arrive.
Product Owners Versus Designers
Modelers should align with product owners by defining concrete roles and responsibilities for each team member.
Product owners should provide what data users are expecting to see and how they wish to interact with the data, not ask for specific designs (this is the role of the modelers/designers).
Product owners are responsible for change management and should be extra careful when dashboard/navigation is significantly different than what is currently being used (i.e. Excel ® ).
Pre-Demo Peer Review
Have a usability committee that:
Is made up of modeling peers outside the project and/or project team members outside of modeling team.
Will host a mandatory gate-check meeting to review models before demos to product owners or users.
Committee is designed to ensure:
Best design by challenging modelers.
Consistency between models.
The function is clear.
Exceptions/calls to action are called out.
The best first impression.
Exception, Call to Action, Measure Impact
Building a useful planning dashboard will be successful if the dashboard allows users to highlight and analyze exceptions (issues, alerts, warning), take action and plan to solve these, and always visually check the impact of the change against a target.
Example: A dashboard is built for these two user stories that compliment each other.
Story 1: Review all of my accounts for a specific region, manually adjust the goals and enter comments.
Story 2: Edit my account by assigning direct and overlay reps.
The dashboard structure should be made of:
Dashboard header: Short name describing the purpose of the dashboard at the top of the page in "Heading 1."
Groupings: A collection of dashboard widgets.
Call to action.
Info grid(s) : Specific to one item of the main grid.
Info charts: Specific to one item of the main grid.
Specific action buttons: Specific to one item of the main grid.
Main charts: Covers more than one item of the main grid.
Individual line items: Specific to one item of the main grid, usually used for commentaries.
A dashboard can have more than one of these groupings, but all elements within a grouping need to answer the needs of the user story.
Use best judgements to determine the number of groupings added to one dashboard. A maximum of two-to-three groupings is reasonable. Past this range, consider building a new dashboard. Avoid having a "does it all" dashboard, where users keep scrolling up and down to find each section.
If users ask for a table of contents at the top of a dashboard, it's a sign that the dashboard has too much functionality and should be divided into multiple dashboards.
Call to Action
Write a short sentence describing the task to be completed within this grouping. Use the Heading 2 format.
The main grid is the central component of the dashboard, or of the grouping. It's where the user will spend most of their time.
This main grid will display the KPIs needed for the task (usually in the columns) and will display one or more other dimension in the rows.
Warning: Users may ask for 20+ KPIs and need these KPIs to be broken down by many dimensions, such as by product, actual/plan/variance, or by time. It's critical to have a main grid as simple and as decluttered as possible. Avoid the "data jungle" syndrome. Users are used to "data jungles" simply because that's what they are used to with Excel.
Tips to avoid data jungle syndrome:
Make a careful KPI election (KPIs are usually the line items of a module).
Display the most important KPIs ONLY, which are those needed for decision making. Hide the others for now.
A few criteria for electing a KPI in the main grid are:
The KPI is meant to be compared across the dimension items in the rows, or across other KPIs.
Viewing the KPI values for all of the rows is required to make the decision.
The KPI is needed for sorting the rows (except on row name).
A few criteria for not electing a KPI in the main grid are (besides not matching the above criteria) when we need these KPIs in more of a drill down mode; The KPI provides valid extra info, but just for the selected row of the Dashboard and does not need to be displayed for all rows.
These "extra info" KPIs should be displayed in a different grid, which will be referred to as "info grid" in this document. Take advantage of the row/column sync functionality to provide a ton of data in your dashboard but only display data when requested or required.
Design your main grid in such a way that it does not require the user to scroll left and right to view the KPIs:
Efficiently select KPIs.
Use the column header wrap.
Set the column size accordingly.
It is ok to have users scroll vertically on the main grid. Only display 15 to 20 rows at a time when there are numerous rows, as well as other groupings and action buttons, to display on the same dashboard.
Use sorts and a filter to display relevant data.
Sort Your Grid
Always sort your rows. Obtain the default sort criteria via user stories. If no special sort criteria is called out, use the alphanumeric sort on the row name. This will require a specific line item.
Train end users to use the sort functionality.
Filter Your Grid
Ask end users or product owners what criteria to use to display the most relevant rows. It could be:
Those that make 80 percent of a total. Use the RankCumulate function.
Those that have been modified lately. This requires a process to attach a last modified date to a list item, updated daily via a batch mode.
When the main grid allows item creation, always display the newly created first.
If end users need to apply their own filter values on some attributes of the list items, such as filter to show only those who belong to EMEA or those whose status is "in progress," build pre-set user-based filters.
Use the new Users list.
Create modules dimensioned by user with line items (formatted as lists) to hold the different criteria to be used.
Create a module dimensioned by Users and the list to be filtered. In this module resolve the filter criteria from above against the list attributes to a single Boolean.
Apply this filter in the target module.
Educate the users to use the refresh button, rather than create an "Open Dashboard" button.
Color Code Your Grid
Use colored cells to call attention to areas of a grid, such as green for positive and red for negative.
Color code cells that specifically require data entry.
Display the Full Details
If a large grid is required, something like 5k lines and 100 columns, then:
Make it available in a dedicated full-screen dashboard via a button available from the summary dashboard, such as an action button.
Do not add such a grid to a dashboard where KPIs, charts, or multiple grids are used for planning.
These dashboards are usually needed for ad-hoc analysis and data discovery, or random verification of changes, and can create a highly cluttered dashboard.
The main chart goes hand-in-hand with the main grid. Use it to compare one or more of the KPIs of the main grid across the different rows.
If the main grid contains hundreds or thousands of items, do not attempt to compare this in the main chart. Instead, identify the top 20 rows that really matter or that make most of the KPI value and compare these 20 rows for the selected KPI.
Location: Directly below or to the right of main display grid; should be at least partially visible with no scrolling.
Synchronization with a selection of KPI or row of the main display grid.
Should be used for:
Comparison between row values of the main display grid.
Displaying difference when the user makes change/restatement or inputs data.
In cases where a chart requires 2–3 additional modules to be created: Implement and test performance.
If no performance issues are identified, keep the chart.
If performance issues are identified, work with product owners to compromise.
These are the grids that will provide more details for an item selected on the main grid. If territories are displayed as rows, use an info grid to display as many line items as necessary for this territory. Avoid cluttering your main grid by displaying all of these line items for all territories at once. This is not necessary and will create extra clutter and scrolling issues for end users.
Location: Below or to the right of the main display grid.
Synced to selection of list item in the main display grid.
Should read vertically to display many metrics pertaining to list item selected.
Similar to info grids, an info chart is meant to compare one or more KPIs for a selected item in the rows of the main grid.
These should be used for:
Comparison of multiple KPIs for a single row.
Comparison or display of KPIs that are not present on the main grid, but are on info grid(s).
Comparing a single row's KPI(s) across time.
Place it on the right of the main grid, above or below an info grid.
Specific Action Buttons
Location: Below main grid; Below the KPI that the action is related to, OR to the far left/right - similar to "checkout."
Should be an action that is to be performed on the selected row of the main grid.
Can be used for navigation as a drill down to a detailed view of a selected row/list item.
Should NOT be used as lateral navigation between dashboards; Users should be trained to use the left panel for lateral navigation.
Individual Line Items
Serve as a call out of important KPIs or action opportunities (i.e., user setting container for explosion, Container Explosion status).
If actions taken by users require additional collaboration with other users, it should be published outside the main grid (giving particular emphasis by publishing the individual line item/s).
Call to action.
Serves as a header for a grouping.
Short sentence describing what the user should be performing within the grouping.
Formatted in "Heading 2."
Directly located next to a drop-down, input field, or button where the function is not quite clear.
No more than 5–6 words.
Formatted in "instructions."
Use Tooltips on modules and line items for more detailed instructions to avoid cluttering the dashboard.
A large and complex model such as 10B cells can take up to 10 minutes to load the first time it's in use after a period of inactivity of 60 minutes.
The only way to reduce the load time is by identifying what formula takes most of the time. This requires the Anaplan L3 support (ask for a Model Opening Analysis), but you can reduce the time yourself by applying the formula best practices listed above.
One other possible leverage is on list setup: Text properties on a list can increase the load times, and subsets on lists can disproportionately increase load times. It is best practice not to use List Properties but house the attributes in a System model dimensioned by the list. See Best practice for Module design for more details.
A model will save when the amount of changes made by end-users exceeds a certain threshold. This action can take several minutes and will be a blocking operation. Administrators have no leverage on model save besides formula optimization and reducing model complexity. Using ALM and Deployed mode increases this threshold, so it is best to use Deployed mode whenever possible.
A model will roll back in some cases of an invalid formula, or when a model builder attempts to adjust a setting that would result in an invalid state. In some large models, the rollback takes approximately the time to open the model, and up to 10 minutes worth of accumulated changes, followed by a model save.
The recommendation is to use ALM and have a DEV model which size does not exceed 500M cells, with a production list limited to a few dozen items, and have TEST and PROD models with the full size and large lists. Since no formula editing will happen in TEST or PROD, the model will never rollback after a user action. It can roll back on the DEV model but will take a few seconds only if the model is small.
Reducing the number of calculations will lead to quicker calculations and improve performance. However, this doesn’t mean combining all your calculations into fewer line items, as breaking calculations into smaller parts has major benefits for performance.
Learn more about this in the Formula Structure article.
How is it possible to reduce the number of calculations? Here are three easy methods:
Turn off unnecessary Summary method calculations.
Avoid formula repetition by creating modules to hold formulas that are used multiple times.
Ensure that you are not including more dimensions than necessary in your calculations.
Turn off Summary method calculations
Model builders often include summaries in a model without fully thinking through if they are necessary. In many cases, the summaries can be eliminated. Before we get to how to eliminate them, let’s recap on how the Anaplan engine calculates.
In the following example we have a Sales Volume line-item that varies by the following hierarchies:
This means that from the detail values at SKU, City, and Channel level, Anaplan calculates and holds all 23 of the aggregate combinations shown below—24 blocks in total.
With the Summary options set to Sum, when a detailed item is amended (represented in the grey block), all the other aggregations in the hierarchies are also re-calculated. Selecting the None summary option means that no calculations happen when the detail item changes. The varying levels of hierarchies are quite often only there to ease navigation, and the roll-up calculations are not actually needed, so there may be a number of redundant calculations being performed. The native summing of Anaplan is a faster option, but if all the levels are not needed it might be better to turn off the summary calculations and use a SUM formula instead.
For example, from the structure above, let’s assume that we have a detailed calculation for SKU, City, and Channel (SALES06.Final Volume). Let’s also assume we need a summary report by Region and Product, and we have a module (REP01) and a line item (Volume) dimensioned as such.
REP01.Volume = SALES06 Volume Calculation.Final Volume is replaced with REP01.Volume = SALES06.Final Volume[SUM:H01 SKU Details.Product, SUM:H02 City Details.Region]
The second formula replaces the native summing in Anaplan with only the required calculations in the hierarchy.
How do you know if you need the summary calculations? Look for the following:
Is the calculation or module user-facing?
If it is presented on a dashboard, then it is likely that the summaries will be needed. However, look at the dashboard views used. A summary module is often included on a dashboard with a detail module below; Effectively, the hierarchy sub-totals are shown in the summary module, so the detail module doesn’t need the sum or all the summary calculations.
Detail to Detail
Is the line item referenced by another detailed calculation line item? This is very common, and if the line item is referenced by another detailed calculation the summary option is usually not required. Check the Referenced by column and see if there is anything referencing the line item.
Calculation and staging modules
If you have used the D.I.S.C.O. module design, you should have calculation/staging modules. These are often not user-facing and have many detailed calculations included in them. They also often contain large cell counts, which will be reduced if the summary options are turned off.
Can you have different summaries for time and lists?
The default option for Time Summaries is to be the same as the lists. You may only need the totals for hierarchies, or just for the timescales. Again, look at the downstream formulas.
The best practice advice is to turn off the summaries when you create a line item, particularly if the line item is within a Calculation module (from the D.I.S.C.O. design principles).
Avoid Formula Repetition
An optimal model will only perform a specific calculation once. Repeating the same formula expression multiple times will mean that the calculation is performed multiple times. Model builders often repeat formulas related to time and hierarchies. To avoid this, refer to the module design principles (D.I.S.C.O.) and hold all the relevant calculations in a logical place. Then, if you need the calculation, you will know where to find it, rather than add another line item in several modules to perform the same calculation.
If a formula construct always starts with the same condition evaluation, evaluate it once and then refer to the result in the construct. This is especially true where the condition refers to a single dimension but is part of a line item that goes across multiple dimension intersections. A good example of this can be seen in the example below:
START() <= CURRENTPERIODSTART() appears five times and similarly START() > CURRENTPERIODSTART() appears twice.
To correct this, include these time-related formulas in their own module and then refer to them as needed in your modules.
Remember, calculate once; reference many times!
Taking a closer look at our example, not only is the condition evaluation repeated, but the dimensionality of the line items is also more than required. The calculation only changes by the day, as per the diagram below:
But the Applies To here also contains Organization, Hour Scale, and Call Center Type.
Because the formula expression is contained within the line item formula, for each day the following calculations are also being performed:
And, as above, it is repeated in many other line items.
Sometimes model builders use the same expression multiple times within the same line item. To reduce this overcalculation, reference the expression from a more appropriate module; for example, Days of Week (dimensioned solely by day) which was shown above. The blueprint is shown below, and you can see that the two different formula expressions are now contained in two line items and will only be calculated by day; the other dimensions that are not relevant are not calculated.
Substitute the expression by referencing the line items shown above.
In this example, making these changes to the remaining lines in this module reduces the calculation cell count from 1.5 million to 1500.
Check the Applies to for your formulas, and if there are extra dimensions, remove the formula and place it in a different module with the appropriate dimensionality .
Thinking through the results of a modeling decision is a key part of ensuring good model performance—in other words, making sure the calculation engine isn’t overtaxed. This article highlights some ideas for how to lessen the load on the calculation engine.
Formulas should be simple; a formula that is nested, or uses multiple combinations, uses valuable processing time. Writing a long, involved formula makes the engine work hard. Seconds count when the user is staring at the screen. Simple is better. Breaking up formulas and using other options helps keep processing speeds fast.
You must keep a balance when using these techniques in your models, so the guidance is as follows:
Break up the most commonly changed formula
Break up the most complex formula
Break up any formula you can’t explain the purpose of in one sentence
Formulas with many calculated components
The structure of a formula can have a significant bearing on the amount of calculation that happens when inputs in the model are changed. Consider the following example of a calculation for the Total Profit in an application. There are five elements that make up the calculation: Product Sales, Service Sales, Cost of Goods Sold (COGS), Operating Expenditure (Op EX), and Rent and Utilities. Each of the different elements is calculated in a separate module. A reporting module pulls the results together into the Total Profit line item, which is calculated using the formula shown below. What happens when one of the components of COGS changes? Since all the source components are included in the formula, when anything within any of the components changes, this formula is recalculated. If there are a significant number of component expressions, this can put a larger overhead on the calculation engine than is necessary.
There is a simple way to structure the module to lessen the demand on the calculation engine. You can separate the input lines in the reporting module by creating a line item for each of the components and adding the Total Profit formula as a separate line item. This way, changes to the source data only cause the relevant line item to recalculate.
For example, a change in the Product Sales calculation only affects the Product Sales and the Total Profit line items in the Reporting module; Services Sales, Op EX, COGS and Rent & Utilities are unchanged. Similarly, a change in COGS only affects COGS and Total Profit in the Reporting module.
Keep the general guidelines in mind. It is not practical to have every downstream formula broken out into individual line items.
Plan to provide early exits from formulas
Conditional formulas (IF/THEN) present a challenge for the model builder in terms of what is the optimal construction for the formula, without making it overly complicated and difficult to read or understand. The basic principle is to avoid making the calculation engine do more work than necessary. Try to set up the formula to finish the calculations as soon as possible.
Always put first the condition that is most likely to occur. That way the calculation engine can quit the processing of the expression at the earliest opportunity.
Here is an example that evaluates Seasonal Marketing Promotions:
The summer promotion runs for three months and the winter promotion for two months.
There are more months when there is no promotion, so this formula is not optimal and will take longer to calculate.
This is better, as the formula will exit after the first condition more frequently.
There is an even better way to do this. Following the principles from above, add another line item for no promotion.
And then the formula can become:
This is even better because the calculation for No Promo has already been calculated, and Summer Promo occurs more frequently than Winter Promo.
It is not always clear which condition will occur more frequently than others, but here are a few more examples of how to optimize formulas:
FINDITEM formula The Finditem element of a formula will work its way through the whole list looking for the text item, and if it does not find the referenced text, it will return blank. If the referenced text is blank, it will also return a blank. Inserting a conditional expression at the beginning of the formula keeps the calculation engine from being overtaxed.
IF ISNOTBLANK(TEXT) THEN FINDITEM(LIST,TEXT) ELSE BLANK
IF BLANK(TEXT) THEN BLANK ELSE FINDITEM(LIST,TEXT)
Use the first expression if most of the referenced text contains data and the second expression if there are more blanks than data.
LAG, OFFSET, POST, etc. If in some situations there is no need to lag or offset data, for example, if the lag or offset parameter is 0. The value of the calculation is the same as the period in question. Adding a conditional at the beginning of the formula will help eliminate unnecessary calculations:
IF lag_parameter = 0 THEN 0 ELSE LAG(Lineitem, lag_parameter, 0)
IF lag_parameter <> 0 THEN LAG(Lineitem, lag_parameter, 0) ELSE 0
The use of formula a or b will depend on the most likely occurrence of 0s in the lag parameter.
Booleans Avoid adding unnecessary clutter for line items formatted as BOOLEANS. There is no need to include the TRUE or FALSE expression, as the condition will evaluate to TRUE or FALSE.
IF Sales > 0 then TRUE ELSE FALSE
You can interact with the data in your models using Anaplan's RESTful API. This enables you to securely import and export data, as well as run actions through any programmatic way you desire. The API can be leveraged in any custom integration, allowing for a wide range of integration solutions to be implemented. Completing an integration using the Anaplan API is a technical process that will require significant action by an individual with programming experience.
Visit the links below to learn more:
Anaplan API Guide
You can also view demonstration videos to understand how to implement APIs in your custom Integration client. The below videos show step-by-step guides of sequencing API calls and exporting data from Anaplan, importing data into Anaplan, and running delete actions and Anaplan processes.
API sequence for uploading a file to Anaplan and running an import action is as follows:
API sequence for running an export action and downloading a file from Anaplan is as follows:
API sequence for running an Anaplan process and a delete action is as follows:
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
Add the following line items:
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.
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.
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
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.
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:
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.
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
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.
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
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
Add two Boolean formatted line items
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%
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.
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.
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
This article describes the technique to dynamically filter specific levels of a hierarchy on a dashboard and provides a method to select and visualize hierarchies on a dashboard.
This article explains how to configure the calculation of the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level on a dashboard.
In this example, we have an organized hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy, we want to calculate a module value that returns the associated level that is to be displayed on a dashboard.
Notes and Platform Context
The technique addresses a specific limitation within dashboards where a composite hierarchy's list level cannot be selected if the list is synchronized to module objects on the dashboard.
The technique uses a static module based on the levels of the composite structure used for filtering of the object on a dashboard.
The technique is based on utilizing the Summary Method "Ratio" on line items corresponding to the list levels of the composite hierarchy to define the values of the filtering line items. Note that this method is not a formula calculation, but rather a use of the Summary Method Ratio on each line item applied to the composite hierarchy.
In this example, a four-level list composite hierarchy list is used. The hierarchy in this example has asymmetrical leaf items per parent:
Defining the Level of Each List
In order to calculate the level of each item in each of the lists L1 - L4, we need to create a module that calculates the associated level of each member by this technique:
1) Create as many line items as levels of hierarchy, plus one technical line item.
2) Configure the settings in the blueprint of the line items of this filtering module, per this example and table:
Summary method Setting Ratio
Technical line item*
Level or L4 (lowest level)
L3 / Technical
L2 / Technical
L1 / Technical
L1 / Technical
When applying these settings, the filtering module looks like this:
*Note the Technical line item Summary method is using Formula. Alternatively, The Minimum Summary Method can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank.
The filtering module with Summary method applied results:
Use the line item at the lowest level—Level (or L4) (LOWEST)—as the basis of filters or calculations.
Applying a Filter on Specific Levels in Case of Synchronization
When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.
In the example, we apply a filter which matches any of the level 4 and 1:
The following filtered dashboard result is achieved by using the composite hierarchy as a page selector:
You may have heard about a model called a data hub, but perhaps you aren’t confident that you understand the fundamentals, primary functions, or considerations when architecting one. There are three main advantages to incorporating a data hub:
Single source of truth: Stores all transactional data from the source system.
Data validations: Ensures all data is correct and valid before the data gets to the spoke model(s).
Performance: It is always faster to load data from a model rather than a file.
Additionally, the administrator can ensure the correct granularity of data in the spoke model(s) when using a data hub. For example, the source system may only contain transactional data at the daily level, but the planners may need the data aggregated to the month. The data hub can summarize the data and export only the data needed.
The following information is designed to further define a data hub and support you in your journey of building your own.
Table of Contents
Definition of the Data Hub
First, we need to define what a data hub is. This can be split into four sections:
Use cases: The data hub should be the first model built, whether you have a single use or multiple use cases. The data should be automatically refreshed on a schedule, whether it is nightly, weekly, monthly, etc., from the source system—often an Enterprise Data Warehouse (EDW). All modules and views that create hierarchies or lists should be stored in the data hub, which enables your models in having one version of truth, as well as reducing the duplication of data.
Model connectivity: Anaplan Connect , one of our 3 rd party vendors (Informatica Cloud, Dell Boomi, Mulesoft, or SnapLogic), or our REST API can be used to automate the loading of data to the data hub from the source system, as well as transferring data from the data hub to the spoke model(s). Additionally, transitional data should not be loaded directly into the spoke module, especially if there is a large volume of data.
Functions: Often, simple ETL (Extract, Transform, and Load) functions can be utilized within your data hub to transform the data for the spoke model(s). This is helpful when consolidating data from multiple sources where you have different “codes” and need a mapping module to ensure the correct data gets mapped correctly.
Team: The management of the data hub should have a designated team of experts who understand what data is stored in the data hub (to ensure duplication doesn’t happen), as well as the how and when the data gets loaded.
Anaplan Architecture with a Data Hub
There are several ways your Anaplan architecture could look, depending on the number of workspaces you currently have and the type of security your company requires. The following are illustrations of common architectures.
Master Hub Model: Across Workspaces
The most common, and recommended, architecture is when the data hub is in its own workspace. Not only does this have the advantage of not interfering with the other models, but it also adds an additional security layer, with a segregation of duties. In this view, the Anaplan Workspace Admin(s) can limit the access to the data hub workspace to only the people who require it.
Master Hub Model: Within a Workspace
The simplest depiction is where your data hub is within the same workspace as your spoke models. While this can be accomplished, it is not best practice as there is no segregation of duties and there is a possibility, upon heavy loads from the source system, of performance issues. Additionally, when adding users, the Anaplan Workspace Administrator (Admin) would need to ensure users don’t have access to the data hub, as well as any users of the data hub not having access to the spoke models
Multiple Data Hubs
Finally, the data hub doesn’t necessarily have to be the only model in the workspace. You can have additional data hubs, if needed.
Factors to Consider When Implementing a Data Hub
There are six main elements to think about when architecting a data hub:
Exporting data to spoke model(s).
One of the cornerstones of The Anaplan Way is data (process, model, and deployment being the others), which is critical to all implementations. You will need to know what data is needed for a certain use case. Consider the following, common, data questions that need to be answered in order to be successful:
What granularity of the data is needed?
How much history is needed? How much history do you have?
Does the source system only have transactional data, but the use case needs the data at the month level? Can the source system do the aggregation for you?
After all data questions have been answered, shift your focus to the source system and consider the following:
Consider the source system. Where is the data coming from? What is the source system, and is it a trusted environment? Is it Excel? Typically, you should stay away from Excel as the “source” because Excel cannot be audited.
Define the data source owners. Who has access to this data? Who is preparing it? Are they part of the project? These are often-overlooked questions that are critical to success. Ideally, the data source owners need to be part of the project from the start to understand the file specifications and prepare the initial load of the data, as well as towards the end of the project to do a final load of the data.
Define file specifications. How many files will be needed? Typically, you will need master data, as well as transactional data. Instead of having one file with all of this data, determine if the data can be split between different files (one for transactional, one for the unique members of the master data). It will be better for Anaplan (for performance reasons) to split these to reduce warnings during the data load process.
Analyze the data. Understand what makes each record unique (date/period and transactional amounts should not be part of this), and make sure the data owners don’t give you everything (Select * From Employee) when you only need five columns. Remember, it is better to ask for additional columns midway through the project than getting all columns in the beginning and only using a select amount.
Consider custom codes in the source system. Find more on this in the transactional lists section. This is a great trick for transactional data. After you have analyzed the data to understand what makes each record/row unique, concatenate the “codes” of the metadata into one transactional code, but remember, you will need to be under the 60-character threshold.
Define the schedule. When is the data available? Is the data on a certain schedule? What is the schedule required with this use case?
Determine the ETL medium to be used. Will Anaplan Connect be sufficient, will one of our 3 rd parties be used, or will a more custom application be needed, such as REST API? Does your company already have this experience inhouse, or will training be required? These will need to be factored into all data stories.
Usually, the largest lists are those containing transactional data. There can be millions of transactional ID’s with several list properties defined. First, properties should not be defined on a transactional list (or any list, except for Display Name, as they do get accounted for in the workspace memory). Secondly, instead of loading metadata to list properties (Cost Center and Account as properties), try to figure out a way to incorporate them into the code. If the transactional data is defining a transactional amount at the intersection of Cost Center and Account for a particular month, attempt to use the code of the Cost Center and the code of the Account concatenated together (0100_57000). Not only will this decrease your list size, but it will also create a healthier model.
In the below example, the model builder did not create a custom code, but rather used a combination of properties to make the record unique, which included the date/period, as well as the transactional amount. Notice the original number of records vs. the number of records after a custom code was created.
By incorporating the date/time period, as well as the transactional amount, it inflated the list size exponentially based on the number of years that were loaded. Doing this not only caused the model to be bigger, but also caused poor model opening performance. See the Appendix for a simple worked example to explain further.
Learn more about sparsity in the two-part series The Truth about Sparsity: Part 1 and The Truth About Sparsity: Part 2 .
Similar to transactional lists, flat lists are not part of a hierarchy and are a series of records grouped in a list, like Products, Companies, Cost Centers, or Employees. These are your “legends” or “anchor” for all metadata about this unique record. Again, the only property that should be defined is a Display Name, if needed. It is best practice, from a model builders’ perspective, to suffix the name with “Flat” or “- Flat”. This helps identify whether the list is part of a hierarchy or flat list (Employee – Flat, Cost Center – Flat, Product – Flat). These lists can be used for data validation, which will be described later in this article.
Ideally, you should have three types of modules in the data hub:
Transactional: A Transactional module will store the transactional data by the time series, whether that be by day, week, month, quarter, or year. The only data, or line items, should be transactional data. No other line items should be defined. Additionally, to keep the size down, make sure the summaries on the line items are turned off, or None, as there is no reason to sum the data within the module.
System: System (SYS) modules, or the “S” in DISCO , do not have time associated with them and should only be dimensionalized by the same list (Employee Flat, Cost Center Flat, Product Flat). These modules store the metadata or attributes about the list item that doesn’t change over time, for example the employee’s start date. Another example of a SYS module would be any kind of mapping that is required, whether it be SYS Time Filter module or a mapping from one source system to another.
Export modules: If the data from the source system is being loaded at a lower granularity than needed in the spoke model(s), export modules can aggregate the data to the specified need (month, quarter, or year level), which will lead to more efficient data load performance to the spoke model(s). Additionally, it is better to only load the granularity of data needed instead of loading all data to the spoke model, but only using a portion of it.
Loading Data vs. Using Formula’s in SYS Modules
If you can devise a custom code where all of the attributes of the data are accounted for, you can greatly increase the performance of your data load, especially on very large data volumes. It is actually faster to use formulas to derive the data from the custom code than it is to load the data. Why? A couple of reasons. First, when data is loaded, the load is triggering the change log, and every change is being recorded in the model history . Second, loading data to another module is an additional action. If you didn’t need that action, you would save processing time.
In the example below, the exact same data was loaded four different ways:
Import Properties to a List: A list was created with all attributes, including the transactional data, and was loaded to list properties (not best practice and against DISCO).
Import to List, Attribute, and Trans: A list was created, the transactional data was loaded to a transactional module, and all of the attributes were loaded to a SYS Attribute module.
Import to List, Trans, Calculate Attribute: A list was created, the transactional data was loaded to a transactional module, but the SYS Attribute model was calculated using two different methods:
One Line Item: Using FINDITEM() with several functions parsing out the information from within the FINDITEM(). For example, FINDITEM(Cost Center, RIGHT(LEFT(Trans Details.Code, '2nd Group’), 3)).
Multiple Line Items: Parsing of the member spread across multiple line items and using FINDITEM() with only the list and code as the parameter. First, you do the parsing to get the correct piece of the code (one line item), and then the FINDITEM() of that code (2 nd line item).
Notice, the best performing data load was the last one, Import to List, Trans, Calculate Attribute (multiple line items), where the parsing out of the data was spread over multiple line items. This is due to the fact that the data load was able to take advantage of Anaplan’s multithreading capabilities. The worst performing data load occurred when data was loaded to the Attribute module because, due to the sheer size of the data, a save had to be performed.
Exporting to Spoke Models
One of the most important concepts to remember when exporting data is to use a view from a module. Lists should not be exported because you lose control over what you export. It is either all or nothing. By using views, you can employ a filter (should always be a Boolean) to render exactly which data needs to be exported. If you need more than one filter, combine both into one line item and use that line as the filter. You will have much better performance if you are only using one Boolean line item as a filter vs. having multiple filters defined.
Another important concept to remember is to only export detailed information, as there is no reason to export parent information (quarter, year, etc.). Not only will you get warnings when exporting parent information, but the performance of the export will decrease because the system will have to create a debug log. The goal is to make sure a debug log is not created, all green checks, so if there ever is an issue, you will know it truly is an issue that needs attention.
Line items in the data hub formatted as text should not be exported as text, but actually as list formatted line items in the spoke model (text->list formatted line item). The goal is to reduce the number of text formatted line items in the spoke model.
Some say they need to do validation in the spoke model, therefore they need to import the data as text. Actually, this is false, because the validation should have already been done in the data hub, so there should be no need to do the validation again.
Lastly, you should think about what really needs to be exported. Do you really need to export historical data that hasn’t been changed? Instead, just export the newly loaded data, or delta data. This can be accomplished by using one of two methods:
From the source system, request IT to only send the updated information, not the full load every time. Additionally, request IT to create a column in the source file with a hardcoded value of “TRUE.” This will tell Anaplan which row is new or has been updated and can be used as a filter for an export. Just know, before the import of the source data gets loaded, make sure the first action within the process clears out the previous true records (set this up via a view using a filter where the view only shows members with a value of true).
Utilize the current period function to only export the current period data. In the SYS Time Filter module, create a line item named Current Period with the formula CURRENTPERIODSTART(). In the export views, filter the data on this line item.
Tips and Tricks
A few of tips and tricks to be aware of include the following:
Hierarchies should not be in the data hub.
Analytical modules should not be in the data hub.
Do not delete and reload lists.
Data Validations Model
Why should hierarchies not be in the data hub? To answer that question, you need to understand why hierarchies are used in the first place. Essentially, hierarchies are only needed to aggregate data for analytical purposes, and since users will not normally login to the data hub, the lists essentially take up space. With that said, it is perfectly okay to create the hierarchies for testing purposes to ensure your actions from the meta modules are building the hierarchies correctly, but as soon as the actions are working correctly and have been verified, you can remove the list structures from the data hub. A case can be made that certain implementations may need the hierarchies created in the data hub for validation purposes of several sources. If this is the case in your implementation, just be sure to only use the hierarchies for validation purposes.
In addition to the above, there are two more reasons to not have hierarchies built in the data hub—cluttered data, and spoke models that pull data from the lists.
Data hubs need to be clean and clutter free to ensure optimal performance, which also makes it easier for the administrators to understand exactly what data is stored in the data hub. Additionally, when you have lists—especially hierarchical lists—spoke model builders will sometimes build their lists from the lists within the data hub instead of from a view. It is best practice to always build lists from views from within a module so the action can benefit from filters (there are no filters when importing from lists).
Analytical modules should not be in the data hub since end users don’t normally access the data hub. There really isn’t a reason to have products by versions by time in the data hub, that belongs to the spoke model. Remember, the data hub should only be used to store data from the source system(s).
Within your nightly data load process, do not delete and reload data, including the list structures. If you have a proper code, you shouldn’t need to do this. Additionally, not only does this impact the overall performance of the process (adding an additional action to delete the list, which then deletes all data associated with that list), but the process is essentially filling up the change log with the exact same data that it had before the delete. When a certain threshold is surpassed, the model will require a save, thus taking up even more time. Ultimately, you are forcing the model to re-aggregate all of the data, instead of just the new data.
Lastly, if you know you will have to do a lot of transformations on your data (consolidating multiple source systems or your data is not clean), think about creating a Data Validations model. This model’s sole purpose would be to clean the data and then feed the data to the data hub, thus keeping the transformations to a minimum in the data hub as well as keeping the data hub clean.
Use Case: Transaction Data is by Store and SKU and Month
The code for the Transaction list is a three-part code Store_SKU_Month
Attributes for Store, SKU and Month are imported as Text and matched against the Store list, SKU list and Time period respectively
An additional line item is needed for the Store and SKU code (for export).
This is the screenshot of the bad way:
Notice the repetition of the attributes. STR07 and SKU031 are repeated each month.
Two data files
Unique combinations of Store and SKU (two-part code)
Store SKU code by month for the quantity.
The transaction details are stored in a module dimensioned by Transactions
The Store and SKU attributes are calculated using the “_” delimiter
The quantity is stored in a module dimensioned Transactions and by month
The additional line item is needed for the Store and SKU code (for export). This is a subsidiary view in the module as it is not dimensionalized by Time.
These are the screenshots of the good way:
Below lists out the breakdown of the model in terms of List size, Line items and the associated member usage of the various structures. The main reasons for the improvement are because lists themselves account for approximately 500b for each member and also there is repetition of the attributes per “month” in the transaction data (as mentioned above).
Hopefully, this article has shed some light on data hubs, how they should be used, and what you can do to ensure they perform at their peak level. Remember, analyze the data to understand what makes the row unique and use that as the code. Every list should have a code—every list!
Little and Often
Would you spend weeks on your budget submission spreadsheet or your college thesis without once saving it?
The same should apply to making developments and setting revision tags. Anaplan recommends that during the development cycle, you set revision tags at least once per day. We also advise testing the revision tags against a dummy model if possible.
The recommended procedure is as follows:
After a successful sync to your production model, create a dummy model using the ‘Create from Revision’ feature. This will create a small test model with no production list items.
At the end of each day (as a minimum), set a revision tag and attempt to synchronize the test model to this revision tag. The whole process should only take a couple of minutes.
Repeat step 2 until you are ready to promote the changes to your production model.
Why Do We Recommend This?
There are a very small number of cases where combinations of structural changes cause a synchronization error (99 percent of synchronizations are successful). The Anaplan team is actively working to provide a resolution within the product, but in most cases, splitting changes between revision tags allows the synchronization to complete. In order to understand the issue when a synchronization fails, our support team needs to analyze the structural changes between the revisions.
Setting revision tags frequently provides the following benefits:
The number of changes between revisions is reduced, resulting in easier and faster issue diagnosis.
It provides an early warning of any problems so that someone can investigate them before they become critical.
The last successful revision tag allows you to promote some, if not most, of the changes if appropriate.
In some cases, a synchronization may fail initially, but when applying the changes in sequence the synchronization completes. Using the example from above:
Synchronizations to the test model for R1, R2, and R3 were all successful, but R3 fails when trying to synchronize to production.
Since the test model successfully synchronized from R2 and then R3, you can repeat this process for the production model.
The new comparison report provides clear visibility of the changes between revision tags.
PLANS is the new standard for Anaplan modeling—“the way we model.” This covers more than just the formulas and includes and evolves existing best practices around user experience and data hubs. It is a set of rules on the structure and detailed design of Anaplan models. This set of rules will provide both a clear route to good model design for the individual Anaplanner and common guidance on which Anaplanners and reviewers can rely when passing models amongst themselves.
In defining the standard, everything we do will consider or be based around:
Performance – Use the correct structures and formula to optimize the Hyperblock
Logical – Build the models and formula more logically – See D.I.S.C.O. below
Auditable – Break up the formula for better understanding, performance, and maintainability
Necessary – Don’t duplicate expressions. Store and calculate data and attributes once and reference them many times. Don't have calculations on more dimensions than needed
Sustainable – Build with the future in mind, thinking about process cycles and updates
The standards will be based around three axes:
Performance - How do the structures and formula impact the performance of the system?
Usability/Auditability - Is the user able to understand how to interact with the functionality?
Sustainability - Can the solution be easily maintained by model builders and support?
We will define the techniques to use that balance on the three areas to ensure the optimal design of Anaplan models and architecture.
As part of model and module design, we recommend categorizing modules as follows:
Data – Data hubs, transactional modules, source data; reference everywhere
Inputs – Design for user entry, minimize the mix of calculations and outputs
System – Time management, filters, list attributes modules, mappings, etc.; reference everywhere
Calculations – Optimize for performance (turn summaries off, combine structures)
Outputs - Reporting modules, minimize data flow out
Why build this way?
Fewer repeated calculations
Optimized structures and formulas
Data and calculations reside in logical places
Model data flows can be easily understood
Model structure can be easily understood
Simplified formula (no need for complex expressions)
Formulas and structures are not repeated
Data is stored and calculated once, referenced many times, leading to efficient calculations
Models can be adapted and maintained more easily
Expansion and scaling simplified
Formula Optimization in Anaplan
Formula Structure for Performance
The Truth About Sparsity: Part 1
The Truth About Sparsity: Part 2
Data Hubs: Purpose and Peak Performance
To Version or Not to Version?
Line Item Subsets Demystified
Best Practices for Module Design
Data Hubs: Purpose and Peak Performance
Formula Structure for Performance
Reduce Calculations for Better Performance
Formula Optimization in Anaplan
Dynamic Cell Access Tips and Tricks
Dynamic Cell Access - Learning App
Personal Dashboards Tips and Tricks
Time Range Application
Ask Me Anything (AMA) sessions
The Planual Rises
The process of designing a model will help you:
Understand the customer’s problem more completely
Bring to light any incorrect assumptions you may have made, allowing for correction before building begins
Provide the big picture view for building
This process includes the following steps:
Let’s take a closer look at each of the steps, why it is important to the overall process, and the results of completing each step.
Note: This document uses the terminology end-user experience, pages, and boards when referring to dashboards.
For more information, check out Training on the New UX.
Understand the Requirements and the Customer’s Technical Ecosystem
Purpose: To solve a problem, you must completely understand the current situation. Performing this step provides this information and the first steps toward the solution.
Process: When you begin a project, you gather information and requirements using tools that include:
Definition of the project scope and project objectives/high-level requirements.
Goal of the project—big picture view of what needs to be accomplished.
IT ecosystem map.
Which systems will provide data to the model and which systems will receive data from the model? What is the Anaplan piece of the ecosystem? What other Anaplan models will this model connect with?
Current business process definition.
Remember that if the current process isn’t working, it needs to be fixed before design can start.
The process steps should be clearly defined for any changes to an existing process or a new process. The organization should plan communication and education programs to inform and train the end-users on the process changes.
Business logic definition.
What key pieces of business logic will be included in the model?
Distributed model needed?
High user concurrency.
Security where the need is a separate model.
Regional differences that are better handled by a separate model.
Model size and end-user and roles in the process. Does the input or start of a second process rely on the output of another?
Do the model outputs need to be real-time? Reporting and process steps should be clearly defined.
Is the organization using ALM to effectively manage development, testing, deployment, and maintenance of applications?
This functionality requires a premium subscription or above.
These have been written by the client—more specifically, by the subject matter experts who will be using the model.
Understand the goal of the project.
The organizational structure and reporting relationships (hierarchies).
Where data is coming from and how much data clean-up might be needed.
What data is organized into categories (for example, product families) or what data relationships exist that need to be carried through to the model (for example, salespeople only sell certain products).
What lists currently exist and where are they are housed.
Which systems (including other Anaplan models) the model will either import from or export to.
What security measures are expected.
What time and version settings are needed.
Plan the End-User Experience for Each Role
Purpose: This is an important step in the model design process. Ultimately, the information or data that the end-user needs to make a good business decision is what drives the entire structure of the model.
Process: On some projects, you may be working with a project manager or a business consultant to flesh out the business process for the user. You may have user stories, or it may be that you are working on design a bit earlier in the process and the user stories haven’t been written. In any case, identify the user roles, the business process that will be completed in Anaplan, and create a high-level design of the user experience. Verify the design with the users to ensure that you have the correct starting point for the next step.
Front to back design has been identified as the preferred method for model design. This approach puts the focus on the end-user experience. We want that experience to align with the process, so users can easily adapt to the model. During this step, focus on:
User Roles – who are the users? You may want to create a RACI matrix to identify who is responsible, who is accountable, who needs to be consulted, and who must be kept Informed.
Identify the business process that will be done using Anaplan.
Review and document the process for each role, focusing on the main steps. You can document the process in any way that works for you. Here is a step-by-step process for how to document the business process:
Gather information from the end-users. For each different role, ask these questions:
What is the start of the process?
What is the result or output of the process?
What are the process inputs (what data are needed) and where do they come from?
What are the actions the user takes? Write these using a verb and an object—for example, approve the request, enter sales amount, add new hire, etc. No need to organize during this step. Use post-it notes to capture them. Keep this discussion focused on the process as it is completed most of the time.
Use this information to document the process, putting the actions in the correct order. You can use any process mapping software or PowerPoint. If there are multiple roles, use swim lanes to identify the roles.
Create a timeline to map the user roles to see if there are any overlaps or dependencies that could cause concurrency issues. These are often missed because concurrency is often thought about by role, not across the whole user base.
Verify the process with the end-users and Subject Matter Experts (SME) and make any corrections.
Ask the end-users to sketch out what they would like to see on their pages and boards. Many processes will require multiple pages and boards. Keep this at a high level. Some questions to guide this discussion include:
What data do you need to see?
What types of charts or graphs might help you better understand the data?
What actions do you need to take?
What decisions do you make?
When making decisions, is there data that you need to compare?
List of user roles.
Process steps for each user role.
Timeline mapping of user roles.
High-level page and board designs for each user role.
Use the User Experience Design From the Previous Step to Determine What Output Modules are Needed in the Model.
Purpose: Think D.I.S.C.O. The Output modules are needed to support the end-user experience or export to another system. This is what should guide your design – all the Input modules and Calculation modules are added with the purpose of providing these output modules with the information needed for the pages and boards or export.
Process: Some questions to help you think through the definition of your Output modules:
What information (and in what format) does the user need to make a decision?
If the end-user is using pages and boards for reporting purposes, what information is required?
Are there modules that will serve to move data to another system? What data and what format is necessary?
List of outputs and desired format needed for each page or board.
Identification of how the data should be categorized on the page or board, or what hierarchies are needed. For example, employees are organized into departments, departments into business units, etc.
Identify What Data Is Available and Needed to Support the Output Modules. Determine the Data Modules the Model Requires.
Purpose: Identify what data the model requires and where it is held. Determine the hierarchies or structures needed in the model.
Process: Organize the data using Data modules, using each module to hold similar types of data, for example, employee, product, or sales data.
Lists needed in the model.
Hierarchies needed in the model.
Data and where it is coming from.
Data modules identified.
Identify What Inputs From End-Users Are Needed
Purpose: Planners want to be able to see how changes will affect overall results. By entering new data, planners can try different scenarios and make decisions based on data. What if manufacturing costs go up? What effect do higher shipping costs have on the bottom line?
Process: Look at the end-user experience designs from step 2 and identify the data types that end-users will be able to add or change.
List of pages or boards where data input is required.
Input modules identified. These modules should not include a lot of calculations.
Determine What Calculations Are Needed to Transform Source Data and Inputs to the Data Needed for Outputs. Identify the Calculation and Systems Modules Needed.
Purpose: Transform data from the Data and Input modules to the data needed for Output modules, using business rules, logic, and formulas.
Process: Some questions to use to think through what calculations are needed:
What is the output?
How should the output data be formatted?
What dimensionality is needed?
What data is available?
Is the source data in the same dimensionality as the output, or is it different?
If the dimensionality is different, what intermediate steps are needed? What Calculation or System modules are needed?
Will the source data need to be converted to a different data type?
Use Anapedia to locate functions. The reverse lookup page provides a list of behaviors and descriptions in plain language of many Anaplan functions. You can also use the search function in Community.
As you are thinking about the calculations needed, pay attention to functions that are used in multiple calculations. Examples might include calculations for pulling data from the current period, the parent in a product hierarchy, or pulling data from a forecast version. These can be included in Systems modules and entered once and referenced many times. Systems modules can also hold lists and list item attributes.
Also, think about how to organize the calculations into Calculation modules. Keep in mind the different types of data (for example, Revenue, Employee, Other Costs) used in the model and keep the calculations for each type together.
List of Calculation and System modules.
List of possible calculations (to be tested when model building).
Using Lucid Charts (Or Some Other Tool), Create Your Model Schema
Purpose: Provides a graphic representation of the model design that is used for communicating with the model-building team. The model schema is required documentation for the project.
Process: Designing your model using a schema means that you must think through all the information you have about the current situation, how it all ties together, and how the model provides what is needed to meet the needs of the end-users. Begin by sketching your ideas on a whiteboard. Whiteboards are excellent collaboration tools and model design is a collaborative process. Be sure to:
Identify the Input, Outputs, and Calculation modules needed for each functional area.
Identify the Data and Systems modules needed for each functional area.
Show the data flow between the functional areas.
Identify time and versions where appropriate.
Transfer the final version to an electronic format.
A model schema that provides the big picture view of the solution. It should include imports from other systems or flat files, the modules, or functional areas that are needed to take the data from the current state to what is needed to support the end-user experience that was identified in step two. Time and versions should be noted where required. Include the lists that will be used in the functional areas/modules.
Your schema will be used to communicate your design to the customer, model builders, and others. While you do not need to include calculations and business logic in the schema, it is important that you understand the state of the data going into a module, the changes or calculations that are performed in the module and the state of the data leaving the module, so that you can effectively explain the schema to others.
Final Design Check
Purpose: A final check of your model to ensure that it is well-designed. We recommend that you have a peer check over your model and provide feedback. It is easy to fall into using the functions you are familiar with, which may or may not be the best solution. You will build your model design skills by participating in a Model Design Check-in, which allows you to talk through the tougher parts of the design with a peer. This check-in is included as part of The Anaplan Way process.
How-to: When your schema is complete, give it a final check to ensure:
You have used DISCO to organize your modules and each module has a defined purpose. Your schema has been kept simple.
“Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius — and a lot of courage to move in the opposite direction.” ― Ernst F. Schumacher
“Simple is better than complex.” –Tim Peters
The model aligns with the manifesto.
The business process has been defined and works well within the model.
As an ad planner, sales representative, or merchandise planner, you might be interested in planning your sales or invoices using tier pricing. This solution will address a user-story that allows you to set the prices by tier range and automatically lookup the right price based on the units ordered. This solution was first introduced by David Smith in his Community Site response to solve a customer satisfaction score and inspired by a question asked by Vignesh Mohan in this post.
Anaplan Connect is a command-line client to the Anaplan cloud-based planning environment and is a java-based utility that is able to perform a variety of commands, such as uploading and downloading data files, executing JDBC SQL queries (for importing & exporting data from Anaplan), and running Anaplan actions and processes. To enhance the deployment of Anaplan Connect, it is important to be able to integrate the trapping of error conditions, enable the ability to retry the Anaplan Connect operation, and integrate email notifications. This article provides best practices on how to incorporate these capabilities.
This article leverages the standard Windows command line batch script and documents the various components and syntax of the script. In summary, the script has the following main components:
Set variable values such as exit codes, Anaplan Connect login parameters, and operations and email parameters
Run commands prior to running Anaplan Connect commands
Main loop block for multiple retries
Establish a log file based upon the current date and loop number
Run the native Anaplan Connect commands
Search for string criteria to trap error conditions
Branching logic based upon the discovery of any trapped error conditions
Send email success or failure notification of Anaplan Connect run status
Logic to determine if a retry is required
End main loop block
Run commands post to running Anaplan Connect commands
Exit the script
Section #1: Setting Script Variables
The following section of the script establishes and sets variables that are used in the script. The first three lines perform the following actions:
Clears the screen
Sets the default to echo all commands
Indicates to the operating system that variable values are strictly local to the the script
The variables used in the script are as follows:
ERRNO – Sets the exit code to 0 unless set to 1 after multiple failed reties
COUNT – Counter variable used for looping multiple retries
RETRY_COUNT – Counter variable to store the max retry count (note: the /a switch indicates indicates a numeric value)
AnaplanUser – Anaplan login credentials in the format as indicated in the example
WorkspaceId – Anaplan numerical or named Workspace ID
ModelId – Anaplan numerical or named Model ID
Operation – A combination of Anaplan Connect commands. It should be noted that a ^ can be used to enhance readability by indicating that the current command continues on the next line
Domain – Email base domain. Typically, in the format of company.com
Smtp – Email SMTP server
User – Email SMTP server User ID
Pass – Email SMTP server password
To – Target email address(es). To increase the email distribution, simply add additional -t and the email addresses as in the example.
From – From email address
Subject – Email subject line. Note that this is dynamically set later in the script.
REM **** SECTION #1 - SET VARIABLE VALUES ****
set /a ERRNO=0
set /a COUNT=0
set /a RETRY_COUNT=2
REM Set Anaplan Connect Variables
set AnaplanUser="<<Anaplan UserID>>:<<Anaplan UserPW>>"
set WorkspaceId="<<put your WS ID here>>"
set ModelId="<<put your Model ID here>>"
set Operation=-import "My File" -execute ^
-output ".\My Errors.txt"
REM Set Email variables
set To=-t "email@example.com" -t "firstname.lastname@example.org"
set Subject="Anaplan Connect Status"
REM Set other types of variables such as file path names to be used in the Anaplan Connect "Operation" command
Section #2: Pre Custom Batch Commands
The following section allows custom batch commands to be added, such as running various batch operations like copy and renaming files or running stored procedures via a relational database command line interface.
REM **** SECTION #2 - PRE ANAPLAN CONNECT COMMANDS ***
REM Use this section to perform standard batch commands or operations prior to running Anaplan Connect
Section #3: Start of Main Loop Block / Anaplan Connect Commands
The following section of the script is the start of the main loop block as indicated by the :START. The individual components breakdown as follows:
Dynamically set the name of the log file in the following date format and indicates the current loop number: 2016-16-06-ANAPLAN-LOG-RUN-0.TXT
Delete prior log and error files
Native out-of-the-box Anaplan Connect script with the addition of outputting the Anaplan Connect run session to the dynamic log file as highlighted here: cmd /C %Command% > .\%LogFile%
REM **** SECTION #3 - ANAPLAN CONNECT COMMANDS ***
REM Dynamically set logfile name based upon current date and retry count.
REM Delete prior log and error files
REM Out-of-the-box Anaplan Connect code with the exception of sending output to a log file
setlocal enableextensions enabledelayedexpansion || exit /b 1
REM Change the directory to the batch file's drive, then change to its folder
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% -workspace %WorkspaceId% -model %ModelId% %Operation%
cmd /C %Command% > .\%LogFile%
Section #4: Set Search Criteria
The following section of the script enables trapping of error conditions that may occur with running the Anaplan Connect script. The methodology relies upon searching for certain strings in the log file after the AC commands execute. The batch command findstr can search for certain string patterns based upon literal or regular expressions and echo any matched records to the file AC_API.ERR. The existence of this file is then used to trap if an error has been caught. In the example below, two different patterns are searched in the log file.
The output file AC_API.ERR is always produced even if there is no matching string. When there is no matching string, the file size will be an empty 0K file. Since the existence of the file determines if an error condition was trapped, it is imperative that any 0K files are removed, which is the function of the final line in the example below.
REM **** SECTION #4 - SET SEARCH CRITERIA - REPEAT @FINDSTR COMMAND AS MANY TIMES AS NEEDED ***
@findstr /c:"The file" .\%LogFile% > .\AC_API.ERR
@findstr /c:"Anaplan API" .\%LogFile% >> .\AC_API.ERR
REM Remove any 0K files produced by previous findstr commands
@for /r %%f in (*) do if %%~zf==0 del "%%f"
Section #5: Trap Error Conditions
In the next section, logic is incorporated into the script to trap errors that might have occurred when executing the Anaplan Connect commands. The branching logic relies upon the existence of the AC_API.ERR file. If it exists, then the contents of the AC_API.ERR file are redirected to a secondary file called BAT_STAT.TXT and the email subject line is updated to indicate that an error occurred. If the file AC_API.ERR does not exist, then the contents of the Anaplan Connect log file is redirected to BAT_STAT.TXT and the email subject line is updated to indicate a successful run. Later in the script, the file BAT_STAT.TXT becomes the body of the email alert.
REM **** SECTION #5 - TRAP ERROR CONDITIONS ***
REM If the file AC_API.ERR exists then echo errors to the primary BAT_STAT log file
REM Else echo the log file to the primary BAT_STAT log file
@if exist .\AC_API.ERR (
@echo . >> .\BAT_STAT.TXT
@echo *** ANAPLAN CONNECT ERROR OCCURED *** >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
type .\AC_API.ERR >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
set Subject="ANAPLAN CONNECT ERROR OCCURED"
) else (
@echo . >> .\BAT_STAT.TXT
@echo *** ALL OPERATIONS COMPLETED SUCCESSFULLY *** >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
type .\%LogFile% >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
set Subject="ANAPLAN LOADED SUCCESSFULLY"
Section #6: Send Email
In this section of the script, a success or failure email notification email will be sent. The parameters for sending are all set in the variable section of the script.
REM **** SECTION #6 - SEND EMAIL VIA MAILSEND ***
@mailsend -domain %Domain% ^
-smtp %Smtp% ^
-auth -user %User% ^
-pass %Pass% ^
-f %From% ^
-sub %Subject% ^
Note: Sending email via SMTP requires the use of a free and simple Windows program known as MailSend. The latest release is available here: https://github.com/muquit/mailsend/releases/ . Once downloaded, unpack the .zip file, rename the file to mailsend.exe and place the executable in the same directory where the Anaplan Connect batch script is located.
Section #7: Determine if a Retry is Required
This is one of the final sections of the script that will determine if the Anaplan Connect commands need to be retried. Nested IF statements are typically frowned upon but are required here given the limited capabilities of the Windows batch language. The first IF test determines if the file AC_API.ERR exists. If this file does exist, then the logic drops in and tests if the current value of COUNT is less than the RETRY_COUNT. If the condition is true, then the COUNT gets incremented and the batch returns to the :START location (Section #3) to repeat the Anaplan Connect commands. If the condition of the nested IF is false, then the batch goes to the end of the script to exit with an exit code of 1.
REM **** SECTION #7 - DETERMINE IF A RETRY IS REQUIRED ***
@if exist .\AC_API.ERR (
@if %COUNT% lss %RETRY_COUNT% (
@set /a COUNT+=1
) else (
set /a ERRNO=1
) else (
set /a ERRNO=0
Section #8: Post Custom Batch Commands
The following section allows custom batch commands to be added, such as running various batch operations like copy and renaming files, or running stored procedures via a relational database command line interface. Additionally, this would be the location to add functionality to bulk insert flat file data exported from Anaplan into a relational target via tools such as Oracle SQL Loader (SQLLDR) or Microsoft SQL Server Bulk Copy (BCP).
REM **** SECTION #8 - POST ANAPLAN CONNECT COMMANDS ***
REM Use this section to perform standard batch commands or operations after running Anaplan Connect commands
exit /b %ERRNO%
Sample Email Notifications
The following are sample emails sent by the batch script, which are based upon the sample script in this document. Note how the needed content from the log files is piped directly into the body of the email.