Parameterizing your Databricks SQL Connections in Power BI

When first connecting to Databricks SQL in Power BI , whether you use Partner Connect or manually enter your Databricks SQL warehouse info in the Databricks connector in Power BI Desktop, your DBSQL warehouse data is hardcoded in each of your M queries.

By default your provided Databricks SQL warehouse data is hardcoded in your M queries.

This is perfectly fine to get started and serves a lot of use cases, but in many of our customers’ production scenarios, you may want to change the Databricks SQL warehouse underlying your Power BI reports, whether as part of a Power BI deployment pipeline or your own CI/CD process, or while testing different warehouse configurations for performance.

Now imagine changing those values manually for dozens or potentially hundreds of queries in your datasets!

To avoid this being a highly manual and hard to scale process, you can use the convenient Parameters feature of Power Query. Here’s a walkthrough on setting up parameters for your Databricks SQL warehouse connection info in Power BI Desktop and then modifying them in the Power BI service.

Walkthrough

1. In Power BI’s Power Query editor, open the Parameters modal dialog from the ribbon.

2. The M queries that connect to Databricks SQL take two values that you can parameterize: the ServerHostName (e.g. adb-984752964297114.14.azuredatabricks.net ) which corresponds to the Databricks workspace and HTTPPath (e.g. /sql/1.0/endpoints/cf8e3ce7662288d6 ) which corresponds to the specific Databricks SQL warehouse you are connecting to.

In this example, we’ll create a parameter for both. If you just have one Databricks workspace you use with Power BI, you can choose to parameterize only the HTTPPath value.

For your parameters, do the following:

• Name them whatever you want, but we recommend ServerHostName and HTTPPath for consistency and to help others easily understand their purpose.
• Optionally provide a Description.
• Set the parameters as Required.
• Set the Type to Text. THIS IS CRITICAL. Parameters of type Any are not modifiable in the Power BI Service.
• If you have a specific set of workspaces or warehouses you want to use, you can optionally populate the Suggested Values field.
• Set the Current Value to an existing workspace and warehouse so your current datasets will continue to work once we’ve parameterized their connections.

3. Replace the hardcoded values in your M queries referencing Databricks SQL with your new parameters.

• For each Databricks SQL based M query you want to parameterize, go to the query and select the first query step (named Source by default.)

• If you are loading a table, you’ll have a query function Databricks.Catalogs with hardcoded arguments for the warehouse you originally connected to

• If you are loading a specific query from Databricks SQL, you’ll have the same Databricks.Catalogs function wrapped in a Value.NativeQuery function.

• Replace the first argument of the Databricks.Catalogs function with the name of your server hostname parameter, and the second argument with the name of your HTTP Path parameter.

4. Test and confirm your parameter values have been applied and your Databricks SQL connection is still working. The most common errors are typos in the parameter values or the names of your parameters in the M queries.

When you publish this report and dataset to the Power BI service, these parameters are available to be edited both manually via the PowerBI UI and programmatically via the REST APIs.

Updating Parameters via the Power BI UI

1. Navigate to the dataset with your parameters in the Power BI Service. Select the ellipsis (“…”) next to the dataset and select Settings to open the dataset settings page.

2. On the settings page, expand the Parameters section to view and modify the values for the parameters we’ve created.

If you are unable to modify the values, go back to your Power BI Desktop file and ensure you set the parameters to type Text.

3. After changing these values, always verify the report is still able to connect to Databricks SQL.

Updating Parameters via the Power BI API

There are two methods to updating parameters, one for “My workspace” and one for datasets in a group workspace.

The Group workspace API endpoint takes a groupID and datasetID:

https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/Default.UpdateParameters

While the “My workspace” API only needs a datasetID:

https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/Default.UpdateParameters

You can retrieve the groupID and datasetID values from your dataset from the Power BI website URI when viewing your dataset, or again, programmatically with the Power BI REST APIs (if, for example, you want to update many different datasets with new parameters at the same time.)

The body signature for both of these API methods is the same. You simply provide a JSON array of your parameter keys and the values you wish to set them to.

"updateDetails": 
[ 
{ "name": "ServerHostName", "newValue": "adb-984752964297111.11.azuredatabricks.net" }, 
{ "name": "HTTPPath", "newValue": "/sql/1.0/warehouses/4e69da7c9ace8afa" } 
]

As always, test connectivity of your Power BI reports to Databricks SQL after modifying these parameters.