Anaplan Connect User Guide

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 36

Anaplan Connect User Guide
Last Updated: 15 August 2013
Introduction to Anaplan Connect..................................................................................3
What is it for?............................................................................................................3
To Install Anaplan Connect..........................................................................................3
Supported authentication methods................................................................................3
Username and password based authentication..........................................................3
Certificate-based authentication................................................................................3
To import a text file and run the import........................................................................5
First run the import manually....................................................................................5
Then copy and edit a Batch file to automate this process.........................................5
Where to find the variables to use in the batch file.......................................................7
AnaplanUser..............................................................................................................7
Workspace ID, Model ID..........................................................................................7
-file............................................................................................................................8
-put.............................................................................................................................8
-import.......................................................................................................................8
-execute......................................................................................................................8
-output........................................................................................................................9
List of Available Operations............................................................................................10
JDBC Operations.............................................................................................................11
Example: Upload a file, but do not run the import.....................................................13
Example: Run the import from a pre-loaded file........................................................13
Example: Upload 2 files and run 2 imports................................................................13
Example: List workspaces using certificate-based authentication..............................14
Example: Export data to a text file or Excel file.........................................................15
First run the export manually..................................................................................15
Then edit and run the batch file ..............................................................................15
Example: Run the export, but do not download.........................................................17
Example: Download the results from an export that has already been run.................17
Example: Running a model to model import..............................................................18
First run the import manually..................................................................................18
Then edit and run the batch file...............................................................................19
Example: Deleting Items from a List..........................................................................20
Passing run-time parameters.......................................................................................22
To schedule an import or export.................................................................................23
Scheduler for Windows XP.....................................................................................23
Scheduler for Windows 7........................................................................................23
JDBC Connectivity..........................................................................................................23
1
SQL statement examples.............................................................................................24
Example: Loading from MySQL databases................................................................25
Example: Load a table from an Access Database into Anaplan.................................27
Example: Load an Excel Spreadsheet into Anaplan...................................................31
FAQ..................................................................................................................................33
What about Mac users, Linux and Unix operating systems?......................................33
Example of a .sh file for use on a Mac.......................................................................33
Can “.zip” files be used to speed up the upload ?.......................................................33
Can the source file name be changed, yet still use the same import definition?........34
Can relative rather than absolute paths be used for the source file?...........................34
Is it possible to close the window after running a Windows batch file?.....................35
Is it possible to list the available import definitions, actions, files, models etc ? ......35
Anaplan Connect batch file does not run successfully. Why not?..............................35
Where does Anaplan Connect run Java from?............................................................36
Why cannot Anaplan Connect communicate with the Anaplan servers when my browser can?.......................................................................................................36
Why do I get a “HTTP status 407: Proxy Authentication Required” message?.........36
2
Introduction to Anaplan Connect
What is it for?
Anaplan Connect lets you automate the process of loading data into Anaplan by running a batch file from your local machine (otherwise known as .bat files). A batch
file is a file that runs a program when you double-click on it in Windows Explorer.
For example, you might have a batch file named RunMyImport that loads a text file Europe.txt up to the Anaplan server. If you have a scheduling program, you could
set it to run this RunMyImport.bat program automatically at, say, 5.00am on a Monday morning, so you do not have to go in early! It takes a bit of setting up but can be
very useful!
To Install Anaplan Connect
1. Start by installing Java, if it is not already there on your PC. Download a free copy of Java from http://java.com/en/download and click Free Java Download.
Follow the instructions for installation. Java 6 is recommended.
2. Download the latest version of the Anaplan Connect software. E.g. anaplan-connect-[version].zip and then unzip it into a suitable folder on your local machine
such as C:\Anaplan Connect.
Supported authentication methods
Anaplan Connect supports the following authentication methods. One of them must be used to authenticate when executing operations using Anaplan Connect.
Username and password based authentication
The same credentials used to log in to Anaplan can be used to authenticate using Anaplan Connect. Please refer to the “-user” option in the “List of Available
Operations” table below.
Certificate-based authentication
Certificates generated by Anaplan can be used to authenticate using Anaplan Connect. To obtain a valid certificate, go to the Certificates tab under My Profile and
click the “Create New Certificate” button. You will be prompted to download a newly-generated certificate (a CER file). Save this file locally on your file system.
There are two ways to instruct Anaplan Connect to use a downloaded certificate for authentication:
3
1. the location of the downloaded certificate (CER file) can be provided directly to Anaplan Connect using the “-certificate” option; or
2. the downloaded certificate can be stored in a password-protected Java KeyStore under a chosen alias. Once this is done, the path to the KeyStore, the
KeyStore password, and the certificate alias should be provided to Anaplan Connect using the “-keystore", "-keystorepass" and "-keystorealias" options,
respectively. Please refer to the keytool documentation for instructions on how to import a certificate into a Java KeyStore.
4
To import a text file and run the import
First run the import manually
To set up the import definitions, the normal import routines must be run once as a ‘dry run’, before you can automate it using a batch file.
1. Open a module and choose Data>Import
2. Choose Upload New File and browse for the source .txt, .csv or .zip file.
3. At the preview screen, check the header row and first data row are correct, then click Next.
4. Check the mapping of each dimension in turn, then Run Import.
Then copy and edit a Batch file to automate this process
A sample batch files is provided for you in the examples subfolder. E.g. C:\Anaplan Connect\examples\example.bat. This batch file uploads a text file then runs a
pre-defined Anaplan import.
1. Copy the sample batch file named “example.bat” in the examples folder C:\Anaplan Connect\examples to the parent folder. In this example the parent folder is
C:\Anaplan Connect, but could be elsewhere depending on where you installed Anaplan Connect.
2. Right-click and Rename the batch file to a name of your choice E.g. RunMyImport
3. Right-click and Edit the batch file. Only edit the highlighted variables shown. (Note that straight double quotes are needed " "as typed in notepad, not curly
double quotes “ ” which you get when typing in Word).
@echo off
rem This example loads a source text file and runs an Anaplan import into a module. For details of how to configure this script see doc/User Guide.html
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Europe P&L.txt" -put "C:\testdata\Europe P&L.txt" -import "P&L from Europe P&L.txt" -execute -output "C:\testdata\Europe P&L Errors.txt"
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% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
cmd /c %Command%
pause
5
4. To run the import, open the folder C:\Anaplan Connect in Windows Explorer and double-click on the batch file RunMyImport.bat
6
Where to find the variables to use in the batch file
AnaplanUser
The Anaplan user name is entered when you first log on to Anaplan. E.g. “andrewgould@hotmail.co.uk”. If you leave out the password it will prompt you when the
batch file is run. Optionally, though less secure, you can enter it in the batch file:
set AnaplanUser="andrewgould@hotmail.co.uk:my password".
This may be necessary if the batch file is scheduled to be run at a time when the user is not present, but remember to secure the location of the batch file so as not to
reveal your password to others.
Workspace ID, Model ID
The workspace ID and Model ID are found at the top left corner of the screen.
7
AnaplanUser
Workspace ID Model ID
-file
The file ID, such as -file Europe P&L.txt, is found by opening the target module in Anaplan and choosing Data> Import. This is the name of the file that was imported
when the import definition was set up for the first time.
-put
The full path name of the source text file is required. E.g. -put "C:\testdata\Europe P&L.txt". It is also possible to refer to a path relative to where the batch file is
located: E.g. -put “examples\ Europe P&L.txt” will look for a file in the Anaplan Connect examples folder.
-import
The import ID, such as -import "P&L from Europe P&L.txt" is found on the Settings tab under Actions.
-execute
Executes the import(s). No variables are required with this command.
8
File ID
Import ID
-output
The full path name of the output error log file is required, such as: -output "C:\testdata\Errors.txt". Any errors occurring in the import will be recorded in this text
file.
9
List of Available Operations
This table shows the full list of commands that can be used in the Operation line of the batch file. The abbreviated syntax can be used to save typing: E.g. –x instead of
–execute. Some operations are followed by a variable. E.g. -put "C:\testdata\Europe P&L.txt" or –p "C:\testdata\Europe P&L.txt"
Syntax Abbreviated
Syntax
Followed by a variable, if
applicable, such as the file
pathname etc
What it does
-help -h display this help
-debug -d Show more detailed output
-quiet -q Show less detailed output
-service -s https//:api.anaplan.com/ API service endpoint (defaults to https://api.anaplan.com/) In the command line,
-service https://api.uat.anaplan.com/ would run Anaplan on the UAT server
-user -u username:password Anaplan user name and optional password in the format “username:password”
-certificate -c Pathname on local machine Path to user certificate used for authentication (an alternative to using a key store)
-keystore -k Pathname on local machine Path to local key store containing user certificate(s) for authentication
-keystorepass -kp Password Password for the key store. If this option is not provided, and the file
~/.anaplan/api-client/keystore-access.txt exists (where ~ is the user’s home directory),
the password will be read and decoded from the contents of this file. Otherwise, the
user will be prompted for a password.
The encoding used for obfuscation is the URL-encoded form of the result of taking the
exclusive-or of each of the characters in the password and the value 129.
-keystorealias -ka Alias Alias of the public certificate in the specified key store
-via -v Proxy URL Use specified proxy
-viauser -vu username:password Pass credentials to authenticating proxy
-workspaces -W List available workspaces
-workspace -w Workspace name or ID Select a workspace by ID or name
-models -M List available models in selected workspace
-model -m Model name or ID Select a model by ID or name
-modules -MO List available modules in selected model
-module -mo Module name or ID Select a module by ID or name
-views -VI List available views in selected module
-view -vi View name or ID Select a view by ID or name
-files -F List available files on the Anaplan server in selected model
-file -f File name on Anaplan server Select a server file by ID or name
-get -g Pathname on local machine Download specified file
-gets Write specified server file to standard output.
-getc Write tab-separated sever file to standard output
-put -p Pathname on local machine Upload specified file
-puts Upload to specified server file from standard input
-putc Upload to specified server file from tab-separated standard input
-imports -I List available imports in selected model
-import -i Import name or ID Select an import by id/name
-exports -E List available exports in selected model
10
-export -e Export name or ID Select an export by id/name
-actions -A List available actions in selected model, such as import, export, delete actions etc
-action -a Action name or ID Select a saved action E.g. delete items from a list
-processes -P List available processes in selected model
-process -p Process name or ID Select a process by id/name
-locale -xl ISO language & country code
separated by underscore eg
'en_US'
Specify locale to use when performing server operation
-connectorproperty -xc Property identifier and value
separated by colon. If value is ?,
prompt user
Specify import data source connection property (eg Salesforce credentials)
-mappingproperty -xm Dimension and vlaue separated by
colon. If value is ?, prompt user
Specify prompt-at-runtime import mapping value
-execute -x -execute will run the preceding -import, -export, -process or -action
-output -o Pathname on local machine Retrieve dump file for completed import
-emd Pathname on local machine Get meta-data for an export
When connecting to Anaplan's production server, the -service option need not be specified. Otherwise an API service endpoint URL is needed to supply to the
program using this option.
A username and password should be supplied for the service in question. If not supplied on the command line using -user, the program will prompt the user for them.
The program can be run with or without specifying -service and/or -user, and the -workspaces option. This will cause the program to connect to the service using the
supplied credentials, obtain a list of available workspaces, and dump them to the standard output in a tabulated form containing the ID and name of the workspace.
c6fc7db1bbccc542a02aa5f545ef8c7a Workspace 1
1cad4e5bced45025612a6c51efa562b0 Workspace 2
JDBC Operations
Syntax Followed by variable if applicable What it does
-loadclass classname Load a Java class. This is used to load the jdbc driver, for example “com.mysql.jdbc.Driver”.
-loadclass is only needed for older (pre JDBC 4.0) drivers
-jdbcurl connectionurl URL for the jdbc query to connect to
-jdbcuser username:password Specify jdbc username and/or password. Username:? will prompt for password
-jdbcproperty name:value Optional, for setting any other properties. If value is ? then prompt for the value
-jdbcquery “SELECT * FROM mytable” or
@myquery.sql
SQL statement for the data to query and upload – or location of file containing query
prefixed by '@'
-jdbcfetchsize number Insert before a query to tell the jdbc driver the number of records to transfer at a time.
11
Example: Update a list then import the data using the same source file
The process is almost identical to the last example, but in this case two import IDs need to be run: First update the list(s), then import the data.
In this example, the source text file Europe P&L.txt contains a series of profit-centres and the data for their budgets by month by income statement line. Since some of
the profit-centres are new, the Organization list needs updating from Europe P&L.txt first before importing the data from the same text file into the Income Statement
module.
As before, the import of new profit-centres into the Organization list and the import of data into P&L module both need to have been run manually beforehand to set up
the import definitions. To import into a list, open a list on the Settings tab, choose Import , highlight a pre-loaded file (or upload a new file) and press Select.
The name of both the list import and the module import can be found under Settings>Actions>Imports.
Once this has been done, the batch file can be set up to automate this process: Note how the operation line at the bottom of the batch file is loading up just the one text
file, but is running two imports off the back of it: One updates the Organization list and the second imports the data.
Just the editable part of the batch file is shown here with the changes highlighted. The non editable part of the batch file below ‘end of settings’ row is identical to the
previous examples and needs to be included.
@echo off
rem This example loads a source text file and uses it to update the organization hierarchy then import the data.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Europe P&L.txt" -put "C:\testdata\Europe P&L.txt" -import "Organization from Europe P&L.txt" -execute -output "C:\testdata\Organization
Errors.txt" -import "P&L from Europe P&L.txt" -execute -output "C:\testdata\Europe P&L Errors.txt"
rem *** End of settings - Do not edit below this line ***
12
Import name for a list
Import name for a Module
Example: Upload a file, but do not run the import
This example uploads the Europe P&L.txt file onto the Anaplan server, but does not run any imports. The data will not change in any modules until the import is run in
Anaplan (Open the module, then choose Data>Import>Select>Run Import)
@echo off
rem This example uploads a source text file but does not run the import
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Europe P&L.txt" -put "C:\testdata\Europe P&L.txt"
rem *** End of settings - Do not edit below this line ***
Example: Run the import from a pre-loaded file
This example runs the import in Anaplan based on a text file named Europe P&L.txt that has already been preloaded onto the Anaplan server. Outputs any errors to
Europe P&L Errors.txt.
@echo off
rem This example runs an Anaplan import into a module from a pre-loaded text file
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-import "P&L from Europe P&L.txt" -execute -output "C:\testdata\Europe P&L Errors.txt"
rem *** End of settings - Do not edit below this line ***
Example: Upload 2 files and run 2 imports
This example uploads two text files and imports the data into two different modules.
@echo off
rem This example uploads two text files imports the data into two modules.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Europe P&L.txt" -put "C:\testdata\Europe P&L.txt" -import "P&L from Europe P&L.txt" -execute -output "C:\testdata\Europe P&L Errors.txt"
-file "Europe Employees.txt" -put "C:\testdata\Europe Employees.txt" -import "Employee Details from Europe Employees.txt" -execute -output
"C:\testdata\Employee Errors.txt"
13
rem *** End of settings - Do not edit below this line ***
Example: List workspaces using certificate-based authentication
This example displays the list of accessible workspaces for the authenticated user.
@echo off
rem This example lists a user's workspaces
set Keystore="/path/to/keystore.jks"
set KeystoreAlias="alias"
set KeystorePassword="password"
set Operation="-W"
rem *** End of settings - Do not edit below this line ***
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
set Command=.\AnaplanClient.bat -s %ServiceLocation% -k %Keystore% -ka %KeystoreAlias% -kp %KeystorePassword% %Operation%
@echo %Command%
cmd /c %Command%
pause
14
Example: Export data to a text file or Excel file
First do a dry run, in which you run the export manually and create the export definition, then edit the batch file to automate this process.
First run the export manually
1. Open the relevant module, choose Data>Export.
2. Click on Save Export Definition and give it a name. Typically name the Export ID after the file you are exporting to: E.g. Income Statement.xls. This will be used
as the name of the Export ID when editing the batch file.
3. Run the export and Save the file onto your local PC in a known path such as C:\testdata\Income Statement.xls.
Then edit and run the batch file
1. Copy the sample batch file named “export-windows” in the examples folder C:\anaplan-connect-1-0-44\examples to the parent folder C:\anaplan-connect-1-0-44.
2. Right-click and Rename the batch file to a name of your choice E.g. RunMyExport
3. Edit the batch file as shown below:
@echo off
rem This example exports the data from a module to a text file, then downloads it to your hard drive.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-export "Income Statement.xls" -execute -get "C:\testdata\Income Statement.xls"
rem *** End of settings - Do not edit below this line ***
15
Export ID
4. Run the batch file: Double click RunMyExport. If the export file already exists, it will be overwritten when the batch file is run.
16
Example: Run the export, but do not download
This example runs the export on the Anaplan Server but does not download the text file or Excel file to your local hard drive.
@echo off
rem This example runs the export but does not download it to your local PC.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-export "Income Statement.xls" -execute
rem *** End of settings - Do not edit below this line ***
Example: Download the results from an export that has already been run
This example downloads the data to your local hard drive provided you have already run the export.
@echo off
rem This example downloads the data to your local hard drive provided you have already run the export.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-export "Income Statement.xls" -get "C:\testdata\Income Statement.xls"
rem *** End of settings - Do not edit below this line ***
17
Example: Running a model to model import
This example shows how to run an import that pulls in data from another model. In this example, Data is imported from a module named Installation Sales in a model
named Model2, into the P&L module in a model named MyBudget Model.
First run the import manually
1. First run the import manually: Data >Import>Connect to Anaplan Model and select a module (or list) as the source of the import.
2. Take note of the Import ID that will be used in the batch file. E.g. P&L from Model2 / Installation Sales .
3. Click Run Import.
18
Import ID
Then edit and run the batch file
This example runs a model to model import within Anaplan, transferring data from the Installation Sales module in Model2 to the P&L module in MyBudgetModel.
@echo off
rem This example runs a model to model import within Anaplan
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-import "P&L from Model2 / Installation Sales" -execute
rem *** End of settings - Do not edit below this line ***
19
Note there is a space before and after the /
Example: Deleting Items from a List
This example shows how to automate the process of deleting items from a list based on a criteria.
For example, you might want to automatically delete items in a list that are now obsolete. The screenshot below shows an example in which customers are deemed to
be obsolete if the rating is less than or equal to 2. The ‘Obsolete’ line item is of Boolean data type and has been set up to be a formula:
Obsolete = Rating <= 2
Line items of Boolean data type that only have the dimensionality of the list, can be used as a criteria to determine which items to delete. In this example,customers 6
and 7 are deemed to be obsolete because they have a raying less than or equal to 2..
On the Settings tab, choose Actions>New Action>Delete from List using Selection.
Select the list such as Customers and the line item that contains the selection criteria E.g. Customer Rating.Obsolete
20
The action will appear under ‘Other Actions’ at the bottom of the list:
The action can be used in the batch file as shown:
@echo off
rem This example deletes obsolete customers from a list
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-action "Delete from Customers Using Obsolete" -execute
rem *** End of settings - Do not edit below this line ***
21
Action to delete obsolete customers
After running the action, just the customers that have a rating of 3 or higher remain.
Note: Due to the difficulty of leaving ‘orphaned’ subtotals, the “Delete from list using Selection” action is not allowed on lists that have subtotals. However, the
delete action does work on lists that contain parent hierarchies and top-level items, just so long as the bottom level hierarchy does not contain summary items itself.
Passing run-time parameters
Some actions require extra information. For example, having the server retrieve data from Salesforce will require Salesforce credentials if they have not previously
been provided and saved against the account in Anaplan. Or an import definition for a module might have a dimension mapped to “Prompt at Runtime”. If you try and
run such an import you will likely end up with a message like:
The operation failed.
Please specify the following parameters:
-mappingproperty Version:?
If you provide the suggested parameter (-mappingproperty Version:?) then the client program will prompt the user for a value when the program is run. If you replace
the question mark with something else then the user will not be prompted, and that value will be passed for that parameter.
Salesforce imports need the following values specified:
-connectorproperty SALESFORCE_API/INSTANCE:TEST (if the Salesforce sandbox instance is to be used rather than the Salesforce production instance)
-connectorproperty SALESFORCE_API/USERID:?
-connectorproperty SALESFORCE_API/PASSWORD:?
-connectorproperty SALESFORCE_API/SECURITY_TOKEN:?
22
Replace some or all of the question marks with concrete values as necessary, although if run from a batch file the caveats about user credentials in the next section also
apply here.
To schedule an import or export
A batch file that runs the import or export can be scheduled to run at a specific time, as a one-off operation, or weekly/monthly. The scheduler is not part of Anaplan
Connect, and the scheduling program and set-up depends on your operating system. The computer will need to be left running while the user is absent for the scheduler
to work.
In the batch file, the password will need to be included in the Anaplan user name as shown, but make sure the location is totally secure as batch files can be read by
anybody.
set AnaplanUser=andrewgould@hotmail.co.uk:mysecretpassword
Optionally, you may want to remove the pause command at the end of the batch file. The pause command leaves the messages on the screen that records what the
batch file has done.
Scheduler for Windows XP
For example, here are the steps involved on a Windows XP operating system to schedule an import on a specific day and time, once a month:
Start>All Programs>Accessories>System Tools>Scheduled Tasks>Add Scheduled Tasks>Next>Browse
C:\anaplan-connect-1-0-44/RunMyImport
Monthly>Next>The First Monday at 05.00> Enter name & password for the PC >Finish
Scheduler for Windows 7
The Scheduler in Windows 7 is almost the same as XP:
Start>All Programs>Accessories>System Tools>Task Scheduler>Create Basic Task> Name the task>Next>Set when to trigger the task>Next>Select 'Start a
program' and browse to 'C:\anaplan-connect>RunMyImport>Next>Finish
JDBC Connectivity
Provided you have the appropriate jdbc driver, you can link directly into Anaplan from databases such as Oracle, Access, MySQL, MS SQL Server or from Excel.
Both lists and data can be imported into Anaplan in this way, and combined with a scheduler can be updated on a regular basis, automatically.
set Operation=-loadclass "com.mysql.jdbc.Driver" -jdbcurl "jdbc:mysql://localhost/apcustomer" -jdbcuser ?:? -file "Upload from jdbc" -jdbcquery "SELECT * FROM
Customers"
The additional parameters are as follows:
-loadclass classname Load a Java class.
-jdbcurl connectionurl URL for the jdbc connection
-jdbcuser username:password Specify jdbc username and/or password – see below for options.
23
-jdbcproperty name:value Optional, for setting any other properties. If value is ? then prompt for the value
-jdbcquery “SELECT * FROM mytable” SQL statement for the data to query and upload
-jdbcquery @myquery.sql location of file containing SQL statement for the data to query and upload
Note that -loadclass is only needed for older (pre JDBC 4.0) drivers - the MySQL driver does not need it, nor should the ODBC bridge driver .
Handling for username/passwords is fairly flexible, since some jdbc sources may not need these. If no user name or password is required by the source then omit the
-jdbcuser parameter
-jdbcuser michael Username = michael, no password required
-jdbcuser michael:? Username = michael, prompt for password
-jdbcuser ?:? Prompt for both username and password
-jdbcuser michael:mypassword Username = michael, password = mypassword
You can set additional properties if needed using -jdbcproperty. The property name can also be ‘user’ or ‘password’, and these are equivalent of setting these properties
using -jdbcuser, but also allow for a username containing “:”
-jdbcproperty user:fred:smith
-jdbcproperty password:?
SQL statement examples
SELECT * FROM table WHERE column1 > 0 Selects all the columns in the table but omits any row that is 0 in column1
SELECT * FROM table WHERE column1 > 0 OR column2 > 0 Selects all the columns in the table but omits any row that is 0 in both column1 and column2
SELECT * FROM versions WHERE versions = 'Actual' Selects all the columns but only the rows that have the value 'Actual' in the versions column
For samples of SQL queries, there are a number of publicly available internet sites, such as: http://www.w3schools.com/sql/default.asp
24
Example: Loading from MySQL databases
1. For a MySQL database, a JDBC MySQL driver needs to be downloaded. The .jar file that contains the driver needs to be downloaded into the Anaplan Connect lib
folder. For example mysql-connector-java-5.1.18-bin.jar has been copied into the lib folder as the driver for version 5.5 of MySQL database. A direct JDBC driver
(from a .jar file) is always preferable over the JDBC-ODBC bridge, which should only be used if no JDBC driver is available.
2. Edit and rename the example.bat file in a similar manner to the one shown below. The example loads into Anaplan all the records from a table named mytable in a
database named test. In this example, you would be prompted for both the database password (jdbc password) and the Anaplan password as neither are contained
in the batch file. -jdbcurl "jdbc:mysql://localhost/test" . In this case the first two parts (jdbc:mysql:) remain fixed, the final part is the hostname and name of the
database.
@echo off
rem This example loads a table from a SQL database into Anaplan using a jdbc driver.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "mytable from MySQL database" -jdbcurl "jdbc:mysql://localhost/test" -jdbcuser "root:?" -jdbcquery "SELECT * FROM mytable"
rem *** End of settings - Do not edit below this line ***
25
Driver for MySQL database
-jdbcuser "root:?" This is the database username, in this example ‘root’. The suffix of :? means prompt for the database password at runtime.
3. In Anaplan, open a module and choose Data>Import. The table from the MySQL database will appear as a source. Select this and run the import through as per
usual.
4. The batch file can now be edited (optional) so that import is run as part of the operation line.
26
Example: Load a table from an Access Database into Anaplan
To get Anaplan Connect to link directly to a database using a jdbc driver, the only line that needs changing in the .bat and .sh files is the Operation one. This example
uploads a table from an Access database to Anaplan.
1. The first thing to do is set up an ODBC connection. For example in Windows XP choose Start>Control Panel>Administrative Tools>Data Sources(ODBC)
For windows 7 choose Start>Control Panel>System and Security>Administrative Tools>Data Sources(ODBC)
The ODBC you want is a System DSN using the Microsoft Access Driver.
2. Point the driver to the correct database: Click Configure then Select the correct database path such as c:\msoffice\access\samples\northwind.mdb
27
3. Then edit the .bat file so the operation line is similar to the example below. This example loads into Anaplan a table named ‘Customers’ from an access database
‘Northwind.mdb’. Access and Excel can use the standard JDBC/ODBC driver that comes with java, without having to copy a driver (.jar) into the Anaplan
Connect Lib folder.
@echo off
rem This example loads a table from an Access database into Anaplan using a jdbc driver.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Customers in Northwind Database" -jdbcurl "jdbc:odbc:NorthwindDB" -jdbcquery "SELECT * FROM Customers"
rem *** End of settings - Do not edit below this line ***
4. Choose Data> Import and the Customer table appears as a data source: ‘Customers in Northwind Database’.
5. Select the import named ‘Customers in Northwind Database’ and proceed with the import. If the header row is not correct, it may be changed by clicking Edit and
selecting the row number for the headers and for the first data row.
28
6. Once the import has been run once manually, the operation line can be amended so that it runs the import as shown below. This means that the process can be fully
integrated: You can upload the table from the database, update the customer list and import the data all in one operation. Using a scheduler, this can be done
automatically on a regular basis.
@echo off
rem This example loads a table from an Access database into Anaplan using a jdbc driver.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Customers in Northwind Database" -jdbcurl "jdbc:odbc:NorthwindDB" -jdbcquery "SELECT * FROM Customers" -import "Customers
from Customers in Northwind Database" -execute -import "Customer Details from Customers in Northwind Database" -execute
rem *** End of settings - Do not edit below this line ***
29
7. The number of items added, renamed and the data cells updated is all shown in the dialog box.
8. The customer list and Customer Details module is updated:
30
Example: Load an Excel Spreadsheet into Anaplan
The example below shows an example of loading a sheet from an Excel spreadsheet into Anaplan.
1. Set up an ODBC driver pointing to the excel file:
In Windows XP, choose Start>Control Panel>Administrative Tools>Data Sources(ODBC)>System DSN
In Windows 7, choose Start>Control Panel>System and Security>Administrative Tools>Data Sources(ODBC)> System DSN
2. Click Add and choose Microsoft Excel Driver. Name the Data source.E.g. Sales Budget Data and Select Workbook. Browse for the workbook you want to
import E.g. C:\Testdata\Sales Budget.xls
31
3. Set up the batch file as shown below. This example loads Sheet 3 from the Sales Budget.xls.
@echo off
rem This example loads a table from an Access database into Anaplan using a jdbc driver.
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Sales Budget.xls" -jdbcurl "jdbc:odbc:Sales Budget Data" -jdbcquery "SELECT * FROM [Sheet 3$]"
rem *** End of settings - Do not edit below this line ***
4. Having loaded the Excel sheet into Anaplan, it can now be used as the source for an import into a list or a module. Open the target module, choose Data>Import
and select the data source named Sales Budget.xls. Then click Select and follow the import wizard.
32
FAQ
What about Mac users, Linux and Unix operating systems?
Instead of the batch files, Mac, Linux and Unix users will need to copy and edit a shell script example.sh which is to be found in the examples folder.
Copy this into the parent folder containing Anaplan Client.sh and save it under a new name such as RunMyImport.sh. Edit it using the TextEdit application. To run a
shell script on a Mac, right-click on the RunMyImport.sh file, Open With>Other and browse for Utilities>Terminal. If this is greyed out, you will need to choose
Enable>All Applications and then choose Terminal.
To run the shell script, right-click, Open With>Terminal
To edit the shell script, right click, Open With>TextEdit
The syntax is slightly different but otherwise it is a very similar process.
Example of a .sh file for use on a Mac
For Mac users, ‘.sh’ files must be used instead of ’.bat’ files This example loads a table named sample from a database named Test_DB into Anaplan.
#!/bin/sh
# This example loads a table named sample from a database named Test_DB into Anaplan
AnaplanUser=iain.duffield@anaplan.com
WorkspaceId="'Iains Workspace'"
ModelId="'Anaplan Connect'"
Operation="-file 'Upload from MySQL jdbc' -loadclass 'com.mysql.jdbc.Driver' -jdbcurl 'jdbc:mysql://localhost/Test_DB' -jdbcuser 'iain.duffield:?' -jdbcquery 'SELECT *
FROM sample'"
#____________________________ Do not edit below this line ______________________________
if [ "${AnaplanUser}" ]; then
Credentials="-user ${AnaplanUser}"
fi
Command="./AnaplanClient.sh ${Credentials} -workspace ${WorkspaceId} -model ${ModelId} ${Operation}"
echo "${Command}"
sh -c "${Command}"
Can “.zip” files be used to speed up the upload ?
No point. Anaplan Connect already zips the source text files as part of the upload process, making the use of .zip files unnecessary. For this reason referring to zip files
in the source file is not supported.
33
Can the source file name be changed, yet still use the same import definition?
Yes. For example the batch file below uses a new file named “New Europe P&L.txt”. Provided it is in the same format as the original, the import will still work. Note
how the File ID and Import ID remain unchanged. Effectively, it copies “New Europe P&L.txt” into the Europe P&L.txt file on the Anaplan server as part of the
upload.
@echo off
rem This example loads a source text file and runs an Anaplan import into a module. For details of how to configure this script see doc/User Guide
set AnaplanUser="andrewgould@hotmail.co.uk"
set WorkspaceId="AndrewGouldltd"
set ModelId="MyBudgetModel"
set Operation=-file "Europe P&L.txt" -put "C:\testdata\New Europe P&L.txt" -import "P&L from Europe P&L.txt" -execute -output "C:\testdata\Europe P&L Errors.txt"
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% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
cmd /c %Command%
pause
Can relative rather than absolute paths be used for the source file?
Yes. It is possible to refer to a path relative to where the batch file is located: E.g. -put “examples\ Europe P&L.txt” will look for a file in the examples folder
immediately beneath the Anaplan Connect folder (the folder where the AnaplanClient.bat and your customised batch files are run from). I.e. Anaplan
Connect/examples/Europe P&L.txt
@echo off
rem This example loads a source text file and runs an Anaplan import into a module. For details of how to configure this script see doc/User Guide
set AnaplanUser=”andrewgould@hotmail.co.uk”
set WorkspaceId=”AndrewGouldltd”
set ModelId=”MyBudgetModel”
set Operation=-file “Europe P&L.txt” –put “examples\Europe P&L.txt” –import “P&L from Europe P&L.txt” –execute –output “examples\Europe P&L Errors.txt”
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% -workspace %WorkspaceId% -model %ModelId% %Operation%
34
@echo %Command%
cmd /c %Command%
pause
Is it possible to close the window after running a Windows batch file?
Yes, just remove the pause command from the end of the batch file.
Is it possible to list the available import definitions, actions, files, models etc ?
Yes, just set the operation to be – imports as shown in the batch file below. This lists all the import definitions that are available in a given model. The list of imports
and exports is also available in Anaplan under Settings>Actions.
Similarly, to list the available file IDs (on the Anaplan server) use –files, for models use –models, for workspaces use –workspaces and for actions, use –actions.
@echo off
rem This example lists all the available imports.
Set AnaplanUser=”andrewgould@hotmail.co.uk”
set WorkspaceId=”AndrewGouldltd”
set ModelId=”MyBudgetModel”
set Operation=-imports
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% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
cmd /c %Command%
pause
Anaplan Connect batch file does not run successfully. Why not?
If the batch file appears to run OK, but numbers do not appear to come through into Anaplan, try changing page, refreshing the browser or closing and re-opening the
target module. It may be that the target module was already open when you ran the batch file. It needs some event to trigger a contact with the Anaplan server, which
will then refresh the view with the latest data.
35
Check the syntax: For example check that variables such as the file name are enclosed in straight double quotes are needed “ “as typed in notepad, not curly double
quotes “ ” which you get when typing in Word. In model to model imports, make sure there is a space before and after the / in the syntax -import “Target Module
from Source Model / Source Module”.
If you are deleting items from a list, check that the list does not contain summary items or subtotals. Such lists cannot use the delete action owing to the difficulty of
dealing with “orphaned” subtotals which no longer have any children. Lists that have parent hierarchies or top level items can use the bulk delete action, provided that
the list that you are editing does not have subtotals in its own right.
If you are performing a SQL query from a Windows machine with the -jdbcquery option, you need to be careful if using the percent character, eg as the wildcard
character in a pattern for the like operator. The Windows command processor may attempt to perform variable substitution on an expression like '%a%', even
though no variable a has been defined, resulting in an empty pattern. The percents can be escaped by doubling them up eg like '%%a%%'.
Where does Anaplan Connect run Java from?
The first place that AnaplanClient.bat looks for a Java install is to look at the JAVA_HOME value. Installing Java should have set JAVA_HOME for you, so you
should not normally have to do anything further. To check to see that this is set up: Start>Right-click on My Computer>Properties>Advanced>Environment
Variables>New
Variable name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre6
The path you need is the folder one level up from the “C:\Program Files\Java\jre6\bin” folder containing all the Java programs.
If JAVA_HOME is not set, then the Windows Registry will be queried to find the location of the most recent version of Java.
Why cannot Anaplan Connect communicate with the Anaplan servers when my browser can?
In some – mostly corporate – environments, all access to the internet is channelled through a proxy server. If this is the case, Anaplan Connect might not necessarily be
able to detect the location and type of the proxy in use. If you know these details you can specify them using the -via option – this accepts a URL which must start with
either http: or socks:.
If you do not know the settings or they are liable to change frequently, Anaplan Connect can make use of a free, open-source, third-party library called Proxy Vole if it
is available.This looks for the settings in different places depending on what operating system it is installed on, which browser is the default browser, etc. It is not
included with Anaplan Connect distribution as it is distributed under a slightly different license, but it can be downloaded from http://code.google.com/p/proxy-
vole/ - unzip the binary version and place the jar file in Anaplan Connect's lib directory, and Anaplan Connect will use it automatically.
Why do I get a “HTTP status 407: Proxy Authentication Required” message?
Some proxy servers require a username and password to be provided in order to access the internet through them. These can be specified using the -viauser option.
There are several different authentication schemes – Anaplan Connect supports basic and digest authentication out of the box, but for NTLM authentication it is
recommended that the open-source JCIFS library be installed. It is not included with Anaplan Connect distribution as it is distributed under a slightly different license,
but can be downloaded from http://jcifs.samba.org/ - get the latest 1.3.x jar file and install it in Anaplan Connect's lib directory.
36

Navigation menu