Best practices for using spreadsheets as BI tools
By Boris Evelson
We all know that the war of fighting the proliferation of spreadsheets (as BI or as any other applications) in enterprises has been fought and lost. Gone are the days when BI and performance management vendors web sites had “let us come in and help you get rid of your spreadsheets” message in big bold letters on front pages. In my personal experience – implementing hundreds of BI platforms and solutions – the more BI apps you deliver, the more spreadsheets you end up with. Rolling out a BI application often just means an easier way for someone to access and export data to a spreadsheet. Even though some of the in memory analytics tools are beginning to chip away at the main reasons why spreadsheets in BI are so ubiquitous (self service BI with no modeling or analysis constraints, and little to no reliance on IT), the spreadsheets for BI are here to stay for a long, long, long time.
With that in mind, let me offer a few best practices for controlling and managing (not getting rid of !) spreadsheets as a BI tool:
- Create a spreadsheet governance policy. Make it flexible – if it’s not, people will fight it. Here are a few examples of such policies:
- – Spreadsheets can be used for reporting and analysis that support processes that do not go beyond individuals or small work groups vs. cross functional, cross enterprise processes
- – Spreadsheets can be used for reporting and analysis that are not part of mission critical processes
- – Spreadsheets cannot be used for any processes that involve systems of record or are part of regulatory reporting
- – Spreadsheets that are used just for reporting and analysis are less of a risk. Spreadsheets that are used as data sources, lists, hierarchies, etc present much higher risk and should be eliminated and replaced with analytical MDM tools provided by most leading BI vendors. At the very least such spreadsheets should definitely fall under a category that should be closely monitored and controlled (see #2).
- Monitor compliance with such policies and guidelines by using spreadsheet management tools from vendors like Compassoft, ClusterSeven, Prodiance and Lyquidity. For example
- – If a spreadsheet based BI app does not fall under any of the spreadsheet management policies you just established (for example, it’s used by a small group in a non mission critical process), it probably only needs to be monitored, but not controlled and managed
- – If a spreadsheet does fall under one of the strict policy categories, you may want to use the tools I just mentioned and other means to enable logging, audit trails, backup / recovery, disaster recovery and other operational risk procedures. For example, if such a spreadsheet uses enterprise standard formula for, say, gross margin calculation, and you notice that someone changed that formula, it’s probably a red flag that needs to be investigated.
- When shortlisting your BI platform and applications technology, give preference to BI vendors that support
- – Exporting reports to the most popular spreadsheet formats. Keep in mind that exporting raw numbers is the easy part. Retaining formulas, links, dependencies and formats is more difficult. Do your due diligence on these items when evaluating your BI vendor spreadsheet export capabilities.
- – Use spreadsheet application as a UI to access and manipulate data from your BI applications. Again, ODBC access to a data source is the easy part. Pulling in and leveraging metadata that you’ve setup in your BI app, and adding additional BI functionality not available in your spreadsheet app, in seamless user experience is less of a commodity.
Did I miss anything? As always, all thoughts and comments are welcome.