Refresh PowerBI reports using C# and web API

In this article we will see how can we connect our custom application to connect to PowerBI instance and trigger  data refresh request and fetch refresh histories.

Register application with Azure

The first step to take is to make sure to register your application with Azure AD. You can do it 2 ways.

• https://portal.azure.com
• https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-walkthrough-push-data-register-app-with-azure-ad/

It is possible to register both ways but 2nd one is more preferable since it provides you all the details and it is very easy and simple. Do not forget to select app type as Native App.

Once you have registered your application with azure, you will receive ClientID. Keep this ClientID safe.

Install NuGet Packages for PowerBI

PowerBI community has created a Github repository for C# and Javascript embaded solutions. You can find more details here.

To work with Power BI reports you can use javascript and C# but I will be posting details related to C# only. Details regarding Javascript embeded solution can be found here. The Microsoft.PowerBI.API is a REST client which helps us consume the PowerBI embeded services.

To use this services you have install this Nuget package through package manager console:

Now you copy and paste and click enter to install the package. This package will install all the required components to develop your application.

Install-Package Microsoft.PowerBI.Api

You might also be required to install a package for AD authentication library:

Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

Connect to PowerBI Instance

Apart from Username, Password to connect to powerbi and ClientID which you received from azure registration, You would need 3 more information to connect to powerBI using C#.

• Authority URL: This url is required to instantiate the AuthenticationContext class which is required for address validation. The Authority URL is:

https://login.windows.net/common/oauth2/authorize

• Resource URL: This url is required to acquire the accesstoken after using has been authenticated. The access token will be used while communicating with powerbi to trigger refresh and fetch refresh histories. The Resource URL is:

https://analysis.windows.net/powerbi/api

• API URL: This url is required to instantiate the PowerBIClient class. The API URL is:

https://api.powerbi.com

Get the Access Token

Follow below mentioned steps to get the access token based on above mentioned information:

As you can see the 4th line in above snippet it uses the AccessToken property of authenticationResult object. This token will be used to trigger refresh.

API groups from PowerBI Client

Following api groups are available by PowerBI Client:

• Dashboards
• Datasets
• Gateways
• Groups
• Imports
• Reports

As quoted in Github post, There are multiple variations for each method. For example, to get a list of reports you can use one of the methods below:

1. client.Reports.GetReports() – Synchronous method to get a list of reports in “My Workspace”.
2. client.Reports.GetReportsInGroup(groupId) – Synchronous method to get a list of reports in specific group (e.g. App workspace).
3. client.Reports.GetReportsAsync() – async method to get a list of reports in “My Workspace”.
4. client.Reports.GetReportsInGroupAsync(groupId) – async method to get a list of reports in specific group (e.g. App workspace).

You can explore all the above mentioned options in your solution, however we will be working on to get Dataset.

Trigger Dataset Refresh

The new PowerBI APIs will allow you to programmatically trigger data refreshes and retrieve refresh history for any dataset that you own. You can read more in detail here.

To trigger refresh for a dataset in the Power BI service, simply make the following HTTP Request.

POST https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes

For those who are new to C# or those who are not sure about how to use above call, i am providing below sample code:

Note carefully that above code uses groupId while creating request. You can fetch Group related data from method exposed by PowerBIClient as mentioned above. However if you do not have group information then you can create request as below, it only uses datasetId:

Get Refresh History

For a particular dataset by making a GET request to the same endpoint.

Sample code is as below:

As you can see, code for triggering refresh and getting history is almost same with 2 difference.

• While trigger request is a POST request, getting history is a GET request.
• While getting history uses a top parameter, which limits the number of records returned.

So now we have successfully requested a refresh on our dataset and then fetched the history for verification.

I hope this article would have helped you.