Business Intelligence Network - The Vision for BI and Beyond

How to “Excel” in Your Business Intelligence Environment

Published: December 7, 2004

Mastering spread mart chaos.

Let’s face it. Whether we like it or not, Excel is the ubiquitous business intelligence tool of choice by most of our business users. Why? Because it is easy to use, has great charting capabilities, permits endless “what if” analyses, integrates seamlessly with other Microsoft Office productivity and communication tools and resides on virtually every business user’s PC already.  It isn’t going away any time soon.

Unfortunately, it is also largely responsible for “single version of the truth” failures in business intelligence. It is the source of the most inconsistent, un-auditable, and unreliable data we can generate—and the most relied upon to report the health and well-being of our corporations! So, given this situation, what are we to do with the inevitable “spread mart” chaos that ensues from Excel’s usage? Unless you can restrict Excel’s utilization because of strict regulatory or compliance restrictions in place, you must ensure that your environment not only supports Excel but actually controls its usage, thus guaranteeing the so critical single version of the truth.

To do this, it is necessary to understand the range of support available from today’s business intelligence tools. At the low end, the tool allows one time extractions of data only with no restrictions, no connections back to the centrally maintained repository of data and queries, and no audit trail available. At the high end, the more sophisticated support where the business intelligence tool tracks everything extracted into Excel including ownership, formulas, formatting and even alterations made to the data in the spreadsheet.

Given that gamut of possible Excel support, there is no one single trait you can point to that guarantees a business intelligence tool will maintain the truth once the data is populated into the spreadsheet; it is a combination of features and functions that must be provided as well as your own internal processes in place that will control the chaos. To make sure you can do as much as you can, let’s look at some requirements to consider when selecting your official business intelligence access tool.

1. Tracking exports—The ability to “follow the data” from data mart or data warehouse to spreadsheet is the first requirement that should be considered. Can your business intelligence tool inform you of the number of exports to Excel that have occurred and by whom? This seems to be one of the biggest challenges corporations face in trying to ensure the version of the truth found in the business intelligence environments. If an export appears to be done on a regular basis or by a significant number of users, then the tool should report back that perhaps it is time to put the export into some form of scheduling mechanism (see next requirement) provided by the business intelligence tool.
2. Scheduling of exports—The ability to automatically export data on a scheduled basis from the mart to the spreadsheet is a real plus. Several business intelligence tools have some form of scheduling capability so make sure you get one with a fairly robust scheduler. You may want the spreadsheet to be created on the server and then be distributed through some form of report distribution mechanism (e.g., publish and subscribe) too. Most of the top business intelligence tool vendors support scheduling to some degree. Pick one that fits your report distribution environment nicely.
3. Expiration Dates—One way to control proliferation is to include a “time bomb” or expiration date along with the exported data that either indicates when a spreadsheet is no longer valid or that actually causes it to be deleted upon reaching that date. This may be more of a future feature for some access tools but it is one that I know several vendors are thinking of including in their next releases.
4. Limiting what is exported—Due to a lot of security and privacy issues today, it is necessary to ensure that the business intelligence tool does not simply dump all the data requested into the spreadsheet. Many also export all of the detailed data rather than allowing some form of filtering or selection to occur (e.g., exporting a subset of sales data for a particular geographic area rather than all data from all areas). Make sure you or your users can put limits on the data that is being exported.
5. Exporting more than just data—The business intelligence tool should export not just the requested data but should also export significant information about that data as well such as formatting instructions, any formulas used, and charting instructions. For example, Cognos’ ReportNet exports a chart into Excel that can then be further manipulated. In addition, look for the ability to include hyperlinks to other reports or sub-reports in the export. This is something that Microsoft's Reporting Services includes in its feature set. You can click on the hyperlink and bring up the sub-report with all the proper filtering in place.
6. Linkage back to the mart—Some business intelligence tools today like Microstrategy’s Office product provide a link between the spreadsheet and the underlying data mart supplying its data such that when the data in the mart changes, the spreadsheet also gets refreshed. This may also include updates to the formats, formulas and charts as well. There may also be direct links between Excel and the data mart. For example, Business Objects’ Business Query permits users to query their universe directly from a spreadsheet.

These are some of my top requirements to look for in selecting the right business intelligence tool for your environment. Obviously, you must also make sure your business intelligence tool will support the various versions of Excel that you have throughout your company. Not all versions are supported by the various business intelligence tools, so do your homework here.

Finally, you should also look to the future a bit and examine the ability of your business intelligence tool to interplay with all the other MS Office products such as PowerPoint and Word. We focused mostly on Excel but it should be clear that, while Excel is one of the most prolific tools for communication in corporations, it is not the only one. The ability to export charts, pictures, tables, etc., directly into Word and PowerPoint is becoming a mandatory feature as well.