theplanual

The definitive set of standards for Anaplan model building.

Read It

Let's talk about it

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

Visit Forums
This post summarizes steps to convert your security certificate to PEM format and test it in a cURL command with Anaplan. The current production API version is v1.3. Using a certificate to authenticate will eliminate the need to update your script when you have to change your Anaplan password. To use a certificate for authentication with the API, it first has to be converted into a Base64 encoded string recognizable by Anaplan. Information on how to obtain a certificate can be found in Anapedia. This article assumes that you already have a valid certificate tied to your user name. Steps: 1.   To properly convert your Anaplan certificate to be usable with the API, first you will need openssl (https://www.openssl.org/). Once you have that, you will need to convert the certificate to PEM format. The PEM format uses the header and footer lines “-----BEGIN CERTIFICATE-----“, and “-----END CERTIFICATE-----“. 2.   If your certificate is not in PEM format, you can convert it to the PEM format using the following OpenSSL command. “certificate-(certnumber).cer” is name of source certificate, and “certtest.pem” is name of target PEM certificate. openssl x509 -inform der -in certificate-(certnumber).cer -out certtest.pem View the PEM file in a text editor. It should be a Base64 string starting with “-----BEGIN CERTIFICATE-----“, and ending with “-----END CERTIFICATE-----“. 3.   View the PEM file to find the CN (Common Name) using the following command: openssl x509 -text -in certtest.pem It should look something like "Subject: CN=(Anaplan login email)". Copy the Anaplan login email. 4.   Use a Base-64 encoder (e.g.   https://www.base64encode.org/   ) to encrypt the CN and PEM string, separated by a colon. For example, paste this in: (Anaplan login email):-----BEGIN CERTIFICATE-----(PEM certificate contents)-----END CERTIFICATE----- 5.   You now have the encrypted string necessary to authenticate API calls. For example, using cURL to GET a list of the Anaplan workspaces for the user that the certificate belongs to: curl -H "Authorization: AnaplanCertificate (encrypted string)" https://api.anaplan.com/1/3/workspaces
View full article
This article covers the necessary steps for you to migrate your Anaplan Connect (AC) 1.3.x.x script to Anaplan Connect 1.4.x. For additional details and examples, refer to the  latest Anaplan Connect User Guide. The changes are: New connectivity parameters. Replace reference to Anaplan Certificate with Certificate Authority (CA) certificates using new parameters. Optional Chunksize & Retry parameters. Changes to JDBC configuration. New Connectivity Parameters Add the following parameters to your Anaplan Connect 1.4.x integration scripts. These parameters provide connectivity to Anaplan and Anaplan authentication services. Note: Both of the urls listed below need to be whitelisted with your network team. -service "https://api.anaplan.com/" -auth "https://auth.anaplan.com" Certificate Changes As noted in our   Anaplan-Generated Certificates to Expire at the End of 2019 blog post, new and updated Anaplan integration options support Certificate Authority (CA) certificates for authentication. Basic Authentication is still available in Anaplan Connect 1.4.x, however, the use of certificates has changed. In Anaplan Connect 1.3.x.x, the script references the full path to the Anaplan certificate file. For example: -certificate "/Users/username/Documents/AnaplanConnect1.3/certificate.pem" In Anaplan Connect 1.4.x the CA certificate can be referenced via two different options. Examples of both options are included at the end of this article as well as in the Anaplan Connect 1.4.x download. Option 1: Direct Use of the Private Key with Anaplan Connect Use your Private Key with Anaplan Connect by providing to certificate, private key and optional private key passphrase.  For example: If your private key has been encrypted use the following: CertPath="FullPathToThePublicCertificate" PrivateKey="FullPathToThePrivateKey:Passphrase" If your private key has not been encrypted then the passphrase can be omitted, however the colon is still needed in the path of the private key. CertPath="FullPathToThePublicCertificate" PrivateKey="FullPathToThePrivateKey:" To pass these values to Anaplan Connect 1.4.x, use these command line parameters: -certificate {path to the certificate file} -privatekey {path to the private key file:}{passphrase} These parameters should be passed as part of the credentials in the script: Credentials="-certificate ${CertPath} -privatekey ${PrivateKey}" Option 2: Create a Java Keystore A Java Keystore (JKS) is a repository of security certificates and their private keys.  Refer to   this video   for a walkthrough of the process of getting the CA certificate into the key store. You can also refer to   Anaplan Connect User Guide   for steps to create the Java key store. Once you have imported the key into the JKS,   make note of this information : Path to the JKS (directory path on server where JKS is saved) The Password to the JKS The alias of the certificate within the JKS. For example: KeyStorePath ="/Users/username/Documents/AnaplanConnect1.4/my_keystore.jks" KeyStorePass ="your_password" KeyStoreAlias ="keyalias" To pass these values to Anaplan Connect 1.4.x, use these command line parameters: -keystore {KeystorePath} -keystorealias {KeystoreAlias} -keystorepass {KeystorePass} These parameters should be passed as part of the credentials in the script: Credentials="-keystore ${KeyStorePath} -keystorepass ${KeyStorePass} -keystorealias ${KeyStoreAlias}" Chunksize Anaplan Connect 1.4.x allows for custom chunk sizes on files being imported. The -chunksize parameter can be included in the call with the value being the size of the chunks in megabytes. The chunksize can be any whole number between 1 and 50. -chunksize {SizeInMBs} Retry Anaplan Connect 1.4.x allows for the client to retry requests to the server in the event that the server is busy. The -maxretrycount parameter defines the number of times the process retries the action before exiting. The -retrytimeout parameter is the time in seconds that the process waits before the next retry. -maxretrycount {MaxNumberOfRetries} -retrytimeout {TimeoutInSeconds} Changes to JDBC Configuration With Anaplan Connect 1.3.x.x the parameters and query for using JDBC are stored within the Anaplan Connect script itself. For example: Operation="-file Sample.csv' -jdbcurl 'jdbc:mysql://localhost:3306/mysql?useSSL=false' -jdbcuser 'root:Welcome1' -jdbcquery 'SELECT * FROM py_sales' -import 'Sample.csv' -execute" With Anaplan Connect 1.4.x. the parameters and query for using JDBC have been moved to a separate file. The name of that file is then added to the AnaplanClient call using the   -jdbcproperties   parameter. For example:  Operation="-auth 'https://auth.anaplan.com' -file 'Sample.csv'  -jdbcproperties 'jdbc_query.properties' -import 'Sample.csv' -execute " To run multiple JDBC calls in the same operation, a separate jdbcpropeties file will be needed for each query. Each set of calls in the operation should include then following parameters: -file, -jdbcproperties, -import, and -execute. In the code sample below each call is underlined separately.  For example: Operation="-auth 'https://auth.anaplan.com' -file 'SampleA.csv' -jdbcproperties 'SampleA.properties' -import 'SampleA Load' -execute -file 'SampleB.csv' -jdbcproperties 'SampleB.properties' -import 'SampleB Load' -execute" JDBC Properties File Below is an example of the JDBCProperties file. Refer to the   Anaplan Connect User Guide   for more details on the properties shown below. If the query statement is long, the statement can be broken up on multiple lines by using the \ character at the end of each line. No \ is needed on the last line of the statement. The \ must be at the end of the line and nothing can follow it. jdbc.connect.url=jdbc:mysql://localhost:3306/mysql?useSSL=false jdbc.username=root jdbc.password=Welcome1 jdbc.fetch.size=5 jdbc.isStoredProcedure=false jdbc.query=select * \ from mysql.py_sales \ where year = ? and month !=?; jdbc.params=2018,04 CA Certificate Examples Direct Use of the Private Key Anaplan Connect Windows BAT Script Example (with direct use of the private key) '@echo of rem This example lists files in a model set CertPath="C:\CertFile.pem" set PrivateKey="C:\PrivateKeyFile.pem:passphrase" set WorkspaceId="Enter WS ID Here" set ModelId="Enter Model ID here" set Operation=-service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -workspace %WorkspaceId% -model %ModelId% -F set Credentials=-certificate %CertPath% -privatekey %PrivateKey% rem *** End of settings - Do not edit below this line *** setlocal enableextensions enabledelayedexpansion || exit /b 1 cd %~dp0 set Command=.\AnaplanClient.bat %Credentials% %Operation% @echo %Command% cmd /c %Command% pause Anaplan Connect Shell Script Example (with Direct Use of the Private Key) #!/bin/sh # This example lists files in a model set CertPath="/path/CertFile.pem" set PrivateKey="/path/PrivateKeyFile.pem:passphrase" WorkspaceId="Enter WS ID Here" ModelId="Enter Model Id Here" Operation="-service 'https://api.anaplan.com' -auth 'https://auth.anaplan.com' -workspace ${WorkspaceId} -model ${ModelId} -F" #________________ Do not edit below this line __________________ if [ "${PrivateKey}" ]; then     Credentials="-certificate ${CertPath} -privatekey ${PrivateKey}" fi echo cd "`dirname "$0"`" cd "`dirname "$0"`" if [ ! -f AnaplanClient.sh ]; then     echo "Please ensure this script is in the same directory as AnaplanClient.sh." >&2     exit 1 elif [ ! -x AnaplanClient.sh ]; then     echo "Please ensure you have executable permissions on AnaplanClient.sh." >&2     exit 1 fi Command="./AnaplanClient.sh ${Credentials} ${Operation}" /bin/echo "${Command}" exec /bin/sh -c "${Command}"  Using a Java Keystore (JKS) Anaplan Connect Windows BAT Script Example (Using a Java Keystore) @echo off rem This example lists files in a model set Keystore="C:\YourKeyStore.jks" set KeystoreAlias="alias1" set KeystorePassword="mypassword" set WorkspaceId="Enter WS ID Here" set ModelId="Enter Model ID here" set Operation=-service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -workspace %WorkspaceId% -model %ModelId% -F set Credentials=-k %Keystore% -ka %KeystoreAlias% -kp %KeystorePassword% rem *** End of settings - Do not edit below this line *** setlocal enableextensions enabledelayedexpansion || exit /b 1 cd %~dp0 set Command=.\AnaplanClient.bat %Credentials% %Operation% @echo %Command% cmd /c %Command% pause Anaplan Connect Shell Script Example (Using a Java Keystore) #!/bin/sh #This example lists files in a model KeyStorePath="/path/YourKeyStore.jks" KeyStoreAlias="alias1" KeyStorePass="mypassword" WorkspaceId="Enter WS ID Here" ModelId="Enter Model Id Here" Operation="-service 'https://api.anaplan.com' -auth 'https://auth.anaplan.com' -workspace ${WorkspaceId} -model ${ModelId} -F" #________________ Do not edit below this line __________________ if [ "${KeyStorePath}" ]; then     Credentials="-keystore ${KeyStorePath} -keystorepass ${KeyStorePass} -keystorealias ${KeyStoreAlias}" fi echo cd "`dirname "$0"`" cd "`dirname "$0"`" if [ ! -f AnaplanClient.sh ]; then     echo "Please ensure this script is in the same directory as AnaplanClient.sh." >&2     exit 1 elif [ ! -x AnaplanClient.sh ]; then     echo "Please ensure you have executable permissions on AnaplanClient.sh." >&2     exit 1 fi Command="./AnaplanClient.sh ${Credentials} ${Operation}" /bin/echo "${Command}" exec /bin/sh -c "${Command}"   
View full 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.
View full article
What are the benefits and drawbacks of using Versions instead a General List
View full article
As a business operations manager on the Anaplan on Anaplan (AoA) team—an internal team, focused on bringing Connected Planning to life within Anaplan—I help to oversee our internal Anaplan model ecosystem and assist in the solutioning and development of Anaplan models across all of our functional business groups.  As Anaplan's largest customer, one of the numerous requirements we must address is user access and security. Utilizing Anaplan's user roles functionality typically gets the job done for granting users access to specific models. Occasionally, we must go one step further and leverage Anaplan's selective access feature. Roles and selective access are powerful tools and address our needs nearly all of the time. However, as we scale our own use of Anaplan, we have begun to encounter the need to provision user's access to lists based on multiple criteria, rather than just a single condition.  In Real Life A real-life user provisioning challenge we’ve encountered is in our headcount planning model. As this model provides real-time reporting on our employees, there are inherent sensitivities and considerations around who can see information for specific employees—taking into consideration visibility to things like compensation and personally identifiable information (PII). We have multiple use cases built out within the model, including recruiting capacity and analysis, attrition reporting, hiring reporting, etc., and the access to specific employee data depends on the end user of the model. Sample employee roster: Joey manages Usain, Eluid, and Meb; Americas Geo; HR Cost Center. In this example model, we have our complete employee roster included. If an HR business partner accesses the model, we want them to see only employees that are tagged to the functional area they support (e.g. finance, sales). Additionally, if a business manager goes into the model, they should only see information for employees where they are the manager, or employees downstream on their management chain. But wait! If the HR business partner is in Europe, they shouldn’t be able to see PII fields for their employees. Do you see how this could get complicated quickly? Additionally, some dashboards that contain non-sensitive employee information are perfectly fine to open up broadly to all users, while others contain sensitive data we need to provision. What’s Next So, how do we handle this? We can’t provision access by roles because all of the aforementioned users need access to the same modules/dashboards as it relates to the employees they manage. Additionally, no single user should be able to see all data for all employees. Selective access could be considered as a solution, but given the levels of complexity and multiple logical drivers—as well as the requirement to not hide reporting of non-sensitive data for employees—that option also has limitations. Enter Dynamic Cell Access (DCA). Since DCA allows us to base read/write access off of formulae logic, it offers us the ability to layer on multiple levels of logic ahead of deciding whether or not someone should be able to read or write on a particular item in a list. It’s dynamic (who would have thought with that name?), which means it adjusts live as data within the model changes. Additionally, it offers us the flexibility to apply the provisioning logic to the exact modules we want to, rather than blanket provision users across the model. DCA In Action The following is a high-level example of how to leverage the power of DCA: Load employee roster data into Anaplan, ensuring the data contains the employee email—the same email that is used to log in to Anaplan. This allows for the mapping of Anaplan users to the employee roster. Set up a System module with the ‘applies-to’ list of the user list. User meta-data staging module: Rows represent model users (Joey, in this example) and the line items represent meta-data off of the roster module. Within this module, we can join the employee roster data and the user list to map the employee’s meta-data to their Anaplan user profile (e.g. cost center, location, management chain, etc.) Using a series of Boolean line items, we can write whatever logic we want to base our DCA on. In our example, this could include: Is HR business partner? Is Euro? Basically, this is a staging module for all of the employee meta-data we want to leverage to create our DCA drivers. Set up a second System module with the ‘applies-to’ list of whatever list you want to apply DCA against, as well as the user list. In our case, this would also be our employee roster list. Create a series of Boolean line items, testing different attributes of the User System module we just set up against the meta-data of the employees. An example would be (Employee Cost Center = User’s Cost Center). DCA logic module for the employee roster list (rows in this module): Line items represent the logic used to determine whether the user (Joey— in the page selector) can see the employee. The key here is to consolidate all of your logic into a single “Master” line item, which is on the far right. Daisy chain your conditions together as desired, with the end result being a master Boolean line item, which is the driver for whether or not a particular user has read or write access to a particular item within the list. In this dashboard you can see that the information is masked for those employees that did not meet all of the criteria identified in the master DCA line item. Select which modules you’d like to apply DCA to. The nice thing about DCA is you can go down to the line item level to map the master Boolean driver against. The incredible power of the process described above is not only the complete control over and ability to customize your user provisioning, but also that as new roster data is loaded into Anaplan, the DCA automatically adjusts itself to account for changes. So, if someone changed cost centers or a manager on an employee changed, the formulas that we set up above would be referencing the new employee meta-data, and would automatically adjust the DCA drivers, allowing for a much more hands-off, sustainable approach to user provisioning. Another inadvertent benefit we discovered with using this methodology is that Anaplan treats cells that are blank as a result of DCA drivers as being blank for filtering purposes. So, if you want to set up a dashboard that auto-filtered employees for the end user based on the logic above, you just have to add a line item hardcoded to contain values for every list item, and then filter that line item for not-blanks on your dashboards. Then you have a dynamic filter based on the user that is viewing the model…pretty slick! Take this one step further and filter for not-blanks on a line item that will always contain data for an employee, and you get completely custom reporting based on which end user is viewing the dashboards.
View full article