Tag Archives: Power Query

Using Power BI to Report on Lookup Fields in SharePoint

This post is the sixth and final post in a series exploring Power BI and complex data types in SharePoint. This post examines the various options in Power BI for working with lookup fields. The previous posts are:

A lookup field in SharePoint contains values looked up from another list in the same SharePoint site. Strictly speaking, the field contains only the ID from the item in the source list, and the value(s) is/are looked up whenever the field is displayed. The lookup field can also be used to display multiple field values from the target list items.

Defining a lookup field in SharePoint

The List

Consider the following list that contains a lookup field named “Neighbourhood”:

The lookup field neighbourhood in the SharePoint view

We can see from the screenshot above that the text value for neighbourhood is displayed in the view, although only the row identifier is stored in the column. We will be able to get both values and more if desired in a Power BI report, but first we need to build the report using Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Loading data rom the Listings list

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. In this case, we can remove the ContentTypeId column and everything to the right of it, with two important exceptions. We want to keep the “FieldValuesAsText” in addition to the special “Neighbourhood” column at the far right of the columns, as we’ll be needing them for our options below.

Using FieldValuesAsText

Examining our columns, we can see that amongst the simpler text fields, we don’t have a “Neighbourhood” column, but instead, a “NeighbourhoodId” column, with numeric values. We do have a Neighbourhood column further off to the right, but it doesn’t display simple text (we’ll come back to this shortly). If we simply want the text value of our lookup target, we can use the “FieldValuesAsText” column quickly.

Scrolling right in the Query editor view, we find the “FieldValuesAsText” column. The record values represent a one to one relationship with the text values of the list row, so we can click on the column expander at the right of the column title. From there we can extract the text value of any column, including our lookup field, “Neighbourhood”.

Extracting text using FieldValuesAsText

With “Neighbourhood” checked, and nothing else, including the “Use original column name..” option, we can click OK, and the “FieldNameAsText” column is replaced by a new column, “Neighbourhood” that contains the text values for Neighbourhood.

If this value is all that is needed, then this is a totally valid approach, and we can move on to report building. However, this is only one way to achieve this goal. If more information is needed, then other methods may be more suitable.

Retrieving all Lookup Field Values from the Extended Column

Given that the lookup target item is a SharePoint list item, all that item’s properties are available to us. We can access them from the extended column set up for the field. In our case, the original “Neighbourhood” column is the extended column. We can expand this column by selecting its column expander.

Extracting field values using the extended column

We then deselect all of the columns except the ones that we want to use in the report. The fields available are the fields available in the target list. In our case, we select the “Title” field, as it is the one being looked up. We can however retrieve any of the fields that we need from the target list.

Keep in mind that “Title” in our example is a simple text field, so no further action is necessary. The retrieved fields can be complex (person, MMS, etc), but keep in mind that if a complex field type is retrieved, it will need to be transformed just like any from the list in question.

The field name in the target list may not adequately describe its function for the report. In our case, “Title” actually means “Neighbourhood” in this report. It’s a good idea to rename it.

Finally, if multiple field values are to be retrieved, the data model could grow significantly. This is because the values for every field are repeated in every row of data. Given that the original lookup column adds a measure of relational behaviour to SharePoint, using this relationship is the most efficient way to work with this data. Power Query allows us to do just that.

Working with Related Tables

To work with related tables, we need not only the original data table (in our case, “Listings”) but also the table for the lookup list itself. To do this, from the Query Editor, we create a new data source like the one created above for “Listings”, but instead we select the lookup list (“Neighbourhoods”).

Once imported, we can remove any extraneous columns, and then set the data type for the ID field to be “Whole Number”.

Setting the ID column to whole number

We also need to set the data type of the “NeighbourhoodId” column in the Listings table to “Whole Number”. Once these options have been set, we are ready to work with the data model and the report. We select “Close and Apply” from the ribbon to load the data into the model.

Once loaded, we launch the relationship builder in the design pane in order to establish the relationship between the two tables.

Power BI relationship editor with default relationship defined

We can see that Power BI has already detected a relationship. However, it is not correct. The model designer assumes that because both tables contain an “id” column, then they must be related. However, the true relationship is between the “Id” column in our “Neighbourhoods” table, and the “NeighbourhoodId” column in our “Listings” table.

We must first delete the detected relationship by selecting the connector between the two tables and pressing “Delete”. We can then create the proper relationship by dropping one of our related columns onto the other. Once this is created, we also need to ensure that the “Cross filter direction” is set to “Both”. We do this by double clicking on the relationship connector and selecting the appropriate option.

Setting cross filter direction

Power BI relationship editor with correct relationship defined

Once the relationship has been established, we can return to the design pane and construct a rudimentary report. We drag a few fields from Listings into a table, create a calculated measure for the number of listings, and we add the “Title” field (renamed to “Neighbourhood”) to the canvas separately. Once we set the visualization for “Neighbourhood” to a slicer, we can easily slice our listings data by neighbourhood.

Slicing report with the values from a lookup field

We can therefore see that there are several options for accessing data for a lookup field, ranging from simple to complex. The trade-off for simplicity is flexibility. Which approach used will depend on your requirements but storing the lookup table separately is the most efficient as the data is only stored once and referenced.

The Road Ahead for SQL Server Reporting Services and Power BI Report Server

Power BI has garnered a lot of attention in the past few years as the “place to be” for Microsoft business intelligence. Power BI is of course Microsoft’s cloud platform for dashboarding and reporting. On premises, Microsoft’s reporting platform has traditionally been SQL Server Reporting Services (SSRS) and is now joined by Power BI Reporting Server (PBIRS). While there are differences between the two in the way that they are licensed and distributed, the main technical difference is that PBIRS includes SSRS, plus the ability to render both Power BI reports (pbix) and Excel based reports through Excel Online.

PBIRS is therefore able to render all four report types as defined by Microsoft:

Paginated

RDL (classic SSRS style reports)

Interactive

PBIX (Power BI Desktop)

Mobile

RDLX or PBIX (Datazen and Power BI Desktop)

Analytical

XLSX (Excel)

Given that PBIRS is the on-premises reporting platform, and Power BI is the cloud platform, they should theoretically be able to perform the same task. However, this isn’t the case. Currently, there is no way to render paginated (or as I like to refer to them, operational) reports in the cloud. PBIRS can render all report types, but today, the Power BI Service cannot.

Jason Himmelstein and I recently hosted Riccardo Muti, Microsoft Group Program Manager for Power BI Report Server on our BI Focal podcast (you can hear the whole show here) and we asked him a few questions about the future of paginated reports.

While it has been hinted at before, Riccardo explicitly stated that the ability to render paginated reports in the cloud was not only on the roadmap, but it was actively being worked on. This will complete the picture for BI in both the cloud and on premises. This is important because I the ideal world, the choice of on-prem, cloud, or hybrid should be related to the data in question, not the available features. No more standing up an Azure virtual machine to be able to get printable, paginated structured reports.

Riccardo sees this service rolling out in three distinct phases. Initially for the preview, RDL reports will be able to connect to cloud data sources like Azure SQL Database, Azure Data Warehouse, Azure Analysis Services, and Power BI data models. The next stage will leverage the On Premises Data Gateway in order to connect to on-premises data like SQL Server, Oracle, Teradata and more. The next phase will begin to leverage Power Query to connect to the world of data sources that Power Query Offers.

With paginated reports adopting Power Query, and Excel having moved to Power Query as a default, it is hard to argue with making an investment in learning Power Query. It all seems to be coming together.

One of the top requested features for SSRS on-premises for year has been the ability to authenticate to it with claims-based authentication. This will be the native authentication mechanism for paginated reports in the service. When this is combined with the fact that these reports will ultimately leverage Power Query, and the vast number of data connection options that it brings, it’s not hard to wonder when these features will be coming down to the on-premises product. When asked about this, Riccardo confirmed that this is certainly the vision for the product.

The future looks bright for both cloud based, and on-premises reporting.

Using Power BI to Report on Multi-Value SharePoint Fields

Power BI has direct support for reporting on SharePoint lists and documents whether SharePoint is on-premises or in the cloud. Getting at your data is relatively straightforward if you know the URL, but as I’ve written about before, SharePoint data can be idiosyncratic. Text and number fields work right away, but more complex data types require a little more thought, and this is certainly true of multi value fields.

Multi value fields are really SharePoint’s way of approximating the behavior of a one-to-many relationship of a relational database. SharePoint can store list-based data, and even though it has the concept of a lookup field, it is not a relational database. Chances are however that if we’re reporting on this type of field, we want it to behave as though it were. Fortunately, Power BI gives us several options for doing this.

The List

Consider the following list that contains a multi-value choice field named Amenities:

In the view, the different values are flattened, and displayed as a single value using a comma to separate them. How do we go about reporting on this data in Power BI? There’s no right answer, as it depends on the report requirements, but there are several options. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with one important exception We want to keep the FieldValuesAsText column (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the multi-value column, Amenities. The simplest way to represent this data is as a string of comma separated values, which is the same way it is shown in a SharePoint view. It is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting a Simple Text Field

If our report is to show all the amenities associated with a listing, then a simple text string will do the job. Examining the data in the Query Editor, you will notice that there are no values displayed for Amenity, the way that there is for simpler field types like “Sold” or “Asking Price”. Instead, each cell contains a “List Value”. Each of these Lists contains the Amenities values for each record and clicking on one will extract those values for that one item. However, of we want to extract the values for all items, so we need to expand the column by clicking on the expand icon in the column header.

We are then given two options, “Expand to New Rows” or “Extract Value”. To extract the values into a single value, we select “Extract Values”

We are then given the opportunity to choose a delimiter, and after that the values in the column are replace with simple text values.

Another way to accomplish this same thing is to use the FieldValuesAsText column (referenced above). The fields in this column contain a single record containing the textual values for all the complex data types in a SharePoint list. If you are doing reporting with SharePoint data in Power BI, FieldValuesAsText is your friend. To use it, we just click the expand icon in its column header, deselect all of the columns that we don’t need (which in our case is everything except Amenities) and click OK. We are returned the contents of the list in a comma separated string for each item.

At this point, we can report on the data.

We click on Close and Apply in the ribbon to return to the report canvas. We add a table visual, and add some of the fields like address, street number, and Amenities to it. You’ll see that the table looks much like it would in a SharePoint list. Next, we add a slicer to the report page, and use one of the other columns, like City. Clicking on a city will filter the table by that city as expected. Now, if we want to filter by Amenity, we can add the Amenity column to the slicer, but it won’t behave the way we want it to. Each combination of amenity values will be represented as a single value. We are unable to slice on a single value like “Garage”.

Flattened multi value field as slicer

To use discrete values for our multi-value field, we need to do a little more work in the Get Data (Power Query) interface.

Extracting Discrete Values from a Multi-Value Field

We follow the initial steps from the “Simple Text Value” section above, but when the expand icon is selected, we select “Expand to new rows” and NOT “Extract values”. When this is done, each row will be copied to accommodate all of the individual Amenity values. If there were 3 Amenity values, we wind up with three rows of identical values, but with each of the values for Amenity.

Expanding to new rows

We can now go to the design surface and add a table, and a slicer that uses the multi value column, and now we can filter on discrete values. This may be sufficient in many cases. However, if we create a measure that based on an aggregate value for the table (like COUNTROWS or SUM), the value will not be correct if there are no slicer values. It works with all of the duplicated rows.

Incorrect number of listings

Again, this approach may work in many situations, but for ultimate flexibility, we need a true one-to-many relationship. Luckily, Power Query gives us a few simple tools to do this.

Creating a One-to-Many Relationship

Again, we follow the initial steps from the “Simple Text Value” section above, but do not click on the expand icon in the Amenities column. A One-to-many relationship requires at least two tables, so now is the point where we need to add a second one. We do so by copying the first. We right click on the query in the Queries pane and select “Duplicate”.

This creates a second query identical to our first, which will load into another table. We can then give the new query a better name, like “Amenities” in our case. Next, we select the Id, and the Amenities columns, right click and select “Remove Other Columns”, and we are left with only the ID and Amenities columns. Now we select the expand icon and select “Expand to New Rows”.

We now go back to the original query and remove the Amenities column. The result is two tables, Listings and Amenities, with Listings containing the bulk of the information. We then click on Close and Apply in the ribbon to load the data into the model, and the relationships icon to take is to the relationship builder. We need to establish a relationship between the two tables, and we do so by clicking on the Id field in one table and dragging it to the Id field of the other. Finally, double click on the relationship line between the tables, and be sure to select “Both” for Cross Filter Direction – that way Amenity values can filter List values. If “Both” is selected, the directional arrow will point two ways.

Now, the same visuals from above can be added using the Amenity column from the second table as a slicer, and everything should work as expected. Listings will be filtered by the value, if no amenities are selected, the correct number of listings will be shown In the data card, and no duplication will appear in the table.

SharePoint multi-value columns are an attempt to replicate the capability of a relational database. With a few simple steps, Power BI can take SharePoint data, normalize it, and analyze it as if it were a true relational database.

A Simplified Method of Working with SharePoint Data in Power BI

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.

Before After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.

Using Excel With External Data – What’s the Right Tool?

Excel has been used with external data for… well, as long as I’ve been using Excel. So why would anyone bother to write a blog post about this given that the capability is so mature? In recent years, Excel has adopted a number of new, and frankly better mechanisms for working with external data, while retaining the old. Given that there are now multiple tools in Excel for working with external data, it’s not always clear as to which one is the best, and unfortunately there is no single tool that wins over all, although I believe that that will be the case soon.

The answer, as always is, “it depends”. When it depends, the important thing is to understand the strengths and weaknesses of each approach. With that said, let’s have a look at all of the options.

ODC Connections

ODC (Office Data Connections) are the traditional method of accessing data in Excel. You can create or reuse an ODC connection from the Data tab in the Excel ribbon.

When using an ODC connection, you establish a connection with a data source, form some sort of query and import the resultant data directly into the Excel workbook. From there, the data can be manipulated and shaped in order to support whatever the end user is trying to do. The one exception to this behaviour is the connection to SQL Server Analysis Services (SSAS). When a connection is made to SSAS, only the connection is created. No data is returned until an analysis is performed (through a pivot table, chart etc), and then only the query results are retrieved.

When the workbook using an ODC connection is saved, the data is saved within it. In the case of an SSAS connected workbook, the results of the last analysis are saved along with it. For small amounts of data, this is just fine, but any large analysis is bound to quickly run into the data limits in Excel which is 1,048,576 rows by 16,384 columns in Excel 2013. In addition such a file is very large and extremely cumbersome to work with, but even as such, Excel has been the primary tool of choice for business analysts for years.

Data loaded into the workbook can be refreshed on demand, but it can also be altered, shaped, mashed up, and as is too often the case, grow stale. Workbooks such as these have become known as “spreadmarts” and are the scourge of IT and business alike. With these spreadmarts, we have multiple versions of the same data being proliferated, and it becomes harder to discern which data is most accurate/current, not to mention the governance implications.

SharePoint has provided a way to mitigate some of the concerns with these connections. SharePoint itself supports ODC connections, and therefore users can access these workbooks stored within SharePoint and it also allows them to refresh data from the source either on demand or on open. A single point of storage along with a measure of oversight and browser access helps to restore a modicum of sanity to an out of control spreadmart environment, but the core issues remain.

In order to help with the core issues, Microsoft introduced PowerPivot in 2009.

PowerPivot Connections

Created in PowerPivot

PowerPivot was originally (and still is) an add-in to Excel 2010, and is a built in add-in to Excel 2013. PowerPivot allows for the analysis of massive amounts of data within Excel, limited only by the memory available to the user’s machine (assuming a 64 bit version). It does this by highly compressing data in memory using columnar compression. The end result is that literally hundreds of millions of rows of data can be analyzed efficiently from within Excel.

You can see that compression at work by comparing the same data imported into an Excel workbook directly, and into a PowerPivot model with a workbook. The following two files contain election data, and represent the maximum number of rows that Excel can handle directly (1,048,576) and 25 columns.

Getting data into the model was originally (and still can be) a completely separate process from bringing it into Excel. PowerPivot has its own data import mechanism, accessed from the Power Pivot window itself. First, click on the PowerPivot tab in Excel and then click manage. If you don’t have a PowerPivot tab, you will need to enable the add-in. If you don’t have the add-in, you have an earlier version of Excel – you’ll need to download it.

Once the PowerPivot window opens, the “Get External Data” option is on the ribbon.

Once the appropriate data source is selected and configured, data will be loaded directly into the data model – there is no option to import that data into a worksheet. Once the data is in, pivot tables and pivot charts can be added to the workbook that connect to the data model much like when creating an ODC connection to Analysis Services. In fact, it’s pretty much exactly like connecting to Analysis services, except that the AS process is running on the workstation.

Created in Excel

PowerPivot, and more importantly the tabular data model was included in Excel 2013. With that addition, Microsoft added a few features to make the process of getting data into the data model a little easier for users that were a little less tech savvy, and may be uncomfortable working with a separate PowerPivot window. That’s actually part of the thinking in leaving the PowerPivot add-on turned off by default.

When a user creates an ODC connection as outlined above, there are a couple of new options in Excel 2013. First, the “Select Table” dialog has a new checkbox – “Enable selection of multiple tables”.

When this option is selected, more than one table from the data source can be selected simultaneously, but more importantly, the data will automatically be sent to the data model in addition to any other import destinations.

Even if the multiple selection option wasn’t chosen, the next dialog in the import process, “Import Data” also has a new check box – “Add this data to the Data Model”.

Its purpose is pretty self-explanatory. It should be noted that if you choose this option, and also choose “Only Create Connection”, the data will ONLY be added to the model, nowhere else in the workbook. This is functionally equivalent to doing the import from the PowerPivot window, without enabling the add-in.

Power Query Connections

When Power BI was originally announced, Power Query was also announced and included as a component. This was very much a marketing distinction, as Power Query exists in its own right, and does not require a Power BI license to use. It is available as an add-on to both Excel 2010 and 2013, and will be included with Excel 2016.

Power Query brings some Extract, Transform and Load (ETL) muscle to the Excel data acquisition story. Data can be not only imported and filtered, but also transformed with Power Query and its powerful M language. Power Query brings many features to the table, but this article is focused on its use as a data acquisition tool.

To use Power Query, it must first be downloaded and installed. Once installed, it is available from the Power Query tab (Excel 2010 and 2013).

Or from the data tab, New Query (Excel 2016)

Once the desired data source is selected, the query can be edited, or loaded into either the workbook, the data model, or both simultaneously. To load without editing the query, the load option at the bottom of the import dialog is selected.

Selecting “Load To” will allow you to select the destination for the data – the workbook, the model or both. Selecting Load will import the data to the default destination, which is by default the workbook. Given the fact that the workbook is an inefficient destination for data, I always recommend that you change their default settings for Power Query.

To do so, select Options from the Power Query tab (2010 and 2013) or the New Query button (2016), click the Data Load section, and then specify your default settings.

Data Refresh Options

In almost every case when external data is analyzed, it will need to be refreshed on a periodic basis. Within the Excel Client, this is simple enough – click on the data tab, and then the Refresh All button, or refresh a specific connection. This works no matter what method was used to import the data in the first place. Excel data connections can also be configured to refresh automatically every time the workbook is opened, or on a periodic basis in the background.

However, workbooks can also be used in a browser through Office Web Apps and Excel Services (SharePoint and Office 365) or as a data source for Power BI dashboards. In these cases the workbooks need to be refreshed automatically in order that the consuming users will see the most up to data when the workbooks are opened. The tricky part is that not all of the connection types listed above are supported by all of the servers or services. Let’s dive in to what works with what.

SharePoint with Excel Services

Excel Services first shipped with SharePoint 2007, is a part of 2007, 2010, and will be included with 2016. From the beginning, Excel Services allowed browser users to view and interact with Excel workbooks, including workbooks that were connected to back end data. The connection type supported by Excel Services is ODC, and ODC only.

Excel Services has no mechanism for maintaining data refresh. However, the data connection refresh options are supported which means that the workbook can be automatically refreshed when opened, or on a scheduled basis (every xxx minutes in the background). Unfortunately, this can come with a significant performance penalty, and once refreshed it is only in memory. The workbook in the library is not updated. The data in the workbook can only be changed by editing the workbook in the client, refreshing it, and re-saving it

Workbooks with embedded data models (PowerPivot) can be opened in the browser, but any attempt to interact with the model (selecting a filter, slicer, etc) will result in an error unless PowerPivot for SharePoint has been configured.

SharePoint with Excel Services and PowerPivot for SharePoint

PowerPivot for SharePoint is a combination of a SharePoint Service application and Analysis Services SharePoint mode. When installed, it allows workbooks that have embedded PowerPivot data models to be interacted with through a browser. The way that it works is that when such a workbook is initially interacted, the embedded model is automatically “promoted” to the Analysis Services instance, and a connection is made with it, thus allowing the consuming user to work with it in the same manner as with a SSAS connected workbook,

The PowerPivot for SharePoint service application runs on a SharePoint server and allows for individual workbooks to be automatically refreshed on a scheduled basis. The schedule can be no more granular than once per day, but the actual data within the model on disk is updated, along with any Excel visualizations connected to it.

When the refresh process runs, it is the functional equivalent of editing the file in the client, selecting refresh all, and saving it back to the library. However, there is one significant difference. The Excel client will refresh all connection types, but the PowerPivot for SharePoint process does not understand Power Query connections. It can only handle those created through the Excel or PowerPivot interfaces.

Power Pivot for SharePoint ships on SQL Server media, and this limitation is still true as of SQL Server 2014. At the Ignite 2015 conference in Chicago, one of the promised enhancements was Power Query support in the SharePoint 2016 timeframe.

Office 365

Office 365, or more precisely, SharePoint Online supports Excel workbooks with ODC connections and PowerPivot embedded models in a browser. These workbooks can even be refreshed if the data source is online (SQL Azure), but they cannot be refreshed automatically. In addition, only ODC and PowerPivot connections are supported for manual refresh. Power Query connections require Power BI for Office 365. In addition, Office 365 imposes a 30 MB model size limit – beyond that, the Excel client must be used. In short, the Office 365 data refresh options are very limited.

Power BI for Office 365

Power BI for Office brings the ability to automatically refresh workbooks with embedded data models. Data sources can be on premises or in the cloud. On premises refresh is achieved through the use of the Data Management Gateway. It also raises Office 365’s model size limit from 30 MB to 250 MB. With Power BI for Office 365 both manual and automatic refreshes can be performed for both PowerPivot and Power Query connections, however Power Pivot connections are currently restricted to SQL Server and Oracle only.

The automatic refresh of ODC connections is not supported. A workbook must contain a data model in order to be enabled for Power BI.

Power BI Dashboards

Power BI Dashboards is a new service, allowing users to design dashboards without necessarily having Office 365 or even Excel. It is currently in preview form, so anything said here is subject to change. It is fundamentally based on the data model and it works with Excel files as a data source currently, and it is promised to use Excel as a report source as well. The service has the ability to automatically refresh the underlying Excel files on a periodic basis more frequent than daily.

In order for a workbook to be refreshed by Power BI, it must (at present) be stored in a OneDrive or OneDrive for Business container. It also must utilize either a PowerPivot, or a Power Query connection. At present, the data source must also be cloud based (ie SQL Azure) but on premises connectivity has been promised.

SQL Server Analysis Services

Another consideration, while not a platform for workbooks is SQL Server Analysis Services (SSAS). Excel can be used to design and build a data model, and that data model can at any time be imported into SSAS. As of version 2014, SSAS fully supports all connection types for import – ODC, PowerPivot and Power Query. Once a data model has been imported into SSAS, it can be refreshed on a schedule as often as desired, and you can connect to it with Excel, and share it in SharePoint. You can also connect to it in Power BI Dashboards through the SSAS connector. From both a flexibility and power standpoint, this is the best option, but it does require additional resources and complexity.

Refresh Compatibility Summary

For convenience, the table below summarizes the refresh options for the different connection types.

 

ODC

PowerPivot

Power Query

Excel Client

M

M

M

SharePoint/Excel Services

M

SharePoint/Excel Services/PP4SP

M

A

SQL Server Analysis Services Import

A

A

A

Office 365

M

M

Office 365 with Power BI

A*

A

Power BI Dashboards

A

A

M – Manual refresh

A – Both Manual and Automatic Refresh

* only limited data sources

 

The Right Tool

I started out above by saying that the selection of import tool would depend on circumstances, and that is certainly true. However, based on the capabilities and the restrictions of each, I believe that a few rules of thumb can be derived. As always, these will change over time as technology evolves.

  1. Always use the internal Data Model (PowerPivot) when importing data for analysis.

     

  2. Power Query is the future – use it wherever possible

    All of Microsoft’s energies around ETL and data import are going into Power Query. Power Query is core to Power BI, and announcements at the Ignite Conference indicate that Power Query is being added to both SQL Server Integration Services and to SQL Server Reporting Services. Keep in mind that we have been discussing only the data retrieval side of Power Query – it has a full set of ETL capabilities as well, which should also be considered.

  3. PowerPivot or ODC Connections must be used on premises

    PowerPivot for SharePoint does not support Power Query for refresh. This means that you MUST use PowerPivot connections for workbooks with embedded models. If you are already using SSAS, use an ODC connection within Excel.

  4. Power Query or PowerPivot must be used for cloud BI.

    PowerPivot connections will work for a few limited cases, but more Power Query support is being added constantly. Where possible, invest in Power Query

  5. If on-premises, consider importing your models into SSAS

    SSAS already supports Power Query. If, instead of using PowerPivot for SharePoint, Analysts build their models using Excel and Power Query, they can be “promoted” into SSAS. All that is then required is to connect a new workbook to the SSAS server with an ODC connection for end users. The Power Query workbooks can be used in the cloud, and the SSAS connector in Power BI Dashboard can directly use the SSAS models created.

  6. Choose wisely. Changing the connection type often requires rebuilding the data model, which in many cases is no small feat.

In summary, when importing data into Excel, the preferred destination is the tabular model, and to import data into that model, Power Query is the preferred choice. The only exception to this is on premises deployments. In these environments, consideration should be given to connecting to a SSAS server, and failing that, PowerPivot imports are the best option.