PowerPivotPro

-Posted by Rob Collie

OK, ignore my last post, that was just silly.

Yesterday one of my fine colleagues at Pivtostream mentioned that he was seeing both field lists simultaneously.  Then followed an exchange where I said “BS,” he said “bet me some cash, oh PowerPivot boy” and, well, he was right.

One should be careful never to wager against one’s own boss.

Anyway, in beta releases of the addin, it was IMPOSSIBLE to bring up the Excel field list without disabling the addin.  Apparently, the team decided at the last minute that allowing the user to bring up the Excel field list could compensate for the lack of certain features.

It’s a brilliant move on their part.  So here it is, both field lists, living together in harmony:

You don’t have to disable anything.  Just go to the Options tab on the ribbon when you have a pivot selected, and click the field list button there:

Implications Galore!

OK, so…  you can do the defer layout update trick which is excellent for long-running queries, without turning off the addin, that’s huge.

But wait, there’s more

You can also adjust the layout of your measures.  Try dragging the little Sigma “Values” capsule around the Excel field list (shown here at right) and you will see what I mean – move it to Row Labels for instance.

This also comes in handy when you have other fields on Column Labels and want to change whether Measures are nested inside of those fields or vice versa.

But the last trick that this side by side field list stuff enables is truly my favorite…

Batch Creation of Measures Without Refresh!

Follow along, this is fantastic.

Step one:  turn on defer layout update:

Two, go create a measure:

Three, ignore this error you get when you enter the formula and click OK:

Four, observe that the measure was created anyway:

This changes my life in rather significant ways.

-Posted by Rob Collie

UPDATE:  It turns out that all of this is unnecessary, there is no longer a need to disable the addin to get these benefits.  (I was still stuck on what was possible in the Betas).  See the latest post on this topic for details.

If you’re like me and you work with large data sets and/or complex measures all the time, you may be annoyed by the time it takes to layout 6-10 fields on a pivot – a handful of measures, a field or two on rows, a couple of slicers… even with the performance of PowerPivot, you can be burning a reasonable amount of time in the click-and-wait game.

I have a trick for you.

First, disable the PowerPivot addin.  Yep, you heard right.  On the File tab, go to Options:

Then select Addins.  At the bottom of that screen, open the dropdown for Manage, and pick COM Addins from the list:

And then in the dialog that comes up, uncheck the PowerPivot addin:

Now go to the Options tab on the PivotTable ribbon and enable the Field List:

OK, now you see the native, built-in Excel field list.  If you are familiar with normal Excel pivottables, this will appear foreign to you, because this is the “OLAP” field list – designed strictly for use with OLAP db’s like Analysis Services, which PowerPivot happens to be under the hood.

Anyway, at the bottom of that field list, you will see the “Defer Layout Update” checkbox.  Check that, then you can make a bunch of changes to the layout of the pivot WITHOUT them refreshing the pivot.  Click Update and all of your changes will run in a single batch while you go get a fresh cup of coffee:

Cool huh?  A few things to note:

1. You can’t create measures without the addin enabled.
2. You also don’t get the fancy slicer auto-layout.
3. The field checkboxes are organized very differently, split into measures and non-measures AND by table – you will see what I mean
4. There are a few “dummy” measures visible in this field list that are hidden in the addin

OK…  I should also mention that we might have had this feature already built-in to the PowerPivot v1 field list if it weren’t for me   It kept coming up during development – “should we build that defer layout feature in?”  And I kept saying “no, not yet.”

Why would I do such a thing, you ask?  Two good reasons.  One, we were running WAY low on time and resources.  By NOT doing that feature, the team was able to do several other important things that today we’d be complaining about instead. 

Two, I knew that if we added that feature, as we worked on PowerPivot internally, we would all turn it on whenever we hit something that was slow.  As a result, we’d not feel the pain of performance issues as acutely as we should, and not as many things would be identified and fixed.  So, like Cortez burning his ships, I wanted to deprive us internally of this crutch.

Of course, now that I no longer work at MS, and I find myself in a job where I crunch hundreds of millions of rows daily, irony has come home to roost   Just kidding really – I still think it was a good v1 decision.  I’m positive that engine query performance is much better for it, and it will be easy for the PowerPivot team to add this feature in v2.