SmartPivot–Free Little Excel Add-In for Olap Pivot Tables, Easily create Excel dashboards from olap cubes,Auto Synchronize Excel Pivot Tables filters
December 5, 2010 3 Comments
(update: SmartPivot version 2.3 – a much improved version- has just been published, please visit SmartPivot Product Page for the latest version features including instant search, quick connect , a stunning pivot viewer visualization for Excel tables & many others)
Note: the content below is now outdated
So, following my previous post “Excel 2007/2010 Pivot Tables: Getting detailed/granular table reports from OLAP… in seconds! “ , I’ve decided to test the feature a little further, including it on our little add-on for Excel, DevScope SmartPivot. And doing that also decided to get some public feedback, making this little Excel add-on publicly available. :)
So… what’s SmartPivot? It’s an Excel add-on that targets olap pivot tables & charts, resulted mainly from some requests from our business intelligence customers. Little things like this:
As you can see, not a lot of features. :) We provide our add-on to existing customers, there is not a any kind of roadmap for some a full featured, as in “paid”, product (we also lack the time for it :) ). It’s basically an exercise of “creativity”, trying to find simple, smart, ways to make Excel a better BI tool for handling OLAP browsing. No promises . :)
So, how to use it? First download at http://www.devscope.net/products/SmartPivot and install it.
Then you’ll need an Excel Pivot Table to access SmartPivot features, simple right click and explore the features:
Example: Creating Dashboards with SmartPivot
Start with a pivot table and add some filters
Then select SmartPivot > Create Connected Chart:
The pivot chart is created, properly connected to the filters pivot table, the pivot filters are replicated to the pivot chart:
Now build the chart as usual, let’s try a trend chart by product :
So, let’s repeat this a few more times in order to get some interesting “360º view” style dashboard, adding more connected charts:
Now I’ll make it a little bit larger, with 3 more easily connected charts we get something like this:
Now the fun begins :) , just change the main pivot filter as needed, let’s pick some specific years and geography (time to get local :) and see how’s Portugal doing):
As you see, chart filters are in sync with the top pivot filters. In fact, let’s add some new filters:
The new filters we’re also replicated to the charts below. So that’s it, almost no effort and you have a more useful view of your data directly in Excel! You can save and reopen the file later, change the filters, the behavior is what you would expect, the filters always drive the charts below.
So… now you may be asking… but hey, that’s the point of Excel 2010 slicers!!! :) And you’re partially right… but please note the following:
- The first working draft of this feature dates back to 2009, before Excel 2010 slicers (although it as been rather interesting seeing that slicers use the same, cof, “dirty” trick ;) )
- SmartPivot works with Excel 2007 & Excel 2010, Slicers are Excel 2010 only
- Is it me or there is no really simple way of creating dashboards from OLAP in Excel 2010, even with slicers/powerpivot, without either build a powerpivot model or hacking through all the charts, connections, pivots and slicers manually? If my understanding is right…it’s a mess!!!! (but may be I’m missing something here)
- Try to get Excel slicers working with complex deep 3,4,5 level hierarchies (even the simple example above has uses lots of levels)…
- Excel 2010 has a great search member features, you don’t get that with slicers, but as SmartPivot uses only standard pivot filters, all existing features apply, search included
For example, let’s search some projectors in another sample dashboard:
The filter selection is sent to the dashboard charts :
So, that’s it for the dashboards the part. Now the rather risky *beta* feature of olap table reports, based on my previous post.
Example: Creating “faster” tabular reports from olap data
Let’s say we need a regular table style report, high detail report, from our olap cube. We’ve seen how Excel has some problems handling this type of queries in pivot tables, let’s how we could do this using SmartPivot.
Again, start with a pivot and some filters, and select Smart Pivot > Create Table Report:
Select the columns you want in the report (not the most amazing column select screen I guess, hey, it’s beta :) ). Let’s make it heavy and select a bunch of columns like date, product, sales manager, etc, ie near to drilltrough style of query :
And that’s it, a few moments later we get the report:
Let’s add some sorting (date), formatting, size the columns, etc:
Ok, 74 rows it’s really not that impressive, let’s increase our filter scope a little bit, let’s say Europe & All Products:
So, now that’s more like it, 338,584 rows from contoso_retail cube, in about 20 seconds, getting 11 columns. Kind of cool right? :)
That’s the fun part, and although it’s been interesting to explore this rather “hacky” feature and seeing what it can do, use it with caution. Due to internals of the olap engine it works rather well with standard measures, not so well with calculated measures (don’t expect to get thousands of rows when requesting the calculation engine…).
Anyway it’s been a life saver on some projects, being able to allow users to see data in this way, let they choose the filters,etc. At least it shows that the “for that use reporting services with some hard coded filters and query the dw database directly“ has other strong alternatives.
So the public download listed above allows you to install, test & use this Add-On. As we don’t have any kind of roadmap for it at this point (may be we’ll put parts of it on codeplex, how knows) the public version allows the creating of dashboards up to 4 charts (kind of usual right?) & table reports with up to 8 columns. Anyone who wish to take it to some deep dive testing just drop my a note and I’ll send you a license.
All feedback will be greatly appreciated!
(Related posts & reference material)
Excel 2007/2010 Pivot Tables: Getting detailed/granular table reports from OLAP… in seconds! – Rui Quintino BI –
Richard Lees on BI: Improving Excel’s Cube Performance
Binding an Excel table to the results of an MDX query « Chris Webb’s BI Blog