Data WareHousing

What is data warehousing?

page 1 of 6

Systems that contain operational data -- the data that runs the daily transactions of a business -- contain information that business analysts can use to better understand how the business is operating. For example, they can see which products were sold in which regions at which time of year. This helps identify anomalies or to project future sales.

However, there are several problems if analysts access operational data directly:

• They might not have the expertise to query the operational database. For example, querying IMS databases requires an application program that uses a specialized type of data-manipulation language. In general, the programmers who have the expertise to query the operational database have a full-time job in maintaining the database and its applications.

• Performance is critical for many operational databases, such as databases for a bank. The system cannot handle users making ad-hoc queries on operational data stores. Imagine that you are doing your banking on the Internet and paying bills. When you hit the OK button, it usually takes only a few seconds to process a payment. Now, consider a bank analyst trying to figure out how to make more money from an existing customer base. The analyst runs a query which is so complex that your transaction now takes about 30 seconds to complete. Obviously that performance time is not acceptable (and neither are the new charges that the analyst is dreaming up). For this reason, operational data stores and reporting data stores (including OLAP databases) are generally separated.

  However, over the last few years, reporting data stores have tended to become pseudo-operational and current. Such stores are called operation data stores (ODSs). Consider the telecommunications industry, for example. ODSs are popular with these companies, as they try to identify fraudulent charges as soon as possible. DB2 is one of the few databases that is well suited for both operational and reporting workloads.

• Operational data is not generally in the best format for use by business analysts. For example, sales data that is summarized by product, region, and season is much more useful to analysts than raw data.

Data warehousing solves these problems. In data warehousing, you create stores of informational data -- data that is extracted from operational data and then transformed and cleansed for end-user decision making. For example, a data warehousing tool might copy all the sales data from the operational database, perform calculations to summarize the data, and write the summarized data to a database that is separate from the operational data. End users can query the separate database (the warehouse) without affecting the operational databases.

By IBM