Strategies for Managing Spreadmarts
By Wayne W. Eckerson and Richard P. Sherman
Business users are empowered by knowledge—and knowledge comes, in part, from having access to accurate and timely information. It is generally up to the information technology (IT) department to supply this information. But it doesn’t always work out that way.
Definition of a Spreadmart. TDWI used the following definition of a spreadmart in the survey it conducted as part of this report:
- A spreadmart is a reporting or analysis system running on a desktop database (e.g., spreadsheet, Access database, or dashboard) 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. Also known as data shadow systems, human data warehouses, or IT shadow systems.
In organizations all over the world, business people bypass their IT groups to get data from spreadmarts. Spreadmarts are data shadow systems in which individuals collect and massage data on an ongoing basis to support their information requirements or those of their immediate workgroup. These shadow systems, which are usually built on spreadsheets, exist outside of approved, IT-managed corporate data repositories, such as data warehouses, data marts, or ERP systems, and contain data and logic that often conflict with corporate data. Once created, these systems spread throughout an organization like pernicious vines, strangling any chance for information consistency and reliability. You’ll find them in all industries, supporting all business functions. According to TDWI Research, more than 90% of all organizations have spreadmarts. (See Figure 1.)
Does your group have any spreadmarts?
Figure 1: Status of spreadmarts
Spreadmarts often lead to the phenomenon of duelingspreadsheets. Murray Trim, a management accountantwith Foodstuffs South Island Limited, described one suchsituation: “We have had the classic situation of two peoplepresenting ostensibly the same data at a board meeting withdifferent figures, which they got from different spreadmarts.”Donna Welch, a BI consultant at financial holding companyBB&T, talks about the issues of trust that arise from duelingspreadsheets: “We constantly hear our users talk aboutmanagement’s distrust of their reports because multiple peoplecame up with different answers.”
Who and Why. Spreadmarts are usually created by business analysts and power users who have been tasked to create custom reports, analyses, plans, benchmarks, budgets, or forecasts. Often, these analysts—especially those in the finance department and the financial services industry—have become proficient with Microsoft Excel or Microsoft Access and prefer to use those tools to create reports and analyses. As a result, most are reluctant to adopt a new corporate reporting “standard,” which they believe will limit their effectiveness. Change comes hard, especially when it means learning a new toolset and adapting to new definitions for key entities, calculations, or metrics. Executives perpetuate the problem because they don’t want to pay hundreds of thousands of dollars or more to build a robust data infrastructure and deploy enterprise reporting and analysis tools. Instead, spreadmarts proliferate.
Dangers of Spreadmarts
Inconsistent Views. The problem with spreadmarts is that their creators use different data sources, calculations, calendars, data conversions, naming conventions, and filters to generate reports and analyses based on their view of the business. The marketing department views customers and sales one way, while the finance department views them another way. The way the business operates in Germany is different from the way it operates in Brazil. Business units sell the same products with different names, packaging, pricing, and partner channels. When each group manages its own data and processes, it’s nearly impossible to deliver a consistent, enterprise view of customers, products, sales, profits, and so on. These parochial silos of data undermine cross-departmental and business unit synergies and economies of scale.
Excessive Time. In addition, business analysts spend two days a week—or almost half their time—creating spreadmarts, costing organizations $780,000 a year! Instead of analyzing data, these high-priced employees act like surrogate information systems professionals, gathering, massaging, and integrating data. Many executives have initiated BI projects simply to offload these time-consuming data management tasks from analysts.
Increased Risk. In addition, spreadmarts are precarious information systems. Because they are created by business users, not information management professionals, they often lack systems rigor. The problems are numerous:
- Users often enter data into spreadmarts by hand, which leads to errors that often go undetected.
- Few spreadmarts scale beyond a small workgroup.
- Users may create poorly constructed queries, resulting in incorrect data.
- Spreadmarts may generate system and data errors when they are linked to upstream systems or files that change without notice.
- Users embed logic in complex macros and hidden worksheets that few people understand but nevertheless copy when creating new applications, potentially leading to unreliable data.
- There is no audit trail that tracks who changed what data or when to ensure adequate control and compliance.
In short, spreadmarts expose organizations to significant risk. Business people may make decisions based on faulty data, establish plans using assumptions based on incorrect analyses, and increase the possibility of fraud and theft of key corporate data assets.
Not All Bad?
No Alternative. Despite these problems, there is often no acceptable alternative to spreadmarts. For example, the data that people need to do their jobs might not exist in a data warehouse or data mart, so individuals need to source, enter, and combine the data themselves to get the information. The organization’s BI tools may not support the types of complex analysis, forecasting, or modeling that business analysts need to perform, or they may not display data in the format that executives desire. Some organizations may not have an IT staff or a data management infrastructure, which leaves users to fend entirely for themselves with whatever tools are available.
As such, spreadmarts often fill a business requirement for information that IT cannot support in a timely, cost-effective manner. Spreadmarts give business people a short-term fix for information that they need to close a deal, develop a new plan, monitor a key process, manage a budget, fulfill a customer requirement, and so on. Ultimately, spreadmarts are a palpable instantiation of a business requirement. IT needs to embrace what the business is communicating in practice, if not in words, and take the appropriate action. Thus, spreadmarts should not be an entirely pejorative term.
Cheap, Quick, Easy. Moreover, since spreadmarts are based on readily available desktop tools, they are cheap and quick to build. Within a day or two, a savvy business analyst can prototype, if not complete, an application that is 100% tailored to the task at hand. Although the spreadmart may not be pretty or “permitted,” it does the job. And it may be better than the alternative—waiting weeks or months for IT to develop an application that often doesn’t quite meet the need and that costs more than executives or managers want to pay.
Nevertheless, there is a high price to pay for these benefits in the long term. Many executives have recognized the dangers of spreadmarts and made significant investments to fix this problem. However, not all have succeeded. In fact, most struggle to deliver a robust data delivery environment that weans users and groups off spreadmarts and delivers a single version of truth.
Remedies
Managed BI Environment. The problem with spreadmarts is not the technology used to create them. Spreadsheets and other desktop-oriented tools are an important part of any organization’s technology portfolio. The problem arises when individuals use these tools as data management systems to collect, transform, and house corporate data for decision making, planning and process integration, and monitoring. When this happens, spreadmarts proliferate, undermining data consistency and heightening risk.
The technical remedy for spreadmarts is to manage and store data and logic centrally in a uniform, consistent fashion and then let individuals access this data using their tools of choice. In other words, the presentation layer should be separated from the logic and data. When this is done, business users can still access and manipulate data for reporting and analysis purposes, but they do not create new data or logic for enterprise consumption. At TDWI, we call this a managed business intelligence environment. The goal is to transform spreadmarts into managed spreadsheets. This lets IT do what it does best—collect, integrate, and validate data and rules—and lets business analysts do what they do best—analyze data, identify trends, create plans, and recommend decisions.
BI vendors are starting to offer more robust integration between their platforms and Microsoft Office tools. Today, the best integration occurs between Excel and OLAP databases, where users get all the benefits of Excel without compromising data integrity or consistency, since data and logic are stored centrally. But more needs to be done.
Change Management. Applying the right mix of technology to address the spreadmart problem is the easy part. The hard part is changing habits, perceptions, behaviors, processes, and systems. People don’t change on their own, especially when they’ve been successful with a certain set of tools and processes for analyzing data and making decisions. Changing a spreadmart-dependent culture usually requires top executives to both communicate the importance of having unified, consistent, enterprise data, and to apply incentives and penalties to drive the right behaviors. Ultimately, change takes time, sometimes a generation or two, but the right organizational levers can speed up the process.
Aligning Business and IT. Another dynamic driving spreadmarts is the lack of communication and trust between business and IT. The business doesn’t adhere to the architectural standards and processes designed to support its long-term interests, while IT doesn’t move fast enough to meet business needs. To reverse this dynamic, both business and IT must recognize each other’s strengths and weaknesses and learn to work together for the common good. IT must learn to develop agile information systems that adapt quickly to changing business conditions and requirements. The business must recognize the importance of building sustainable, scalable solutions. IT must learn about the business and speak its language, while the business must not blame IT for failures when it continually underfunds, overrides, and hamstrings IT so that it cannot possibly serve business needs.
Architectural Approaches to Spreadmarts
Recognizing that you have a spreadmart problem is the first step. Most of the people we surveyed know their organizations have spreadmarts, but they don’t know what to do about them.
The survey presented respondents with nine different approaches to addressing the spreadmart issue. (See Table 1.)
What strategies have you employed to remedy the problems caused by spreadmarts, and how effective were they?
Table 1. Respondents could select more than one response.
Ironically, the most common approach that organizations use is simply to leave the spreadmarts alone. But as with everything else in life, ignoring a problem does not make it go away, and often makes it worse. When asked how effective this approach was, a majority (58%) said “not very effective.”
Replace with BI Tools. The next most popular approach is to “provide a more robust BI/DW solution,” employed by almost two-thirds of respondents (63%). This approach was considered “very effective” by 24% of respondents. BI software has progressed from best-in-class niche products to BI platforms that provide integrated reporting, analysis, visualization, and dashboarding capabilities within a single, integrated architecture. In addition, many BI vendors now offer planning, budgeting, and consolidation applications to supplement their BI offerings.
We recommend caution with these BI replacement approaches. First, don’t assume that business users will find the BI tools easy to use. Second, don’t assume that business users will see the benefit of these systems if their spreadmarts are answering their business questions today. Get business users (not just power users) involved in the selection and implementation of BI tools, provide ongoing training, and market the benefits. “If it ain’t broke, don’t fix it”—if the business users are not committed to using the BI tools, walk away from the project and look for other spreadmarts the business perceives as a problem.
Create a Standard Set of Reports. Almost as many companies (58%) assumed that creating a standard set of reports using their standard BI tools would eliminate the need for spreadmarts as those that implemented new BI tools (63%). Organizations assumed that these reports would become their systems of record for decision making. Only 18% found this approach very effective. The most likely reasons for the shortcoming were, first, that no set of reports will effectively cover every management decision, so there was a gap in what was provided. Second, since this approach burdened IT with a queue of reports to develop, the business faced two of the primary reasons spreadmarts were created initially: the IT group did not understand what the business needed, and the IT group was not responsive to business needs.
Excel Integration. The only approach respondents rated more effective than adopting BI tools was “providing BI tools that integrate with Excel/Office” (29%). For a spreadmart user, the next best thing to Excel is Excel that integrates with the corporate BI standard. This approach was used by slightly more than half of the respondents (53%). However, Office integration technology can also provide users more fuel to proliferate spreadmarts if it enables users to save data locally and disseminate the results to users. Some BI vendors—and ironically, Microsoft is one of them—now provide a thinclient Excel solution where administrators can deny users the ability to download or manipulate data.
Some experts claim that power users use BI tools mainly as a personalized extract tool to dump data into Excel, where they perform their real work. According to our survey, that’s not the case. Only a small percentage (7%) of spreadmarts obtain data this way. More than half of spreadmarts (51%) use manual data entry or manual data import. It follows that a major way to drain the life out of spreadmarts is to begin collecting the data they use in a data warehouse and create standard reports that run against that data. Of course, if there are no operational systems capturing this data, then a spreadmart is the only alternative.
Sometimes strong-arm tactics are effective in addressing spreadmarts. Reassigning the creators of spreadmarts to other activities is certainly effective, if an executive has the clout to carry this out and offers a suitable BI/DW replacement system. For example, the director of operations at a major national bank reassigned 58 people who were creating ad hoc performance reports with a set of standard reports created using a standard BI platform, saving $300 million a year and dramatically improving the bank’s quality and efficiency in industry benchmarks. This may be the dream of those who are hostile to spreadmarts, but the survey illustrates that this is a rare occurrence.
Gentler approaches are seldom very effective. New policies for the proper use of spreadsheets generally fall on deaf ears; they are very effective only 12% of the time. The problem isn’t that business people do not know how to use the spreadsheets, but that they think they have no alternative.
Multiple Solutions. Given the low percentage of respondents who can vouch for the effectiveness of any of the approaches listed in Table 1, it’s not surprising that managing the proliferation of spreadmarts is such a difficult task. It is more of a change management issue than a technological one. While it’s important to bring new technologies to bear, such as BI tools that integrate with Excel, it’s critical to figure out which levers to push and pull to change people’s habits and perceptions. No single approach is effective on its own; therefore, organizations must apply multiple approaches.
Conclusion
Spreadsheets are here to stay. Business users have them, are familiar with them, and will use them to do their jobs for years to come. Memo to IT: Deal with it! Our recommendation is to choose a solution that balances business and IT priorities and yields the greatest business value.
Wayne W. Eckerson is the director of Research at TDWI. Eckerson is an industry analyst and the author of Performance Dashboards: Measuring, Monitoring, and Managing Your Business (John Wiley & Sons, 2005).
Richard P. Sherman is the founder of Athena IT Solutions, a Boston-area firm offering data warehousing and business intelligence consulting and training. He is an expert instructor and speaker at industry conferences and seminars and teaches at Northeastern University’s graduate school of engineering.
This article was excerpted from the full, 28-page report by the same name.
This report was sponsored by Actuate, Cognos, Microsoft, MicroStrategy, Pentaho, SAP, Unisys, and XLCubed.