skip to Main Content

 
I don't think the artist had data apps in mind, but this pic rocks!

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:

Access Versus PowerPivot

OK, so what’s the answer?

PowerPivot Does Things That Access Will Never Do

You had me at DAX MeasuresThe 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!

image

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.”

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 7 Comments
  1. As a consultant I really appreciated this post!
    I am a what you would refer to as a future db pro, and currently I am investigating and trying to understand how to deliver PowerPivot to a client and still convince them that I am needed. Do you see the conflicting intentions here?
    One thing I realize is that I need to improve on the softer skills in order to go “beyond amazing” here. Sharpen the business konowledge a bit. For example, learn the client’s intdustry, walk in their shoes for a while, etc.

  2. Hi Rob,

    After the diagram “this is what they see at a high level,” it would be useful to have another diagram showing the true relationships (i.e. “what they *should* be seeing at a high level.”).

    The new diagram,for example, would show blocks representing Access and other databases (even an Atom feed). Import arrows would extend from the data sources to PowerPivot. The PowerPivot block would be superimposed on the Excel block. This new diagram will clearly show that:

    1) PowerPivot needs an external data source, so isn’t a direct substitute for Access. It can, however, *use* Access as a data source.
    2) PowerPivot is designed to work with data from multiple sources simultaneously.
    3) PowerPivot isn’t a separate App like Access, but is sitting on top of Excel and taking over the external data import duties, i.e. Excel doesn’t externally query PowerPivot data, as implied in the “what they see” diagram.
    4) PowerPivot is extending the capability of Excel somehow, but exactly how won’t be obvious in the diagram without some explanatory notes.

    Colin

  3. PowerPivot (as I understand and have seen in my experience) does work well with a table that is connected to another table that is connected to another table that is connected to another table.

    For example, let PKA is the PK in Table A, PKC is the PK in Table C, and PKA and PKC together make up the composite PK in Table B. Further, let PKE be the PK in Table E and PKC and PKE make up the composite key for Table D. In PowerPivot, even if you create the diagram just as you should there is no way to see a join between Tables A and E even though they are clearly related.

    Consider a bill of materials. That fits the above problem perfectly. If I am wrong, please show me because I would love to do it in PowerPivot.

  4. I personally switch between Excel, Power Series, and Access on a regular basis. here are a few reasons for using Access that many skip. Excel and Power Series don’t scale on the Data Collection side of the equation. I’m working on upsizing an application (upsizing?) to Access Services. Why because I don’t wish to have a worksheet to collect every project that I’m collecting data on. The original spreadsheet was great for piloting but when you get to 40+ projects its a mess. What would be a great idea is for a stand alone DAX engine that could fit between Access, Access Services, Excel, and Power BI. That way you could use the same data shaping which ever data source you had.

  5. Worked on a project using VBA where I automated Excel pivot tables, splitting out reports then send various email with attachments with a single click of a button. Thought I could scale this up to accommodate future grow of data, so I turned to Power Query and PowerPivot. Well after all the prep works done in PQ and PV, I run my code and it failed. Spent quite sometime and figured out VBA does not work with PQ or PV. While PQ and PV are powerful tools, its short on office automation. Does anyone have a workaround or do we have to learn the M language.

  6. I think this is asking the wrong question, but in 2011, it was the only question to ask. Today it is between Access and Power Query. I have used Excel+Access since Office 5 (back when it was Excel 5 and Access 7 I believe) in the mid-90’s. Once Power Query came on the scene, I was able to do far more data modeling in Excel, and do it both faster and with less hassle.

    I still use Access when I need to store data, but I no longer put Access between Excel and larger data sources like SQL Server.

    And when I have the data like I want it, then I reach for Power Pivot to start reporting.

Leave a Comment or Question