A 48 hour case study from Bing – Zero to Sixty :)

“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:

PP_Old

PP_OldData

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:

     PP_New2

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!

PP_NewSP

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 :)

5 Responses to A 48 hour case study from Bing – Zero to Sixty :)

  1. amirnetz says:

    OMG. What a great story. More. I want more of those.

    – Amir.

  2. Darinee has had it easy. One of my biggest annoyances with Excel is the imbecilic PivotTable calculated field feature, which feels like it was developed by a freshmore on a summer job. Because of this idiot feature, I’ve had to flex my VBA muscles to create *real* calculated fields based on a data field. Well, the party line has been to create the Excel formulas you need outside the PivotTable. Well, OK, how do you do that for each measure in a data field and synchronize the outside “calculated column” when the table is filtered, expanded with new data or the column total is toggled on or off? When I’m done coding you can’t tell where the actual PivotTable ends and the outside calculated column begins. But this is totally unnecessary work…at least it should be. I’m disgusted every time I have to go through this routine.

    In stark contrast, DAX calculated measures are amazing. It makes Excel’s calculated field feature feel even dumber than it already is. No more senseless VBA coding. I could cry…Make that two tears: one for the elation of finally having real calculated fields in PivotTable and the other for the sadness that stat and financial functions aren’t supported in DAX (sigh). Now instead of VBA coding, it’s just the occasional figuring out how to do standard deviation or CHINV from first principles. Did I say standard deviation? Nah, that one is so fundamental (along with its variance companion) that it must be in CTP3.

    • amirnetz says:

      DAX is the unsung hero of PowerPivot. It is hard to show it off well in a short demo. But once you really dig your teeth into it, you find out that it is the real powerbase of PowerPivot. DAX is petty amazing, and it is only V1.0.

      Looking forward to the next release (give us a bit more time folks…) I think DAX alone is going to change everything you knew about BI or about Excel… It is truly exciting to see it evolving.

  3. Amir, you’re taken the words right out of my mouth. Exactly how I think about DAX.

  4. greg kramer says:

    exceptionally cool for excel jocks to see how things are unfolding behind the scenes at ms…also, very good marketing 2.0, it seems to me.

    really jazzed about all of this.

Leave a Comment or Question