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:
- Data-shaping – much easier in a database than in basically any other tool, this is the first benefit you will see.
- 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.
- 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:
To do that, you need a calculated column in your Customers table:
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:
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.
