Connecting to Power BI REST API from a Service (i.e. No User Present)

Introduction

This post will show you the following using C#:

• How to obtain an access token and authenticate with the Power BI REST API
• How to make a request to the Power BI REST API using an access token

Background

I wanted to push data into Power BI. Searching online, I was introduced to the Power BI REST API. This API allows an application to create DataSets and Tables within Power BI. Furthermore, you can clear Tables and push new data / rows. Unfortunately, at the time of writing this tip, all examples of how to authenticate and connect to this REST API were using a web or desktop application. The idea being that the user would be prompted via some sort of UI to login using OAuth and then be returned to the application at which point calls can be made to the web service (refer to this link for more details). This was not sufficient for my purposes.

My application was a service that would execute on a schedule and no physical user or UI would exist. The following solution is a very simple way to obtain an Authorization Token that can be used to authenticate your application when making calls to the Power BI REST API.

Using the Code

To use this code, you will need the following information:

• Login Address
  You can use "https://login.windows.net/common/oauth2/token"
   
• Resource Uri
  You can use "https://analysis.windows.net/powerbi/api"
   
• Client ID
  This is a GUID that you can obtain from your Azure account. Go to this link for details on how to obtain this value.
   
• Grant Type
  Use the text "password". 
   
• Username
  This is your username used to access Power BI.
   
• Password
  This is your password used to access Power BI.
   
• Client Secret
  This is a Base64 encoded string that you can obtain from your Azure account. Go to this link for details on how to obtain this value.
• Power BI REST URI
  The base URI is "https://api.powerbi.com/". This can be appended to depending on the operation you want to perform. For example, to select a list of tables you can use "https://api.powerbi.com/v1.0/myorg/datasets/{DataSet ID}/tables".

IMPORTANT: You need to make sure you have the appropriate access to interact with the API. Go to this link for more details.

Copy the following C# method into a new project in Visual Studio. I tested using a console app but you can use anything you want. Pass the values described previously into the method. Please note that this example utilizes NewtonSoft (get via Nuget) for json deserialization.

public static string GetAccessToken(string resourceUri, string clientID, 
	string grantType, string username, string password, string scope, 
		string clientSecret, string loginAddress)
        {
            StringBuilder body = new StringBuilder();
            body.Append("resource=" + HttpUtility.UrlEncode(resourceUri));
            body.Append("&client_id=" + HttpUtility.UrlEncode(clientID));
            body.Append("&grant_type=" + HttpUtility.UrlEncode(grantType));
            body.Append("&username=" + HttpUtility.UrlEncode(username));
            body.Append("&password=" + HttpUtility.UrlEncode(password));
            body.Append("&scope=" + HttpUtility.UrlEncode(scope));
            body.Append("&client_secret=" + HttpUtility.UrlEncode(clientSecret));

            using (WebClient web = new WebClient())
            {
                web.Headers.Add("client-request-id", Guid.NewGuid().ToString());
                web.Headers.Add("return-client-request-id", "true");

                string data = web.UploadString(loginAddress, body.ToString());

                dynamic result = JsonConvert.DeserializeObject(data);

                try
                {
                    return result.access_token;
                }
                catch { // Log as you want }
            }

            return null;
        }

You can then simply use the token within your web service requests. For example, the following will select a list of DataSets.

public static Dictionary<string, string> GetDataSets(string accessToken)
        {
            using (WebClient webClient = new WebClient())
            {
                webClient.Headers.Add("Authorization", String.Format("Bearer {0}", accessToken));

                try
                {
                    string json = webClient.DownloadString("https://api.powerbi.com/v1.0/myorg/datasets");

                    dynamic response = JsonConvert.DeserializeObject(json);

                    Dictionary<string, string> datasets = new Dictionary<string, string>();

                    foreach(var dataset in response.value)
                    {
                        datasets.Add(dataset.name.ToString(), dataset.id.ToString());
                    }

                    return datasets;
                }
                catch (Exception e) { // Log as you want }
            }

            return null;
        }

The returned Dictionary contains DataSet names as the key and id as the value (GUID). The DataSet id will be needed for most operations.

Points of Interest

Getting authentication working and pushing data into Power BI proved to be challenging at first. Please let me know if there is anything unnecessary that is being done within the authentication example above. My company is now pushing large amounts of data into Power BI DataSets / Tables on hourly and daily schedules. This is then distributed to the rest of the organization using Content Packs. Other users cannot see the DataSets you create until they have imported a Content Pack that contains the DataSets. Users can then create very powerful reports and dashboards using this data which is automatically updated when new data is pushed up by the service. I was new to Power BI but have been impressed with what you can do with data (especially for the relatively low cost of Power BI!).

History

There is obviously a lot more to Power BI. However, this will hopefully get you past the authentication stage quickly and interacting with the Power BI REST API. You can find out more about specific operations available within the Power BI REST API by going here.