There is a good reason why there are two indispensable application in Microsoft Office – Word and Excel. They are simple to learn, extraordinarily powerful, and ubiquitous. But there is a major difference in the use of these two packages. Essentially all documents are the same, while every spreadsheet is different.
This is because you can use spreadsheets for a staggering variety of jobs. Some, like making a list of your expenses, are no more than useful aides, and could be done (perhaps not quite as conveniently) using other means. Others (such as the lists of contacts that some salesmen keep in spreadsheets) are misuses, as they create a personal archive of data that should belong to the business. But the great value in spreadsheets is where they are used to express key information for a business, often drawn from many different sources, in a way that allows managers to make decisions. This makes spreadsheets indispensable to directors, forecasters and marketers.
But the more the business relies on spreadsheets, the more difficult it becomes to keep control of the data. There are more spreadsheets, the spreadsheets get bigger, the data in one spreadsheet is used in other spreadsheets, pivot tables are created and the data from the pivot tables is used in further spreadsheets, leading to a tangle of logic that can be impossible to track back to source. And even if the source is found, what confidence can there be that the data is the correct data? Does it contain last week’s figures? How was it originally entered into the sheet?
The entire problem is caused by the inability of spreadsheets to separate the data from the logic. The great strength of a spreadsheet is that you can use it to express complicated data relationships in a way that is concise and as easy to understand as is possible. But it you can set up a single spreadsheet with the formulae you want, why should you not be able to use that spreadsheet for multiple different data sets?
The answer is both simple and ludicrous at the same time. The reason why you cannot easily separate the data from the logic in a spreadsheet is that the data does not easily conform to a standard relational database. Such is the ubiquity of the relational database that everybody thinks that this is the ‘natural’ way to store data. Wikipedia states that a ‘database’ has ‘tables’. But this applies only to relational databases. Of course some clever people work out that you can use ‘multi-dimensional cubes’ to store specialist data for analytic purposes, but that is considered a niche way to handle data with limited (but sometimes very important) use, and Wikipedia does recognise various ‘database models’, but none appear to have any bearing on spreadsheets
But we like spreadsheets precisely because they show data in a way that is clearly understandable. If it is easy for us to understand, then why should not a computer use the same logic as us? If we understand that a particular cell represents the sum of a number of items of data that fit the headings for that cell, then that should be easy for a computer. The only information required to specify a cell should be the relevant heading information and any additional contextual information (in Excel terms – any ‘filter’) that might apply.
I have written before that I find it astonishing that Microsoft has not considered the idea in the paragraph above, and acted on it, as the implications are obvious – that we need to reconsider the way we store data for spreadsheets in such a way that it mirrors the way we think about data. If we do this, then the instructions required to load data into a spreadsheet should be simple, and any changes we might make to data within a spreadsheet can be correctly stored – simply by attaching the ‘obvious’ label to the data we store.
Azquo does this. And, to give the data some authenticity, we also attach to each item of data the name of the person or source from which it originated, when and where it was entered. This is the best audit trail that can be provided, and it’s not even difficult to provide, so it must simply be collective blindness that nobody but us seems to have come up with this idea.
To see a more detailed explanation of the Azquo database, please see here