There is an ongoing debate in Business intelligence and Analytics circles about what role spreadsheets have in data management.

At the extreme pro-spreadsheet side, Excel can fix all, be all, and IT departments should just be disbanded, because the world can run on workbooks and macros.

At the other end of the spectrum, all that is evil and wrong, all the corrupts data quality, all that is unholy in the realm of data management process and master data management can be traced back to renegade spreadsheets, the “culture of excel” and spreadsheet applications should be uninstalled from every desktop never to be seen again.

And of course, in the middle, in the real world, we all use excel constantly, every day, all the time for lots of things.

Some organisations do use it too much, others probably spend way to much money on their business intelligence solutions because they don’t let their users use it enough (although fewer of these than the first sort is my guess).

What is my take on it? Excel is a fantastic scratchpad, and a powerful reporting tool. It is not, however, a data repository. The official version of the data needs to be in a database, managed by someone who knows data, data models, and master data management.

But if you are looking for a quick tool to create dashboards, reports, and give people easy access to data- don’t discount Excel.

This is why we’ve recently greatly enhanced Datamartist’s ability to read and (more importantly) generate Excel workbooks. The fact is that excel is an application that is installed on every machine, is familiar to many, and is an excellent way to deliver data. Not store it, but deliver it.

The challenge that many Datawarehouse projects have faced is that they are competing with all the excel spreadsheets that circulate through the typical organisation. The business intelligence team is hard pressed to deliver new reports as quickly as they are demanded- and as a result people bypass the system and create a web of spreadsheets that become the “real values”.

How can you get data out of databases and into excel? How can you export from SQL Server to excel, or oracle to excel, quickly and easily? Datamartist provides a way to build reusable, automated queries and transformations from multiple databases and files and to generate excel workbooks with graphs and tables in an automated way.

You can run Datamartist as a scheduled job, generating the excel workbooks you need regularly, so users come to understand that while the spreadsheet is useful, it is ephemeral. The master copy of the data is in the datawarehouse. Excel workbooks are reports. But because they are available quickly, a hybrid solution of the core data warehouse and excel reports can deliver better data in a format users are comfortable with.

What are some of the things excel does particularly well?

• Good graphs, flexible, easy to set up and format
• Excellent pixel by pixel formating control- ability to just get what you want in terms of look and feel- add in images, tables, anything
• Great integration with MS office (obviously) so going into Word or Power Point is easy
• Great control of printing/formatting- ability to generate PDF by installing a print to PDF capability
• Incredible adoption and broad compatibility- chances are, whoever you want to share with has excel.

So don’t be an extremist on either end of the spectrum- don’t think for a moment that “just spreadsheets” will solve your data management needs- on the other hand, don’t be an excel bigot- excel has a roll to play, and in fact, can be a tool to drive adoption of your data warehouse and master data management process.