Note: This post was originally published on Microsoft MVP Award blog, as part of the Technical Tuesday series.
Power Bi gives us the possibility to create amazing reports. Even if it's great to be able to share those reports from the very secure Power Bi portal sometimes we need to share them inside other applications or websites. Once again, Power BI doesn't disappoint us by providing Power BI Embedded. In this post, I will explain how to use Power Bi Embedded and make it secure so each tenant can only his data.
The Problem
Despite many online exist that explain how to use filters to change the witch is visible in our reports, filters can easily be changed by the user. Even if you hide the filter panel, those setting could easily be modified using JavaScript... Therefor, it's definitely not the best way to secure private information.
The Solution
In this post, I will be using roles to limit the access the data. The well knew the database Adventure Works will be used to demonstrate how to partition the data. In this case will be using the customer table.
In Azure
Open the Azure portal to create a Power BI Embedded component. Of course in a real project, it would be better to create it in an Azure Resource Management (ARM) template, but to keep this post simple we will create it with the portal. Click on the big green "+" at the top left corner. In the search box type
powerbi, and hit Enter. Select
Power BI Embedded in the list and click the
Create button. Once it's created go to the Access Keys property of the brand-new Power BI Workspace Collection and take note of Key. We will need that key later to upload our Power BI report.
For this demo, the data source will be Adventure Works in an Azure Database. To do it simply click again the "+" button and select Database. Be sure to select Adventure Works as the source if to reproduce this demo.
In Power BI Desktop
Power BI Desktop is a free tool from Microsoft that will help us to create our report; it can be download
here.
Before we get started, two options need to be modified. Go in the File menu and select Options and Settings, then Options. The first onr, is in the section (tab)
Preview Features; check the option:
Enable cross filtering in both direction for DirectQuery. The second is in the section DirectQuery, check the option
Allow unrestricted measures in DirectQuery mode. It's a good idea to restart Power BI Desktop before continuing.
To create our reports we first need to connect to our datasource, in this case our Azure Database. Click the Get Data button, then Azure and after that Microsoft Azure SQL Database. It's important to be attentive on the type of connection Import or Direct Query, because you won't be able to change it after. You will need to rebuild your report from scratch. For this case select
DirectQuery.
This chart will be displaying information about invoice detail. Be sure to include the table that will be used for your role. In this case, I will be using Customer. Each customer must see only their invoices.
The report will contain two charts: the left one is a bar chart where you see the invoice historic, the right one is a pie chart that shows how products in the invoice(s) are distributed by category.
Note: in the sample database all customer have only one invoice and hey are all at the same date
Now we need to create our dynamic Role. In the Modeling tab click on
Manage Roles and create a CustomerRole mapping the CompanyName of the customer table to the variable
USERNAME()
Of course, to test if our charts are really dynamics, create other roles, and give them specific values ex: "Bike World" or "Action Bicycle Specialists". To visualize your report as those user, simply click on the
View as Roles, in the Modeling tab, and select the role you want.
See how the charts look when see from "Action Bicycle Specialists".
The report is now ready. Save it and we will need it soon.
Powerbi-cli
To upload our report in our Azure Workspace Collection, I like to use PowerBI-CLI because it runs everywhere, thanks to Node.js.
Open a command prompt or Terminal and execute the following command to install PowerBI-CLI:
npm install powerbi-cli -g
Now if you type 'powerbi' you should have the powerbi-cli help display.
It's time to use the access key we got previously, and use it in this command to create a workspace in our workspace collection.
//== Create Workspace ===========
powerbi create-workspace -c FrankWrkSpcCollection -k my_azure_workspace_collection_access_key
Now, let's upload our Power BI report into Azure. Retrieve the workspace ID returned by the previous command and pass it as the parameter -w (workspace).
//== Import ===========
powerbi import -c FrankWrkSpcCollection -w workspaceId -k my_azure_workspace_collection_access_key -f "C:\powerbidemo\CustomerInvoices.pbix" -n CustomerInvoices -o
Now we will need to update the connectionstring of our dataset. Get his ID with the following command:
//== Get-Datasets ===========
powerbi get-datasets -c FrankWrkSpcCollection -w workspaceId -k my_azure_workspace_collection_access_key
Now update the connectionstring, passing the datasetId with the parameter
-d:
//== update-connection ===========
powerbi update-connection -c FrankWrkSpcCollection -w workspaceId -k my_azure_workspace_collection_access_key -d 01fcabb6-1603-4653-a938-c83b7c45a59c -u usename@servername -p password
In Visual Studio
All the PowerBi Embeded part is now completed. Let's create the new Asp.Net MVC Web Application. A few Nuget packages are required, be sure to have those versions or newest:
- Microsoft.PowerBI.AspNet.Mvc version="1.1.7"
- Microsoft.PowerBI.Core version="1.1.6"
- Microsoft.PowerBI.JavaScript version="2.2.6"
- Newtonsoft.Json version="9.0.1"
By default Newtonsoft.Json is already there but needs an upgrade.
Update-Package Newtonsoft.Json
And for the Microsoft.PowerBI one, an install command should take care of all the other dependencies.
Install-Package Microsoft.PowerBI.AspNet.Mvc
We also need to add all the access information we previously used in our powerbi-Cli into our application. Let's add them in the web.config.
...
<appSettings>
<add key="powerbi:AccessKey" value="my_azure_workspace_collection_access_key" />
<add key="powerbi:ApiUrl" value="https://api.powerbi.com" />
<add key="powerbi:WorkspaceCollection" value="FrankWrkSpcCollection" />
<add key="powerbi:WorkspaceId" value="01fcabb6-1603-4653-a938-c83b7c45a59c" />
</appSettings>
...
Here the code of the InvoicesController:
using System;
using System.Configuration;
using System.Linq;
using System.Web.Mvc;
using demopowerbiembeded.Models;
using Microsoft.PowerBI.Api.V1;
using Microsoft.PowerBI.Security;
using Microsoft.Rest;
namespace demopowerbiembeded.Controllers
{
public class InvoicesController : Controller
{
private readonly string workspaceCollection;
private readonly string workspaceId;
private readonly string accessKey;
private readonly string apiUrl;
public InvoicesController()
{
this.workspaceCollection = ConfigurationManager.AppSettings["powerbi:WorkspaceCollection"];
this.workspaceId = ConfigurationManager.AppSettings["powerbi:WorkspaceId"];
this.accessKey = ConfigurationManager.AppSettings["powerbi:AccessKey"];
this.apiUrl = ConfigurationManager.AppSettings["powerbi:ApiUrl"];
}
private IPowerBIClient CreatePowerBIClient
{
get
{
var credentials = new TokenCredentials(accessKey, "AppKey");
var client = new PowerBIClient(credentials)
{
BaseUri = new Uri(apiUrl)
};
return client;
}
}
public ReportViewModel GetFilteredRepot(string clientName)
{
using (var client = this.CreatePowerBIClient)
{
var reportsResponse = client.Reports.GetReportsAsync(this.workspaceCollection, this.workspaceId);
var report = reportsResponse.Result.Value.FirstOrDefault(r => r.Name == "CustomerInvoices");
var embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id, clientName, new string[] { "CustomerRole" });
var model = new ReportViewModel
{
Report = report,
AccessToken = embedToken.Generate(this.accessKey)
};
return model;
}
}
public ActionResult Index()
{
var report = GetFilteredRepot("Action Bicycle Specialists");
return View(report);
}
}
}
The interesting part of this controller is in the method GetFilteredRepot. First, it gets all the reports from our workspaces than look for the one named: "CustomerInvoices". The next step is where the loop gets closed; it creates the token. Of course, we pass the workspacecollection, workspace and report references, and that could be it. I mean passing only those references would result to our reports where all customers were displayed... But obviously that not what we want right now. The two last parameters are username and an Array of roles. When we created roles in Power BI Desktop, we created one call
CustomerRole that was equal to the variable
USERNAME(). So here we will pass the client name as username and specify that we want to use the role "CustomerRole".
Last piece to the puzzle is the View, so let add one.
@model demopowerbiembeded.Models.ReportViewModel
<style>iframe {border: 0;border-width: 0px;}</style>
<div id="test1" style="border-style: hidden;">
@Html.PowerBIReportFor(m => m.Report, new { id = "pbi-report", style = "height:85vh", powerbi_access_token = Model.AccessToken })
</div>
@section scripts
{
<script src="~/Scripts/powerbi.js"></script>
<script>
$(function () {
var reportConfig = {
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: false
}
};
var reportElement = document.getElementById('pbi-report');
var report = powerbi.embed(reportElement, reportConfig);
});
</script>
}
One great advantage of using Asp.Net MVC is that we have an @Html.PowerBIReportFor at our disposal. Then we can instantiate the report with the call of
powerbi.embed(reportElement, reportConfig);. Where I pass some configuration to remove the navigation, and the filter panes, but that optional.
Now if we run our project, you should have a result looking like that.
Wrap it up
Viola! This of course was a demo and should be optimized. Please leave a comment if you have any questions, or don't hesitate to contact me. It's always great to chat with you.
References:
Making Time (Bob Clagett) - I really enjoyed reading that book. I've been watching Bob's YouTube Channel for a while. ANd was happy to ear about his new book. In Making Time, a little bit like in his videos, Bob explains how he build stuff... but this time instead of a furniture or a room, it's a new career/ business. A short book (88 pages) but a lot of fun.
