Excel 2013 Business Intelligence New Features –Deep Dive – I

(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)

image6[3]

SmartPivot Latest Version for Excel 2007, 2010, 2013 & PowerPivot

(see my second post here for new features related to pivottable/olap data navigation)

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

image

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!

image

(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.

image

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!

image

 

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.

image

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:

image

image

image

(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)

image

Fully refreshable, it is a regular Excel Connection!

image

image

PowerView

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.

image

PowerView (after asking you to enable the feature) starts in his own Excel sheet. Each powerview dashboard is a new sheet.

 

 

image

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:

image

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

image

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:

image

In PowerPivot, locate the table, then edit the table behavior options selecting the id and image uri  field :

 

image

 

image

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:

image

 

Other Notes

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.

image

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.

image

PowerView and external tabular models

Yes, connecting from Excel to a SSAS tabular instance and using PowerView in Excel is available!

imageimage     image

 

but…

 

Not so good – Still no PowerView for Multidimensional?

image

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? :)

 

Next, new pivot table features for data navigation !

(And I wonder if the excel team has been test driving some of our SmartPivot features lately ;) )

Excel 2013 Business Intelligence New Features –Deep Dive – II

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….

Advertisements

About Rui Quintino
Developer & Software Architect @ DevScope

One Response to Excel 2013 Business Intelligence New Features –Deep Dive – I

  1. Pingback: Excel 2013 and BI | James Serra's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: