BI Tools v. Excel

When I think of BI tools, I'm thinking of something specifically built to sit on top of a data warehouse and pull the data in a structured way, like Business Objects(R) or Cognos(R). There are among us "BI Snobs" who think that Microsoft(R) Excel(R) is bad and who would like to stamp out spreadsheet use. These are probably the same people who think Microsoft(R) Access(R) databases need to be stamped out in the corporate environment, but I would tend to agree with them on that point, which is a post for another day.

When I worked on a data warehouse team, part of my job was selling the value of the data warehouse and BI tool we were charged with implementing. But I admit that even I would get to the end of the flexibility with my BI software and resort to converting my report to Excel to complete my analysis. I was a closet Excel lover, because like many other business people, I was raised on Excel and tended to think about analysis in this very conventional, two-dimensional fashion. I'm sure I'm not the first person to write about the pros and cons of Excel v. BI tools, but please indulge me for a minute, since that's what I'm thinking about this morning.

See, I don't believe that there are two types of people in this world: BI (Business Object, Cognos, or whatever) people and spreadsheet people. I believe it is possible to use both technologies as long as you are smart and aware. The big issue with spreadsheets is that they are very prone to error, accidental or otherwise. Spreadsheets can be used to "spin" the truth and make the results seem different than they are, to one's benefit. When using your BI tool, however, you are pulling from your warehouse calculations that have been (presumably) previously vetted by a team of business and technical resources and have been agreed upon by everyone as the "one version of the truth." But BI tools will never be as flexible as spreadsheets and spreadsheets will never be as powerful as BI tools at handling massive amounts of data and drilling down.

The other problem with spreadsheet use is that once you convert your data out of your BI tool, you lose the ability to drill down to the detail, which eliminates much of the value of your data warehouse. The best thing I can suggest for this is maintaining documentation of the BI report from which your spreadsheet originated, so you can track back to the underlying data when necessary.

Also, as I mentioned before, Excel encourages thinking about analysis in a conventional, two-dimensional approach, where a dimensionally-modeled data warehouse has your data organized in a much more efficient, multi-dimensional way. So you may be shortchanging yourself and adding steps by forcing the data into a two-dimensional format. If that doesn't make sense, just ask me and I'll try to explain it more clearly.

I may be stating the obvious, but I don't believe the BI tools will ever be successful at stamping out spreadsheets, nor should they be. As for the accidental errors that are easy to commit in spreadsheets, the best thing to do is create a lot of checking calculations (usually calculations that should equal zero) and be thorough at going back over your formulas and making sure they are accurate. Run BI reports to compare to your spreadsheets to make sure they pass the "sniff" test. And once you feel good about the integrity of your spreadsheet, lock out the cells that should not be manually entered. I also visually indicate which cells have manual entries, in order to make very short work of figuring out where errors may be, once I have built my model. Use both technologies, but be smart and thorough about how you use them and there will still be a place for spreadsheets in the world of BI.