SmartPivot new build, release notes

Just finished publishing, download here as usual. Not a major release for now, more to come soon… not until #sqlsaturday 188 here in Portugal I’m afraid! :)

As for changes in this release, 2.33:

image

No need to request activation keys for free features, just install & use. (free activation key will still be needed only for testing full features, can’t change that, sorry!)

Bug fix when building the search cache for large hierarchies, now works with large hierarchies for levels up to 100.000 members. previously it would skip the entire hierarchy, not good (sorry about that…).

You can now connect directly using the search option, you will be asked for a connection using quick connect and the search pane will open instantly after that. fast!

Filter by list now works with specific member names including characters like ‘.

Search & parent child hierarchies bug fixes and other fast search performance improvements.

And finally got that tooltips filled in the ribbon! :)

image

(as for the future, following pivotviewer, cubes/powerpivot fast search I wonder if we can help with global saved sets/clusters… been getting interesting feedback on this, 2013 named sets are almost useless I’m afraid to say, let’s see…)

So now, sqlsat mode=on, clickpivot=on… ;)

Take care!

Rui

SmartPivot new build, quick connect with recent cubes list & full text search adjustments

Just finishing up uploading a new build of SmartPivot. (this is a “minor” release, more to come soon – as we are processing feedback and working out on some usability improvements on the full text/instant search over ssas olap/powerpivot feature)

Meantime… :)

-you now have a recent cubes list when using QuickConnect (kudos to our team members José Barbosa & Rui Romano for this one, handy indeed! :) )

image

image

-in this release we opted for automatically create the fast text search cache automatically whenever it’s not still available (previously we were doing this only for local cubes/ppivot models). We got so used to the feature that waiting for a refresh click already seemed too much. :)

image

Take care!

Rui

ps-download page is now at http://www.devscope.net/products/SmartPivot

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

New release of #SmartPivot for Excel up and running, With a bonus PivotViewer visualization option included!

Yes, we’ve just finished publishing a new version of our OLAP/Cubes accelerator Add-On for Excel (and yes, still with a Free version!), with major improvements (I hope! :) ). 

image image image imageimage

Let me introduce me some of the new features. Actually I’ll probably need another few posts for all of them, just keep tuned in.

By the way, the download is available here. Please note that you’ll need to request a free activation key before using any feature, the dialogs on the Excel will guide on this. Any problem please tell me.

Some of the features (dashboards features & filter synchronization) are targeted for a full featured licensing, but there are a lot of useful features on the free version!

 

…Starting with New PivotViewer exploration for Excel tables!

PivotViewer for anyone! That was about time! Yes,  there’s a new PivotViewer exploration option for any excel base table. If you’ve seen my post on kindle book savings  a few weeks ago you probably already know what this option will allow you to do. You’ll need SilverLight 5.0 for this and an Excel table , and the click of a button!

(for example, my book stats table I “extracted” from amazon …)

image

Just select SmartPivot> Explore with PivotViewer, and that’s it, instant PivotViewer visualization:

image

It can switch between image style cards or numeric field based ranking colors boxes, ex: Red (lower) to Green (Higher). 

Yes, we are strong believers that we can make use of this in a much more data intelligence/business intelligence context, even without images, so we’ve worked out this scenario. And that’s why its available through SmartPivot. What do you think?

(ordering on the coloring field shows it better I think)

image

Zoom it a little bit and you’ll get the item details:

image

And if you have a field that points to an image, just switch to image based card (the classic PivotVewer style):

image

BTW: Nice feature of PivotViewer is that it does automatic drill down on date values!

Ex. Date column on Excel

image

Proper date filters & drill down actions!

image

Click the year… then month…auto drill-down!image

 

In the next post, I’ll showcase the more classic olap/cube related features:

image

Hope you find the features useful. Tell us what you think!

 

Regards,

 

Rui

Cube Excel Dashboards with SmartPivot-Now Free, See full activation key below

(update: version 2.0 of SmartPivot – a much improved version- has just been published, read it here, download it here)

Yes, we’ve just decided to just let the SmartPivot –Version 1 – Excel Add-In go freely available for anyone who wishes to easily create cube dashboards or sync several pivot table filters with Excel 2007 & Excel 2010.

So that’s it, install it and use it, free of charge. If you have any feedback, problem or feature request just contact me or the support email through the Excel SmartPivot menu.

image

Just download & install the Excel Add-In using the setup and then enter the activation key below to unlock full features.

No time trial. No ads. Although we actually maintained the activation process in order to be able to follow SmartPivot usage.

Activation Key (this only works with V1, for the latest V2.0 you should request the activation key using the SmartPivot Ribbon license option in Excel):

5906-B547-7872-4108-A7EE-6AA3-C193-6F68-B55B5ED3

Kind regards,

RQ

Nasty Excel 2010 Pivot Chart Filters “on mouse over” “Feature”….or ”bug"..?

From some customers usage & feedback of SmartPivot I thought this would be something interesting to share, specially as its not a SmartPivot bug. :)

In fact, its a little annoying issue with the new pivot chart filter zone in office 2010.

In office 2010 pivot charts you get direct access to the underlying pivot source filters (filters, columns, rows), witch is really a great feature for building dashboards with PowerPivot or SmartPivot.

The problem is that it’s very “sensitive”, as soon as you mouse over the filter buttons on the chart, the pivot fields editor changes to that pivot chart context. Confusing when you’re changing top level filters and moving around the screen with your mouse, while you try to set up & finish up some pivot chart layouts.

Example: (as soon as you mouse over the pivot chart filters in yellow, the field editor changes context! )

Its very easy to miss this subtle change and change the wrong chart after that.

image

 

Bug or feature? :) Either way, what I can say is that’s *really*  annoying in these scenarios.

A quick workaround is to disable the filter zones if you won’t need them any more (you can always enable them again later):

image

 

 

RQ