In an earlier blog posting I explained the issues with using Excel to try and analyze data from a firm’s financial management system. The problem is getting the data from the system to the spreadsheet.
There are few choices:
- Connect Excel directly to the tables and download from the database. This is difficult since joining the right tables and using the right indexes probably requires a programmer for everything other than simple lists. For example, a client list with addresses can be done by a mid-level Excel user with a little help establishing an ODBC connection to a SQL database. Try analyzing a 5 year revenue trend by billing attorney, it’s more difficult.
- Most standard reports can be run with an Excel spreadsheet as the output. This is fine, however, if you want to further manipulate the data, add calculations etc. all of these are lost next time you want to run or update the same report.
So, what is the answer? We need vendors to provide an entirely new approach to getting financial information into an Excel spreadsheet, PivotTables, charts and graphs.
Here are the new requirements:
- Existing spreadsheet models. Allow a user to take an existing spreadsheet(s) and insert data directly into specified rows and columns. Allow for multiple insertion points in one or s series of workbooks. CFO’s already have dozens of important spreadsheets that get manually updated each month with information from the financial management system.
- Choose data refresh sequence. Allow a user to specify the sequence of refreshing the data that comes from the financial database. Don’t just refresh all the data automatically when opening the spreadsheet.
- Select data components from a list. Allow a user to build a new spreadsheet, PivotTable or graph by just clicking on a choice of data elements from a list (described in English). The user
- Automatic database attachment. The requirements above should all be available from an Excel Ribbon Bar or Task Pane with a direct connection to the all the firms databases including the data warehouse.
In other words, make all this real simple so we don’t have to be programmers to get information into our spreadsheets.
In my next blog post I’ll describe how new technology such as Service Oriented Architecture and Microsoft Communication Foundation can meet these requirements by exposing valuable queries from existing databases.