Santa’s Elven Productivity Simulator Leaked Online!

December 23, 2010

 
PowerPivot Model for Elven Productivity in Santa's Workshop

Now With Ornaments! :)

Get Your Password Now!

Folks, Wikileaks is at it again.  This time, they’ve come into possession of one of Santa’s most closely-guarded management tools:  his Elven Workshop Productivity Simulator.

And naturally, Wikileaks has asked for my assistance in getting this out to the public.

So I did what every responsible person would do.  I decided to host it.

To log in, go to https://clients.pivotstream.com/sites/santa/pages/simulator.aspx

Username:  santa@pivotstream.com

P-word:  S@ntaModel1

How it was done

I won’t go into too much detail, but…

  1. The web app uses PowerPivot for SharePoint, Excel Services, etc.
  2. The app was built 100% in Excel 2010 with PowerPivot for Excel
  3. No VBA, no code, nothing like that
  4. PowerPivot measures in hidden pivot tables
  5. Then, a dose of advanced Excel formulas, and LOTS of conditional formatting

Special Thanks To

My wife Jocelyn – for doing the “lite brite” style tree art for me in Excel.

Pivotstream CEO Jeff Elderton – for letting me bounce ideas off of him…  and for, ahem, helping me “spruce” up the visuals a bit (get it??  Spruce???  Certainly worth every penny, this entertainment).

All of my colleagues at Pivotstream – for humoring me and tolerating this sort of thing.  I dare say they encourage it.

All of you – for stopping by, whether this is your first visit, one of many, or even…  both your first AND last visit :)

Disclaimers

1) I may disable this account at any moment if things get too crazy.  Check in with me on Twitter if you want to see status updates.

2) If you are not Christian, please don’t take any offense.  Trust me, I am no more Christian than you are – I’m pretty much not religious in any accepted sense, but I do love the trappings of the holidays I was raised with.

3) I am of course not associated with Wikileaks – big shocker I know.  I personally don’t have much trouble with Wikileaks though – the less secrets big organizations have from the little folks, the better, in my opinion.  So I can either take it or leave it when it comes to Wikileaks, but the reaction, the move to freeze assets and shut down any mention of them in the press – well THAT is pretty scary.

Anyway, enough disclaimer crap.  Enjoy, everyone.  I hope you find this as entertaining as I did…  which is pretty unlikely now that I think about it :)

4 Comments | Non-Football Examples | Tagged: | Permalink
Posted by powerpivotpro

Another way to get and use slicer values in formulas

December 9, 2010

 
Back in June, Kasper posted a trick which lets you detect a user’s selection in a slicer, and use that in a PowerPivot measure.  That’s a very useful trick, one that we employ all the time at Pivotstream.

But sometimes, that is overkill.  Sometimes, you just want to grab a slicer’s selected value and use it in an Excel formula, right there in the sheet.  Here is the simplest method we have discovered so far:

1) Duplicate the field as a slicer AND a report filter

First step is to take the field you want to use as a slicer, and add it to your pivot both as a slicer, and as a report filter, as in this simple pivot:

Using Slicer Values in Excel Formulas Step One 
Date Field Dragged to Both Slicer and Report Filter

2) Observe that the Report Filter “Tracks” the Slicer

OK, now click a date in that slicer.  Look what happens to the report filter in the sheet:

Slicer and Report Filter Stay in Synch

Cool huh?  Since they are the same field, the report filter has to always be in synch with the slicer.  And unlike the performance penalty that can pile up with multiple slicers, duplicating a field like this will NOT make your pivot slower at all.

3) Use the Report Filter Cell in a Formula

Yeah, you probably see where this is going already:  now you can reference the report filter cell in a formula, like this:

Reference the Report Filter to Get the Slicer Selected Value

4) Clean up the visuals

Move the formula to a more centered location, change the font, and hide the row that contains the report filter:

Hide Report Filter Row But Formula Still Reflects Slicer Selection

Notes

  1. I haven’t tried it but I am pretty sure this will work with regular pivots, too, not just PowerPivots.
  2. If you select multiple items in the slicer, you will get the text “Multiple Items” in your formula instead of a single value.  UPDATE:  I have since written another post that covers this.  It’s not a pretty technique but it gets the job done.  Post is here:  http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/ 
  3. When you really get started thinking about this, there’s really no limit to the cool tricks you can pull off.  I’ll show a few more specific examples over time, but I’m sure you guys will discover many cool tricks of your own, too.  Here’s some food for thought:  report filters aren’t the only way to get slicer selections into a worksheet cell.

10 Comments | Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot Refresh: CPU/RAM Spike at Completion

December 6, 2010

 
I mentioned this in a recent post, but I figure a picture illustrates it better.  This is a screenshot of task manager as a PowerPivot refresh completes.  This one ran for about 30 minutes, so this represents just the tail end of the process:

PowerPivot Data Refresh CPU and RAM Spike 

Note the two highlighted points – one of the 4 CPU’s pegged at 100% for awhile – and while the other 3 did go “quiet” at the very end, they WERE active for part of the time that the fourth CPU was at 100.  So – CPU gets precious near the end of refreshes (after hovering around 50% for much of the process).

Even more notably, RAM usage spiked by nearly 2 GB!  The PowerPivot file in question was 1.45 GB on disk when it was complete.  And that workbook was still in RAM after refresh completed, so the 2 GB spike was pure overhead during the final compression process.

(Side effect:  Even if you have enough RAM to load a workbook, that does not mean you have enough RAM to refresh it.)

Allocate your server RAM and CPU around the refresh process, folks.

Leave a Comment » | Data Import, Performance, SharePoint | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot for Excel 2007?

December 2, 2010

I see this question a lot.  Excel 2010 sure looks a lot like Excel 2007 – they both use the Ribbon instead of traditional menus, and they both use the new XLSX file format.  So I can understand why it seems like 2007 should work.

I’m sad to say that PowerPivot is NOT supported, and simply will not function, with Excel 2007.

Stuck on Excel 2007?  Not all hope is lost…

First off, you can get a trial version of Excel 2010 by clicking here.  If you are just in an evaluation phase right now, that likely will help you quite a bit.

The next thing to remember is that your entire organization does NOT need to upgrade to 2010.  Only the PowerPivot authors need to have 2010, and then they can publish their work to SharePoint 2010 (and servers are often upgraded long before desktops).  So this *might* help.

(Also keep in mind that your entire org does NOT have to adopt SharePoint, either.  A single “departmental server” running on a reasonably capable desktop computer can do the job quite nicely).

Lastly, even your PowerPivot authors can still run 2007 for most tasks, and have 2010 installed side by side for PowerPivot tasks.  That is sometimes a useful concession to IT.

OK, so why IS Excel 2010 required?

Is this some nefarious MS plot?  Not this time.  There are good reasons why 2007 is not supported, and I worked closely on two of them while I was at Microsoft.

Reason 1 – Slicers

The PowerPivot team (of which I was a member at the time) decided that PowerPivot was not going to “shine” in customers eyes with the existing appearance of Excel.  A more engaging, “Fisher Price” style of interaction with a finished report was required.

(I can confirm the truth of this from my experience on the Great Football Project in 2006 – our focus group participants BADLY needed some sort of modern-day filtering/exploration method, and Excel 2007 simply didn’t have it).

So the PowerPivot team actually “donated” a bunch of people, for about two years, to the Excel team in order to build the Slicers feature into Excel 2010 – as illustrated by Region, Territory, and DR-ST below:

Slicers - One Reason Why PowerPivot Will Not Work With Excel 2007

Reason 2 – Embedded Data

The second reason is the bigger one, the one that made it impossible to support Excel 2007.

It was very much a requirement that PowerPivot be able to store its data inside the XLSX workbook, rather than in a separate file.  Imagine, as an Excel user, if you were told you had to lug two files around everywhere just to make your pivottables work.

So, Excel 2010 had to invent a mechanism for allowing other applications to store their data inside of workbooks, and for allowing that data to be *fetched* by those applications WHILE EXCEL IS RUNNING, which um, normally locks other applications out of the file.

This was some of the most imaginative engineering I have ever witnessed, on the part of some amazing engineers like Shahar Prish and Raman Iyer.  (My role was essentially High Priest…  and Drawer of Pretty Diagrams).

So, today, you can take a PowerPivot workbook, rename it from .XLSX to .ZIP, and then navigate into XLCustomData and see a file named Item1.data:

Embedded Data - The Biggest Reason Why PowerPivot Will Not Work With Excel 2007

Anyway, hopefully that clears up the questions – “does PowerPivot work with Excel 2007,” “what do I do now that I know it doesn’t,” and “why DOESN’T it work?”

13 Comments | Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro