Analysts at the IT industry’s leading advisory company, Gartner, have been instrumental in defining the nature of the challenges surrounding ‘shadow data’. While appreciating that there are good reasons for some users to build such ‘spreadmarts’, the analysts point out the real dangers of using the approach in critical areas of business judgement and fiduciary reporting.
To understand and consult on the problem, Gartner has examined the shortcomings of business intelligence and ERP systems in enabling organisations to conduct such seemingly straightforward activities as closing their books at the end of a financial period, looked at how user-defined applications have become a new phenomenon in most organisations, and mapped out the rise of financial governance technologies designed to enable the management of financial risk.
Information Age spoke to Neil Chandler, a research director specialising in BI and corporate performance management at Gartner
Information Age (IA): After 30 years of decision support technology and reporting tools, why are financial controllers, derivatives traders, drug trial analysts and million of others still hooked on Excel, Access and related user-defined applications?
Neil Chandler (NC): Spreadsheets are great personal tools but terrible collaborative tools. They have been used pervasively within organisations, but the result has been the creation of ‘spreadmarts’. In finance, marketing, sales, operations, HR: they occur everywhere.
Whether people understand it or not, a spreadsheet is basically a programming language – a programming tool for the database attached to it – that most people feel empowered to use.
Relatively immature or low-level development goes into them, which consequently culminates in a whole bunch of errors and typically
means they are very locally focused around terminologies and definitions that are outside the established norm for the wider organisation. So, for example, different people in the same organisation come up with different definitions of what profit is.
IA: That does not sound too healthy. What impact does that have?
NC: I sat in on a board meeting last year where those at the table had a pack containing data on the company’s performance, assembled via a range of [different departments’] spreadmarts. They spent the first hour arguing about who had the right numbers in support of production rates, profitability, failures rates and costs.
IA: So that data is extracted from operational sources or maybe a data warehouse and manipulated for the user’s own, presumably legitimate, reasons.
NC: Therein lies the problem. Spreadmarts all have a very strong lack of auditability. There is no easy method to track who has changed what data or where the data has come from or how often it is updated – or whether the numbers have been massaged or not.
So there is a lot of opportunity for information to be fraudulently used or misused. But even in the situation where everyone wants it to be right, there are still critical issues that can create inconsistency, such as when the data was extracted and from where. So if it is extracted from a financial system and there are journals happening every day, then if it is not done at a point when the system has closed the reporting for that particular period, it is likely to be using an incorrect number.
Users may be sucking data in from a BI interface, so they might have some reliable reports, but they are extracting and saving that data to Excel and also rekeying data. They want that because it gives them greater flexibility; they may not be getting the kind of analytical insight that they need from the systems they are provided by the IT function. Or they may want to look at things in a different way, with a different structure from the way that is being represented through the core systems.
IA: Who is implementing these systems?
NC: Typically power users and analysts – and it is not always a good use of their time. These are highly expensive employees who you’d rather have doing value-added analysis rather than spending their time involved in programming languages.
I would estimate that 50% of the organisations I speak to still have a significant investment in spreadsheets. That particularly applies to things like budgeting and forecasting.
IA: There are different degrees of risk here, though. There might not be as much concern about marketing and HR creating spreadmarts as the finance department using them to extract data from the operational systems and then manipulating it as part of statutory reporting or financial closing.
NC: Absolutely. That is were we should start to get control of spreadsheet dependency.
What we have seen over the past few years has been the rise of corporate performance management (CPM). And the primary function CPM systems deliver is the rule of critical financial applications.
So organisations are moving their financial consolidation and financial reporting out of their ERP systems, mainly because of inflexibility of these to actually meet a number of different reporting constructs. They want to be able to represent data for internal and external stakeholders and to be able to manipulate that with a greater degree of flexibility using robust applications designed specifically for these functions.
IA: Who is perceiving this as a problem: the IT organisation, FDs and other senior management, auditors and regulators?
NC: It is the sort of thing that keeps CFOs awake at night – the quality and integrity of their internal systems. On the other hand, IT is trying to standardise and expand the footprint of BI to an enterprise level, and in the process they are looking at auditing the existing reporting systems – looking at where, for example, Excel or Access are used. And as they audit that for standardisation, they find all these internal systems.
IA: Are there technologies that can be applied here to ensure the integrity of analysis?
NC: There are packaged applications in support of spreadmart/replacement strategies to which the area of finance has been particularly receptive. Because the data has to be accurate, it has to be transparent, it has to auditable, it has to be shared with external stakeholders, and, increasingly, there are regulatory and compliance controls imposed on the finance function.
The GL Company is one vendor that is providing these solutions to replace [spreadmart] systems with robust, repeatable, manageable capabilities that do not necessarily put a huge burden on the IT function.
Such packages empower the finance department to manage their own investments in their environment without the inherent risks.
The GL technology provides a relatively simple user interface so the finance function can maintain their own constructs and develop new systems without putting a huge burden on IT.
IA: Do you see a point where shadow data systems will be reduced to a background level?
NC: Financial professionals love Excel, so, rather than try to usurp Excel, the most successful products I have seen replace the back-end of a spreadsheet system with a robust application. They can use the spreadsheet interface for data entry or generating reports, but they have taken away all of the problems of using the spreadsheet system by not storing any of the data in the spreadsheet itself but storing it in a central, auditable application.
That way you don’t have five different people coming to a meeting with five different definitions of profit.
