In late 2007, analysts at market intelligence company The Data Warehousing Institute (TDWI) set out to investigate the uncontrolled use of spreadsheets by end-users when undertaking data analysis tasks.
Their findings make for sobering reading: of the 200 companies surveyed, of all sectors and sizes, 90% reported that they were living with the problem of so-called ‘spreadmarts’. And problem it is.
According to TDWI analyst Wayne Eckerson, a spreadmart is “a reporting or analysis system running on a desktop database (for example, an Excel spreadsheet or Access database) that is created and maintained by an individual or group that performs all the tasks normally done by a data mart or data warehouse, such as extracting, transforming and formatting data, as well as defining metrics, submitting queries and formatting and publishing reports to others”.
The concern is that spreadmarts – also known as ‘shadow data systems’ – carry with them considerable corporate risk, he says: “Once created, these systems spread throughout an organisation like pernicious vines, strangling any chance of information consistency and reliability.”
That means that the basis of many spreadmart users’ decisions is flawed. A review in 2005 by KPMG Consulting reported that material errors are evident in 95% of financial models. It also noted that “In spreadsheets with more than 200 lines, the probability of errors approaches 100%.”
However, the risks of uncontrolled spreadsheet use are becoming more widely appreciated. The European Spreadsheet Risks Interest Group, an independent body led by researchers from the universities of Greenwich and Cardiff, alongside the Northern Ireland branch of Information Systems Audit and Control Association), tracks financial scandals brought about by uncontrolled use of desktop analysis capabilities.
Witness the experience of a major
Continued popularity
So why do so many organisations continue to allow employees to import vital corporate data onto their desktops, and to massage and manipulate it at will?
The simple truth is that it’s hard to stop, says Nick Gomersall, senior vice president of sales at The GL Company, a specialist in accounting intelligence software for ERP systems. “It doesn’t take much technology expertise to create an ODBC [Open Database Connectivity] link between an end-user’s PC and their company’s back-end ERP system,” he says. “A simple ‘wizard’ can guide an end-user through the entire process, without them needing to involve their IT department, enabling them to download data and to write data back to core transactional systems,” he says.
This approach comes with certain advantages, he concedes. “Where end-users are already familiar with the applications involved, a solution can quickly be put in place to meet immediate data analysis needs,” he says. In periods where business analysts are under intense pressure to get data and to model it – at the close of a financial period, for example – “it can be far more convenient to access and model data than screaming blue murder at IT specialists to get hold of the management information required on their behalf.”
In part, that’s down to the shortcomings of existing ERP and BI systems, he says. ERP systems are built to handle detailed transactional data held in a relational database format but don’t provide the high-level summary analysis needed for management information reports.
BI tools, by contrast, offer a strong underlying reporting architecture, achieved by ‘de-normalising’ that data ready for analysis – but the scale and complexity of such systems can make them costly to implement and cumbersome to use.
Unmanageable systems
It’s no wonder, then, that desktop analysis remains so universally popular. But, as Gomersall warns, “spreadsheets and databases of this kind are often only partially documented (if at all) and come with little or no security, data integrity safeguards or version control. Because they lack the strong underlying architecture needed to be reliable in the long term, changes become increasingly difficult and eventually become unmanageable.”
According to research by Grenville Croll of EuSPRIG, much of the problem stems from the fact that firms are asking more and more of the humble spreadsheet. They are used increasingly to price and record more intricate financial analytics, often straining the technological limits of the spreadsheet itself. Croll noted that some of his sources had complained that the existing 256-column limit in Excel unnecessarily limits the number of instruments in a financial portfolio, as well as constraining the level of detail in temporal models. He also found that there have been difficulties in spreadsheets of over 50 megabytes, “a size that is not at all uncommon”. Indeed, spreadsheets 20 times larger than that do exist
That raises important questions about data security, too. Shadow data systems residing on PCs and laptops can hold substantial chunks of company information, as well as confidential customer, supplier and employee data. “The access control processes for these systems are often much more lax than for a centralised company database, and may not even exist at all. Physically locating sensitive data on desktop or laptop computers can leave an organisation very exposed if the computer is stolen,” says GL’s Gomersall. Plus, he adds, organisations should appreciate they are only “one hard drive failure away from a catastrophe”.
Direct line
His organisation recommends an alternative approach: “accounting intelligence”. This aims to speed up all financial functions – from month-end close to final regulatory consolidations – by running a real-time process against the ERP system’s operational databases rather than a batch process feeding a data warehouse. By direct processing of data held in an ERP system, a trial balance can be produced in minutes, and in some cases seconds, from the last transaction hitting the database.
There are other advantages to this approach, he says. Accounting intelligence applications include report templates that can be used as the basis for the most common types of report, including balance sheets, profit and loss statements, vendor analysis, inventory holdings and any type of report or analysis across the whole ERP system.
Because the structure of the ERP system is held within an accounting intelligence application, extra fields can be added by selecting them from a menu and new data can be incorporated easily, because the handling of the relevant table joins is automatic. Perhaps most importantly, security authorisations contained within the ERP system are maintained, preventing ‘maverick’ users from accessing data they are not authorised to view, let alone manipulate.
That may well prove attractive to organisations that are today turning a blind eye to their growing spreadmart problem. And the evidence suggests that there are plenty of those about, says Jay Heiser, an analyst with IT industry adviser Gartner.
“IT organisations, which typically do not directly support the use of Excel, are usually unaware of the extent to which sophisticated and complex spreadsheets are being used within the enterprise,” he says. “Unless explicitly directed by a compliance officer to exert more control over spreadsheet usage, most IT managers avoid intruding too deeply into the area of user-developed applications.”
But that blindspot is now something that many feel needs to be addressed.
