Power BI in Office 365: What You Need to Know

In a previous article we covered the BI features of SharePoint on-premises. Now let’s move to the cloud.

Windows Azure and Office 365 are the key pillars of the Microsoft cloud strategy. Pretty much any new Microsoft software today will take to cloud into consideration and the hybrid mode looks realistic and promising.   

Microsoft has become a leader in the business intelligence market: the Redmond giant is not just extending its existing on-premises Business Intelligence (BI) tools to the cloud, it’s also creating new features and new tools in the cloud. These tools might eventually be ported on-premises, but the roadmap is not yet clear. However, the cloud-first approach is a reality.

The Microsoft BI cloud strategy is called “Power BI” and is based on two principles:

Business Intelligence the Old Way

Conceptually and technically, there were certain procedures one followed for working in BI before Power BI. Conceptually, if you wanted to create a dashboard or even a simple report to analyze, you had to do the following:

Technically the traditional BI process proceeded this way:

In the traditional Microsoft BI world, the tools associated with these steps are summarized in Figure 1.

Figure 1

Self-Service BI in the Cloud: Power BI

However, in the new BI model of self-service in the cloud, Excel 2013 is the center of the Power BI universe, thanks to free add-ins like Power Query, Power Pivot, Power View and Power map. Figure 2 and Figure 3 illustrate the Power BI process, as well as the mapping between Power BI and the traditional BI approach.

Figure 2

Figure 3

Basically Excel 2013 provides the ETL with Power Query, the data warehouse/cube is created in memory with Power Pivot, and reports are generated with Power View or Power Map (which is more “story telling” oriented).

Office365 brings the essential element of sharing with Power BI sites by allowing the publication of reports and by providing a gallery of dynamic and valid data sets; the data sets can be shared with Power Query. Critically important in most BI scenarios: On-premises data can (safely) be shared via the Data Management Gateway, and the data refresh works.

The attentive reader will have noticed the “search” step in Figure 3. Indeed, users must be able to search through sets of data across the web and the enterprise (this is provided by Power Query), but must also be able to ask questions in natural language. That is the role of a new tool: Q & A.

We should point out that the Excel add-ins require the Professional Plus version of Office 2013; however, Power Query and Power Pivot are supported in Excel 2010 as well.

All of these tools are very powerful and sexy, but it’s important to define who does what.

Power BI Players

To get a better understanding of Power BI, it’s important to know who the players are, from creator to user to IT staff. The BI Power Players are the following:

Power Query

Power Query is a free add-in for Excel 2013 that allows dashboard and report creators to find data that are external or internal to the enterprise (html tables in websites, data marketplaces, big data, social networks, wikis, databases, web services, feeds) as illustrated in Figure 4.

Figure 4

Data sets can be created from a large variety of data sources in Power Query (see Figure 5).

Figure 5

Users can easily transform the data (merge, clean-up, etc.)—see Figure 6.

Figure 6

They can also create derived fields, create new data sets and share them (with data refresh)—see Figure 7.

Figure 7

PowerPivot and the Data Model

When the data sets (potentially from different data sources) have been found, they can be linked together in a new data model (the data engine of Excel) with Power Pivot, another Excel 2013 add-in (see Figure 8).

Figure 8

New calculation columns and complex operations like YTD, KPIS, and many others can be defined in an Excel like language called DAX (Data Analysis Expression).

The data in Power Pivot is actually loaded and compressed into a high performing in-memory database; data manipulation (such as filtering and sorting) is blazingly fast even when dealing with millions of rows. Power Pivot has been available since Excel 2010.

We will get back to the Power Pivot features in a future article, but for the impatient reader let’s mention that Power Pivot data models saved online will transparently be loaded in SQL Server Analysis Service (SSAS) provided by Microsoft.

Data models in Excel can be exploited to create pivot tables very familiar to business users. One of the added values is that these pivot tables can work with linked tables: There’s no need to use lookup functions anymore!

Power View and Power Map: Visualizing Data

The data models allow us to generate good-looking and interactive reports.  This is thanks to two tools, Power View and Power Map.

With Power View, you can create slides and highly interactive reports. Since most BI data usually contains different kinds of geographical information, Power View can also show data on maps. Power View was already part of SharePoint 2010 Enterprise (on-premises).

Today Power View reports can be created in Excel 2013 and will show up as worksheets (see Figure 9).

Figure 9

Previous versions of Power View required Microsoft Silverlight on the workstation, and this was a terrible limitation for people who use Apple iPads and Android devices. Now the Power BI version can be rendered in Silverlight or in HTML 5, which opens the door to any platform, including (but not limited to) mobile devices.

Regarding mobile devices, a Power BI app for Windows 8, Windows 8.1, and Windows RT is available in the Windows Store. This app provides a much better user experience than the default HTML 5 rendering.

With Power Map, (see Figure 10), you can easily create animated maps and time-oriented reports with 3D visualizations.

Figure 10

Power Map is particularly interesting in map-based storytelling. Videos of the storytelling can be generated and shared across the team or the enterprise.

Power Map animations can’t be rendered in web mode. Excel 2013 is still required on client workstations, unless the animations have been recorded in videos files that can be shared in SharePoint document libraries.

Office 365 and SharePoint Online: Share, Manage, Answer Questions

Once the reports (including the data models) have been created, we can share them in “normal” SharePoint libraries in Office 365 (see Figure 11).

Figure 11

The associated data model is (transparently) embedded into the Excel file. Even if the maximum file size is limited to 2GB in SharePoint, PowerBI workbooks exceeding 250MB cannot be display in a browser (they can still be loaded in Excel 2013).

When users interact with Excel workbooks containing data models, the data are automatically and transparently loaded in SQL Server Analysis services tabular model on the back end and the reports can be rendered in the appropriate technology in the server: Power View or Excel Services.

The same mechanism applies when sharing these kinds of workbooks in SharePoint 2013 Enterprise on-premises. However, in the cloud, Microsoft manages the instance of Analysis Services as well, since this is part of the subscription.

If the workbook size is greater than 250MB, it will be loaded in Excel 2013 rich client and won’t be rendered in web mode from the server.

A SharePoint BI Power App (Figure 12) can be provisioned in SharePoint sites. This creates a Power BI site where reports stored in document libraries can be featured and queried.

Figure 12

When clicking on the app, the reports that are featured will show up (see Figure 13).

Figure 13

We’ll discuss and describe the SharePoint Power BI app with more details in a future article. For the record, the app itself runs in Windows Azure and is based on the new SharePoint 2013 “provider-hosted app” architecture.

Another interesting functionality that this Power BI app provides is the ability to query the data models with a natural language. This new feature is called Q & A; Q&A is based on the concept of a smart semantic engine. The user doesn’t have to fully know the data model structure and column names: he or she can type a query in a very simple search text box as illustrated in Figure 14.

Figure 14

Even typos are supported!

Q & A returns the data in different formats based on the type of results. For instance, if a search result contains reference to an area (geographical information), the user will probably be able to visualize the result in a map.

About typos....The next figure (Figure 15) illustrates the types of reports that our query can return, based on a very simple query (with 2 typos): “number of gold medals by country in athletic betwee 1980 and 2000 year by year."

Figure 15

At the time of writing, the only supported language is English; support for other languages is to be announced.

Queries can also be featured (Figure 16) and therefore reused across the enterprise.

Figure 16

Office 365 comes with a new BI Administration console called the Power BI Admin center (Figure 17), where we can easily manage (among others) connections with on-premises data sources.

Figure 17

Indeed, a killer feature of Power BI is the ability to securely connect on-premises data sources in Office 365. This is provided by the Data Management Gateway service of Office 365 and Azure.

Microsoft Data Management Gateway is a client agent that enables secure cloud access for on-premises data sources within your organization and exposes data from on-premises data sources as OData feeds. Only SQL Server and Oracle data sources are supported in this first version.

Agent gateways can be installed on-premises and registered with to the Data Management Gateway Services in the cloud. This operation is very easy to set up. Up to 200 agent gateways can be defined per Office 365 tenant.

When a data management gateway has been defined with an on-premises computer, one or several data sources (see Figure 18 and Figure 19) can be defined and leveraged through the gateway.

Figure 18

Figure 19

When data sources accessed via a data management gateway have been defined, they can be used and added to a data model, like any other data set (see Figure 20).

Figure 20

Data refresh works out of the box.

This means that if the Excel workbook fetches data from external data sources (data coming from SharePoint list, web services, cloud or on-premises databases like the “Customers” data set illustrated in Figure 20) if the original data are modified, this will be reflected in the workbook. We will describe the Data Refresh with more details in a next article.

Impressive Business Intelligence Tools

We have been impressed by the Power BI tools (Power Query, Power View, Power Pivot, and Q&A). Microsoft has made the BI process and tools very easy to grasp for power users, BI analysts, and IT teams. By allowing any user to query the data model, the Q & A is a concretization of the semantic search.

Even though the tools are powerful and easy, the self-serve BI process still needs to be strictly organized--the data steward will play a key role in this picture.

A killer feature in Power BI is the ability to (safely) mix up data in the cloud and data on-premises (with data refresh) thanks to the new Data Management Gateway Service. As we will detail in our next article, Microsoft seriously considered security: Data and credentials are fully encrypted and can easily be managed. The SharePoint governance plan must take this new element into account.

With Power BI, the cloud-first approach promoted by Microsoft is a reality. Even though PowerView and PowerPivot are available on premise, Office 365 is still required to benefit from many Power Query features (for instance data sets can't be shared on-premises); Q&A is also only available in Office 365.

At the time of writing, Office 365 provides a new special BI subscription with prices that varies between 20$/user to 52$/user. A free trial of Power BI for Office 365 is available at the Microsoft website (no credit card required).

In upcoming articles, we will take a deeper look at these tools.