Power BI Custom Authentication in ISV applications (Custom Data Connector)

Sorry for my long interval for posting because of my private matters. This blog is alive. (My wife is having trouble with her leg and I’m now changing my work time…)

In this post I describe how ISVs can implement their own Power BI Data Connector with custom authentication experience.
When users select “Get Data” in Power BI, your custom connector will be appeared. Your connector can provide custom authentication UI and custom data.
Even when some ISVs create (and submit) custom template content pack, this connector’s flow is needed when using custom OAuth authentication.

In this post I explain straightforward about this flow with developer’s view.

Before staring…

Before starting, please enable the custom connector feature in your Power BI Desktop for development as follows.

  • Launch Power BI Desktop
  • Select “File” – “Options and settings” – “Options” menu
  • In the dialog window, select “Preview features” and enable “Custom data connectors”

Note : Currently, Data Connectors are only supported in Power BI Desktop. (You cannot also publish to Power BI service.)

Next you must install Power Query SDK in your Visual Studio. After installed, you can see the data connector project template in your Visual Studio.

Overall Flow

For developing custom authentication, you must understand how OAuth works with API applications.
You can use your own favorite OAuth provider (Google account, your own custom IdP, etc) for building OAuth supported Data Connector, but here we use Azure Active Directory (Azure AD) for implementation. (Here we use Azure AD v1 endpoint. See the note below.)

Step1 ) Beforehand you must register your api application (A) and your client application(=Power BI Data Connector) (B) in Azure AD. This operation is needed once for only ISV. (The user doesn’t need to register.)

Step2 ) When the user uses your Power BI Data Connector, your data connector (B) shows the following url in web browser or browser component for login.

https://login.microsoftonline.com/common/oauth2/authorize
  ?response_type=code
  &client_id={app id of B}
  &resource={app uri of A}
  &redirect_uri={redirected url for B}

Step3 ) With Power BI Data Connector, the previous “redirected url for B” is the fixed url, “https://preview.powerbi.com/views/oauthredirect.html”.
After the user has logged-in, code is returned by the query string as follows. Then your data connector (B) can retrieve the returned code value.

https://preview.powerbi.com/views/oauthredirect.html?code={returned code}

Step4 ) Next your data connector (B) posts the following HTTP request against Azure AD (without UI). Then the access token is returned as http response as follows.

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

grant_type=authorization_code
&code={returned code}
&client_id={app id of B}
&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "token_type": "Bearer",
  "scope": "user_impersonation",
  "expires_in": "3599",
  "ext_expires_in": "0",
  "not_before": "{start time of this token}",
  "expires_on": "{expired time of this token}",
  "resource": "{app uri of A}",
  "access_token": "{returned access token}",
  "refresh_token": "{returned refresh token}",
  "id_token": "{returned id token}"
}

Step5 ) Your data connector (B) calls your api application (A) with the previous access token as follows.

GET https://contoso.com/testapi
Authorization: Bearer {access token}

Step6 ) Your api application (A) can verify the passed token and return the result dataset if it’s valid. (See “Develop your API with Azure AD” for verification.)
The result dataset is shown in your Power BI client.

Note : Currently the native application in Azure AD v2 endpoint cannot have the redirect urls with “http” or “https” protocols. You cannot use “https://preview.powerbi.com/views/oauthredirect.html” as redirected urls.
Therefore we’re using Azure AD v1 endpoint in this post.

Here I explain how to setup or implement with corresponding above steps.

Register your app in Azure AD (Step 1)

First you must register your api application (A) and your client application(=Power BI Data Connector) (B) with Azure Portal.

When you register your api application (A), you must define the scope (permission or role) in manifest. (See below.)
You can use the appropriate scope values like “read” and “write” along with your API requirements. In this post we use the default settings as follows (scope value is “user_impersonation”), and your data connector (B) must use the scope “{app uri}/user_impersonation” (ex: https://test.onmicrosoft.com/235426d2-485d-46e0-ad0d-059501ab58a4/user_impersonation) for accessing your api application.

When you register your data connector (B), the app type must be “native application”, and you must include “https://preview.powerbi.com/views/oauthredirect.html” (common for all data connectors) as the redirect urls.

After you’ve done, you must open the required permission setting of data connector application (B) and add the scope (here “user_impersonation” scope) for accessing api application (A).

Note : If it’s production application, you must set these applications (A and B) as multi-tenanted applications and the user must consent your applications in the user’s tenant before using your data connector. In this post we skip these settings.

Implement your API – Create OData Feed service compliant with Power BI (Step 6)

Before implementing your data connector, now we shall implement the api application (B) beforehand.

You can create your api as simple rest api (consumed as Web.Contents() in your data connector) or odata-compliant rest api (consumed as OData.Feed() in your data connector) with your favorite programming languages.
Here we implement as OData v4 feed service with ASP.NET (.NET Framework) as follows. (See “OData.org : How to Use Web API OData to Build an OData V4 Service without Entity Framework” for details.)

First, create your “ASP.NET Web Application” project and please select [Empty] and [Web API] in the creation wizard as follows.

Add Microsoft.AspNet.OData package with NuGet. (Not Microsoft.AspNet.WebApi.OData, because it’s not v4.)

Note : You cannot use .NET Framework 4.6.2 with latest Microsoft.AspNet.OData 6.1.0 because of the dll confliction.

Add and implement your MVC controller. Your controller must inherit ODataController as follows.
Here I created the following simple controller which returns the data (3 records) of SalesRevenue class.

...
using System.Web.OData;
...

public class TestController : ODataController
{
  [EnableQuery]
  public IQueryable<SalesRevenue> Get()
  {
    return new List<SalesRevenue>()
    {
      new SalesRevenue { Id = 1, Country = "US", Value = 200 },
      new SalesRevenue { Id = 2, Country = "Japan", Value = 80 },
      new SalesRevenue { Id = 3, Country = "Germany", Value = 120 }
    }.AsQueryable();
  }
}

public class SalesRevenue
{
  public int Id { get; set; }
  public string Country { get; set; }
  public int Value { get; set; }
}
...

Edit your WebApiConfig.cs as follows.

...
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using Microsoft.OData.Edm;
...

public static class WebApiConfig
{
  public static void Register(HttpConfiguration config)
  {
    // Web API configuration and services

    // Web API routes
    config.MapHttpAttributeRoutes();

    config.Routes.MapHttpRoute(
      name: "DefaultApi",
      routeTemplate: "api/{controller}/{id}",
      defaults: new { id = RouteParameter.Optional }
    );

    // Add here
    ODataModelBuilder builder = new ODataConventionModelBuilder();
    builder.Namespace = "Demos";
    builder.ContainerName = "DefaultContainer";
    builder.EntitySet<Controllers.SalesRevenue>("Test");
    IEdmModel model = builder.GetEdmModel();
    config.MapODataServiceRoute("ODataRoute", "osalesdat", model);
  }
}
...

Now your api is consumed as https://{your api hosted url}/osalesdat with Power BI Desktop. (Please try with OData feed connector !)

Finally you must add the authentication in your api application.
As I mentioned earlier, your api will receive the following HTTP request from Power BI Data Connector. Your api application must verify the access token (the following Authorization header value), retreive several claims, and return data as you like.
For example, your api application can retrieve tenant-id from token, and can return each tenant’s data with your programming code. (i.e, you can easily implement multi-tenancy.)

GET https://contoso.com/api/test
Accept: */*
Accept-Encoding: gzip, deflate
Authorization: Bearer eyJ0eXAiOi...

For this implementation, please see my earlier post “Build your own Web API protected by Azure AD v2.0 endpoint with custom scopes“, but with ASP.NET you just select “Configure Azure AD Authentication” with the right-click on your project, or you can configure in the project creation wizard.

Note : If you select “new app” instead of “existing app” in the configuration dialog, your api application is automatically registered in Azure AD. In this case, you don’t need to register your api app manually in step 1.

When you want to protect api, please add Authorize attribute as follows.

public class TestController : ODataController
{
  [Authorize]
  [EnableQuery]
  public IQueryable<SalesRevenue> Get()
  {
    return new List<SalesRevenue>()
    {
      new SalesRevenue { Id = 1, Country = "US", Value = 200 },
      new SalesRevenue { Id = 2, Country = "Japan", Value = 80 },
      new SalesRevenue { Id = 3, Country = "Germany", Value = 120 }
    }.AsQueryable();
  }
}

Implement your Data Connector (All)

Now let’s create your data connector (B) in turn.
First you start to create your project with “Data Connector Project” template in Visual Studio.

Power BI Data Connector should be written by Power Query M formula language. (See M function reference for details.) All logic is written in “PQExtension1.pq” file. (We’re assuming our project named “PQExtension1”.)
Here I show you overall sample code (PQExtension1.pq) as follows. (Here I’m skipping the code for exception handling, etc.)
Now let’s see what this code is doing step by step.

section PQExtension1;

[DataSource.Kind="PQExtension1", Publish="PQExtension1.Publish"]
shared PQExtension1.Contents = (optional message as text) =>
  let
    source = OData.Feed(
      "https://contoso.com/osalesdat",
      null,
      [ ODataVersion = 4, MoreColumns = true ])
  in
    source;
 
PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];

StartLogin = (resourceUrl, state, display) =>
  let
    authorizeUrl = "https://login.microsoftonline.com/common/oauth2/authorize"
      & "?response_type=code"
      & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
      & "&resource=https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4"
      & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"
  in
    [
      LoginUri = authorizeUrl,
      CallbackUri = "https://preview.powerbi.com/views/oauthredirect.html",
      WindowHeight = 720,
      WindowWidth = 1024,
      Context = null
    ];

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    tokenResponse = Web.Contents("https://login.microsoftonline.com/common/oauth2/token", [
      Content = Text.ToBinary("grant_type=authorization_code"
        & "&code=" & query
        & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
        & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"),
      Headers = [
        #"Content-type" = "application/x-www-form-urlencoded",
        #"Accept" = "application/json"
      ]
    ]),
    result = Json.Document(tokenResponse)
  in
    result;

...

Note : Use Fiddler for debugging HTTP flow.

Implement your Data Connector - Signing-in (Step 2)

When you collaborate with OAuth authentication flow with your Power BI Data Connector, first you define your connector as follows. The following "Label" text will be displayed in the wizard window in Power BI. (See the screenshot in following "Run !" section.)

PQExtension1 = [
  Authentication = [
    OAuth = [
      ...
    ]
  ],
  Label = "Test API Connector"
];

When you want to navigate to the login UI with OAuth, you must add the following StartLogin.
Here 97f213a1-6c29-4235-a37b-a82dda14365c is application id (client id) for your data connector (B), and https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4 is the url-encoded string for app uri of your api app (B). Please change for your appropriate values.

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      ...
    ]
  ],
  Label = "Test API Connector"
];

StartLogin = (resourceUrl, state, display) =>
  let
    authorizeUrl = "https://login.microsoftonline.com/common/oauth2/authorize"
      & "?response_type=code"
      & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
      & "&resource=https%3A%2F%2Ftest.onmicrosoft.com%2F235426d2-485d-46e0-ad0d-059501ab58a4"
      & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"
  in
    [
      LoginUri = authorizeUrl,
      CallbackUri = "https://preview.powerbi.com/views/oauthredirect.html",
      WindowHeight = 720,
      WindowWidth = 1024,
      Context = null
    ];

Implement your Data Connector - Get auth code (Step 3)

After the user has succeeded login with UI, code is returned to callback uri (https://preview.powerbi.com/views/oauthredirect.html) as part of query strings. When your data connector receives this code value, you must write as follows.
The following FinishLogin is invoked by connector framework after the login is succeeded.

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];
...

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    code = query
    ...
  in
    result;

Implement your Data Connector - Get access token (Step 4)

With code value, your data connector can retrieve access token as follows.
Here M function Web.Contents() posts http-request without UI (silently).

PQExtension1 = [
  Authentication = [
    OAuth = [
      StartLogin = StartLogin,
      FinishLogin = FinishLogin
    ]
  ],
  Label = "Test API Connector"
];
...

FinishLogin = (context, callbackUri, state) =>
  let
    query = Uri.Parts(callbackUri)[Query],
    tokenResponse = Web.Contents("https://login.microsoftonline.com/common/oauth2/token", [
      Content = Text.ToBinary("grant_type=authorization_code"
        & "&code=" & query
        & "&client_id=97f213a1-6c29-4235-a37b-a82dda14365c"
        & "&redirect_uri=https%3A%2F%2Fpreview.powerbi.com%2Fviews%2Foauthredirect.html"),
      Headers = [
        #"Content-type" = "application/x-www-form-urlencoded",
        #"Accept" = "application/json"
      ]
    ]),
    result = Json.Document(tokenResponse)
  in
    result;

As you can see, FinishLogin returns OAuth JWT (json web token) to Power BI. (The access token and refresh token are included in this JWT.)

Implement your Data Connector - Call your api application (Step 5)

Finally you call your api application (previously generated OData feed service) as follows.
As you can see, here you don't need to specify Authorization header in your M code. The framework will automatically set access token in the request.

[DataSource.Kind="PQExtension1", Publish="PQExtension1.Publish"]
shared PQExtension1.Contents = (optional message as text) =>
  let
    source = OData.Feed(
      "https://contoso.com/osalesdat",
      null,
      [ ODataVersion = 4, MoreColumns = true ])
  in
    source;

Run !

Now let's start to build, deploy, and run !

Build your project in Visual Studio and copy the generated .mez file (which is the zip file format) into "%UserProfile%DocumentsMicrosoft Power BI DesktopCustom Connectors" folder.
Launch Power BI Desktop and select "Get Data". As you can see below, here's your data connector in the list.

When you select your custom data connector, the following dialog with "Sign In" button is displayed.

When you push "Sign In", the login-UI (in this case, Azure AD sign-in) is displayed.
Here we used Azure AD for our trivial example, but you can use your own provider (sign-in experience), as I mentioned earlier.

When you successfully logged-in and push "Connect" button, the following navigator is displayed along with your OData feed metadata.
In this case, we defined only "Test" entity in the previous code, but you can also include functions (not only entities), if needed.

When you select entities and proceed, you can use dataset in Power BI as follows.
As you see here, the end-user doesn't concern about your implementation (OAuth, OData, etc). The user just uses the given connector and can securely get all required data with custom authentication.

Note : Once you connect the data source, the connection is cached in Power BI Desktop. Please clear connection cache using "File" - "Options and settings" - "Data source settings" menu.

The following is the sample code which is handling exceptions and others (which is accessing Microsoft Graph), and please refer for your understanding.

[Github] MyGraph Connector Sample
https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph

 

Reference : [Github] Getting Started with Data Connectors
https://github.com/Microsoft/DataConnectors

 

Advertisements

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

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

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

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

Understanding R for non-scientists with Power BI

Now, Python and R are the commonly used languages for the statistical computing, but these are not easy to learn and use for the business users (marketers, analysts, etc).

Several months ago we introduced R visuals for Power BI. Using Power BI, you can now easily combine your real data (business data) with a few lines of code, and share that visuals for other users without any additional setup. Moreover, the ISV folks can also use these visuals in your own custom apps using Power BI Embedded.

In this post, I show you an example powered by R for beginner’s understanding.
As I describe here, we reuse the existing data, scripts, and visuals. Even if you are not a programmer or scientist, you can modify the complete script (complete sample) and see how the results are shown very quickly.

Note : Developers can also contribute R enabled custom visuals for the end users. (The R packages can be installed as part of the visual download process, and the users can use the visuals without any knowledge of R.) Here I cannot describe about this.

Install R runtime and Power BI

When the user views the R enabled Power BI report on the web, the user needs only the web browser (Power BI service account) and no additional components are needed on the local computer.
But, when you author the R enabled Power BI report, you must use Power BI Desktop and R runtime (or Microsoft R Open, which gives multithreading and high performance based on the open R). In this post, we edit the existing R visuals and see how it works, then you must install these components in your local computer.

Then Power BI Desktop automatically detects the installed R runtime, when it opens. (The only thing you need to do is just installation of Power BI Desktop and R runtime !)

Install R packages

For start-up users, here are the Microsoft and community contributed several examples, and you can open and see how it works using Power BI.
Now, let’s use the most simple example “Correlation Plot” in this showcase.

Power BI – R Script Showcase
http://community.powerbi.com/t5/R-Script-Showcase/bd-p/RVisuals

Please download the Power BI file (.pbix) of the “Correlation Plot” sample, and open this file using your Power BI Desktop.

First you notice that the error in Power BI report is shown, because the required R packages are not installed.
In this example, only the package called “corrplot” is used, and then you must install this package using R console. (See the following command.)

Note : You can also download the package installation script from the showcase site and can install required packages using this script.

Note : If you don’t have permissions for the R installation location, the personal library is used for the package installation location. In Windows, {your user profile location}DocumentsRwin-library{version} is used by default.

install.packages("corrplot", repos="http://cran.us.r-project.org")

Note that the following document is describing the supported R packages in Power BI Service (Power BI on the web). All the previous showcase examples only use these supported packages.

The supported R packages in Power BI service
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-r-visuals/

The following script removes the installed package. Please fill the appropriate values in {your profile location} and {version}.

remove.packages("corrplot", lib="{your profile location}\Documents\R\win-library\{version}")

After installing the packages, you can now view the R enabled report in your Power BI Desktop as follows.

Source data and data frame

Before you see and modify R scripts, please check your input data and dataset. The data is imported from the data source and resides in this pbix file. Then you can easily see the source data using this pbix file.

First you must understand what columns are used for this R visual. In this example, “Discounts”, “Profit”, and “Units Sold” are used.

When you want to see the data in these columns, please insert the table into this report and drag these columns into the “Values” area in this table. (See the following screenshot.)
You can see a lot of source data in this table, or you can export data to CSV.

This data (2 dimension vector) is also referenced as “dataset” in the R script on Power BI.
For example, if you write the following R script in the editor, you can see this data frame (named “dataset”) as the table format. (Sorry, but not all the data is displayed or scrolled.)

Note : Please install the “gridExtra” package and the dependent package called “gtable” beforehand.
install.packages("gtable", repos="http://cran.us.r-project.org")
install.packages("gridExtra", repos="http://cran.us.r-project.org")

library(gridExtra)
grid.table(head(dataset))

See what the R script does (very simple !)

Let’s see the R script in the Power BI R script editor. It’s so long script, but don’t mind ! Almost all is preparing parameters (arguments), comments, and error handlings.
Only the following is the essence of this script. Please copy and paste into your editor, and run. As you can see, the same graph (visual) is shown in the report.

require("corrplot", character.only = TRUE)
M <- cor(dataset, use="pairwise.complete.obs")
corrplot(M, method='circle', order='original', type='full', addrect=2,
  mar=c(0.5, 0.25, 0.25, 0.1) + 0.5, tl.col="red", tl.cex=0.95,
  number.digits=1, number.cex=0.6, addCoef.col=NULL)

The function “cor” is analyzing the correlation of these columns, and the result is set in the variable called “M”. Then the function “corrplot” plots this result as visuals in the canvas. (Please see the function reference for the details of the parameters.)

For example, let’s consider the following dataset. As you see, “Discounts” and “Units Sold” is completely dependent. Assuming that the unit price is 500 and the prime cost is all 0 (i.e, the selling is all for profits), you can also find that “Profits” is also strongly dependent in this case. As a result, the graph (visuals) is shown as the following screenshot.
Of course, this is the very extreme case …

dataset <- data.frame(
  "Discounts" = c(100,200,300,200),
  "Profits" = c(60000,90000,90000,90000),
  "Units Sold" = c(150,300,450,300)
)
require("corrplot", character.only = TRUE)
M <- cor(dataset, use="pairwise.complete.obs")
corrplot(M, method='circle', order='original', type='full', addrect=2,
  mar=c(0.5, 0.25, 0.25, 0.1) + 0.5, tl.col="red", tl.cex=0.95,
  number.digits=1, number.cex=0.6, addCoef.col=NULL)

 

The R of Power BI is not distributed, not clustered, not batched, or not streaming like the professional developing platforms. But the business users (marketers, analysts, etc) can easily retrieve data from the existing web (html table), files, database, services, and analyze quickly by the rich operations like correlation, clustering, statistical forecasting, etc, and share the visuals in a casual way.

There exist other so many useful samples in R Script Showcase. Take it easy and enjoy R with Power BI !

 

[Reference] Create Power BI visuals using R (official document)
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-visuals/

 

Works with Row Level Security in Power BI and Power BI Embedded

In this blog post, I describe how you can use the Row-Level Security (RLS) for Power BI, also including Power BI Embedded.

The outline of using Row-Level Security (RLS) is the following.

  1. Create and manage the roles using Power BI Desktop
  2. Publish to Power BI service
  3. Manage the user for the previous roles
  4. Share to the other users

In this example, we assume the following simple database table and use RLS with “Country” column. For example, the employees in Japan region can see the only Japan data, and the US colleagues can see the only US (North America) data.

Before using Row Level Security (RLS) described here, keep in mind that you need Power BI Pro. (This Row Level Security is the Pro feature.)

Manage Role using Power BI Desktop

First you start to build your report using Power BI Desktop. (You select the data source from database, and create the report.)
After you’ve done, please click [Manage Roles] button in [Modeling] tab in Power BI Desktop.

This displays the following window.
In this window, you can create the new roles (multiple), and for each role you can specify the filtering condition called “rule”.
For example, the following each role (“ChinaRole”, “JapanRole”, “USRole”) represents the filtering condition using [Country] column in [Sales] table.

Later we set the Power BI users in each role, and the corresponding role is used for each users.

Of course, you can specify more advanced conditions using DAX expression. Especially, you can use the username() function with rules, and this kind of  filtering is called “dynamic security”.

Note : If you use the dynamic security, you must use DirectQuery and enable the switch [Enable cross filtering in both directions for DirectQuery] in the options and settings window. (see the following screenshot)

Notice : When you’re using Power BI Desktop, username() returns a user in the format of DOMAINUSER. On the other hand, when Power BI service, username() will return the User Principal Name (UPN) which looks similar to an email address.

Manage users using Power BI service

Before sharing this report for the other users, you publish the previous report to the Power BI service using your account. (As I wrote before, you need the Pro feature.)

Notice : After you publish, you should specify the database credential (database username and password) selecting the [DATASET SETTINGS] menu in Power BI service. (The credential is not published in Power BI service for security reasons.)

After publishing, you select […] in the dataset and select [SECURITY] menu in your Power BI service. (See the following screenshot)

The previous roles (which you’ve created in Power BI Desktop) are displayed in the window. You add users (who is in the same organization in Azure AD) into each roles.
If many users exist in your tenant (organization), you can also add the security group or distribution list to the role. (Sorry, but it seems that the Office 365 Group cannot be added now…)

Check how it works, and Sharing

Before sharing your dashboard to the other users, you can check how the user can see your report.
Select […] menu in the role name, and you can see and select the [Test as role] menu. (See the following screenshot.)

The report which is filtered by the selected role is displayed.

Please click the role name in the upper bar, input the user id (UPN) in the drop-down window, and push [Apply] button.

Then the role which the selected user is belonging is applied to the report.
In the following example, the user “Jeet Jagasia” is belonging to the US (North America) Role, and the report is filtered by this role.

Even if the user is belonging to multiple roles, the corresponding all data of these roles is displayed in the report. (For example, the following person is belonging to both “JapanRole” and “ChinaRole”.)

Notice : The owner (i.e, yourself) of this report is not filtered and always see the all data. As a result, if you select yourself in the previous test window, the data is not filtered.

After the test is done, you share the dashboard including this report to the other users. All the user can see the filtered report. (If the user is not belonging to any role, no data is displayed and the error displays.)

RLS in Power BI Embedded (your custom app)

As I described in the previous post “How to use Power BI Embedded via REST“, the Power BI Embedded doesn’t depend on the Azure AD account or windows account. You can use your favorite authentication (forms auth, basic auth, etc) in your app, and you can combine these account with Power BI RLS. That is, you can freely determine “which user uses what roles” in your app.
Let’s look at the example.

Beforehand, you must understand how the Power BI Embedded works and how the access token (which is used for embedding reports) is used. In this post I don’t explain about that, but you can see “How to use Power BI Embedded via REST” for the details.

After your app authenticate the user, your app should determine what role is needed, and use the following json string as the input of access token signature.  As you can see, your app can include the proper “roles” and “username” in the claims of the access token.

{
  "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},
  "roles":"{role names (multiple) delimited by comma}",
  "username":"{user name}"
}

The following is the simple example of how to implement using PHP.
We just added the code in bold font to the original source code in “How to use Power BI Embedded via REST“. This app shows the “View Report !” button, and if you click this button, the embedded report is displayed, which is filtered by the roles of “JapanRole” and “ChinaRole”.
If your report is using dynamic security, the value of “username” is also used in the filtering.

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

// 2. construct input value
$token1 = "{" .
  ""typ":"JWT"," .
  ""alg":"HS256"" .
  "}";
$token2 = "{" .
  ""wid":"a581de28-288a-43ce-bb43-0059a991a7ce"," . // workspace id
  ""rid":"9559247b-ef11-4d14-b772-29f6b4d08c3f"," . // report id
  ""wcn":"mywsc"," . // 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")) . "," .
  ""roles":"JapanRole,ChinaRole"," .  // role name
  ""username":"matsuzaki"" .  // user name
  "}";
$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>
  
(function () { document.getElementById('btnView').onclick = function() { var iframe = document.getElementById('ifrTile'); iframe.src = 'https://embedded.powerbi.com/appTokenReportEmbed?reportId=9559247b-ef11-4d14-b772-29f6b4d08c3f'; iframe.onload = function() { var msgJson = { action: "loadReport", accessToken: "", height: 500, width: 722 }; var msgTxt = JSON.stringify(msgJson); iframe.contentWindow.postMessage(msgTxt, "*"); }; }; }()); </body>

The embedded report is displayed like the following. (The data is filtered by “JapanRole” and “ChinaRole”.)

If you’re using Power BI Embedded .NET SDK (which is built on the top of REST API), it makes this really easy.
Now, please download the .NET SDK sample in the github, and follow the article “Microsoft Azure : Get started with Power BI Embedded sample“, and change the app settings.
If you want to add roles and username in your token, please change the source code in Controllers/DashboardController.cs as follows.

public async Task<ActionResult> Report(string reportId)
{
  using (var client = this.CreatePowerBIClient())
  {
    var reportsResponse = await client.Reports.GetReportsAsync(this.workspaceCollection, this.workspaceId);
    var report = reportsResponse.Value.FirstOrDefault(r => r.Id == reportId);
    //var embedToken = PowerBIToken.CreateReportEmbedToken(
    //  this.workspaceCollection,
    //  this.workspaceId,
    //  report.Id);
    var embedToken = PowerBIToken.CreateReportEmbedToken(
      this.workspaceCollection,
      this.workspaceId,
      report.Id,
      "matsuzaki",
      new string[] { "JapanRole", "ChinaRole" });

    var viewModel = new ReportViewModel
    {
      Report = report,
      AccessToken = embedToken.Generate(this.accessKey)
    };

    return View(viewModel);
  }
}

For the details about RLS with Power BI Embedded, see the official document “Microsoft Azure : Row level security with Power BI Embedded“.

 

 

Build Your Custom Visuals in Power BI (Step-by-Step)

In this blog post, I show you how to create your own custom visuals in Power BI.
You can easily create your own visuals and put them together with the existing visuals in your report. You can also submit your visual into Office Store (Seller Dashboard) for publishing.

Note : I described about custom visuals in the previous post “How to create Power BI custom visuals (Japanese)“, but the new development tool (the following command-line tool) is released and generally available. Please use this new tool.

Setup your dev env

For custom visual development, all you have to prepare is PC (or Mac), Node.js, and Power BI service account (free). Nothing else is needed !

Before development, you must install the Power BI visual tool (command-line tool) which is used for all following development tasks.

npm install -g powerbi-visuals-tools

Create New Project

Next, you create the new custom visual project using “pbiviz” command previously installed.
Here we create the project named “MyTestVisual” as follows.

pbiviz new myTestVisual
cd myTestVisual
npm install

The folder “myTestVisual” is created, and all the project files are located in this folder.
You can change these source code as you like.

Anyway, Run and Test !

The source code is written by the typescript.
Please see the source code of src/visual.ts in your project folder. This is the main logic of your custom visuals, and it’s just displaying the simple text as follows. (When the “update” method is called for re-drawing, the “updateCount” is incremented each time.)

module powerbi.extensibility.visual {
  export class Visual implements IVisual {
    private target: HTMLElement;
    private updateCount: number;

    constructor(options: VisualConstructorOptions) {
      console.log('Visual constructor', options);
      this.target = options.element;
      this.updateCount = 0;
    }

    public update(options: VisualUpdateOptions) {
      console.log('Visual update', options);
      this.target.innerHTML = '<p>Update count: <em>${(this.updateCount++)}</em></p>';
    }

    public destroy(): void {
      //TODO: Perform any cleanup tasks here
    }
  }
}

Now let’s look at this sample in your Power BI !
Move to the visual project folder (which contains pbiviz.json), and please type the following command.
This launches the http server (https://localhost:8080/), and starts (hosts) this visual in this server.

pbiviz start

Open and login to Power BI service (https://app.powerbi.com). Then select [Settings] menu like the following screenshot.

When the setting page is displayed, select [Enable developer visual for testing].

This enables developer visual in your visual pane. After you enable this, you can see the following icon (Developer Visual icon) in the visual pane on Power BI service.

When you insert this visual into your page, the local visual (which is hosted in https://localhost:8080/) is displayed like the following screenshot.
If you resize this visual, you can see that the count is incremented by re-drawing.

Note : If you see the error like the following screenshot, it might be that the server certificate of https (https://localhost:8080) is not installed in your client. Please see “PowerBI-visuals : HTTPS Certificate Setup” and install the certificate. Or, permit this page to browse using browser settings.

The “pbiviz” (Visual Builder) is having the built-in file watcher. If you change your source code, it detects your changes and the source code is compiled (re-built) soon. (You can check the work in Power BI service immediately.)

Build your own visuals !

Super, Super Basic

As I described above, the main class of your custom visual is in src/visual.ts and this class implements IVisual interface.
The important methods of your visual class (IVisual) is the constructor and “update” method. You must setup your visual in your constructor, and draw your visual in the “update” method.

The “update” method passes “options” as an argument, and options.dataViews includes all the data for visualization. (You can take multiple dataViews, but we assume only one dataView in this blog post. We always refer as options.dataViews[0].)
The dataView is including the properties of “metadata”, “categorical”, “matrix”, “table”, and “tree”. The “metadata” is including the metadata information like “what properties are included”, “which type is each property”, “what option (which I explain later) is selected” and etc.
When you change your code (src/visual.ts) as follows, you can see like the following screenshot. (The json string of options.dataViews[0].metadata.columns is displayed.)

export class Visual implements IVisual {
  private target: HTMLElement;

  constructor(options: VisualConstructorOptions) {
    var captionArea = document.createElement("div");
    captionArea.innerHTML = "This is test chart";
    options.element.appendChild(captionArea);
    this.target = document.createElement("div");
    options.element.appendChild(this.target);
  }

  public update(options: VisualUpdateOptions) {
    this.target.innerHTML =
      JSON.stringify(options.dataViews[0].metadata.columns);
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }
}

The others (“categorical”, “matrix”, “table”, and “tree”) are including data itself. The data of “categorical”, “matrix”, “table”, and “tree” is the same data which the user has specified, but the expression of these data differs for each other.
Here I want to explain the details of each data expression (“categorical”, “matrix”, “table”, and “tree”), but “categorical” and “table” is most commonly used data expression, and I’ll explain only these 2 samples.

For example, we assume the following source.

ItemName Year Country Cost
Mouse 2014 Japan 25
Power Unit 2015 Japan 30
Accesory 2016 Japan 18
Cable 2015 North America 14
Mouse 2016 North America 30
Phone 2016 China 100

In this case, “categorical” expression would be like following. (As I describe later, this expression depends on the capabilities definition. Below is one of the examples for your understanding.)

{
  "categories": [
    {
      "source":{
        "displayName": "Country"
      }
      "values":[
        "Japan",
        "Japan",
        "Japan",
        "North America",
        "North America",
        "China"
      ]
    },
    {
      "source":{
        "displayName": "Year"
      }
      "values":[
        "2014",
        "2015",
        "2016",
        "2015",
        "2016",
        "2016"
      ]
    }
  ],
  "values":[
    {
      "source":{
        "displayName": "Cost"
      }
      "values":[
        "25",
        "30",
        "18",
        "14",
        "30",
        "100"
      ]
    }
  ]
}

The “table” expression would be like following.

{
  "columns":[
    {"displayName": "Year"},
    {"displayName": "Country"},
    {"displayName": "Cost"}
  ],
  "rows":[
    [2014, "Japan", 25],
    [2015, "Japan", 30],
    [2016, "Japan", 18],
    [2015, "North America", 14],
    [2016, "North America", 30],
    [2016, "China", 100]
  ]
}

If you want to see these data (“metadata”, “categorical”, “matrix”, “table”, and “tree”) in debug-time, you can use the following button in the Developer Visual. (When you click the following “view dataView as json” button, the following screenshot is displayed.)

dataRoles and dataViewMappings

The previous data expression is determined by your definition called “capabilities”.
Open the capabilities.json in your project root folder, and please update this json as follows. (The property “kind” means that “0” is “grouping data” and “1” is “measuring data”.)

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": 0
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": 1
    }
  ],
  "dataViewMappings": [
    {
      "categorical": {
        "categories": {
          "for": {
            "in": "myCategory"
          },
          "dataReductionAlgorithm": {
            "top": {}
          }
        },
        "values": {
          "select": [
            {
              "bind": {
                "to": "myMeasure"
              }
            }
          ]
        }
      }
    }
  ]
}

If you insert this visual in your page, you can see the visual pane like the following screenshot. As you can see, the defined dataRoles (“Summarize Category” and “Measure Data”) is displayed in the visual pane.

Please drag the following data (“Country” and “Cost”) from the data source which is having the table as the previous example in the database.

Then you can get the following json as options.dataViews[0].categorical in the “update” method. (Note that options.dataViews[0].categorical doesn’t return the following string, but you can retrieve each element like options.dataViews[0].categorical.categories and options.dataViews[0].categorical.values.)
As you can see, the following data is summarized by Country.

{
  "categories": [
    {
      "source": {
        "roles": {
          "myCategory": true
        },
        "displayName": "Country",
        "index": 0,
        ...
      },
      "values": [
        "China",
        "Japan",
        "North America"
      ],
      "identity": [...],
      "identityFields": [...]
    }
  ],
  "values": [
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        ...
      },
      "values": [
        100,
        73,
        44
      ],
      "minLocal": 44,
      "maxLocal": 100
    }
  ]
}

Next example shows how you can use “group by” in your capabilities.json.

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": 0
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": 1
    },
    {
      "displayName": "Grouping",
      "name": "myGroup",
      "kind": 0
    }
  ],
  "dataViewMappings": [
    {
      "categorical": {
        "categories": {
          "for": {
            "in": "myCategory"
          },
          "dataReductionAlgorithm": {
            "top": {}
          }
        },
        "values": {
          "group": {
            "by": "myGroup",
            "select": [
              {
                "for": {
                  "in": "myMeasure"
                }
              }
            ]
          }
        }
      }
    }
  ]
}

If you insert this visual in your page, you can see the visual pane like the following screenshot. Here we can see “Grouping” area in the visual pane.

If you select “Year” as grouping data, you can see the following dataView result as the categorical data.
As you can see, the result data is expressed as “Cost” data of 2 dimensions of categories (“Country” and “Year”).

{
  "categories": [
    {
      "source": {
        "roles": {
          "myCategory": true
        },
        "displayName": "Country",
        "index": 0,
        ...
      },
      "values": [
        "China",
        "Japan",
        "North America"
      ],
      "identity": [...],
      "identityFields": [...]
    }
  ],
  "values": [
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2014,
        ...
      },
      "values": [
        { },
        25,
        { }
      ],
      "identity": {...}
    },
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2015,
        ...
      },
      "values": [
        { },
        30,
        14
      ],
      "identity": {...}
    },
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2016,
        ...
      },
      "values": [
        100,
        18,
        30
      ],
      "identity": {...}
    }
  ]
}

If you want to get data as “table” (not “categorical”), you should write as follows in the capabilities.json.

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": 0
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": 1
    }
  ],
  "dataViewMappings": [
    {
      "table": {
        "rows": {
          "select": [
            { "for": { "in": "myCategory" } },
            { "for": { "in": "myMeasure" } }
          ]
        }
      }
    }
  ]
}

If you select “Country” and “Cost” as input data, you can get the following data in options.dataViews[0].table. (Note that options.dataViews[0].table doesn’t return the following string, but you can retrieve each element like options.dataViews[0].table.columns, options.dataViews[0].table.rows, and etc.)

{
  "columns": [
    {
      "roles": {
        "myCategory": true
      },
      "displayName": "Country",
      "index": 0,
      ...
    },
    {
      "roles": {
        "myMeasure": true
      },
      "displayName": "Cost",
      "index": 1,
      "isMeasure": true,
      ...
    }
  ],
  "rows": [
    [
      "China",
      100
    ],
    [
      "Japan",
      73
    ],
    [
      "North America",
      44
    ]
  ],
  "identity": [...],
  "identityFields": [...]
}

The following document is very useful resource for dataRoles and DataViewMappings.

PowerBI-visuals : DataViewMappings
https://github.com/Microsoft/PowerBI-visuals/blob/master/Capabilities/DataViewMappings.md

Custom Visual Properties

As you saw before, you can specify “what kind of data the user can input” using the dataRoles definition in capabilities.json. But you can add other custom properties in your visuals.

First, you define your custom object (using “objects”) and custom property (using “properties”) using capabilities.json as follows.

{
  "dataRoles": [
    ...
  ],
  "dataViewMappings": [
    ...
  ],
  "objects": {
    "myCustomObj": {
      "displayName": "Test Object",
      "properties": {
        "myprop": {
          "displayName": "Test Property",
          "type": { "bool": true }
        }
      }
    }
  }
}

After you modify capabilities.json, you must implement “enumerateObjectInstances” method in your visual class (src/visual.ts). In this method, the property value is populated by the variable (in this case, “this.myVisualProp”).
If the user change this properties’ value, the “update” method is called. Then your code should save this value into the variable (“this.myVisualProp”) and do the appropriate task in the “update” method. (The following example is just showing the value in the visual UI.)

export class Visual implements IVisual {
  private target: HTMLElement;
  private myVisualProp: boolean;

  constructor(options: VisualConstructorOptions) {
    var captionArea = document.createElement("div");
    captionArea.innerHTML = "This is test chart";
    options.element.appendChild(captionArea);
    this.target = document.createElement("div");
    options.element.appendChild(this.target);

    this.myVisualProp = false;
  }

  public update(options: VisualUpdateOptions) {
    // This example just shows the selected property in visual.
    this.myVisualProp = options.dataViews[0].metadata.objects["myCustomObj"]["myprop"];
    this.target.innerHTML =
      "Custom Prop is " + this.myVisualProp;
  }

  public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstanceEnumeration {
    let objectName = options.objectName;
    let objectEnumeration: VisualObjectInstance[] = [];

    switch (objectName) {
      case 'myCustomObj':
        objectEnumeration.push({
          objectName: objectName,
          properties: {
            myprop: this.myVisualProp,
          },
          selector: null
        });
        break;
    };

    return objectEnumeration;
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }
}

The result looks like as follows.

In this example we’ve created the simple boolean property, but you can also use other types of properties including the color pallet like the following screenshot.

In this example I’ve just explained custom properties. But you can also support other advanced capabilities like “highlighting”. (The highlighting enables selected value highlighted and causes all other visuals on the page, as the built-in visual does.) For the highlighting capabilities, see “PowerBI-visuals : Highlighting” for details.

Visualize your data !

After you determine the data format using dataViewMappings, now you can start to build your own visuals.

Here we assume the previous “table” format of data.
In this case, options.dataViews[0].table.columns returns :

{
  "0":{
    "roles": {"myCategory": true},
    "displayName": "Country",
    ...
  },
  "1":{
    "roles": {"myMeasure": true},
    "displayName": "Cost",
    ...
  }
}

options.dataViews[0].table.rows returns :

[
  ["China", 100],
  ["Japan", 73],
  ["North America", 44]
]

We use these data for our visualization.

You can use your favorite external library for visualization (see “PowerBI-visuals – Adding External Libraries“), but the d3.js is included in custom visuals by default, and here we also use this library for drawing.

Before using d3.js in your typescript, you must include the type definition for the d3. Please type the following command and include d3 type definition in your project.

Note : If you search d3 type definition using “typings” command (like the following), you can find 2 definitions in both “npm” source and “dt” (DefinitelyTyped) source. You must use one in “dt” source using “–source” option.
typings search --name d3

Note : Now d3 version 4 is released and compatible for typescript 2.0 (not compatible for typescript 1.8.x). Because pbiviz (visual tools) is now still using typescript 1.8.x, then you must specify d3 old version (commit# 7d3a939fbe55576fad2e074d007f7cc671aa0e78) which is compatible for typescript 1.8.x, instead of “typings install d3 --source dt --global” (Described on Dec 2016)

npm install -g typings
typings install d3=github:DefinitelyTyped/DefinitelyTyped/d3/d3.d.ts#7d3a939fbe55576fad2e074d007f7cc671aa0e78 --source dt --global

Next you must modify tsconfig.json in your project as follows (add the line of the bold font).

{
  "compilerOptions": {
    "allowJs": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "target": "ES5",
    "sourceMap": true,
    "out": "./.tmp/build/visual.js"
  },
  "files": [
    ".api/v1.1.0/PowerBI-visuals.d.ts",
    "src/visual.ts",
    "typings/index.d.ts"
  ]
}

Now it’s ready !
You can implement your custom visuals (src/visual.ts) as follows using d3.js. Note that here we’re using the fixed label (“Country”, “Cost”), the fixed color (fill-in color in chart), and so on. But you can also retrieve these values from metadata or columns definition. Sorry, but now I’m cutting these code in this example for your understanding. (Please add these code in your production.)

export interface TestItem {
  Country: string;
  Cost: number;
}

export class Visual implements IVisual {
  private svg: d3.Selection<SVGElement>;
  private g: d3.Selection<SVGElement>;
  private margin = { top: 20, right: 20, bottom: 200, left: 70 };

  constructor(options: VisualConstructorOptions) {
    // append svg graphics
    this.svg = d3.select(options.element).append('svg');
    this.g = this.svg.append('g');
  }

  public update(options: VisualUpdateOptions) {
    // "this" scope will change in the nested function
    var _this = this;

    // get height and width from viewport
    _this.svg.attr({
      height: options.viewport.height,
      width: options.viewport.width
    });
    var gHeight = options.viewport.height
      - _this.margin.top
      - _this.margin.bottom;
    var gWidth = options.viewport.width
      - _this.margin.right
      - _this.margin.left;
    _this.g.attr({
      height: gHeight,
      width: gWidth
    });
    _this.g.attr('transform',
      'translate(' + _this.margin.left + ',' + _this.margin.top + ')');

    // convert data format
    var dat =
      Visual.converter(options.dataViews[0].table.rows);

    // setup d3 scale
    var xScale = d3.scale.ordinal()
      .domain(dat.map(function (d) { return d.Country; }))
      .rangeRoundBands([0, gWidth], 0.1);
    var yMax =
      d3.max(dat, function (d) { return d.Cost + 10 });
    var yScale = d3.scale.linear()
      .domain([0, yMax])
      .range([gHeight, 0]);

    // remove exsisting axis and bar
    _this.svg.selectAll('.axis').remove();
    _this.svg.selectAll('.bar').remove();

    // draw x axis
    var xAxis = d3.svg.axis()
      .scale(xScale)
      .orient('bottom');
    _this.g
      .append('g')
      .attr('class', 'x axis')
      .style('fill', 'black') // you can get from metadata
      .attr('transform', 'translate(0,' + (gHeight - 1) + ')')
      .call(xAxis)
      .selectAll('text') // rotate text
      .style('text-anchor', 'end')
      .attr('dx', '-.8em')
      .attr('dy', '-.6em')
      .attr('transform', 'rotate(-90)');

    // draw y axis
    var yAxis = d3.svg.axis()
      .scale(yScale)
      .orient('left');
    _this.g
      .append('g')
      .attr('class', 'y axis')
      .style('fill', 'black') // you can get from metadata
      .call(yAxis);

    // draw bar
    var shapes = _this.g
      .append('g')
      .selectAll('.bar')
      .data(dat);

    shapes.enter()
      .append('rect')
      .attr('class', 'bar')
      .attr('fill', 'green')
      .attr('stroke', 'black')
      .attr('x', function (d) {
        return xScale(d.Country);
      })
      .attr('width', xScale.rangeBand())
      .attr('y', function (d) {
        return yScale(d.Cost);
      })
      .attr('height', function (d) {
        return gHeight - yScale(d.Cost);
      });

    shapes
      .exit()
      .remove();
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }

  // convert data as following
  //
  //[
  //  "Japan",
  //  100
  //],
  //[
  //  "America",
  //  300
  //],
  //...
  //
  // -->
  //
  // [{"Country" : "Japan",   "Cost" : 100},
  //  {"Country" : "America", "Cost" : 300},
  // ...]
  //
  public static converter(rows: DataViewTableRow): TestItem[] {
    var resultData: TestItem[] = [];

    for (var i = 0;
      i < rows.length;
      i++) {
      var row = rows[i];
      resultData.push({
        Country: row[0],
        Cost: row[1]
      });
    }

    return resultData;
  }
}

When you insert this visual in your page, you can see your visual as follows.

Packaging and Distributions

The programming has done. Now you can create your distributable package using the following command.
After the command has completed, the pakage file is created as dist/{your project name}.pbiviz. (This .pbiviz file is the zip file, and you can extract and see the resource in this package.)

pbiviz package

After you have created your package (.pbiviz), you can import this package by selecting […] – [import a custom visual] (see the following screenshot) in the visual pane of Power BI Desktop or Power BI services.

 

With Real-time, Embedded, and more …

You can use your custom visuals in Power BI Embedded. That is, you can include your own custom visuals into your PowerBI integrated applications. (But, note that the PowerPoint export is not supported in custom visuals …)

This custom visual can also be updated by real-time in your Power BI dashboard. (For example, pin to the dashboard, and push the rows into the dataset using rest api.)

 

If you have completed, you can share your visuals in the Power BI community (see below), and everybody can download your custom visuals. You can also check these useful visuals as your development examples.

Power BI : Visual Gallery
https://app.powerbi.com/visuals/

Please refer the following useful document for details.

PowerBI-visuals : Developer’s documents
https://github.com/Microsoft/PowerBI-visuals#developing-your-first-powerbi-visual

 

How to use Power BI Embedded via REST

Power BI Embedded is deprecated. Please see my post for the 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 bold value 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>
  
(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: "", height: 500, width: 722 }; var msgTxt = JSON.stringify(msgJson); iframe.contentWindow.postMessage(msgTxt, "*"); }; }; }()); </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)

Power BI Embedded のアプリ開発 (PHP, etc)

This post is the old article, and please see “How to use Power BI Embedded via REST” for new one.

(2016/07/20 : この投稿は古い情報です。最新の Power BI Embedded 開発については「How to use Power BI Embedded via REST」を参照してください。)

Power BI を使った開発

こんにちは。

以前、Power BI の UI を Application に組み込む方法として、Power BI の Tile や Report を embed する方法を紹介しました。(匿名での公開も、認証ありでの公開も可能です。「Power BI の UI を iframe で表示する」を参照してください。)
この方法は、構築した内容を部門内に共有するケースや (認証ありの場合)、逆に、広くあまねく外部に共有するケース (匿名の場合) で使用できます。

しかし、一般の開発者 (または ISV 企業) が顧客ごとに Application に組み込む場合、利用組織ごとの Provisioning (配置、展開) などの観点から、上述の embed の手法をそのまま使うわけにはいきません。(例えば、顧客組織ごとに参照するデータや Report なども変わってくるでしょう。)
こうしたケースでは、ここで紹介する Power BI Embedded を使って、展開先のアプリケーションごとに、DataSet や Report のセキュアな配布が可能です。利用顧客側では、Azure AD のアカウント (組織アカウント) を準備する必要もありませんので、従来の Credential と組み合わせて自由に Application に組み込むことができます。

現在、この Power BI Embedded を使った開発用に .NET と Node.js の SDK が提供されていますが、他言語でも開発できます。ここでは、こうした PHP, Python, Java など他言語の開発者も対象に、Power BI Embedded の内部動作 (仕組み) を紹介します(2016/05/05 : 赤字追記)

 

Power BI Embedded の概要

Power BI Embedded は Microsoft Azure 上の開発サービスであり、一般に、下記の流れで使用します。

  1. サービス提供者は、まず Microsoft Azure 上にログインして、Application 用に Power BI Workspace Collection という領域を作成します。
  2. Power BI Desktop や Excel などを使って Application 内で使用する DataSet や Report を作成します。(後述の通り、Database の Connection String を配置時に変えることができるので、ここでは開発用の一時的な Database を使って構築して構いません。)
  3. Power BI Workspace Collection 内に、サービス提供先の顧客 (利用組織) ごとに Power BI Workspace を作成します。
  4. 作成した Power BI Workspace に、上記で構築した Power BI Desktop や Excel などの成果物を Import します。
  5. Application 内から REST API を使って Report を取得し、Application 内に embed (貼り付け) します。

上記 1 は Azure Portal か (ARM ベースの) Azure REST API を使って構築 (管理) でき、上記 2 以降は Power BI Embedded REST API か Power BI Embedded の SDK (.NET SDK、NodeJS SDK を提供) を使います。

特に SDK は優秀で、これから述べる内部実装を理解しなくても、直観的な API を使って簡単に構築でき、下記の Getting Started のドキュメントですぐに試すことができます。(下記は .NET SDK のチュートリアルです。)

Microsoft Azure : Get started with Microsoft Power BI Embedded
https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-get-started/

繰り返しになりますが、本投稿では、この SDK のサンプルは使用せず、Java、PHP、Python などのさまざまな言語で実装するケースを想定して中身を徹底解説します。

 

Power BI Workspace Collection の作成と Access Key の取得

まず、Azure Portal を表示して、Power BI Workspace Collection を作成します。(下図)

作成された Power BI Workspace Collection を表示すると、下図の通り Access Key を取得できるため、これをコピーします。

なお、前述の通り、Azure の新しい (ARM ベースの) REST API を使って、Power BI Workspace Collection を管理 (作成含む) できます。詳細は下記を参照してください。

Microsoft Azure : Power BI Embedded REST – Power BI Resource Provider APIs
https://msdn.microsoft.com/en-us/library/azure/mt712306.aspx

 

Authentication (AppToken による REST API 実行)

以降の処理は、すべて Power BI Embedded REST API (下記) を使って処理できます。(つまり、プログラムから呼び出せます。)

Microsoft Azure : Power BI Embedded REST
https://msdn.microsoft.com/en-us/library/azure/mt712303.aspx

この REST API の呼び出しには、上記で取得した Access Key を使って生成される App Token が必要です。
以降に、この AppToken の生成方法と Authentication の流れを解説します。

まず、基礎知識として、OAuth Json Web Token (JWT) の理解なので、知らない方は「Azure AD を使った Service (API) 開発 (access token の verify)」を熟読しておいてください。(ここでは、この JWT については知っているという前提で解説します。)
ただし、今回使用する Access Key は Symmetry Key のため、使用する algorithm (署名作成の algorithm) は、「Azure AD を使った Service (API) 開発 (access token の verify)」とは異なります。

Power BI Embedded REST API の AppToken は、一言で書くと、上記で取得した Access Key (Symmetric Key) から HMAC SHA256 アルゴリズムによって生成されるハッシュを OAuth JWT の Signature (デジタル署名) として使用する token です。(Azure Storage (v1) とのやりとりを SDK を頼らずに開発した経験がある方は、この辺りの仕組みはご存じかと思います。)

具体的に説明します。
まず、OAuth JWT の入力 (input) に相当する 1 番目の token と 2 番目の token として、以下の文字列を準備します。(下記は、見やすいように改行や空白を入れています。)
最初の token は署名のフォーマットを意味しており、HS256 は上述の HMAC SHA256 を意味しています。
2 番目の token の wcn は、上記で作成した Power BI Workspace Collection の名前で、nbf (Not Before) は Token の有効期限の開始日時 (通常は現在時刻)、exp (Expiration) は有効期限の終了日時 (通常は開始時刻より 1 時間後)、iss (Issuer) は皆さんが構築する Application の名前を設定します。(下記では、実際に .NET SDK が使用している Application 名を設定しています。)

{
  "typ": "JWT",
  "alg": "HS256"
}
{
  "ver": "0.1.0",
  "type": "provision",
  "wcn": "mywsc01",
  "iss": "PowerBISDK",
  "aud": "https://analysis.windows.net/powerbi/api",
  "exp": 1461586171,
  "nbf": 1461582584
}

Azure AD を使った Service (API) 開発 (access token の verify)」で解説したように、これら 2 つの token を RFC 4648 による Base 64 エンコード (Base64 Url Encoding) によってエンコードして、. (dot string) でつなげた下記のような文字列を作成します。

eyJ0eXAiOi・・・.eyJ2ZXIiOi・・・

この値を入力 (input) として、HMAC SHA265 によるハッシュ (バイトコード) を生成し、このハッシュを RFC 4648 Base 64 エンコード (Base64 Url Encoding) したものが添付する署名 (signature) です。
例えば、下記は、PHP と C# を使用して、この署名 (signature) を生成するサンプル コードです。(この方式は標準に沿ったものなので、多くの言語で関数や API などが提供されているはずです。)

PHP の場合

<?php
// Get encoded signature value
$hash = hash_hmac('sha256',
	'eyJ0eXAiOi...(input value)',
	'1iNOEz1fwr...(access key)',
	true);
$sig = rfc4648_base64_encode($hash);
print($sig);

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

C# の場合

static void Main(string[] args)
{
  // Get encoded signature value
  var alg = System.Security.Cryptography.HashAlgorithm.Create();
  var key =
    new System.Security.Cryptography.HMACSHA256(
      Encoding.UTF8.GetBytes("1iNOEz1fwr...(access key)"));
  var resbyte =
    key.ComputeHash(
      Encoding.UTF8.GetBytes("eyJ0eXAiOi...(input value)"));
  var res = Rfc4648_Base64_Encode(resbyte);
  Console.WriteLine(res);
  Console.ReadLine();
}

static string Rfc4648_Base64_Encode(byte[] arg)
{
  string res = Convert.ToBase64String(arg);
  res = res.Replace('+', '-').Replace('/', '_');
  res = res.TrimEnd('=');
  return res;
}

前述の 1 番目の token と 2 番目の token に、この作成された署名を (ドット区切りで) 繋げた下記の OAuth JWT が AppToken です。

eyJ0eXAiOi・・・.eyJ2ZXIiOi・・・.VgoIEHGSOH・・・

生成された AppToken を使って、下記の通り、Power BI Embedded REST API を呼び出すことができます。

GET https://api.powerbi.com/beta/collections/{workspace collection name}/workspaces
Authorization: AppToken eyJ0eXAiOi...

なお、上述の 2 番目の token (Claim) は、利用場面に応じて異なる値が必要なので注意してください。
後述の手順における Workspace の作成時には type に provision を指定し、Workspace が決められた開発作業では type に dev を指定し、Report の Embed (Application の表示) では type に embed を指定します。

provision の場合 (Workspace の作成時)

{
  "ver": "0.1.0",
  "type": "provision",
  "wcn": "{workspace collection name}",
  "iss": "PowerBISDK",
  "aud": "https://analysis.windows.net/powerbi/api",
  "exp": {utc start time},
  "nbf": {utc end time}
}

dev の場合 (DataSet, Report の Import など)

{
  "ver": "0.1.0",
  "type": "dev",
  "wcn": "{workspace collection name}",
  "wid": "{workspace id}",
  "iss": "PowerBISDK",
  "aud": "https://analysis.windows.net/powerbi/api",
  "exp": {utc start time},
  "nbf": {utc end time}
}

embed の場合 (Report の表示時)

{
  "ver": "0.1.0",
  "type": "embed",
  "wcn": "{workspace collection name}",
  "wid": "{workspace id}",
  "rid": "{report id}",
  "iss": "PowerBISDK",
  "aud": "https://analysis.windows.net/powerbi/api",
  "nbf": {utc start time},
  "exp": {utc end time}
}

 

Workspace, DataSet, Report の作成

上述の概念がわかれば、あとは Power BI Embedded REST を使って、処理を順番に実行するだけです。
典型的な構築手順は下記です。

なお、以降では、上記で作成された {workspace collection name} を mywsc01 と仮定します。

Power BI Desktop を使った pbix ファイルの作成

Application に配置する DataSet と Report を Power BI Desktop を使って構築します。これは、もちろん、REST API ではなく、手作業で作ります。

そして、作成した内容を [名前を付けて保存] を選択し、.pbix ファイルとして保存します。

なお、下記の通り、接続先の Database の Connection String は REST API を使って (配置時などに) 変えることができるので、Power BI Desktop の成果物作成では Import や DirectQuery を使って開発用の Database と接続された形で作成して構いません。(下記の {workspace id} と {dataset id} については後述します。)
つまり、実際の配置の際、利用顧客ごとに接続先の Database をわけて展開できます。

POST https://api.powerbi.com/beta/collections/mywsc01/workspaces/{workspace id}/datasets/{dataset id}/Default.SetAllConnections
Authorization: AppToken eyJ0eXAiOi...

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

Power BI Workspace の作成

上述で作成した Power BI Workspace Collection (今回の場合、mywsc01 と仮定します) に、顧客用の Power BI Workspace を新規作成 (追加) します。
以下の REST API を実行します。

POST https://api.powerbi.com/beta/collections/mywsc01/workspaces
Authorization: AppToken eyJ0eXAiOi...
HTTP/1.1 201 Created
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true
Location: https://wabi-paas-1-scus-redirect.analysis.windows.net/beta/collections/mywsc01/workspaces

{
  "@odata.context": "http://wabi-paas-1-scus-redirect.analysis.windows.net/beta/collections/mywsc01/$metadata#workspaces/$entity",
  "WorkspaceId": "656c4ab0-4635-47ab-b4e9-23d322e35228",
  "workspaceCollectionName": "mywsc01"
}

ここで返される Workspace Id は、このあとの処理で使用しますので Application でおぼえておいてください。

pbix ファイルの Import

上記で保存した .pbix ファイルを Power BI Workspace に Import します。
下記の REST API を実行します。

なお、URI パラメータの datasetDisplayName には、Power BI Workspace 内に作成する DataSet の名前 (任意) を設定します。

POST https://api.powerbi.com/beta/collections/mywsc01/workspaces/656c4ab0-4635-47ab-b4e9-23d322e35228/imports?datasetDisplayName=mydataset01
Authorization: AppToken eyJ0eXAiOi...
Content-Type: multipart/form-data; boundary=A300testx

--A300testx
Content-Disposition: form-data

{.pbix ファイルの中身 (byte 列)}
--A300testx--

この要求の結果として、通常は、登録完了までに時間がかかるため、下記の通り、202 (Accepted) の HTTP Status と Import 作業の Id (Import Id) が返されます。

HTTP/1.1 202 Accepted
Content-Type: application/json; charset=utf-8
Location: https://wabi-paas-1-scus-redirect.analysis.windows.net/beta/myorg/imports/f55010a6-21c3-4e88-98ff-a127e12857c5

{
  "id":"f55010a6-21c3-4e88-98ff-a127e12857c5"
}

Application では、この Import Id (上記の f55010a6-21c3-4e88-98ff-a127e12857c5) を使って、下記の通り、登録の状況を確認できます。
下記は、まだ登録中 (Publishing) の状態です。

GET https://api.powerbi.com/beta/collections/mywsc01/workspaces/656c4ab0-4635-47ab-b4e9-23d322e35228/imports/f55010a6-21c3-4e88-98ff-a127e12857c5
Authorization: AppToken eyJ0eXAiOi...
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "id": "f55010a6-21c3-4e88-98ff-a127e12857c5",
  "importState": "Publishing",
  "createdDateTime": "2016-04-25T11:09:58.753",
  "updatedDateTime": "2016-04-25T11:09:58.753"
}

登録が完了 (成功) すると、下記の通り、登録結果が返されます。

補足 : 下記では、公開されている Power BI のサンプルをアップロードしたため、webUrl のみが設定されていますが、database と接続された開発では、下記の tablesdatasources に内容が設定されます。
上述の通り、dataset id を使って、この datasource の connection string を更新できます。

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

{
  "id": "f55010a6-21c3-4e88-98ff-a127e12857c5",
  "importState": "Succeeded",
  "createdDateTime": "2016-04-25T11:09:58.753",
  "updatedDateTime": "2016-04-25T11:09:58.753",
  "reports": [
    {
      "id": "c0c160e2-54a6-47ac-9893-7c73b4894d3b",
      "name": "mydataset01",
      "webUrl": "https://embedded.powerbi.com/reports/c0c160e2-54a6-47ac-9893-7c73b4894d3b",
      "embedUrl": "https://embedded.powerbi.com/appTokenReportEmbed?reportId=c0c160e2-54a6-47ac-9893-7c73b4894d3b"
    }
  ],
  "datasets": [
    {
      "id": "27faf92b-2f77-4411-8c0a-089be9392137",
      "name": "mydataset01",
      "tables": [
      ],
      datasources:[
      ],
      "webUrl": "https://embedded.powerbi.com/datasets/27faf92b-2f77-4411-8c0a-089be9392137"
    }
  ],
  "name": "mydataset01"
}

 

UI の作成 (Report の embed)

以上で設定は完了しました。(必要な Workspace, Database, DataSet, Report が準備できました。)
Application では、Report を取得して、Web の UI に貼り付けます。

まず、Application で、以下の REST API を実行して、使用可能な Report の一覧を取得できます。

GET https://api.powerbi.com/beta/collections/mywsc01/workspaces/656c4ab0-4635-47ab-b4e9-23d322e35228/reports
Authorization: AppToken eyJ0eXAiOi...
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true

{
  "@odata.context": "http://wabi-paas-1-scus-redirect.analysis.windows.net/beta/collections/mywsc01/workspaces/656c4ab0-4635-47ab-b4e9-23d322e35228/$metadata#reports",
  "value": [
    {
      "id": "c0c160e2-54a6-47ac-9893-7c73b4894d3b",
      "name": "mydataset01",
      "webUrl": "https://embedded.powerbi.com/reports/c0c160e2-54a6-47ac-9893-7c73b4894d3b",
      "embedUrl": "https://embedded.powerbi.com/appTokenReportEmbed?reportId=c0c160e2-54a6-47ac-9893-7c73b4894d3b"
    }
  ]
}

HTTP Response (上記) の embedUrl が、Web に貼り付ける Report の URL になります。

これを iframe を使って貼り付けますが、もちろん、ここでも認証 (AppToken による認証) が必要です。「Power BI の UI を iframe で表示する」で解説した方法で、token (上記の AppToken) を iframe に POST する必要があります。(これにより、この embedUrl の内容が iframe に表示されます。)

注意 : 上述の通り、この際に使用する token (AppToken) の Claim に注意してください。(embed 用の Claim を設定してください。)

下記は、PHP を使って、この embedUrl を iframe で表示するサンプル コードです。このサンプルでは、Text Box に App Token を入力してボタンを押すと Report が表示されます。
(下記は Report の embedUrl をそのまま埋め込んでいますが、もちろん、現実の開発では、この辺りの処理もすべて Application から動的に設定してください。下記は、仕組みを理解する目的で作成したサンプルです。)

<!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>
  access token:<input type="text" id="txtToken" />
  <button id="btnView">View Report !</button>
  
(function () { document.getElementById('btnView').onclick = function() { var iframe = document.getElementById('ifrTile'); iframe.src = 'https://embedded.powerbi.com/appTokenReportEmbed?reportId=c0c160e2-54a6-47ac-9893-7c73b4894d3b'; iframe.onload = function() { var token = document.getElementById('txtToken').value; var msgJson = { action: "loadReport", accessToken: token, height: 500, width: 722 }; var msgTxt = JSON.stringify(msgJson); iframe.contentWindow.postMessage(msgTxt, "*"); }; }; }()); </body> </html>

 

※ 変更履歴

2016/05/05  Power BI Embedded の Node.js SDK (PowerBI-Node) のリリースにあわせて追記