Actions for Data Warehouse Success

The following are some suggestions for the warehouse builder. These are points I rarely see discussed or I do not see discussed enough in the barrage of articles about data warehousing.
From day one establish that warehousing is a joint user/builder project
Warehouse projects will fail if the builders get specs from the users, go off for 6 months, and then come back with the 'finished' project. Warehouses are iterative! (I think the word iterative means there are lots of mistakes in the projects.) Builders and users working with each other will not reduce the number of iterations, but it will reduce the size of them. By the way, see Peter Block's Flawless Consulting for a great discussion of how to bring about 'joint' projects.
Establish that maintaining data quality will be an ONGOING joint user/builder responsibility
Organizations undertaking warehousing efforts almost continually discover data problems. Best to establish right up front that this project is going to entail some additional ongoing responsibility.
Train the users one step at a time
Typically users are trained once. In several days they learn both the basics and intermediate and sometimes advanced aspects of using a tool. Slow down! Consider providing training initially in the minimum needed for the user to get something useful from the tool. Then let the user use the tool for a while (meaning several days, weeks, or months). Having basic training and some hands on experience, the user will have a much better context with which to grasp the next level. Also, once the basics and the next level are learned, keep training the users! After a year using the tool, schedule advanced training.
Train the users about the data stored in the data warehouse
Users often need more training about the stored data than about the tools used to access the data. Do not assume the data are self-explanatory or that any metadata you may provide will answer any questions. Note that users are often used to seeing data in canned reports and seeing data in its "raw" form can be confusing.
Consider doing a high level corporate data model / data warehouse architecture "exercise" in three weeks
Actually, the key point regarding time is to "time-box" the exercise into a relatively short time. After about three weeks, the marginal benefits from additional time devoted to these types of exercises rapidly decrease. - The corporate model is going to identify, at a high level, subjects and relationships and most importantly, what are the chunks of information that it makes sense to deliver in different projects. The architecture part of the exercise to determine the dimensions, definitions of derived data, attribute names, and information sources that you will attempt to use consistently in your data warehousing efforts. The exercise also consists of coming to an agreement as to how to keep the corporate model up-to-date and how to make sure future data warehousing efforts pay attention to the architectural principles.
Implement a user accessible automated directory to information stored in the warehouse
The majority of successful warehousing efforts I have seen included providing some means for the warehouse user to locate stored information. Most of the times this involved building a separate database with directory information. And most of the time, a pretty simple database sufficed for initial use.
Once you know what raw data you want to feed into the data, request that data
If you have done some reading on data warehouse development you probably have read that figuring out the process of extracting, transforming, and loading (ETL) usually takes the majority of the time in initial data warehouse development. In project management lingo, figuring out ETL is usually on the critical path. - If you know what raw data you need, request it as soon as you know it. You are probably going to have to ask one of the programmers of the legacy feeder systems to initially get this data for you. For reasons of politics, overwork, and just plain lack of knowledge of how data are physically stored in a system, the feeder system programmer often can take a while to get you that data.
Determine a plan to test the integrity of the data in the warehouse
Do not underestimate the importance of user faith in the integrity of the warehouse data. Huge warehouse efforts quickly go sour if after system roll-out users find multiple mistakes. A good investment of time in the initial stages of a warehouse project is for the builder and user to jointly determine what checks will be made on the warehouse data during development and what checks need to be made on an ongoing basis. The checks including tying warehouse data controls back to controls in feeder systems, checking the correctness of aggregation logic, testing whether classifications codes were assigned correctly.
From the start get warehouse users in the habit of 'testing' complex queries
Many people will assume that the query result is correct. At the very least, get the user in the habit of eyeballing the query or report to check if several records that should be included are, in fact, included and that several records that should not be included are, in fact, not included.
Coordinate system roll-out with network administration personnel
Use of data warehousing systems can bring about some strange spikes in network activity. If you keep network administration people informed of the roll-out schedule, chances are they will monitor network activity for you and be ready to make adjustments to the network as necessary.
Have a good grasp of desktop databases and spreadsheets

Even if you are dealing with a 100 TB database, there are so many little tasks to be done in a data warehousing project where knowledge of these tools will be helpful. Skillful use of these tools during development can be a huge productivity enhancer.
Understand that the spreadsheet is your users' primary analytical tool

That is the analytical tool most users are most familiar with. Be prepared to build in capabilities that amplify the poer of spreadsheets.
Be prepared to support beginning users immediately and at any time

We developers often greatly underestimate users' hesitation to begin using the data warehouse. This hesitation could be because of user fear of technology or user fear that they will not get IS support. So, the first point is to be available to help when the user wants to try to use the data warehouse the first time. Users also may want to use the data warehouse for the first time during the weekend or at 6:00 in the morning or 8:00 at night. The distractions are less at those times. If you want to make that beginning user as a committed customer of your data warehouse, you better be available to support the user when he starts out whatever the day or the hour.
Maintain the audit trail to the feeder systems

That is, make it as easy as possible to tie the data in the data warehouse to the feeder systems. Your users have to trust the numbers in the data warehouse. You owe this to the users in order to maintain their trust.
Market and sell your data warehousing systems
For the most part, use of data warehousing systems is optional. This means you have to identify the potential users of the systems, help them understand what are the benefits of the system, and then make them want to keep coming back to use the system.

The Case Against Data Warehousing

The literature is full of testimonials for data warehousing. There is almost nothing about the arguments against data warehousing. In this paper I attempt to slightly fill that void by shedding light on business and cultural factors that greatly lessen the value of data warehousing for certain organizations. By the way, when I refer to data warehousing, I refer to both centralized data warehousing systems and data marts.

Some of the reasons data warehousing efforts may not be appropriate for certain organizations are:
Data warehousing systems, for the most part, store historical data that have been generated in internal transaction processing systems. This is a small part of the universe of data available to manage a business. Sometimes this part has limited value.

That is, sometimes the business end user community does not have a strong interest in old transaction processing system data beyond what are available in basic reports generated in transaction processing systems. This lack of interest often stems from the fact that the markets in which a business competes are in great flux or that the internal structure of the organization is in perpetual transition. If these conditions exist, there may not be a solid historical base to compare current performance with. Also, sometimes there is a lack of interest in looking at this data in any in-depth way because a business is so simple that a data warehouse is overkill.
Data warehousing systems can complicate business processes significantly.

Though the interest in business process reengineering seems to have waned, some of the appreciation of how complicated processes can slowly strangle a business has remained. Data warehousing, if unchecked, can foster the "institutionalization" of easily created reports whose reason for being quickly is forgotten while people still toil to process these reports. If your organization does not know how to throw out processes (pardon my calling producing, distributing, and reading a report a "process"), data warehousing can quickly add clutter to the business environment.
If most of your business needs are to report on data in one transaction processing system and/or all the historical data you need are in that system and/or the data in the system are clean and/or your hardware can support reporting against the live system data and/or the structure of the system data is relatively simple and/or your firm does not have much interest in end user ad hoc query/report tools, data warehousing may not be for your business.

Whew! You can say that again. - Anyway, you may find that as more of these conditions are met, the less value data warehousing may add to your firm. And once you get away from the big "Fortune 500, centralized IS" type shops most of the data warehousing vendors slant their marketing to, these conditions describe the reporting needs of many firms.
Data warehousing can have a learning curve that may be too long for impatient firms.

Despite the speed of the data warehousing development effort, it takes time for an organization to figure how it can change its business practices to get a substantial return on its data warehousing investment. I speculate that rigorous analysis of the return on most of the major data warehousing implementers' investments would find a much longer average payback period that you would surmise from reading the trade press.
Data warehousing can become an exercise in data for the sake of the data.

Organizations find that there are unlimited opportunities to add data to their data warehouse. Data warehouses, like most other complex systems, take a life of their own. Unfortunately, adding data without questioning the business value of the data can lessen the business value of the data warehouse and quickly increase the cost of maintaining the data warehouse.
In certain organizations ad hoc end user query/reporting tools do not "take".

This is of concern to organizations that believe they can get their return on investment by having users write many of their own queries and reports. In some firms there are profound cultural barriers in the business organization to the acceptance of a tool that allows a person to ask questions on his own. Trying to promote the use of such a tool in these organizations is setting yourself up for failure. Or, sometimes these tools do not take because a business is so complicated that only relatively simple reports with little business value can be written by end users.
Many "strategic applications" of data warehousing have a short life span and require the developers to put together a technically inelegant system quickly. Some developers are reluctant to work this way.

Again, the importance of the culture cannot be underestimated. This time, though, the issue is in the IS organization. If your sell of the data warehousing project is the ability to do this strategic work (which is probably now being done by your users with large and complex spreadsheets) as opposed to the usual development of canned and semi-canned reports and queries, ask yourself if the IS culture can accept this mode of working. For many organizations this approach to systems work is much harder to accept than most people realize.
There is a limited number of people available who have worked with the full data warehousing system project "life cycle".

I refer to availability of both employees and consultants. Systems of some depth require a considerable amount of time to develop fully. In other words, it takes a long time to gain experience with the usual problems that develop at different phases of a data warehousing effort. You should be wary of a consultant who says he has experience implementing scores of data warehouses in a couple of years. Usually this is experience will be with a well-defined part of a data warehousing project that was amenable to outsourcing or with minor projects.
Data warehousing systems can require a great deal of "maintenance" which many organizations cannot or will not support.

Despite the best efforts to architect a system so "maintenance" (in quotation marks because it seems often there is never the closure to the initial data warehousing effort that the term "maintenance" implies) demands are minimized, many systems by their very nature require a great deal of care and feeding once they are in "production". It is important to note that the more successful a warehouse is with the users, the more maintenance it may require. Organizations who cannot or will not staff to meet these maintenance demands should think twice before they jump into the data warehousing business. By the way, it's very easy for the users to quickly go sour on a system they were enthusiastic about at roll-out time if the system personnel do not support the maturing of the system.
Sometimes the cost to capture data, clean it up, and deliver it in a format and time frame that is useful for the end users is too much of a cost to bear.


The percentage of time that must be devoted to extracting, cleaning, and loading data has been well discussed in the literature. It should be pointed out that there are some potential "show-stoppers" in these efforts. Loading data from previous years can require the knowledge of transaction processing system developers who have long since moved on. Cleaning data so they are in a form that is acceptable to users from different functional areas may require arbitration skills the typical data warehousing developer may not possess. Finally, data may have to be loaded into a data warehousing system in a processing window that just isn't big enough. Sometimes compromises are acceptable get-arounds. Often, though, compromises end up substantially compromising the value of the information in the data warehouse.



You may have gotten the impression from reading the trade press that data warehousing is only for large organizations because it requires huge staffs and huge budgets. Well, most of the trade press is dominated by vendors/consultants/publications trying to market to large organizations with huge staffs and huge budgets. - Though I have no way to prove this, in terms of numbers, I think most data warehousing efforts are done by small staffs with modest budgets. In fact, smaller organizations are probably much more "into" data warehousing than larger organizations. It is only recently that practical technology for huge organizations who lust for multi-terabyte databases has become available. The technology for more modestly sized data warehouses, on the other hand, has been available for many years.

Finally, you may have seen articles that state that data warehousing failure rates are between 10% and 90%. Though how these failure rates are determined is suspect, there is no denying that data warehousing is risky. Now the fact that these efforts are risky does not bolster the case against data warehousing. Data warehousing has not repealed the positive relationship between risk and expected return in capital projects. However, if your organization does not know how to manage risky projects, then data warehousing may not be for you.

The Case for Data Warehousing

The following is a list of the basic reasons why organizations implement data warehousing. This list was put together because too much of the data warehousing literature confuses "next order" benefits with these basic reasons. For example, spend a little time reading data warehouse trade material and you will read about using a data warehouse to "convert data into business intelligence", "make management decision making based on facts not intuition", "get closer to the customers", and the seemingly ubiquitously used phrase "gain competitive advantage". In probably 99% of the data warehousing implementations, data warehousing is only one step out of many in the long road toward the ultimate goal of accomplishing these highfalutin objectives.

The basic reasons organizations implement data warehouses are:
To perform server/disk bound tasks associated with querying and reporting on servers/disks not used by transaction processing systems


Most firms want to set up transaction processing systems so there is a high probability that transactions will be completed in what is judged to be an acceptable amount of time. Reports and queries, which can require a much greater range of limited server/disk resources than transaction processing, run on the servers/disks used by transaction processing systems can lower the probability that transactions complete in an acceptable amount of time. Or, running queries and reports, with their variable resource requirements, on the servers/disks used by transaction processing systems can make it quite complex to manage servers/disks so there is a high enough probability that acceptable response time can be achieved. Firms therefore may find that the least expensive and/or most organizationally expeditious way to obtain high probability of acceptable transaction processing response time is to implement a data warehousing architecture that uses separate servers/disks for some querying and reporting.
To use data models and/or server technologies that speed up querying and reporting and that are not appropriate for transaction processing

There are ways of modeling data that usually speed up querying and reporting (e.g., a star schema) and may not be appropriate for transaction processing because the modeling technique will slow down and complicate transaction processing. Also, there are server technologies that that may speed up query and reporting processing but may slow down transaction processing (e.g., bit-mapped indexing) and server technologies that may speed up transaction processing but slow down query and report processing (e.g., technology for transaction recovery.) - Do note that whether and by how much a modeling technique or server technology is a help or hindrance to querying/reporting and transaction processing varies across vendors' products and according to the situation in which the technique or technology is used.
To provide an environment where a relatively small amount of knowledge of the technical aspects of database technology is required to write and maintain queries and reports and/or to provide a means to speed up the writing and maintaining of queries and reports by technical personnel

Often a data warehouse can be set up so that simpler queries and reports can be written by less technically knowledgeable personnel. Nevertheless, less technically knowledgeable personnel often "hit a complexity wall" and need IS help. IS, however, may also be able to more quickly write and maintain queries and reports written against data warehouse data. It should be noted, however, that much of the improved IS productivity probably comes from the lack of bureaucracy usually associated with establishing reports and queries in the data warehouse.
To provide a repository of "cleaned up" transaction processing systems data that can be reported against and that does not necessarily require fixing the transaction processing systems

Please read my essay on what data errors you may find when building a data warehouse for an explanation of the type of "errors" that need cleaning up. The data warehouse provides an opportunity to clean up the data without changing the transaction processing systems. Note, however, that some data warehousing implementations provide a means to capture corrections made to the data warehouse data and feed the corrections back into transaction processing systems. Sometimes it makes more sense to handle corrections this way than to apply changes directly to the transaction processing system.
To make it easier, on a regular basis, to query and report data from multiple transaction processing systems and/or from external data sources and/or from data that must be stored for query/report purposes only

For a long time firms that need reports with data from multiple systems have been writing data extracts and then running sort/merge logic to combine the extracted data and then running reports against the sort/merged data. In many cases this is a perfectly adequate strategy. However, if a company has large amounts of data that need to be sort/merged frequently, if data purged from transaction processing systems needs to be reported upon, and most importantly, if the data need to be "cleaned", data warehousing may be appropriate.
To provide a repository of transaction processing system data that contains data from a longer span of time than can efficiently be held in a transaction processing system and/or to be able to generate reports "as was" as of a previous point in time

A Definition of Decision Support

The term decision support, if my knowledge of history of this area is correct, goes back to the 1970s when it was coined by some academics associated with the Massachusetts Institute of Technology. Since then, many academic definitions have been offered. - My purpose in this essay is to provide a definition that may lend clarity to practitioners.
A decision support system or tool is one specifically designed to allow business end users to perform computer generated analyses of data on their own.

I believe the essence of decision support is, in the language of the 1960s, to allow end users to do their own thing. I note that this definition is still fuzzy because what constitutes analyses and "on their own" are debatable points.
We cannot say that decision support systems or tools necessarily support the making of decisions.

What's in a name? - As far as I know, cognitive researchers do not agree on how decisions are made. Therefore, saying that these tools support making decisions is not a provable statement. Nor, is it, in may opinion, an insightful way of defining these tools.
These tools do not analyze by themselves - rather they help a person analyze.

In other words, the tools facilitate analyses rather than perform analyses. If you want to learn more about how the tools facilitate analyses, see my essay on What Decision Support Tools are Used For.
Data warehousing and decision support systems and tools do not necessarily go hand in hand.

Many data warehouses are not used as decision support systems. And decision support systems or tools do not necessarily require the use of a data warehouse as a source for data. I assert that, by far, the most used decision support tools are spreadsheets not connected in any automated way with a data warehouse.
Business intelligence seems to have become the vendors' preferred synonym for decision support.

My guess is because decision support has an academic connotation and, as just mentioned, decision support systems do not necessarily support decisions. On the other hand, business intelligence systems do not necessarily make a business more intelligent. By the way, the consultant-coined term business intelligence goes back to the late 1980s, fell out of use, and then was revived by the DW/DSS world in the late 1990s. Confusingly, business intelligence is also used as a synonym for competitive intelligence (and is probably a more apt term for that area). By the way, "analytics" seems to be an up and coming name for this area - despite the mid-1990 consultant-coined term "analytical applications" never taking hold.

A Definition of Data Warehousing

A data warehouse is a copy of transaction data specifically structured for querying and reporting.

Ralph states that a data warehouse is "a copy of transaction data specifically structured for query and analysis". Two quibbles I have with Ralph's definition are: 1) Sometimes non-transaction data are stored in a data warehouse - though probably 95-99% of the data usually are transaction data. 2) I say "querying and reporting" rather than "query and analysis" because the main output from data warehouse systems are either tabular listings (queries) with minimal formatting or highly formatted "formal" reports. Queries and reports generated from data stored in a data warehouse may or may not be used for analysis. - For some more information about why the transaction data are copied, you may want to see my essay The Case for Data Warehousing. To learn about the key decisions that must be made in determining the structure of a data warehouse, you may want to see my essay Aspects of Data Warehouse Architecture.

What I especially like about Ralph's definition is what he does not say.
The form of the stored data has nothing to do with whether something is a data warehouse.

A data warehouse can be normalized or denormalized. It can be a relational database, multidimensional database, flat file, hierarchical database, object database, etc. Data warehouse data often gets changed. And data warehouses often focus on a specific activity or entity.
Data warehousing is not necessarily for the needs of "decision makers" or used in the process of decision making.

Of course if you want to define every user as a decision maker and all activities as decision making processes, then my assertion is false. But in my experience, the overwhelming uses of data warehouses are for quite mundane, non-decision making purposes rather than for grist for making decisions with wide ranging effects (so-called "strategic" decisions.). In fact, I would assert that most of data warehouses are used for post-decision monitoring of the effects of decisions - or, as some people might say, for "operational" issues. By the way, this is not saying that using data warehousing in the decision making process is not a wonderful, potentially high return effort. But my caution is that though the trade press, vendors, and many industry experts trumpet the role of data warehousing vis-à-vis decision making, in reality we do not now have nor will we ever have a clear understanding of decision making

Business Intelligence and Data Warehousing Solutions

Business decisions are only as good as the information on which they are based. Our Business Intelligence and Data Warehousing Solutions practice ensures the availability of business-critical information. It also opens the door to competitive advantage and a host of other benefits, allowing companies to substantially enhance bottom-line profitability. Patni has helped numerous enterprises address the challenge of unlocking the enterprise data resources that enable insight into competition, market dynamics, customers, products and operations.

Patni's Business Intelligence Solution Practice helps companies in a wide range of industries leverage business-critical information across the enterprise - an essential first step in making decisions that are intelligent and timely. Our Data Warehousing solutions allow you to access, analyze and share data from disparate sources; data silos are broken down and made accessible.

End-to-End Business Intelligence and
Data Warehousing Solutions Portfolio
Our range of high-quality, scalable Business Intelligence and Data Warehousing Solution offerings include:
Consulting
Customized Business Intelligence Solution Development
Application Management.

Patni has a proven Business Intelligence track record. Through hundreds of successful Business Intelligence and Data Warehousing projects, representing over 2,000 person-years of delivered effort, we have gained in-depth Business Intelligence expertise that spans domain knowledge, methodologies, technologies and tools. Customers in a wide range of industries - insurance, banking and financial services, manufacturing, telecom, utilities and retail, to name a few - rely on Patni's Business Intelligence solutions; these include more than 15 Fortune 100 companies.

Getting Started with Learning About Data Warehousing

Read up on some fundamental technical topics

If you are technical, you may find you will be greatly helped by reading up on SQL queries (especially multi-table and summary queries and subqueries), database indexing, join processing, and how query optimization works. The latter knowledge will most likely be found in books aimed at DBAs for specific commercial databases. This knowledge will help you even if you are not in a DBA role. - If you are not technical, you can still read primers on SQL and database design.

Visit a couple of organizations that have had data warehousing systems in production for over a year

You will get an excellent education if you can ask an organization who 'has done it' what are the biggest issues it faced in developing systems and what are the biggest issues it faces in maintaining systems. Also, ask what the organization felt it did right and what it felt it could have done differently. I believe that if you do this you will learn a great deal aspects of data warehousing that do not get discussed much in the literature - specifically the politics of data warehousing projects, the maintenance burdens data warehousing imposes, and how to deal with data warehousing software/hardware vendors and consultants. If you cannot visit other organizations, try going to vendor road shows or data warehousing conventions and talk with people with real experience with data warehousing. To repeat the point just made, too much about data warehousing goes unsaid by the media, the books, the vendors, and the consultants.

Download a trial copy of a query tool and an OLAP tool or an open source or free tool

Look for tools with sample data that you can experiment with on your own. The sample data is sure to highlight the tool's selling point. However, by playing with the tools you can get a feel of what companies use these tools for in real life.

Read this site

While this whole site is geared to the person getting started, the essays on a definition of data warehousing, the case for data warehousing, the case against data warehousing, aspects of data warehousing architecture, a definition of decision support, and what decision support tools are used for may be especially useful to a person new to the field.

Read the books "Building the Data Warehouse" by W. H. Inmon and "The Data Warehouse Toolkit" by Ralph Kimball

With due respect to all the other fine books on data warehousing and decision support, when read in combination I believe these two books provide a great introduction to and overview of the strategic and tactical issues system developers face - even though the original version of these books are over ten several years old. Despite what you read in the trade media, the basics of data warehousing do not change that much. Especially valuable are Inmon's overall overview and description of the iterative nature of data warehouse development and Kimball's description of data modeling principles and query/report tools. If you want to read further, check out Kimball's other books. Kimball stands out as a writer who is both substantive and easy to read. Finally, if you want a 10,000 foot, non-technical view of data warehousing in the business, read "Competing on Analytics: The New Science of Winning" by Thomas Davenport

Build something!

Computer texts love to cite a (supposedly) Confucian quote "What I hear I forget. What I see I remember. What I do I understand." Well, this quote is apt in the case of learning about data warehousing. After you build something, no matter how modest, you will gain a more profound appreciation of the topic.