How to use Power BI Embedded via REST

Uncategorized

By Tsuyoshi Matsuzaki on 2016-07-20 • ( 10 Comments )

Power BI Workspace Collection (old version of Power BI Embedded) in Azure is deprecated. Please see my post for new embed model. (July 14, 2017)

 

This post was published as the official document on 02 Aug/2016. Refer “How to use Power BI Embedded with REST”

I described how to use Power BI Embedded REST in the previous post “App Dev using Power BI Embedded without SDK (PHP, etc)“, but which is based on Power BI Embedded Preview. This time, I explain about the GA (General Availability) version of Power BI Embedded.

In the released (GA) version, several features (authentication, etc) were updated or added.
(see the team blog “What’s new and what’s next for Power BI Embedded (July 2016)” for details about the updated features.)

Power BI Embedded : “What is ?” and “What for ?”

The overview of Power BI Embedded is described in the official site (see “Microsoft Azure : Power BI Embedded“), but first I explain about this service straightforward and shortly.

The ISV (or Cloud Solution Vendor, CSV) often wants to use the Power BI in their own application as UI building blocks.

You know, the embedding the Power BI reports or tiles into your web page is already possible without Power BI Embedded. (see my previous post “embedding the Power BI UI using iframe“.)
When you want to share your reports in your same organization, you can embed the reports with Azure AD authentication. (The user who views the reports must login using their own Azure AD account.) When you want to share your reports for all users (including external users), you can simply embed with anonymous access.

But you see, this simple embed solution doesn’t match the case of ISV application.
Almost ISV (or CSV) applications need to deliver the data for each customers. For example, if you are delivering some service for both company A and company B, the users of company A will see the data of only company A. That is, the multi-tenancy is needed for the delivery.
The ISV application might also be offering their own authentication methods (forms auth, basic auth, etc). Then the embedding solution must collaborate with this existing authentication methods safely. (Of course, it is not allowed that all user who knows the url can access the corporate sensitive data.)
It is also needed that all users of ISV applications can use without Power BI subscription (without the extra purchase or licensing).

That is, the Power BI Embedded is designed for this kind of all ISV (or CSV) scenarios. (i.e. the Power BI Embedded resolves these problems.)
I explain how to use this Power BI Embedded along with these scenarios in this blog post.

You can use .NET (C#) or Node.js SDK, and you can easily build your application with Power BI Embedded.
But, in this blog post, I explain about HTTP flow (incl. AuthN) of Power BI without SDKs. Understanding this flow, you can build your application with any programming language, and this helps you to understand the essence of Power BI Embedded.

A lot of existing ISV applications (AvePoint, NINTEX, etc) are already using this Power BI Embedded. See “Power BI blog : Power BI Embedded is now Generally Available!” for these released applications.

Create Power BI workspace collection, and get access key (Provisioning)

Power BI Embedded is one of the Azure services. Only the ISV who uses Azure Portal is charged for usage fee (per hourly user session), and the user (who views the report) is not charged.
Before starting the application development, you must create the Power BI workspace collection using Azure Portal. (see the following picture.)

Note (Important !) : Currently, please select US location (East US 2, etc) for the Power BI Embedded Workspace Collection. Now several APIs are not supported in other locations like Japan, Brazil, etc, and some APIs return the error (status: 404 Not Found). (Described at 2016/10)

Each workspace of Power BI is the workspace for each customers (tenant), and you can add many workspaces in each workspace collection. (The same access key is used in each workspace collection. i.e, the workspace collection is the security boundary for Power BI Embedded.)

When you finish to create the workspace collection, you should copy the access key in this workspace collection within the Azure Portal.

In this post I’m doing these tasks manually, but you can also provision the workspace collection and get access key via REST API. (i.e, if you need, you can automate these provisioning works.)

Power BI Resource Provider APIs
https://msdn.microsoft.com/en-us/library/azure/mt712306.aspx

Create pbix file with Power BI Desktop (Provisioning)

Next, you must create the data connection and reports to be embedded.
For this task, there’s no need to programming (no code). You just use the Power BI Desktop.

In this blog post, I don’t explain the details about how to use the Power BI Desktop. See the tutorial document of Power BI Desktop usage.

Note : Currently (Oct 2016), the database with basic credential is supported for the data source of Power BI Embedded, and other data sources are not supported. Moreover, the on-premise datasource through Power BI gateway is not also supported in Power BI Embedded.
(i.e, only supported for Azure SQL Database, Azure SQL Data Warehouse, and etc.)

When you finish, please save the report in your local disk as pbix file (.pbix).

Create Power BI workspace

Provisioning is all done.
First, let’s get started to create a customer’s workspace in the previous workspace collection via REST APIs. The following HTTP POST Request (REST) is creating the new workspace in your existing workspace collection. (We assume that the workspace collection name is “mywsc01”.)
You just set the access key which is previously copied as “AppKey”. (It’s very simple authentication !)

HTTP Request

POST https://api.powerbi.com/v1.0/collections/mywsc01/workspaces
Authorization: AppKey 9BKsnTVkRP...

HTTP Response

HTTP/1.1 201 Created
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true
Location: https://wabi-us-east2-redirect.analysis.windows.net/v1.0/collections/mywsc01/workspaces
RequestId: 4220d385-2fb3-406b-8901-4ebe11a5f6da

{
  "@odata.context": "http://wabi-us-east2-redirect.analysis.windows.net/v1.0/collections/mywsc01/$metadata#workspaces/$entity",
  "workspaceId": "32960a09-6366-4208-a8bb-9e0678cdbb9d",
  "workspaceCollectionName": "mywsc01"
}

The returned “workspaceId” is used for the following subsequent api calls, and your application must remember this value.

Import pbix file into your workspace

Each workspace can host a single Power BI design work and dataset (including datasource settings), i.e. pbix file. Your application must import your pbix file (which is previously prepared) to the workspace as follows.
As you can see, you can upload the binary of pbix file using MIME multipart in http.

The uri fragment “32960a09-6366-4208-a8bb-9e0678cdbb9d” is the workspace id (see the previous result), and query parameter “datasetDisplayName” is the dataset name to create. (The created dataset holds all data related artifacts in pbix file, such as imported data, and the pointer to the data source, etc).

POST https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/imports?datasetDisplayName=mydataset01
Authorization: AppKey 9BKsnTVkRP...
Content-Type: multipart/form-data; boundary="A300testx"

--A300testx
Content-Disposition: form-data

{the content (binary) of .pbix file}
--A300testx--

This import task might be a long running. Then this task is once accepted as follows, and your application can ask the task status using import id. (In this example, the import id is “4eec64dd-533b-47c3-a72c-6508ad854659”.)

HTTP/1.1 202 Accepted
Content-Type: application/json; charset=utf-8
Location: https://wabi-us-east2-redirect.analysis.windows.net/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/imports/4eec64dd-533b-47c3-a72c-6508ad854659?tenantId=myorg
RequestId: 658bd6b4-b68d-4ec3-8818-2a94266dc220

{"id":"4eec64dd-533b-47c3-a72c-6508ad854659"}

The following is asking the status using this import id.

GET https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/imports/4eec64dd-533b-47c3-a72c-6508ad854659
Authorization: AppKey 9BKsnTVkRP...

If the task is not completed (still running), the HTTP response could be the following.

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
RequestId: 614a13a5-4de7-43e8-83c9-9cd225535136

{
  "id": "4eec64dd-533b-47c3-a72c-6508ad854659",
  "importState": "Publishing",
  "createdDateTime": "2016-07-19T07:36:06.227",
  "updatedDateTime": "2016-07-19T07:36:06.227",
  "name": "mydataset01"
}

If the task is completed (all done), the HTTP response could be the following.

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
RequestId: eb2c5a85-4d7d-4cc2-b0aa-0bafee4b1606

{
  "id": "4eec64dd-533b-47c3-a72c-6508ad854659",
  "importState": "Succeeded",
  "createdDateTime": "2016-07-19T07:36:06.227",
  "updatedDateTime": "2016-07-19T07:36:06.227",
  "reports": [
    {
      "id": "2027efc6-a308-4632-a775-b9a9186f087c",
      "name": "mydataset01",
      "webUrl": "https://app.powerbi.com/reports/2027efc6-a308-4632-a775-b9a9186f087c",
      "embedUrl": "https://app.powerbi.com/appTokenReportEmbed?reportId=2027efc6-a308-4632-a775-b9a9186f087c"
    }
  ],
  "datasets": [
    {
      "id": "458e0451-7215-4029-80b3-9627bf3417b0",
      "name": "mydataset01",
      "tables": [
      ],
      "webUrl": "https://app.powerbi.com/datasets/458e0451-7215-4029-80b3-9627bf3417b0"
    }
  ],
  "name": "mydataset01"
}

Data source connectivity (and multi-tenancy of data)

Although almost all the artifacts in pbix file are imported into your workspace, the credential for database is not imported. As a result, if you’re using DirectQuery mode, the embedded report cannot be shown correctly. (But, if you’re using Import mode, you can view the report using the existing imported data.)
In such a case, you must set the credential using the following steps (via REST calls).

First, you must get the gateway datasource as follows. You know that the following dataset id is the previouly returned id.

HTTP Request

GET https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/datasets/458e0451-7215-4029-80b3-9627bf3417b0/Default.GetBoundGatewayDatasources
Authorization: AppKey 9BKsnTVkRP...

HTTP Response

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true
RequestId: 574b0b18-a6fa-46a6-826c-e65840cf6e15

{
  "@odata.context": "http://wabi-us-east2-redirect.analysis.windows.net/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/$metadata#gatewayDatasources",
  "value": [
    {
      "id": "5f7ee2e7-4851-44a1-8b75-3eb01309d0ea",
      "gatewayId": "ca17e77f-1b51-429b-b059-6b3e3e9685d1",
      "datasourceType": "Sql",
      "connectionDetails": "{\"server\":\"testserver.database.windows.net\",\"database\":\"testdb01\"}"
    }
  ]
}

Using returned gateway id and datasource id (see the previous “gatewayId” and “id” in the returned result), you can change the credential of this datasource as follows.

Note : If the error of 404 Not Found occurs, please change (re-create) the location of the Power BI Embedded resource in Azure Portal. (Please see the note above.)

HTTP Request

PATCH https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/gateways/ca17e77f-1b51-429b-b059-6b3e3e9685d1/datasources/5f7ee2e7-4851-44a1-8b75-3eb01309d0ea
Authorization: AppKey 9BKsnTVkRP...
Content-Type: application/json; charset=utf-8

{
  "credentialType": "Basic",
  "basicCredentials": {
    "username": "demouser",
    "password": "P@ssw0rd"
  }
}

HTTP Response

HTTP/1.1 200 OK
Content-Type: application/octet-stream
RequestId: 0e533c13-266a-4a9d-8718-fdad90391099

In production, you can also set the different connection string for each workspace using REST API. (i.e, you can separate the database for each customers.)
The following is changing the connection string of datasource via REST.

POST https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/datasets/458e0451-7215-4029-80b3-9627bf3417b0/Default.SetAllConnections
Authorization: AppKey 9BKsnTVkRP...
Content-Type: application/json; charset=utf-8

{
  "connectionString": "data source=testserver02.database.windows.net;initial catalog=testdb02;persist security info=True;encrypt=True;trustservercertificate=False"
}

Or you can use the row level security in Power BI Embedded and you can separate the data for each users in one report. (see “Works with Row Level Security in Power BI and Power BI Embedded” for details.)
As a result, you can provision each customer report with same pbix (UI, etc) and different datasources.

Notice : If you’re using Import mode instead of DirectQuery mode, there’s no way to refresh models via API.
These constraints are planned for the future implementation. See “What’s new and what’s next for Power BI Embedded (July 2016)“.

Note : Currently the DirectQuery mode is limited to one single database.

Hosting (embedding) reports in your web page

Authentication

In the previous REST api we can use the access key (“AppKey”) itself as authorization header. Because these calls can be handled in server side (backend) and it can be safe.
But, when you embed the report in your web page, this kind of security information would be handled using JavaScript (i.e, frontend). Then the authorization header value must be secured. (If your access key is theft by the malicious user or malicious code, they can call any operations using this key instead of you.)

When you embed the report in your web page, you must use the computed token instead of access key (“AppKey”).
As I mentioned in earlier posts (see “How to use Azure Storage without SDK“), your application must create the OAuth Json Web Token (JWT) which consists of the claims and the computed digital signature. As I illustrated below, this OAuth JWT is dot-delimited encoded string tokens.

First, you must prepare the input value (which is signed later). This vlaue is the base64 url encoded (rfc4648) string of the following json, and these are delimited by the dot (.) character.

Notice : Later I will explain how to get the following report id.

Notice : If you want to use Row Level Security (RLS) with Power BI Embedded, you must also specify “username” and “roles” in the claims.  (see “Works with Row Level Security in Power BI and Power BI Embedded” for details.)

{
  "typ":"JWT",
  "alg":"HS256"
}

{
  "wid":"{workspace id}",
  "rid":"{report id}",
  "wcn":"{workspace collection name}",
  "iss":"PowerBISDK",
  "ver":"0.2.0",
  "aud":"https://analysis.windows.net/powerbi/api",
  "nbf":{start time of token expiration},
  "exp":{end time of token expiration}
}

Second, you must create the base64 encoded string of HMAC (the signature) with SHA256 algorithm. This signed input value is the previous string.

Last, you must combine the input value and signature string using dot (.) character. The completed string is the app token for the report embedding. (Even if the app token is theft by the malicious user, they cannot get the original access key. This app token will be expired soon.)

Now I show you the PHP programming example of these steps. See the following example.

<?php
// 1. power bi access key
$accesskey = "9BKsnTVkRP...";

// 2. construct input value
$token1 = "{" .
  "\"typ\":\"JWT\"," .
  "\"alg\":\"HS256\"" .
  "}";
$token2 = "{" .
  "\"wid\":\"32960a09-6366-4208-a8bb-9e0678cdbb9d\"," . // workspace id
  "\"rid\":\"2027efc6-a308-4632-a775-b9a9186f087c\"," . // report id
  "\"wcn\":\"mywsc01\"," . // workspace collection name
  "\"iss\":\"PowerBISDK\"," .
  "\"ver\":\"0.2.0\"," .
  "\"aud\":\"https://analysis.windows.net/powerbi/api\"," .
  "\"nbf\":" . date("U") . "," .
  "\"exp\":" . date("U" , strtotime("+1 hour")) .
  "}";
$inputval = rfc4648_base64_encode($token1) .
  "." .
  rfc4648_base64_encode($token2);

// 3. get encoded signature
$hash = hash_hmac("sha256",
	$inputval,
	$accesskey,
	true);
$sig = rfc4648_base64_encode($hash);

// 4. show result (which is the apptoken)
$apptoken = $inputval . "." . $sig;
echo($apptoken);

// helper functions
function rfc4648_base64_encode($arg) {
  $res = $arg;
  $res = base64_encode($res);
  $res = str_replace("/", "_", $res);
  $res = str_replace("+", "-", $res);
  $res = rtrim($res, "=");
  return $res;
}	
?>

Finally, embed the report in your web page

For embedding your report, you must get the embed url and report id using the following REST api.

HTTP Request

GET https://api.powerbi.com/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/reports
Authorization: AppKey 9BKsnTVkRP...

HTTP Response

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true
RequestId: d4099022-405b-49d3-b3b7-3c60cf675958

{
  "@odata.context": "http://wabi-us-east2-redirect.analysis.windows.net/v1.0/collections/mywsc01/workspaces/32960a09-6366-4208-a8bb-9e0678cdbb9d/$metadata#reports",
  "value": [
    {
      "id": "2027efc6-a308-4632-a775-b9a9186f087c",
      "name": "mydataset01",
      "webUrl": "https://app.powerbi.com/reports/2027efc6-a308-4632-a775-b9a9186f087c",
      "embedUrl": "https://embedded.powerbi.com/appTokenReportEmbed?reportId=2027efc6-a308-4632-a775-b9a9186f087c",
      "isFromPbix": false
    }
  ]
}

You can embed the report in your web app using the previous app token.
Please look at the next sample code. The former part is the same as the previous example. In the latter part, this sample shows the “embedUrl” (see the previous result) in the iframe, and is posting the app token into the iframe.
(Notice : please change the highlighted values for your own.)

<?php
// 1. power bi access key
$accesskey = "9BKsnTVkRP...";

// 2. construct input value
$token1 = "{" .
  "\"typ\":\"JWT\"," .
  "\"alg\":\"HS256\"" .
  "}";
$token2 = "{" .
  "\"wid\":\"32960a09-6366-4208-a8bb-9e0678cdbb9d\"," . // workspace id
  "\"rid\":\"2027efc6-a308-4632-a775-b9a9186f087c\"," . // report id
  "\"wcn\":\"mywsc01\"," . // workspace collection name
  "\"iss\":\"PowerBISDK\"," .
  "\"ver\":\"0.2.0\"," .
  "\"aud\":\"https://analysis.windows.net/powerbi/api\"," .
  "\"nbf\":" . date("U") . "," .
  "\"exp\":" . date("U" , strtotime("+1 hour")) .
  "}";
$inputval = rfc4648_base64_encode($token1) .
  "." .
  rfc4648_base64_encode($token2);

// 3. get encoded signature value
$hash = hash_hmac("sha256",
    $inputval,
    $accesskey,
    true);
$sig = rfc4648_base64_encode($hash);

// 4. get apptoken
$apptoken = $inputval . "." . $sig;

// helper functions
function rfc4648_base64_encode($arg) {
  $res = $arg;
  $res = base64_encode($res);
  $res = str_replace("/", "_", $res);
  $res = str_replace("+", "-", $res);
  $res = rtrim($res, "=");
  return $res;
}   
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8" />
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <title>Test page</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
  <button id="btnView">View Report !</button>
  <div id="divView">
    <iframe id="ifrTile" width="100%" height="400"></iframe>
  </div>
  <script>
    (function () {
      document.getElementById('btnView').onclick = function() {
        var iframe = document.getElementById('ifrTile'); 
        iframe.src = 'https://embedded.powerbi.com/appTokenReportEmbed?reportId=2027efc6-a308-4632-a775-b9a9186f087c'; 
        iframe.onload = function() {
          var msgJson = {
            action: "loadReport",
            accessToken: "<?=$apptoken?>",
            height: 500,
            width: 722
          };
          var msgTxt = JSON.stringify(msgJson);
          iframe.contentWindow.postMessage(msgTxt, "*");
        };
      };
    }());
  </script>
</body>

The result will be showed as follows.

Interactive Communication with JavaScript API

Now the Power BI Embedded only shows the report in the ifame. But in the future you can use new client side API that will let you send information into the iframe as well as get information out. Please see the Power BI blog “What’s new and what’s next for Power BI Embedded (July 2016)” for the future plans.

(Added Oct/2016) Using JavaScript API, you can communication with the embedded report bidirectional as follows. See the official blog post “Introducing the new Power BI JavaScript API” for more details.

• Enable or disable some report features (page navigation, filtering)
• Navigate to pages
• Filter report
• Display as full screen
• Event handling (loaded, error, pageChanged)

 

Categories: Uncategorized

Tagged as: Azure, PowerBI