Semantic model Data Refresh API in Power BI

One of the newly added feature in Power BI Feb 2025 update was “Link semantic model refresh details from external applications”. More details can be found here and also the official announcement is seen here.

This article will examine the recent updates to the refresh APIs and how granular details of the semantic model refreshes can be exposed as an URL.

The article will focus primarily on how to leverage Refresh API’s to

  • Refresh a specific semantic model in a given workspace

  • Refresh all the semantic models of a given workspace

  • Check the statuses of the refreshes and construct a refresh URL

SetUp

To get started, our service principal should have Dataset.Read.All and Dataset.ReadWrite.All delegated permissions for the Power BI service.

Power BI REST API

We will have to use the https://analysis.windows.net/powerbi scope.

Create a new C# console application and add the following settings to the local.settings.json file

{
  "AllowedHosts": "*",
  "ClientId": "ClientId of the service prinicipal",
  "Workspace": "Workspace where the semantic model resides",
  "Semantic Model": "Name of the semantic model"
}

Add the following Nuget packages to the console application

dotnet add package Azure.Identity --version 1.8.0
dotnet add package Microsoft.Extensions.Configuration --version 6.0.0
dotnet add package Microsoft.Identity.Client --version 4.47.0
dotnet add package Newtonsoft.Json --version 13.0.4

Code

Add required references to the code

using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.Net.Http.Headers;
using System.Text;

Declare a bunch of variables

 public static HttpClient client = new();
 public static string response = "";
 public static string url = "";
 public static string semanticmodel = "";
 public static string clientId = "";
 public static string workspace = "";
 private static string[] scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
 private static string Authority = "https://login.microsoftonline.com/organizations";
 private static string RedirectURI = "http://localhost";

Read the localsettings.json file

  public static void ReadConfig()
  {
      var builder = new ConfigurationBuilder()
      .AddJsonFile($"local.settings.json", true, true);
      var config = builder.Build();
      clientId = config["ClientId"];
      workspace = config["Workspace"];
      semanticmodel = config["Semantic Model"];
  }

Authenticate the user and generate the bearer token

   public async static Task<AuthenticationResult> ReturnAuthenticationResult()
   {
       string AccessToken;
       PublicClientApplicationBuilder PublicClientAppBuilder =
           PublicClientApplicationBuilder.Create(clientId)
           .WithAuthority(Authority)
           .WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
           .WithRedirectUri(RedirectURI);

       IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
       var accounts = await PublicClientApplication.GetAccountsAsync();
       AuthenticationResult result;
       try
       {
           result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
                            .ExecuteAsync()
                            .ConfigureAwait(false);
       }
       catch
       {
           result = await PublicClientApplication.AcquireTokenInteractive(scopes)
                            .ExecuteAsync()
                            .ConfigureAwait(false);

       }

       return result;
   }

GetAsync method

 public async static Task<string> GetAsync(string url)
 {
     AuthenticationResult result = await ReturnAuthenticationResult();
     client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

     HttpResponseMessage response = await client.GetAsync(url);
     try
     {
         response.EnsureSuccessStatusCode();
         return await response.Content.ReadAsStringAsync();
     }
     catch
     {
         Console.WriteLine(response.Content.ReadAsStringAsync().Result);
         return null;
     }

 }

POST method to refresh all semantic models in a given workspace

    public static async Task<string> PostAsyncRefreshSemanticModel(string url)
    {
        AuthenticationResult result = await ReturnAuthenticationResult();
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

        JObject jsonObject = new JObject
        {
            ["notifyOption"] = "MailOnFailure",
            ["retryCount"] = "3"
        };
        HttpRequestMessage httprequestmessage = new HttpRequestMessage
        {
            Method = HttpMethod.Post,
            RequestUri = new Uri(url),
            Content = new StringContent(jsonObject.ToString(), Encoding.UTF8, "application/json")

        };
        HttpResponseMessage response = await client.SendAsync(httprequestmessage);

        response.EnsureSuccessStatusCode();
        await response.Content.ReadAsStringAsync();
        return response.StatusCode.ToString();
    }

POST method to refresh a specific semantic partition of a given workspace. This method is to be used only to refresh a specific partition

  public static async Task<string> PostAsyncRefreshPartition(string url)
  {
      AuthenticationResult result = await ReturnAuthenticationResult();
      client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

      JObject jsonObject = new JObject
      {
          ["type"] = "Full",
          ["commitMode"] = "transactional",
          ["maxParallelism"] = 2,
          ["retryCount"] = 2,
          ["timeout"] = "02:00:00",
          ["objects"] = new JArray
      {
          new JObject
          {
              ["table"] = "Dim_Customers",
              ["partition"] = "All Customers"
          }
      }
      };
      HttpRequestMessage httprequestmessage = new HttpRequestMessage
      {
          Method = HttpMethod.Post,
          RequestUri = new Uri(url),
          Content = new StringContent(jsonObject.ToString(), Encoding.UTF8, "application/json")

      };
      HttpResponseMessage response = await client.SendAsync(httprequestmessage);

      response.EnsureSuccessStatusCode();
      await response.Content.ReadAsStringAsync();
      return response.StatusCode.ToString();
  }

The method above is refreshing a partition called All Customers and table Dim_Customers in the partition. This method can be handy when a given partition does not require regular refreshes.

Details on the payload required for the API can be found here .

Method to refresh all the semantic models in a given workspace.

 public async static Task<string> RefreshWorkspace()
 {
     response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
     JObject jobject_groups = JObject.Parse(response);
     JArray jArray_groups = (JArray)jobject_groups["value"];
     foreach (JObject path in jArray_groups)
     {
         if (path["name"].ToString() == workspace)
         {
             url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
             url = url + "/datasets/";
             response = await GetAsync(url);
             JObject jobjectdatasets = JObject.Parse(response);
             JArray jArraydatasets = (JArray)jobjectdatasets["value"];
             foreach (JObject path_datasets in jArraydatasets)
             {
                 response = await PostAsyncRefreshSemanticModel(url + path_datasets["id"].ToString() + "/refreshes");
             }
         }

         url = "";
     }

     return "Success";
 }

Note : The approach above is to iterate across all the datasets for a given workspace and then make a call to the PostAsyncRefreshSemanticModel method.

Method to refresh a specific semantic model in a given workspace.

     public async static Task<string> RefreshSemanticModel(string SemanticModel)
     {
         response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
         JObject jobject_groups = JObject.Parse(response);
         JArray jArray_groups = (JArray)jobject_groups["value"];
         foreach (JObject path in jArray_groups)
         {
             if (path["name"].ToString() == workspace)
             {
                 url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
                 url = url + "/datasets/";
                 response = await GetAsync(url);
                 JObject jobjectdatasets = JObject.Parse(response);
                 JArray jArraydatasets = (JArray)jobjectdatasets["value"];
                 foreach (JObject path_datasets in jArraydatasets)
                 {
                     if (path_datasets["name"].ToString() == SemanticModel)
                     {
                         response = await PostAsyncRefreshSemanticModel(url + path_datasets["id"].ToString() + "/refreshes");
                     }

                 }

             }

             url = "";
         }

         return "Success";
     }

Method to check the status of a refreshes

  public async static Task<Dictionary<string, object>> CheckStatus()
  {
      response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
      JObject jobject_groups = JObject.Parse(response);
      JArray jArray_groups = (JArray)jobject_groups["value"];
      Dictionary<string,object> refreshDictionary = new();
      foreach (JObject path in jArray_groups)
      {
          if (path["name"].ToString() == workspace)
          {
              url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
              url = url + "/datasets/";
              response = await GetAsync(url);
              JObject jobjectdatasets = JObject.Parse(response);
              JArray jArraydatasets = (JArray)jobjectdatasets["value"];
              foreach (JObject path_datasets in jArraydatasets)
              {
                  response = await GetAsync(url + path_datasets["id"].ToString() + "/refreshes");
                  JObject JObjectrefreshresponse = JObject.Parse(response);
                  JArray JArrayrefreshresponse = (JArray)JObjectrefreshresponse["value"];
                  foreach(JObject pathrefresh in JArrayrefreshresponse)
                  {
                      refreshDictionary.Add(pathrefresh["requestId"].ToString(), pathrefresh);
                  }

              }

          }
      }

      return refreshDictionary;
  }

Below is the response of the refresh API

The return type of the CheckStatus method is a dictionary. Adding the details to the dictionary object of type <string,object>in the CheckStatusmethod.

Refresh Details

The recent update indicates that, to retrieve additional detailed information about a specific refresh request, we can use the sample notebook code seen here.

On similar lines, to create a URL that points to the details of individual refresh id for an external application, we can modify the CheckStatus method to the following.

  public async static Task<Dictionary<string, string>> CheckStatus()
  {
      response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
      JObject jobject_groups = JObject.Parse(response);
      JArray jArray_groups = (JArray)jobject_groups["value"];
      Dictionary<string,string> refreshDictionary = new();
      foreach (JObject path in jArray_groups)
      {
          if (path["name"].ToString() == workspace)
          {
              url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
              url = url + "/datasets/";
              response = await GetAsync(url);
              JObject jobjectdatasets = JObject.Parse(response);
              JArray jArraydatasets = (JArray)jobjectdatasets["value"];
              foreach (JObject path_datasets in jArraydatasets)
              {
                  response = await GetAsync(url + path_datasets["id"].ToString() + "/refreshes");
                  JObject JObjectrefreshresponse = JObject.Parse(response);
                  JArray JArrayrefreshresponse = (JArray)JObjectrefreshresponse["value"];
                  foreach(JObject pathrefresh in JArrayrefreshresponse)
                  {
                      refreshDictionary.Add(pathrefresh["requestId"].ToString(), "https://app.powerbi.com/groups/" + path["id"].ToString() + "/datasets/" + path_datasets["id"].ToString() + "/refreshdetails/" + pathrefresh["requestId"].ToString());
                  }

              }

          }
      }

      return refreshDictionary;
  }

The output of the above method is still a Dictionary object but of the type <string,string> where the key stores the RequestId and value stores the constructed URL that points to the details of the refresh request.

Clicking on one of the URL values, displays the refresh details.

Complete Code

using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.Net.Http.Headers;
using System.Text;
namespace Refresh_Model
{
    internal class Program
    {
        public static HttpClient client = new();
        public static string response = "";
        public static string url = "";
        public static string semanticmodel = "";
        public static string clientId = "";
        public static string workspace = "";
        private static string[] scopes = new string[] { "https://analysis.windows.net/powerbi/api/.default" };
        private static string Authority = "https://login.microsoftonline.com/organizations";
        private static string RedirectURI = "http://localhost";

        static async Task Main(string[] args)
        {
            ReadConfig();
            await RefreshSemanticModel(semanticmodel);
            await RefreshWorkspace();
            await CheckStatus();
        }

        public async static Task<string> RefreshSemanticModel(string SemanticModel)
        {
            response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
            JObject jobject_groups = JObject.Parse(response);
            JArray jArray_groups = (JArray)jobject_groups["value"];
            foreach (JObject path in jArray_groups)
            {
                if (path["name"].ToString() == workspace)
                {
                    url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
                    url = url + "/datasets/";
                    response = await GetAsync(url);
                    JObject jobjectdatasets = JObject.Parse(response);
                    JArray jArraydatasets = (JArray)jobjectdatasets["value"];
                    foreach (JObject path_datasets in jArraydatasets)
                    {
                        if (path_datasets["name"].ToString() == SemanticModel)
                        {
                            response = await PostAsyncRefreshPartition(url + path_datasets["id"].ToString() + "/refreshes");
                        }

                    }

                }

                url = "";
            }

            return "Success";
        }

        public async static Task<string> RefreshWorkspace()
        {
            response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
            JObject jobject_groups = JObject.Parse(response);
            JArray jArray_groups = (JArray)jobject_groups["value"];
            foreach (JObject path in jArray_groups)
            {
                if (path["name"].ToString() == workspace)
                {
                    url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();

                    url = url + "/datasets/";
                    response = await GetAsync(url);
                    JObject jobjectdatasets = JObject.Parse(response);
                    JArray jArraydatasets = (JArray)jobjectdatasets["value"];
                    foreach (JObject path_datasets in jArraydatasets)
                    {
                        response = await PostAsyncRefreshSemanticModel(url + path_datasets["id"].ToString() + "/refreshes");
                    }

                url = "";
            }

            return "Success";
        }

        public async static Task<Dictionary<string, string>> CheckStatus()
        {
            response = await GetAsync("https://api.powerbi.com/v1.0/myorg/groups");
            JObject jobject_groups = JObject.Parse(response);
            JArray jArray_groups = (JArray)jobject_groups["value"];
            Dictionary<string,string> refreshDictionary = new();
            foreach (JObject path in jArray_groups)
            {
                if (path["name"].ToString() == workspace)
                {
                    url = "https://api.powerbi.com/v1.0/myorg/groups/" + path["id"].ToString();
                    url = url + "/datasets/";
                    response = await GetAsync(url);
                    JObject jobjectdatasets = JObject.Parse(response);
                    JArray jArraydatasets = (JArray)jobjectdatasets["value"];
                    foreach (JObject path_datasets in jArraydatasets)
                    {

                        response = await GetAsync(url + path_datasets["id"].ToString() + "/refreshes");
                        JObject JObjectrefreshresponse = JObject.Parse(response);
                        JArray JArrayrefreshresponse = (JArray)JObjectrefreshresponse["value"];
                        foreach(JObject pathrefresh in JArrayrefreshresponse)
                        {

                            refreshDictionary.Add(pathrefresh["requestId"].ToString(), "https://app.powerbi.com/groups/" + path["id"].ToString() + "/datasets/" + path_datasets["id"].ToString() + "/refreshdetails/" + pathrefresh["requestId"].ToString());
                        }


                    }

                }
            }

            return refreshDictionary;
        }

        public static void ReadConfig()
        {
            var builder = new ConfigurationBuilder()
            .AddJsonFile($"local.settings.json", true, true);
            var config = builder.Build();
            clientId = config["ClientId"];
            workspace = config["Workspace"];
            semanticmodel = config["Semantic Model"];
        }

        public async static Task<string> GetAsync(string url)
        {

            AuthenticationResult result = await ReturnAuthenticationResult();
            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

            HttpResponseMessage response = await client.GetAsync(url);
            try
            {
                response.EnsureSuccessStatusCode();
                return await response.Content.ReadAsStringAsync();
            }
            catch
            {
                Console.WriteLine(response.Content.ReadAsStringAsync().Result);
                return null;
            }

        }

        public static async Task<string> PostAsyncRefreshSemanticModel(string url)
        {
            AuthenticationResult result = await ReturnAuthenticationResult();
            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

            JObject jsonObject = new JObject
            {
                ["notifyOption"] = "MailOnFailure",
                ["retryCount"] = "3"
            };
            HttpRequestMessage httprequestmessage = new HttpRequestMessage
            {
                Method = HttpMethod.Post,
                RequestUri = new Uri(url),
                Content = new StringContent(jsonObject.ToString(), Encoding.UTF8, "application/json")

            };
            HttpResponseMessage response = await client.SendAsync(httprequestmessage);

            response.EnsureSuccessStatusCode();
            await response.Content.ReadAsStringAsync();
            return response.StatusCode.ToString();
        }


        public static async Task<string> PostAsyncRefreshPartition(string url)
        {
            AuthenticationResult result = await ReturnAuthenticationResult();
            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);

            JObject jsonObject = new JObject
            {
                ["type"] = "Full",
                ["commitMode"] = "transactional",
                ["maxParallelism"] = 2,
                ["retryCount"] = 2,
                ["timeout"] = "02:00:00",
                ["objects"] = new JArray
                {
                    new JObject
                    {
                        ["table"] = "Your Table",
                        ["partition"] = "Your Partition"
                    }
                }
            };

            HttpRequestMessage httprequestmessage = new HttpRequestMessage
            {
                Method = HttpMethod.Post,
                RequestUri = new Uri(url),
                Content = new StringContent(jsonObject.ToString(), Encoding.UTF8, "application/json")

            };
            HttpResponseMessage response = await client.SendAsync(httprequestmessage);

            response.EnsureSuccessStatusCode();
            await response.Content.ReadAsStringAsync();
            return response.StatusCode.ToString();
        }

        public async static Task<AuthenticationResult> ReturnAuthenticationResult()
        {
            string AccessToken;
            PublicClientApplicationBuilder PublicClientAppBuilder =
                PublicClientApplicationBuilder.Create(clientId)
                .WithAuthority(Authority)
                .WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
                .WithRedirectUri(RedirectURI);

            IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
            var accounts = await PublicClientApplication.GetAccountsAsync();
            AuthenticationResult result;
            try
            {

                result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
                                 .ExecuteAsync()
                                 .ConfigureAwait(false);

            }
            catch
            {
                result = await PublicClientApplication.AcquireTokenInteractive(scopes)
                                 .ExecuteAsync()
                                 .ConfigureAwait(false);

            }

            return result;

        }
    }

}

Conclusion

Through this write up I tried to demonstrate how the API’s can be customized for a specific need, whether it requires refresh of a single semantic model or all the semantic models in the given workspace. Also I have highlighted how to leverage the new additions to the recent update of the refresh API’s.

The only missing feature in my opinion is the ability to return the refresh details directly in the response rather than requiring the construction of a URL that points to a new location.

Thanks for reading !!!

0
Subscribe to my newsletter

Read articles from Sachin Nandanwar directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Sachin Nandanwar
Sachin Nandanwar