PowerPivotPro

Here’s the IM chat I had with Darinee right after I published her guest post:

9 am tomorrow was too generous.

Imagine how much things can change once we’ve empowered an army of Darinee’s :)

“When you can snatch this pebble from my hand, you will be re- HEY, GIVE THAT BACK!”

-Me

I promised a guest post, and a guest post I deliver :)  Darinee is a good friend that I met during my brief stint working on Bing, before Amir came calling and said “So, Rob, we’ve got this little project…”

I didn’t really think of her as an Excel person, and I definitely don’t think of her as a BI person.  To me, she is test programmer extraordinaire, cold-blooded Halo assassin, and relentless-pursuer-of-the-right-thing.  I guess it’s that latter quality that made her give me a call when she got assigned a reporting function…

Here, in her own words, is Darinee’s Fantastic 48 Hour Adventure with PowerPivot:

—

Hey all,

I’m Darinee, and I’m a test lead in Bing. But after a week of playing with PowerPivot, I’m seriously thinking that data analysis and visualization may be a future career move. J (Just kidding! No, really, don’t tell my manager…)

Every test lead at Microsoft, at some point, gets tasked with the job of analyzing bug metrics, and this always is done in Excel, because we don’t have time for any other approach. Excel can be great but mailing files around inevitably leads to out-of-sync files, and, frankly, storing the file in a Sharepoint document library doesn’t change things much since people save them off locally anyway.

Before PowerPivot, I had built an Excel file that dynamically pulled data from our bug database. It had an über-table with 70K rows of bug data and a bazillion calculated columns, many of which were VLOOKUPs to other tables. While all of this data allowed me to graph what I needed, this ultimately meant a sluggish Excel file that took forever to update – plus it was 25MB. :(

Here’s what it looked like:

So, what did I do? Rob here worked with me in Bing for awhile before leaving us to go back to BI-land (traitor!), and I called him about PowerPivot because last time we talked, he said it was aimed at this sort of thing. By the end of our 30 minute conversation, I couldn’t get this dopey grin off my face as to what PowerPivot+Sharepoint could do for me. I installed the Excel add-in. That was Tuesday around lunchtime.

By late afternoon, the smile was even bigger. Switching over to PowerPivot took less time than updating my old spreadsheet! PowerPivot completely simplified my data model AND resulted in faster performance. I was able to throw those lookup tables each into their own PowerPivot table, connected by relationships (no VLOOKUP’s!), while still producing the same meaningful pivot tables. In fact, another table of data, which was prohibitive to add to my prior workbook, added hardly any size to my new PowerPivot workbook. Multi-table pivots are f.p.! (That stands for, ahem, frankly phenomenal.) And my new file, despite containing much more data than the old file, now was only 13MB!

Ok, I’m kind of a n00b for not knowing about Slicers before, but I love how PowerPivot automatically formats them around my charts. I was easily able to add Slicer controls to break down the data by six different fields without dealing with layout. A few tweaks to appearance, and I was now looking at a dashboard-type application, whereas before I had just had… a spreadsheet. And again, I couldn’t get this dopey grin off my face at work so my neighbors knew something extraordinary was up.  They were right:

Wednesday morning, I set up my own Sharepoint server with a PowerPivot gallery, and boom! – I had the same usable reports via a URL that I could share. No more out of sync problems, because everyone just consumes via the web interface. Portal with thumbnails, too?  Money!

By Thursday mid-day I’d finished my tweaks. Ultimately, PowerPivot made organizing my data simple and let me create beautiful reporting apps quickly – reporting with Excel is now actually FUN! The official reporting team next door is eyeing my site now, saying ooh… we want that. And all in a few days’ work. :)

—

Rob here again.  Seriously, this was a blast for me.  I didn’t expect things to go this way, to be honest.  I figured I’d give her a quick summary on the phone, and maybe in a few weeks she’d have time to kick the tires a little, ask me more questions, etc.  This isn’t her main job, after all.

Nope, not Darinee.  About once an hour, every hour, after our phone chat, I’d get an update over IM.  “OMG, multi-table pivots!!!”  Then “Slicers rock!!!”  And “I can’t believe this is still Excel!”

And then the clincher, Wednesday morning – “Hey, I’ve got my SharePoint server set up, here’s a URL to a published report – whaddya think?”

Everyone, please do me a favor – when you blast through things at this speed and are having as much fun with it as Darinee is, drop me a note so I can share that joy.  Darinee’s dopey smile was nothing compared to mine :)

Summing up, here are the things that struck me:

1. She no longer has a version control problem on her spreadsheets, thanks to web rendering
2. Her next step is to configure auto-refresh of these reports for zero-maintenance updates – Darinee, I give you til 9 am tomorrow :)
3. Her reports are now much more functional than before – they operate on more data and offer much more exploration capability thanks to slicer dashboards
4. Her files, despite containing much more data, are half the size as before
5. Her coworkers are all excited about this.  Excited.  About Excel reports.  Let that sink in.  And then remember that end user engagement pays all sorts of dividends.
6. She literally went from knowing NOTHING about PowerPivot, to a full deployment on a server she set up, in two days time.

Awesome.  Darinee and I are still arguing over who should feel grateful to who :)