Sort by:
Overview The Anaplan Optimizer aids business planning and decision making by solving complex problems involving millions of combinations quickly to provide a feasible solution. Optimization provides a solution for selected variables within your Anaplan model that matches your objective based on your defined constraints. The Anaplan model must be structured and formatted to enable Optimizer to produce the correct solution. You are welcome to read through the materials and watch the videos on this page, but Optimizer is a premium service offered by Anaplan (Contact your Account Executive if you don't see Optimizer as an action on the settings tab). This means that you will not be able to actually do the training exercises until the feature is turned on in your system. Training The training involves an exercise along with documentation and videos to help you complete it. The goal of the exercise is to setup the optimization exercise for two use cases; network optimization and production optimization. To assist you in this process we have created an optimization exercise guide document which will walk you through each of the steps. To further help we have created three videos you can reference: An exercise walk-through A demo of each use case A demo of setting up dynamic time Follow the order of the items listed below to assist with understanding how Anaplan's optimization process works: Watch the use case video which demos the Optimizer functionality in Anaplan Watch the exercise walkthrough video Review documentation about how Optimizer works within Anaplan Attempt the Optimizer exercise Download the exercise walkthrough document Download the Optimizer model into your workspace How to configure Dynamic Time within Optimizer Download the Dynamic Time document Watch the Dynamic Time video Attempt Network Optimization exercise Attempt Production Optimization exercise
View full article
As a model builder you have to define line items formats over and over. Using a text expander/snippet tool, you can speed up the configuration of modules. When you add a new Line Item, Anaplan sets it by default as a Number (Min Significant Digits : 4, Thousands Separator : Comma, Zero Format : Zero, etc.). You usually change it once and copy it over to other line items in the module. Snippet tools can store format definition of generic formats (numbers, text, boolean, or no data) and by a simple shortcut, paste it in the format of the desired line items.  Below is an example of a number format line item with no decimal and hyphens instead of zeros. On my Mac, I press Option + X, I type "Num..." and get a list of all Number formats I pre-defined. I press Enter to paste it. It also works if several line items were selected. The value stored for this Number format is : {"minimumSignificantDigits":-1,"decimalPlaces":0,"decimalSeparator":"FULL_STOP","groupingSeparator":"COMMA","negativeNumberNotation":"MINUS_SIGN","unitsType":"NONE","unitsDisplayType":"NONE","currencyCode":null,"customUnits":null,"zeroFormat":"HYPHEN","comparisonIncrease":"GOOD","dataType":"NUMBER"} Here is the result of a text format snippet. {"textType":"GENERAL","dataType":"TEXT"} Or a Heading line item (No Data, Style : Heading 1). ---- false {"dataType":"NONE"} - Year Model Calendar All false false {"summaryMethod":"NONE","timeSummaryMethod":"NONE","timeSummarySameAsMainSummary":true,"ratioNumeratorIdentifier":"","ratioDenominatorIdentifier":""} All Versions true false Heading1 - - - 0  This simple trick can save you a lot of clicks. While we are unable to recommend specific snippet tools, your PC or Mac may include one by default, while others are easy to locate for free or low-cost online.
View full article
Filters can be very useful in model building and are widely used, but they can come at the expense of performance; often very visible to users through their use on dashboards. Performance can also hit imports and exports, which in turn may lead to the blocking of other activity, causing a poor perception of the model. There are some very simple guidelines to designing well performing filters: Using a single Boolean filter on a line item that does not have time or versions applied and does not have a summary is fastest. Try to create a Boolean line item that incorporates all the filter criteria you want to apply. This allows you to re-use the line item and combine a series of Boolean line items into a single Boolean for use in the filter. For example, you may want to filter on three data points: Volume, Product Category, and Active Status. Volume is numeric, Product Category is a list formatted line item matching a user selection, and Active Status is a Boolean. Create a line item called Filter with the following formula: Volume > Min Vol AND Product Cat = User Selection.Category AND Active Status Here’s a very simple example module to demonstrate this… A Filter line item is added to represent all the filters we need on the view. Only the Filter line needs to be dimensioned by Users. A User Selection module dimension only by Users is created to capture user-specific filter choices: Here’s the data before we apply the filter: With the filter applied: A best practice suggestion would be to create a filter module and line items for each filter part. You may want other filters and you can then combine each filter as needed from this system module. This should reduce repetition and give you control over the filters to ensure they can all be Boolean. What can make a filter slow? The biggest performance hit for filters are when nesting dimensions on rows. The performance loss is significantly increased by the number of nested dimensions and the number of levels they contain. With a flat list versus nested dimensions (filtering on the same number of items) the nested filter will be slower. This was tested with a 10,000,000 list versus 2 lists of 10 and 1,000,000 items as nested rows; the nested dimension filter was 40% slower. Filtering on line items with a line item summary will be slow. A numeric filter on 10,000,000 items can take less than a second, but with a summary will take at least 5 seconds. Multiple filters will increase time This is especially significant if any of the preceding filters do not lower the load, because they will take additional time to evaluate. If you do use multiple filter conditions, try to order them so the most effective filters are first. If a filter doesn’t often match on anything, evaluate whether it's even needed. Hidden levels act as a filter If you hide levels on a composite list, this acts like a filter before any other filter is applied. The hiding does take time to process and will impact more depending on the number of levels and the size of the list. How to avoid nested rows for export views Using nested rows can be a useful way to filter a complex set of data for export but, as discussed above, the filter performance here can be poor. The best way around this is to pivot the dimensions so there is only one dimension on rows and use the Tabular Multi Column export option with a Filter Row based on Boolean option. Some extra filter tips… Filter duration will affect saved views used in imports, so check the saved view open time to see the impact. This view open time will be on every use of the view, including imports or exports. If you need to filter on a specific list, create a subset of those items and create a new module dimensioned by the subset to view that data.
View full article
Model Load: A large and complex model such as 10B cells can take 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, besides reducing the model size, is by identifying what formula takes most of the time. This requires the Anaplan L3 support, 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 by up to 10 times. See if you can impact the model load on reviewing these 2 and use module line item instead. Model Save: 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. Administrator have no leverage on model save besides formula optimization and model size reducing.  Model Rollback: A model will roll back in some cases of invalid formula, or when a model builder attempts to create a process, an import a view which name already exists. In some large implementation cases, on a complex model made of 8B+ cells, 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 production list limited to a few dozen items, and have TEST and PROD model 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.
View full article
Learn how to organize your model into logical parts to give you a  well-designed model that is easy to follow, understand and amend at a later date
View full article
This article describes how to use the Anaplan DocuSign integration with single sign-on (SSO).
View full article
Anaplan Connect v1.3.3.5 is now available. 
View full article
Audience: Anaplan Internal and Customers/Partners Workiva Wdesk Integration Is Now Available We are excited to announce the general availability of Anaplan’s integration with Workiva’s product, known as the Wdesk. Wdesk easily imports planning, analysis and reporting data from Anaplan to deliver integrated narrative reporting, compliance, planning and performance management on the cloud. The platform is utilized by over 3,000 organizations for SEC reporting, financial reporting, SOX compliance, and regulatory reporting. The Workiva and Anaplan partnership delivers enterprise compliance and performance management on the cloud. Workiva Wdesk, the leading narrative reporting cloud platform, and Anaplan, the leading connected-planning cloud platform, offer reliable, secure integration to address high-value use cases in the last mile of finance, financial planning and analysis, and industry specific regulatory compliance. GA Launch: March 5th  How does the Workiva Wdesk integration work? Please contact Will Berger, Partnerships (william.berger@workiva.com) from Workiva to discuss how to enable integration. Anaplan reports will feed into the Wdesk platform. Wdesk will integrate with Anaplan via Wdesk Connected Sheets. This is a Workiva built and maintained connection. What use cases are supported by the Workiva Wdesk Integration? The Workiva Wdesk integration supports a number of use cases, including: Last mile of finance: Complete regulatory reporting and filing as part of the close, consolidate, report and file process. Workiva automates and structures the complete financial reporting cycle and pulls consolidated actuals from Anaplan. Financial planning and analysis: Complex multi-author, narrative reports that combine extensive commentary and data such as budget books, board books, briefing books and other FP&A management and internal reports. Workiva creates timely, reliable narrative reports pulling actuals, targets and forecast data from Anaplan. Industry specific regulatory compliance & extensive support of XBRL and iXBRL: Workiva is used to solve complex compliance and regulatory reporting requirements in a range of industries.  In banking, Workiva supports documentation process such as CCAR, DFAST and RRP, pulling banking stress test data from Anaplan. Also, Workiva is the leading provider of XBRL software and services accounting for more than 53% of XBRL facts filed with the SEC in the first quarter of 2017.
View full article
Bring Your Own Key (BYOK) is now available. This enables designated Encryption Administrators to encrypt model data using your organization's encryption keys. For more information, see the BYOK User Guide. Note: Bring Your Own Key is an additional product that your organization can purchase if it has the Enterprise edition. Best practices This section contains some best practices to follow when using BYOK. Development Practices Identify or create a workspace that does not contain any essential model data. Encrypt the workspace to practice using BYOK.  After successfully encrypting the workspace: Run the tests on models in the workspace that you want. Follow the same procedure to encrypt your production workspace. If required, decrypt the development workspace. Ensure Workspaces are not in use Workspaces can't be encrypted when they are active. Ensure that your users are no longer using any models in the workspace before starting encryption. Do not start encryption until the workspace state is "Ready". Encrypting before loading data The first encryption is known as encryption in place. This is an offline event. To reduce the amount of time for this encryption, we recommend encrypting a workspace when it is first created or before significant data is loaded. Data added to models within the workspace after encryption is automatically encrypted. This is known as encryption on the fly. It's likely that this is sensitive data and it is more secure to load it after the workspace is encrypted. Identify users for key roles Identify users to be assigned the Encryption Admins role as early as possible. Identify users to be assigned the Tenant Auditor role. Encryption Admin role To maintain separation of duties, Encryption Admins should not have access to any model data. Ensure that Encryption Admins are added as members of at least one workspace with a model permission of "no access". Let your account representative know the email addresses of the Encryption Admins when you first order BYOK. Ideally, assign more than one person to the Encryption Admin role. Encryption Admin users can assign other users in their tenant the Encryption Admin role or remove it using the Access Control feature of the Administration app. Note: Only a limited set of users are eligible to be assigned the Encryption Admin role. Only users who were submitted to Anaplan as potential Encryption Admins appear in the Access Control section of the Administration app. If any users are missing, add them to the workspace in your tenant with the role 'No Access' then contact Anaplan Support and request that those users are added to the list of eligible Encryption Admins. Tenant Auditor role The Tenant Auditor role can access the BYOK audit logs. You might want to specify different users to the ones assigned the Encryption Admin role, but that’s your choice. Your Tenant Administrator can assign users to this role. Tenant Auditors need to be a user in at least one Anaplan workspace, ideally with a model permission of "no access". Wait When the "BYOK" status changes following a successful encryption or decryption action in a workspace, wait two minutes before running another operation on that workspace. This enables trailing processes to complete and helps to prevent unexpected errors. Features As an Encryption Admin, you can use the Reassign Key button on the Encrypted Workspaces page to easily apply key rotation on your workspaces. BYOK now has audit logging. You can use the Audit Service API to: Retrieve up to 30 days of logs. Get the BYOK history for your tenant. Get the BYOK history for your tenant for specific dates that you specify. Get information about who carried out an action in BYOK, when it was done, and what was done. For more information, see the Audit Service API section of the BYOK User Guide. Issues Resolved Issue Description As an Encryption Administrator, you can now assign or remove the Encryption Admin role. Known Issues and Workarounds Issue Workaround When generating a key using the required values, but without waiting before entering values, key generation fails with the "Invalid Key Name" message. Wait a few seconds before entering data on the Generate New Encryption Key popup. When editing an encryption key, the Key Alias field is disabled and cannot be changed. –
View full article
This guide assumes you have set up your runtime environment in Informatica Cloud (Anaplan Hyperconnect) and the agent is up and running. This guide focusses solely on how to configure the ODBC connection and setting up a simple synchronization task importing data from one table in PostgreSQL to Anaplan. Informatica Cloud has richer features that are not covered in this guide. The built-in help is contextual and helpful as you go along should you need more information than I have included in this guide. The intention of this guide is to help you set up a simple import from PostgreSQL to Anaplan and this guide is therefore kept short and is not covering all related areas. This guide assumes you have ran an import using a csv file as this needs to be referenced when the target connection is set up, described under section 2.2 below. To prepare, I exported the data I wanted to use for the import from PostgreSQL to a csv file. I then mapped this csv file to Anaplan and ran an initial import to create the import action that is needed.   1. Set up the ODBC connection for PostgreSQL In this example I am using the 64-bit version of the ODBC connection running on my local laptop. I have set it up for User DSN rather than System DSN, but the process is very similar should you need to set up a System DSN. You will need to download the relevant ODBC driver from PostgreSQL and install it to be able to add it to your ODBC Data Sources as per below (click the Add…button and you should be able to select the downloaded driver).     Clicking the configuration button for the ODBC Data Source opens the configuration dialogue. The configurations needed are: Database is the name of your PostgreSQL database. Server is the address to your server. As I am setting this up on my laptop, it’s localhost. User Name is the username for the PostgreSQL database. The password is the password for the PostgreSQL database. Port is the port used by PostgreSQL. You will find this if you open PostgreSQL. Testing the connection should not return any errors.   2.    Configuring source and target connections After setting up the ODBC connection as described above, you will need to set up two connections, one to PostgreSQL and one to Anaplan. Follow the steps below to do this.   2.1 Source connection – PostgreSQL ODBC Select Configure > connection in the menu bar to configure a connection.    Name your connection and add a description Select type – ODBC Select the runtime environment that will be used to run this. In this instance I am using my local machine. Insert the username for the database (same as you used to set up the ODBC connection). Insert the password for the database (same as you used to set up the ODBC connection). Insert the data source name. This is the name of the ODBC connection you configured earlier. Code page would need to correspond to the character set you are using. Testing the connection should give you below confirmation. If so, you can click Done.   2.2 Set up target connection – Anaplan The second connection that needs to be set up is the connection from Informatica Cloud to Anaplan.   Name your connection and add a description if needed Select type – AnaplanV2 Select the runtime environment that will be used to run this. In this instance I am using my local machine. Auth type – I am using Basic Auth which will require your Anaplan user credentials Insert the Anaplan username Insert the Anaplan password Certification Path location – leave blank if you use Basic Auth Insert the workspace ID (open your Anaplan model and select help and about) Insert the model ID (find in the same way as for workspace ID) I have left the remaining fields as per default setting.   Testing the connection should not pass any errors.   3 Task wizard – Data synchronization The next step is to set up a data synchronization task to connect the PostgreSQL source to the Anaplan target. Select Task Wizards in the menu bar and navigate to Data Synchronization as per below screen shot.   This will open the task wizard, starting with defining the Data Synchronization task as per below. Name the task and select the relevant task operation. In this example I have selected Insert, but other task operations are available like update and upsert.   Click Next for the next step in the workflow which is to set up the connection to the source. Start by selecting the connection you defined above under section 2.1. In this example I am using a single table as source and have therefore selected single source. With this connection you can select the source object with the Source Object drop down. This will give you a data preview so you can validate the source is defined correctly. The source object corresponds to the table you are importing from.     The next step is to define the target connection and you will be using the connection that was set up under section 2.1 above.   The target object is the import process that you ran from the csv file in the preparation step described under section 1 above. This action is referred to below as target object. The wizard will show a preview of the target module columns.    The next step in the process is the Data Filters that has both a Simple and an Advanced mode.   I am not using any data filters in this example and please refer to the built-in help for further information on how to use this.   In the field mapping you will either need to manually map or get the fields automatically mapped depending on if the names in the source and target correspond. If you map manually, you will need to drag and drop the fields from the source to the target. Once done, select Validate Mapping to check no errors are generated from the mapping.     The last step is to define whether to use a schedule to run the connection or not. You will also have the option to insert pre-processing commands and post-processing commands and any parameters for your mapping. Please refer to the built-in help for guidance on this.   After running the task, the activity log will confirm whether the import ran without errors or warnings.   As I mentioned initially, this is a simple guide to help you to set up a simple, single source import. Informatica Cloud does have more advanced options as well, both for mappings and transformations.
View full article
Summary 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 relational SQL queries (for loading into Anaplan), and running Anaplan actions and processes. To enhance the deployment of Anaplan Connect, it is import 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. cls echo on setlocal enableextensions 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 Domain="spg-demo.com" set Smtp="spg-demo" set User="fpmadmin@spg-demo.com" set Pass="1Rapidfpm" set To=-t "fpmadmin@spg-demo.com" -t "gburns@spg-demo.com" set From="fpmadmin@spg-demo.com" 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 *** :START REM Dynamically set logfile name based upon current date and retry count. set LogFile="%date:~-4%-%date:~7,2%-%date:~4,2%-ANAPLAN-LOG-RUN-%COUNT%.TXT" REM Delete prior log and error files del .\BAT_STAT.TXT del .\AC_API.ERR 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 cd %~dp0 if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser% set Command=.\AnaplanClient.bat %Credentials% -workspace %WorkspaceId% -model %ModelId% %Operation% @echo %Command% 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% ^ %To% ^ -f %From% ^ -sub %Subject% ^ -msg-body .\BAT_STAT.TXT 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 @goto :START ) else ( set /a ERRNO=1 @goto :END ) ) 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 :END 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.  Success Mail: Error Mail:
View full article
Making sure that production data lists are correctly marked within a model is a  key step to setting up and using 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: 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 are three ways to identify all of the formulas which are hard-coded to production data lists. Check the list as a production data list and the error that occurs will tell you where the first hard-coded reference is. As you fix one item at a time, the following error will tell you where the next reference is. Anaplan only recommends this method if there are potentially one or two line items that need to be fixed in the model.  Manually scroll through the line items of a model to identify the line items with hard-coded references. We recommend this method if the model administrator has a very good understanding of where all of the hard-coded line items would already reside. Export the line items with formulas and filter 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. The following instructions will demonstrate how to accomplish option 3 of exporting line items. Open the model which you are changing production data lists for Within the settings tab, open Modules and click on the Line Items tab Export all of the line items Set a filter on all of the column headers of your Excel ®   file Filter the Formula column and set your filter criteria to the following two criteria (use Contains for both criteria, change the filter from AND to OR) SELECT: List 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.   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. Afterwards, 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. Example: 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. Example: 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.
View full article
Note that this article uses a planning dashboard as an example, but many of these principles apply to other types of dashboards, as well. Methodology User stories Building a useful planning dashboard always starts with getting a set of very clear user stories, which describes how a user should interact with the system: The user stories need to identify: What the user wants to do What data the user need 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 project and/or project team members outside of modeling team will host 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 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. Dashboard structure 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 Main grid(s) 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 Light instructions A dashboard can have more than one of these groupings, but all elements within a grouping needs are here 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. Example:   General guidelines  Call to action Write a short sentence describing task to be completed within this grouping. Use heading 2 format.   Main grid(s) The main grid is the central component of the dashboard, or of the grouping. It's where the user will spend most of his/her 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: 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 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; 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 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 Vertical scroll 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. Status Flag 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," envision building pre-set user-based filters Warning #1: This will have an impact on the module size and will require a few line items defined by a "user" dimension. Forget about filtering for 500k+ accounts with 2,000 users. Instead, it works well for 5,000 items by 400 users. Warning #2: The grid will become read-only for the line item(s) that are not defined by users, as well as the other required dimensions. The workaround is to publish the subsidiary view, but will prevent editing within the main grid. This might be considered more for read-only grids or reporting dashboards. Display these user-based filters above the main grid, and have an "Apply filter" button that simply re-opens the same dashboard and will apply the filter to the grid.  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. Main charts 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 selection of KPI or row of main display grid Should be used for: Comparison between row values of main display grid Displaying difference when 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 Info grid(s) 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 terriories 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 main display grid Should read vertically to display many metrics pertaining to list item selected Info charts 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 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) Light instructions 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" Action Instructions Directly located next to a drop down, input field, or button where function is not quite clear No more than 5–6 words Formatted in "instructions"
View full article
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. Avoid this: 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   
View full article
Question I am working on a dashboard and I do not want to show every line item or list item included on a module. What is the difference between using the Hide functionality and the Show functionality?    Answer There are two main differences. The first is what happens when you add a new line item or item to a list. If you've used Hide, you will see new items or line items added to the module. If you've used Show, the new item will not appear.    Let's say I have a module with 5 line items and I only want to see line items 1, 2, and 5. In this example, I choose to hide line items 3 and 4. If I were to add a new line item to this module I will see that new line item appear. In this same senario, if I were to select to Show line items 1, 2, and 5, I would not see the new line item appear. I could also reorder the line items with the Show functionality if I wanted to. The other difference is that by using Show you can change the order of the items based off of the order that you select them. Try this: Select multiple line items by holding down Command (Mac) or Control (Windows), and then right-click or choose the drop down option and click on Show Selection. The line items will appear in the order you selected them.
View full article
Summary We explain here a dynamic way to filter specific levels of a hierarchy. This provides a better way to filter & visualize hierarchies.  Overview This tutorial explains how to calculate the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level. In this example we have an organization hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy we want to calculate a filtering module value that returns the associated level. Context and notes This technique addresses a specific limitation within dashboards where a composite hierarchy's level cannot be selected if the list is synchronized to multiple module objects on the dashboard. We show the technique of creating a static filtering module based on the levels of the composite structure. The technique utilizes the Summary method Ratio of line items corresponding to the list levels to define the value of the filtering line items. Note that it is not a formula calculation but a use of the summary method Ratio applied to the composite hierarchy.   Example list We defined in this example a 4-levels list as follows: Defining the level of each list In order to calculate the level of each item in the lists, we need to create a module that calculates it by: Creating as many line items as level of hierarchy + one technical line item. Changing the settings in the blueprint of those line items according to the following table: Line Item Formula Applies to Summary Summary method Setting Ratio Technical line item 1 (empty) Formula   Level or L4 (lowest level) 4 Org L4 Ratio* L3 / Technical L3 3 Org L3 Ratio L2 / Technical L2 2 Org L2 Ratio L1 / Technical L1 1 Org L1 Ratio L1 / Technical                       When applying these settings, the calculation module looks like this: *Note that the Technical line item Summary method is using Formula, 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.     We can now use the line item at the lowest level—“Level (or L4)” in the example—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 on the level 4 and 1:   This gives the following result:    
View full article
Personal dashboards is 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 rather than 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 are going to 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 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.
View full article
This article provides the steps needed to create a basic time filter module. This module can be used as a point of reference for time filters across all modules and dashboards within a given model. The benefits of a centralized Time Filter module include: One centralized governance of time filters. Optimization of workspace, since the filters do not need to be re-created for each view. Instead, use the Time Filter module.  Step 1: Create a new module with two dimensions—time and line items. The example below has simple examples for Weeks Only, Months Only, Quarters Only, and Years Only. Step 2: Line items should be Boolean formatted and the time scale should be set in accordance to the scale identified in the line item name. The example below also includes filters with and without summary methods, providing additional views depending on the level of aggregation desired. Once your preliminary filters are set, your module will look something like the screenshot below.  Step 3: Use the pre-set Time Filters across various modules and dashboards. Simply click on the filters icon in the tool bar, navigate to the time tab, select your Time Filter module from the module selection screen, and select the line item of your choosing. Use multiple line items at a time to filter your module or dashboard view.
View full article
Dimension Order affects Calculation Performance Ensuring consistency in the order of dimensions will help improve performance of your models. This consistency is relevant for modules and individual line items. Why does the order matter? Anaplan creates and uses indexes to perform calculations. Each cell in a module where dimensions intersect is given an index number. Here are two simple modules dimensioned by Customer and Product. In the first module, Product comes first and Customer second and in the second module, Customer is first and Product second. In this model, there is a third module that calculates revenue as Prices * Volumes. Anaplan assigns indexes to the intersections in the module. Here are the index values for the two modules. Note that some of the intersections are indexed the same for both modules: Customer 1 and Product 1, Customer 2 and Product 2 and Customer 3 and Product 3, and that the remainder of the cells have a different index number. Customer 1 and Product 2 is indexed with the value of 4 in the top module and the value of 2 in the bottom module. The calculation is Revenue = Price * Volume. To run the calculation, Anaplan performs the following operations by matching the index values from the two modules. Since the index values are not aligned the processor scans the index values to find a match before performing the calculation. When the dimensions in the module are reordered, these are the index values: The index values for each of the modules are now aligned. As the line-items of the same dimensional structure have an identical layout, the data is laid out linearly in memory. the calculation process accesses memory in a completely linear and predictable way. Anaplan’s microprocessors and memory sub-systems are optimized to recognise this pattern of access and to pre-emptively fetch the required data. How does the dimension order become different between modules?. When you build a module, Anaplan uses the order that you drag the lists onto the Create Module dialog The order is also dependent on where the lists are added. The lists that you add to the pages area are first, then the lists that you add to the rows area, and finally the lists added to the columns area. It is simple to re-order the lists and ensure consistency. Follow these steps: On the Modules pane, (Model Settings>Modules) look for lists that are out of order in the Applies To column. Click the Applies To row that you want to re-order, then click the ellipsis. In the Select Lists dialog, click OK. In the Confirm dialog, click OK. The lists will be in the order that they appear in General Lists. When you have completed checking the list order in the modules, click the Line Items tab and check the line items. Follow steps 1 through 3 to re-order the lists. Subsets and Line Item Subsets One word of caution about Subsets and Line Item subsets. In the example below, we have added a subset and a Line Item Subset to the module: The Applies To is as follows: Clicking on the ellipsis, the dimensions are re-ordered to: The general lists are listed in order first, followed by subsets and then line item subsets You still can re-order the dimensions by double clicking in the Applies to column and manually copying or typing the dimensions in the correct order. Other Dimensions The calculation performance relates to the common lists between the source(s) and the target. The order of separate lists in one or other doesn’t have any bearing on the calculation speed.
View full article
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 are 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 Or 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) Or 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. Sales>0 Instead of IF Sales > 0 then TRUE ELSE FALSE
View full article