Surprising Example of PowerPivot Compression

February 8, 2010

Tony and Mike

“Best not analyze too deeply on this one, huh?”

A long time ago I promised a guy named Sam that I would dig up some examples of PowerPivot compression.  I then, of course, forgot all about it until today.  Sorry Sam.

But today I was playing with a data set on my desktop machine that was really getting me down.  The data was provided as a (regular, non-PowerPivot) Excel file with 500K rows in it.  And my machine labored to do anything at all with that data – opening it took forever.  Deleting a single row took forever.  And so on.

But this was the same machine I had used to do a PowerPivot demo with a 100M row data set, which ran with no problem!  0.5% of the data was bogging me down in Excel!

So I decided to do a “before and after” comparison.

Hypothesis

Based on my knowledge of PowerPivot compression, I expected that PowerPivot would do a little bit better than Excel on disk, and a lot better in RAM.

Didn’t quite turn out that way :)

Results

       PowerPivot Compression Relative to Excel

 

Data Stored in Excel Sheet

Data Stored in PowerPivot
File Size on Disk

126 MB

10.2 MB
RAM consumed by Excel.exe

1200 MB

140 MB

Wow.  8x better in RAM, 12x better on disk!  (I was SO tempted to use a pie chart.  Just kidding).

Good thing I ran the experiment twice!

First time I did this, the PowerPivot RAM number was 600 MB.  Still 2x better than Excel, but 8x is much better :)

Not quite sure what was going on with that 600 MB number.  I’d had Excel open for awhile and probably hadn’t closed Excel.exe completely after closing the native Excel file.  Probably that was it.

OK, don’t analyze!  Rejoice!

Bottom line:  12x better on disk and 8x better in RAM, no matter what, is tremendous.  Not to mention the performance of working with the data and how much better that is.

And PowerPivot’s data compression ratio only gets better with larger data sets.  500k rows is big, yes, but remember, this same computer handled 100M, which I obviously can’t even test out in Excel for comparison.

Now that the data is loaded into PowerPivot, I don’t even have to use my desktop machine to work with it.  I’m switching over to my laptop.  The laptop is even running 32-bit whereas the desktop is 64.  But hey, the laptop fits on my new treadmill desk :)

And my waistline could use some compression.

4 Comments | Uncategorized | Tagged: | Permalink
Posted by powerpivotpro

Tommy Farmer? Donny Chong? A Photo Essay

February 4, 2010

Given that it’s Doppelganger Week on Facebook, I thought it was time to show you what I’ve been talking about.  Donald Farmer and Tommy Chong – not just separated at birth, but living intertwined lives ever since.

Check it out.

25 blog_author_donald_lg9 Donald_Farmer          4
7 Capture 3 1

Photos, Left to Right, Top to Bottom:  Donald at University, Greenwich 1976; Donald’s “Medieval Archaeology” Period, date unknown; Tommy, artist’s rendering on side panel of 1968 VW Minibus, 1981; Donald’s famous Speaking Vest makes its debut on Redmond campus; Tommy, mugshot, 1998; Donald, TechEd 2006 speaker photo; Donald on day 25 of his late 2009, 9-country, 27-day speaking tour; Tommy’s guest speaker role at SP Conf 2009 going awry; Donald on Larry King’s Extreme Makeover Edition; Tommy’s inaugural “Power Hour” podcast, Days Inn, Boulder Colorado 2006.

(Yes, Donald selected me as one of his top 10 BI bloggers of the year, and THIS is how I repay him.)

Leave a Comment » | Uncategorized | Permalink
Posted by powerpivotpro

The bug count also rises

January 14, 2010

blaine-and-vulcan-in-predator

“I will do it for Prado, who was once great with the bugs. I will do it for the time we filled Prado’s office with bouncy balls, and for the time Prado wore his nerf weapons in the marketing hall and slew all of them with no fear and only a great joy at the combat.”

After last night’s post where I related the story of an engineer (me) savaging a marketer, I couldn’t resist sharing this story that appeared in MicroNews (the MS internal newsletter) during my first year at Microsoft.  It was a contest – write a (hyper-short) software story in the style of Hemingway.

 

The whole story is here, and is only about half a page.  Well-worth the read:

http://www.workpump.com/bugcount/bugcount.html 

That half-page work of fiction – both its mere existence and its contents – is also a glimpse into what I would call “old Microsoft,” which I basically caught the tail-end of.  Good times.

John Browne, author of software Hemingway without peer, I salute you, and am grateful I could find your fine work preserved on the web.  Oh, and I include your requested link to the copyright license.

1 Comment | Uncategorized | Permalink
Posted by powerpivotpro

PowerPivot for Science?

January 7, 2010

Is anyone using PowerPivot in a scientific environment, or considering it?

If so, drop me a note.  I know some folks who would like to talk to you.

Leave a Comment » | Uncategorized | Tagged: | Permalink
Posted by powerpivotpro

The anti-inspiration for PowerPivot UX

December 18, 2009

(As the holidays are upon us, I’m falling into an even slightly less formal mood…)

You’ve probably heard that at Microsoft, we spend a lot of time on user experience design.  Many iterations, lots of psychological evaluation (of the software, not the engineers), and some pretty sophisticated usability laboratory testing.

Up front, we also develop a number of conceptual mockups (screenshots or slide decks) that demonstrate to everyone the vibe that we are aiming for.  These serve as inspiration throughout the product cycle.

For PowerPivot, we also took the extraordinary step of identifying a screenshot of what we wanted to avoid.  Check out this controller guide from a recent video game:

Explicitly designed PowerPivot to NOT look like this

OK, not really.  This was created by the guys at Penny Arcade, one of my favorite reads.

But the spirit is the same.  How many manuals have you opened, seen something that essentially looked just like this, and then immediately put back down?

Happy Friday :)

3 Comments | Uncategorized | Tagged: , | Permalink
Posted by powerpivotpro

An oldie but a goodie: The Evil Pivot Wizard

December 8, 2009

Had a wonderfully entertaining and intriguing discussion with someone today that brought this back to mind.

A few years ago, someone posted a survey on SlashDot, asking “Who is the most powerful wizard?”  The choices were things like Harry Potter, Voldemoort, and Gandalf.  But there were some entertaining write-in votes as well.

My favorite, of course, was this one.  And remember that SlashDot is not exactly a pro-Microsoft forum:

“The Excel Pivot Table Wizard’s magic is both arcane and available for use by mere mortals. Forged in the fires of the dread mount Microsoft, wrapped in evil, the Excel Pivot Table Wizard freely gives to all men willing to read two pages of documentation. By aligning yourself with him, you become master of the office, performing tasks in seconds that would take the peons working in the mines with you eons to accomplish through normal means. The wizards magic is irresistible, locking you into an enchanted dungeon of a single proprietary operating system and it comes at no mean cost for you or your company. But in the end, should you wish to win on the field of economic battle, you must ally yourself with the Excel Pivot Table Wizard.”

Hilarious.  I had that tacked to the door of my office for several years.  (I also love the author’s struggle between his anti-MS prejudice on one hand, and his simple acknowledgment of the value on the other.  We all struggle with something I guess.)

Anyway, I’m brewing some tea and settling in for some work on the Great Football Project.  I’ve been away from it for too long and it is time.

1 Comment | Uncategorized | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot Beta (CTP3) Tomorrow!

November 17, 2009

Yep.  11/18.  At http://powerpivot.com

If you’re arriving at my site for the first time, I suggest you check out the following for CTP3 inspiration:

What is PowerPivot?

Bing 48-Hour Case Study

Temperature Mashup Demo

The Great Football Project

And any questions, please post as comments :)

Leave a Comment » | Uncategorized | Permalink
Posted by powerpivotpro

Ugh

November 12, 2009

I’ve spent a lot of time the last two days recording PowerPivot videos – some for this site, and some for a special project.  Today when I uploaded them to YouTube, well, I discovered I’d used too fine a resolution, and you can’t read anything in the formula bar.

Are formulas really all that important in an Excel environment?  I was one sad dude when I saw what I’d done.

Good news:  re-recorded two of them.  They are uploading now.

Leave a Comment » | Uncategorized | Permalink
Posted by powerpivotpro

More Excel Services API Info

November 10, 2009

Two new links – one to the official Excel blog, and one to Shahar Prish’s personal blog.  Definitely check out Shahar’s blog – he’s a bit of a Renaissance man… one of the big brains behind Excel Services, but also one of the co-inventors of the method that enabled us to embed the PowerPivot db directly in the Excel file, which makes him a personal hero of mine :P

Excel blog post:  http://blogs.msdn.com/excel/archive/2009/11/09/excel-services-in-sharepoint-2010-rest-api-examples.aspx

Shahar’s blog:  http://blogs.msdn.com/cumgranosalis/

Leave a Comment » | Uncategorized | Tagged: | Permalink
Posted by powerpivotpro

Sports fans use BI!

November 9, 2009

I love this story, had to share.  Apparently, Kansas City Chiefs fans noticed that Larry Johnson was just 75 yards short of passing Priest Holmes for the Chiefs’ all-time record for most career rushing yards.

Larry Johnson is quite simply, not a nice guy.  His record off off-field violence and remarks has endeared him to exactly no one.  And Priest Holmes was a class act that everyone loved, forced to retire early by a spinal condition.

So Chiefs fans started an online petition, requesting that the Chiefs intentionally bench Johnson, arguably their best player, so that he could not break Holmes’ record and forever be remembered as a Chiefs “great.”

Today, the Chiefs went one better and simply cut Johnson from the team.  As in, go away and don’t come back.  Just an incredible story really, both about the fans and the team’s management.

http://sports.espn.go.com/nfl/news/story?id=4637300 

At least one meaty PowerPivot post coming later today.  I have a special guest lined up :)

Leave a Comment » | Uncategorized | Tagged: | Permalink
Posted by powerpivotpro

« Previous Entries
Next Entries »