Walkthrough of Power BI Embedded (Embed Power BI in ISV application)

Uncategorized

By Tsuyoshi Matsuzaki on 2017-07-14 • ( 17 Comments )

Power BI Embedded enables ISV developers to integrate Power BI report in their own applications.
This satisfies the several needs for ISV, such as : integrating the existing authentication method in ISV application, not needing Power BI license for application users, data separation for each tenant (same report, but different data), etc.

I have described about the fundamentals of old Power BI Embedded in my old post, however, now the capacity-based plan, such as, Power BI Premium and new Power BI Embedded (in Microsoft Azure), is released. Power BI Embedded is reborn.
This new consistent embed experience unlocks Power BI for all ISVs. Eventually all Power BI capabilities, such as, DirectQuery, streaming, report edit, and more data sources can be supported in ISV application, unlike old Power BI Embedded.

In this post I’ll show you this new embed experience and flow for ISV developers and other broad developers.

Note : You can maintain your existing old Power BI Workspace services (old Power BI Embedded) in Microsoft Azure, but you must remember that you cannot create the new one. (Use the new Power BI embed if you create the new one.)

Before starting … (for New Power BI Embedded)

When you use new Power BI Embedded in Microsoft Azure, first you must add new “Power BI Embedded” resource in your Microsoft Azure subscription with organization account. (Note that you cannot use Microsoft Account for creating Power BI Embedded resource.)

After you’ve created, you go to ordinary Power BI service UI (https://app.powerbi.com/) and log in with your organization account.
Now you can assign your Power BI Embedded capacity for your workspace in Power BI services !

Create pbix file with Power BI Desktop

Now let’s start to create your report.
First you define the data source and create reports to be embedded using Power BI Desktop. (There’s no need to programming for building reports.)
This artifact (created in PBI desktop) is used as template (data set, reports, etc) for each customers.

In this post, I don’t explain the details about how to create report in Power BI Desktop, but you can refer the Power BI Desktop tutorial document (official document) for details.

Note : You can also download the sample of “Retail Analysis PBIX“.

When you have finished, please save in your local disk as Power BI file (.pbix file).

Create workspace and assign capacity

Next you create the Power BI workspace (app workspace) in Power BI service (in which, you can assign Power BI Embedded capacity, as I mentioned above). Each workspaces will provide the reports and data source for each customers.
It is done by UI (Power BI services) or rest api, but you need a user that has a pro license in order to create an app workspace within Power BI.

Note : The app workspace is equal to “group”. When you create app workspace with rest api, please add a group.

In Power BI services (Web UI), select “Workspaces” menu, and push “Create app workspace”. (Then the following form is displayed. Please input the name of workspace and press “Save”.)

When you create your app workspace in your real production application, please turn on “premium” (see above) and then you can assign your Power BI Embedded capacity, which is charged in Microsoft Azure. (You don’t need to use Azure Portal. It’s completely integrated with Power BI service.) By doing this, the report in this workspace is not consumed by the personal license, but the resource-based license is used with flexible Power BI Premium or Power BI Embedded capacity. (When it’s development purpose or trial, this setting is not needed. This is only for production.)
Or you can also assign your capacity (Premium capacity or Embedded capacity) using Power BI admin portal. See “Manage capacities within Power BI Premium and Power BI Embedded” for details about admin portal settings.

After you’ve created the customer’s app workspace, please get the workspace id (so called “group id”). You can copy this id from the url (address) of the workspace in Power BI services (Web UI), or you can get with rest api as follows.

Note : The following authorization token (access token) in the HTTP header is the master account’s user token i.e, Azure AD token. See my early post for getting the Power BI user token. (Later I explain the idea about the token.)

GET https://api.powerbi.com/v1.0/myorg/groups
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal

{
  "@odata.context": "http://df-app-scus-redirect.analysis.windows.net/v1.0/myorg/$metadata#groups",
  "value": [
    {
      "id": "a4781858-f3ef-47c2-80a9-fa14845c833b",
      "isReadOnly": false,
      "name": "myws01"
    },
    ...
    
  ]
}

Import pbix

Next you should import your Power BI file (.pbix file) into this generated workspace. This task also can be done by rest api or UI.
When you want to use UI, just push “publish” in Power BI Desktop as the following picture.

After you’ve imported Power BI file, please get the imported dataset id, report id, and report embed url.
The following HTTP request retrieves the imported dataset id. (Please change a4781858-f3ef-47c2-80a9-fa14845c833b into your group id.)

GET https://api.powerbi.com/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/datasets
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal

{
  "@odata.context": "http://df-app-scus-redirect.analysis.windows.net/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/$metadata#datasets",
  "value": [
    {
      "id": "44a12ee1-8da7-4383-a2cf-89129ef6e1a7",
      "name": "Retail Analysis Sample",
      "addRowsAPIEnabled": false,
      "configuredBy": "demotaro@test.onmicrosoft.com"
    }
  ]
}

The following request retrieves the report id, as well as report embed url (embedUrl) which is used for embedding report later.

GET https://api.powerbi.com/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/reports
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal

{
  "@odata.context": "http://df-app-scus-redirect.analysis.windows.net/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/$metadata#reports",
  "value": [
    {
      "id": "b21f4f90-e364-4b4c-9281-c5db87cdf3a5",
      "modelId": 0,
      "name": "Retail Analysis Sample",
      "webUrl": "https://app.powerbi.com/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/reports/b21f4f90-e364-4b4c-9281-c5db87cdf3a5",
      "embedUrl": "https://app.powerbi.com/reportEmbed?reportId=b21f4f90-e364-4b4c-9281-c5db87cdf3a5&groupId=a4781858-f3ef-47c2-80a9-fa14845c833b",
      "isOwnedByMe": true,
      "isOriginalPbixReport": false,
      "datasetId": "44a12ee1-8da7-4383-a2cf-89129ef6e1a7"
    }
  ]
}

Data source connectivity and multi-tenancy of data

Although almost all the artifacts in pbix file are imported into your workspace, the credential for the data source is not imported because of security reasons. As a result, if you’re using DirectQuery mode, the embedded report cannot be shown correctly. (On the other hand, if you’re using Import mode, you can view the report because the data is imported in your dataset.)

For ISV applications (SaaS applications, etc), the separation of data is also concerns. The data of the company “A” will be different from the one of company “B”.

In such a case, you can set and change the connection string or credentials using rest api.

First you can get the data source id and gateway id by the following HTTP request.  (In this example, we assume that the type of data source is SQL Server.)
Note that the following “id” in HTTP response is the data source id.

GET https://api.powerbi.com/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/datasets/44a12ee1-8da7-4383-a2cf-89129ef6e1a7/Default.GetBoundGatewayDataSources
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal

{
  "@odata.context": "http://df-app-scus-redirect.analysis.windows.net/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/$metadata#gatewayDatasources",
  "value": [
    {
      "id": "2a0bca27-a496-450c-80e0-05790ad8875f",
      "gatewayId": "d52ba684-afa8-484d-b5d5-790842b6ab9f",
      "datasourceType": "Sql",
      "connectionDetails": "{"server":"server01.database.windows.net","database":"db01"}"
    }
  ]
}

Using gateway id and data source id, you can set (or change) the credential of this data source as follows.

PATCH https://api.powerbi.com/v1.0/myorg/gateways/d52ba684-afa8-484d-b5d5-790842b6ab9f/datasources/2a0bca27-a496-450c-80e0-05790ad8875f
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...
Content-Type: application/json; charset=utf-8

{
  "credentialType": "Basic",
  "basicCredentials": {
    "username": "demouser",
    "password": "pass@word1"
  }
}

HTTP/1.1 200 OK

The following changes the connection string for the data source via rest api. (The data source id is also changed when you change the connection string.)
That is, you can import Power BI file and set the different connection string for each customer’s tenant.

POST https://api.powerbi.com/v1.0/myorg/groups/a4781858-f3ef-47c2-80a9-fa14845c833b/datasets/44a12ee1-8da7-4383-a2cf-89129ef6e1a7/Default.SetAllConnections
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...
Content-Type: application/json; charset=utf-8

{
  "connectionString": "data source=tsmatsuz-server2.database.windows.net;initial catalog=db02;persist security info=True;encrypt=True;trustservercertificate=False"
}

HTTP/1.1 200 OK

Note : The data source for OData and SharePoint Online is also supported in Power BI Embedded. See here. (Added April 2018)

Note : You can update your report programmatically and automate the provisioning and modification for many customers with new Power BI Embedded Update Report API. See here for details. (Added April 2018)

The idea of AuthN / AuthZ for new embed model

Note (Feb 2019 Update) : You can now use a service principal and Azure AD application permissions in Power BI Embedded. (See here for the announcement.)
In this post, I’m using a user token (master user) for Power BI Embedded instead.

Before embedding your report in your application, you must learn about the idea of AuthN (authentication) / AuthZ (authorization) in the new embed model.

In the previous provisioning phases (creating workspace, importing pbix, setting credentials, etc), you can use Azure AD user’s (master user’s) access token for calling APIs, since it can be done by admin (or developers) and not exposed for the end-users.
However, the user to view your embedded report should run all tasks without Azure AD credentials. Hence you cannot use Azure AD access token directly in this phase.

For this reason, you should get Power BI embed token in the backend (in the server-side) instead, and your application can use this token for embedding securely.
Such like a regular Azure AD token, this token can also be used for only some specific user’s operations (viewing, editing, etc). If the user needs other operations, another token should be issued in server side and provided for this user. (The token expires in one hour.)
To issue this token, you should invoke the following HTTP request.

POST https://api.powerbi.com/v1.0/myorg/groups/{group id}/reports/{report id}/GenerateToken
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...
Content-Type: application/json; charset=utf-8

{
  "accessLevel": "View",
  "allowSaveAs": "false"
}

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal

{
  "@odata.context": "http://df-app-scus-redirect.analysis.windows.net/v1.0/myorg/groups/{group id}/$metadata#Microsoft.PowerBI.ServiceContracts.Api.V1.GenerateTokenResponse",
  "token": "H4sIAAAAAA...",
  "tokenId": "63c8d0ea-800d-462c-9906-22a4567f276f",
  "expiration": "2017-07-15T08:29:29Z"
}

As you can see above, the Azure AD access token must be provided for this HTTP request. (See the above “Authorization” header.) How to get this access token ?
When you want to get Azure AD access token for application in the backend (without interactive login UI), service principal and application permission can often be used for issuing token. (See my post for application permission in Azure AD.)
However unfortunately Power BI doesn’t support application permission currently. Therefore, for getting embed token, you should then use the user access token with non-interactive sign-in instead of using service principal and application permission. (Please wait till application permission is supported in Power BI.) (Feb 2019 Update : Now you can use service principal and application permission in Power BI Embedded. You don’t need to to use a master user anymore.)
For example, the following HTTP request is extracting access token with a master user by OAuth Resource Owner Password Credentials (ROPC) grant flow (without interactive login UI). You can use this Azure AD token for getting embed token.

Note : OAuth 2.0 Resource Owner Password Credentials (ROPC) grant is not recommended in the usual cases, since it’s not secure and the several advanced features (such like, MFA, etc) are not supported in this flow.

POST https://login.microsoftonline.com/common/oauth2/token
Content-Type: application/x-www-form-urlencoded

grant_type=password
&client_id=dede99a5-ed89-4881-90a4-4564dae562f7
&client_secret=P4GmxWa...
&username=tsmatsuz%40test.onmicrosoft.com
&password=pass%40word1
&resource=https%3A%2F%2Fanalysis.windows.net%2Fpowerbi%2Fapi

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "token_type": "Bearer",
  "scope": "Content.Create Dashboard.Read.All Data.Alter_Any Dataset.Read.All Dataset.ReadWrite.All Group.Read Group.Read.All Metadata.View_Any Report.Read.All Report.ReadWrite.All",
  "expires_in": "3599",
  "ext_expires_in": "0",
  "expires_on": "1500032600",
  "not_before": "1500028700",
  "resource": "https://analysis.windows.net/powerbi/api",
  "access_token": "eyJ0eXAiOi...",
  "refresh_token": "AQABAAAAAA..."
}

If the user needs to edit the report, please send the following HTTP request (“Edit” in “accessLevel“) for getting an embed token. In the same way, you can set “Create” in “accessLevel” for getting token to create new reports in your embedded Power BI.

POST https://api.powerbi.com/v1.0/myorg/groups/{group id}/reports/{report id}/GenerateToken
Accept: application/json
Authorization: Bearer eyJ0eXAiOi...
Content-Type: application/json; charset=utf-8

{
  "accessLevel": "Edit",
  "allowSaveAs": "false"
}

Note : The RLS (Row-Level Security) is not supported now. (It is on the roadmap.) The RLS (Row-Level Security) for tiles and dashboards is now supported in Power BI Embedded ! (March 2018 Update)

Hosting (embedding) reports in your web page

Now you can embed your Power BI reports in your application with Power BI JavaScript API.

Let’s see the following javascript example.
The access token (txtAccessToken) is the embed token (not Azure AD access token, see above) for report viewing, and please set your embed url (txtEmbedUrl) and report id (txtEmbedReportId) which are previously retrieved by rest api.

Hosting with powerbi.js (View Mode)

<html>
<head>
  <title>Test</title>
  <script src="/Scripts/powerbi.js"></script>
</head>
<body>
  <div id="captionArea">
    <h1>Power BI Embed test</h1>
  </div>
  <div id="embedContainer" style="height:500px">
  </div>
  <script>
    (function () {
      // Please change these values
      var txtAccessToken = 'H4sIAAAAAA...';
      var txtEmbedUrl =
        'https://app.powerbi.com/reportEmbed?reportId=b21f4f90-e364-4b4c-9281-c5db87cdf3a5&groupId=a4781858-f3ef-47c2-80a9-fa14845c833b';
      var txtEmbedReportId = 'b21f4f90-e364-4b4c-9281-c5db87cdf3a5';

      var models = window['powerbi-client'].models;
      var permissions = models.Permissions.All;
      var config = {
        type: 'report',
        tokenType: models.TokenType.Embed,
        accessToken: txtAccessToken,
        embedUrl: txtEmbedUrl,
        id: txtEmbedReportId,
        permissions: permissions,
        settings: {
          filterPaneEnabled: true,
          navContentPaneEnabled: true
        }
      };

      var embedContainer = document.getElementById('embedContainer');
      var report = powerbi.embed(embedContainer, config);
    }());
  </script>
</body>
</html>

This HTML will display the following embedded report (View Mode).

In the backend of javascript api, iframe is inserted in your web page, and some attributes (including embed token) are passed by the inter-frame communications.
For example, the following sample displays the same result without Power BI JavaScript API. See the following postMessage(). (Please use JavaScript API for your production. This is the sample code just for your understanding.)

Note : The uid (uniqueId) is the random string.

Hosting Manually (View Mode)

<html>
<head>
  <meta name="viewport" content="width=device-width" />
  <title>Test without Power BI JavaScript API</title>
</head>
<body>
  <div id="captionArea">
    <h1>Power BI Embed test</h1>
  </div>
  <div id="embedContainer">
    <iframe id="ifrTile" width="100%" height="500px"></iframe>
  </div>
  <script>
    (function () {
      // Please change these values
      var txtAccessToken = 'H4sIAAAAAA...';
      var txtEmbedUrl =
        'https://app.powerbi.com/reportEmbed?reportId=b21f4f90-e364-4b4c-9281-c5db87cdf3a5&groupId=a4781858-f3ef-47c2-80a9-fa14845c833b';
      var txtEmbedReportId = 'b21f4f90-e364-4b4c-9281-c5db87cdf3a5';

      var iframe = document.getElementById('ifrTile');
      iframe.src = txtEmbedUrl;
      iframe.onload = function () {
        var msgJson = {
          "method": "POST",
          "url": "/report/load",
          "headers": {
            "x-sdk-type": "js",
            "x-sdk-version": "2.3.2",
            "uid": "87oes"
          },
          "body": {
            "settings": {
              "filterPaneEnabled": true,
              "navContentPaneEnabled": true
            },
            "type": "report",
            "tokenType": 1,
            "accessToken": txtAccessToken,
            "embedUrl": txtEmbedUrl,
            "id": txtEmbedReportId,
            "permissions": 7,
            "uniqueId": "87oes"
          }
        };
        iframe.contentWindow.postMessage(msgJson, "*");
      };
    }());
  </script>
</body>
</html>

With the new embed experience, you can easily enable application users to edit embedded reports as follows. (You can also enable users to create new reports in your embed experience.)
You must remember that this embed token must be for editing (i.e, "accessLevel": "Edit").

Hosting with powerbi.js (Edit Mode)

<html>
<head>
  <title>Test</title>
  <script src="/Scripts/powerbi.js"></script>
</head>
<body>
  <div id="captionArea">
    <h1>Power BI Embed test</h1>
  </div>
  <div id="embedContainer" style="height:500px">
  </div>
  <script>
    (function () {
      // Please change these values
      var txtAccessToken = 'H4sIAAAAAA...';
      var txtEmbedUrl =
        'https://app.powerbi.com/reportEmbed?reportId=b21f4f90-e364-4b4c-9281-c5db87cdf3a5&groupId=a4781858-f3ef-47c2-80a9-fa14845c833b';
      var txtEmbedReportId = 'b21f4f90-e364-4b4c-9281-c5db87cdf3a5';

      var models = window['powerbi-client'].models;
      var permissions = models.Permissions.All;
      var config = {
        type: 'report',
        tokenType: models.TokenType.Embed,
        accessToken: txtAccessToken,
        embedUrl: txtEmbedUrl,
        id: txtEmbedReportId,
        permissions: permissions,
        viewMode: models.ViewMode.Edit,
        settings: {
          filterPaneEnabled: true,
          navContentPaneEnabled: true
        }
      };

      var embedContainer = document.getElementById('embedContainer');
      var report = powerbi.embed(embedContainer, config);
    }());
  </script>
</body>
</html>

Using JavaScript API, your javascript code can also interact with the embedded report (like filtering, reload, changing page, visual settings, etc) and it can be seamlessly integrated with your application.
You can see the following github example for these operations with JavaScript sample code.

[Github] Microsoft Power BI – Report Embed Sample
https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html

Note (Nov 2020 Update) : Now Power BI Embedded is improved and you can apply new UI, such as – page navigations, new pallet (“visualizations” and “fields” panes) in edit mode, action bar in reports, and personal bookmarks -, by setting config. (See above JavaScript code for config.)
See Power BI team’s post for details.

Categories: Uncategorized

Tagged as: PowerBI