At Simba we often hear the term “spreadmart”.   This informal term, even defined on Wikipedia, refers to spreadsheets of Business Intelligence (BI) and other important data, each of indefinite date/origin/modification being distributed around an enterprise.  Many BI infrastructure groups try to stamp out spreadmarts by purchasing and training users on an expensive, end-to-end BI system.   In the end though, someone, even as high as a CFO will have to cut and paste the resulting data into a spreadsheet for a monthly report.

One solution to this problem is to embrace the concept of refreshable spreadsheets.   When you import data into Excel from a data source (OLAP cube or relational database), it is best to do it with a direct connection to the data source.   This is preferable to first doing an export from the database in some form that can be imported into, or cut and pasted into Excel.   The advantage of the direct connection is a user can open the spreadsheet and click on Data > Refresh to get the latest data from the source.  In contrast, a non-direct mechanism means the data goes into the spreadsheet just once and is immediately stale and untraceably-sourced.

In Excel 2007/2010 you can simply use Data > Get External Data > Other Sources > From Data Connection Wizard to create a Microsoft Office Data Connection (.odc) file and populate your spreadsheet.   Once the .odc file exists in your My DocumentsMy Data Sources directory you can re-use it for other new spreadsheets using Data > Existing Connections.   (Hint:  Always carefully set the “Friendly Name” field when creating a connection, as that is what shows up for future use in Existing Connections.)

To standardize where data is coming from when you pass such a spreadsheet around, ask your network administrator to put the .odc file on the network.    Then  re-target your spreadsheet to use the .odc file on the network rather than your home directory.  Now when you mail the spreadsheet around, everyone gets access to the same, current “one version of the truth”.    Not only that, they too can create new spreadsheets from the same interesting source (specified in the network-located connection file) by using Excel’s Data > Existing Connections > Existing Connection on the Network list.