A few things I forgot to clarify yesterday:
Wait, that’s a different list. Here is the real list:
3 Comments | 
Hello World / Administrivia | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Contests | 
Permalink
Posted by powerpivotpro
“Shooting space garbage is no test of an Excel Warrior’s mettle!
I think it is appropriate to start this post with the following disclaimer:
You do NOT have to be able to win this contest in order to be a monster at PowerPivot.
Think of this as brain candy. Something that opens your eyes to some of the high-end power available when you merge PowerPivot calcs with native Excel calcs. A 300-level technique.
***CONTEST UPDATE*** Check here for 3 small rules clarifications.
This is a pretty common desire. If the report consumer wants to see ALL of the data broken out by the same set of filters, why make them repeat those filter selections on every single sheet of a workbook?
Isn’t it better, in those cases, to give them a single set of “master” slicers on a single sheet like this?
Master Slicers Sheet
(Sensitive Data Redacted as Always)
There are 14 slicers on that sheet. The user can select the department, class, etc. that they care about up front.
Once they have made their selections, in this particular workbook they then have dashboard sheets that look like this:
Multi-Chart Dashboard Sheet Fed from PowerPivot
We have several sheets like that in this one workbook, with 20-30 charts on each. All charts react to the slicers on the Master Slicers sheet.
But you’ll also notice that 4 slicers appear on this sheet, too. Those are duplicates of 4 of the slicers on the master sheet. They are the same exact fields as the corresponding 4 on the master sheet, so they are always in sync – set them on one sheet and all other sheets reflect the same selections.
That is merely a convenience – we figured that most slicer selections would be made once, but that users would appreciate being able to bounce around the calendar a bit with the time slicers, without having to switch sheets.
There are also about 20 sheets in the workbook that look like this:
1 of 20 Single-Chart Sheets Fed from PowerPivot
Again, with the four time slicers repeated for convenience.
80 unique charts and 14 unique slicers. What could go wrong?
Well, as I documented in the post on slicer cross-filtering, this is roughly the equivalent of having 80 * 14 = 1,120 pivots all updating at once in response to a single click. (Maybe it’s not quite that bad, but it DOES get VERY complicated).
And that is very slow, even if the performance of any single pivot is super-fast.
But our report that we built at Pivotstream is fast. It does NOT bog down on slicer cross filtering performance. So, how did we do it?
After we built this workbook at Pivotstream I realized that our technique could be refined quite a bit, made simpler. And while we are already doing that, I realized:
THIS IS AN EXCELLENT CONTEST FOR EXCEL PROS!
I still have two MSDN Subscriptions to hand out. These basically are unlimited, free MS software for your own personal use, so they are a pretty damn good prize.
The top two submissions will win those. Runners-up will win PowerPivot architecture posters from Denny Lee, assuming he has not run out 
Have fun
1 Comment | Performance, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Contests | Permalink
Posted by powerpivotpro
“I don’t always use spreadsheets, but when I do, I prefer… PowerPivot.”
-P Kumar
OK, I’ve never met P Kumar. Most people haven’t. He is a mysterious man, preferring to go by his first initial only, much like the famous J Allard at Microsoft. P probably looks nothing like the picture above. But he is, quite simply…
The Most Tenacious Man in the World.
When I posted the Where’s Waldo PowerPivot Contest, I knew it was challenging. I half expected the winner would be someone who had already stumbled upon the answer, rather than someone who went looking for it. I mean, I had stumbled upon it myself just a few days prior.
But this did not deter P. Nor did the fact that he had never installed PowerPivot, client or server. Off he went.
I kinda get the impression that P barely slept for 48 hours, performing the equivalent of a depth-first-search traversal of every pixel in the product. You can get a play-by-play account of his efforts here in the comments thread.
Anyway, here’s the answer:
Yeah, it’s in the usage monitoring features of PowerPivot for SharePoint. Those dashboard views are rendered in Excel Services from workbooks created by the PowerPivot team, and they accidentally left in a chart from their own internal testing of the product.
You can even create your own workbooks as custom views, which I have been doing lately to filter Pivotstream employees out (so we just see the activity of our customers).
Congratulation to P, the most tenacious person I’ve encountered in a long time. He truly deserves the prize. Now P, I expect you to do wonderful things with that software ok? Please report back so we can track your exploits further
This reminds me of one of my favorite old jokes – “How do you hunt an elephant?” Each profession’s quirks are revealed by their approach. In particular, the approach of computer programmers seems relevant:
The full list can be found here.
2 Comments | Hello World / Administrivia | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Contests | Permalink
Posted by powerpivotpro
P Kumar has won in impressive fashion.
No one has sent in a correct answer yet. Two things I think are worth pointing out:
1) The logins are visible in the UI, and don’t require you to go digging through obscure files on the hard drive to find
2) They appear in PowerPivot for SharePoint, NOT in PowerPivot for Excel
Happy hunting.
Leave a Comment » | Hello World / Administrivia | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Contests | Permalink
Posted by powerpivotpro
P Kumar has won in impressive fashion.
An Unusual MVPI recently was awarded the title of Microsoft MVP for SQL Server, as a result of my community work around PowerPivot. Good stuff – I get access to some newsgroups, a little advance info every now and then, get to attend the MVP Summit in February, etc… oh, and I get to put the neat little MVP symbol in my email signature, displayed here at the right.
A brave new world we live in, where a guy like me who knows hardly anything about databases lands in a SQL Server MVP program. PowerPivot is a bit of a black swan, straddling the fence between hardcore db’s and the world of information workers. Makes just as much sense for me to be in the Excel MVP program really, so hopefully I will still get plugged into those feedback loops as well.
Fabulous PrizesOK, that’s great for me and all, but how does it benefit you? Well, for starters, as part of the MVP packet, I got three MSDN subscription cards to give out!
These subscriptions must be activated within the next few months, and are good for a full year once activated.
I’m not an expert on all the particulars, but what it basically gives you is full access to the MS software catalog, for your own personal use and experimentation. Office, Windows, SQL, SharePoint, Exchange, Visual Studio, etc. – a Golden Ticket of sorts.
I recently discovered that some of the PowerPivot development team’s Windows Domain logon names were accidentally shipped in the product. Pretty funny.
To win the first MSDN card, all you have to do is find where that occurs, and email me: rob@pivotstream.com
Simple huh? Hee hee. Whoever finds this is indeed worthy.
If I receive multiple correct entries within the first 24 hours after this post goes live, I will randomly choose one of those entries as the winner. This will give everyone a fair chance, regardless of where you live.
After the first 24 hours, first correct entry wins.
If I have reason to believe that you already have access to MSDN, I doubt I will award you the card, so let’s leave this for people who don’t have it already ok?
I have not yet decided how to award the other two, but will figure it out soon. Also, I have a few other prizes that are a bit on the silly side – stuff from Microsoft with various logos on it that certify the wearer/user as nerd royalty – that I will also be giving away because my wife commands it
Watch this space for announcements.
14 Comments | Hello World / Administrivia | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Contests | Permalink
Posted by powerpivotpro
