SmartPivot–Free Little Excel Add-In for Olap Pivot Tables, Easily create Excel dashboards from olap cubes,Auto Synchronize Excel Pivot Tables filters

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

image

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

 

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:

image

Easily create graphical dashboards over olap cubes

-Excel 2007 or 2010!
-Create connected charts
-Automatic filter configuration
-Filter sync
-(no, no slicers in the screenshot :) )

image

Synchronize filters across pivot tables

-Creates connections between different pivot table filters
-Auto-Update

image

Create tabular reports from olap cubes **BETA**

-Following my last post  on this
-Works great with standard, not calculated, measures
-Free ad-hoc filtering & column selection

 

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:

 

image

 

Example: Creating Dashboards with SmartPivot

Start with a pivot table and add some filters

 

image

Then select SmartPivot > Create Connected Chart:

 

image

 

The pivot chart is created, properly connected to the filters pivot table, the pivot filters are replicated to the pivot chart:

 

image

 

Now build the chart as usual, let’s try a trend chart by  product :

 

image

 

 

So, let’s repeat this a few more times in order to get some interesting “360º view” style dashboard, adding more connected charts:

 

image

 

Now I’ll make it a little bit larger, with 3 more easily connected charts we get something like this:

 

image

 

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

 

 

image

 

As you see, chart filters are in sync with the top pivot filters. In fact, let’s add some new filters:

 

image

 

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:

image

 

The filter selection is sent to the dashboard charts :

 

image

 

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:

image

 

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 :

image

 

And that’s it, a few moments later we get the report:

image

 

Let’s add some sorting (date), formatting, size the columns, etc:

image

 

Ok, 74 rows it’s really not that impressive, let’s increase our filter scope  a little bit, let’s say Europe & All Products:

 

image

 

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 –
http://biresort.net/blogs/rquintino/archive/2010/07/25/excel-2007-2010-pivot-tables-getting-detailed-granular-table-reports-from-olap.aspx

Richard Lees on BI: Improving Excel’s Cube Performance
http://richardlees.blogspot.com/2010/04/improving-excels-cube-performance.html

Binding an Excel table to the results of an MDX query « Chris Webb’s BI Blog
http://cwebbbi.wordpress.com/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/

 

 

 

RQ