[Start Here] Anaplan Connect 2.0 and JDBC Connectivity

AnaplanOEG
AnaplanOEG
in Best Practices

 

We are kicking off this series, focused on Anaplan Connect 2.0, with 3 articles:

  1. Start Here - all you need to know to get started on your Anaplan Connect 2.0 journey,
  2. Advanced Scripts - ready-to-use scripts to take your Anaplan Connect 2.0 further, and
  3. Integration with On-Prem integrations platforms - connect your Anaplan data to virtually any On-Prem and SaaS platforms

What is Anaplan Connect?

Anaplan Connect is a Java based command line utility that facilitates automation of integrations between Anaplan and data sources. Data sources, currently, supported by Anaplan Connect include flat files and databases (on-prem & cloud). Database connectivity is established via Java Database Connectivity (JDBC).

The following resources will help you get started with Anaplan Connect installation and implementation.

In this article, we will focus exclusively on integrating Anaplan with following databases using Anaplan Connect and JDBC:

  • SAP HANA Cloud
  • Snowflake
  • Azure SQLDB
  • Google Cloud BigQuery
  • Oracle (On-Prem)
  • Oracle Cloud Database

What is covered in this article?

Connectivity to both On-Prem & Cloud Databases via JDBC using Anaplan Connect is, essentially, the same.  The differences are in required JDBC libraries (.jar), how JDBC string is constructed and some nuances in SQL queries. We will present JDBC connection strings for databases listed above, required JDBC libraries, and any relevant tips.

What is NOT covered in this article?

We will not be presenting basics of Anaplan Connect, scripting, and authentication.

Please refer to resources above to get started on Anaplan Connect.

What pre-requisites do I need?

  • Knowledge of building Anaplan Connect scripts
  • Java 8 installed (or supported JDK. Please refer to Anaplan Connect documentation for Java compatibility).
  • Anaplan Connect 2.0 installed on a Windows/Linux/MacOS
  • Database access & connectivity information you wish to connect to
  • JDBC Driver(s)
  • Whitelisting of api.anaplan.com, auth.anaplan.com
  • Port 443 open for bi-directional communication
  • Anaplan account with either basic authentication (username/password) or CA Certificate.
  • Text editor (ex: Sublime Text, UltraEdit, etc…)

Deep Dive on Integration

Integrating with SAP HANA Cloud

In this section, we will cover connectivity to SAP HANA Cloud via JDBC using Anaplan Connect 2.0. We will begin by establishing and testing JDBC connectivity outside of Anaplan Connect, followed by configuring Anaplan Connect scripts to import data from a SAP HANA Cloud table into Anaplan.

Establishing JDBC connectivity

  • Download & copy latest jdbc driver for SAP HANA Cloud to <anaplan-connect>/lib directory.
  • Download root certificate ‘DigiCert Global Root CA’ from DigiCert.  You can download this certificate in pem format here.  Additional JDBC information can be found on SAP Help Portal here.
  • Add ‘DigiCert Global Root CA’ certificate to Java VM Keystore using following command.  Note:  You may need to log into linux/MacOS as root to perform this step.  Instructions to enable root user on a MacOs can be found here.

Linux/MacOs

keytool -import -trustcacerts -keystore $JAVA_HOME/jre/lib/security/cacerts -storepass <password> -alias DigiCertGlobalRootCA -import -file DigiCertGlobalRootCA.crt

Windows

keytool -import -trustcacerts -keystore "%JAVA_HOME%\jre\lib\security\cacerts" -storepass <password> -alias DigiCertGlobalRootCA -import -file DigiCertGlobalRootCA.crt

Ensure JAVA_HOME is set.  You will need your Java VM Keystore password.  Default value for Java VM Keystore is likely ‘changeit’.

  • Once certificate has been added to the keystore, we will build jdbc connection string. JDBC syntax for SAP HANA Cloud is:

jdbc:sap://<host endpoint>:<port>

  • You can obtain endpoint for your SAP HANA Cloud database in SAP HANA Database Explorer under database properties.  Port is standard HTTPS port, 443.

annejulie_0-1623181235320.png

  • Based on information from the picture above, JDBC string would look something similar to:

jdbc:sap://430f7d5a4c5866c.hana.trial-us10.hanacloud.ondemand.com:443

Test JDBC Connection

  • Once we create JDBC connection string to our SAP HANA Cloud database, we will test the connection using Java on the command line.  Syntax to test JDBC connection is:

java -jar <jdbc driver> -u User1,Password123 -n 12345678-abcd-12ab-34cd-1234abcd.hana.hanacloud.ondemand.com:443 -o encrypt=true -c "SELECT 1 FROM SYS.DUMMY";

  • Launch command prompt (Windows) or Terminal (MacOs) and change directory to location of <anaplan-connect>/lib directory.  You should already have your jdbc driver (ex: ngdbc-2.8.12.jar) in this directory. 
  • Using your database information construct a command line syntax similar to the one shown below, resulting in one row from the SQL query:

annejulie_1-1623181235325.png

  • Now that we have successfully established jdbc connection to an SAP HANA Cloud database and tested it, we are ready to configure Anaplan Connect script.

Configure Anaplan Connect Script

In this scenario, I have a database table in SAP HANA Cloud named “Accounts”. This table has data that will be used to populate a LIST (Accounts) and a Module (AccountDetails) in Anaplan. There is also an associated Anaplan Process we will execute from Anaplan Connect script. Anaplan Connect script will connect to SAP HANA Cloud table “Accounts” via JDBC and then will execute a process, procLoadAccountDetails.

Two files need to be configured for Anaplan & JDBC integration:

  • Jdbc-query.properties:  This file will contain JDBC connectivity information as well as SQL query.  Sample example-jdbc-query.properties file is available as a starter in <anaplan-connect>/examples directory.
  • Anaplan Connect script:  This script will reference the jdbc.properties file (instead of a flat file) to establish connection to the database and execute the SQL query.  Output from the query is, then, uploaded to a file (data source) on Anaplan platform.

Configure JDBC Properties

Following steps outline instructions to configure jdbc.properties and Anaplan Connect script.

  • Copy example-jdbc-query.properties file to <anaplan-connect> directory. 
  • Rename the file to something meaningful (ex:  jdbc_SAPHANACloud_Select_query.properties)
  • Depending on the type of authentication and OS being used, copy appropriate sample script from <anaplan-connect>/examples directory.  For this article, I’ll be using sample_basic_auth_import.sh.
  • Rename sample_basic_auth_import.sh to something meaningful (ex: jdbc_SAPHANACloud_accounts_select_basic_auth.sh).
  • We will edit the jdbc properties file providing required JDBC connection and SQL statement.   Update following variables in the jdbc properties file:

Jdbc.connection.url, jdbc.username, jdbc.password, & jdbc.query

annejulie_2-1623181235343.png

 

  • If you notice, the line for jdbc.params is commented out.  This is because my SELECT statement does not have any parameters (ex: values for a ‘where’ clause).

Anaplan Connect Script

  • We’ll create an Anaplan Connect script now to reference the jdbc properties file.
  • In a text editor, open your Anaplan Connect script you copied earlier to <anaplan-connect> directory.
  • Add following variables
    • ProcessName, jdbcproperties
  • Optional: Remove variable “ImportName”. You may choose to execute an Import Action instead of a Process. In such case, you may keep variable ImportName.
  • Provide following information:
    • AnaplanUser, WorkspaceId, ModelId, ProcessName, FileName, ErrorDump, jdbcproperties

annejulie_3-1623181235347.png

  • Modify the line “Operation =” to reference jdbcproperties variable and execute a process instead of an import action.

annejulie_4-1623181235349.png

  • Save your script and execute it from the command prompt.
  • Successful run will show database connection, SQL query execution, number of records transferred from the database to Anaplan, and results from Process execution.

annejulie_5-1623181235380.png

 

NOTE: when it comes to exports, we strongly recommend to use csv format. Other extensions like "xls'" might bring some issues.

 

Next section will present Anaplan Connect connectivity to Snowflake.

Integrating with Snowflake

Setting up Anaplan Connect script to integrate Anaplan with Snowflake is very similar to the steps outlined above for SAP HANA Cloud. Slight modification to jdbc properties file and Anaplan Connect script is all it takes. We will examine JDBC connection properties required to establish connection to a Snowflake database.

In this example, my snowflake database name is “AnaplanDB”, name of virtual Warehouse is “COMPUTE_WH”, and schema is “Public”. Similar to SAP HANA Cloud, I have a table named “Accounts” from which Anaplan Connect will extract data and import into a model via a process.

JDBC Driver

  • Download latest JDBC driver for Snowflake by following instructions provided here. Current version of JDBC driver is 3.13.4 (snowflake-jdbc-3.13.4.jar).
  • Copy JDBC driver to <anaplan-connect>/lib directory.

JDBC Connection String

Connection string (JDBC) for Snowflake is as follows:

"jdbc:snowflake://.snowflakecomputing.com/?"

Connection parameters include information such as database name, schema name, warehouse name, etc.  A comprehensive list of connection parameters for JDBC can be found here.

You can obtain <account_name> from the URL of your Snowflake account.        

annejulie_6-1623181235386.png

Based on information from the image, JDBC string for my Snowflake database would be something like:

"jdbc:snowflake://he34739.us-east-1.snowflakecomputing.com/ warehouse=COMPUTE_WH&db=AnaplanDB&schema=public"

JDBC Properties

  • Copy example-jdbc-query.properties file to <anaplan-connect> directory. 
  • Rename the file to something meaningful (ex:  jdbc_Snowflake_Select_query.properties)
  • Edit JDBC properties file created above and update values for
    • Jdbc.connection.url, jdbc.username, jdbc.password, & jdbc.query
  • Sample JDBC properties file is shown below

annejulie_7-1623181235430.png

Anaplan Connect Script

  • We’ll create an Anaplan Connect script now to reference the JDBC properties file.
  • In a text editor, open your Anaplan Connect script you copied earlier to <anaplan-connect> directory.
  • Add following variables
    • ProcessName, jdbcproperties
  • Optional: Remove variable “ImportName”. You may choose to execute an Import Action instead of a Process.  In such case, you may keep variable ImportName.
  • Provide following information:
    • AnaplanUser, WorkspaceId, ModelId, ProcessName, FileName, ErrorDump, jdbcproperties

annejulie_8-1623181235448.png

  • Modify the line “Operation =” to reference jdbcproperties variable and execute a process instead of an import action.

annejulie_9-1623181235450.png

  • Save your script and execute it from the command prompt.
  • Successful run will show database connection, SQL query execution, number of records transferred from the database to Anaplan, and results from Process execution. You may notice additional verbose information. If you choose to omit this information, you may experiment with JDBC connection parameter tracing.

annejulie_10-1623181235471.png

Integrating with Azure SQL Database

Setting up Anaplan Connect script to integrate Anaplan with Snowflake is very similar to the steps outlined above for SAP HANA Cloud.  Slight modification to JDBC properties file and Anaplan Connect script is all it takes.  We will examine JDBC connection properties required to establish connection to a SQL database on Azure.

In this example, my Azure SQL database name is “AnaplanDB”, on a server labeled “anaplandi”, and schema is “dbo”. 

annejulie_11-1623181235474.png

 

Similar to SAP HANA Cloud & Snowflake, I have a table named “Accounts” from which Anaplan Connect will extract data and import into a model via a process.

annejulie_12-1623181235477.png

JDBC Driver

  • Login in Azure Portal, select your database, and choose Connection strings from the left-hand pane.
  • Select JDBC on the right-hand pane.  You will notice jdbc connection string under JDBC (SQL authentication) and a link to download JDBC driver for SQL server.

annejulie_13-1623181235491.png

  • Alternatively, you may download JDBC driver for SQL server from the link here.
  • Once you download and unzip the driver zip file, select the jar file that corresponds to your Java version. I have Java 8 installed on my system, therefore, I will choose mssql-jdbc-9.2.1.jre8.jar.

annejulie_14-1623181235498.png

  • Copy JDBC driver (ex: mssql-jdbc-9.2.1.jre8.jar ) to <anaplan-connect>/lib directory.

annejulie_15-1623181235502.png

JDBC Connection String

Constructing JDBC string for Azure SQL database is very simple.  The connection string for your database is provided to you in Azure portal. Copy JDBC connection string provided in Azure portal under settings > Connection strings > JDBC.

annejulie_16-1623181235515.png

JDBC Properties

  • Copy example-jdbc-query.properties file to <anaplan-connect> directory. 
  • Rename the file to something meaningful (ex:  jdbc_AzureSQL_Select_query.properties)
  • Edit JDBC properties file created above and update values for
    • Jdbc.connection.url, jdbc.username, jdbc.password, & jdbc.query
  • Copy JDBC connection string from Azure portal and replace the value for jdbc.connection.url variable.
  • Sample JDBC properties file is shown below

annejulie_17-1623181235536.png

Anaplan Connect Script

  • We’ll create an Anaplan Connect script now to reference the JDBC properties file.
  • In a text editor, open your Anaplan Connect script you copied earlier to <anaplan-connect> directory.
  • Add following variables
    • ProcessName, jdbcproperties
  • Optional:  Remove variable “ImportName”. You may choose to execute an Import Action instead of a Process. In such case, you may keep variable ImportName.
  • Provide following information:
    • AnaplanUser, WorkspaceId, ModelId, ProcessName, FileName, ErrorDump, jdbcproperties

annejulie_18-1623181235552.png

  • Modify the line “Operation =” to reference jdbcproperties variable and execute a process instead of an import action.

annejulie_19-1623181235554.png

  • Save your script and execute it from the command prompt.
  • Successful run will show database connection, SQL query execution, number of records transferred from the database to Anaplan, and results from Process execution. annejulie_20-1623181235595.png

Integrating with Google Cloud (GCP) BigQuery

Setting up Anaplan Connect script to integrate Anaplan with Google Cloud Platform’s (GCP) BigQuery is similar to the steps outlined above for SAP HANA Cloud. Creating JDBC connection string has some additional steps.  We will present steps necessary to capture required information to create JDBC connection to GCP BigQuery.  Once JDBC connection string is defined, rest of the Anaplan Connect steps are similar to other JDBC data sources.

Additional information on Anaplan & GCP BigQuery integration can be found on community here.

In this example, my GCP BigQuery dataset name is “anaplandi”, on a project labeled “celtic-spider-206221”, and the table is “Accounts”. 

annejulie_21-1623181235597.png

Anaplan Connect will extract data (using SQL SELECT) from Accounts table and import into a model via a process.

GCP Administration

Before we create JDBC connection and Anaplan Connect scripts, we must perform couple of administration tasks on Google Cloud Platform to create a service account and enable BigQuery API. Creating a service account will allow us to authenticate to GCP via a downloadable private key (JSON file).

annejulie_22-1623181235598.png

 

  • In GCP, go to API & Services  Credentials  Service Accounts  Manage Service Accounts to create a service account.

annejulie_23-1623181235603.png

 

annejulie_24-1623181235605.png

  • Once you create the service account, you will need to generate a private key that will contain information about client_email, project_id, and private key.
  • Under “Actions” for the service account, select “Manage Keys”. From the drop down list, select “Create new key”.

annejulie_25-1623181235619.png

  • Select the downloadable file type (JSON/P12) and secure the file in a safe place.  You will need this as part of JDBC connection string.

annejulie_26-1623181235623.png

  • Next, we will need to enable GCP BigQuery API.
  • In GCP, go to API & Services > Dashboard and click on Enable API and Services.

annejulie_27-1623181235625.png

  • Search for and enable following APIs:
    • BigQuery API, Google Cloud Storage JSON API

annejulie_28-1623181235627.png

annejulie_29-1623181235630.png

  • You should see both APIs enabled in your dashboard.

annejulie_30-1623181235636.png

JDBC Driver

  • Download latest GCP BigQuery JDBC driver by Magnitude Simba from Google’s website.
  • Copy JDBC driver and related .jar files to <anaplan-connect>/lib directory.

JDBC Connection String

GCP BigQuery JDBC driver by Magnitude Simba uses OAuth2.0 protocol for authentication & authorization (via Oauth APIs).  In our example, we will use Google Service Account method.  Instructions to setup a Google Service Account can be found here.  Additional information on constructing jdbc connection string can be found on Simba documentation here.

You will need following information from GCP console to construct jdbc connection string to BigQuery:

  • ProjectId, OAuthServiceAcctEmail, OAuthPvtKeyPath (json/p12 file you downloaded earlier).
  • ProjectId can be found from list of projects in GCP console.

annejulie_31-1623181235639.png

  • You will find OAuthServiceAcctEmail under service accounts you created earlier.

annejulie_32-1623181235648.png

  • OAuthPvtKeyPath is the location of json/p12 file you created earlier.

Once you have above information, you can construct JDBC connection string using following syntax:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project_id>;OAuthType=0;OAuthServiceAcctEmail=<email address>; OAuthPvtKeyPath=<path_to_json/p12>;IgnoreTransactions=1;

Sample JDBC connection string may look something similar to:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=celtic-spider-206221;OAuthType=0;OAuthServiceAcctEmail=pmarpaka@celtic-spider-206221.iam.gserviceaccount.com;OAuthPvtKeyPath=/opt/bigquery/certs/celtic-spider-206221-6a4c6.json;IgnoreTransactions=1;

We will use this JDBC connection string in JDBC properties file next.

JDBC Properties

  • Copy example-jdbc-query.properties file to <anaplan-connect> directory. 
  • Rename the file to something meaningful (ex:  gcpbigquery-jdbc-query.properties)
  • Edit jdbc properties file created above and update values for
    • Jdbc.connection.url & jdbc.query
  • Since we’re using service account and OAuth credentials, we don’t need to provide username and password. Comment out variables jdbc.username and jdbc.password.
  • Using example shown above, construct jdbc connection string and paste it for variable jdbc.connect.url.
  • Sample JDBC properties file is shown below

annejulie_33-1623181235678.png

Anaplan Connect Script

  • We’ll create an Anaplan Connect script now to reference the jdbc properties file.
  • In a text editor, open your Anaplan Connect script you copied earlier to <anaplan-connect> directory.
  • Add following variables
    • ProcessName, jdbcproperties
  • Optional: Remove variable “ImportName”.  You may choose to execute an Import Action instead of a Process. In such case, you may keep variable ImportName.
  • Provide following information:
    • AnaplanUser, WorkspaceId, ModelId, ProcessName, FileName, ErrorDump, jdbcproperties

annejulie_34-1623181235689.png

  • Modify the line “Operation =” to reference jdbcproperties variable and execute a process instead of an import action.

annejulie_35-1623181235691.png

  • Save your script and execute it from the command prompt.
  • Successful run will show database connection, SQL query execution, number of records transferred from the database to Anaplan, and results from Process execution. 

annejulie_36-1623181235733.png

Integrating with Oracle database (On-Prem)

Setting up Anaplan Connect script to integrate Anaplan with On-Prem Oracle database is similar to the steps outlined for other JDBC sources above. Therefore, we will not be regurgitating the same information for Oracle database in this section. We will, however, provide you JDBC driver information and JDBC connection syntax required when connecting to Oracle database (On-Prem).

JDBC Driver

  • Download latest jdbc driver (ex: ojdbc8.jar) for your Oracle database version from Oracle here.
  • Copy jdbc driver to <anaplan-connect>/lib directory.

JDBC Connection String

JDBC connection string for Oracle database (On-Prem) is pretty straight forward.  You will need following information: Oracle server name or IP address, port (generally 1521), service name. JDBC syntax is as follows:

Jdbc:oracle:thin:<server/ip>:<port>:<servicename>

Example: jdbc:oracle:thin:192.168.196.128:1521:xe

Integrating with Oracle Cloud Database

Setting up Anaplan Connect script to integrate Anaplan with Oracle Cloud Database is similar to the steps outlined for other JDBC sources above.  For this scenario, we’ll be using Oracle Autonomous Database. In this section we will present the following:

  • Download Client Credentials (Oracle Wallet) for authentication
  • Required JDBC Drivers
  • Constructing JDBC connection string

Download Client Credentials

  • Log in to Oracle Cloud. Go to Overview > Autonomous Database > <database>.
  • Under database details, click on DB Connection.

annejulie_37-1623181235735.png

  • Under Wallet type, select Instance Wallet and click Download Wallet.  You may be prompted for a password. Download the wallet to the system that’s running Anaplan Connect.

annejulie_38-1623181235743.png

  • Wallet will name following naming convention:  Wallet_<dbname>.zip (ex: Wallet_anaplandb.zip).
  • Unzip the wallet to a directory.  Path to this directory will be used in your jdbc connection string.  As a best practice, you will want to secure access to this directory to only “integration user” as it contains private key information for authentication and access.

JDBC Driver

  • You will require following JDBC drivers to connect to Oracle Cloud Autonomous Database.
    • odbc8.jar, ucp.jar, oraclepki.jar, osdt_core.jar, osdt_cert.jar
  • You may download these drivers from Oracle here.
  • Copy above mentioned drivers to <anaplan-connect>/lib directory.

JDBC Connection String

JDBC connection string for Oracle Cloud Autonomous Database is relatively straight forward. You will need following information: TNS alias (found in tnsnames.ora in Wallet_<dbname> folder), path to Wallet_<dbname> directory. JDBC syntax is as follows:

Jdbc:oracle:thin:<TNS alias>?TNS_ADMIN=<path-to-wallet>

Example:

jdbc:oracle:thin:@anaplandb_high?TNS_ADMIN=/Anaplan/DataIntegration/OracleCloud/Wallet_anaplandb

TNS alias can be found in tnsname.ora file in Wallet_<dbname> directory.

annejulie_39-1623181235751.png

Anaplan Connect

  • Build a jdbc properties file with jdbc connection string, database username, password, and SQL SELECT query.

annejulie_40-1623181235763.png

  • Build an Anaplan Connect script referencing the JDBC properties file to execute either an Action or a Process.

annejulie_41-1623181235779.png

  • Run Anaplan Connect script

annejulie_0-1623186965530.png

Summary

For a quick reference, the table below provides sample JDBC connection strings for different database solutions we covered in this blog.

Database

JDBC Connection String

SAP HANA Cloud

jdbc:sap://430f7d5a4c5866c.hana.trial-us10.hanacloud.ondemand.com:443

Snowflake

jdbc:snowflake://he34739.us-east-1.snowflakecomputing.com/?warehouse=COMPUTE_WH&db=AnaplanDB&schema=public

Azure SQL

jdbc:sqlserver://anaplandi.database.windows.net:1433;database=anaplandb;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

GCP BigQuery

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=celtic-spider-206221;OAuthType=0;OAuthServiceAcctEmail=pmarpaka@celtic-spider-206221.iam.gserviceaccount.com;OAuthPvtKeyPath=/opt/bigquery/certs/celtic-spider-206221-6a4c6.json;IgnoreTransactions=1;

Oracle (On-Prem)

jdbc:oracle:thin:192.168.196.128:1521:xe

Oracle Cloud

jdbc:oracle:thin:@anaplandb_high?TNS_ADMIN=/Anaplan/DataIntegration/OracleCloud/Wallet_anaplandb

 

Additional information on Oracle JDBC Thin Connections and Wallets can be found here.

 

Ready to move on to the next step? Let's deep dive into Advanced Scripts.

 

Got feedback on this content? Let us know in the comments below.

Tagged:

Comments

Categories