Microsoft has just published a new REST API for Power BI Preview that allows you to push data into cloud based datasets sitting in their cloud environment. Over the weekend, I put together some test code to explore the possibilities of the new API. (You can find the sample code here on github). The REST API is still quite limited in its abilities but supports a key scenario for the new Power BI Preview Service – pushing data into Power BI Preview in real time.

Getting Started

In order to access the Power BI Preview REST API, you will need to authenticate your application and your user identity through Azure Active Directory. The way you do this is to set up an Azure AD and create a profile for your application under configuration.

The client id is a key to that is supplied by Azure to be included when you pass in credentials from your application. The Redirect URI is the page for logging into Azure AD – for a console app this should be https://login.live.com/oauth20_desktop.srf and for a web application it should be the page which you create to receive the token that Azure AD generates when you authenticate. The page needs to be registered here and needs to match what is passed in along with the authenticate request.

You also need to grant permissions to the Power BI Service in order to use the REST API to push data. image

Now that this is configured you can start building an application.

Scenario #1: Building a Basic Console Application

My first attempt was to build a basic console application that created a test dataset and pushed in some data. In building this application, I also built a PowerBIDataTransferService class that manages the various interactions with the rest API. I can use the same class with the second scenario below.

The way that the Power BI Preview API works is all JSON and HTTP based – you send in commands with JSON data as part of your HTTP call and Power BI Preview responds with an HTTP Response typically with JSON data included in the response.

Logging into Power BI REST API

The first step is to login. The login method looks like this:

public void Login()
{
//Create a new AuthenticationContext passing an Authority.
AuthenticationContext authContext = new AuthenticationContext(authority);
//Get an Azure Active Directory token by calling AcquireToken
if (Username != "" && Username != null)
{
UserCredential user = new UserCredential(Username, Password);
token = authContext.AcquireToken(resourceUri, clientID, user).AccessToken.ToString();
}
else
{
token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectURI)).AccessToken.ToString();
}
}

The class supports two different scenarios: 1) you use Microsoft’s login URI for console apps and when you run the app it will prompt you to login or 2) you supply a username and password directly. In either scenario, the key thing you get back is a token from Azure AD that you pass into each of your REST API Preview Calls.

Creating a Dataset

The next step is to create a dataset. A dataset is a collection of tables and tables have columns that can be one of the following types: int64, bool, DateTime, string and double. Creating a dataset involves structuring JSON data to represent this schema. What I did was to use this web site to model C# classes that could be easily serialized to the JSON required. I then used JSON.NET to serialize the dataset schema to JSON and send it off to Power BI Preview.

For sending basic DatasetRequests and HTTPRequests, I borrowed some code from Microsoft’s sample code – you can find the original code here.

Using this approach, our CreateDataset method is quite simple:

/// /// Creates a dataset based on a DatasetSchema. ///
/// Dataset Schema represents the definition of dataset including dataset name, tables and columns for each table.
/// Created dataset as .NET object.
public Dataset CreateDataset(DatasetSchema Schema)
{
try
{
//Create a POST web request to list all datasets
HttpWebRequest request = DatasetRequest(datasetsURI, "POST", token);
PostRequest(request, JsonConvert.SerializeObject(Schema));
return (FindDataset(Schema.name));
}
catch (Exception ex)
{
throw;
}
}

When you create the dataset in Power BI Preview successfully, you will see an empty dataset in Power BI Preview with your table structure. In my test console application, I created a table with a test int, test date, test bool, test double and test string column.

NOTE: There doesn’t seem to be yet a REST API method for deleting a dataset. There also doesn’t seem to be methods for altering the dataset schema yet either.

Adding Rows

Once you have a dataset created, you can now add rows to the table. I created a simple test that pushed a row with a random int every 5 seconds.

public static void AddRows(Object myObject, EventArgs myEventArgs)
{
Random random = new Random();
double randomDouble = random.NextDouble() * 5;
int randomInt = random.Next(1, 5);
ArrayList rows = new ArrayList();
        rows.Add(new TestRow() { TestColumnBool = true, TestColumnDateTime = DateTime.Now, TestColumnDouble = randomDouble, TestColumnInt = randomInt, TestColumnString = "test" });
        PowerBI.AddRow(dataset, "testTable", rows);
    }

Again, in this case I have encapsulated the raw JSON by allowing you to use a basic value object defined in C# that is then translated serialized dynamically into JSON when the request is passed. The translation works by inspecting the object and translating the public properties into the appropriate JSON values.

Testing It Out

If you are successful and sending in the right REST API calls you will see a new dataset created and the table being populated with rows. The cool thing about the new Power BI Preview is the dashboards are updated in real time so if you have created a graph you will see it being updated as data is added. Here is an example of the data I added from my test application.