PowerBI API in .Net

Article
1/17/2024

The French version is here.

1 - Introduction

Through this article, I am going to introduce PowerBI REST API, along the work I have done with the advent of this technology. During the MS TechDays 2015 in France, I have had the opportunity to be involved in the build of a real-time data processing prototype using Apache Storm.

The purpose of this process was the data gathering in a PowerBI Dashboard. While developing the project, I discovered PowerBI REST API, which is a service allowing to store data and then to use them straightforward from PowerBI within Office365.

First, we are going to focus on how to use the PowerBI REST API. I will give you an introduction to a library which makes it easier to consume REST services, followed by its upgrade to be able to use it within Xamarin applications.

Finally, we are going to build a use case in order to understand how to put together a cross-platform mobile application and PowerBI REST API.

Notes :

- This article is not a tutorial for PowerBI REST API, but a sample of usage. For this matter, the PowerBI tools must be installed and configured in the Azure portal. If not, please refer to the MSDN documentation https://msdn.microsoft.com/en-us/library/dn877544.aspx

For a good understanding of the cross-platform mobile applications development, you can find all the information you need on the Xamarin website : http://developer.xamarin.com/

2 - PowerBI REST API

PowerBI provides a simple and easy way to build high-quality reports that can be gathered together in a dashboard to get a better overview. Each group of report is related to a data source (Dataset). It is possible to use multiple types of data sources like Excel, SqlServer Analysis Services, Dynamics or Salesforce. To extend the usage, Microsoft provides a “Data from anywhere” solution in the form of an HTTP consumable WebAPI. With this solution, you can create tables and save data directly in the Cloud.

Before using this service, you must have a valid access token. To do so, an authentication is required on https://login.windows.net/common.oauth2/authorize using OAuth 2.0.

The PowerBI API exposes four operations to create and use data :

- Create a Dataset

Get all Datasets
Insert a data into a table
Clear a table

All these actions can be executed through a base URL, in the beta version of PowerBI REST API it’s : https://api.powerbi.com/beta/myorg/datasets

Creating a Dataset and its related tables

This operation is the most important because we must define a data structure using the following supported type definitions :

- Int64

Double
Boolean
Datetime
String

To describe a data model, a JSON format is required. The Dataset has a name and contains a list of tables. Each table contains a column list composed of a name and a valid data type. Here is a JSON example of dataset with a Product table :

{    "name": "MyDataset",    "tables": [        {            "name": "Product",            "columns": [                {                    "name": "Id",                    "dataType": "Int64"                },                {                    "name": "Price",                    "dataType": "Double"                },                {                    "name": "IsAvailable",                    "dataType": "Boolean"                },                {                    "name": "CreationDate",                    "dataType": "DateTime"                },                {                    "name": "Name",                    "dataType": "String"                }            ]        }    ]}

To create the Dataset, an HTTP POST request with the JSON on the base URL must be used. The request will return the Dataset Identifier.

Listing all Datasets

The Dataset list can be obtained from an HTTP GET request on the base URL. Its JSON format is structured like below :

{    "datasets": [        {            "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",            "name": "dataset1"        },        {            "id": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",            "name": "dataset2"        }    ]}

Inserting data into a table

To add data, an HTTP POST request is required, composed of the Dataset's identifier, the table name as parameters and the JSON set of data to insert. The URL format is :

url/id/tables/tableName/rows

For the Product table example, in the beta version of the API, it will be like :

https://api.powerbi.com/beta/myorg/datasets/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/tables/Product/rows/tables/Product/rows
The JSON request body must be an object list corresponding to the table structure. 
Here is an example of  data insertion :

{    "rows": [        {            "Id": 1,            "Price": 500.00,            "IsAvailable": true,            "CreationDate": "02/11/2015",            "Name": "Computer"        }    ]}

Clearing a table

To delete all the data in a table, an HTTP DELETE request must be sent with the same URL format as the insert operation.

3 - PowerBI REST API in .Net

To use the REST API in the Microsoft.Net ecosystem, you must reference multiple NuGet packages, including Microsoft.IdentityModel.Clients.ActiveDirectory, Microsoft.Net.Http and Newtonsoft.Json. The idea of a library to simplify the use of these packages appeared quickly. At first, the aim was to provide a reusable code and an intuitive usage of the library, even for people with little experience in .Net development. I started by making a ConfigSection available for the developer's configuration file .config. The section should contain information related to the authentication with OAuth and also the PowerBI REST API root URL. Even though the URL is the same for all in the preview version, it will be different in the Release version.

Once the configuration part has been implemented, the next goal was to trigger all the operation through a unique method. The solution of proposing an Action as a unique method parameter allows us to prepare a pre-authenticated instance first. The library will be able to specify the different operations we want to provide in place without worrying about Azure Active Directory part. Let's see how to use it !

To begin, you must add the NuGet package PowerBI.Api.Client to your projet :

PM> Install-Package PowerBI.Api.Client
Now it is necessary to specify the PowerBIConfiguration section in your .config file :

<?xml version="1.0" encoding="utf-8"?><configuration>    <configSections>        <section name="PowerBiConfiguration" type="PowerBI.Api.Client.Configuration.PowerBiConfiguration"/>    </configSections>    <PowerBIConfiguration>        <OAuth            Authority="https://login.windows.net/common/oauth2/authorize"        Resource="https://analysis.windows.net/powerbi/api"        Client="MyClientId"        User="MyUserName"        Password=""/>        <Api Url="https://api.powerbi.com/beta/myorg/datasets" />    </PowerBIConfiguration></configuration>

In the OAuth tag, you need to enter your own credentials (ClientId, User, Password).

The configuration part is now complete, you can consume the API directly with the PowerBIClient class. To call an Action, it is not possible to create an instance of this class directly, you have to use the method :

public static void Do(Action<PowerBIClient> action)

The action provides an authenticated instance with OAuth2 and is ready to be used. Here are all the available methods to consume the API with the class instance :

/// <summary>/// Get all Datasets./// </summary>public IList<Dataset> GetDatasets() /// <summary>/// Get Dataset identifier by name./// </summary>public string GetDatasetId(string datasetName) /// <summary>/// Check if a name matches with a registered Dataset/// </summary>public bool IsDatasetExist(string datasetName) /// <summary>/// Check if an identifier matches with a registered Dataset/// </summary>public bool IsDatasetIdExist(string datasetId) /// <summary>/// Create a Dataset and its related tables/// <param name="datasetName">Nom du Dataset.</param>/// <param name="types">Liste des types correspondant à des tables</param>/// </summary>public bool CreateDataset(string datasetName, params Type[] types) /// <summary>/// Insert a data into a table/// </summary>public bool Insert(string datasetId, object obj) /// <summary>/// Insert a list of data into a table/// </summary>public bool InsertAll(string datasetId, IList<object> objs) /// <summary>/// Clear a table/// </summary>public bool Delete<T>(string datasetId)

For the Product table example, here is the DTO matching with the table structure :

using System; namespace Exemple{    public class Product    {        public int Id { get; set; }        public double Price { get; set; }        public bool IsAvailable { get; set; }        public DateTime CreationDate { get; set; }        public string Name { get; set; }    }}

To create the dataset, you can simply use the following code :

PowerBIClient.Do(api => {    var isCreated = api.CreateDataset("DatasetName", typeof(Product));};

You can use the overloaded Do method returning the request result. It is then possible to process the result of the Dataset creation outside the action :

var isCreated = PowerBIClient.Do<bool>(api => {    return api.CreateDataset("DatasetName", typeof(Product));};
As you can see, the use of PowerBIClient class is straightforward !

4 - PowerBI REST API and Xamarin

Considering the fact that Xamarin is a very promising and cross-platform developing software (especially for data-driven applications), I am going to share with you the way of developing a cross-platform application that consumes the PowerBI REST API simply and efficiently.

To do so, we needed to have a Portable Class Library (PCL) that posts data to the PowerBI REST API straight from a mobile device. The most challenging part for that was the Active Directory Azure Library (ADAL). At this moment, the official released version is not a PCL and only the Pre-Release version 3.0 is available on NuGet. Therefore I had to create two projects (a classic library and a PCL version) to distinguish the two versions. For the PCL, I decided to make a custom package of ADAL, because I had to adapt some parts of the code to get a full compatibility with Xamarin. The PowerBI.Api.Client.PCL library uses the custom ADAL package and, for every mobile OS, I created a "Provider" to deal with Platform-specific developments.

Note :

- The PCL PowerBI REST API is released in its alpha version due to the fact that the referenced packages are based on ADAL alpha version source code.

To use the PCL PowerBI REST API we need to reference the NuGet package :

PM> Install-Package PowerBI.Api.Client.PCL -Pre

The singularity of a PCL library lies on the fact that it is platform independent and therefore, it i not wise to rely on the configuration section. The PowerBIClient class needed to be extended with an Initialize method inside which we provide the OAuth credentials to access to the PowerBI REST API :

PowerBIClient.Initialize(    new Api     {        Url = "https://api.powerbi.com/beta/myorg/datasets"    },     new OAuth     {         Authority = "https://login.windows.net/common/oauth2/authorize",        Resource = "https://analysis.windows.net/powerbi/api",        Client = "MyClientId",        User = "MyUser",        Password ="MyPassword"    });
Once the initialization is complete, we need to set up the "Provider" for each mobile platform, into the corresponding Xamarin project :

iOS :

PM> Install-Package PowerBI.Api.Client.ADAL.PCL.iOS -Pre

Android :

PM> Install-Package PowerBI.Api.Client.ADAL.PCL.Android -Pre

A last operation is required in order to complete the compatibility between ADAL and iOS. In the AppDelegate class of your iOS project, we need to add the following instruction to the FinishedLaunching method :

AdalInitializer.Initialize();

At this point, we will be able to push data into PowerBI REST API from iOS and Android devices in the exact same way as a "classic client" would.

Notes :

- In the PCL version, we will not be able to create Datasets. This lack is due to the fact that the PCL profile 111 does not allow us to access to the System.Reflection namespace (required to create the table columns)

At i am write this article, there is no Windows Phone 8 "Provider' in the source repository, but it will be available soon.

5 - Usecase : T-Shirt Shop

When we talk about Business Intelligence and data analysis, we imagine complex processing using servers. The possibility giver to us by PowerBI REST API to obtain data from user devices is particularly enticing. Thanks to cross-platform technologies, we are able to collect data in an heterogeneous environment in a simple way.

To demonstrate theses capabilities, I started to build a Proof of Concept. The prototype goal is to realize an online T-Shirt shopping application that sends data in the PowerBI REST API to obtain a serie of reports about :

- Sales rate per day and per product

Sales trend per hour slot
Sales breakdown by product, gender and size

These reports will be useful for Marketing purpose to maximize sales.

To start, I created the following data object containing all the data related to the T-Shirt order and three properties to treat data and time accurately:

/// <summary>/// Order./// </summary>public class Order{    /// <summary>    /// Gets or sets the product reference.    /// </summary>    /// <value>The product reference.</value>    public string ProductReference { get; set; }     /// <summary>    /// Gets or sets the name of the product.    /// </summary>    /// <value>The name of the product.</value>    public string ProductName { get; set; }     /// <summary>    /// Gets or sets a value indicating whether the order is for a men.    /// </summary>    /// <value><c>true</c> if this instance is men; otherwise, <c>false</c>.</value>    public bool IsMen { get; set; }     /// <summary>    /// Gets or sets the size.    /// </summary>    /// <value>The size.</value>    public string Size { get; set; }     /// <summary>    /// Gets or sets the order date.    /// </summary>    /// <value>The order date.</value>    public DateTime OrderDate { get; set; }     /// <summary>    /// Gets or sets the time slots.    /// </summary>    /// <value>The time slots.</value>    public DateTime TimeSlots { get; set; }     /// <summary>    /// Gets or sets the hour slots.    /// </summary>    /// <value>The hour slots.</value>    public int HourSlots { get; set; }}

Then, I created the Dataset with a console program using the CreateDataset method :

var isCreated = PowerBIClient.Do<bool>(api => api.CreateDataset("XamarinPowerShop", typeof(Order)));

I also added, to the program, the ability to delete all data in the table which was very useful to me during the development phase :

var isDelete = PowerBIClient.Do<bool>(api => api.Delete<Order>(api.GetDatasetId("XamarinPowerShop")));

After that, I decided to create a Xamarin.Forms application to be able to share 99% of the source code between the Android and the iOS versions. In the main ViewModel, I inserted the data through the insert method :

var isInsert = PowerBIClient.Do<bool>(api => api.Insert(api.GetDatasetId("XamarinPowerShop"), order));

To get an idea of the overall development, here are several screenshot of the final application :

https://raw.githubusercontent.com/Vtek/XamarinPowerShop/master/img/iOS-Screen1.png https://raw.githubusercontent.com/Vtek/XamarinPowerShop/master/img/iOS-Screen2.png https://raw.githubusercontent.com/Vtek/XamarinPowerShop/master/img/iOS-Screen6.png

And here are the reports made using PowerBI :

https://raw.githubusercontent.com/Vtek/XamarinPowerShop/master/img/PowerBI.png

You can check all the source code of this prototype on Github : XamarinPowerShop

As you might see, the source repositories do not include a Windows Phone 8 project. The application for this OS will be added soon !

6 - Conclusion

In this article, we have had the opportunity to discover some of the PowerBI REST API capabilities. It was very pleasing to discover that we can use it in a simple and straightforward way and that it allows us to get efficient results in a few lines of code.

I had a lot of fun working on this project. PowerBI is a great and ergonomic tool, with easy grip, even for people like me who are not experts in Business Intelligence.

I had the opportunity to combine the usage of the PowerBI REST API with mobile development and I must say that it offers many usage scenarios for both technologies.

You can find all the achieved work on Github :

- PowerBI.Api.Client