Shattering old myths (again): analysis services cubes can’t handle tabular reports with #excel? adhoc tabular reports won’t work with cubes & excel? -definitely busted with #smartpivot
June 22, 2012 2 Comments
But tired of seeing and feeling our users struggle (and SSAS users in general who would certainly appreciate an improved SSAS !) to get these scenarios to work with cubes & excel we decided to go for a “second round” and do whatever we could to help. And so we did. :)
The create table report feature
So, as I’ve shown in a previous post there is a way to enable this scenario on Excel & SSAS (multidimensional), surprisingly usable & extremely fast in most cases. That’s why we included in our SmartPivot Excel AddIn, a feature called Create Table Report.
Just connect to an SSAS cube, throw some filters, select the option and you will be able to build a rather nice tabular report with a much better performance (usually minutes in a regular pivot table against seconds or less with this feature).
Also a bit surprisingly, we came to know that there are still SmartPivot 1.0 users that use SmartPivot specifically for this feature.
The new & improved Create Table Report Feature – Like a Drillthrough
This week we took it a step further, in a way that, like the Detail Cell Value By feature, we can start building a table report from any cell value on the pivot:
Just select a cell value, select Create Table Report and the cell context will be cloned to the new adhoc table report (much like the SmartPivot Detail Cell Value action).
It will select the measure you had selected on the source pivot table. And it will even filter for related attributes only.
Search or click the fields you need, click OK, and usually in a few seconds you get the needed table report!
Activating the total row on the table, we can easily confirm the total we previously saw on the source pivot for France/Clothing/CY2008:
Even better, the selected fields are saved and stored with the associated measure you first selected (per user local preferences). So the next time you do the same action with that measure, your previously selected fields will be the default. Ex, lets close Excel and reopen the file, but detail a different cell:
The previously used fields for that measure will be selected by default:
Table report & total check:
Can this be used as an alternative to drilltrough? Well, probably it will do the trick in a much better & usable way, because:
- You can customize the fields you want
- It will work with calculated measures (although I would not advise pulling millions of rows in these cases…)
- Saving the last fields used for a measure will allow you to reuse the most frequent detail fields
- You can later change filters as you wish, even adding fields. The table report will refresh accordingly. You can’t do that with a drillthough ssas excel table.
The user experience is painless, fluid, like it should be. No need for fixed reporting services reports or SQL queries to the relational database. It – makes – sense. (users usually like that!)
What about performance?
Like I said on my previous post, usually blazingly fast when compared to regular pivot tables. Below are real numbers taken from a medium SSAS DB (wifi connection):
(sorry for the not so great resolution, hope it shows 367.309 rows on 35 seconds, 11 columns-don’t try that on a pivot table!)
First of all, we’re aware that this can be abused… But either can it be (actually it is!) with regular ssas pivot tables, causing server/excel overhead & response times to grow exponentially as you increase the number of columns on the pivot.
Second, it seems there’s a limit somewhere at 1.048.571 rows. Still looking into this one, but I would say that our users could live with that. :)
Would this be interesting to you?
The improved feature is currently being tested on some of our most demanding customers :), and will be available in a future build of SmartPivot.
We are really interested in getting all kind of feedback. If you would like to beta test this feature you can reach our support email, available through the SmartPivot addin, alternatively just use my blog feedback forms.
Last but not the least, if you’re an Microsoft MVP just drop me a note and we’ll get you full free licenses!