1. Introduction I have written this guide to hopefully help save some time as I encountered a few, less straight forward, issues when setting this up. There is a guide describing Anaplan Connect, so please regard this as a complement to that guide on how to set up a JDBC connection. I will use PostgreSQL as an example throughout this guide, but this should work with any JDBC source. This guide will step you through how to set up and use Anaplan Connect to import data from a PostgresSQL table. Please note I am using the PostgreSQL JDBC driver in this example as the JDBC:ODBC bridge is not supported in Java 8+. I will step through the parameters used in the example script to explain what action they perform and how to use them in the script. I wrote the below script using Notepad++, but any text editor will work. You will need to save the file as a .bat file that you will run to kick off the import. You will need to store the .bat file in the root of the Anaplan Connect directory. 2. Prerequisites You will need to start off with a csv version of the import that you are setting up. Easiest would be to export the PostgreSQL table you are planning to set up the import for and use this for the first csv import. The csv import is done to create an import action that will be called from the .bat script. Please take extra care when setting up the import as the PostgreSQL import will need to be in tab format rather than comma separated. I did this by running the csv as comma separated import and then changing the import specification in Anaplan to be tabular. I am sure there are other ways of doing this, but this worked well for my purposes. After running the csv import, it is time to write the script. 3. Explanation of the batch script I will only focus on the parts that you will need to edit in the script. Above you will find the full script which is based on the example in the Anaplan Connect guide with a few additions. The “-import” and “-execute” commands are not in the Anaplan Connect user guide batch examples, but they will need to be included for the import to run. If left out you will still receive a message the import transferred data, but your target model will not have received any as you will not kick off the actual import action. Good luck!
... View more
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 more