cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Connect to Microstrategy

User16765131552
Contributor III

Can Azure Databricks be connected through Microstrategy?

1 ACCEPTED SOLUTION

Accepted Solutions

User16765131552
Contributor III

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):

  1. Install the Simba driver as documented here: https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#step-1-download-and-install-a-jdbcodbc...
  2. 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)

  1. Confirm the test button succeeds
  2. 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.

View solution in original post

1 REPLY 1

User16765131552
Contributor III

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):

  1. Install the Simba driver as documented here: https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#step-1-download-and-install-a-jdbcodbc...
  2. 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)

  1. Confirm the test button succeeds
  2. 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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!