by Sean McCall, Pariveda Solutions Vice President

How dependent is your business on a labyrinth of free-form, linked spreadsheets that massage and transform your enterprise data? Do you know where that data resides after it has been adjusted and augmented by these spreadsheets? Are you managing this gray market of data?

Spreadsheets are the ultimate quick prototyping tool. They enable unprecedented flexibility in model building, testing and learning (aka trial and error or what-if analysis), and light automation with features like Record Macro. Spreadsheets also provide useful data exploration and data visualization, and are most often unconstrained and fluid.

Who’s in Control of Data?

Most organizations are in the process of maturing their enterprise data warehouse (and the enterprise data integration to support it), but IT maintains control of enterprise data. This can create significant organization friction in the gap between business information needs and IT delivery of data. In instances where the business’s appetite for information is underserved by IT, the spreadsheet is the most common workaround and leads to the gray market of data. It can be more efficient for analysts to simply “do it themselves” in Microsoft Excel or Microsoft Access with whatever data they can copy and paste from enterprise systems.
But the spreadsheet’s strength is also its downfall as a production application. Resulting data in spreadsheets most often becomes a local silo of information, tucked away on desktops, network file shares, thumb drives, and attached to emails. That data is not an enterprise asset. Furthermore, the same spreadsheet model likely exists in many places, with changeable data, and probably with varying model versions. Multiple versions of the truth, anyone?

A Web of Dependent Decision-Making Objects

Compounding the problem, these spreadsheets are often connected to each other, creating a web of dependent decision-making objects. And how often does a prototype model from Finance or Operations become a de facto production-like application? Analysts in Finance or Operations who are charged with answering questions from the corner office will serve themselves if supply is insufficient. Business needs will not go unmet. However, these analysts are certainly not thinking about backing up the model, security, consistency through using validation for values, application variables, protected formulas, etc.
Obviously, shutting down all rogue spreadsheet production-like applications is not the answer. You are open for business and you have to keep the lights on. But you can start to corral the sprawl with an architectural option that keeps everyone happy.

A Solution That Works For All

Use the spreadsheet as a front-end user interface, but keep the data in an enterprise-class data warehouse for centralized storage. Use Microsoft Visual Studio Tools for Office (VSTO) to facilitate the ODBC database connection.
From there, you can back up the data, share the data across the enterprise, improve data governance, and more. You can easily build a custom Excel ribbon and create a VSTO “enabled document,” where the custom menu appears for that spreadsheet document only. Once the spreadsheet model is ready to be hardened, you can store the latest version on the intranet to manage version control. You balance enterprise data risk while ensuring line-of-business personnel can create dynamic and flexible models, in a way that can be governed by IT.

The technology world is changing. How much can you trust your legacy business models in the new mobilized, digitized, virutalized, and globalized world? Can you afford to not manage the enterprise risk from the gray market of data in your enterprise?