Mendix driven Power BI reporting using Power BI row level security and Azure Active Directory Security.

6
Mendix driven Power BI reporting using Power BI row level security and Azure Active Directory Security.   Solution Goal : Use light weight interactive Power BI reports inside a Mendix IFrame were the row level security, Multitenant Security and OData feeds are controlled through a Mendix Cloud application with its existing security. Be able to create Power BI reports on the fly and add them to Mendix clients dynamically in a blink of an eye.   The current solution.   We have a Mendix application were we can add Power BI licenses to the standard Mendix License. This adds a Menu Item were the Power BI reports can be viewed. With each one of these licenses a unique client id is created with a role that is later matched to the Power BI Security. We also create Client OData Extract Objects with a OData extract URL, username and password. This is used to extract all the different data feeds we may want to use.     We use Matillion which is a ETL tool to extract all the OData(Any data source) that is specified in the client OData Extracts. So we first Extract the Mendix Power BI client extract data using OData. We loop through all the active clients and extract all the data that is need for the client. We then add row level security on to each row using the ClientName, ClientID and Site or LocationID.       Once all the data is transformed from all the sources it is written to a Snowflake Data Base. We extract data from Mendix, Ikentoo which is a Food and beverage point of sale and Xero at the moment but it could be anything.   Once this is done we create a work space and Data Set in power BI(This will be done once). All this information is stored in Mendix along with the Azure active directory user. The Azure active directory controls the access to the Power BI api.   Now reports can be created from the Power BI desktop and published to the Power BI cloud. A role and filter must be created for each Mendix client in Power BI, these come from the ClientID and ClientName records in Mendix. The report name and Power BI ID is created in Mendix and the reports can be viewed in Mendix using the Power BI api. At the moment we create a html file and use a java action to copy it to the Mendix web directory on the server and use an iframe which points to that URL. We can’t use a custom string widget or html snipped as the html sting contains java script that cant be rendered by Mendix. As seen Below!   <!DOCTYPE html> <html>   <body>  <div id="reportContainer" style="height:900px;width:1500px;"></div>      <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/jquery/dist/jquery.js"></script>  <script src="https://microsoft.github.io/PowerBI-JavaScript/demo/node_modules/powerbi-client/dist/powerbi.js"></script>    <script>         (function () {             var embedToken = "H4sIAAAAAAAEAB2Wx6rFDI6E3-XfusE5NfTCOefjuHPOOXuYd5_bsxVCSEV9qP7nHzt9hzkt_vn3P6hdq1FXSD8nNJHuR1HmdByus6C7ppu4B__i81ISfsaPhU_lXqHC53cNAyjgqV-QJqDvLgxHZUstu1dxEYqMBnuhOTIQXWULu5yACLWvSWSOsbdtrl6B_EDT7L2osFwqtJ1yjulRqECD7ISmRGmJtw-5kOIs2vD58Ou43odpgB7CvLINcf1xmKrT0sIGKgBdUC3oGup4RV7IRXxzcPPLRHcBXZxp2fzZrneaIsQu4tzETGSlbDq8MRi0anYDTv6n_CCIcbz-wl1cisaMnlpkYppsXzCnN0miB9abCm5cD-c3wsWdaNXgkBKED12CgWN606am_sDsV8L6aOw38xLJnwzSAOflaVxlhEkaa7COaZo3wsEOuY_n4PTlgiRkZeybaR0Iw4JhQfj3IqU8mDO7InxqZwLVU2kFRjPTVBrSCyDeTHOc_sx_pRuh5zr3kVVISGfmWhWDGbRLCUJyuJzHmuxJOY01h54wWoyrvJicl840zimKEUMgQGV47g6lkT5sb44JLlzh8jJtsPyMxW5w2Szjj-9iJh2uzHver0dhabM4CVD4cb2ceXhwj8wll8d2ZOIYw9ebdhxRB-K1OwGYb4urOLsJ1BpTceMJUQmWEcJ8uMERWTUtp0MwXG_iXLpoYfFu15DPWs9acFRZXOmglXXrg1pNmvky2vGi7T1tsinJp9_hh0wHxFZFMx-ZfNF06YM7kLTm7kD_oe1vjgcUXUKOmhH4ldtMZcuEcQoF715Q9WQHBEeBOd9Df5-fUXlkpadti6ahm59i5WCJORUwTZOl1BDB53miolbZmSEmofLlXCxr2wIPYiIfwbrbL1QEIKtDVut4sTv37zHuTS49m99H9SXu8c9hQS6PtRvqr-M5J9bpEm8MqQAUO-6tjHZFl3LNS-wv8NExgBHGegBgRMrxkJcBzBMuCerdWJg9I5_ZfPwkZgbdd_jIsHTD5ES9LFOWEym-bpAVuMpEvsQSNy1zUTfEBxdWnxZptpTKAmqYNcnZJBp5eKOnxMyWK2Yj4GpUaGo_iEyD9dBdlkvKvN0Ma7wXkUm5QIhjSAlJUU6muBUqqAdp3mJLDh-uzDmfM8f6Dn2C3Ev631IXohdVTl7Cu3z3RnvkRBc2LYSmYPqKC1tEYMdpPakBC75zVbZV5ogwdurPOz4Gdq8mZad21iA7Ioea-RVzvoQAThi-PE3OSszyMlNw6s3ntzUdBvwsPva7gdg7XbadTEHo2vGDgNvIL-YPrzJMz6hSfqhx-T142MWW8zBIfGReGixbSyyCVSEwa7DQoGhoe9yRvvKSnbN4u9bu6qaPTrVI_nJsC15y9eG0MWWxC1fU5nGZ4LuNLy-tC-xirEaWPGASdwuSzTnY4sr1mShi70qfY36Tw_1ZS3xDCCfbYsWvAYt-Dmb21ehz_IONkIrzocOQweaJBbMXFFBKcPFBoaZJHkUZPeuvu0R1opIqVHV3WAu9dy8X42rvUZi2JIeOXNKR0qyHydu9QoL5aDVgo2_NCgjrZgFwZoOWl1JvIuEDPooAhg-AQfCkbxj3Imggv9_bPdVBy9nxlX4dc9rMeR1rXiEtUu4En7BcDAIkcMrVVE5j2jj_yzYJC5pp17ib8k1gyuFevNoNieOM105m0fzeOyivAAXTm0sPiVOjUm6M5BKXo32udZ9WjsDKC49MjQm4uLIOoDZrGTenax3QpUtovGbN26AACDrozH--zT_cVoEchJF_9Je7ICXATcGfCYsAsQRamDTKgkvdIwIlGcb4PFm6fTuHFilLIrPJ3_hUGeD95osw5OaMZhODQDSe8srB54cvBc4Xj9iph6vkIsShb5aHk1ttvX9iEbj2NnE_myeNK8HI_IahdMaYGqTVXLFv21uyQnrC9-s06yBr-kES5zmTRMoEELaxLKRfYeO8CsYHJjvF-dC2M2n4RNKVid-aYMncrXovTqGpjtZSUIz_ri7W1uGupM3PnReVZWwm-cMK63cqkIadjRXvqU53MKQu1Gc6cUrDQYysCIV3cijIrLQUr6_xdyiZP6YAMmu7t8rf2OSBxHb5I5eItZ9tc6GsaRbgZkqQn3S1WsSPO6hWdtTNag-hwyXxbtDIzANhUS48mtWxesO3A6dejsiHYx6jHAIwOqvbbfpTmmWM7FeQIZ4I6JrjBzq5rOQLs2GKcxuDG4bFxJzf7Xv6BInR7Fu69wV_u0vtT6j79zgII3hEfKlB_tsbwPqlaKWLp55bmr2_C8mFW1Y0mwRKZ4ua0FJtk_mqrletS_z3Aryve9AWopAlil9sI5T323OvGWM1pqHvPb9l2pxxHfB__vUPt73LMWvl-xeHhIeoG7mFhGsKZa5-F_zv9X8kXVugxiwudmHyUK2rjVi1r7Z0tMv_RS6yrLnMyY-8n6-h1p2tdM1vey8TcxYUciOIh-S03JRXviWU13AcAa2Q0H0lmo5PDNgIu6E5gNpgeG8XrlgodWr_M7mgicRSaSiosIw0Dv1cGtywwz13fdWy4jmESoZr9g_cZhiiGXRdkEOdUj_4kJEKEz8ZbJ1t5i1x6_NpqVbX7k9VMS93KRkrGwkXavGdl_WnDXPVSu-frgc8YcRUqdf3Q3F8AL6dEsuzDZ4fVRG6btsnlEgns86BWG0lluRC-5c-9HeENIRu9hqQSUwnXrBkeOte4qaf2dz5z3_-K_O7NOWmBH8qH0FMI9FE9mdPJ3cjWlvExfX_d3ltPaXHuZV_bZ7hbmEEDO3LIpoQp3wu0qTwlxwCZ3x647YUzbDoSJj1QHWqB5fKZARxky2_eX6rWKpC6_BwgAHwImGvI2IetpDT8f6dpw5sck469YWSkur-CLFXTYyFp_37jFEGuiu47LRrywiGHxFrkeSQcCF31tt2UrAJLYLfFtqSR_CrJoRx0wDmT9HYNH9v4nkHqHFgfl97JJZRtrQ03Hlu3u9KXus-796mKnrCbYQ6jvqhrKH5S9KCI2BuKVUd5ENP9j6BYkKfmRYygjKHo1YS1187dllmJnblbWnBz0OO7BSjtqVERCs1-wPS20S5xAlHr1VlUgGyvqi25wFwWDODHIB-UN3z_pP5f_8PIb-qoAIMAAA=";             var reportId = "61b1fa59-2ed2-48a7-a075-099ae2c310a6";             var embedUrl = "https://app.powerbi.com/reportEmbed?reportId=27093fcd-fcb4-4875-8d16-d948d4bdbd80&groupId=3def51b8-8cbb-480d-8aa6-0667cb63dff6";   var models = window["powerbi-client"].models;               var config = {                 type: "report",       tokenType: models.TokenType.Embed,                 accessToken: embedToken,                 embedUrl: embedUrl,                 id: reportId,                       settings: {                     filterPaneEnabled: false,                     navContentPaneEnabled: true                 }             };               powerbi.embed(document.getElementById("reportContainer"), config);           })();    </script>   </body>   </html>     By doing this we can quickly create reports in Power BI and share them with all our Power BI clients. When the clients view the reports they will only see their data. We do not have to create a report for each client. The Mendix application encapsulates and controls the Power BI reports.         I am sure there are other Mendix partners or developers that see the value in using this Power BI and Mendix use case. We have gone through the whole process of getting this working. We are prepared to work with Mendix to create a Power BI widget that has all the necessary input variables and then just displays the power BI report. This would make the whole process easier and it can be made available for everybody in the community.     Regards, Patrick
asked
4 answers
4

What a coincidence... This week I have created a PowerBi widget that does the thing you are describing above.

Here's the repository: https://github.com/JelteMX/mendix-powerbi-widget

answered
0

Thanks for the information. The information you provided is very helpful for Power BI training learners.

answered
0

Hi guys, 

Indeed it's very interesting topic. 

Can anyone share the info upon the module from which we can integrate this kind app?

I'm investsigating the "ReportingForMendix" module, but I could not find any page that looks as the first page mentioned at the begining of the conversation. 

Thanks in advance, 

Kind regards,
Aleksandar

answered
0

Opsimize rocks

answered