When business users are not getting what they want out of the enterprise business intelligence system they very rarely just give up. Successful business people didn’t get where they are by giving up when someone doesn’t deliver something, they take things into their own hands and get it done.

Knowing this, it’s not surprising that a huge amount of data collection, extraction, and transformation happens in Excel spreadsheets, or Access databases that are made without the involvement (and often under the direct scorn of) the IT department in large companies. In my previous life I was in the IT department, and I saw some amazing systems generated with hundreds of spreadsheets and databases. This mix of spreadsheets and databases, created without the involvement of the IT department by power users or external consultants (financed out of departmental budgets) are often referred to as Spreadmarts or Shadow Systems.

For an interesting survey on the subject, take a look at TDWI’s report “Strategies for Managing Spreadmarts: Migrating to a Managed BI Environment”. This report is now a year old, but I’m certain as valid as ever.

The title suggests that the solution is managed BI- I won’t get into that right now, but you’ll notice the study was sponsored by the likes of Microsoft, Cognos, Microstrategy and SAP- so of course the solution is Big Business Intelligence solutions.

But what’s really interesting from the survey, is how the different groups within the respondent companies feel about spreadmarts and shadow data systems. The analysts love them, the executives are unsure, and IT hates with a passion. This makes for an interesting mix.

This is very much what I’ve seen in my experience. IT and the Business are at odds with each other, and senior management is either disinterested or forced to take sides.

Where do I stand? I’m in the “avoid them if you can” camp when we’re talking about a tangle of spreadsheets and undocumented MS Access databases that can be error prone and time consuming. I understand why it’s often unavoidable, but I’ve seen first hand how painful these systems are to maintain.

On the other hand, I don’t subscribe to the school of thought that says “Excel needs to be eliminated- analysts should use the Business Intelligence systems only, otherwise there will be chaos.” Let’s not go overboard. Excel and spreadsheets are useful tools, and have their place. Additionally, I really feel for business users who simply can’t get what they want from the IT departments. I used to be the IT department, and it was frustrating to not have the resources available to build what people needed.

As one of the authors of the above report, Rick Sherman, said in a recent podcast:

“reality is no matter how many IT folks that you have in your company you’re not likely to have enough resources or time to meet every business users reporting or analytical requirements…”

He presents what is a refreshingly balanced approach to Excel. In his predictions for trends in 2009, number 5 is “Excel becomes an accepted tool in a BI portfolio”. He points out that this may not be mainstream in 2009, but I hope he’s right about the trend. A pragmatic, inclusive strategy with more power to the people while avoiding the chaotic side of spreadmarts is where the solution is.