Connect your on-premises AS Cube with Power BI Embedded and re-use your existing security

If you have deployed your SSAS Cubes on premises and you don’t want to deploy them into the cloud, but you want Power BI? One option is to use Power BI Report Server, but there you don’t have all capabilities which Power BI offers you. You can only embed the Reports with an iFrame and you also don’t have the latest features.
With Power BI Embedded you can integrate your Reports, Dashboards and also Tiles into your own application and you can design the app by your needs. Power BI Embedded can only used by the cloud environment. So, there is no option to use this technology on your own on premises infrastructure.
When you also have some limitations from your management like:

  • GDPR
  • Compliance
  • Row Level Security on your Cubes
  • safe money; don’t give every user in your organisation a Power BI Pro licence or buy a Premium capacity

… it can be very complicated to use Power BI Embedded.

For the first to things you can implement a Hybrid Scenario. You have your own infrastructure, install a Power BI Data Gateway and connect your Power BI Tenant to your local environment. But this solves not the two other problems. If you use a Power BI Embedded application, you can run it with a „Embed for your customers“-Mode, so you spent money for the capacity, for one Power BI Pro license, but the application has only one user, so you get in struggle with the Row Level Security. So you must by for every user a license or for the app a capacity? No! You can set up the Gateway with an Impersonation Mode. With this setting you can push your user names to this Cubes and re-use the Row Level Security.

So, GDPR und Compliance can be solved by the Gateway, because you have only Runtime data at the cloud. The Row-Level Security can be used by the Impersonation and you can also safe money by using Power BI Embedded „Embed for your customers“ and with this technology you can design your own application.

Now I want to demonstrate how you achieve this:

To create an Emdedded App you need:

  1. a Service User
  2. a Power BI Pro license
  3. Setup a Data Gateway
  4. Analysis Services on our Infrastructure as Service environment
  5. to Setup the Gateway
  6. Invoke the Gateway for the Impersination
  7. Power BI Embedded APP
  1. Service User
The user will only assigned to a group which group will synchronized to our local Server.

2. Power BI Pro

To assign very easily a Pro Trial license to this user, log in with this account to Power BI and create a new Workspace; No other Office 365 licences are needed!

3. Setup a Data Gateway

Now we must install a Data Gateway on our on premises environment. This is also a reason why we need a Pro license. The Data Gateway connects our AS Cube to the Cloud.

4. Analysis Services on our Infrastructure as Service environment

First we must add the Gateway user as an SSAS Administrator. This permission is needed to change the user context.
In the Cube we have a role named as „RLS“. The role will filter the data. The members are AD Accounts which will don’t have a Power BI or O365 license.

5. Setup the Gateway

I’ve created a Power BI Report which will use a Live connection to the Cube. The report display a Sales Amount to test the RLS and the current username who views the report.
After the report published to the service, we must setup the Gateway connection
The dataset must be connected to the Gateway. After that you can view the report with the data from the on premises cube.

6. Invoke the Gateway for the Impersination

Here comes the magic. We must setup the Gateway connection to impersonate the users. It’s really no magic, but it’s very cool :) The setting cannot be done via the portal, it must be done via the REST API.

On the msdn website (Power BI REST API documentation) is the magic. There you can find the hint how you can setup an impersonation


"emailAddress": "HybridService001
@plenz.onmicrosoft.com",
"datasourceAccessRight": "ReadOverrideEffectiveIdentity"
}

You can setup the Gateway without Postman or other tool. The try it button will set the properties. You need the data source and the gateway id. You can get the id’s from the gateway configuration website.

1: Gateway Id, 2: Connection Id
After a successful run you should get a 200 code

7. Power BI Embedded APP
You can register very easily an APP with the on-boarding Tool.
Or you can download the code and manually configure the APP: https://github.com/microsoft/PowerBI-Developer-Samples/tree/master/PowerShell%20Scripts

After the wizard completion you can download a configured Visual Studio solution
Now we must edit the Report ID in the web.config to our live report.

Now we can test the Report by providing another username with the Rolename.

Conclusion

With the impersonation you can connect Power BI to your Cubes which are using a Row Level Security. So, Hybrid scenarios are not very new, but when you Embedded, a Data Gateway and this setting, you can provide Power BI solutions very easily and very cheap, because you can use the A-SKU for Embedding. But you must aware of, that you must code an application which makes the authentication.


Schreibe einen Kommentar