Rob Collie, chief technology officer at software company Pivotstream, continues his series explaining how you can do more with Excel through an exciting add on program from Microsoft: PowerPivot.
In my previous article I described how a 2006 BI project, which used traditional methods, taught me:
1. BI projects demand nearly as much time investment from the knowledge worker ‘customers’ as they do from the BI professionals tasked with the implementation itself.
2. The actual cost of BI projects is more than what is actually accounted on the books.
3. Communication comprises the bulk of the hidden cost (and duration) of BI projects.
Now I will explain how, in 2009, I personally re-implemented the second half of the same project using PowerPivot, and the efficiency gains surprised even me.
Not so great expectations
I was part of the Microsoft engineering team for PowerPivot at the time, and 13 years at MS had taught me that ‘first version’ products almost never deliver on their full promise.
I started the 2009 re-implementation with the hope that I could replicate the cube that the BI consultant and I built in 2006. To deliver the same results that two of us had achieved collaboratively, on my own, was a 'stretch' goal.
The actual results were a shock:
1. I re-implemented 100% of the 2006 project, successfully, on my own.
2. The implementation process was much faster.
3. I also found myself branching out and adding features that were never considered in the initial project.
That knowledge workers armed with PowerPivot can truly deliver industrial-strength results is an absolutely necessary quality, and if that was ‘all’ PowerPivot did, the ROI would still be dramatic. (This is reinforced for me every day in my new career, as evidenced by this case study.) Yet the ROI benefits are even greater.
Significant time reduction
One of the first measures (formula-driven metrics) we implemented in 2006 took about a week. Back and forth iteration consumed the vast majority of that time, as described previously.
The exact same process took me one hour in 2009. But technology was not the reason a week became an hour. The crux is this: a human being communicates with him or herself, in thoughts, MUCH more rapidly and accurately than two human beings can communicate with each other.
This phenomenon plays out every day, for all of us. Consider business meetings, and how much time they consume to achieve results that can often be summarized in a few bullet points. Interpersonal communication is just orders of magnitude slower than individual thought.
Now let’s consider how that impacts a traditional BI project:
In 2006, both people involved knew our respective domains extremely well and were incredibly efficient on our own. As it stood though, in order to do anything productive, we were bottlenecked on communication between ourselves. Not even the simplest detail could be accomplished until we effectively merged our understandings. As the charts indicate, that was where most of the time was spent.
Process flow of a PowerPivot project
Here are the 2006 process diagrams from part two, translated into a single diagram illustrating a well-run PowerPivot project:
Important points:
- Only build enough data warehouse to get started. Communication of requirements at a high level like that takes a fraction of the time that it does to plan for everything. If you omit critical needs you can revisit those later with a much better-informed perspective.
- Get the knowledge worker started. As soon as that 'foundational' data warehouse is ready, turn the PowerPivot author loose on building reports. As part of that process they will be building cubes, but PowerPivot makes that a seamless part of report building – the desired result drives the underlying implementation, not vice versa.
- Because much of the implementation is performed by the same brain that knows the business and its needs, there is much less reliance on interpersonal communication.
- Iterate! When the PowerPivot author needs something added or modified in the data mart, they are capable of providing very precise requirements. In two years of applying this system, we’ve found 99% of requests take only minutes for the database professional to implement.
ROI: before and after PowerPivot
Let’s summarise the costs and benefits of the two project approaches:
Links
Excel extras: introducing PowerPivot
Excel extras: the hidden cost of traditional BI projects
Spreadsheet skills: naming names
CIMA professional development
CIMA on demand