I got into a bit of a disagreement with someone today over the idea of Pre-Aggregation of data before bringing it into PowerPivot in Excel as opposed to bringing in all the source data for all the tables to be analyzed and then doing all the necessary aggregations exclusively in Excel using PowerPivot.
Let me make it perfectly clear that I am not pleased that there has to be a disagreement .. the fact that it looks like it is becoming one is sad. Unfortunately I feel the need to aggressively promote what I believe is true because there are people who are in my opinion limiting the prospects for PowerPivot and Excel by their promotion of a limited vision for PowerPivot. I know in their hearts they believe they are right – But I beg to offer an alternative view. I believe that those who do not share my vision and I are discussing 2 separate uses for PowerPivot and thus there is less disagreement than there would appear to be.
Here’s the nub of this thing … I have a vision of PowerPivot as a “Report Generator”. I am personally not especially interested in the use of PowerPivot for ad-hoc analysis – the proverbial “ad-hoc slicing and dicing” of data. Oh sure I do that from time to time but mostly I develop databases for clients and the reports that they need to see on a regular basis for management. I have found PowerPivot when combined with VBA to be an extraordinary Report Generator – second to none IMHO. I think people will benefit from knowing how I am using PowerPivot. Frankly, if I was to keep it to myself (which I could), then I believe that MY business would suffer. I believe we all would miss a BIG opportunity to get more companies using PowerPivot ever day.
I have a long experience with databases (for what that’s worth) and one of the most important things I learned a long time ago is “You do not want to accumulate multiple copies of your data all over the place!” In a perfect world there should be only one version of the “Raw” truth. Yes Corporations have moved to a Data Warehouse model for the most part and that’s awesome but to me that’s where the raw data to be used for analysis should stay. This is important for several obvious reasons that I don’t need to go into here.
One of my big problems with PowerPivot, especially the Cloud versions of PowerPivot within PowerPivot SharePoint services and now PowerBI, has always been the fact that it ends up creating it’s own private data warehouse in addition to existing Corporate data warehouses, to say nothing of the data stores of the applications themselves that create and manage the data through business activity.
Now that does NOT mean that I do NOT believe in the value of the functionality of Power BI and Microsoft’s Cloud offering for data storage and use in Browser-based Analytics or as a source for Client-Side Analysis. I do, however, believe that I would rather store pre-aggregated and somewhat pre filtered data “up there” if necessary. In this way I would not have a Data Warehouse but rather a “Reporting Data Store for PowerPivot Reports” all of which would be based on a moment in time – but would only include the smallest amount of data necessary.
I have also found that by using my technique with VBA I can develop powerful Client-Server solution against SQL Server (or other database servers) without going through SharePoint or PowerBI. But by using Pre-Aggregation and the power of the database server I can deliver solutions that refresh in seconds and that are small enough files that they can be distributed in “old-fashioned” ways if so desired – or simply published to PDF files and then thrown away. I have trouble seeing what is wrong with that.
When I use the techniques I have been promoting here, I am able to source data from as close to the source of the data as possible, in a timely fashion through the Client Excel, thus removing the need for all the overhead and complexity of publishing and managing my reports in the Cloud and not recreating yet another data warehouse with it’s attendant issues around timeliness, maintenance, access and management in general. I can create a stand-alone solution that I manage and that I can make as secure and reliable as I can. This way MY users will get exactly what they want when they want it and can rely on that…..
I do believe also that there are a LOT of developers of Corporate reports that this is all they really want to do.
I must also add that this in no way means that I think any less of the capabilities of PowerPivot because I am promoting an alternative way of using it. Quite the contrary – it is because I see what PowerPivot is capable of that I am promoting this as yet another way to use the product, to get better use out of Excel and to give our customers (internal or external) what they REALLY want … not what I think is good for them.
Why this has to create so much negativity from certain quarters disappoints me no end. And this from people I call friends or at least friendly fellow members of a community that wants the same thing in the end. We’re all trying to do the same thing … provide better solutions for our customers to get better results with their efforts – and to get paid for doing so of course :-) … but paid for good results! I believe that PowerPivot is THE tool to allow us to do that.