The case for data warehousing. (Data Warehousing).

Too much of the data warehousing literature confuses "next order" benefits, with the basic reasons why organisations implement data warehouses. A little time reading data warehouse trade material indicates that the purpose of using a data warehouse is 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 in order that transactions will be completed in 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, and can lower the probability that transactions complete in an acceptable amount of time. Also running queries and reports, with their variable resource requirements, on the servers/disks used by transaction processing systems can make it complex to manage servers/disks in such a manner that there is a high enough probability that acceptable response time can be achieved. Firms 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 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, as the modeling technique will slow down, and complicate, transaction processing. Also, there are server technologies that may speed up query and reporting processing but 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.). Note; the degree to which a modeling technique, or server technology, is a help or hindrance to querying/reporting and transaction processing, depends on the different vendors products, and the situation in which the technique or technology is applied.

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 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, who may "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 he 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

The data warehouse provides an opportunity to clean up the data without changing the transaction processing systems. However, 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 needing reports using data from multiple systems, have been writing data extracts, running sort/merge logic to combine the extracted data, then running reports against the sort/merged data. In many cases this is a an adequate strategy. However, if a company has large amounts of data that need to be sort/merged frequently, and data purged from transaction processing has to be reported on, and most importantly, if the data has 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

Older data are often purged from transaction processing systems so the expected response time can he better controlled For querying and reporting, this purged data and the current data may he stored in the data warehouse where there is less of a need to control expected response time, or the expected response time is at a much higher level. Regarding "as was" reporting, sometimes it is difficult, if not impossible, to generate a report based on some characteristic at a previous point in time. For example, if you want a report of the salaries of employees at grade Level 3 as of the beginning of each month in 1997, you may not be able to do this because you only have a record of current employee grade level. To be able to handle this type of reporting problem, firms may implement data warehouses that handle what is called the "slowly changing dimension" issue.

To prevent persons who only need to query and report transaction processing system data, from having access to transaction processing system databases, and the logic used in their maintenance.

(The concern here is security. For example, data warehousing may be of value to firms that wish to allow report and querying only over the Internet.)

Some firms implement data warehousing for all the reasons cited. Some implement data warehousing for only one of the reasons quoted. I am not saying that a data warehouse has no "business" objectives. (I grit my teeth when I say that because I am not one to assume that an IT objective is not a business objective. IT people are business people too.) I do believe that the achievement of a "business" objective for a data warehouse necessarily comes about because of the achievement of one or many of the above objectives.

Examining the list reveals that the need for data warehousing is mainly caused by the limitations of transaction processing systems which are not, however, inherent. That is, the limitations will not be in every implementation of a transaction processing system, and will vary in degree of crippling capacity.

Finally, a firm that expects to get business intelligence, better decision making, closeness to its customers, and competitive advantage simply by plopping down a data warehouse is in for a surprise. Obtaining these next order benefits requires firms to figure out, usually by trial and error, how to change business practices to best use the data warehouse and then to change their business practices. That can be harder than implementing a data warehouse. www.dwinfocenter.org/casefor.html.