Excel 2013 Business Intelligence New Features –Deep Dive – II
July 19, 2012 2 Comments
(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)
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)
An interesting feature is the ability to expand/collapse the field list. (Another one that we have in SmartPivot :) )
Because, particularly in large cubes it really helps end users getting a mental map over the information available on the cube:
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.
Cell Context Drill Down
Example: Quick Explore > Drill down by subcategory results in
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
results in: (same total amount, but different drill, cross drill)
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
And you’ll get:
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!
(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:
Just select the date dimension you want to slice by:
And you’ll get a time slider, you can then adjust the levels/zoom to years, months,…
Like a slicer you use the Report Connections option to setup the connections to other pivot items:
And the timeline will then slice the selected pivot charts/pivot tables:
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.