Schedule Refresh Errors - Some data sources may not be listed because of hand-authored queries

[Anonymous]

Hi All,

I get this error while trying to refresh my Power BI Dashboard on the Service side. I use SQL code for Azure Databricks. How can I refresh the dashboard on Service? It doesn't allow me because of this. I am running automated jobs from my Azure Databricks Notebooks and my tables and queries in Power BI are based off sql code to pull that and then I want to automate that and automate onto Service with scheduled refreshes 1x/month. There has got to be a way around this right? I know in Tableau you can schedule refreshes using SQL code to a database.

Thanks!

[rajendraongole1]

Hi @Anonymous - Power BI Service is related to dynamic data sources, which are not supported for refresh in the Power BI Service. Since you are using SQL code from Azure Databricks to pull the data, this introduces a dynamic nature to the dataset, preventing it from being refreshed automatically on the Power BI Service.

You will need to use an On-premises Data Gateway to refresh your data from Azure Databricks. The gateway acts as a bridge between Power BI Service and on-premises (or cloud) data source.Configure the connection for Azure Databricks in the gateway settings in Power BI Service.Once configured, you can then schedule refreshes via the gateway.

Azure Databricks allows you to expose data via a REST API. You can build an API endpoint to retrieve the data and then consume it in Power BI Service. Power BI supports scheduled refreshes for datasets based on web data sources.

Data refresh in Power BI - Power BI | Microsoft Learn

On-premises data gateway - Power BI | Microsoft Learn

Datasets - Refresh Dataset - REST API (Power BI Power BI REST APIs) | Microsoft Learn

By setting up the gateway or using an API-driven approach, you can automate your scheduled refreshes for the dataset pulling from Azure Databricks and SQL code.

Hope this helps.

[Anonymous]

Hi @rajendraongole1,

I am just a Power BI user and data analyst. I feel like this goes outside of my expertise. I don't know how to develop either a gateway or Rest API. Do I need both? There is no way around this? How come Power BI can't automatically set up refreshes for SQL code but other BI tools like Tableau can and have for years?

[rajendraongole1]

Hi @Anonymous -Yes, you are correct and i can understand how frustrating technical hurdles can be, especially when you're trying to focus on data analysis and reporting. Let’s simplify the process to help you set up scheduled refreshes for your Power BI dashboards connected to Azure Databricks without diving deep into gateways or APIs.

Connect Power BI Desktop to Azure Databricks
Open Power BI Desktop.

Get Data from Azure Databricks:

Click on "Get Data" in the Home ribbon.
Select "Azure" from the left pane.
Choose "Azure Databricks" and click "Connect".
Enter Connection Details:

Server Hostname: This is your Azure Databricks workspace URL without the https:// prefix. For example, if your workspace URL is https://adb-1234567890123456.7.azuredatabricks.net, enter adb-1234567890123456.7.azuredatabricks.net.
HTTP Path: To find this:
Log into your Azure Databricks workspace.
Click on your user icon (top right) and select "User Settings".
Go to the "Advanced" tab and find the "JDBC/ODBC" connection details. Copy the "HTTP Path" (it looks like /sql/1.0/endpoints/...).
Authentication:

Choose "Personal Access Token (Basic)".
Token: Enter your Personal Access Token (PAT). If you don't have one:
In Azure Databricks, click on your user icon > "User Settings" > "Access Tokens" > "Generate New Token". Give it a name and click "Generate".
Copy and securely store the PAT, as you won’t be able to view it again.
Import Data:

After authentication, navigate through your databases and select the tables or views you need.
Click "Load" to import the data into Power BI Desktop.
Build Your Report:

Create your visualizations, ensuring they display the data as intended.
Publish to Power BI Service:

Click on "File" > "Publish" > "Publish to Power BI".
Sign in to your Power BI account if prompted.
Select the desired workspace and publish your report.

How to Connect to Databricks from PowerBI (youtube.com)

for scheduling you can follow the above document links using onprem data gateway.