DATA  WAREHOUSE

    A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect.  Typically, a data warehouse is housed on an enterprise mainframe server.  Data from various online transaction processing (OLTP) applications and other sources is selectively extracted and organized on the data warehouse database for use by analytical applications and queries.

    The data warehouse concept evolved from the growing competitive need to quickly analyze business information.  The existing institutional systems were not set up to optimize data retrieval and the process was difficult, tedious, and subject to a variety of errors.  Existing operational systems could not meet this need because:

    - lack of on-line historical data

    - data required for analysis resided in different operational systems

    - operational database designs were inappropriate for decision support.

A data warehouse eliminates these problems by storing the current and historical data from disparate operational systems that business decision makers need in a single, consolidated system.  This makes data readily accessible to people who need it without interrupting on-line operational workloads.  (Click here for a look at the data warehouse at Syracuse University.)

    There a several business benefits from data warehousing:

1) Immediate information delivery.  Data warehouses shrink the length of the time between when business events actually occur and when executives are made aware of  them.  For example, in many corporations, sales reports are printed about once a month - sometimes as late as a week after the end of the month.  Thus, the June sales reports are delivered not until perhaps the first week of July.  Using a warehouse, those same reports are available on a daily basis.  Given this data time compression, business decision makers can exploit business opportunities that they would otherwise miss.

2) Data integration from across, and even outside, the organization.  To provide a complete picture, warehouses typically combine data from multiple sources such as a company's order entry and warranty systems.  Thus, with a warehouse, it may be possible to track all transactions a company has with each customer - from that customer's first inquiry, through the terms of their purchase, all the way through any warranty or service transactions.  This makes it possible for managers to obtain answers to such questions as, "Is there a correlation between where a customer buys our product and the amount typically spent in supporting that customer?"

3) Future vision from historical trends.  Effective business analysis frequently includes trend and seasonality analysis.  To support this, warehouses typically contain multiple years of data.

4) Tools for looking at data in new ways.  Instead of paper reports, warehouses give users tools for looking at data differently.  They also allow those users the ability to manipulate their data.  There are times when a color-coded map or bar-chart may speak volumes over a simple paper report.  An interactive table that allows the user to drill down into the detail data with the click of a mouse can answer questions that might take months to answer in a traditional system.

5) Freedom from IS department resource limitations.  One of the problems with computer systems is that they oftentimes require computer experts to use them.  In the old days, when a report was needed, the requesting manager called the IS department.  IS then assigned a programmer to create the report - sometimes the report could be created in a few days, sometimes in over a year!  With a warehouse, users create most of their reports themselves.  Thus, if a manager needs a special report for a meeting in, say, half and hour, they, or their assistant, can create that report in a matter of minutes.

(Click here for guidance in estimating the ROI of a data warehouse.)

    The strategic benefits from data warehousing can be looked at from an accounting perspective.  A data warehouse presents both threats and opportunities for a company's accounting department.  While the numerous exciting advantages of data warehousing (see above) have been widely discussed,  what has not been widely anticipated is the impact that data warehouses may have on accounting practice.  The impact is likely to occur in two principal areas.  One is that audit trails may now involve the data warehouse itself either as the source of figures or as a check on figures obtained elsewhere.  The second, is the potential to use the warehouse in conducting the audit, thereby saving time and money.  It is this second impact that is of prticular import to accountants.

    Conventional database management systems (DBMS) otherwise known as online transaction processing (OLTP) systems, used in business for the last 15 years, are largely passive.  That is, they function strictly as data repositories, but with appropriate tools for querying and building large-scale applications.  These conventional DBMS function well in an environment characterized by simple, short-lived transactions that have long been common in accounting systems.  In other words, these  conventional databases are effective in processing standard transactions.  However, they lack the necessary features to support the following advanced transactions that are becoming increasingly common:

    - transactions involving such complex data types as graphics and multimedia

    - transactions involving time-oriented data (where different dimensions of  time are critical)

    - transactions in which data must be available on the World Wide Web or integrated with Internet/intranet technologies

    - transactions in which deadlines are critical

    - transactions in which decision support is the main focus as opposed to operations

So, in summary, whereas conventional DBMS are sufficient for managing standard transactions, they function poorly at data warehousing and data mining tasks.  Data warehousing is a readily available solution to the limitations that are inherent in conventional DBMS.

    To understand data warehousing, one must contrast OLTP systems with the data warehousing approach, which is characterized by online analytical processing (OLAP) systems.  Standard OLTP systems are used to create operational data and reports in business areas.  A data warehouse (OLAP), on the other hand, is created from data that already exists in an organization as well as from external data that is useful to analyze.

    The audit function seems to be a natural application for data warehousing.  To illustrate, assume that a company employs substantial data warehousing technology.  Further assume that the company has operational data for the last ten years as well as external industry data.  The auditors can examine the data warehouse to determine whether there are any major changes in financial condition that would cause concern.  Assume that the auditors notice that bad debt expense is 1.1 percent of sales for the current year versus .9 percent for the prior year.  Under normal circumstances this might be a cause for concern that might lead to additional testing, examination of transactions, and the sending of letters to customers, all of which increases the cost of the audit.  However, assume that the auditors examine the data warehouse and find that the bad debt expense was actually at the second lowest level of any year during the last 15 years and was actually lower than the industry average for the current year.  The auditors could actually reduce their reliance on testing transactions because bad debts is probably not a cause for concern, given the deeper analysis provided by the data warehouse.  This admittedly simple example illustrates a significant point: data warehouse technology can facilitate improved audits at reduced cost!

http://www.kenorrinst.com/dwpaper.html - a good explanation of basic data warehouse architecture

http://www.datawarehousing.com - This site is dedicated to documenting ALL  DATA  WAREHOUSING  RELATED  INFORMATION  ON  THE   INTERNET!

http://www.mcknight-associates.com - homepage of The Data Warehousing Institute

http://www.dci.com/speakers/archive/zornes.htm - a taxonomy of corporate data warehouses

http://www.as400.ibm.com/db2/dataware.htm - what IBM has to say about data warehousing

http://system-services.com/dwintro.asp - a good intro to data warehousing from System Services Corporation

http://www.dwcleads.com - a data warehouse for the mortgage industry: access to over 100 million consumers

http://www.businessguidance.com - Business Guidance Systems, Inc. - a data warehousing consulting firm

http://www.sqlmag.com - SQL (Structured Query Language) Server Magazine

http://www.dmreview.com/awards/top100 - 1999 Data Warehouse Top 100 Awards, as selected by readers of "DM Review"