{"id":721,"date":"2020-12-02T14:14:27","date_gmt":"2020-12-02T12:14:27","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=721"},"modified":"2020-12-03T21:08:18","modified_gmt":"2020-12-03T19:08:18","slug":"connect-your-on-premises-as-cube-with-power-bi-embedded-and-re-use-your-existing-security","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=721","title":{"rendered":"Connect your on-premises AS Cube with Power BI Embedded and re-use your existing security"},"content":{"rendered":"\n<p>If you have deployed your SSAS Cubes on premises and you don&#8217;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&#8217;t have all capabilities which Power BI offers you. You can only embed the Reports with an iFrame and you also don&#8217;t have the latest features. <br>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. <br>When you also have some limitations from your management like:<br><\/p>\n<ul>\n<li>GDPR<\/li>\n<li>Compliance<\/li>\n<li>Row Level Security on your Cubes<\/li>\n<li>safe money; don&#8217;t give every user in your organisation a Power BI Pro licence or buy a Premium capacity<\/li>\n<\/ul>\n<p>&#8230; it can be very complicated to use Power BI Embedded.<br><br>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 &#8222;Embed for your customers&#8220;-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. <br><br>So, <a href=\"https:\/\/powerbi.microsoft.com\/de-de\/blog\/power-bi-gdpr-whitepaper-is-now-available\/\">GDPR und Compliance<\/a> 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 &#8222;Embed for your customers&#8220; and with this technology you can design your own application. <br><br>Now I want to demonstrate how you achieve this:<\/p>\n\n\n\n<p>To create an Emdedded App you need:<br><\/p>\n\n\n\n<ol type=\"1\"><li>a Service User<\/li><li>a Power BI Pro license<\/li><li>Setup a Data Gateway<\/li><li>Analysis Services on our Infrastructure as Service environment<\/li><li>to Setup the Gateway<\/li><li>Invoke the Gateway for the Impersination<\/li><li>Power BI Embedded APP<\/li><\/ol>\n\n\n\n<ol><li><strong>Service User<\/strong><\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"893\" height=\"700\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image.png\" alt=\"\" class=\"wp-image-750\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image.png 893w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-300x235.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-768x602.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-383x300.png 383w\" sizes=\"(max-width: 893px) 100vw, 893px\" \/><figcaption>The user will only assigned to a group which group will synchronized to our local Server.<\/figcaption><\/figure>\n\n\n\n<p><strong>2. Power BI Pro<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"453\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1-1024x453.png\" alt=\"\" class=\"wp-image-751\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1-1024x453.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1-300x133.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1-768x340.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1-500x221.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-1.png 1403w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption>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!<\/figcaption><\/figure>\n\n\n\n<p><strong>3. Setup a Data Gateway<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"639\" height=\"584\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-2.png\" alt=\"\" class=\"wp-image-752\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-2.png 639w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-2-300x274.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-2-328x300.png 328w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><figcaption>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.<\/figcaption><\/figure>\n\n\n\n<p><strong>4. Analysis Services on our Infrastructure as Service environment<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"980\" height=\"389\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-5.png\" alt=\"\" class=\"wp-image-755\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-5.png 980w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-5-300x119.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-5-768x305.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-5-500x198.png 500w\" sizes=\"(max-width: 980px) 100vw, 980px\" \/><figcaption>First we must add the Gateway user as an SSAS Administrator. This permission is needed to change the user context. <\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"613\" height=\"297\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-6.png\" alt=\"\" class=\"wp-image-757\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-6.png 613w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-6-300x145.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-6-500x242.png 500w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><figcaption>In the Cube we have a role named as &#8222;RLS&#8220;. The role will filter the data. The members are AD Accounts which will don&#8217;t have a Power BI or O365 license.<\/figcaption><\/figure>\n\n\n\n<p><strong>5. Setup the Gateway<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"547\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12-1024x547.png\" alt=\"\" class=\"wp-image-764\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12-1024x547.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12-300x160.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12-768x410.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12-500x267.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-12.png 1521w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption>I&#8217;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.<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"802\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7-1024x802.png\" alt=\"\" class=\"wp-image-758\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7-1024x802.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7-300x235.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7-768x601.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7-383x300.png 383w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-7.png 1105w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption>After the report published to the service, we must setup the Gateway connection<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"482\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13-1024x482.png\" alt=\"\" class=\"wp-image-765\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13-1024x482.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13-300x141.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13-768x361.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13-500x235.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-13.png 1333w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption>The dataset must be connected to the Gateway. After that you can view the report with the data from the on premises cube.<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"415\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-1024x415.png\" alt=\"\" class=\"wp-image-766\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-1024x415.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-300x122.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-768x311.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-1536x622.png 1536w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14-500x203.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-14.png 1622w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><strong>6. Invoke the Gateway for the Impersination<\/strong><\/p>\n\n\n\n<p>Here comes the magic. We must setup the Gateway connection to impersonate the users. It&#8217;s really no magic, but it&#8217;s very cool :) The setting cannot be done via the portal, it must be done via the REST API.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-1024x440.png\" alt=\"\" class=\"wp-image-760\" width=\"614\" height=\"263\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-1024x440.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-300x129.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-768x330.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-1536x661.png 1536w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8-500x215.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-8.png 1802w\" sizes=\"(max-width: 614px) 100vw, 614px\" \/><figcaption>On the <a href=\"https:\/\/docs.microsoft.com\/en-us\/rest\/api\/power-bi\/gateways\/adddatasourceuser#example\">msdn <\/a>website (Power BI REST API documentation) is the magic. There you can find the hint how you can setup an impersonation<\/figcaption><\/figure>\n\n\n\n<p><code>{&nbsp; <br>   \"emailAddress\": \"HybridService001<\/code>@plenz.onmicrosoft.com<code>\", <\/code><br>     <code>\"datasourceAccessRight\": \"<strong>ReadOverrideEffectiveIdentity<\/strong>\"<br>}<\/code><\/p>\n\n\n\n<p>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&#8217;s from the gateway configuration website.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"306\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9-1024x306.png\" alt=\"\" class=\"wp-image-761\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9-1024x306.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9-300x90.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9-768x229.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9-500x149.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-9.png 1360w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption>1: Gateway Id, 2: Connection Id<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"910\" height=\"537\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-10.png\" alt=\"\" class=\"wp-image-762\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-10.png 910w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-10-300x177.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-10-768x453.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-10-500x295.png 500w\" sizes=\"(max-width: 910px) 100vw, 910px\" \/><figcaption>After a successful run you should get a 200 code<\/figcaption><\/figure>\n\n\n\n<p><strong>7. Power BI Embedded APP<\/strong><br>You can register very easily an APP with the on-boarding <a href=\"https:\/\/app.powerbi.com\/embedsetup\">Tool.<\/a><br>Or you can download the code and manually configure the APP: <a href=\"https:\/\/github.com\/microsoft\/PowerBI-Developer-Samples\/tree\/master\/PowerShell%20Scripts\">https:\/\/github.com\/microsoft\/PowerBI-Developer-Samples\/tree\/master\/PowerShell%20Scripts<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"907\" height=\"703\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-11.png\" alt=\"\" class=\"wp-image-763\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-11.png 907w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-11-300x233.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-11-768x595.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-11-387x300.png 387w\" sizes=\"(max-width: 907px) 100vw, 907px\" \/><figcaption>After the wizard completion you can download a configured Visual Studio solution<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"895\" height=\"350\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-15.png\" alt=\"\" class=\"wp-image-767\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-15.png 895w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-15-300x117.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-15-768x300.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-15-500x196.png 500w\" sizes=\"(max-width: 895px) 100vw, 895px\" \/><figcaption>Now we must edit the Report ID in the web.config to our live report.<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"897\" height=\"535\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-16.png\" alt=\"\" class=\"wp-image-768\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-16.png 897w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-16-300x179.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-16-768x458.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2020\/12\/image-16-500x298.png 500w\" sizes=\"(max-width: 897px) 100vw, 897px\" \/><\/figure>\n\n\n\n<p>Now we can test the Report by providing another username with the Rolename.<\/p>\n\n\n\n<p><strong>Conclusion<\/strong><\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p><br><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you have deployed your SSAS Cubes on premises and you don&#8217;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&#8217;t have all capabilities &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=721\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/721"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=721"}],"version-history":[{"count":26,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/721\/revisions"}],"predecessor-version":[{"id":777,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/721\/revisions\/777"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}