Power BI task for jobs

Important

The Power BI task feature is in Public Preview.

While you can publish to Microsoft Power BI online manually from your Azure Databricks workspace, you can use a Power BI task to orchestrate your Power BI semantic models automatically.

To learn more about publishing to Power BI in the Azure Databricks UI, see Publish to the Power BI service from Azure Databricks.

Requirements

Configure a Power BI task

After you have a Power BI connection configured, you can create a task to automate publishing using that connection.

Note

The jobs UI displays options dynamically based on other configured settings.

To begin the flow to configure a Power BI task:

  1. Navigate to the Tasks tab in the Jobs UI, for the job to which you want to add a task.
  2. Click Plus icon. Add task.
  3. Enter a Task name.
  4. In the Type drop-down menu, select Power BI.
  5. Configure the task properties (see the following table for the properties and their use).
  6. (Optional) To configure retries, run-duration or streaming-backlog thresholds, or notifications, see Advanced task settings.
  7. Click Save task.

Note

Databricks recommends setting a Azure Databricks service principal to be the Run as identity on the task. For the best practices, see Best practices for jobs governance. The service principal will require the necessary privileges to access the Databricks tables, schemas, Power BI connection, and SQL warehouse used by the task.

When editing a task, the credentials of the current user are used, but when the task is run, the Run as identity is used. The identity must have the correct privileges to run the task.

Power BI task property Description
SQL Warehouse The SQL warehouse that processes refreshes in Import mode, or queries in DirectQuery mode for the semantic model. The Power BI task requires a SQL warehouse. General-purpose compute is not supported.
Power BI connection The Power BI connection for this task. The task uses this connection to fetch Power BI workspaces and semantic models, and to publish to Power BI. See Create a Power BI connection in Unity Catalog for orchestration.
Power BI workspace The Power BI workspace to which a semantic model is published.
Power BI semantic model The Power BI semantic model to publish. Select an existing model, or type a new model name and click Publish new semantic model <name>.
Overwrite existing model By default, metadata updates are only appended to an existing model. Checking this box ensures all metadata and data updates propagate to Power BI semantic models when the task is run.
Power BI query mode The default query mode for the tables being published. When DirectQuery is selected, you can also set query modes on individual tables using the Configure table query modes property. One of the following values:
  • Import The data for the model is cached in Power BI. To update the data, users must refresh the model before using it, which queries the SQL warehouse and loads the data.
  • DirectQuery The data is not stored in Power BI. When the user creates or loads a dashboard, the SQL warehouse is queried for the most recent data.

Power BI query mode is also referred to as storage mode in Power BI. For more information about query modes, see Semantic model modes in the Power BI service.
Tables to update The source tables and schemas for the semantic model. If you select a schema for this property, then when you run the task, all tables under the schema at that time are used for the update. The task updates any new tables, columns, comments, and primary key/foreign key relationships.
Authentication method Defines how the semantic model authenticates back to the chosen SQL warehouse. When using OAuth, credentials may need to be configured on Power BI after the first task run. If you can't set service principal credentials in the Power BI UI for your configuration, see Configure credentials with a REST API. When using PAT, it generates and embeds a PAT for the Run as identity.
Configure table query modes When DirectQuery is selected as the Power BI query mode, you can optionally set individual tables to use Dual storage mode. Tables set to Dual storage mode can act as either Import or DirectQuery modes depending on the context of the query.
For more information, see Semantic model modes in the Power BI service.
Refresh after update This option is available if Import is selected as the query mode. By default, only the model metadata is updated, but if this checkbox is checked, then it also triggers a data refresh (which queries the SQL warehouse). This refresh can be seen in the refresh history on Power BI.

To edit, clone, disable, or delete this task, see Configure and edit tasks in Lakeflow Jobs.

Configure credentials with a REST API

To set the semantic model's service principal credentials with a REST API instead of the Power BI UI, use either the Microsoft Fabric Connections API or the Power BI REST API:

  • The Microsoft Fabric Connections API sets credentials on the connection and works for all connection types: cloud, on-premises data gateway, and VNet data gateway.
  • The Microsoft Power BI REST API sets credentials on a data source. It works for on-premises data gateway connections and cloud data sources.

To authenticate with a Azure Databricks service principal, use the Basic credential type with the service principal application ID as the username and its secret as the password.

Microsoft Fabric Connections API

Use the Microsoft Fabric Connections API to update the credentials on the connection that the semantic model uses. The Fabric API requires a Microsoft Entra ID access token, and the identity you use must have permission for the connection (or Power BI gateway admin permission for a gateway connection). For how to acquire a token, see the Microsoft Fabric REST API documentation.

The steps depend on whether Power BI connects through a cloud connection or an on-premises data gateway.

Cloud connection

  1. List the connections that the identity can access and find the ShareableCloud connection for your Azure Databricks SQL warehouse. Note its id.

    GET https://api.fabric.microsoft.com/v1/connections

    For details, see the Microsoft documentation on List Connections.

  2. Update the connection's credentials, using the connection id. Pass the service principal credentials directly:

    PATCH https://api.fabric.microsoft.com/v1/connections/{connectionId}

    {
  "connectivityType": "ShareableCloud",
  "credentialDetails": {
    "credentials": {
      "credentialType": "Basic",
      "username": "<service-principal-application-id>",
      "password": "<service-principal-secret>"
    }
  }
}

    For details, see the Microsoft documentation on Update Connection.

On-premises data gateway

  1. List the connections that the identity can access and find the OnPremisesGateway connection for your Azure Databricks SQL warehouse. Note its id and gatewayId.

    GET https://api.fabric.microsoft.com/v1/connections

    For details, see the Microsoft documentation on List Connections.

  2. Get the gateway's public key, using the connection's gatewayId:

    GET https://api.fabric.microsoft.com/v1/gateways/{gatewayId}

    For details, see the Microsoft documentation on Get Gateway.

  3. Encrypt the service principal credentials with the gateway's public key. For details, see the Microsoft documentation on Configure credentials programmatically.

  4. Update the connection, using the connection id from the first step. Send one values entry per gateway member:

    PATCH https://api.fabric.microsoft.com/v1/connections/{connectionId}

    {
  "connectivityType": "OnPremisesGateway",
  "credentialDetails": {
    "credentials": {
      "credentialType": "Basic",
      "values": [
        {
          "gatewayId": "<gateway-id>",
          "encryptedCredentials": "<encrypted credential data>"
        }
      ]
    }
  }
}

    For details, see the Microsoft documentation on Update Connection.

Other connectivity types, such as a VNet data gateway (VirtualNetworkGateway), use the same endpoint with the credential payload for that type. For the full request schema, see the Microsoft documentation on Update Connection.

Power BI REST API

You can also use the Power BI REST API to update the credentials for the data source. The Power BI REST API requires a Microsoft Entra ID access token. The steps are the same whether Power BI connects through a cloud data source or an on-premises data gateway, but the requirements differ:

  • Cloud data source (no gateway): You must be the semantic model owner. Send the credentials directly and set encryptionAlgorithm to None.
  • On-premises data gateway: You must have gateway admin permission. Encrypt the credentials with the gateway's public key and set encryptionAlgorithm to RSA-OAEP.

  1. Get the semantic model's data sources and find the Azure Databricks data source. Note its datasourceId and gatewayId. Power BI binds a cloud data source to an automatically provisioned cloud gateway, so a gatewayId is present even without an on-premises gateway.

    GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/datasources

    For details, see the Microsoft documentation on Get Datasources.

  2. Update the credentials for that data source, setting encryptionAlgorithm for your connection type. The following example is for a cloud data source, with encryptionAlgorithm set to None:

    PATCH https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources/{datasourceId}

    {
  "credentialDetails": {
    "credentialType": "Basic",
    "credentials": "{\"credentialData\":[{\"name\":\"username\",\"value\":\"<service-principal-application-id>\"},{\"name\":\"password\",\"value\":\"<service-principal-secret>\"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "None",
    "privacyLevel": "None"
  }
}

    For an on-premises data gateway, encrypt the credentials with the gateway's public key, replace the credentials value with the encrypted data, and set encryptionAlgorithm to RSA-OAEP.

    For details, see the Microsoft documentation on Update Datasource. For a PowerShell example that retrieves the gateway public key and encrypts the credentials, see the Databricks M2M OAuth credentials management sample. For details on encrypting credentials, see the Microsoft documentation on Configure credentials programmatically.

Troubleshooting

Don't edit a semantic model in the Power BI service while a Power BI task updates it. Doing so can leave the model stuck on Pending changes.

For information about troubleshooting publishing to Power BI, see Troubleshoot publishing to Power BI. If you are still having trouble, you can submit product feedback. See Submit product feedback.

Additional resources

  • Last updated on