Search code examples
azurepowerbi

Update dataset of the Power BI Report

I updated a dataset of the Power BI report using rest api: https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/Default.UpdateDatasources

Then I refreshed the dataset using the api: https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes

but I don't see any visual changes in the Power BI.

How can I resolve this issue ? Do this APIs take time to update the dataset ?

These are the screenshots of the APIs:

POST API to updated Dataset

POST API to refresh the dataset

Solution

  • Initially, I registered Single Tenant Microsoft Entra ID application, Added and Granted delagated type Power BI Service permission of Dataset.Read.Write.All API permission like below:

    enter image description here

    Use below authorization endpoint at browser to get authorization_code :

    https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?
&client_id=<AppID>
&response_type=code
&redirect_uri=YOUR REDIRECT URI
&response_mode=query
&scope=&scope=https://analysis.windows.net/powerbi/api/.default
&state=12345

    enter image description here

    Now, Generated Access token using below token endpoint with below parameters:

    GET https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token

client_id: <APP_ID>
client_secret: <CLIENT SECRET>
scope:&scope=https://analysis.windows.net/powerbi/api/.default
grant_type:authorization_code
redirect_uri:https://jwt.ms
code:AUTHORIZATION_CODE
client_secret:ClientSecret

    enter image description here

    NOTE: For updating datasources of datasets, there are some Limitation, Ensure Only these data sources are supported, SQL Server, Azure SQL Server, Azure Analysis Services, Azure Synapse, OData, SharePoint, Teradata, and SAP HANA.

    Microsoft recommends to use enhanced dataset metadata.

    As this MsDoc refers, If you're not using enhanced dataset metadata, it will take upto 30mins to update data sources operation to complete and after that refresh the dataset.

    After generating access token, use below query to update datasources of dataset:

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

    Request Body:

    {
  "updateDetails": [
    {
      "datasourceSelector": {
        "datasourceType": "Sql",
        "connectionDetails": {
          "server": "My-Sql-Server",
          "database": "My-Sql-Database"
        }
      },
      "connectionDetails": {
        "server": "New-Sql-Server",
        "database": "New-Sql-Database"
      }
    },
    {
      "datasourceSelector": {
        "datasourceType": "OData",
        "connectionDetails": {
          "url": "http://services.odata.org/V4/Northwind/Northwind.svc"
        }
      },
      "connectionDetails": {
        "url": "http://services.odata.org/V4/Odata/Northwind.svc"
      }
    }
  ]
}

    If you get the response of Status code 200 OK which means updating datasources operation will started successfully. Wait for 30 minutes to complete the operation, and then refresh the dataset.

    Use below query to refresh the dataset:

    POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes

    After running this query, If you get Status Code 202 Accepted which means your data sources of dataset are updated successfully.

    References:

    Update data source - Dataset

    Refresh dataset