Thomas Wouters & Joran De Wilde | Building a client portal app with embedded Power BI reports

So hello everyone, my name is Thomas and thanks for tuning in today. So today we are talking about a client portal that we've built for a customer of ours in the Netherlands where we used our Shiny with embedded Power BI reports.

And I practiced my presentation beforehand for some friends in Belgium who do not work in the IT sector and they didn't understand a little thing. So hopefully you will understand something.

So we are Thomas and Joran, both working for the IT company AXI and we traveled all the way from Belgium especially for you today. AXI is a full certified partner from RStudio since 2020 and has a sector specific approach. If there are any questions during the presentation you can always ask through Slido or you can find us at a stand next door and have a little chat and coffee with us.

So what can you expect during your talk? Some insights why it's not always Power BI or Shiny but sometimes Power BI and Shiny. We are going to present some examples of the use of our Shiny in production and we have also some code samples for you. And of course some references to a famous man in a bow tie.

Background and customer introduction

So why are we speaking here today? Six months ago Rachel Dempsey from RStudio had an online session with Paul Chang from Microsoft about capacity planning for Microsoft Azure data centers and my colleague Thomas commented on the session if Paul had ever considered about embedding Power BI dashboards with our Shiny in combination with Azure functions that we have used for plumber APIs and Paul had never seen this before so Rachel replied on that like I think that's a great presentation too and that's why we are here today.

So the customer with whom we realized this project is a Dutch mortgage portfolio management group or the MPM and the MPM is part of the Bloudrist group that is a group of company that provides mortgage lending services together with the entire mortgage value chain and as an organization they can offer institutional investors to invest in Dutch residential mortgages.

The MPM offers portfolio management services including providing access to an online interactive dashboard called the mortgage management tool and on the slide you can see some references, some customers served by the MPM that are famous banks in Europe.

The old portal and new requirements

So as promised I was going to tell you more about how the portal was before we started so the MPM customers could consult their insights of their portfolio through the classical BI approach using click, they could log in and they can save their bookmarks and filter with custom filters but however these reports were separate from the MPM website and were not customized so the MPM has not full ownership of the portal.

So the MPM wants to evolve their portal to a serious data science portal and they want to provide their customers with richer data and insights so this new portal had some requirements like rendering documents including forecasts on time series, still providing classical dashboards but leveraging a new Azure data platform with Power BI, because they like to work in a customer-oriented way they want full control of the look and feel and the user experience and they also want to keep ownership of the portal and of course the portal needs to be future-proofed.

So Shiny is often thought of as a dashboarding tool and it plays like the categories like Power BI or Tableau but Shiny is more than that, Shiny can be used for data science applications including dashboards, full-stack web development and real-time prediction applications. As you can see from the graph, Shiny has a very strong business capability rating but the learning curve is a bit more difficult than a Power BI or Tableau.

Since R is an open source language, there are several packages available and together with the MPM we have chosen for R Markdown, Shiny and plumber. This allowed us to create interactive web applications and by using R Markdown we were able to generate our tailored documents like PDF. We used plumber to separate the logic in different functions.

BI and R integration options

So when looking at the already existing integrations between the more common BI tools and R we see that there are actually a lot of options. First of all it's easy to use an R or Python script in your data preparation, use custom visuals within your BI tools that originate from R or Python or even use a plumber API to for instance embed a forecast of your data. So integrating R in your BI tools isn't that difficult.

The other way around however can pose some challenges. Usually the only way to integrate a BI tool into your R data product is by embedding it. When looking at the integration capabilities of Tableau and R for instance we see two major packages, ShinyTableau and PlumberTableau. ShinyTableau being used for using Shiny in your Tableau dashboards and PlumberTableau you can use to use an API written in R in your Tableau's calculated fields.

Power BI also offers quite some R integrations. For instance it's very easy to get your data using an R script or to use R powered visuals in your Power BI dashboards. So with these two examples I think it's quite clear that using R in your more common BI tools is quite straightforward. The other way around however, that's another question. For instance how would you, and this is also the topic of this talk, embed a Power BI dashboard in your Shiny application?

Embedding Power BI in Shiny

If you want to use a dashboard that was written in Power BI in our Shiny application the first question that arised for us was whether or not we should. We could also just migrate the existing visuals to our Shiny application. This way Power BI serves more as a design tool where we kind of see what works and what doesn't and the final visuals are recreated in our Shiny application.

This is an interesting approach if you have dashboards that don't evolve a lot, that don't change a lot and you have people who feel comfortable working in R and Shiny. The option of embedding is more interesting if you have dashboards that do change and evolve a lot. This way you can update your dashboards without having to republish your app every time. Furthermore you can have a team that's really dedicated on maintaining the Power BI dashboards who don't need to have any Shiny knowledge.

Furthermore you can have a team that's really dedicated on maintaining the Power BI dashboards who don't need to have any Shiny knowledge.

For our case we were definitely in the second category where we had a team of people who didn't necessarily possess the correct amount of Shiny knowledge and we had dashboards that changed and evolved a lot.

How does embedding a Power BI dashboard work exactly? The first thing we do is write the application and the general structure of the application itself in R and Shiny. Next we use our Power BI and its drag and drop features to create a nice looking dashboard and thereafter we simply embed the dashboard via a nice and clean way into our app. This way we get a portal that thanks to a unified UX and style, it's very difficult for the customers to notice where your Power BI dashboard ends and our Shiny application begins.

Of course to be able to embed a Power BI dashboard you'll need a few things. First of all of course the Power BI desktop software because with this software you'll be creating your Power BI dashboards. You'll also definitely need the Power BI service because after all that is where your reports will be published to. With the Power BI service you could technically embed your Power BI dashboards already using iFrames however if you want to embed it using the Power BI API you'll also need Power BI dedicated capacity which you can either get via Power BI Premium or Power BI Embedded. Keep in mind however that this option will be a bit more pricey but I hope to convince you in the next slide why this extra cost actually might be worth it.

So the easiest solution is using iFrames to embed your Power BI dashboard however this comes with some very big disadvantages. First you'll have to authenticate again in each iFrame which means that the customer will have to log in for each dashboard that it wants to see and there are also no interaction possible between your Power BI dashboard and your Shiny app. These disadvantages are negated by using API embedding because thanks to the Power BI API you can integrate an automatic authentication and with the API you can also catch all the interactions which means that you can actually have your Shiny app communicate with your Power BI dashboard. This way you can create bookmarks, apply and delete filters and many many many more options.

Technical implementation

How does one embed a Power BI dashboard into an R Shiny app you may ask? Well fortunately for us Microsoft offers a lot of examples on how to embed a Power BI dashboard using the API. Unfortunately however there is no clear example of how to do it using R Shiny. Lucky for us and probably unlucky for others there's JavaScript to the rescue. Via the Power BI JavaScript library you can actually embed a Power BI dashboard using JavaScript.

This means that your R Shiny app and the JavaScript files will have to communicate with each other. On the R Shiny website there's already a very nice overview of how to set up the interactions between your R and your JavaScript. A brief overview, so send custom meshes can be used in your R application to trigger JavaScript functions or JavaScript code. These triggers can be read and caught using the add custom message handler in your JavaScript files and to send input back from your JavaScript to your Shiny application the Shiny dot on input change button can be used.

A quick example of how we use these functions in our app. So when a user navigates in your R Shiny app to a page where there should be a Power BI report we will call the send custom message function which will call the embed API function in your JavaScript files. With this custom message we'll also send some parameters for our report. Next in the Power BI embed dot JS JavaScript file the add custom message handler will catch this call embed API and using some magic JavaScript code we can actually and using the library we can embed the Power BI dashboards at a specific container in our Shiny app. When it's done rendering we will let our app know that the report has been rendered by using the set input value and our Shiny app can continue with its next step or steps in the process.

To realize the authentication and the more complex interactions some more complex embedding steps are needed. We won't go into too much detail in this talk but I can share with you already the tip of the embedding iceberg. For authentication we use the Azure auth package this together with the HTTR package for retrieving the embed token made it possible to integrate the authentication within our Shiny app so no more two different authentications necessary. The Power BI API was then used to create bookmarks to apply and delete filters and so on and so on.

I want to conclude with a brief overview of how to embed a Power BI dashboard into a Shiny app. For everyone just tuning in this is probably one of the most important slides of our talk. So we start by getting an access token via Azure with this access token we can get the embed URL from the Power BI API and with this URL together with JavaScript we can render our Power BI reports in a container in our app. Keep in mind however that you will need Power BI embedded capacity for this to work.

For everyone just tuning in this is probably one of the most important slides of our talk. So we start by getting an access token via Azure with this access token we can get the embed URL from the Power BI API and with this URL together with JavaScript we can render our Power BI reports in a container in our app.

Closing thoughts

So what lessons have we learned today? Align your Power BI style and your layout within your Shiny app. If you can't see where the Shiny ends and your Power BI begins you are doing it right. Be mindful and aware of some cloud costs and add logging where you can. Shiny can log Power BI actions as well.

So at the start of the presentation I mentioned this, those were the check marks but did we cover them all? We covered some debunking choices. We have seen some examples of Shiny in production. You can find our code samples via the link on the slide where you can find step-by-step documents created with Quarto, but we didn't cover a reference to a famous man in a bow tie so without further ado that's all folks.

Q&A

For Thomas and Joran, why did you decide to integrate BI for the portal into Shiny instead of the other way around?

We think that with using our Shiny you have a lot more options than just staying in Power BI which is why we chose to actually do it this way. It's also a very interesting concept to embed a Power BI dashboard in a Shiny app and Shiny offers way more options and customizability than Power BI. You're not stuck in the logic of Power BI. You have more freedom which is also what the customer wanted so we chose for this way of working.

How did you ensure both PBI and Shiny use the same data?

That's a very interesting question which I don't have the answer to right now but definitely come look at us at our booth and we can discuss this in finer detail.

Do you know if other BI tools like Tableau have API driven authentication like Power BI does?

So we believe that the same setup could actually also be achieved with Tableau. Tableau has also an API for embedding however we chose for Power BI in this case because our customer was already heavily relying on the Microsoft stack which made it an obvious choice to choose Power BI to embed but it is also possible with the Tableau API.