Thursday, August 8, 2019

Improve Power BI User Experience by Leveraging Azure Services


Business Case:

Our client, a multinational technology company, adopted Power BI across their entire organization. As a result, many internal project and sales managers moved hundreds of reports to Power BI.

The sheer volume of reports made organization challenging. Power BI did not allow our client to divide reports into team subcategories as they usually did. Team members could not easily organize their own requirements either.

Filter management provided another hurdle. Filters applied on one report were not automatically applied to another. A team member who wanted to view data from France, for example, needed to apply the France filter to each individual report. To mitigate this, our client used OneNote to document reports for role-specific users, a time-consuming and labor-intensive task.

Each quarter, our client’s business leaders met to discuss sales and marketing goals. To prepare for this meeting, business leaders created a PowerPoint using visuals from Power BI reports. This process was extremely time-consuming, taking around one month per quarter to complete. Our client ran VMs to generate slide decks on demand, spending over $10,000 a month.

Key Challenges:

  Develop a report organization system 
  Create a global filter for reports 
  Enable business leaders to export visuals to PowerPoint in a way that is quick and cost efficient  

Our Solution:

We implemented a three-part solution: 
1.  Report collections 
2.  Global filter panel 
3.  Export to PowerPoint tool  

1. Report Collections

To improve report organization, we created report collections driven by user roles. Admins can create the collections, and embed reports on the website. Reports in collections appear in the appropriate workspace and act as a one-stop shop for their designated users.

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.

2. Global Filter Panel

We created a global filter panel that enables central management of all filters across all reports. We built the filter using HTML5 controls, integrating it as part of the Azure web application.

Individual users can use the global filter panel to customize the filters applied to their reports. Users can also establish favorite filters, customizing the default views of reports to suit individual needs.

3. Export to PowerPoint Tool

To reduce the time needed to export Power BI visuals to PowerPoint, we implemented an in-house Export to PowerPoint tool.

Figure 1: Export to PowerPoint Tool Architecture

The tool represents Power BI reports as a gallery of visuals. Users can place visuals in an online representation of the desired PowerPoint slide, choosing from both out-of-the-box and custom visuals. The tool exports chosen snapshots into a PowerPoint slide deck, complete with a custom layout.

To generate PowerPoint exports, we used Azure Kubernetes Services to orchestrate two containers. One captured snapshots of the visuals and the other generated the PowerPoint file. All images and files are saved in Blob storage. When a user downloads the final PowerPoint file, any related files and images are deleted from Blog storage.

Business Outcomes:

The completed Azure web application significantly improved user experience. Report collections meant that users no longer had to search to find relevant reports. The global filter saved users time by eliminating the need to apply an individual filter to every report. 

Our Export to PowerPoint tool reduced the time needed to create the quarterly PowerPoints from one month to a half hour. The time needed to export the PowerPoint decreased as well, from several hours to five minutes.  

Since the completed Azure web application did not require VMs to constantly run, our client saved over $10,000 a month. 

Highlights:

    Created Azure web application that enabled users to filter and organize reports easily
    Decreased time required to produce PowerPoint slide decks from one month to half an hour via a custom Export to PowerPoint tool
    Lowered costs by implementing a pay-per-use architecture, saving the client over $120,000 annually