This post should serve to inform you as a manager or executive what to expect from analysis presented in Microsoft Power BI. This primarily non-technical post will cover terminology, design, visuals, data connections and updates, and user empowerment.

Terminology

Desktop, Service, Mobile App:

The Power BI product has three primary components: Desktop, Service and Mobile App. Executives are more likely to use the Power BI service or Mobile App than Power BI Desktop. Here is a quick summary of the differences between these alternatives:

Power BI Desktop is a traditional Windows application like Excel, Word, PowerPoint and Outlook.  It is the primary authoring environment used by analysts and developers to create Power BI reports and the visuals they contain. It can also be used to view and interact with Power BI reports. In Desktop, a business intelligence specialist can build a sophisticated data model by connecting to one or more data sources, relating tables, creating calculations, splitting or combining fields, etc… What is Power BI Desktop missing? The ability to build or view a “dashboard” and the ability to share your analysis.  Dashboards exist only in Power BI Service, and cannot be authored or viewed in Power BI Desktop. To share what has been authored in Power BI Desktop, you could copy it to a fileshare or SharePoint site like we’ve been doing since Lotus 1-2-3 was a thing (and try to keep up with all the versions that grow out of that copy), but a far better way is to publish the report to the Power BI Service.

The Power BI Service resides at https://powerbi.microsoft.com. It is a software-as-a-service product connected to your Office 365 account. The primary purpose of the Power BI Service is sharing insights. A Power BI Service user can:

• View and edit dashboards (with appropriate permissions).
• View, edit, author and interact with reports (with appropriate permissions).
• View reports and dashboards published as an “App” (with appropriate permissions). More on this later.
• Author reports from scratch by connecting directly to data sources, but to a more limited degree than with Power BI Desktop.

The third Power BI client is the mobile app available from the Apple, Google Android and Microsoft Windows app stores. It is a touch-optimized application that enables viewing Power BI dashboards. In addition, the mobile app allows you to set data alerts and view visuals based on cached data while offline.

Visual:

A chart, graph, table, or other graphic depiction of data and data relationships. More about visuals below.

Report:

A report in Power BI is analogous to a workbook in Excel. Reports can have one or more tabs.

Dashboard:

A dashboard is a canvas on which visuals are “pinned” from reports. A dashboard is well named; its purpose is to enable at-a-glance views of business metrics. Clicking through any of the dashboard visuals will open the report from which the visual was pinned, so that the user can explore the context of the visual. As stated above, dashboards exist in powerbi.com and the Power BI mobile apps, not in Power BI desktop.

One killer feature that can be enabled in Power BI dashboards is “Q&A”. This feature enables a user to type (or speak, with Microsoft Cortana integration) natural language questions and receive charts, graphs and maps in response. Here I typed “count of violations by type” and Power BI presented me with a bar chart. I then changed “type” to “Zip Code” and it drew me a map.

App:

Unfortunately, there are two completely different uses of the term “app” in the Power BI world. First, there is the above-mentioned mobile app that installs from an app store on an iPhone or other device. Second, there is the concept of a group of reports and dashboards published together in the Power BI Service. These “Apps” make it easy for your IT organization to publish and maintain secure access to groups of reports.

Visuals

Power BI currently (as of October, 2017) ships with 29 visuals. One of its many killer features is the fact that this visual library is extensible. Any developer can build and submit visuals for Microsoft to evaluate and publish to their online store, and many have. A quick count showed 113 custom visual offerings in the store, ranging from key performance indicators to gauges, maps, temporal charts, filters, etc. Most of these custom visuals are license free.

Providing Feedback on Visuals

With such a vast selection of visuals from which to choose, it can be tempting for a Power BI report developer or analyst to use as many as possible. In many cases, a custom visual can provide insight or “tell a story” better than any of the out-of-the-box visuals. However, the best report design advice is always “simpler is better.” Keep that in mind if you have the opportunity to provide report design feedback. Report design should prioritize the conveyance of important business information with a minimum of distraction. More on design later.

Report developers should use specific visual types to convey specific data insights. There are many perspectives on this topic, including a great illustration by Andrew Abela.

• Clustered bar or column charts are best for comparisons.The ribbon chart, new as of September, 2017, adds visual queues that show ranking trends over time.

• Line charts and sparklines are best for reflecting change over time.
• To depict the contribution of parts to the whole, stacked bars or columns are usually better than pie charts. Pie charts can be used with very small numbers of categories, but designers should consider waffle charts for simple comparisons between larger numbers of categories. Heat maps can be a useful way to convey contribution of parts across multiple dimensions. Waterfall charts can also be a great way to express an additive metric that has many components, e.g. “fiscal year sales and gap to-date over several different licensing types”.
• To express states of a flow, a funnel chart can be best. If the data flows from multiple sources to multiple destinations, a Sankey chart can provide a unique visual insight. An example of a good use of Sankey is “average daily quantity of customer support request by channel and by call center”.
• Typically, histograms depict distributions the best. Box-and-whisker visuals are great for comparisons between distributions.
• Power BI has several different ways to show spatial and geographic data. ArcGIS is probably the most powerful.
• Scatter charts are best to show correlations, like call center expenditures versus calls handled. Notice that this one is augmented with a trend line and an explanatory label.

• Single metrics, like a running monthly count of safety incidents, are best shown in a “card” visual. If that metric is a key performance indicator with a specific goal and trend, the KPI visual is best.

• Report developers should never underestimate the power of a simple table. Showing key detail in-situ with the storytelling visuals can serve to answer questions efficiently. This isn’t mandatory, as users can always drill to data from a visual (click the ellipsis at the visual upper right, select “see data”)
• Slicers enable interactive filtering.

When presenting controversial insights, or when presenting complex business metrics to experts, more raw data is better than more pretty visuals. Designers should rely in simple charts augmented with tables.

Another thought with regard to visuals and reports – keep in mind whether the information you are being presented is declarative or exploratory. Declarative visuals indicate statements of fact, e.g. “third quarter sales of potatoes on the western U.S. were down 7.6% year over year”, and they don’t change often. Report writers have time with these to apply design and polish. In contrast, exploratory visuals are the result of innovative thinking, usually by a team, about the business, e.g. “we compared tuber sales to dark green veg sales in the west, and found a negative correlation; we think the ketogenic diet trend is decreasing demand for starchy veg”. These visuals are usually less color coordinated, polished, and shiny than declarative visuals, because they are fresh. As an executive, focus your feedback on the content of exploratory visuals, not their beauty, to encourage quick and open sharing of innovative thought.

Interactions of Visuals

Power BI visuals can act as interactive report filters. For example, imagine clicking on the “Midwest region” column of a “Net Fiscal Quarter Sales by Region” column chart. Power BI will immediately filter the entire report tab to the Midwest region. This capability is on by default, and it is developer configurable to highlight rather than filter the report.

Report designers can use a visual called a “slicer” to give you an easy way to interactively filter a report. These have been around in Excel for quite a while. Often however, a small visual like a “call volume by call center” bar chart can be used as both an informational visual and a report filter. This can be a better use of screen real-estate than a slicer.

Drilldown with Visuals

The drilldown button: in the upper right corner of a data visual will toggle drilldown mode for that visual. With it off, clicking on a line or column will simply select and focus that segment of the visual, like this:

Toggle the drilldown button on, then click on a report data point, and the visual will drill down to the next level of the x-axis hierarchy, if one is available. In the visual below I have toggled drilldown on, then clicked on the “2015” data point of the VanArsdel line. The report is now focused on 2015.

In contrast, the “go to the next level in the hierarchy” button: in the upper left corner of a visual will do exactly what is says. It will focus the whole visual down one level of the x-axis hierarchy. For example, after clicking that button, the report below shows sales summed by month over all of the years in the database. It is clear from the visual below that June is the top sales month for VanArsdel over the past 17 years.

The “expand all down one level in the hierarchy” button: in the upper left of the visual will increase “grain” in the chart by including the next level of detail from the next lower hierarchy level, as in this example:

Focus Mode

Have someone looking over your shoulder while discussing a specific visual, or need to focus discussion one one specific visual during a meeting? Click on the “focus mode” icon () in the top right corner, and it will zoom to fill the canvas. This is a temporary display mode – nothing will happen to the layout of the rest of the report if you use this.

Data Connections and Updates

The most powerful feature of Power BI may be the “get data” button. It lists 76 data sources to which the product can connect out-of-the-box, and the data connector API is fully documented, so third parties can write their own connectors. Further, Power BI has its own on-board query processor, meaning it can consume data from multiple sources and combine the results. In the example below, a standard sales database has been augmented with population data to create a “sales per capita” calculation. This was done by screen-scraping a couple of Wikipedia pages (Population by US City and State Codes by State) from Power BI desktop, then relating the resulting tables to the existing geography table on city and state.

It is not always feasible or practical to combine multiple data sources, so please be understanding if your data geek says it can’t be done.

Streaming data can be shown real-time in a Power BI report. More traditional non-streaming data can be updated up to eight times daily to the Power BI Service, or forty-eight times daily on the new “Premium” Power BI product. A developer can easily place an “Updated on: [date] [time]“ card on a report tab to indicate the freshness of the data in the report.

User Empowerment

In Power BI Desktop, and to a certain extent in the Power BI Service, users are empowered to build their own reports and visualizations. Given some training and practice, users can answer their own questions and perform self-service research without involving their IT group.

This leads to a classic two-edged sword in business intelligence: multiple versions of the truth, also known as “Excel Hell.” As shown below, three different analysts’ sales models might define “Territory Sales Growth” in three different ways. Tony uses a denominator that includes last year’s prorated sales, while Pat uses last year’s total fiscal year-to-date, and Sri factors in adjustments that were made after the FY closed in both the numerator and the denominator. Which is correct? From which calculation should business decisions be made?

Multiple Versions of the Truth (FYTD = fiscal year to date)

Catapult Systems advocates a tiered system of report tagging in order to signal the degree of trust that should be attributed to a calculation in a report. This can serve to encourage data exploration and sharing while signaling an appropriate level of caution. We will be happy to tell you more about it in person.

Design

If you have the chance to specify the design of Power BI reports or provide feedback, here are some common rules of thumb.

• Whitespace is valuable. Developers may feel the need to fill every square millimeter of space with information, and if you’ve asked for 35 visuals on a topic, that may be what they deliver. The usual design goal is clearly organized report with a limited number of impactful visuals surrounded by plenty of whitespace.
• Consistency of layout is an asset in a multi-tab report. One common guideline is that who, what, how, when and why oriented visuals should each have their own consistent places on a report.
• Report colors can be entirely customized, including the base color palet.
• Branding and backgrounds can add to the professional look of a report.
• Layouts can be customized for use on mobile devices as well as Power BI Desktop and Service.

Summary

The purpose of this article is to summarize Power BI for managers and executives being asked to use the product. It was intended to help you get smart quickly on the essential features of the product, and to enable you to give some feedback on report design. This is the most rapidly evolving major product I have ever seen out of Microsoft. There are many capabilities and technical features that were not covered here such as security, bookmarks, parameters, data models, DAX calculations, etc., that your report developers should understand and utilize. Now is a great time to be in the business analytics field.

Feedback always appreciated.