Office 2013, Office 365 Editions and BI Features

Reposted from Chris Webb's blog with the author's permission.

By now you're probably aware that Office 2013 is in the process of being officially released, and that Office 365 is a very hot topic. You've probably also read lots of blog posts by me and other writers talking about the cool new BI functionality in Office 2013 and Office 365. But which editions of Office 2013 and Office 365 include the BI functionality, and how does Office 365 match up to plain old non-subscription Office 2013 for BI? It's surprisingly hard to find out the answers.

For regular, non-subscription, Office 2013 on the desktop you need Office Professional Plus to use the PowerPivot addin or to use Power View in Excel. However there's an important distinction to make: the xVelocity engine is now natively integrated into Excel 2013, and this functionality is called the Excel Data Model and is available in all desktop editions of Excel. You only need the PowerPivot addin, and therefore Professional Plus, if you want to use the PowerPivot Window to modify and extend your model (for example by adding calculated columns or KPIs). So even if you're not using Professional Plus you can still do some quite impressive BI stuff with PivotTables etc. On the server, the only edition of Sharepoint 2013 that has any BI functionality is Enterprise Edition; there's no BI functionality in Foundation or Standard Editions.

[For those of you thinking of upgrading from Excel 2010 PowerPivot to Office 2013, Marco has all the details on compatibility of PowerPivot workbooks across different versions here.]

Office RT, which runs on Windows RT, has several limitations on its BI functionality: there's no PowerPivot, Power View or Excel Data Model. Luckily, Kasper has summarised what it does do in a blog post here, so I won't repeat what he says.

Moving on to 2013 functionality in Office 365, and specifically BI in Sharepoint Online, things get more complicated. Although feature support information for Office 365 is on Technet here, the best place to start is Andrew Connell's blog post and corresponding feature matrix that is viewable through (appropriately enough) the Excel Web App. The feature matrix makes it very easy to filter Office 365 features by workload so you only see the BI-related ones:

As you can see, the short answer is that you need either Office 365 E3 or E4, or SharePoint Online Plan 2 to get BI functionality. The E3 and E4 plans are also the only plans to include subscriptions to the desktop versions of Office Professional Plus, and they allow it to be installed on up to 5 machines per user. The other thing you'll notice is that PerformancePoint is not available at all in Office 365 (read into that what you will); it is of course available in Sharepoint 2013 Enterprise Edition on-premises.

There are other functionality differences between Sharepoint Online in Office 365 and on-premises Sharepoint too. The details are here, but the important ones are:

• At least for the moment, Excel workbooks can be no larger than 10MB
• The Excel Data Model will only refresh successfully if it sources data from the workbook itself; no external data sources are supported (though again I'd be surprised if that restriction isn't lifted in the future)
• There is no PowerPivot for Sharepoint functionality such as the Gallery, usage reporting or scheduled data refresh.

These are quite significant restrictions, it's true, but if you are a purely self-service BI shop and you just want to use Sharepoint Online to publish PivotTable or Power View reports that don't need to be refreshed (or can be refreshed manually on the desktop and then uploaded) then this functionality should be sufficient. This is the kind of scenario I showed here, and I think a lot of customers with no existing BI will still be impressed with it; obviously it's a problem if you want to do any kind of corporate BI.

BUT. At the time of writing the Enterprise plans for Office 365 haven't been fully updated for Office 2013 functionality, so all this BI functionality isn't actually available yet to most subscribers. This means that the desktop versions of Office you can download are still 2010 and not 2013; online, while you can get the latest Sharepoint features if you're part of the Office 365 Preview, if you're currently an Office 365 subscriber you're probably still on Sharepoint 2010. The official line on when the upgrade to 2013 functionality will take place is a bit vague - it will take place "in the course of 2013" - and there seem to be a few upset customers out there (see here for example). February 27th seems to be a significant date.

Finally, apart from Office 365 it's also possible to view Excel workbooks via SkyDrive. However pretty much no BI functionality is available when you do this: no Excel Data Model, no external connections, no Power View, just the ability to view (and not alter) PivotTables. These restrictions seem to be more or less the same if you use just the Office Web Apps server on-premises without Sharepoint 2013 - see the relevant table here for details.

In summary: my head hurts! All these editions and licences. it would be nice if it was less complicated.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .