Anaplan Connect 2.0 - Advanced Scripts
This article is the 2nd part of our 3-blogs series on Anaplan Connect 2.0.
If you missed part 1, go here.
Disclaimer:The scripts and examples in this article have been fully tested by our team, but they are not supported by Anaplan; if you have any issue, please use the comments below.
Target Audience: Integration Specialists
Prerequisites:
- Technical: the same as Anaplan Connect 2.0 (we will be focusing on this version in the article)
- Windows (We might issue Linux versions at a later stage)
- Some scripting knowledge
Before we start
Be sure you had a look at the official Anaplan Connect User Guide where examples of scripts are provided.
They treat a large range of needs, like tracking the workspaces and models sizes or updating lists without using Anaplan actions. Anaplan Transactional APIs are also accessible through Anaplan Connect 2.0, so please have a look at those.
Purpose of this article
After different implementations of Anaplan Connect, we wanted to share some of the most common customizations of the Anaplan Connect scripts that have been implemented.
Quick focus: Why scripting? In some situations like displayed in the use cases below, a quick win can be to add some extra features to the Anaplan Connect scripts so that Anaplan models can benefit of them (like the today's date) or ITs can have a better handling of what comes out of those Anaplan Connect scripts. Nevertheless, it can appear that in other situations, scripting might not be efficient enough and coding or use of ETL solutions cannot be avoided (especially when heavy workload is to be done on the source file). This article is about providing some insights and some help in situation we have encountered during previous implementations of Anaplan Connect (AC).
By no means, the tips and tools provided here can be considered as best practices or Anaplan supported syntaxes. There can be better methods to address the needs displayed in that article. Please share your ideas in the comments!
Use cases
Today's date
This is a frequent ask. A quick search on community can show the need to have today's date in a model and there are already several answers to that question. This paragraph is about summarizing that in a more detailed how-to format.
- Create a line item that will be updated by the following import.
We will import the data as text so that it can be used and transformed in various ways in the model afterwards.
- Create a simple import file with a date contained in it.
- Create the import action in Anaplan
- Use the following script to update the date with today's date
This script has been tested with AC 2.0 and AC 1.4.4
@Echo off
:: This script looks up the current date, writes it to a csv file, and sends it to Anaplan
:: Here we set the file path of the csv file we will be writing the current date to
set FileName="RealToday.csv"
SET DateTimeToday=%date:~0,2%/%date:~3,2%/%date:~-4%
@Echo Today;%DateTimeToday% > %FileName%
set AnaplanUser="chriskeo@anaplan.com:XYZCH"
set WorkspaceId="8a81b094654f3d4c0166313797f85c7b"
set ModelId="0B0ED9F7DF404807822526EAAE222405"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set DSName="today.txt"
set ImportName="Import Today Date"
set DumpName="Error.log"
set Chunksize=1
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file %DSName% -put %FileName% -import %ImportName% -execute -output %DumpName%
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
@Echo %Command%
cmd /c %Command%
The script works as follows:
The variable DateTimeToday parses the server date and generates a value in the format we want. For instance, %date:~0,2% is about the day. Then you can parse in the same fashion the month and the year, all separated by "/".
This variable is used to create the file RealToday.csv, located on the Anaplan Connect server. This file will then be used to update the datasource "today.txt" located in the Anaplan model.
You can see that the update has been sent to the Anaplan model:
The date is then imported into the model
Adding a timestamp
In extension of the previous part, we can now use that to timestamp a log file or a source file that we want to archive. In the following example, we intend to archive the source file after it has been used in the Anaplan Connect script.
To do so, simply use the copy command and add at the end of the filename the timestamp you would have previously set up.
@Echo off
:: This script looks up the current date, writes it to a csv file, and sends it to Anaplan
:: Here we set the file path of the csv file we will be writing the current date too
set AnaplanUser="chriskeo@anaplan.com:BlueWhiteRed"
set WorkspaceId="8a81b094654f3d4c0166313797f85c7b"
set ModelId="0B0ED9F7DF404807822526EAAE222405"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set DSName="numbered_imports_opps.txt"
set FilePath="sampleFileToImport3.txt"
set ImportName="Import_numbered_opps"
set DumpName="Error.log"
set Chunksize=1
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file %DSName% -put %FilePath% -import %ImportName% -execute -output %DumpName%
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
rem @Echo %Command%
cmd /c %Command%
SET DateTimeToday=%date:~0,2%_%date:~3,2%_%date:~-4%
copy "D:\Dev\AC200\anaplan-connect-2.0.0\%FilePath%" "D:\Dev\AC200\anaplan-connect-2.0.0\Archive\%DateTimeToday%_%FilePath%
🔆 Notice that we commented the "@echo %Command%" line. It's a good practice to do so as we might not want to print out credentials everytime AC scripts are fired up.
This will generate in the designated folder a file with a timestamp in its name:
Output the command prompt content to a file
It can happen that for some logging reasons, the output in the command prompt (stdout) is valuable and worth being extracted for further analysis. For instance, when one needs to supervise the status of the different runs of Anaplan Connect.
To do so we only need to do the following: adding additional parameters to the cmd line
If we take the script for the today's import and we amend it in the following way:
Obviously, to do so, you'll have to go over the REM line stating not to edit below the line.
Then you will notice that a new file is generated after the execution of the batch.
To know more about this, I invite you to search on google the concepts of stdin, stdout and stderr.
Notice that nothing will be displayed in the prompt screen.
Now, you can use what we've learned in the first part and generate a log that will be different each day by applying this simple syntax.
You'll obtain the following code:
@Echo off
:: This script looks up the current date, writes it to a csv file, and sends it to Anaplan
:: Here we set the file path of the csv file we will be writing the current date too
set FileName="RealToday.csv"
SET DateTimeToday=%date:~0,2%_%date:~3,2%_%date:~-4%
@Echo Today;%DateTimeToday% > %FileName%
set AnaplanUser="chriskeo@anaplan.com:AllezLOM"
set WorkspaceId="8a81b094654f3d4c0166313797f85c7b"
set ModelId="0B0ED9F7DF404807822526EAAE222405"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set DSName="today.txt"
set ImportName="Import Today Date"
set DumpName="Error.log"
set Chunksize=1
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file %DSName% -put %FileName% -import %ImportName% -execute -output %DumpName%
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
rem @Echo %Command%
cmd /c %Command% > result_%DateTimeToday%.log
pause
We can now have a log file that will be updated every day.
Note that I have changed the separator for the date's tokens as slashes would clash with the filename.
One last thing ! If you double the chevron sign like this : cmd /c %Command% >> result_%DateTimeToday%.log, then you'll be able to concatenate the different outputs of the different executions as shown below:
Adding a line ID to an input file
Scripting can help you achieve some transformations tasks that you could not do easily otherwise. For instance, the following code shows a way to add a row id column to a file with few additional lines in the Anaplan Connect script.
This is usually useful when we want to import some transactional data and you want to upload all the lines but the file extracted does not contain that column.
Warning: this method is suitable when the input file is not too big (up to few megabytes). Above that size, you may experiment some poor performances. It's also noticeable that the linux version of that tool (soon to be released) looks to be more efficient.
For instance, we've got the following module:
and the following source file:
You can see that we are missing the Row ID column (called here "id_line").
An import action is already prepared, considering that you'll get that additional column:
You will be missing a row id column to import it in a transactional manner. Instead of asking for IT's help, you might try the following code:
set FilePath="imports_opps.txt"
set FileTarget="numbered_opps.txt"
copy\Dev\AC200\anaplan-connect-2.0.0\%FilePath% buffer.txt*
set /p texte=< %FilePath%
echo id_line,%texte% > %FileTarget%
set "LineNumber=1"
setlocal EnableDelayedExpansion
for /f "skip=1 tokens=* delims=,+" %%G in (buffer.txt) do (
echo !LineNumber!,%%G >> %FileTarget%
set /a "LineNumber=!LineNumber!+1"
)
del buffer.txt
First, the program copies the source file (the one without Row IDs, named as FilePath) to a buffer file. It will then generate in the target file (the one to be sent to the Anaplan, named as FileTarget) the first line, which contains the headers columns (by concatenating the name of the Row ID column (here id_line) and the first line of the source file)
With a loop, each line of the buffer file will be added to the FileTarget file concatenated with the incrementing line id and the "," as column separator. Eventually, we'll delete the buffer file.
The file that will be imported will now contain the new column:
Running Anaplan Connect scripts with variables
- Creating a master batch file
It can be very useful when writing several AC scripts to locate the variables in one single place so that, if an amendment is required (like the change of credentials for instance), you won't need to update all the batch scripts with the same corrections (quite a tedious task...) To do so, you can set up a master batch file that will encapsulate the other batches.
In the following example, the master file is called "MasterBatchExample.bat" and it contains the credentials used in the actual Anaplan Connect scripts.
Looking at the AC script, you can see that we replaced the credentials by the master variable generalCreds we have set up in the MasterBatchExample.bat
You would then only need to launch the Master Batch script to execute Anaplan Connect script.
- Using vEncrypt utility
For some more advanced tasks, like encrypting the credentials or calling Anaplan Connect scripts by passing variables as parameters in the prompt screen, you can also use the vEncrypt script quoted in the following article: https://community.anaplan.com/t5/How-To/How-to-leverage-sample-codes-to-generate-authentication-strings/ta-p/109439, at the last part of the page.
Let's say you have encrypted your credentials (username/password) and that you can execute vEncrypt as described in the referred article. You'll obtain a similar screen than this one:
You could now also add parameters like workspace ID or model ID (up to 5) at the end of the command line.
To achieve that, just amend the AC script with the following format:
@Echo off
:: This script looks up the current date, writes it to a csv file, and sends it to Anaplan
:: Here we set the file path of the csv file we will be writing the current date too
set AnaplanUser=%1
set WorkspaceId=%2
set ModelId=%3
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set DSName="numbered_imports_opps.txt"
set FilePath=%5
set ImportName=%4
set DumpName="Error.log"
set Chunksize=1
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file %DSName% -put %FilePath% -import %ImportName% -execute -output %DumpName%
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
rem @Echo %Command%
cmd /c %Command%
With "%1", "%2", etc … being the parameters you've passed in the command line (be careful of the order).
Got feedback on this content? Let us know in the comments below.
Contributing authors: Pavan Marpaka and Christophe Keomanivong.