Tag: PowerPivot

Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016

Published March 3, 2016

SharePoint has supported PowerPivot enabled workbooks since they were first introduced with the initial PowerPivot for Excel Add-On. This support was initially provided via PowerPivot for SharePoint, which was comprised of two major components. The first component was a special, and mostly hidden instance of SQL Server Analysis Services (SSAS) running in what came to be known as SharePoint mode. The other component was a SharePoint shared service application that provided for the scheduled refresh of workbooks and an administrative dashboard.

Initially, these two components came as a single product, PowerPivot for SharePoint, available on the SQL Server install media. The PowerPivot for SharePoint that shipped with SQL Server 2008 R2, and with 2012 prior to SP1 would install both components on to a SharePoint server. Beginning with SharePoint 2013 and SQL Server 2012 SP1 it became possible to separate the two components, allowing for greater scalability. An entry for each SSAS server running in SharePoint mode simply needed to be added to the Excel Services service application (the “BI Servers”), and they would automatically be used for any embedded PowerPivot models.

With the deprecation of Excel Services in SharePoint 2016, this support moves to Office Online Server 2016 (OOS), and with it, some further decoupling. It is now possible to support the data model with having first installed the PowerPivot for SharePoint service application. Technically, this was possible in SharePoint 2013, but it was far from obvious as to how to make it happen. All that is necessary for PowerPivot workbook support is an Analysis Services server running in what is now called “PowerPivot Mode”.

PowerPivot Mode is that very same specialized version of Analysis Services that exists to support data models that was previously referred to as “SharePoint Mode” in earlier versions. The option to install it is now front and center along with “Tabular Mode” and “Multidimensional and Data Mining mode”. To install a PowerPivot mode Analysis Services server, simply install Analysis Services as normal, and choose “PowerPivot Mode” when you get to the options screen.

The other important thing to note in this dialog box is the inclusion of the computer account for the OOS server(s) as (an) administrator(s). This is necessary because the OOS machines will need to create and tear down data models from Excel workbooks on demand, and the OOS process on these machines run as the Network Service account. Note that this is NOT necessary because of EffectiveUserName as with a regular Tabular Mode SSAS server. EffectiveUserName is not used in PowerPivot mode. It the server gets installed without this account added as administrator, it can be added later via SSMS 2016 in the same manner described in my earlier article regarding Tabular mode SSAS.

Once the PowerPivot mode SSAS server is up and running, the OOS server needs to be configured to use it (If you don’t yet have an operational OOS environment, please see this earlier article to get up and running). As with all other OOS configuration options, this is done through PowerShell. The command to add a PowerPivot mode server is:

New-OfficeWebAppsExcelBIServer –ServerId NAUTILUSSQLBI\SHAREPOINT

In the example above, the name of the PowerPivot mode server is NAUTILUSSQLBI and the instance name is SHAREPOINT. I have found it helpful to use named instances for PowerPivot Mode servers.

Additional servers can be added to the farm in a load balanced configuration by simply rerunning the above command with any additional server ids. This is similar to the way it was done in Excel Services, by adding them to the BI Servers list.

Once this has been put into place, it should be possible to interact with any Excel workbooks that contain a data model. All slicers and pivots should work. This will not, however allow you to schedule data refreshes, or use any other PowerPivot for SharePoint features. For that, you’ll need to add PowerPivot for SharePoint to your SharePoint farm, and that is the topic of an upcoming article.

5 Comments

Rethinking Business Intelligence in SharePoint and SQL Server 2016

Published December 14, 2015

SharePoint 2016 and SQL Server 2016 will both be released in 2016, adding to the changing Business Intelligence landscape already being disrupted by Power BI. Many of them will be incremental, but some are significant architectural changes that require a rethink of how we will approach on premises and cloud based Business Intelligence.

All of the bits to deploy the SharePoint based BI components are now available. With the December 8, 2015 publication of the white paper “Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016” white paper, it’s possible to kick the tires and to come to a few conclusions. I will be posting a number of “how to” posts in the coming weeks, but I felt that it was important to set the context for them first. The Business Intelligence ground has shifted significantly, and this greatly affects the way that we think of, design and use Business Intelligence tools with SharePoint. For the record, there is quite a bit of opinion in here, and I want to make it crystal clear that the opinion is mine, and not stated by Microsoft.

Excel and Excel Services

In August 2015, Microsoft announced that Excel Services would not be a part of SharePoint in 2016, which came as a big shock to the community. Excel has always been one of the main pillars of BI in SharePoint, the other two being SQL Server Reporting Services (SSRS) in SharePoint integrated mode, which we’ll get to below, and PerformancePoint. As I’ve argued before, PerformancePoint, while still included in SharePoint 2016, has been dormant for several versions, and likely doesn’t have much of a future. It’s been exposed to the elements, and has gotten quite rusty. I wouldn’t put much weight on that particular pillar. In this context, Microsoft’s decision to remove Excel Services, (the only BI component delivered by the Office team) seems like a big deal.

In reality, it’s not such a big deal in itself. Microsoft has, for the most part, shifted the functionality of Excel Services to Office Online Server (OOS). I explained in another post that despite its name, OOS is NOT a cloud service, but in reality is the new name for the Office Web Apps server – the server that allows for browser-based editing and viewing of Excel documents. The difference between Excel Services and Office Web Apps Server has always been confusing to users and a configuration headache for administrators using both. I believe that Microsoft’s consolidation of Excel Services and OOS makes a great deal of sense.

BI professionals need to understand the change to options and components with this new model, and they need to understand that the change Microsoft has made does not represent a net loss in functionality. In fact, I think you will see a net gain because users can make changes to workbooks with data connections and embedded data models directly from a browser.

PowerPivot for SharePoint

Microsoft will continue to deliver PowerPivot for SharePoint as a SharePoint Service application, and a special instance of Analysis Service. However, Microsoft has significantly changed the installation process. Previously to install PowerPivot for SharePoint you had a separate installation mode for SQL Server, which would install either SSAS in SharePoint mode alone or SSAS in SharePoint mode along with the PowerPivot for SharePoint bits if you wanted to install it on a SharePoint server. With SQL Server 2016, it’s simply one of the modes for the SSAS install (called PowerPivot mode), and the PP4SP bits are installed separately (always). You’ll also need to perform separate configuration steps to connect the OOS server to the PowerPivot mode instance.


Installing SSAS in PowerPivot mode in SQL Server 2016 CTP 3.1	Installing PowerPivot for SharePoint SQL Server 2014

Microsoft did not make very many changes to PowerPivot for SharePoint 2016. Users will note one big improvement—they can refresh Power Query-based connections. While this improvement is not currently in the preview s, Microsoft has promised this feature for the final release. Until now, PowerPivot for SharePoint could update workbooks with new data, but only if those workbooks contained standard Excel-based or PowerPivot-based connections. If you had used Power Query to import data, you were out of luck. This contrasts sharply with Microsoft’s cloud based Power BI service , which can only use Power Query to import and refresh many data sources. It was impossible to answer the question “which tool should I use for data import” without being aware of the destination platform. Now, it is simple. Use Power Query, and your workbooks will work on all platforms.

When I mention that there aren’t many changes, this includes the PowerPivot Gallery. The Gallery is a specialized SharePoint document library template that allows you to see thumbnails of your Excel and Power View reports, and gives easy access to refresh options and self-service reporting options. As with prior versions of the Gallery, it is delivered as a Silverlight application on a SharePoint view page. As I will discuss later, the Silverlight dependency could be construed as a problem, but it is not necessary to use the Gallery in order to interact with Power Pivot workbooks. You can switch to a more standard library view and still have access to workbook refresh options.

The fact that Power Query refreshes PowerPivot-based and Excel-based connections may be reason alone to update existing PowerPivot for SharePoint installations to the 2016 versions. If you navigate to the Feature pack page for SQL Server 2016 CTP 3.1, you’ll see add-ins for both SharePoint 2016 and 2013, so this will be possible, for SharePoint 2013 at least (and no, you will not need your database server to be SQL 2016 as well). Correspondingly, the SSRS Integrated mode from SQL Server 2016 will work on all SharePoint from 2013 and up (but will need the new 2016 add-in).

Power View

Power View first debuted with SSRS in SQL Server 2012. Microsoft developed Power View to become the future self-service BI reporting tool that SSRS itself never really was. Initially, Power View has very specific requirements to use it, so specific that very few people did. You needed to be running SSRS in SharePoint integrated mode (it wasn’t available in native mode), and it needed to connect to a SQL Server 2012 SSAS tabular mode instance. Once that was set up, you would create a BISM connection file and then use it to launch Power View from a SharePoint Library. When Excel 2013 debuted, it contained a version of Power View that could work with embedded data models, which greatly increased its adoption. In all cases, Power View interaction required Silverlight in order to access it using a browser.

The Silverlight dependency was a clear problem, as it prevented mobile users from working with it, and Silverlight’s “retirement” meant that realistically, no new features would be added. Microsoft addressed this problem fairly quickly in Office 365 with the addition of an HTML5-based rendering engine that would be invoked if the browser machine did not have Silverlight. These HTML 5 enhancements never made it into the on-premises version of SharePoint. While Microsoft initially put a lot of energy into the HTML5 rendering engine, but Microsoft appeared to stop adding new features at about the same time that it achieved feature parity with Silverlight.

This stoppage, combined with recent moves, indicate to me that Power View has no future. I can think of three major developments that lead me to this conclusion, and you can find these developments in Power BI, Excel 2016, and the Office Online Server.

The original Power BI Service, originally introduced in 2012 and retired on Dec 31, 2015, was based on Office 365 and Excel, and leveraged Power View for self-service reporting. Microsoft introduced a new version of the Power BI Service in July 2015; it is a standalone service that connects to Excel among many other sources. The visuals in the new Power BI service are similar to, but not the same as Power View. Realistically, they are the logical evolution of what Power View started, and are based on the D3 JavaScript engine. I think Microsoft could have made it less confusing had they just called these visuals Power View V2, but it is what it is. The open architecture allows Power BI to quickly implement new visualizations, whether they come from Microsoft or from the community. You can see the many new visuals in the Power BI Visuals gallery, most of which have been submitted by the community. When you import models and Power View from a workbook into Power BI, any Power View reports are converted to the new version, and it’s a one-way street. In this environment, Power View is clearly considered a legacy technology.

Microsoft first included Power View in Excel as a ribbon item in 2013. During the test phase of Excel 2016, it disappeared from the ribbon. Power View still exists in the product, and you can add it back to the ribbon as I describe in this article. However, I wonder why Microsoft removed it. In my opinion, the reason is simple. Microsoft wants people to stop using embedded Power View in Excel, and to start using Power BI for self-service reporting. This just makes sense from where I’m standing. Excel has a rich set of native visuals that can connect to embedded data models, and most of the Power View visuals aren’t as mature as these are. Having Power View in Excel never made much sense, except possibly from a usability standpoint. If an analyst wants to live within Excel, they can use Excel Visuals and expose them as an Excel report in Power BI. For self-service reporting, we can connect to an Excel file and use Power BI visuals. There’s simply no longer any need for Power View as an Excel embedded tool.

Finally, let’s look at the new Office Online Server 2016. When OOS renders an Excel workbook that has an embedded Power View report, it will use Silverlight to do so. That’s right… OOS 2016 will require Silverlight for Power View rendering. We know that Microsoft has already updated the Power View rendering engine for HTML5 with some of the visuals for Office 365, so we know that it was possible to do so. The only reason that I can think of that Microsoft didn’t do this entirely through Power View is to discourage people from using it, or the effort was too great for a technology that was being replaced. In addition, now in order to user Power View with OOS, you’ll need to use Kerberos constrained delegation. Microsoft did not require this in the past because Excel Services was running on the same server as SharePoint itself and could pass the User Principal Name through to the backing SSAS server that used EffectiveUserName. Now Kerberos is required for this.

So why doesn’t Microsoft just state that Power View is at end of life? Microsoft didn’t say that, but that was the message I heard at the October 2015 PASS summit. I think that the reason is that so far, Power BI is a cloud service only. In order to replace Power View fully with Power BI, customers need to embrace cloud services to some level, and there are organizations that are still not ready to do this. Very soon, after Microsoft releases SQL Server 2016, SSRS will support direct rendering of PBIX reports. (The file format for Power BI Designer and the new visuals.) At that point, the new visuals, and the new self-service reporting tools will be available on premises, but for now Power View is the only tool that can function in an on-premises only environment. It doesn’t really have a future, but it’s still necessary. It’s certainly not the only technology to exist in this state – both InfoPath and PerformancePoint serve similar roles. Microsoft still supports them and PerformancePoint, while dormant, could come back any time should Microsoft choose to do so.

SQL Server Reporting Services

Microsoft has included SSRS as a core part of the Business Intelligence workload in SharePoint since SharePoint 2003. SQL Server 2005 SP1 introduced SSRS in SharePoint integrated mode, which allowed administrators to replace the web server and storage functions of the SSRS server with that of SharePoint’s, making it easier to administer. With SQL Server 2012, you had the option to deploy SSRS as a SharePoint service application, further simplifying administration and scaling. During this period, the native mode SSRS server was always still available for those that didn’t use SharePoint, but over time, it lagged behind its sibling from a features standpoint. Many people wondered aloud if native mode SSRS had a future at all, and if SharePoint would become a required component. They needn’t have worried.

At the same time, in the past few years we’ve seen a marked shift in the way that Microsoft has positioned SharePoint, from being at the center of everything to being more a set of services. The first hint of this was the new app (now add-in) model for SharePoint, and more recently with the wholesale shifting of services, of which the Excel Services change is a prime example. This shift, combined with a renewal of emphasis on SSRS for structured reporting is cause for re-evaluation.

At the PASS summit, Microsoft rolled out its reporting roadmap. It’s comprehensive, well thought out, and exciting. I’ve pointed out before that it doesn’t include the name “PerformancePoint”, but you know what else doesn’t feature prominently? SharePoint. Microsoft committed to SharePoint integration but they offered few details.

With SQL Server 2016, customers will still deploy SSRS through both native and SharePoint integrated modes. However, for the first time, the feature set will be significantly greater in native mode at least on initial release. With the roadmap, Microsoft defined four report types:

Paginated Reports – I call these operational, or structured reports. These are “classic” SSRS reports.
Interactive Reports – These reports are built with Power BI Desktop, and will run in SSRS and Power BI Web. I call these “Analytical reports” and this role would previously been performed by Power View.
Mobile Reports – These reports are aimed at mobile devices, and are what was previously known as Datazen.
Analytical Reports and Charts – Excel workbooks.

SSRS 2016 will be the delivery mechanism for 3 of these 4 report types, but only in native mode initially. Integrated mode will support these report types one way or another down the road, but we just don’t know when. Microsoft is investing in quite a few new areas in SSRS, and it’s worthwhile to break down exactly which of the new features will be available in the two different Reporting Services modes at release. For a definition of these features, please refer to Microsoft’s roadmap announcement.

Feature	Native Mode	SharePoint Integrated Mode
Paginated Reports	X	X
Interactive Reports	X*
Mobile Reports	X
New Reporting Portal	X
New visuals	X	X
HTML5 rendering	X	X
Pin and link SSRS visuals to Power BI dashboards	X

*Shortly after initial release

It’s pretty clear that the tables have turned. Power View reports are now the only thing that is uniquely offered in SharePoint integrated mode. If you have SharePoint and you decide to use native mode SSRS, no functionality is lost – you can still use the SSRS web part in SharePoint for report rendering and dashboards. Reports will be stored in the SSRS server, and you’ll need to set up security separately. On the plus side, you can leave these tasks to a Reporting admin, who will not need to know how the SharePoint security model works. The biggest issue that I can see is that while integrated mode allows you to work with a single authentication provider, the SSRS native mode server requires its own, making a direct connection with it necessary, at design time at least.

These downsides aside, this shift to a focus on native mode fits with what is happening with Excel on the Office side. The two become peers that work together, as opposed to being dependent on one another. The increased functionality makes native mode compelling, even if you are running a SharePoint farm. If you are creating a new BI environment and you want to take advantage of the new SSRS features, and/or you are looking to the future for your BI investments, my recommendation is now to provision a native mode SSRS server whether or not you have SharePoint in most cases. If you already have an investment in integrated mode Reporting Services, don’t panic. Patience will be a virtue here. You will gain all of the new visuals and appearances immediately, and the other pieces will come in over time. Microsoft has not yet clearly stated the roadmap for integrated mode beyond the release of 2016.

Summary

So, to summarize, all of this represents a shift away from SharePoint as a dependency and to it as an interface option. Instead of these tools working “on” SharePoint, moving forward they will work “with” it. Overall:

Power View can now be considered a legacy product.

Power View was the future of the past. The future is now Power BI. If it helps, think of the visual elements in Power BI as being Power View V2, which in effect they are. If you’re thinking about using Power View to build a report, please consider Power BI Desktop. If it’s not possible, the good news is that if you use Excel to build it, you will be able to easily import it into Power BI Desktop – it has a migration path forward, and on premises PBIX support is also on the way.
SQL Server Reporting Services should be deployed in Native mode

Whether or not you have a SharePoint farm, native mode SSRS is the way to go for a new deployment, even if you’re not yet ready for SQL Server 2016 SSRS. The reason for this is that migrating reports between the two modes is not simple.
Excel Reporting is alive and well

Excel is still well supported and Microsoft is investing in it. It is the tool for analysts and model builders and is easily portable to Power BI and to SSAS. The removal of Excel services in SharePoint is simply and architectural shift, not a functional one, and Excel reports are very well supported in Power BI.
Power BI and Power BI Desktop are the preferred tools for self-service reporting

Self-service reporting is clearly the domain of Power BI. The legacy options are still available for current on-premises customers, but if you want to future-proof your investments, look to Power BI.

6 Comments

Analysis Services Power Pivot Mode in SQL Server 2016

Published June 24, 2015

It is fairly well known that ever since SQL Server 2012, users have been able to install SQL Server Analysis Services (SSAS) in one of two modes – either “Multidimensional or Data Mining Mode” or “Tabular Mode”. Multidimensional is the traditional SSAS mode that supports OLAP cubes, and Tabular is the new engine that supports in memory xVelocity (or PowerPivot) models. Any given instance can only run in one of the two modes, but multiple instances/modes can exist on the same server. SQL Server 2016 (available as CTP2 as of this writing) is adding a third mode to SSAS – PowerPivot Mode.

What’s PowerPivot mode all about? Well, to start with, it’s actually not new – it’s actually older than Tabular mode. It has been available since SQL Server 2008 R2, but was never installed the same way as the other two modes, and had a couple of other different names. To my mind, this has led to a great deal of confusion that this change aims to fix. What it truly is, is a special instance of Tabular mode that exists solely to support PowerPivot for SharePoint. Up until now, this instance was installed during the installation of SQL Server Power Pivot for SharePoint, as can be seen in the screen below.

From the initial release of SQL Server 2008 R2 until (but not including) SQL Server 2012 SP1, this component needed to be installed on a SharePoint server, which is to say a server that had the basic SharePoint bits installed and had been joined to the farm. This requirement led to more than a few instances of SharePoint being installed on SQL Servers, but I digress. The correct way place to install this was on a SharePoint server that was already part of the farm. When this option was selected, what actually got installed was two things. First, a special instance of Analysis Services (it didn’t really have a name at the time), and a SharePoint service application that handled automatic refresh among other things.

The problem with this is that it forced the SharePoint server in question to also perform all of the Analysis Services functions. It wasn’t possible to scale out the analysis capabilities separately from the SharePoint server(s) themselves. This changed with the combination of SharePoint 2013 and SQL Server 2012 SP1. SharePoint Server 2013 allows for Excel Services to utilize one or more instances of what was now known as Analysis Services SharePoint Mode. This can be configure through Excel Services in the Data Model configuration options.

One can add any number of SSAS servers to the service allowing you to scale out in a limitless fashion. These SSAS servers must be running in “SharePoint mode”. The problem is that it’s not immediately obvious as to how to run an SSAS server in SharePoint Mode. The way that this is done is by installing “PowerPivot for SharePoint” on a server. With SQL Server 2012 SP1, this installation no longer requires a SharePoint server. However, if it is installed on a SharePoint server its behaviour is different. When installed on a server without SharePoint, a standalone instance of Analysis Services SharePoint mode will be installed. You can then connect to it using the Excel Services configuration shown above. However, when installed on a server with SharePoint, both the SSAS SP mode instance and the service application will be installed (as with prior versions).

While this behaviour makes sense, it’s certainly not intuitively obvious as to what’s going on. The Data Model Settings in SSAS only refer to registering “SQL Server Analysis Services”, and makes no mention of SharePoint mode. Regular SSAS servers will not work for this capability. On the SQL Side, it’s also not obvious that “PowerPivot for SharePoint” is the installation option for SSAS SharePoint mode, or that there are different behaviours when installed on farm joined servers or not. Finally, the name SSAS SharePoint mode isn’t particularly descriptive – the server in question is there exclusive to support PowerPivot for SharePoint.

Given all of this, the new installation option in SQL Server 2016 becomes clear, and should go a long way to help clear up confusion. PowerPivot mode is that same specific instance of SSAS Tabular mode that was previously referred to as “Analysis Services SharePoint Mode”. To complete the picture, the language in the PowerPivot for SharePoint configuration tool has also been updated for clarity. It may not be completely consistent, but it’s easier to understand.

While none of this represents any major shifts in functionality or capability, it does help to understand the various components of the overall solution. Hopefully the language in Excel Services in SharePoint 2016 will also be updated accordingly.

1 Comment

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

Published May 30, 2015

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.

Always use the internal Data Model (PowerPivot) when importing data for analysis.
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.
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.
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
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.
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.

21 Comments

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

Published March 24, 2015

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.

3 Comments