6.1 Separating Data and Logic


When spreadsheets first appeared, they brilliantly blurred the distinction between programming and information. Spreadsheet users could enter their data and work on it without having to do things like "programming." All the information could reside in a single file, readily shared, and copy and paste functionality along with a few basic functions ensured that spreadsheets were easy to learn. An unknown but clearly vast amount of business decision-making has rested on spreadsheets, and an incredible amount of business data is stored in spreadsheets.

This power has come at some cost, however. While spreadsheets are accessible, their mixing of data and logic has created a few problems. While copy and paste works well for simple spreadsheets, it becomes complicated quickly if, for example, users try to combine logic from multiple spreadsheets. Suddenly development style matters. Spreadsheet software, with its smart copy and paste features and support for multiple workbooks, has done a lot to simplify this process, but the work involved in making these pieces communicate is still very real. Mergers and acquisitions, for instance, often face a serious challenge in reconciling the spreadsheets used by decision-makers at the various organizations.

Even on a smaller scale, the combination of data and logic that make spreadsheets so powerful can create some substantial annoyances. I work, for example, with data I need to analyze on a weekly, monthly, quarterly, and annual basis. I use the same basic logic for all of this analysis. The company I work for makes it available in Excel spreadsheets, generated from a database. I end up with an enormous number of largely duplicate spreadsheets over time, as only the data has changed. There's no simple way for me to aggregate the information from multiple spreadsheets, and if I want to make a change to the logic, I have to make that change every time I download new information. That thoroughly discourages me from making logic changes.

Another cost of spreadsheets is that they act as roach motels: data comes in but it never goes back out to databases, except as spreadsheets. This problem will be addressed in the next chapter.


Excel has addressed these issues to some degree with features like ODBC integration with databases. Instead of storing all the information in spreadsheets directly, the user can specify an area of the spreadsheet to be populated with information from a database query. In places where you trust your users with such access or can provide secure facilities to provide the information, this can be genuinely useful stuff. Users can analyze information using the CPU power on their desktops, customize how they see the data, and manipulate it without ever (hopefully) having to request development of custom processes. They can load new data into their spreadsheets whenever they need to do so, without fear of overwriting the logic they've so painstakingly created.

Unfortunately, that scenario only works for a limited number of cases where users have direct (or nearly direct) access to information. There are many untrusted users, as well as users who travel or are otherwise disconnected. There are lots of users who need access to historical information, and may need to process that information a few times before actually letting it into the final spreadsheet. There are users with intermittent connections, who access their information through things like web servers and file servers.

In these cases, using XML as a base format for data works very nicely. XML files are self-contained, and are easily sent as attachments in email or loaded from a file or web server without any special infrastructure. Instead of users having direct access to a database, they can be given access to copies of the parts of the database that interest them. If users want to tinker with the data for forecasting, for instance, or just to make themselves feel briefly better about their results they can tinker without having any impact on the original data source. Users who want to aggregate information from multiple data sources can do so using either Excel's own tools or the wide variety of XML-processing tools available.

Users can also treat Excel as a tool for creating and manipulating XML data, provided that the data structures fit neatly into Excel's expectations of columns and rows. While Excel is in some ways a more limited XML editor than Word, it also provides a much simpler interface, one that is easy for users to set up and use themselves.



Office 2003 XML
Office 2003 XML
ISBN: 0596005385
EAN: 2147483647
Year: 2003
Pages: 135

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net