Found this ...
Azure Databricks to Microstrategy JDBC/ODBC Setup Tips
Purpose
This is a quick reference for common Microstrategy configuration tips, tricks, and common pitfalls when setting up a connection to Databricks:
Networking
For Azure, we recommend engaging with your relevant Azure SA overlay resource as there are common Azure networking configurations that might be getting in the way.
Common things to consider:
- IP Access Lists: has remote connectivity been locked down? Is the MSTR server within an allow-listed IP?,
- MSTR server routing: is MSTR server actually allowed to hit public IPs like the Databricks control plane given the networking setup?
Chances are there might be networking configs that need to get established to enable said connectivity from MSTR to the control planeโs ODBC/JDBC endpoint.
Setup
Once thatโs set and connectivity has been confirmed (e.g. by pinging the Databricks endpoint):
- Install the Simba driver as documented here: https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#step-1-download-and-install-a-jdbcodbc...
- Configure a DSN using the recommended settings below; this may vary between environments and providers e.g. likely use 32bit and 64bit unless you specifically know which one is needed for MSTR
Spark Server Type: SparkThriftServer
Host: demo.cloud.databricks.com (no http/s here)
Port: 443
Database: default
Mechanism: User Name and Password
User Name: token
Password: your-PAT-here
Thrift Transport: HTTP
Hit HTTP Options button, HTTP Path: /sql/protocolv1/o/0/your-cluster-name
Hit SSL Options: Enable SSL (ON)
- Confirm the test button succeeds
- In MSTR create the database instance/user/etc., and then go load the warehouse catalog which will poll the metastore for databases/tables to tag the tables you want to register in MSTR.
Other tips
- If you see no tables in the warehouse catalog, you may need to adjust the options in warehouse catalog to query for a specific database name to allow it to read the list of tables for said database.
- Youโll be limited to connecting via ODBC/JDBC, which requires the use of a personal access token (PAT) at the moment; MSTR will not be able to do use a secret to connect to Databricks (please confirm the latest via our JDBC/ODBC docs referenced above)
- If you are using the MSTR Intelligence Server, it should be an easy config for their MSTR admin as long as theyโve at least set up one ODBC connection previously, the ODBC config would be the same generically across most BI tools.
- If you are not using Intelligence Server but instead just doing local laptop versions of โMicrostrategy Desktopโ like an analyst would install, the end user will have to be savvy enough to understand ODBC DSN setup (64 vs 32 bit, etc.) and they wonโt have as many options to control SQL as the enterprise version will provide.
- Once set up, if you hit performance issues, you should look at a variety of MSTR caching options (element values for prompts, etc.), multi-sourcing their attribute values from lookup tables, etc.
