Informatica Cloud and ODBC Connection to Import PostgreSQL

stefangradin

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.