If you have Power BI Reports that source their data from an Azure Data Bricks instance using the Azure Databricks Connector in Power BI then you may find this post useful!
This week we have deploying a lot of Power BI Reports across Development, Staging and Production environments and have had to manually update the connection string to the appropriate Databricks cluster with Power Query from the Advanced Editor of Power BI for each Query and for each Report - very time consuming and prone to error!.
So we figured it's about time I worked out how to parameterise the connection - so here it is!
In this example we can assume that you want to update an existing Power BI report which has the connection string for the Azure Databricks instance hardcoded in the connection string withing Power Query and not want to parameterise it instead
Log into your Databricks Development instance and bring up your Cluster Advanced Options where you will see the Server Hostname and HTTP Path details under the JDBC/ODBC tab. Take a note of these values as you will need them below.
Open the Power BI Report and chose Transform data to open the Power Query environment.
Create 2 new "Text" Parameters - 1 for the Server Host Name and the other for the HTTP path as shown below and populate them with the details that you have noted above. In this example we have called them @ServerHostName and @HTTPPath
Edit the Source = Databricks.Contents line to replace the hard coded coded cluster details with the new parameters as shown below:
If prompted to enter your credentials to connect to your Azure Databricks cluster then do so here
Confirm that Preview data is loading to your Query and you know that everything is working as expected!
Save your pbix file, refresh your report and upload to your development environment with the new parameters.
Now you have updated your report to use Parameters instead of hardcoded Server Hostname and HTTP Paths!
If you need to move your report to another environment simply update the parameter values in the report, refresh the data and upload to your new environment. This will save you having to refresh each query individually saving a lot of time and also room for ever.
I hope you find this tip useful!