skip to Main Content

 
image

Think of a Database as a Bodybuilding Supplement
for Your PowerPivot Regimen

In part one I covered the following three benefits of pulling data into PowerPivot from a database, as opposed to using other sources such as Excel itself:

  1. Data-shaping – much easier in a database than in basically any other tool, this is the first benefit you will see.
  2. Auto-refresh – by removing manual data shaping from the system, you can then rely 100% on PowerPivot’s server-side automatic refresh to keep your reports up to date.  The first time in “Excel history” that Excel pros aren’t slaves to their own spreadsheets.
  3. More quality, less errors – the combination of PowerPivot’s “portable formulas” and performing your data shaping in a database greatly reduces the opportunity to make mistakes.

OK, time for benefits 4-7.

Benefit #4:  Complex Calc Columns

Let’s say you want to stamp your customers into quintiles – the top 20% in terms of dollars spent, the bottom 20%, etc.

And you don’t want that to be dynamically re-calculated every time you slice or filter a pivot – you just want it statically calculated according to total overall sales, so that you can then use the quintile as a slicer or row/column axis in your pivot.  You want to be able to offer a pivot report like this:

image

To do that, you need a calculated column in your Customers table:

image

But that’s a reasonably tricky calculated column, as it has to be calculated per-customer, against their matching rows in the Sales table, and then ranked against other all other customers.  If you do that in Excel, you’ve introduced another manual step which foils auto-refresh and in turn introduces more chances for error, etc. 

And doing it in PowerPivot is awkward, at least in v1.  I’m not saying you can’t do it in PowerPivot, because you can, and that DOES auto-refresh.  But it’s just so much easier in a database, if you have someone around who knows databases, and there are more complex examples than this one which get even harder in PowerPivot.

Benefit #5:  Centralized, Re-Useable Logic

This one is both its own distinct benefit as well as a multiplier for other benefits (such as data-shaping and complex calcs).  Anything you do in Excel is manual.  Anything you do in PowerPivot is “tied” to the workbook in which you did it.  But logic that you put in a database can be re-used across many different workbooks.

Which, of course, is very nice.  No need to re-write it every time.  No chance that it accidentally diverges from other models (a source of error).  And if you need to modify that logic in the future, you only have to do it in one place.

At this point it’s worth pointing out that a PowerPivot BI environment DOES share some characteristics with a traditional BI environment.  These db-centric benefits are a hallmark of traditional BI, and these are the things you want to preserve from the traditional approach.

Benefit #6:  “Magic” Sliding Windows and Parameters

This is one of my favorites.  Oftentimes, an organization operates according to a calendar, or even multiple calendars, that are not quite the same as the calendar hanging on your wall.  And that leads you to a place where your measures like “growth versus prior year” can get pretty complex.

I won’t go into great detail here because it would take awhile, but imagine a slicer driven from a table that is purely calculated in the database before input:

image

To get a sense of some similar techniques, which revolve around using “unconnected” slicer tables as inputs, see this post and this post.

Benefit #7:  Compression

It’s a fact:  imported columns compress much better in PowerPivot than calculated columns.  So if you have a choice between doing a calc column in your db (and then importing it) or importing a table and then adding that calc column via a DAX calc column in PowerPivot, DO IT IN THE DATABASE!

Better compression means smaller files, less memory consumption, and often faster response times to slicer clicks.  (Note that the bigger the table, the bigger the difference you will see).

Again, deserving of its own post in the future.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 2 Comments
  1. Hi,

    THANKS! should have read this post a few years back….:) I found this out the hard way. Sometimes I felt alone, but now I know there are more people like me :). And more importantly, the way I work now (enriching the data in Acces first, before exporting to Excel) is not stupid after all.

    —> An additional plus I would like to add is that building the relationships in the data base gives more functionality and checks (I use Acces). The great thing is, that these relationships are also imported into powerpivot.

    I use an Acces database. This gives standard queries to identify double records and unrelated records. In my work as a financial controller with a SAP system, this is very important.

    Personally, I like to do a lot of calculated fields in Acces. Getting more familiar with DAX, this could become less important. On the other hand, ACCES handles these fields well. I am curious how my (and your) preferences will evolve. (wait, Page 18 of your books gives away the answer)

    I do not know what MS will be doing with Acces in the future, but for me a a financial controller with a SAP ERP system, using a lot of look up tables, the combination of Acces and Excel is a very strong combination.

    Kind regards from the Netherlands,

    Lennart

  2. Hi Rob, your the best!

    Quick question on the image table for Benefit #4: “calculated column for stamping customers into quintiles”
    Does the image data reflects the real data output and if yes which version of adventures works was used as I seem not to be getting similar output.

    Hope to hear soon.

    Thanks

Leave a Comment or Question