Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Introduction to adding Datasets using Power BI API

Iu-Wei Sze

Introduction:

I recently worked on a POC using the Power BI API. The purpose of the POC was to be able to add a dataset from a SQL database into my Power BI workspace through the use of a console application instead of manually having doing it from the GUI in Power BI. The goal was to make it abstract enough so that we can use it for any database in SQL by simply adjusting a few parameters inside an app.config file.

To get an example of how one would use the Power BI API in visual studio, I referred to this github repository to be a base for my application. There are some things that the example doesn’t show you which is what I’ll be focusing on in this article.

Since the purpose of this article is to go over lesser known features of the Power BI API, it’s assumed you have basic knowledge of how to set up the API itself. For those unfamiliar with the set up process, refer to this link for a complete guide on setting up the client application to use the Power BI Rest resources.

 

Changing the Credentials input GUI:

While following the github example, one of the things I noticed was that they didn’t specify how we would handle automatically logging into Power BI without the GUI screen popping up each time. This is easily done by modifying the AccessToken() method.

Replace this line below to change your login method:

token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri), PromptBehavior.RefreshSession).AccessToken;

To…

token = authContext.AcquireToken(resourceUri, clientID, new UserCredential(PowerBiUser, PowerBiPassword)).AccessToken;

Where PowerBiUser and PowerBiPassword are strings for your username/email and password to login to power bi from the Power BI Dev site. With this change, you should be able to automatically login without having to force the user to input their credentials each time.

 

Creating Datasets within Power BI using JSON:

The example shows how one would use classes that represent each datatable and create a JSON string from that to send to power bi. However, I found a way to bypass the additional step to create new classes for each datatable by building the JSON string myself. This would be useful in situations where you are dynamically reading in the properties of the SQL Database you’re adding to your workspace and don’t want to deal with using Reflection in .NET.

For creating datasets, the JSON string has the following set up, this is in form of a string to make it easier for copy and pasting:

"{\"name\":\"" + (name of dataset) + "\",\"tables\":[{\"name\":\"" + (datatable name) + "\",\"columns\":[“ + (column information) + “]}]}"

Where the “column information” section is another set of JSON strings that indicate the column name and data type (for example Int64, nvarchar, float, etc).

Each column that is added to the dataset will have the following details:
"{\"name\": \"" + (name of the column) + "\", \"dataType\": \"" + (the datatype of this column) + "\"}"

Note: While creating this method to send JSON, I found out that power bi doesn’t take int32 so all Int types from SQL or visual studio would have to be converted to a Long (also known as Int64).

 

Modifying Existing Datasets in Power BI using JSON:

The idea of sending JSON to modify a dataset already in Power BI is similar to Create but in this case, you would send a DatasetRequest method similar to the following to specify the dataset and table you wish to modify.
DatasetRequest(String.Format("{0}/datasets/{1}/tables/{2}/rows", datasetsUri, datasetId, tableName), "POST", AccessToken());

The following is the JSON string for the rows:
"{\"rows\":[{“ + (rows information) + "}]}"

We specify the column name and value for each row we are adding. For example, if this was the JSON string:
"{\"rows\":[{\"id\":1,\"name\":\"John\",\"category\":null}]}"

It’s send a single row where the column “id” is 1, the “name” field is John and there is a null value in “category”. This is under the assumption that there are no additional columns being added when appending the row.

 

Adding Columns To Existing Datasets:

There is an UpdateTableSchema method in the github example which is used when we want to add another column to a preexisting dataset in Power BIThis same trick to send the data as JSON can be applied in that situation as well and is extremely similar to Create Dataset’s JSON. This is the JSON that will be sent to append a new column for those interested:
"{\"name\":\"" + tableName + "\",\"columns\":[" + columnsInfo + "]}"

Each column being appended should have the following details inside columnsInfo:
“{\”name\”: \”” + columnName + “\”, \”dataType\”: \”” + columnDataType+ “\”}”

There are more functionalities using the Power BI API that were not mentioned in this article. For those who are interested in exploring more about the API, here are a few links to get you started:
Documentation On Power BI REST API
Registering a Web App instead of Client App
Authenticating to Power BI
Limitation in Power BI Workspace

_________________________________________________________________________________________

To learn more on how Tallan can transform your data analytics into actionable insights and help you maximize profitability, CLICK HERE.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\