This guide provides a comprehensive set of the steps involved in establishing an ODBC connection between a SQL Server database and a Planning Analytics Version 12 instance. By following these instructions, users can successfully configure and utilize the ODBC Data Connector for seamless data integration.

Download ODBC data connector 

The first step is to log into your IBM PA V12 instance and navigate to the Administration tab and click on Databases.

Click on the target database you want connect using ODBC.  In the example below, we are connecting to the Production database.

Click on the download icon in the top right corner of the screen and click on ODBC Data Connector. 

The data connector should then download to your local machine in a zipped file. 

Unzip the file from the Download folder and place it on the machine you want to connect to (usually where the data sources reside). You can unzip the file to a new or existing folder or at the root of one of your drives.

When unzipped, you should see the following: 

Install ODBCIS Service 

Run the InstallODBCWindowsServices as administrator. 

Press Enter three times on these questions for a default installation. It is not necessary to change this configuration. 

This script will run and return a message saying it started successfully. 

In Windows Services, search for “IBM Cognos” and confirm that the IBM Cognos TM1 ODBC Integration service is running. 

Configure JSON Files 

Update serverPort 

You can then start to configure the config.internal.json file, which you will find in the deployment folder from your downloaded data connector file.

Open the file and change the server port of the odbcis-gateway to the port you would want it to listen on. 

Server Port in Windows Firewall 

Create a Windows firewall rule to allow the port specified to be accepted in the inbound rules. 

Search in the Windows search bar for Windows Defender Firewall with Advanced Security and open it. 

Click on Inbound Rules and click New Rule. 

  Select Port and click Next.

Enter the port number you specified in the config.internal.json file and click Next. 

 Select Allow the connection and click Next. 

Click the check boxes for Domain, Private, and Public, then click Next. 

Enter the rule name and description then click Finish. 

Server SSL Cert 

Generate a server authentication SSL certificate from a trusted CA listed in the trustedCAs.txt file. Also ensure that the key file is unencrypted.
Open the tls folder, then the server folder and add your cert chain and key in this folder. 

In the server folder, add the key and cert chain files.

Update the config.internal.json file to point to the new cert and key. 

ODBC Datasource Connections 

Add the ODBC connections to the config.odbcis.json file. This file is also in the deployment folder.

The client is the SQL login to the Datasources.   

If you do not have any existing ODBC connections, setup an ODBC connection first. 

In the Windows search bar, search for ODBC and open the correct ODBC Data Sources for your operating system. 

Click on System DSN and then click Add.

Select ODBC Driver 17 for SQL Server and click Finish. 

Select with SQL server authentication using ID and password entered by the user and fill in your credentials to SQL Server Management Studio, then click Next. 

 Click next again.

Click Finish.

Upon clicking Finish, the screen to test the data source pops up.

Click Test Data Source and it should test successfully. 

If you do not have login credentials for the SQL Server Management Studio, an admin has to create it for you.

If you have admin access, you can run SQL Server Management Studio as admin and create your own login credentials.

Within SQL Server Management Studio you change the authentication type of the database.

Right click on the database and click Properties. 

 Click Security and then select SQL Server and Windows Authentication mode and click ok. 

 Go to Services and restart the ODBCISSD services 

Allow IP's and Port 

Once the ODBCIS files have been configured, and you have allowed traffic on the specific serverport, you need to allow traffic on your specified Egress IP’s for your region.

Planning Analytics as a Service uses three Network Address Translation (NAT) gateways for each cluster to manage external requests. This means that any requests originating from within the cluster and destined for an external endpoint are routed through one of the central egress points. This ensures that the external endpoint only sees a single IP address of the three remote IP addresses, depending on the availability zone in Planning Analytics where the request was initiated. This approach provides a simplified view of the cluster for external systems and enhances security by controlling the number of IP addresses exposed to the outside world.

Use this link to find the specific IP addresses for your IBM Cloud’s region.

Egress IP addresses | IBM Cloud Docs 

Configure ODBCServiceRoot

Once the traffic on the Egress IP’s for the port has been allowed, go to the Administration tab in IBM PAW, select the database you want to configure and click on Configuration. In the example below, we are using the Production database.

Within Configuration, click on External database.

Input the ODBCServiceRoot.

https://{public dns}:{serverport}/api/v1/ 

For example; https://paengine.rcmnow.com:5555/api/v1/ 

It follows the structure of Public dns – can be a public dns or a public facing ID. 

Serverport – port specified in config.internal.json 

You can test the connection using a TI process as shown below by running a SQL query extracting data from a table that you know exists in the database. For example, if you know the GL table exists you can run SELECT * FROM GL.

Troubleshooting

If you run into any errors along the way, here are some resources you can use to troubleshoot:

How to Validate Planning Analytics ODBC Data Connector (ODBCIS) Connection via API (ibm.com) 

Must Gather - Troubleshoot Planning Analytics ODBC Connector (ODBCIS) (ibm.com) 

Troubleshoot Planning Analytics ODBC Connector Connections and SSL Certificates (ibm.com)

Conclusion

I hope this guide has helped clarify how to set up an ODBC connection in IBM Planning Analytics 12. If you have any questions or would like PMsquare to provide guidance and support for Planning Analytics, contact us today.

Stay ahead with expert insights and best practices—subscribe to our newsletter for updates delivered straight to your inbox.