One of the biggest new features in Excel 2013 is the ability to import and analyze massive amounts of multi tabular data. However, it’s not really all that new – if you’ve used PowerPivot in the past, you’ll already be familiar with the underlying technology. What’s really new here is that Excel can now work with these large data models without the need of an add-in. The even better news is that Excel Services in SharePoint supports this new data engine. What may be a little unclear is what is necessary to make it all work, and this post is an attempt to clarify this a little bit.
To really understand what’s new, we need to understand what is (or was) available via the 2010 family of products. It’s a bit of a bumpy ride that covers 3 main product groupings, SQL Server, SharePoint, and Office, so hang on, and I’ll do my best to clarify.
Background
When SharePoint Server 2007 was initially released, it contained Excel Services. Among other capabilities, the original Excel Services was a means of surfacing an Excel workbook, or part of a workbook to a user through a browser interface without the requirement to have Excel installed client side. It was relatively limited, and the version of Excel Services that shipped with SharePoint 2010 removed many of those limitations. The Office Web Applications that install alongside of SharePoint bring the ability to edit workbooks directly in the browser.
At approximately the same time that SharePoint 2010 was released, the SQL server team released PowerPivot for Excel. PowerPivot is a free, downloadable add in to Excel that allows Excel users to work with massive amounts of data, and to analyze it in ways previously available only to advanced OLAP systems. The reason that it can accommodate the required data volumes is because it adds a new highly compressible data engine (previously named Vertipaq, now called x-Velocity) directly into the Excel file. These PowerPivot workbooks work perfectly when you have Excel 2010 and the PowerPivot add in installed, but what happens when you want to use Excel Services to share it with your team?
To solve this problem, the SQL Server team also released PowerPivot for SharePoint and included it with SQL Server 2008 R2 (read – it’s not available out of the box with SharePoint 2010). Installing PowerPivot for SharePoint in your SharePoint farm adds a new SharePoint shared service that works with Excel Services to allow those PowerPivot enabled workbooks to be rendered up by the server. These workbooks are “live” so that users can very effectively interact with them, using slicers, etc.
The way that it does this is that it creates a local “runtime” version of Analysis Services on the server that is used to perform all of the necessary analytical calculations. Also, to keep the data fresh, PP for SP also contains a series of timer jobs and configurations that connect to the back end data sources on a periodic basis. If you’re up for it, there’s a very detailed description of the architecture here.
Pre 2013 Usage
Without getting into much detail (like I will below with 2013 ), setting up PowerPivot for SharePoint involves starting with a SharePoint farm that is enabled for Excel Services, adding the PowerPivot for Excel add-on to one or more workstations that will create at least one PowerPivot enabled workbook, and then installing PowerPivot for SharePoint onto a SharePoint server FROM the SQL Server 2008 R2 or 2012 media. After performing some (relative onerous in some cases) configuration steps, The PowerPivot enabled workbooks can be uploaded into a SharePoint library or PowerPivot gallery, and then used by anyone accessing the SharePoint environment. There are quite a few moving parts here including
- Excel 2010
- SharePoint Server
- Excel Services
- PowerPivot for SharePoint
- SQL Server
- Analysis Services
As with any system, the more moving parts that there are, the more opportunity there is for failure. In addition, there are a fairly broad set of skillsets required to make it all work, so any simplification is welcome.
What’s New in 2013
The big news is that the x-Velocity engine that is the underlying power behind PowerPivot is included in Excel 15 without the use of an add-in, which will undoubtedly increase its adoption. This does lead to some practical questions around how this works with SharePoint 2013 and Excel Services. Couple this with the fact that PowerPivot still exists, and the landscape can get pretty confusing.
Excel Services itself can render Excel workbooks with embedded data models, but it’s not possible to interact with them. Clicking on slicers, filters etc. simply won’t work. The workbook can be seen, but it is completely static. Any attempt to interact with it will result in an error like the following.
As with PowerPivot in SharePoint 2010, what is needed is an Analysis Services engine to perform the necessary work. The big difference with 2013 is that the engine no longer needs to be installed on the SharePoint server – you can connect to an Analysis Services server elsewhere on your network, and that server does not need to be part of your SharePoint farm.
That server does, however need to be operating in “SharePoint mode”, and it can be a little unclear as to what that means. In the end what it means is that PowerPivot for SharePoint (available on SQL 2012 media) is installed on a non-SharePoint server. However, if you try to do this using the release versions of SQL Server 2012 (or 2008 R2 for that matter) you will get an error because the SharePoint bits are not present. What is needed is a minimum of SQL Server 2012 SP1, which as of this writing is available as a Community Technology Preview V3, and can be found here. To set this up, run the SQL install on the AS machine, and at the Setup Role prompt choose SQL Server PowerPivot for SharePoint.
Unless you need it, you don’t need to install the relational engine. No, you’re not really using PowerPivot at this point, but it’s the option you need to pick. I suspect that the name may change by the time SQL Server 2012 SP1 is released, but for now, that’s what it is.
Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.
Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.
The options here are pretty limited…what’s your Analysis Services server? Click on Add Server, and add your Analysis Services (PowerPivot for SharePoint) server. Unless you’ve changed it, PowerPivot for SharePoint will have installed in a non default instance named POWERPIVOT, so the server name will take the form SERVERNAMEPOWERPIVOT, as below
You may need to run an iisreset on your SharePoint front end servers, but once this is complete, you’re ready to test. To do that, you’ll need to start up Excel 2013, and import multiple tables from a data source. This triggers creation of the model.
Once imported (it may take a bit), create a pivot table and add an interactive element (a slicer is perfect). Test it out, and if it’s working, save it to a SharePoint document library.
Close Excel, navigate to the library, and click on the workbook. It should open in the browser. Once open, trigger the interactivity element (click the slicer) and if it works, you have everything configured properly.
If you want to have a look under the covers, start SQL Server Management studio, and create an Analysis Services connection to your instance (defined above). You should see a new model created for your workbook.
If it’s not there, make sure that you’ve interacted with the workbook – it doesn’t get created until the first interaction.
Whither PowerPivot?
Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.
On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).
On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.
Configuring PP for SharePoint 2013 is beyond the scope of this post, but I will likely address it in a future post.
Compatibility
With Office 2013 we have the Third major release of PowerPivot, and the data model on the second platform for SharePoint. It’s not likely that anyone will upgrade all of their applications immediately, so the question is, what works where? The good news is that the core engine is relatively unchanged, and models created with any versions of PowerPivot should work well with Excel Services 2013 (and Analysis Services SharePoint mode). Conversely, models created with Excel 2013 whether with or without the PowerPivot 2013 add-in should work with previous versions of SharePoint. I haven’t yet had the chance to try out all of the various permutations and combinations, and would like to hear what your experience is.
Hopefully, this helps clear up some of the confusion around the Excel BI features in SharePoint 2013. Hopefully the naming in the SQL media installation gets cleaned up by release.
Update – July 26 2012 – If you’re interested in trying out any of the tools that I mention below, you can use a pre-built environment that’s been set up on CloudShare. Click here to sign up and access the environment.
Superb article. I’ve been fighting the data model error you mentioned above for two days on a course load for a Business Intelligence course I have to teach in couple of weeks. You have given me an excellent education on how all the pieces are supposed to work and why they are supposed to work. I really appreciate the work you’ve put into this.