Comparing Access to PowerPivot

Here’s a question that comes up with increasing frequency:  “PowerPivot seems kinda similar to Access in many ways, what’s the difference?”

Why Does the Question Come Up?

Some of you are thinking “yeah, I can see why people would ask that.”  And others of you are thinking “WHAT???  That questions makes ZERO sense, they are NOTHING alike!”

You are both right.

The biggest reason why the question comes up, I think, is the longstanding symbiosis between Access and Excel – Access as data source, Excel as analysis tool.  Remember, Excel used to be limited to about 64,000 rows of data.  And many Excel pros learned to import large data sets into Access rather than Excel, manipulate and prep the data in Access, and THEN import from Access into Excel for PivotTables, charting, etc.

With Excel expanding from 64k to 1M rows in 2007, that tradition has already begun to fade, to an extent.  But even for data sets that fit into Excel, there is still a good reason that drives Excel pros into Access:  VLOOKUP is slow.

VLOOKUP = Excel Acting Like a Database, and Driving Folks to Access

Even for large data sets, arithmetic calculations in Excel can be blindingly fast.  After all, Excel is designed for that.  But VLOOKUP, and its more advanced cousin INDEX/MATCH, is not arithmetic.  It’s a search – “go find me a value that looks like X, and when you find it, return value Y from the same row.”

Even when you’re dealing with row counts merely in the thousands, that can get slow in Excel.  Because “search and retrieve” is what databases are designed for.  And Excel is not a database.  When it comes to finding values, Excel isn’t terribly more efficient than Word.  (A risky thing for me to say, I expect to be corrected in three…  two…  )

But since many data sets inherently “arrive” as multiple separate tables, you can’t avoid trying to splice them together, and that means VLOOKUP, or using a real database product.  I know the SQL snobs will say that Access doesn’t qualify, but Access IS a real db.

An Understandable Question

With that in mind, it’s easy to see why longtime Excel pros see their first PowerPivot demo, and come away asking this question.  At a high level, this is what they see:

OK, so what’s the answer?

PowerPivot Does Things That Access Will Never Do

The first time you write a “Sort By Slicers” or an “Iffer-Blanker”or  a Set of Greater/Less Than Slicers or a Custom Calendar Running Total or even just worked with ALL(), you know this isn’t Access.  Oh, and you are doing that in Excel pivots, not in a separate window.

There are other benefits of course, for sure, but DAX Measures are a gamechanger.  Let’s leave it at that for now.

And Yes, You Can Replace “Access as Data Source” With PowerPivot*

The two biggest reasons that drove Excel pros into Access in the past are in fact alleviated with PowerPivot.  No 64K row limit in PowerPivot.  No 1M row limit either.  I commonly demo a 300M PowerPivot row workbook on my laptop!  You can load a lot more data into PowerPivot than you can into Access.

And VLOOKUP isn’t something you even need anymore in PowerPivot.  Got multiple tables?  Fine!  Leave them as separate tables, link them via relationships, and you are done.  That’s not even just a convenience – leaving them as separate tables is actually even just better, for many reasons.

All of that “go find me a match in another table” stuff is taken care of by PowerPivot.  Lightning fast in fact.

But whoa there, I put an asterisk on that statement above.  For good reason.

PowerPivot Goes Better with Databases!

Database –> PowerPivot –> Excel = Happy Happy

Fact is, if you work in an organization that uses SQL Server or another industrial strength database product, you are better off connecting PowerPivot to that db.

Or more accurately, you will get even more out of PowerPivot if you have the cooperation of a database professional.

Why is that?  I can (and will) write many posts on that.  For now let’s keep it simple and just point out two reasons:  1) Databases are inherently a very good place to do data “shaping,” which is not something you can do at all in PowerPivot.   and 2) Databases are great places to perform complex row-wise and cross-row business calcs.  They centralize those calcs for re-use, often take the db pro 5 mins to do versus much longer for you, and result in faster and more compact workbooks than if you use calc columns.

I’m not saying you need a db pro to get amazing things out of PowerPivot.  But there’s another level even beyond amazing, and it opens up when you cooperate with a db pro.

Final Note:  Be Thankful PowerPivot Wasn’t Built By Office

I say this because it would have been questioned to death.  It’s hard to imagine, but as a product like PowerPivot is taking shape at Microsoft, no one is really sure how to describe it yet, or even what it’s going to turn out to truly be.  A hundred people in the Office org would have had the same question – are we cannibalizing the Access business, and there would have been as much time spent answering that as designing the actual product.

Now, in hindsight, no one in Office is worried about that.  Access always had a much bigger mission than carrying around data for Excel.  And Access’s current mission has evolved quite a bit from what it was even a few years ago.

But those inevitable nagging questions early on would have saddled PowerPivot with a number of “thou shalt not cross this line” concessions.  Concessions which ultimately were not needed, and that would have hurt the product.

Gives new meaning to the term “Office Politics.”