How I Learned to Stop Worrying and Love Spreadmarts

How I Learned to Stop Worrying and Love Spreadmarts

For decades, business intelligence (BI) professionals have been trained to stamp out spreadmarts—those pesky spreadsheets and Access databases that analysts create outside the official purview of corporate BI/IT. But today, savvy BI managers view spreadmarts as a vehicle to turn them from data goats into data heroes.

 The Bane of Spreadmarts

Spreadmarts are data silos that undermine information consistency. Produced by an individual analyst, they are often chock full of errors due to poorly written SQL or replicated Excel macros. Spreadmarts lead to the “dueling spreadsheet” phenomenon where executives backed by spreadsheet-wielding analysts spend entire meetings arguing about whose data is correct rather than making sound business decisions.

Today, we’re in the era of spreadmarts. (See “BI Power Struggle: A Strategy for Success.”) Never before have business units and analysts been so empowered to access, analyze, and publish data on their own without assistance from corporate BI/IT. But instead of spreadsheets and Access databases, today analysts use data discovery tools, such as Tableau and Qlik. (See “Making Peace with Tableau.”) While companies that standardize on these tools—either for data discovery or enterprise BI—often enjoy great success, those that haven’t are drowning in a spreadmart tidal wave.

But maybe that’s not such a bad thing. Let me explain.

 A Silver Lining

Spreadmarts are created by business analysts, whose main task is to dig into data, analyze anomalies, explore trends, and prepare plans. They answer new questions and evaluate new strategies that the business didn’t consider just days, weeks, or months earlier. In other words, spreadmarts contain the leading-edge of information that the organization needs to operate efficiently and effectively.

It’s impossible for traditional BI/IT teams to gather requirements to meet the needs of business analysts, except in a general sense. At best, corporate BI/IT can dimensionalize core subject areas (e.g. customer, product, geography) and facts (e.g. sales, profits, costs). These data elements that form the base compounds which analysts use to concoct entirely new information products.

Yet, the output of this analytical alchemy is often reports and dashboards that business people find useful and want replicated on a periodic basis. When this happens, the ad hoc analysis becomes a production report or dashboard. Unfortunately, in most cases, the analyst who created the insight is responsible for reproducing the report on a fixed schedule, consuming time he might otherwise spend performing new analyses.

 Turning Defeat into Victory

Next Generation of Requirements. Here’s where savvy BI managers can turn defeat into victory. Rather try to stamp out spreadmarts, savvy BI managers view them as instantiations of the next generation of requirements for the data warehouse, reports and dashboards. Rather than go through a formal and tedious requirements process, they use spreadmarts to anticipate business needs and build suitable information artifacts before business people even ask.

Analyst Productivity. Moreover, savvy BI leaders also see spreadmarts as an opportunity to rescue analysts from the drudgery of report writing and production reporting. By offering to produce their published reports, BI managers can free up analysts to spend more time analyzing data.

Save Headcount. Moreover, the savvy BI manager continually searches for glorified report writers masquerading as analysts in the business units.  A well-constructed spreadmart strategy can replace these “report analysts”, saving business unit heads money. Actually, a better strategy is for BI managers to coopt these workers, creating a dotted line relationship to them and providing support, training, and standard tools and processes.

How to Become a Hero

So how can BI leaders turn the threat into an opportunity? How can they turn potential defeat into victory? How can spreadmarts turn them into a hero to business users and analysts alike?

The answer is pretty simple: watch, partition, and produce.

Step One: Watch. You can’t manage spreadmarts if you don’t know where they are and who is creating them. So take an inventory of business analysts in each division. Find out who they are, what they create, where they store their output and what business users do with it. Take a few to lunch. In fact, take every analyst you can find to lunch! Repeatedly. These folks are your eyes and ears in the business trenches. Treat them well!

Step Two: Partition. Second, create one or more safe zones or sandboxes in your corporate BI ecosystem where analysts can play with data. Allow them to mash up corporate data with their own data and external data to create new data sets. Give them data preparation tools to accelerate the mashup process. More importantly, give them a file system or collaboration platform where they can publish their output and share with others.

Step Three: Produce. Finally, communicate to analysts that their output is only valid for 90 days and then it gets deleted or archived. If they (or their managers) want to perpetuate a report, then they should turn it to corporate BI/IT to produce using standard data and processes. This alleviates analysts from reporting duties and delivers timely reports and dashboards to business users.

If you watch, partition, and produce, you’ll become a data hero in no time. Analysts will love you because you give them all the data they need, while offloading tedious report writing duties; managers will love you for making their analysts more productive and delivering timely, useful reports; and executives will love you for streamlining the data factory and increasing business agility.

Wayne Eckerson

Wayne Eckerson is an internationally recognized thought leader in the business intelligence and analytics field. He is a sought-after consultant and noted speaker who thinks critically, writes clearly and presents...

More About Wayne Eckerson