Rob Collie, chief technology officer at software company Pivotstream, continues his new series explaining how you can do more with Excel through an exciting add on program from Microsoft: PowerPivot.
In my introductory article last month, I explained PowerPivot’s unique hybrid position between IT driven business intelligence and knowledge worker driven spreadsheet intelligence. In parts two and three, my goal is to illustrate the ways in which PowerPivot saves time and money tangibly while improving decision quality and speed.
This is best explained by studying a real world BI project (see below) in a ‘before and after’ sense. Fortunately I can speak from firsthand experience of building the exact same project twice: first using traditional BI tools/methodology, and then several years later with PowerPivot.
Let us explore the ‘before’ first.
Traditional methodology
While working at MSN/Windows Live I was given a USD70k budget for a BI project. I hired an excellent external BI consultant to do the work, while I served as requirements owner and eventual analyst/report writer.
Like most similar BI projects it consisted of two phases. First to clean and collect the data into a unified ‘data warehouse’. Second to build an OLAP cube – a special kind of database optimised for reporting and analysis – on top. It was scheduled to run for 14 weeks and consume the entire USD70k budget (naturally).
Requirements communication: hidden costs
I didn’t realise until I later rebuilt the project using PowerPivot that the financial cost of the consultant was only a fraction of the project's true cost. As he knew nothing about the subject matter we were dealing with, I was required to be almost as heavily involved during those 14 weeks. That equated to four weeks of time burned by someone with other responsibilities.
If you just count salary and benefits, those four weeks cost around USD10,000. If you factor in lost revenue that I otherwise could have generated as a software engineer, it is at least another USD20,000.
Furthermore, requirements communication consumed two thirds of part one. The first two weeks were spent almost exclusively on specs, and about half of the following four weeks on refinements, corrections and clarifications.
Finally, the data warehouse was overbuilt. I estimate that at least 30% of the resulting database was never used once, either in the cube or in reports.
This is a common problem when asked to anticipate future requirements long before they arise. Questions like ‘will you ever need to do X?’ usually get answered with ‘yes, we might,’ just in case. I knew I would not receive additional future budget.
Part two saw a continued heavy focus on requirements communication. Throughout the eight weeks, I estimate that each of us were engaged on requirements about 50% of the time, incurring another USD10,000-USD80,000 in hidden costs.
Again, I ended up requesting capabilities in the OLAP cube that were never ultimately used. Likewise, as soon as I was able to start working with early versions of the cube, I identified problems resulting from omission or miscommunication (on my part) or misunderstanding on the part of the BI pro, as well as software bugs. The resulting iterative feedback loop was more important than the original requirements communication.
Almost inevitably budget ran out before needs were met. For every need I thought I had but never materialised, there were two legitimate needs I did not anticipate until my budget was gone. Given the complexity of the toolset, there was no opportunity to address those ‘missed’ needs myself – I was stuck.
Traditional BI: hidden costs and pitfalls
I learnt three key lessons. Firstly, knowledge workers are a huge uncosted component of a BI project. They spend from half to two thirds as much time on the project as BI pros themselves, with true costs much higher than their salaries and benefits. Yet limiting their involvement does not save costs either. If ultimate consumers of a BI system are not involved in its creation, the result will be ineffective.
Secondly, ‘waterfall’ planning is especially dangerous in BI. Believing you can do all your planning up front and then switch to execution is pure hubris. A BI project is meant to answer people’s questions, which are difficult to predict and change. I would suggest that people cannot know the ‘right’ questions to ask until they’ve asked (and received answers to) ‘wrong’ questions). A consequence of this is that such projects can be both underbuilt and overbuilt.
Lastly, going forward, your budget is used up, the project is over and the BI pro moves on, leaving unmet needs which are rarely revisited. The consequence is that the knowledge workers are left with a system that they themselves cannot extend properly.
In part three, I will explain how PowerPivot transformed this project when I revisited it in 2010.
Links
Part one of excel extras
Spreadsheet skills
CIMA professional development
CIMA on demand