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

Honestly, it feels a little bit weird to hop back on this thread, as so much as been said  before by Chris Webb, Richard Lees and several other bloggers (me included).

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.

image

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

image

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:

image

 

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

image

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!

image

image

Activating the total row on the table, we can easily confirm the total we previously saw on the source pivot for France/Clothing/CY2008:

image

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:

image

The previously used fields for that measure will be selected by default:

image

Table report & total check:

image

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

image

or

image

Limitations/Considerations

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

image

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!

Take care!

Rui

About these ads

About Rui Quintino
Developer & Software Architect @ DevScope

2 Responses to 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

  1. Pingback: Tabular Queries in Excel 2013, Good news, Bad news,DAX Queries and the missing DAX visual query builder « Rui Quintino Blog

  2. Pingback: #SmartPivot new beta release with #SSAS and #PowerPivot instant text search, now supports for #Excel2013 « Rui Quintino 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

Follow

Get every new post delivered to your Inbox.

Join 279 other followers

%d bloggers like this: