Should You Build A Data Warehouse?

Jim's intro: Gary Holmes is a strategic partner of mine and a heck of a smart guy on the design of data solutions.  How do I know?  He designed the multi-terabyte "360 degree view" warehouse used at Home Shopping Network.  Find out why you don't necessarily need to start out with a data warehouse in this article.

A Guest Opinion by Gary Holmes
First published 10/29/2001

The Question:
As organizations adjust to the realities of today's economy they also struggle to improve their ability to turn data into usable information.  Is building a data analysis system an appropriate use of scarce resources in a soft economy?  Does your organization have the experience to do it successfully?  These questions will be answered differently by each organization.  But, if you could increase revenue, cut costs, or improve your market position by using data analysis capabilities, an investigation is certainly justified.

Often this investigation results in undertaking a data warehouse project, and data warehouse solutions have been discussed in industry publications for many years now, with examples of spectacular successes as well as equally spectacular failures.  So, how do you ensure that your efforts will be successful?  The first step is to pause to understand your business needs, the problems to be addressed, and the potential solutions, which we hope to make clear in this article.  Ultimately, of course, the success of the project will be in the hands of your project team and the players that support your efforts.

Controlling the Reaction:
Once you begin, frustrated users will often attempt to jump aboard, with departments pushing to have their own "one small wish" inserted and fulfilled as soon as possible.   Unfortunately, accommodating such wishes can be very disruptive and costly.  And, even though your organization may have survived for years without advanced reporting and analysis capabilities, there will be a push to get it all, and now! 

A large (enterprise) data warehouse may not be your initial goal.  But, since they are the most visible and popular solutions touted in today's trade publications, they have come to constitute a common demon infecting many reporting and analysis projects.  By bowing to the pressure and deciding on a full-blown data warehouse effort prior to goal definition, you open yourself up to extensive scope expansion.  A project once intended to improve marketing efficiency now becomes a total enterprise data warehouse effort, involving every department in the organization.  This warning should by no means turn you away from standard data warehouse technology and methodology, it is merely intended to direct you to proceed prudently, without being pushed into anything that is not based on your real business needs. There are alternatives.

Building a data warehouse can be expensive and many organizations do not possess the necessary experience to successfully plan and implement such a project.  As a result, first year budgets are often expended for less then satisfying returns, or the project flounders and provides little relief to the frustrated user community.  To avoid these scenarios you need to remove some of the emotion and plan for what is really needed.  Below are common constructs that may suit your needs.

1. Reporting Team: This is a dedicated team of individuals who work with your existing applications and technology to provide the required reports.  Tying up valuable resources to produce an endless list of reports is a short-term solution at best.  The need for varied and complex reports is insatiable, but a prioritized approach may satisfy your most critical needs and will help you define the requirements for a proper long-term solution.

2. Reporting Repository: This is a database server build to meet the specific reporting requirements people are pressing for right now.  It is initially cheap to provide and usually meets the immediate need.  The problem is that usually very little effort is expended to accommodate future requirements and it therefore becomes larger, more expensive, more complex, and less useful over time.  These systems almost always become a huge drain on the organization if not redesigned and replaced shortly after they are built.  This is the type of approach Jim and I would probably use for a Customer Marketing / pre-CRM ROI Test.

3. Operational Data Store (ODS): This is basically a duplicate of the information that resides in your operational systems, such as Call Center, Manufacturing, and Financial.  Proper database security and technical aspects are managed to provide somewhat improved reporting and analysis performance, with much improved access to the data.  The information, however, is still very hard to make sense of because it exists in the same complex data structures as the source systems.  An ODS is often the first step towards building a data warehouse, since it gets the data out where people can see and work on it.  Unfortunately, because of the volume of information that may be involved, it could be an expensive first step if not managed carefully.

4. Data Mart: These are generally small data warehouses.  Data marts use the same structures and many of the same development methods as data warehouses.  The difference is that they are intended to meet a specific need or to deliver only one type of information.  A true data warehouse would cover information from many systems and would be used by many different people in many different departments.  A data mart on the other hand uses the same techniques on a smaller scale to provide specific information to a specific group of people.  For instance, it may only cover Customer Sales information for use by the Marketing department.  This would eliminate the need to build in Manufacturing or Finance information and perhaps would only require summary totals.  Data marts are much cheaper to build and maintain, but they may not provide a good solution for sharing information across the company.

5. Data Warehouse (DW): A data warehouse is a database that stores information from many areas in the company in a manner that allows this information to be easily combined in reports and in analysis tools.  It is generally expected that the data will be checked for accuracy and will be quick to retrieve and easy to understand.  Future reporting and analysis requirements are anticipated where possible and security of the information is properly managed.  A data warehouse effort that covers the majority of the information used by a company can be very expensive to build and could even be out of date by the time it is completed, if the effort is not planned properly.

The Solution:
As you can see there are a number of solutions that could be applied to your business needs and, like most things in life, moderation and practicality usually lead to the most satisfying solution.  The alternatives above can be, and often are, combined to satisfy information and analysis requirements while keeping budgets under control.

If you and your organization do not have extensive experience with successful reporting and analysis efforts consider starting very small and get some help if you are uncomfortable with the details.  You can successfully build a data warehouse step by step if you first start with a solid plan, manage your goals, and include from the beginning the people who will be using it.  And don't forget to prioritize your goals.  By focusing on those that provide the greatest potential for success and return on investment you can increase your odds of maintaining a successful project plan. By basing your plan on a prioritized set of goals, you will be building on your initial successes in order to increase the support you get from other members of the organization and to justify the budget for your next move.

A good place to start is to build a mini reporting repository to see if the reporting and analysis capabilities that everyone is clamoring for will actually help the business.  Often the most critical reporting needs, once met, do solve a problem - and then priorities change.  As mentioned, even a small success will build support and help justify the expense.  At the very least, this effort will allow you to solidify your requirements, bring familiarity with the data, and facilitate the testing of some new technology.  This mini project could also be carried out in parallel as you develop you project plan and finalize your goals.  But remember, this can only be the first step in a larger plan to create a lasting solution; it cannot become the focus of your efforts and consume all of your resources.

Some organizations have a hard time justifying even the initial planning and "mini project" efforts.  So how can you rally the support to get started?  Here is where the incremental strategy comes to the rescue.  It should be quite easy to find at least two challenges that could be met with improved reporting and analysis systems.  By obtaining a conservative estimate of the increased profit or reduced cost that would result, you should be able to justify the planning phase and a "mini project."

Estimating and justifying the cost of the larger effort, however, is not practical without knowing your prioritized goals and the steps necessary to achieve them.  Failing to do so will only lead to poor, if not disastrous, results. Once your prioritized goals and solid plan are in place, however, you can manage the budget and adjust your resource requirements based on solid information.  Goals that do not merit the budget expenses can be eliminated, moved down the priority list, or sent back to the requesting department for justification.

Conclusion:
Our initial question was "Should we build a data warehouse?"  The answer is that a true data warehouse may or may not be the correct solution for your organization.  A large data warehouse effort can be a very expensive and risky venture.  But, by prioritizing goals and solidly planning before spending large amounts, you can eliminate much of the risk while rallying the support needed to succeed.  Your goals and your plan will help you determine the correct solution and by taking an incremental approach you will ensure that each new project phase will be more successful than the last.  One thing is certain, if your organization could greatly benefit from improved reporting and analysis capabilities, it would be unwise not to investigate today's possible solutions.

Gary Holmes is a Data Warehouse Architecture consultant that has been working on Data Warehouse and Decision Support systems since 1989.