“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.
The Goal: One Slicer Sheet Impacting Many Report Sheets
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.
The Snag: Slicer Cross-Filtering Performance
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?
The Contest: Find Efficient Techniques!
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 🙂
- Use lots of charts, and have them all respond to a shared set of slicers
- Entries must include your PowerPivot workbook, plus an explanation of what you did
- Use any data source you want, but please use non-sensitive data and enough rows that we can spot performance differences
- Use at least 6 slicers, with cross-filtering enabled
- You do NOT have to use PivotCharts, but you can
- Macros can be used to design the report if you find that helpful, but should NOT be part of the update process when I click a slicer. (Authoring time is ok. Run time is not.)
- Entries will be judged on, in roughly descending order:
- Performance – I will compare query speed with slicer cross-filtering turned on vs. turned off. Smaller differences are better for this contest.
- Polish – as I slice the report, do the charts all still look nice? Ex: Blank space in charts = not good.
- Ease – how hard is it to execute your technique? We will turn winning entries into blog posts, and this will become a new reference technique here at P3 Adaptive .
- Originality – this never hurts but is not crucial. Fun is good. Unexpected benefits and features are even better.
Have fun 🙂