Spreadmarts and Spreadsheet Hell - Part 1 of 2

[Calumo]

Spreadsheet Hell, Spreadmarts and Other Data Shadow Systems
A spreadmart is a reporting, planning, or analysis system usually built in spreadsheets (Access databases are also common), created and maintained by an individual or workgroup, utilising large amounts of data that should be stored in a data mart or data warehouse, performing tasks such as extracting and transforming data; defining variables and calculations; and creating reports.

Good or bad, spreadmarts are so common, more than 90% of all organizations have them. On the one hand, we should embrace this as evidence of a business need; on the other hand, we must recognize the problems and risks that spreadmarts can create.

One Version of the Truth
Spreadmart creators use different data sources, calculations, measure definitions and naming conventions based on their view of the business. Each group represents a different point of view and it becomes nearly impossible to deliver a consistent, enterprise view of account, measure, customer, product and so on. Spreadmart silos undermine cross-departmental and business synergies as well as economies of scale.

Risks of Errors
With only the business need in mind and no information management rigor, problems can arise:

• Data is often entered by hand leading to errors that can go undetected.
• Users may create poorly constructed calculations, lookups and queries, also resulting in undetected errors.
• Embedded logic, complex macros and hidden worksheets are understood by few and difficult to maintain.
• Spreadmarts usually do not scale beyond a small workgroup.
• Spreadmarts may generate system and data errors when they are linked to other systems or files that change without notice.
• No audit trails exist of who changed what data, or when to ensure adequate control and compliance.

Spreadmarts expose organizations to significant risk. Business people may make decisions based on faulty data, or incorrect assumptions and analyses.

Time is Money
Research has shown that an average business analyst spends two days a week producing and maintaining spreadmarts. Instead of analyzing data, these high-priced employees act like surrogate information systems professionals, gathering, massaging, and integrating data.

Just do it
Sometimes the data that people need to do their jobs doesn’t exist in a central accessible place. Sometimes the incumbent BI solution does not offer the types of analysis, forecasting, or modeling desired. Sometimes the IT staff or data management infrastructure is inadequate, leaving the business to fend for themselves. Despite the risks, “just doing it” in a spreadmart seems to get the job done and is better than waiting weeks or months for an alternative.

What can be done about this?

• Next week we will talk about a solution for reporting, planning and analysis without Spreadmarts, without the risks, with one version of the truth but still getting it done.
• Where organizations integrate business analytics and planning with governance, risk and compliance, they can significantly reduce the ongoing cost of compliance and gain a true competitive advantage.

More...