Thursday, August 8, 2019

Azure Services Improves Power BI User Experience

Business Scenario

Our client, a large international software company, adopted Power BI across their entire organization. As a result, many internal project managers and sales managers moved their reports to Power BI. The managers’ workspaces utilize hundreds of reports. Because the workspaces contain so many reports, organization is a challenge.

Our client asked us to create a solution for their Power BI reports to solve three challenges:

   Report organization
   Filter management
   PowerPoint creation

The first challenge involved report organization. Because of the way Power BI functions, our client could not divide reports into team subcategories. In addition, team members could not easily organize their own requirements. As a result, team members needed extra effort to locate relevant reports. For our solution to succeed, users needed to find report organization intuitive.

The second challenge involved filter management. Filters applied on one report were not automatically applied to another. Team members could not globally manage filters. As a result, team members who wanted to know about data from France, for example, needed to apply the France filter to each individual report. Only then could team members analyze the data. Before the implementation of our solution, our client used OneNote to document reports for role-specific users. Users then used the documentation to select the correct report and apply the right set of filters. To improve user experience, our solution needed to enable global filter application.

The third challenge revolved around creating PowerPoint decks. Each quarter, business leaders within our client’s organization met to discuss sales and marketing goals. To prepare for the meeting, the business leaders created a PowerPoint using visuals from Power BI reports. In the client’s old process, business leaders needed to:

1. Manually screenshot the report
2. Save the image
3. Move the image to the PowerPoint
4. Format the image correctly

Effort to create PowerPoint slide decks routinely required one month per quarter. The cost of running the VMs each month to generate the slide decks on demand regularly exceeded $10,000. Our solution needed to minimize the VM cost and the time spent creating the PowerPoint for the quarterly meeting. 

To address the three challenges, we implemented a three-part solution:

   Report collections
   Global filter panel
   Export to PowerPoint tool

To improve report organization, we created report collections driven by user roles. Admins can create the collections, and admins can also embed reports on the website. Admins also determine how collections are presented to users. Admins use business capability, focus area, area grouping, role category, and role to determine who sees the Power BI reports. Reports then appear in the appropriate workspace and act as a one-stop shop for the designated users.

To improve filter management, we created a global filter panel. The global filter panel enables central management of all filters across all reports. Individual users can use the global filter panel to customize the filters applied to their reports. As a result, users do not need to reapply filters each time they navigate to a report. Users can also favorite filters.

To reduce the time needed to export Power BI visuals to PowerPoint, we implemented our own Export to PowerPoint tool. Using the tool, users can browse Power BI reports as a gallery of visuals. Once users identify the relevant visuals, they place the visual in an online representation of the PowerPoint slide. Users can choose both out-of-box and custom visuals to include in their slides. When the slides are complete, the tool exports snapshots into a PowerPoint slide deck, complete with custom layout. Our Export to PowerPoint tool reduced the time needed to create the quarterly PowerPoints from one month’s effort to a half hour.

How We Did It: The Technical Implementation

To build the role selection capabilities, we created an Azure web application with embedded Power BI reports. The application connected to an Azure database that housed the report collection and role definitions. The setup allowed admins to add additional reports to the collection through the web interface.

We built the global filter panel using HTML5 controls and integrated the panel as part of the web application. Integrating the panel enabled us to filter selections through the user session and save favorite filter selections. Favorite filters empowered users to customize the default views of the reports to suit individual needs. Global filter selection is passed to the embedded reports, filtering the displayed data within each report.

To generate PowerPoint exports, we used Azure Kubernetes Services to orchestrate two containers. (See Figure 1.) One container loaded the reports in a headless browser (Puppeteer) and captured snapshots of the visuals in the reports. We saved the captured images to Blob storage. We used the other container to generate the PowerPoint file from the selected images and save the file to Blob storage. Once a user successfully downloads the PowerPoint file, the file and images are deleted from Blob storage. 

Figure 1: Export to PowerPoint tool architecture

Business Outcomes:

   Improved user experience by filtering reports based on role
   Empowered users to create custom report collections
   Reduced effort by allowing users to filter reports globally
   Decreased time required to produce PowerPoint slide decks from days to minutes
   Lowered costs by implementing a pay-per-use architecture

The completed Azure web application resulted in several benefits for our client. The custom report collections significantly improved user experience. Users no longer had to search to find relevant reports. The global filter also improved user experience. Using the global filter, users could apply the same filters across multiple reports, saving significant amounts of time.

The Export to PowerPoint tool resulted in the most influential benefits for our client. Using the Export to PowerPoint tool, our client reduced the time needed for creating PowerPoints from one month to a few hours. The actual time needed to export the PowerPoint decreased as well, from several hours to five minutes. Most importantly, because the completed Azure web application did not require VMs to constantly run, our client saved over $10,000 per month.