Tag Archives: Power Pivot

Business Intelligence in SharePoint 2019

The recent availability of the SharePoint 2019 public preview, and the supporting information that accompanies it has clarified the status of Business Intelligence features in SharePoint 2019. This release, with one exception, is the culmination of the process of decoupling BI from SharePoint which began in SharePoint 2016 through the removal of Excel Services. This decoupling strategy was initially articulated in the fall of 2015 with the document Microsoft Business Intelligence – our reporting roadmap which stated that SQL Server Reporting Services was to be the cornerstone of their on-premises BI investment (and not SharePoint).

The embedded BI features now run with SharePoint as opposed to on SharePoint. These changes do however require some planning and some effort on behalf of those that have already invested in the current platform and wish to move forward on-premises. With this in mind, and the fact that concise information around these changes is a bit difficult to find, I wanted to put this reference together. This post does not get into migration strategies, only the changes themselves.

The source for much of the below comes from discussions with the relevant product teams, and official information is found (today) in two primary places. The document What’s deprecated or removed from SharePoint Server 2019 Public Preview
which was published concurrently with the SharePoint 2019 public preview, and Christopher Finlan‘s presentation at the Microsoft Business Application Summit 2019 entitled Self-service BI and enterprise reporting on-premises with Power BI Report Server.

A summary of the changes to BI features, and a brief discussion of each is below.

Feature Status
SQL Server Reporting Services Integrated Mode Removed
Power View Removed
BISM file connections Removed
PerformancePoint Deprecated
PerformancePoint – Decomposition Trees Removed
Power Pivot for SharePoint Removed
Scheduled workbook data refresh Removed
Workbook as a data source Removed
PowerPivot management dashboard Removed
PowerPivot Gallery Removed

SQL Server Reporting Services Integrated Mode

SSRS Integrated mode was deprecated in November 2016, as was not a part of SQL Server 2017. However, organizations could continue to use SSRS versions from 2016 and prior in SharePoint 2016. This is not supported in SharePoint 2019, which means that integrated mode isn’t an option at all with SharePoint 2019. The good news is that the recent Report Viewer web part fully replicates the capabilities of the SSRS Integrated mode web part.

Power View

Power View was a feature of SSRS Integrated mode and is available in Excel. When Excel Services was removed in 2016, Power View in Excel required SSRS Integrated mode to work. Both supporting platforms are now gone, and thus Power View is not supported in SharePoint 2019.

BISM file connections

The BISM file connection type was used by Excel and SSRS to connect Power View reports to SQL Server Analysis Services data sources. This connection type has been removed along with Power View.

PerformancePoint Services

PerformancePoint is a combination of capabilities that includes dashboarding, scorecards, and analytic reports. Very few new features have been added to PerformancePoint in the last few versions, and this one even loses a few. Many of of these features are also available in Power BI and Power BI report server, and Microsoft has taken the decision to deprecate this product. This gives customers with a PerformancePoint investment time to migrate their assets but is a clear indication that it will also be removed in a subsequent release.

PerformancePoint – Decomposition Trees

The Decomposition Tree feature in PerformancePoint came originally from ProClarity – one of the three products that made up the original PerformancePoint product. These visuals are based on Silverlight, and have been removed from the product accordingly.

PowerPivot for SharePoint

PowerPivot for SharePoint is not supported in SharePoint 2019. PP4SP was originally a combination of two technologies – a specialized version of SQL Server Analysis Services, and a SharePoint service application. In the 2016 version, these two parts were split into two – the SSAS component became a part of the SQL Server installation media as SSQL – PowerPivot mode, and the service application, which continued the name PowerPivot for SharePoint. To be clear, it is the second of the two that has been removed. SSAS PowerPivot mode continues to be an important component and is used by Office Online Server for working with Excel files that have embedded models.

Scheduled workbook data refresh

This feature allowed for the automatic refresh of the data stored within Excel workbooks in SharePoint. It requires a PowerPivot data model to work, but the refresh operation would refresh all connected data in the workbook on a scheduled basis. This was a component of PowerPivot for SharePoint. It has recently been announced that this capability will soon be available in Power BI Report Server.

Workbook as a data source

With PowerPivot for SharePoint deployed, it is possible to use the data model in a published Excel workbook as the data source for another workbook. This feature will no longer be available, and there are no plans at present to reintroduce it.

PowerPivot Management Dashboard

Originally a part of SharePoint Central Administration, the management dashboard provided status updates on all PowerPivot for SharePoint operations. Being a part of PowerPivot for SharePoint, this has been removed accordingly.

PowerPivot Gallery

The PowerPivot Gallery is a modified SharePoint Document library form that displays worksheet thumbnails contained in published Excel workbooks. This component is Silverlight based, and part of PowerPivot for SharePoint. It has been removed accordingly.

Power View, Decomposition trees, and the PowerPivot gallery were the last remaining features that carried a Silverlight dependency. SharePoint 2019 no longer has any Silverlight dependencies.

These changes are significant for anyone with an existing Business Intelligence investment that plans to move to SharePoint 2019. I intent to write more about migration strategies and will be addressing these topics at various conferences in the future.

Power BI Desktop Excel Connections

Using Excel Files with Power BI Desktop and SharePoint

As discussed in a previous post, Working with Excel Files in the Power BI Service, Excel and Power BI have a rich, complex relationship. Power BI Desktop is the primary design tool for Power BI, and it has many feature overlaps with Excel as an analytic tool. Excel can be used both as an analytic tool and a data source, and the structure of the Excel file will dictate the way that Power BI Desktop can be used with it. If Excel is being used as an analytic tool (i.e. connected to data), the appropriate items in the file can be imported into Power BI Desktop. If it is being used as a data source (data in worksheets), Power BI Desktop will connect to it, and use its data to build a model. This post attempts to articulate the nuances of both scenarios.

Importing from Excel

Power BI Desktop has an unfortunate name in my opinion. It is a design tool and is not meant to replicate the capabilities of the Power BI service on the desktop, as the name might suggest. A better name for the product would I believe be Power BI Designer. Its purpose is to connect to and transform data (Power Query), build data models for Analysis (Power Pivot) and build reports (report designer).

Used as an analysis tool, Excel has all these capabilities as well. In fact, the first two (Power Query and Power Pivot) are identical to what is already in Power BI Desktop. Excel also has Power View for analytic reporting. Power View is very similar to the type of reporting in Power BI Desktop, but uses a different technology and has been deprecated for some time. As a result, Excel charts and pivot tables are the primary means of visualizing data in Excel.

So why would you need to use Power BI Desktop if you are using Excel? As explained in my previous post, the Power BI service can fully interact with Excel as an analysis tool, and allows you to interact with Excel right from the Power BI Service. If Excel is meeting all your analytics needs, then there may be no need to introduce Power BI Desktop at all. However, if you wish to take advantage of Power BI’s analytic reporting capabilities, and you have existing Excel assets, you may wish to convert them to the native Power BI format.  Whatever the reason, moving from Excel to Power BI is relatively straightforward with Power BI Desktop.

From the File menu in Power BI Desktop, select Import, and then Excel Workbook Contents.

Importing Excel Files contents

You are then prompted to select an Excel file. Once selected, you are then presented with a warning dialog.

Excel files contents warning dialog

The dialog does a very good job of explaining what will happen, specifically the fact that data from workbooks will not be brought into this new file. Any Power Pivot data models or Power Queries will be brought in. If the workbook contains legacy Power View sheets, they will be converted to native Power BI visuals. In addition, any legacy (non-Power Query) data connections used by the source file’s Power Pivot data model will be converted to Power Query and imported.

Imported Power View sheet

Legacy Power View Sheet converted to Power BI visuals

A complete list of workbook content and what is/isn’t converted is below:

Excel Content Import to PBI Desktop Support
Data in sheets Not imported
Data model (Power Pivot) Imported
Data connections Converted to Power Query and imported
Power Queries Imported
Power View Sheets Converted to PBI visuals and imported
Pivot charts/tables Not imported
Excel charts Not imported
Macros Not imported

Once imported, the new Power BI file (PBIX) lives on its own and contains no connection or any other type of relationship to the original source Excel file. If the source Excel file is changed, there is no way to update the PBIX file. Any imported data connections are between the PBIX file and the original data source. The new PBIX file can be published to the Power BI service like any other.

Connecting to Excel

Connecting to Excel as a data source is a very different thing than importing from it. In this scenario, the data in the worksheets and only the data in the worksheets is brought into the data model. The is very different behaviour than that of connecting to Excel files to the Power BI Service, where both the model and the worksheet data is brought in.

Using the Excel Connector

The easiest, and most obvious way to connect to Excel worksheet data is by using the Excel connector. From the ribbon in Power BI Desktop, select Get Data. The Excel connector is right at the top of the list.

Connecting to Excel files on the file system

Selecting it allows you select your source file, and then the workbooks within it, and then build out the data model.

This approach works well but carries with it an important limitation. The new queries are  connected to the file using a local file system. This means that to be refreshed, an on-premises data gateway is required. In order to eliminate the gateway requirement, you can connect to the file in SharePoint using the SharePoint folder connector.

Using the SharePoint Folder Connector

The SharePoint Folder connects to all the files stored in libraries of a SharePoint site. It allows you to report on file metadata, but it also allows you to drill into file contents.

From Power BI Desktop select Get Data but instead of selecting Excel, Search for SharePoint and select SharePoint folder.

Using the SharePoint folder connector

Once selected, enter the URL of the SharePoint site (NOT the URL of a library or folder) in the dialog box.

Next, you will be presented with a preview of all the files in your site. Unless you are only interested in file metadata, click on the Edit button to bring up the Power Query editor.

The initial view will contain all the files in the site, but we are interested in the content of just one of those files. Every file in this view will contain the hyperlinked value “Binary” in the Content column. Clicking that link for the file that you want to connect to will drill down into the contents.

Site contents using the SharePoint folder connector

From this level, you can build your Power Query, data model, and report as needed just as if you had used the Excel connector. The difference is that now when you publish your report to Power BI, it will know the file is stored in SharePoint and will connect directly to it. It will not require a gateway for refresh purposes. Once credentials are registered, the report will refresh itself directly from the workbook stored in SharePoint.

XLS vs XLSX

A note of caution. The above SharePoint folder approach only works for XLSX files. The Power BI Desktop and the Power BI service both support both Excel file formats (XLS and XLSX). However, refresh does not. If the source file format is XLS, and a refresh is attempted, you will receive the classic “microsoft.ace.oledb.12.0 provider” error in the Power BI service.

Excel files refresh error with XLS file types

The older Excel file format (XLS) requires an Access driver to refresh, which is not a part of the Power BI service. The newer XLSX file does not require this driver. As a result, if the source file is XLS, refreshing it requires going through an On-Premises Data gateway, and that gateway machine must also have the ACE components installed.

To recap, you can bring Excel assets into Power BI Desktop by using the import function, and you can load data from Excel files through Power Query. The two operations have very different results, and the can be combined if a source workbook contains both analyses and data.

Working with Excel files in the Power BI Service

Power BI has been able to work with Excel files since it was first introduced. Indeed, it was born from the analytic capabilities in Excel. Users can connect directly to Excel files by using the Power BI service and nothing but a browser. However, depending on the content of the Excel file, and the method of connecting, the resulting products can be very different. In this post I will attempt to clarify this behavior. A subsequent post will detail the options available when working with Excel files in Power BI Desktop.

File Structure

Excel is a multi-purpose tool. It contains all the building blocks of Power BI, and as such, it is an excellent Business Intelligence client. Excel files are also often used (much to my chagrin) as a data storage container, or as a data transport medium. Understanding how the file is structured, and what you want to do with it is key to making the right choice when combining it with Power BI.

Originally Excel files (workbooks) were collections of worksheets. Analysts could import data into those worksheets and then analyze them with the tools that Excel provided. Although Excel was never intended to be a database, it’s ease of use and familiarity led many people to begin using it that was, and “spreadmarts” (spreadsheet data marts) quickly became a problem. The problems arose because the instant data was extracted from a source it became stale, and the fact that it was being stored in worksheets meant that it could be edited (changing history) and became subject to the data size limitations of a worksheet.

To take advantage of Excel’s analytic capabilities without being subject to the issues involved in worksheet data storage, the data model was introduced, initially through PowerPivot. The data model is a “miniaturized” version of the SQL Server Analysis Services tabular engine that runs in Excel. This data model is read only, refreshable, and highly compressed which importantly means that its only data limitation is the amount of available memory available on the machine running it. Importantly, this engine is the same engine that is used by Power BI – the advantages of which we’ll explore shortly.

Excel of course still needs to be able to use worksheets and be Excel, so we can’t just remove the worksheet capability (which incidentally is effectively what Power BI Desktop is – Excel without worksheets). Therefore, today from a data perspective, Excel files can have data in the data model, worksheets or both. From the Power BI service perspective, the important thing is whether the file contains a data model, as it treats the two cases differently.

Getting Excel Data

From the Power BI service, you click the Get Data button, and then the Get button in the Files tile. You are then presented with one of two dialogs depending on whether you are using a personal workspace, or an app workspace.

Personal workspace

Importing files into a Power BI Personal Workspace

Connecting file-based data to a personal workspace

When importing into a personal workspace, there are 4 possible data sources.

A local file is one that is stored on a file system local to the machine being used. Selecting this option will allow you to work with the Excel file stored in that location, but if the file is being used as a data source (data is in the worksheets), then a Data Gateway will be required for any data refreshes. Power BI will also connect to a file stored in OneDrive, either Personal or Business (through office 365). Finally, the service can work with files stored in any accessible SharePoint site (not simply Team sites as the name would indicate).

App workspace

Importing files into a Power BI App Workspace

Connecting file-based data to an App workspace

When importing into an App workspace, there are 3 possible data sources. The Local File and SharePoint – Team Sites options are precisely the same as when importing into a personal workspace. The difference is the OneDrive – Workspace name option replaces the two other OneDrive options. Choosing this option allows you to work with files stored in the “Group OneDrive”. Since every App workspace is backed by an Office 365 or “Modern” group, it also has access to the SharePoint site for that group. The “Group OneDrive” is the Documents library within that SharePoint site. Therefore, choosing SharePoint – TeamSites and navigating to the Documents library will render the same results in a few more mouse clicks, but also give access to all other document libraries within that site.

Connect vs Import

Once you navigate to the Excel file that you want to work with, you select it, and click connect. You will then be presented with two options for the file, Import or Connect.

This choice dictates how the file is brought into the Power BI service. The structure of the file determines exactly what is brought in to the service in both cases.

Connect

Clicking the Connect button allows Power BI to connect to and work with the Excel file in place. The workbook is displayed as an Excel workbook in full fidelity in the Power BI interface using Excel Online. The file itself is shown in the Workbooks section in the Power BI interface, and it stands alone from other Power BI elements (except that regions of it can be pinned to a dashboard). Connecting to an Excel report will not create a Power BI Dataset, Report, or Dashboard. All operations, including refresh (see below) are controlled through the workbook.

At no point is the file moved, or “brought in” to the Power BI service. If the file is being stored in SharePoint, or OneDrive, anything done to the file in the Power BI service will be visible to anyone with access to the file itself, whether they are a Power BI user or not. This includes refresh, which will be discussed further below, but the important part to remember here is that if the data in the connected file is refreshed through the Power BI service, and it is being stored in SharePoint (or OneDrive), all users will be able to see updated data the next time that they open the file.

Connecting to an Excel file behaves the same way whether the file contains a data model or not, but the file must contain a data model in order to be refreshed by the Power BI service.

Connected Excel file within Power BI

Connected Excel file within Power BI

Import

Importing an Excel file behaves totally differently from connecting to it. When an Excel file is imported, it is treated as a data source to Power BI, and the assets within that file are brought into the Power BI service. Subsequent changes to the source file are not immediately reflected within the Power BI service, but are retrieved through the refresh process.

The way that the assets are brought into the service depends very much on the structure of the file, specifically whether it contains a data model or not. If the file does not contain a data model, then Power BI will use the data contained in the Excel worksheets to construct a new one. This is similar to what happens when a CSV file is imported into the service. If the file does contain a data model, then the worksheet data is imported, and that data model is brought into the service as-is. One important exception to this is if worksheet data uses the same query as an existing model, the worksheet data is ignored, and the data model is brought in as-is. This is important because Excel’s Power Pivot editor can be used to edit the model, creating calculated columns, calculated measures and relationships prior to import. The model that is automatically created when the file does not contain a model has no editing capabilities.

When an Excel file with a data model is imported, the data model (imported or created) is added to datasets, and a link to the dataset is added to the default dashboard for the workspace. If no default dashboard exists, one will be created. A report can then be authored in the service. If the workbook contains any PowerView reports, these will be converted to native Power BI reports and added to the service as well. Any embedded 3D maps are not brought in.

Imported Excel File showing calculated measures

Imported Excel File showing calculated measures

Refresh

Data refresh options, and behavior depend on both the Get Data choice (connect or import) and the structure of the Excel file.

Connected Workbooks

If the workbook is connected to the service, and it does not contain a data model, it cannot be refreshed. This is true even if the worksheets in the workbook contain data from Power Query queries. This is the only scenario that does not support refresh in any way.

If the workbook contains a data model refresh is supported. The interesting part is that refresh will be triggered not only for the data model itself, but for any worksheets that have Power Queries as a data source. Therefore, a workaround to the lack of refresh support for a worksheet with no data model is to add a blank data model.

For refresh to work, the data source must be available to the Power BI service. This means that the source must be available in the cloud or registered on an available On-Premises Data Gateway.

The important thing to note about connected workbooks is that the refresh options that are performed on them are permanent – refreshed data is stored with the workbook. This means that if the connected workbook is stored in SharePoint, or shared through OneDrive, updated data is available to all users with access regardless of whether they are Power BI users.

Imported Workbooks

Refresh options for imported workbooks are slightly more complicated. As mentioned above data is either imported from the worksheets, a data model imported into the service or both.

If data was imported from worksheets, then the Excel file is the data source from the standpoint of Power BI. If the file is stored in SharePoint or OneDrive, it will automatically be refreshed every hour by default. This means that changes to the underlying Excel file will be reflected back in the Power BI service within an hour. This feature can be disabled, but it is not possible to change to hourly schedule, nor precisely when it will occur.

Refresh options for workbooks in OneDrive/SharePoint

Refresh options for workbooks in OneDrive/SharePoint

If the file is stored on a file system, it can be scheduled more granularly, but you will need to connect to it through an On-Premises Data Gateway.

If the file contained a data model that was imported into the service, then the original source of data for that data model (the query) is what the Power BI service will refresh from (NOT the Excel file itself). In this case, the refresh options are the same as with most other Power BI data sources – Excel is taken out of the picture completely, and any changes to the source Excel file will not be reflected into the service. The exception to this is if the file had both a data model, and worksheet data that was imported.

In the case of an Excel with both a data model and worksheet data, both cases above will apply. The workbook is used as a data source for the table that was created by Power BI on import, and the original data model’s source is updated independently. This means that changes to the worksheet data are reflected in the Power BI service when refreshed, but any model changes to the original Excel file are not. Both the OneDrive and regular refresh schedules are used for imported files of this type.

Refresh options for a combined data source

Refresh options for a combined data source

The following table summarizes the refresh options available for file structure and connection type.

File Structure

Get Data option

Connect

Import

Worksheet data None Refresh from worksheet
Data model only Refresh from model source Refresh from model source
Data model plus worksheet data Refresh from model source and worksheet source Refresh from model source and worksheet

Summary

Both Excel and Power BI are powerful tools in their own rights, and the decision to use one does not preclude using the other and in fact there are many good reasons for doing so. Bringing refreshability to Excel files stored in SharePoint is just one of them. It is however important to understand how it all works in order to get the maximum impact.

Schedule Data Refresh for SSAS Connected Excel Workbooks with PowerPivot for SharePoint

Using Excel Services, SharePoint users have been able to share workbooks that are connected to back end data since SharePoint 2007. Typically, the connection is made to SQL Server, or to Analysis services although a wide variety of sources are available. It’s also possible to publish individual components from these workbooks anywhere within the site collection through the Excel Web Access web part. Users can navigate to a dashboard page that contains all sorts of elements including an Excel chart that is connected to back end data. Well, to be precise, it was connected to back end data, the last time the workbook was saved. The workbook itself can be refreshed, but only manually.

When you open an Excel workbook in a browser through Excel services, by default, you’ll see the visualizations and any stored data in precisely the way that the workbook was when it was last saved. If you need to see more up to date data, you can select “Refresh Connections”. If (and sometimes that’s a big if) the server and connections are set up properly, the server will fetch updated data and update the workbook.

 This works well enough, but the problem is that when you, or anyone else opens the workbook again, they’ll still see the old version of the workbook, and will need to manually refresh the date again. In addition, any visualizations published elsewhere on a dashboard will also continue to show old data unless manually refreshed. If the amount of data is significant, this poses a serious performance issue to the server(s). There’s also a significant usability impact in that it’s a pretty big ask of an end user to have them constantly hitting a refresh button.

To get around this issue, one option is to set the refresh options in the data connections of the workbook. Excel Services respects these options. There are two settings that we need to be aware of, periodic refresh, and refresh on open. Connection properties can be accessed within the Excel client by selecting the Data tab, choosing Connections, then highlighting the connection in question and selecting Properties.

Periodic refresh will allow the workbook to be automatically refreshed in the background while it is opened in the browser. This can be useful when the source data is changing frequently. Refresh on opening will have the greatest impact in our scenario, as it will automatically refresh the data in the workbook whenever the file is opened. This will also work with published objects (Excel Web Access web parts) – every time that the web part is opened, the data will be automatically refreshed. This solves the usability problem above because the user no longer needs to manually update the data. However, it does not affect the server load problem.

Due to the fact that the data and visualizations retain the state that they had when the workbook was last saved, it also affects search. When the search indexer runs, it will only index the data that is saved in the workbook. It has no means of refreshing the data. Finally, in addition to the load imposed on the servers by constant refreshes, if the quantity of data being refreshed is large, users can experience significant lags when loading the file. This obviously introduces another usability option. While the refresh options in Excel are helpful, they don’t fully solve the problem. What is needed is a way to automatically open the file for editing, refresh the data, and resave it to SharePoint.

If you have ever used Power Pivot for SharePoint, you know that it can do exactly that. Power Pivot for SharePoint contains two primary elements – a specialized instance of SQL Server Analysis Services that allows users to interact with workbooks that contain embedded PowerPivot models, and a SharePoint service application that among other things, keeps those embedded models refreshed. Using the PowerPivot Gallery (enabled when PowerPivot for SharePoint is installed), you can configure a workbook’s refresh options by clicking on the icon in the Gallery view, or by selecting “Manage PowerPivot Data Refresh” in the simple All Documents view.

 Data Refresh options in PowerPivot Gallery View

 Data Refresh options in All Documents View

Once configured, the PowerPivot for SharePoint Service will refresh the data model in the workbook on a periodic basis (no more than once per day). The service essentially opens the workbook in edit mode, refreshes all of the data connections, and saves the workbook back to the library. If versioning is enabled, it will be saved as a new version. Unfortunately, if you’re not using a PowerPivot data model, the options are unavailable. In Gallery view, the icons are simply unavailable, and while the option is available in the All Documents view, selecting it results in an error.

On the surface, it would seem that using workbooks with PowerPivot is the only option for keeping large volumes of back-end data up to date in Excel visualizations. However, there is a small loophole that you can take advantage of.

The refresh function in PowerPivot for SharePoint refreshes all of the connections in a workbook. While this option is unavailable if the workbook has no embedded PowerPivot model, when it does, it refreshes ALL of the data connections in the workbook, whether they connect to a model, a back end SSAS server, SQL server or whatever. So therefore, if you want to keep your connected data refreshed, the solution is to add a dummy PowerPivot model to your workbook.

Simply open up the PowerPivot window, import some small amount of data from an external source, and save it. Once saved, the PowerPivot refresh options will appear, and you’ll be able to schedule data refresh for your workbook. You can even deselect the refresh of the source data for your dummy model, and the other connections will work just fine.

Once your workbooks are being updated automatically, your users will be presented with up-to date data on load with no delays, all dashboard visualizations will be up to date and quick to render, and the visible data will be picked up by your search crawler. All will be well with the world.

Power BI as a Product Today

Recently, I have come across several situations where people are confused about where Power BI fits in a solution scenario. There is a fair bit of confusion as to precisely what the product is and what it does. The problem is that Power BI isn’t really a product at all, but instead a collection of different products and services. Adding to the confusion is the fact that some of these products require a Power BI license, while others do not. In fact some of these products are actually embedded in other products.

Power BI is Microsoft’s cloud based Business Intelligence solution billed as “Self service analytics for all of your data”. In reality, it’s a little more than self service, it also is a great solution for team BI as it’s based on Office 365. That’s all well and good, but what is it really? What does it consist of, and how does it work? If you look at the main product site for Power BI, it’s not immediately obvious at what you get when you purchase it, or what you need to run it. This post is an attempt to demystify the product.

To start, let’s break it down by its constituent components. Today Power BI consists of the following parts.

image

Unfortunately, this can be rather confusing from a product perspective. Looking first at the on-premises components, Power Query, Power View, and Power Map are all Excel plug ins. Excel is therefore a prerequisite for Power BI. All of these add ins also require (or in the case of Power Query, support) the embedded xVelocity data model, and therefore Power Pivot is a prerequisite. Power Pivot is included in Excel 2013 (Professional Plus), but it can also be downloaded for free for Excel 2010.

Also included in Excel 2013 is Power View, and, with Office 2013 SP1, Power Map. Power Query is downloaded separately, but is free. This is where much of the confusion arises. Due to the fact that these three add ins are included in the product definition of Power BI, it is often assumed that a Power BI license is required to use them. It is not. These products have a life of their own, and can be fully (or almost fully) used within Excel without any association with a Power BI license.

Power Query contains a few features that will only work with a Power BI tenant, mostly involved around the creation and maintenance of shared queries. Since this is part of the cloud service, this makes complete sense, but none of the other features of the product are in any way reduced in the absence of a license. Power View is enhanced through a Power BI license, but only because this makes Power View reports available within the mobile client(s). Indeed, Power Map has no use whatsoever of a Power BI license. Power Maps cannot be viewed at all within a browser – they are a client side feature only. In my opinion, they shouldn’t even be included under the Power BI umbrella, but that’s just my opinion.

Thus far, I have been talking about the modelling and visualization creation aspects of the tools, but what about pure consumption clients? The whole idea of power BI is that designers can create these models and users can interact with them. The workbooks containing these models are stored within Office 365, so do casual users need a license?

The answer is of course maybe. If these users are going to take advantage of any of the services specifically offered by Power BI, then the answer is yes. For example, any user can open a workbook in a browser in Office 365. However, if they want to interact with that model, by using a slicer, pivot table, etc, and that model is larger than 10 MB, then the answer is yes. Obviously, if the user wants to use the Power Q&A features, then the answer is also yes.

For the record, I don’t like this answer. To my mind, designers and content creators should require a license, but consumers should not. This would greatly encourage adoption of the product, so I do hope for some changes in this area.

So, precisely what do you get when you purchase a Power BI license? These are the things that you will absolutely need a Power BI license for.

  • Opening workbooks in a browser with models larger than 30 MB on Office 365
  • Interacting with (slicers, pivot tables, etc) workbooks in a browser with models larger that 10 MB on Office 365
  • Automatic refresh of on premises data
  • Sharing of Power Query queries
  • Refresh of Power Query queries
  • Power Q&A – Natural language queries
  • Power BI mobile application

and that’s it.

In fact, if you check out my earlier article “Whither Power Pivot for SharePoint”, you’ll see that many of the features of Power BI are already available in Power Pivot for SharePoint.

To my mind, the product “Power BI” should not include the Excel add ins, but only list them as a requirement, much like Excel itself is a requirement. This would help to reduce confusion. The next version of Power will support their inclusion. If you’re interested in this new version, you can sign up for the preview when it’s ready here. I’ll be writing more about that shortly.