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.