Why Excel Is Not an Enterprise Business Intelligence Solution

August 4, 2009
Larry Zagata
Blog

But don’t let the title fool you.

I am not one of those Business Intelligence (BI) consultants who thoroughly bashes and discounts Excel. I think Excel is a flexible, powerful tool that has almost limitless uses and value. In fact, before becoming a convert to enterprise BI, I used Excel to create the world’s greatest spreadsheets. With a bit of tweaking, these spreadsheets could walk outside and pick up the morning paper for me. I used Excel for everything, but I just didn’t know there were problems with what I was doing. In my everyday conversations, I come across folks doing the same thing.

Excel can run a small government, but alas, it’s a fairly lousy BI platform.

The problem is when Excel is used not as “a BI tool” but instead “the enterprise BI solution”. The main concern I have with using Excel for a full-on enterprise app is that it is so easy, so pervasive, that it introduces error far too easily.

What is the first thing someone does when they have a report or query? Yes, they dump it to Excel and start adding formulas, deleting rows, sorting data, setting up custom filters, grouping data, etc. In contrast, an enterprise BI tool controls the manipulation of data. But with Excel, that data manipulation is wide open. Even the best Excel users can introduce errors when sorting or deleting or adding formulas. It doesn’t take much to think you are summing up an entire row of data, but you miss a few things, hit a wrong macro, select the wrong row or mishandle the result and you’re up the proverbial river.

Now scale that to many users, all of whom are familiar with the tool and operating without boundaries or a cohesive business context. That becomes trouble, and quick.

In other words: if everyone is using Excel to manipulate data, write their own formulas, macros, delete content, etc., the probability of error being introduced increases dramatically. This is not just a singular error, either; often Excel spreadsheets are passed around and up through the organization. Meaning: if an error happens with the first user, then the errors are simply compounded as the file goes down the line. Yes, you can lock cells, lock the spreadsheet and provide other file + element controls, but that’s sloppy, prone to inconsistency and doesn’t solve the entire issue.

Also, what about the data sources? Usually a simple spreadsheet is not the biggest concern. The bigger concern is with the data sources being pulled into the spreadsheet. Individuals may be pulling from Access databases, other spreadsheets, etc. What if the data source is wrong? If someone created their own Access database and the data in that individual’s database is wrong, then the information in the spreadsheet will be wrong through however many iterations it sees. There needs to be some controls on the data access, extraction and transformation process in order to prevent errors from occurring this way.

In today’s SOX and compliance-heavy world, it becomes risky to use Excel for planning, budgeting and financial reporting, despite its ubiquity, low learning curve and convenience. Given the potential for error, there is high risk to signing off on financial statements based upon an Excel -driven workflow. Excel can certainly be part of the overall process, but for data and information control and trustworthiness, Excel is not the entire answer. Because it is not an enterprise BI solution.

Today’s BI tools are designed for the masses. If users are properly trained, “pro” BI apps can be just as easy to use as Excel and provide all or more of the features and functionality when it comes to reporting — without the significant potential for error. In fact, today’s BI tools integrate with Excel seamlessly and, given the controls of enterprise BI tools, can be used jointly with Excel in a harmonious fashion. Excel simply becomes another tool in the enterprise BI solution toolbox, which is its rightful place in the BI problem domain.

As is the case so often, the gods live in the details of choosing the right tool for the right job.

If you would like further information on an Enterprise BI strategy or BI applications themselves, please email me. I’m a nice guy and love to talk about this more than you can possibly fathom. I know there is lots of interest (read: questions), and I’ll be happy to lend you my time for an initial conversation.

If social media is your thing, you can follow us on Twitter or become our fan on Facebook. Or, if old school RSS is your gig, you can subscribe here.

Previously by Larry Zagata: