Excel 2013 Business Intelligence New Features –Deep Dive – I
July 19, 2012 1 Comment
(update: SmartPivot for Excel version 2.3 – a much improved version- has just been published, please visit SmartPivot Product Page for the latest version Excel BI features including instant search, quick connect , a stunning pivot viewer visualization for Excel tables & many others)
Well, full installing an Office preview on my work laptop, with so many ongoing data projects & addons targeting Excel 2007/2010, only a few days after its release is certainly not a “treat” I’m known for. :) It usually takes a few… months. In the meantime I usually count on amazing guys like Chris to keep me posted on the news. :) (amazing posts by the way- go read them if you are into Excel 2013 BI! )
Anyway, a calculated risk: 1) my past experiences with Excel betas side by side had always been useful, 2) on our 3 hours train trip to Lisbon today my friend & colleague Jose Antonio Silva gently shared his win8 tablet with the full office 2013 preview installed, and what a fun & quick trip it was. From the usual netflix odata feeds, to PowerView @Excel, in a few minutes (and with the help of our PowerView guru Cristiano Xavier :) ) we already had crafted some amazing dashboards. Directly over basic Excel tables, no complexity whatsoever, beside constantly struggling to resize dashboard items in a tablet!
That got me craving for more, needing to take a much deeper look on some features. And so, no other way than really installing the new preview.
Installing Office 2013 Preview
Amazingly, not much to say beside it was unbelievably easy& fast, in a few minutes, without restart whatsoever, or closing any Office 2010 App, I was already working on Excel 2013 (I guess that’s why its called a “streamed” install, flawless). Never seen before.
Minor issue was that I was never asked what specific features to install and in a few minutes,to my surprise…, I already had the full preview available. (Previous betas would not support Outlook side by side….. I’ll soon now if this issue remains I guess… )
Starting Excel 2013
First notes, all my addins loaded just fine, from our SmartPivot to MasterData,WWT, all seem good to go. Great!
(by the way, please count me in on “the lets forget the top level menus fixed upper case shall we” ! ?:) ) What the hell… I’m also known for my lack of branding/design skills, but that feels pretty annoying to me honestly.
Due to the number of addins I have enabled (a few I guess), and with the new large upper case titles, Excel can’t get enough space to show them all… and exhibits an absurdly annoying arrow that moves the ribbon left or right. Horrible!
OData & Azure Data Market are now native providers in Excel
Probably as a side effect of the powerpivot/xvelocity runtime now fully integrated into Excel, we now can use odata feeds like we would use any other database table, it will bind easily to a regular excel Table Query.
Beside supporting powerpivot this opens up several new & interesting scenarios, not specifically related to powerpivot or powerview . As http odata servers are very easy to code & deploy. I bet that few people could build a native oledb provider to use in Excel, well, an odata server/endpoint is a no brainer. Lots of tools, samples & sdks.
End users will now have no problem in importing odata feeds to regular Excel tables, way more simple than having to open powerpivot and being forced to use pivot tables/aggregated values.
For example, using the netflix odata api to list movies in the 80s:
(note the “add this data to the data model”, that reads something like: add this data table to the internal powerpivot-datamodel- table collection)
And that’s it, our title list is available in Excel (and as we can see above we can even create a powerview report without having to go through the intermediate excel table)
Fully refreshable, it is a regular Excel Connection!
Having that excel table, the obvious next step was to load it directly into the new integrated PowerView engine. And I was pleased to see that this scenario is perfectly supported! No need to create a powerpivot model, not even opening powerpivot (that is even disabled by default like Chris already noted).
Click on your Excel table, then Insert > PowerView, and that’s it.
PowerView (after asking you to enable the feature) starts in his own Excel sheet. Each powerview dashboard is a new sheet.
How cool is that? Just a table. :). So, if you already know PowerView from SQL/SharePoint, it’s exactly the same, use the field list, drag & drop & chart/table/tile views to quickly build amazing and interactive dashboards. Example:
And as our odata feed includes picture urls, we can configure the table behavior in PowerPivot to use these images. For that you’ll need first to enable PowerPivot 2013 version on Excel. File > Options > Addins > Com Addins> Microsoft Office PowerPivot for Excel 2013
Now, you can open PowerPivot to tune the data model that Excel automatically created for you, note that you should open the 2013 version, it looks like this:
In PowerPivot, locate the table, then edit the table behavior options selecting the id and image uri field :
Save & back to our PowerView Excel dashboard, see that we can now use tiles & image fields to improve our initial dashboard with some visual context:
Duplicating PowerView Excel sheets seems to be disabled, but you can use select all in a powerview dashboard, then create another one, and paste the contents. It should duplicate the dashboards with only the global fields missing from the original.
The “data model” connection is not editable/viewable. Would rather prefer that it could be at least read only :). it’s always interesting looking under the hood.
PowerView and external tabular models
Yes, connecting from Excel to a SSAS tabular instance and using PowerView in Excel is available!
Not so good – Still no PowerView for Multidimensional?
As far as I can see there’s still no way of connecting PowerView to SSAS multidimensional cubes. I have an unpleasant gut feeling tickling me that all hope is lost on this one (sad for me, for end users that made ssas a top selling olap platform, sad for bi in general) .
We have been frequently told that MS is committed to this. Well, I think there’s no room for commitment any more, only time to get the damn feature working! Or not, and for once and for all, put an end to this never ending thread!
By the way… if there really was anything resembling something like a business intelligence “semantic” model, on top of both tabular/multidimensional, this should be easy to pull off. Unfortunately, regarding the so called “bism”, I don’t believe that’s the case.
Update 21-07-2012 – SSAS MVP & MVG (most valuable guy :) ), Boyan Penev, shares my concern on his latest post: “http://www.bp-msbi.com/2012/07/is-excel-2013-power-view-hurting-ssas/”
Now, can other MS BI/SSAS MVGs join us please? :)
(And I wonder if the excel team has been test driving some of our SmartPivot features lately ;) )
Now, what I’m really afraid to do… is closing my trusted outlook 2010…. will it open again? I guess I’ll find out tomorrow….