Excel 2013 Business Intelligence New Features –Deep Dive – II

(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

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

So, we’ve seen PowerView in Excel in my previous post,  now for the more “traditional” PivotTable Excel 2013 new features. For now focusing on multidimensional based pivot tables (very probably all apply to tabular models also).

New fields layout

There is a new fields layout. Some changes, minor improvements, the same annoying  2013 upper case style… and still lacking frequently requested & obvious features. Search for example (a feature that we have in SmartPivot and it’s extremely useful for large & complex cubes)

 

image

Expand/Collapse Fields

An interesting feature is the ability to expand/collapse the field list. (Another one that we have in SmartPivot :) )

image

Because, particularly in large cubes it really helps end users getting a mental map over the information available on the cube:

image

ok, now for the cool new features

Pivot Cell& Item Level Quick Explore Actions

Both on cell values & items (members), the Quick Explore allows to drill down/cross drill or get an immediate trend chart.

image

Cell Context Drill Down

Example: Quick Explore > Drill down by subcategory results in

image

Note that it’s not a typical drill down, it isolated the value (9.359.102,62) in the same pivot and then drill down to bikes subcategories.

Cell context cross drill

Example: Quick Explore > Drill down by customer/geography

image

results in: (same total amount, but different drill, cross drill)

image

A rather usefull feature, in fact we also have in SmartPivot :) (called detail by feature), although we preferred to always create a new pivot table instead on changing the source one. And yes, I can confirm from several users feedback  that it is a very valuable feature!

Cell context Chart Trend

Now, this is a very interesting feature that we (honestly!) were planning to test anytime soon with some of our Excel addons users. The fact is that values are rarely static, there’s frequently a context, a trend to a value that is of critical relevance. This feature helps! it shows the value trend to a user, automaticaly. Just select the cell value > Quick Explore > Trend Chart

image

And you’ll get:

image

For BI developers or power users that chart can seem very easy to setup, but I can assure  that for many users it is not. Also remember that you didn’t have to change you main pivot table, nor pass specific filters to the chart pivot. All the context was copied from the cell coordinates on the pivot to the chart.

Another very interesting thing to note, is that unlike Excel 2010, PivotCharts don’t seem to need a PivotTable to support them. So no more hidden sheets for PivotChart data! The PivotChart lives now on its own. Way better!

image

image

(no hidden sheets & chart data source is the connection, not hidden pivot table)

A new kind of Slicer- TimeLine

There is also a new kind of slicer, the TimeLine. Pretty straightforward to setup:

image

Just select the date dimension you want to slice by:

image

And you’ll get a time slider, you can then adjust the levels/zoom to years, months,…

image

Example years:

image

Like a slicer you use the Report Connections option to setup the connections to other pivot items:

image

image

And the timeline will then slice the selected pivot charts/pivot tables:

image

 

And that’s it for today! My next post will take a more “holistic” view on all these new Excel 2013 BI features. Particularly, and if anyone is listening…. :) what can still (realistically) be improved until the RTM? And what can be improved after the RTM using vsto and addons.

For now, I’m very impressed with some possibilities  (PowerView without event opening any kind of BI modeler like PowerPivot), but I am also very concerned with some hard questions: will PV support multidimensional? will both PowerView and DataModel/PowerPivot be finally available to Excel VBA/VSTO? Because not being the case… we will be  locked to MS RTM features only and that will discard plenty of very interesting scenarios & improvements.

Hard questions… let’s see.

Take care,

 

Rui

Advertisements

About Rui Quintino
Developer & Software Architect @ DevScope

2 Responses to Excel 2013 Business Intelligence New Features –Deep Dive – II

  1. David Hager says:

    The Model Object is already present, but difficult to use do to a current lack of documentation. And.., some people are listening, but very few :().

  2. 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: