次の方法で共有

Building Apps with the new Power BI APIs

  • [アーティクル]

Last month, Microsoft unveiled the new and improved Power BI, a cloud-based business analytics service for non-technical business users. The new Power BI is available for preview in the US. It has amazing new (HTML5) visuals, data sources, mobile applications, and developer APIs. This post will focus on the new Power BI APIs and how to use them to create and load data into Power BI datasets in the cloud. Microsoft is also working with strategic partners to add native data connectors to the Power BI service. If you have a great connector idea, you can submit it HERE. However, ANYONE can build applications that leverage the new APIs to send data into Power BI, so let’s get started!

[View:https://www.youtube.com/watch?v=5DCW834Vt6I]

Yammer Analytics Revisited

I’ve done a ton of research and development on using Power BI with Yammer data. In fact, last year I built a custom cloud service that exported Yammer data and loaded it into workbooks (with pre-built models). The process was wildly popular, but required several manual steps that were prone to user error. As such, I decided to use the Yammer use case for my Power BI API sample. Regardless if you are interested in Yammer data, you will find generic functions for interacting with Power BI.

Why are Power BI APIs significant?

Regardless of how easy Microsoft makes data modeling, end-users (the audience for Power BI) don’t care about modeling and would rather just answer questions with the data. Power BI APIs can automate modeling/loading and give end-users immediate access to answers. Secondly, some data sources might be proprietary, highly normalized, or overly complex to model. Again, Power BI APIs can solve this through automation. Finally, some data sources might have unique constrains that make it hard to query using normal connectors. For example, Yammer has REST end-points to query data. However, these end-points have unique rate limits that cause exceptions with normal OData connectors. Throttling is just one example of a unique constraint that can be addressed by owning the data export/query process in a 3rd party application that uses the Power BI APIs.

Common Consent Vision

My exploration of the Power BI APIs really emphasized Microsoft’s commitments to Azure AD and "Common Consent" applications. Common Consent refers to the ability of an application leveraging Azure AD to authenticate ONCE and get access to multiple Microsoft services such as SharePoint Online, Exchange Online, CRM Online, and (now) Power BI. All a developer needs to do is request appropriate permissions and (silently) get service-specific access tokens to communicate with the different services. Azure AD will light up with more services in the future, but I’m really excited to see how far Microsoft has come in one year and the types of applications they are enabling.

Power BI API Permissions

Power BI APIs use Azure Active Directory and OAuth 2.0 to authenticate users and authorize 3rd party applications. An application leveraging the Power BI APIs must first be registered as an Azure AD Application with permissions to Power BI. Currently, Azure AD supports three delegated permissions to Power BI from 3rd party applications. These include "View content properties", "Create content", "Add data to a user’s dataset". "Delegated Permissions" means that the API calls are made on behalf of an authenticated user…not an elevated account as would be the case with "Application Permissions" ("Application Permissions" could be added in the future). The permissions for an Azure AD App can be configured in the Azure Management Portal as seen below.

Access Tokens and API Calls

With an Azure AD App configured with Power BI permissions, the application can request resource-specific access tokens to Power BI (using the resource ID "https://analysis.windows.net/powerbi/api"). The method below shows an asynchronous call to get a Power BI access token in a web project.

getAccessToken for Power BI APIs

/// <summary>/// Gets a resource specific access token for Power BI ("https://analysis.windows.net/powerbi/api")/// </summary>/// <returns>Access Token string</returns>private static async Task<string> getAccessToken(){    // fetch from stuff user claims    var signInUserId = ClaimsPrincipal.Current.FindFirst(ClaimTypes.NameIdentifier).Value;    var userObjectId = ClaimsPrincipal.Current.FindFirst(SettingsHelper.ClaimTypeObjectIdentifier).Value;     // setup app info for AuthenticationContext    var clientCredential = new ClientCredential(SettingsHelper.ClientId, SettingsHelper.ClientSecret);    var userIdentifier = new UserIdentifier(userObjectId, UserIdentifierType.UniqueId);     // create auth context (note: no token cache leveraged)    AuthenticationContext authContext = new AuthenticationContext(SettingsHelper.AzureADAuthority);     // get access token for Power BI    return authContext.AcquireToken(SettingsHelper.PowerBIResourceId, clientCredential, new UserAssertion(userObjectId, UserIdentifierType.UniqueId.ToString())).AccessToken;}

 

The Power BI APIs offer REST endpoints to interact with datasets in Power BI. In order to call the REST end-points, a Power BI access token must be placed as a Bearer token in the Authorization header of all API calls. This can be accomplished server-side or client-side. In fact, the Power BI team has an API Explorer to see how most API calls can be performed in just about any language. I decided to wrap my API calls behind a Web API Controller as seen below. Take note of the Bearer token set in the Authorization header of each HttpClient call.

Web API Controller

public class PowerBIController : ApiController{    [HttpGet]    public async Task<List<PowerBIDataset>> GetDatasets()    {        return await PowerBIModel.GetDatasets();    }     [HttpGet]    public async Task<PowerBIDataset> GetDataset(Guid id)    {        return await PowerBIModel.GetDataset(id);    }     [HttpPost]    public async Task<Guid> CreateDataset(PowerBIDataset dataset)    {        return await PowerBIModel.CreateDataset(dataset);    }     [HttpDelete]    public async Task<bool> DeleteDataset(Guid id)    {        //DELETE IS UNSUPPORTED        return await PowerBIModel.DeleteDataset(id);    }     [HttpPost]    public async Task<bool> ClearTable(PowerBITableRef tableRef)    {        return await PowerBIModel.ClearTable(tableRef.datasetId, tableRef.tableName);    }     [HttpPost]    public async Task<bool> AddTableRows(PowerBITableRows rows)    {        return await PowerBIModel.AddTableRows(rows.datasetId, rows.tableName, rows.rows);    }}

 

Power BI Model Class

/// <summary>/// Gets all datasets for the user/// </summary>/// <returns>List of PowerBIDataset</returns>public static async Task<List<PowerBIDataset>> GetDatasets(){    List<PowerBIDataset> datasets = new List<PowerBIDataset>();    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient{ BaseAddress = baseAddress })    {        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json; odata=verbose");        using (var response = await client.GetAsync("datasets"))        {            string responseString = await response.Content.ReadAsStringAsync();            JObject oResponse = JObject.Parse(responseString);            datasets = oResponse.SelectToken("datasets").ToObject<List<PowerBIDataset>>();        }    }     return datasets;} /// <summary>/// Gets a specific dataset based on id/// </summary>/// <param name="id">Guid id of dataset</param>/// <returns>PowerBIDataset</returns>public static async Task<PowerBIDataset> GetDataset(Guid id){    PowerBIDataset dataset = null;    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient { BaseAddress = baseAddress })    {        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json; odata=verbose");        using (var response = await client.GetAsync(String.Format("datasets/{0}", id.ToString())))        {            string responseString = await response.Content.ReadAsStringAsync();            JObject oResponse = JObject.Parse(responseString);        }    }     return dataset;} /// <summary>/// Creates a dataset, including tables/columns/// </summary>/// <param name="dataset">PowerBIDataset</param>/// <returns>Guid id of the new dataset</returns>public static async Task<Guid> CreateDataset(PowerBIDataset dataset){    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient{ BaseAddress = baseAddress })    {        var content = new StringContent(JsonConvert.SerializeObject(dataset).Replace("\"id\":\"00000000-0000-0000-0000-000000000000\",", ""), System.Text.Encoding.Default, "application/json");        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json");        using (var response = await client.PostAsync("datasets", content))        {            string responseString = await response.Content.ReadAsStringAsync();            JObject oResponse = JObject.Parse(responseString);            dataset.id = new Guid(oResponse.SelectToken("id").ToString());        }    }     return dataset.id;} /// <summary>/// !!!!!!!!!!!! THIS IS CURRENTLY UNSUPPORTED !!!!!!!!!!!!/// Deletes a dataset/// </summary>/// <param name="dataset">Guid id of the dataset</param>/// <returns>bool indicating success</returns>public static async Task<bool> DeleteDataset(Guid dataset){    bool success = false;    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient { BaseAddress = baseAddress })    {        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json");        using (var response = await client.DeleteAsync(String.Format("datasets/{0}", dataset.ToString())))        {            string responseString = await response.Content.ReadAsStringAsync();            success = true;        }    }     return success;} /// <summary>/// Clear all data our of a given table of a dataset/// </summary>/// <param name="dataset">Guid dataset id</param>/// <param name="table">string table name</param>/// <returns>bool indicating success</returns>public static async Task<bool> ClearTable(Guid dataset, string table){    bool success = false;    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient { BaseAddress = baseAddress })    {        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json");        using (var response = await client.DeleteAsync(String.Format("datasets/{0}/tables/{1}/rows", dataset.ToString(), table)))        {            string responseString = await response.Content.ReadAsStringAsync();            success = true;        }    }     return success;} /// <summary>/// Adds rows to a given table and dataset in Power BI/// </summary>/// <param name="dataset">PowerBIDataset</param>/// <param name="table">PowerBITable</param>/// <param name="rows">List<Dictionary<string, object>></param>/// <returns></returns>public static async Task<bool> AddTableRows(Guid dataset, string table, List<Dictionary<string, object>> rows){    bool success = false;    var token = await getAccessToken();    var baseAddress = new Uri("https://api.powerbi.com/beta/myorg/");    using (var client = new HttpClient { BaseAddress = baseAddress })    {        //build the json post by looping through the rows and columns for each row        string json = "{\"rows\": [";        foreach (var row in rows)        {            //process each column on the row            json += "{";            foreach (var key in row.Keys)            {                json += "\"" + key + "\": \"" + row[key].ToString() + "\",";            }            json = json.Substring(0, json.Length - 1) + "},";        }        json = json.Substring(0, json.Length - 1) + "]}";        var content = new StringContent(json, System.Text.Encoding.Default, "application/json");        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);        client.DefaultRequestHeaders.Add("Accept", "application/json");        using (var response = await client.PostAsync(String.Format("datasets/{0}/tables/{1}/rows", dataset.ToString(), table), content))        {            string responseString = await response.Content.ReadAsStringAsync();            success = true;        }    }     return success;}

 

Here are a few examples of calling these Web API methods client-side.

Client-side Calls to Web API

// sets up the dataset for loadingfunction createDataset(name, callback) {    var data = {        name: name, tables: [{            name: "Messages", columns: [                { name: "Id", dataType: "string" },                { name: "Thread", dataType: "string" },                { name: "Created", dataType: "DateTime" },                { name: "Client", dataType: "string" },                { name: "User", dataType: "string" },                { name: "UserPic", dataType: "string" },                { name: "Attachments", dataType: "Int64" },                { name: "Likes", dataType: "Int64" },                { name: "Url", dataType: "string" }]        }]};     $.ajax({        url: "/api/PowerBI/CreateDataset",        type: "POST",        data: JSON.stringify(data),        contentType: "application/json",        success: function (datasetId) {            callback(datasetId);        },        error: function (er) {            $("#alert").html("Error creating dataset...");            $("#alert").show();        }    });} // clear rows from existing datasetfunction clearDataset(datasetId, callback) {    var data = { datasetId: datasetId, tableName: "Messages" };    $.ajax({        url: "/api/PowerBI/ClearTable",        type: "POST",        data: JSON.stringify(data),        contentType: "application/json",        success: function (data) {            callback();        },        error: function (er) {            $("#alert").html(("Error clearing rows in dataset {0}...").replace("{0}", $("#cboDataset option:selected").text()));            $("#alert").show();        }    });} // adds rows to the datasetfunction addRows(datasetId, rows, callback) {    var data = { datasetId: datasetId, tableName: "Messages", rows: rows };    $.ajax({        url: "/api/PowerBI/AddTableRows",        type: "POST",        data: JSON.stringify(data),        contentType: "application/json",        success: function (data) {            callback();        },        error: function (er) {            $("#alert").html("Error adding rows to dataset");            $("#alert").show();        }    });}

 

My application can create new datasets in Power BI or update existing datasets. For existing datasets, it can append-to or purge old rows before loading. Once the processing is complete, the dataset can be explored immediately in Power BI.

Conclusion

The new Power BI is a game-changer for business analytics. The Power BI APIs offer amazing opportunities for ISVs/Developers. They can enable completely new data-driven scenarios and help take the modeling burden off the end-user. You can download the completed solution outlined in this post below (please note you will need to generate your own application IDs for Azure AD and Yammer).

Solution Download

Comments

  • Anonymous
    March 02, 2015
    The comment has been removed

  • Anonymous
    March 31, 2015
    The comment has been removed

  • Anonymous
    June 05, 2015
    This article has been well written and useful. I am running the Web App Sample with AAD to connect to my Power BI datasets. I am getting below exception, any idea.... Resource 'api.powerbi.com/.../datasets& is not registered for the account.

  • Anonymous
    March 14, 2016
    Can we take table data from SQL and connect it as a data source in Power BI APIs?