{"id":1447,"date":"2024-11-02T17:21:42","date_gmt":"2024-11-02T15:21:42","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1447"},"modified":"2024-11-02T17:21:42","modified_gmt":"2024-11-02T15:21:42","slug":"passing-parameters-variables-to-tabular-cubes-from-power-bi-emedded","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1447","title":{"rendered":"Passing parameters\/variables to Tabular Cubes from Power BI Emedded"},"content":{"rendered":"\n<p>In this article I would like to show you how parameters\/variables can be passed from Power BI Embedded to Tabular Cubes. In this example I would like to show you how users can be passed to the cube that do not exist in Windows. This means that user names are passed to the model, which then filters the data accordingly. For this I use the dynamic RLS you described in the following article:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-flip-it-de-sql-bi-and-more wp-block-embed-flip-it-de-sql-bi-and-more\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"G1ViWE6h9s\"><a href=\"https:\/\/www.flip-design.de\/?p=1435\">Dynamic row-based security (RLS)<\/a><\/blockquote><iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8222;Dynamic row-based security (RLS)&#8220; &#8212; flip-it.de :: SQL, BI and more\" src=\"https:\/\/www.flip-design.de\/?p=1435&#038;embed=true#?secret=zLV2NjlTtm#?secret=G1ViWE6h9s\" data-secret=\"G1ViWE6h9s\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>Because the user does not exist in AD, impersinaton, which was described in the following article, is not possible:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-flip-it-de-sql-bi-and-more wp-block-embed-flip-it-de-sql-bi-and-more\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"xFAIueHsmC\"><a href=\"https:\/\/www.flip-design.de\/?p=721\">Connect your on-premises AS Cube with Power BI Embedded and re-use your existing security<\/a><\/blockquote><iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8222;Connect your on-premises AS Cube with Power BI Embedded and re-use your existing security&#8220; &#8212; flip-it.de :: SQL, BI and more\" src=\"https:\/\/www.flip-design.de\/?p=721&#038;embed=true#?secret=ho1tdOflwP#?secret=xFAIueHsmC\" data-secret=\"xFAIueHsmC\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>No users can be passed to the respective cube who are running this session and they can only be Windows users. Thus, this example used users that do not exist in the directory.<\/p>\n\n\n\n<p>I got the idea for this from the following article:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-kasper-on-bi wp-block-embed-kasper-on-bi\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"3Rd5BU2H1Y\"><a href=\"https:\/\/www.kasperonbi.com\/using-customdata-and-ssas-with-power-bi-embedded\/\">Using CUSTOMDATA and On-Premises SSAS with Power BI Embedded<\/a><\/blockquote><iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Using CUSTOMDATA and On-Premises SSAS with Power BI Embedded&#8221; &#8212; Kasper On BI\" src=\"https:\/\/www.kasperonbi.com\/using-customdata-and-ssas-with-power-bi-embedded\/embed\/#?secret=AW9KwmbSQF#?secret=3Rd5BU2H1Y\" data-secret=\"3Rd5BU2H1Y\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>The programming within the Power BI Embedded application was contributed by my colleague Michael Bujotzek, because he simply developed really cool source code!<\/p>\n\n\n\n<p>The magic lies in the following function: CUSTOMDATA. The nice thing here is that a lot of data can be transferred. In itself you can treat it like a one-dimensional array, i.e. h. If further information is to be passed, all you have to do is agree on a separator. I&#8217;ll show you how this works later.<\/p>\n\n\n\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/dax\/customdata-function-dax\">https:\/\/learn.microsoft.com\/en-us\/dax\/customdata-function-dax<\/a><\/p>\n\n\n\n<p>First it is necessary that a hybrid scenario is set up. This means that a gateway must point to the Tabulat Cube and a report with a semantic model must use it and be integrated into the application. A model with the dynamic RLS must be built inside the cube.<\/p>\n\n\n\n<p>First, we have a simple cube that uses dynamic row-based security here. A role is not included here, it&#8217;s just about the structure so that relevant facts about the respective user are provided.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image.png\"><img decoding=\"async\" loading=\"lazy\" width=\"940\" height=\"539\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image.png\" alt=\"\" class=\"wp-image-1448\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image.png 940w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-300x172.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-768x440.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/figure>\n\n\n\n<p>After the cube is deployed to the server, Power BI sets up the gateway to that cube. Here the user is fixed, i.e. h. all connections are established with the configured user.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1.png\"><img decoding=\"async\" loading=\"lazy\" width=\"787\" height=\"1024\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1-787x1024.png\" alt=\"\" class=\"wp-image-1449\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1-787x1024.png 787w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1-231x300.png 231w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1-768x999.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-1.png 940w\" sizes=\"(max-width: 787px) 100vw, 787px\" \/><\/a><\/figure>\n\n\n\n<p>If you call up the report, which provides a view of the current Windows user name (USERPRINCIPALNAME), you get the cube with all of its data under this user name.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-2.png\"><img decoding=\"async\" loading=\"lazy\" width=\"940\" height=\"479\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-2.png\" alt=\"\" class=\"wp-image-1450\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-2.png 940w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-2-300x153.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-2-768x391.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/figure>\n\n\n\n<p>If you configure or program the web application (this is just the example application from Microsoft) so that it receives CUSTOMDATA, data is included in the function. Here the value 123 is passed to the CUSTOMER role.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>using (var pbiClient = await GetPowerBiClient())\r\n            {\r\n                \/\/ Create a request for getting Embed token \r\n                \/\/ This method works only with new Power BI V2 workspace experience\r\n                var effectiveIdentity = new EffectiveIdentity(\r\n                    username: \"123\",\r\n                    roles: new List&lt;string> { \"Customer\" },\r\n                    datasets: datasetIds.Select(datasetId => datasetId.ToString()).ToList() \r\n                );\r\n<\/code><\/pre>\n\n\n\n<p>The role is absolutely simple, this is where the information that the function returns is used. To check, I transferred the content from the function into a measure.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-3.png\"><img decoding=\"async\" loading=\"lazy\" width=\"748\" height=\"698\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-3.png\" alt=\"\" class=\"wp-image-1451\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-3.png 748w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-3-300x280.png 300w\" sizes=\"(max-width: 748px) 100vw, 748px\" \/><\/a><\/figure>\n\n\n\n<p>If further information is provided from the function, it can be extracted accordingly. Here is an example of how the second part is extracted. Of course, this must correspond to the handover.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SecondPart:= \r\nVAR FullText = CUSTOMDATA()\r\nVAR Parts = SUBSTITUTE(FullText, \"-\", \"|||\")\r\nVAR SecondPart = PATHITEM(Parts, 2, TEXT)\r\nRETURN SecondPart\r\n<\/code><\/pre>\n\n\n\n<p>If you now call up the application with the value 123 if the information is filtered according to this user name:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-4.png\"><img decoding=\"async\" loading=\"lazy\" width=\"940\" height=\"479\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-4.png\" alt=\"\" class=\"wp-image-1452\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-4.png 940w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-4-300x153.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-4-768x391.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/figure>\n\n\n\n<p>Now you pass on a different user name, which of course has to be maintained in the table if it is filtered by the user.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-5.png\"><img decoding=\"async\" loading=\"lazy\" width=\"940\" height=\"479\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-5.png\" alt=\"\" class=\"wp-image-1453\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-5.png 940w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-5-300x153.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2024\/11\/image-5-768x391.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/figure>\n\n\n\n<p>Personally, I find this function very valuable because it allows parameters\/variables to be passed to a dataset within Power BI.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this article I would like to show you how parameters\/variables can be passed from Power BI Embedded to Tabular Cubes. In this example I would like to show you how users can be passed to the cube that do &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1447\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","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\/1447"}],"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=1447"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1447\/revisions"}],"predecessor-version":[{"id":1454,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1447\/revisions\/1454"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1447"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1447"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}