Uncategorized

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

In my old post I described the fundamentals of old Power BI Embedded which enables ISV developers to integrate Power BI report in their applications.
This meets the several needs for ISV including : 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.

As you know, now the capacity-based Power BI Premium and new Power BI Embedded (in Microsoft Azure) is released. If you’re not familiar, please see the following team blog’s announcement in the past.

Power BI blog “Microsoft accelerates modern BI adoption with Power BI Premium” (May 3, 2017)

… With Power BI Premium we’re also advancing how Power BI content is embedded in apps created by customers, partners and the broad developer community. As part of the new offering we are converging Power BI Embedded with the Power BI service to deliver one API surface, a consistent set of capabilities and access to the latest features. Moving forward we encourage those interested in embedding Power BI in their apps to start with Power BI Desktop and move to deployment with Power BI Premium. Existing apps built on Power BI Embedded will continue to be supported. …

This new consistent embed experience unlocks Power BI for all ISVs, and all Power BI capabilities such as DirectQuery, streaming, report edit, and more data sources can be supported in ISV application.
In this post I show you this new embed flow for ISV developers and other broad developers.

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

Before starting … (for New Power BI Embedded)

If 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. (You cannot use Microsoft Account for creating Power BI Embedded resource.)

After you’ve created, you go to Power BI service (https://app.powerbi.com/) and log in with your organization account. As you can see below, 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 !
First you define the data source and create reports to be embedded with your Power BI Desktop. (There’s no need to programming for building reports.)
This is used as template of artifacts (data set, reports, etc) for each customers.

In this post, I don’t explain the details about how to use the Power BI Desktop and see the Power BI Desktop tutorial document (official document) for usage.

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

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

Create workspace and assign capacity

Next you create the Power BI workspace (app workspace). Each workspaces will provide the reports, data source, and other artifacts for each customers.
You will need a user that has a pro license in order to create an app workspace within Power BI, and this task is done by UI (Power BI services) or rest api. (Power BI Pro trial can be used for trial use.)

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

Log-in to 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 assign your resource capacity. Even if you’re using Power BI Embedded capacity in Microsoft Azure, you can select this Power BI Embedded capacity in this dialog. (You don’t need to use Azure Portal. It’s completely integrated with Power BI service.) By doing this, the report 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 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 old 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 import your Power BI file (.pbix file) into this generated workspace. This task also can be done by rest api or 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 to 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 retrieves the report id, as well as report embed url (embedUrl) which is used for embedding report.

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 programatically 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

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

In provisioning phase (creating workspace, importing pbix, setting credentials, etc), it’s okay to use the Azure AD user’s (master account’s) access token for calling api, because it’s not exposed to the end users. (The only admin does these management tasks.)

How about viewing the embedded report ? (Of course, AuthN / AuthZ must be needed for viewing report.)
The user in ISV application is not necessarily Power BI users or Azure AD users, then it’s not good to use the Azure AD access token directly. If you were to use the master account’s user token for all end user’s reports, the token will be abused for other reports that the user doesn’t have permission.

In this case, you can get Power BI embed token in the backend (in the server-side) with the following HTTP request, and your application can use this token for embedding (user-side processing) securely. This signed token is only for some specific user’s operation (viewing some report, etc) and if the user needs other operations, another token must be issued in the server side. (The token expires in one hour.)

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 notice, the Azure AD access token must be provided for the HTTP request above, and this Azure AD access token must be issued in your application’s backend (without interactive login UI). That is, this type of access token must be app-only token. (See my old post for the OAuth flow of app-only access token.)
However unfortunately Power BI doesn’t support app-only token currently. Therefore, for getting embed token, you now must use the user access token with non-interactive sign-in instead of using app-only token. (Please wait till app-only access token is supported in Power BI.)
For example, the following is the OAuth password grant flow and this doesn’t need the interactive sign-in. And you can use this user token for getting embed token in server side.

Note : OAuth password grant flow is not recommended in the usual cases, because it’s not secure and the several advanced security features like 2FA or others are not supported.

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 in the server side with “Edit” for “accessLevel“. In the same way, you can set “Create” as “accessLevel” for enabling users 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 15, 2018)

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 for report viewing (not Azure AD access token), and please set your embed url (txtEmbedUrl) and report id (txtEmbedReportId) which are previously retrieved by the rest api.

<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. (See the following postMessage().)
For example, the following sample code displays the same result without Power BI JavaScript API. (Please use JavaScript API for your production. This is the sample code just for your understanding.)

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

<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").

<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, you can also interact with the embedded report, like filtering, reload, changing page, visual settings, etc.
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

Advertisements

Categories: Uncategorized

Tagged as:

12 replies »

  1. Can you please explain why the license should be upgraded to the Premium in production? Do you throttle api when you detect too many requests? Do you slow the performance of our power bi tenant?

    Like

  2. Default.GetBoundGatewayDataSources seems to be returning null when called within the context of a group. If i leave that out I can get results.

    Like

  3. Thanks for your post – after several days of research, this post was the only one with the working sample of power bi embedded in a web page via javascript.

    I’d appreciate if you could tell me how to dynamically pass the embed token into this javascript code, as it continuously expires.

    Like

  4. This is a terrific and very helpful post! Could you clarify that I am approaching the problem correctly? I have tried to adapt your guide listed above.

    Prior to starting development:
    1. Create a dedicated PowerBI Pro user.
    2. With that user, register a PowerBI Native App at dev.powerbi.com/apps
    3. Create a PowerBI Workspace
    4. In that Workspace, create a PowerBI Report to use as a template for all my customers.

    In my web application do the following:
    1. Use the Client ID created above and my dedicated PowerBI user’s credentials to get Access Token.

    With this access token, for each new client, use the PowerBI REST API to do the following:
    1. Create a Workspace
    2. Create PowerBI Datasets for the client (I am not using Direct Query)
    3. Clone the template Report into the client Workspace and associate it with the Datasets created above.
    4. As new data comes in, update the PowerBI Datasets

    When the client requests the report…
    1. Use the GenerateToken method for their report with my desired permissions and then embed token and report in the client’s workspace.

    My concerns with this approach are the following:
    1. The report doesn’t have user level authentication with my application. Somebody could just extract the embedded report URL and token and pass it along.
    2. I am duplicating the Report. If I need to make a change to the report format, I need to propagate that change to all the cloned Reports.

    How does this approach seem to you? Are there more sensible alternatives for my desired result (provide my customers with the same report format and separate non Direct Query Datasets)?

    Like

  5. Hello, all I have read this blog and I truly admit that it is one of the best blogs I have read about Power BI and its related aspects. The use of Power BI is very crucial because of it being a powerful business analytic tool. I myself being a techie like to read such blog posts and after reading this I truly appreciate it. I don’t intend to question the blogger’s knowledge but I want to do some sharing of knowledge with you all and hence here I am about o post something which is one of the important aspects of Power BI:

    https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

    Like

  6. A very informative blog post. Came across this while trying to find how to embed reports created using Power BI.

    I am though new to Power BI and wanted to confirm if Power BI can support the requirement I have.

    I need to extract data from mySQL for the creation of the report. Have therefore installed the desktop version of Power BI.

    I then need to create reports that are to be embedded in our website that users will access using their supplied User ID and password.

    Additionally, the same report needs to be displayed for different users based on the institution from where they are logging on. So if they log on from University X then they need to be displayed data for University X. The report is the same but the data is different. Do I need to create different reports for all the institutions separately or can this be dynamic and I just create one report?

    Also, can Power BI reports support filtering by time? So if I want can I see data for a month/year etc.

    Can you please help e out here.

    Like

    • Hi, Jay-san. My concern is whether MySQL data source is supported or not in current Power BI Embedded.
      Power BI Embedded used to have strict restrictions for supported data sources, because the data source must be updated after upload, when using Power BI Embedded. Please see below, and now SQL Server, Azure SQL Server, Analysis Services, Azure Analysis Services, OData Feed, and SharePoint are only supported for Update Datasources API.

      https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources

      But now you can change various kind of parameters (including connection string) using PBI Embedded as follows.

      https://azure.microsoft.com/en-us/updates/power-bi-embedded-query-parameters-api/

      As a result, please try and see whether you can set connection string for MySQL in PBI Embedded. (I’m sorry, but I don’t have the answer for this question …)

      For other questions, you can involve your own credential mechanisms with PBI Embedded, and also you can use time related functions in DAX on your report. Let’s enjoy !

      Like

    • >> From what you say the main issue is updating the data after embedding the .pbix file.

      Yes.
      Sorry, but I don’t have any examples for using these data sources (except for SQL database, etc).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s