As you may already have read, the first public preview for Office 2013 is now available and there’s lots of new BI functionality in there – see here for an overview. Here’s a quick summary of what the two really important changes are:
- PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.
- Power View has also been integrated into Excel: Power View reports become a new type of sheet inside a workbook, and you can use it against data held in the integrated xVelocity/PowerPivot engine; I’m not clear yet whether it will work on a SSAS 2012 Tabular model (and at some point a SSAS Multidimensional model, once support for DAX on Multidimensional models arrives) but I hope it does. No more need to moan about Power View being tied to Sharepoint!
There are a whole bunch of other BI-related changes in Excel which I’ll try to summarise in another post soon (stuff like the suggestions for charts and PivotTables, flash fill, timeline slicer). However I think that the two changes above represent a master-stroke on the part of Microsoft: they make Excel 2013 a serious contender in the self-service BI tool stakes. Certainly, other vendors will be quick to point out the features they have and that Excel doesn’t, and dedicated BI vendors will always be able to add new features faster and more frequently than Excel, but that’s not the point. It won’t happen overnight but at some point every company will upgrade to Office 2013 and when they do, all users will have a BI tool on their desktops which is vastly more capable than Excel today and will be good enough for the majority of BI scenarios – which means that the need to even look at third party tools will disappear.
UPDATE A few clarifications:
- The PowerPivot addin, while still an addin, comes bundled with Excel – there’s no separate download
- As the comments below show, and I can confirm, Power View does work with SSAS 2012 Tabular models
I’ll be posting more details throughout the day on Twitter as I play with the new preview, and will post something more substantial here later
Chris…were you able to get PowerPivot working?
Hmm, no, not now for some reason…
ok..what you have to do is go to FILE–>OPTIONS–>Add-Ins. At the bottom hit the drop down next to Manage: . Then select COM Add-Ins and click GO. From there you can enable PowerPivot. You have to shut Excel down and reopen.
A funny thing is that it picks up your prior install of the Office 2010 PowerPivot add-in. So in Excel 2013 you will see two PowerPivot tabs. The second one is the new one.
Also, PowerView will not connect to any outside source; only internal sources (i.e. data in a tab, PowerPivot) This is because PowerView in SharePoint is the “server” version of PowerView whereas PowerView in Excel 2013 is the local end-user version…
Frank, thanks for your solution to enable Power Pivot.
Power View works for me with external data from a tabular mode SSAS instance (multidimensional cubes still not possible to connect to). How to do: DATA -> From Other Sources -> From Analysis Services, chose your tabular mode instance and when Excel askes you about what to do with the data, you take the Power View option.
Thanks Robert….didn’t see that option the first time.
Integrated means (to me) programmable. Do these features have the ability to be manipulated like any other functionality of Excel with VBA?
I’m not quite sure. Since xVelocity is now the engine behind all data import in Excel, when I tried to record a macro that seemed to work. Changes made in the PowerPivot tab weren’t recorded though. I may be missing something though.
Yes , Object Model in VBA is available. Check out ALT+F11 and the object model explorer.
Apparently, Excel 2013 now has the “Model Object”. See: http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15). However, no online documentation yet.
“I’m not quite sure. Since xVelocity is now the engine behind all data import in Excel,”
I don’t see how this can be possible. I can import data into a standard Excel table, so what would xVelocity be doing in this case? Also, I can import into a standard Excel PivotTable…but can’t use any DAX functions…which then must require the PowerPivot add-in.
Hmm, I think there’s more going on here than I understood when I said that. It seems like in some cases when you import data it goes directly into the worksheet, in others it goes into the native xVelocity store, which is (I gather) called the Excel Model. However what is the case is that xVelocity is now built into Excel and the PowerPivot addin is only necessary for defining relationships and managing measures – PowerPivot no longer stores the data itself.
I’ll try to find out what happens when – from what I can see, when you import a single table from SQL Server it doesn’t go into the Excel Model, but when you import multiple tables from the Data tab that does go into the Excel Model and you do see it in the PowerPivot window subsequently.
Chris,
What’s actually happening is the following:
1) If you choose to import the data into one or more tables, then xVelocity isn’t involved.
2) If you select a single table to import into a PivotTable, there is an option to make it part of the data model. If you choose not to make it part of the data model, then xVelocity isn’t involved. It’s the same standard PivotTable that we’ve created in the past on import. Therefore, you can add calculated fields and calculated items from the PivotTable tools Analyze tab, but you can’t create DAX measures. On the other hand, if you choose to make the PivotTable part of the data model, the PivotTable becomes an xVelocity table – you can’t add calculated fields and calculated items from the Analyze menu, but you can create DAX measures, as one would expect.
3) If you select more than one table to import into a PivotTable, xVelocity is immediately invoked. The option to make the PivotTable a part of the data model is greyed out. This must be so because the standard PivotTable can be created from only a single table.
You’re correct that xVelocity is divorced from PowerPivot, and built into Excel. Also, relationships can be created without PowerPivot (from the Relationships button in the Data tab). Without PowerPivot, you’re quite limited though. You can’t create DAX measures, mark a table as date, specify sort columns, create hierarchies, perspectives, and all of the other stuff you can do through the PowerPivot window. PowerPivot is now simply a UI to administer the data model.
I might do a post on this topic because I think that even experienced Excel users will be confused by this new data model thing.
Yes, it would be good if you could write this up – thanks!
Any chance we can use other Excel functions like XIRR in DAX Measures?
Not sure, I’ll check.
Vidas Matelis claims to DAX function to calculate XIRR, but he has never posted it.
Actually, Vidas has posted it here: http://portfolioslicer.com/portfolio-slicer/technical-info/calculated-measures
However he’s run into a performance issue with PowerPivot which won’t be fixed until the next CU of 2012, so I guess that’s why he hasn’t publicised it more.
Thanks!
My XIRR implementation is very “crude” – as PowerPivot does not have recursion, I simple calculate XIRR value multiple times and this way I can find value that matches required condition (value closest to 0). But as Chris said – I hit “nested IFs” issue and I am hoping that this issue will be resolved in next SQL Server 2012 CU. Currently my XIRR implementation works for basic calculations, but I cannot add more code to check conditions when XIRR should come back with special values, as extra IFs substantially increase memory consumption.
Very impressed with Excel 2013 MS has been busy!
Can Excel Power View be deployed to SharePoint?
Note; Unfortunately, Power View doesn’t display my Hierarchies from a BISM tabular model as it does PowerPivot. So cannot drill up and down one of the best features – hope its added\fixed.
Mapping does work with BISM tabular – nice.
Excel Power View reports can be viewed in Sharepoint, yes – see http://cwebbbi.wordpress.com/2012/07/18/building-a-simple-bi-solution-in-excel-2013-part-2/
Excellent! – missed you post on Excel Power View deployment to PowerPoint.
As far as the BISM Tabular Power View Drill down perhaps you need SQL 2012 SP1 (CTP3) to get it to work.