When you plan to build a house, you start with what you want and can afford. You decide on architectural style, size and number of rooms. Then you hire an architect to design the house and create a detailed blueprint. The architect gives you feedback on how the various rooms can fit together, what the building codes are and how to fit in the infrastructure such as wiring and plumbing. Most importantly, the architect advises you on what is really possible or practical given your location, house size, wishes, budget and timetable. And she gives you ideas for things you hadn’t thought of. Had you skipped this step and gone straight to a builder with your wish list, you would likely go over budget and get a house not well sited on your lot, with poor aesthetics and limited usefulness.

Like the unfortunate house, many data warehouses are built without an architectural blueprint. Companies focus on individual data warehousing (DW)/business intelligence (BI) projects without seeing how they fit as part of an overall architecture. As a result, many DW/BI environments have become a collection of technology, product and data silos that are loosely connected and require an intensive commitment of resources to operate, upgrade, maintain and enhance. Many businesspeople are frustrated with the state of their DW/BI environments, which take forever to enhance and are always a release away from becoming pervasive throughout the enterprise. They remember the investments of time, resources and budget, and they ask why they still have to use spreadsheets (i.e., data shadow systems or spreadmarts) as the superglue for reporting and analysis.

The Clues to an Accidental Architecture

How do you know you are in this situation? Ask these questions:

• Do you have multiple BI and performance management (PM) tools or applications scattered throughout your enterprise?
• Do your business groups continually engage vendors to bring in new BI solutions to make up for the current environment’s shortcomings?
• Have your business groups built many data shadow systems – and continue to build more – to do their reporting and analysis?
• Are people assuming that emerging technologies such as service-oriented architecture, software as a service or on-demand software, master data management, customer data integration or open source software will deliver information nirvana, solving all your information needs?

If any of these are true, your company has probably developed an accidental architecture for your DW/BI environment. Even if each DW/BI project was well thought out and designed, you still have multiple DW/BI silos. You use different technologies, products and data architectures for each one. Each project was going to solve the shortcomings of past initiatives. The sum of the parts, i.e., projects, does not add up to what the business was expecting.

If you are still in denial, let’s probe a little deeper. From an IT perspective, ask yourself:

• Can IT provide the data models for your DW, data marts, cubes and all the other data structures used for reporting and analysis, including Microsoft Access, SAS and Microsoft Excel files?
• Can IT provide the extract, transform and load (ETL) workflows documenting the data integration, starting from your data sources all the way to where the business consumes the information, including your DW, data marts, cubes and any other data structures used for reporting and analysis?
• Does IT use an ETL product to load your DW, data marts, cubes and other data structures? Or is the ETL tool only used for loading the DW? If hand coding is involved, is it documented and managed?
• Can IT document all the processes and data used to build reports or perform analysis?
• Can IT identify and document your data shadow systems?

The typical response from IT is a nervous laugh. They typically will be able to generate the ETL workflow from data sources to the DW (they can have their ETL tool generate that) and they sometimes have a DW data model (but it is usually not current). But the travels and transformation of the data from the DW to business reports and analysis, especially in spreadsheets, are generally a mystery.

If you built your DW/BI environment according to an architecture, you would have all this information at your fingertips, and you would be laughing at the poor souls who do not. But chances are, I have confirmed your journey down the accidental architectural “yellow brick road.” Each new BI technology spurs an attempt by the wizard (the latest vendor you are working with) to magically solve your problems.

Before you can solve a problem, you have to recognize it and want to fix it. This is true of any addition, and the technology du jour is certainly an appealing addition.

Your enterprise has evolved an accidental architecture, and you admit you need to break the cycle of dependency. There are no silver bullets that will solve your problems, but there is a road to recovery. It will take hard work, resources and commitment, but it will be worth it. In the next column, I will discuss how to start this journey.