I just recently set up our Cloud PA system to use Databricks. We used secure gateway. Make sure you pay attention to the ODBC setup and pass on all the parameters you need to set to support. This included the HTTP settings, and the security settings. Because Databricks ODBC connection is linked to a particular cluster/database, you will need to create the secure client connection and then open a support ticket so the ODBC connection can be configured for each.
As far as the gateway client, that just needs to be a system that has access to both systems. We have a small dedicated server on premise that servers as the gateway between IBM Cloud and all of our other data sources.
Original Message:
Sent: Wed June 28, 2023 09:53 AM
From: Alex Golovakha
Subject: PA Cloud data sourcing from Databricks ODBC driver
I'd just add the TM1Py library to your DB cluster and push data from the Spark DF into the TM1 cloud using a non-interactive account in the Python notebook. This way you will not have issues with authentication on either side and you'll do all your SQL work in DB notebook rather than in the TI.
Also, if you need to control your loads from the TM1 side you can use DB job API and curl/powershell script to run your notebook.
This is all from personal experience/working implementations, not guesswork :)
Cheers!
Alex.
------------------------------
Alex Golovakha
IL
Original Message:
Sent: Wed June 28, 2023 09:14 AM
From: DANIEL BERNATCHEZ
Subject: PA Cloud data sourcing from Databricks ODBC driver
Bernd:
Thanks you - This is exactly what I needed to get started.
------------------------------
Daniel Bernatchez
Clear Apex LLC
Original Message:
Sent: Wed June 28, 2023 03:10 AM
From: Bernd Siebert
Subject: PA Cloud data sourcing from Databricks ODBC driver
Hello Daniel,
Planning Analytics on Cloud (PAoC) supports only two types of ODBC connections:
- By Secure Gateway
- By a cloud-to-cloud ODBC connection in case of cloud hosted realtional databases like Amazon Redshift or Snowflake. A cloud-to-cloud ODBC connection does not use the Secure Gateway.
If Databricks is cloud hosted, you ( respectively the PAoC Support ) may create a cloud-to-cloud ODBC connection which does not use the Secure Gateway. To that end you have to open a support ticket. Although Databricks is not listed by the Product Documentation among the cloud-to-cloud ODBC connections, PAoC Support may have setup such a connection in the past.
Regards
Bernd
Links:
1)
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=connections-customer-provided-data-sources-odbc-drivers
All products / IBM Planning Analytics / 2.0.0 / Getting Started / Using Planning Analytics / Planning Analytics Workspace / Administer IBM Planning Analytics Workspace / Administer IBM Secure Gateway (cloud only) / Add a data source to a Secure Gateway / Data source connections /
Customer-provided data sources (ODBC drivers)
2)
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=drivers-amazon-redshift
odbc-drivers
All products / IBM Planning Analytics / 2.0.0 / Getting Started / Using Planning Analytics / Planning Analytics Workspace / Administer IBM Planning Analytics Workspace / Administer IBM Secure Gateway (cloud only) / Add a data source to a Secure Gateway / Data source connections / Customer-provided data sources (ODBC drivers) /
Amazon Redshift
The Amazon Redshift connection is a cloud-to-cloud connection that does not use a Secure Gateway tunnel. The customer must provide a publicly accessible service.
To get started, open a support ticket indicating that a connection is desired and provide the ODBC driver for the connection with the following details:
- DSN (Data Source Name)
- Server address
- Port
- Database name
3)
https://quebit.com/askquebit/connecting-snowflake-to-ibm-planning-analytics-cloud/
Connecting Snowflake to IBM Planning Analytics (Cloud)
------------------------------
Bernd Siebert
Original Message:
Sent: Tue June 27, 2023 12:36 PM
From: DANIEL BERNATCHEZ
Subject: PA Cloud data sourcing from Databricks ODBC driver
I am looking into connecting the PA Cloud environment to a Databricks SQL data source. Databricks has their own an ODBC Driver.
Before I start looking into this I was hoping to get some insight from this forum:
- Has anyone setup a PA Cloud to Databrick SQL data source?
- Is Secure Gateway the only option? I am not sure I will be allowed to setup the Secure Gateway Client on the Databricks side.
Any feedback would be appreciated.
------------------------------
Daniel Bernatchez
Clear Apex LLC
------------------------------