» » » » » Data warehouse

Data warehouse

In computing, a data warehouse (DW) is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.

A data warehouse maintains its functions in three layers: staging, integration, and access. Staging is used to store raw data for use by developers. The integration layer is used to integrate data and to have a level of abstraction from users. The access layer is for getting data out for users.

This definition of the data warehouse focuses on data storage. The main source of the data is cleaned, transformed, catalogued and made available for use by managers and other business professionals for data mining, online analytical processing, market research and decision support (Marakas & O’Brien 2009). However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform and load data into the repository, and tools to manage and retrieve metadata.

Benefits of a data warehouse

A data warehouse maintains a copy of information from the source transaction systems. This architectural complexity provides the opportunity to:

  • Maintain data history, even if the source transaction systems do not.
  • Integrate data from multiple source systems, enabling a central view across the enterprise. This benefit is always valuable, but particularly so when the organization has grown by merger.
  • Improve data, by providing consistent codes and descriptions, flagging or even fixing bad data.
  • Present the organization’s information consistently.
  • Provide a single common data model for all data of interest regardless of the data’s source.
  • Restructure the data so that it makes sense to the business users.
  • Restructure the data so that it delivers excellent query performance, even for complex analytic queries, without impacting the operational systems.
  • Add value to operational business applications, notably customer relationship management (CRM) systems.

History

The concept of data warehousing dates back to the late 1980s [1] when IBM researchers Barry Devlin and Paul Murphy developed the “business data warehouse”. In essence, the data warehousing concept was intended to provide an architectural model for the flow of data from operational systems to decision support environments. The concept attempted to address the various problems associated with this flow, mainly the high costs associated with it. In the absence of a data warehousing architecture, an enormous amount of redundancy was required to support multiple decision support environments. In larger corporations it was typical for multiple decision support environments to operate independently. Though each environment served different users, they often required much of the same stored data. The process of gathering, cleaning and integrating data from various sources, usually from long-term existing operational systems (usually referred to as legacy systems), was typically in part replicated for each environment. Moreover, the operational systems were frequently reexamined as new decision support requirements emerged. Often new requirements necessitated gathering, cleaning and integrating new data from “data marts” that were tailored for ready access by users.

Key developments in early years of data warehousing were:

  • 1960s — General Mills and Dartmouth College, in a joint research project, develop the terms dimensions and facts.[2]
  • 1970s — ACNielsen and IRI provide dimensional data marts for retail sales.[2]
  • 1970s — Bill Inmon begins to define and discuss the term: Data Warehouse
  • 1975 — Sperry Univac Introduce MAPPER (MAintain, Prepare, and Produce Executive Reports) is a database management and reporting system that includes the world’s first 4GL. It was the first platform specifically designed for building Information Centers (a forerunner of contemporary Enterprise Data Warehousing platforms)
  • 1983 — Teradata introduces a database management system specifically designed for decision support.
  • 1983 — Sperry Corporation Martyn Richard Jones defines the Sperry Information Center approach, which whilst not being a true DW in the Inmon sense, did contain many of the characteristics of DW structures and process as defined previously by Inmon, and later by Devlin. First used at the TSB England & Wales
  • 1984 — Metaphor Computer Systems, founded by David Liddle and Don Massaro, releases Data Interpretation System (DIS). DIS was a hardware/software package and GUI for business users to create a database management and analytic system.
  • 1988 — Barry Devlin and Paul Murphy publish the article An architecture for a business and information systems in IBM Systems Journal where they introduce the term “business data warehouse”.
  • 1990 — Red Brick Systems, founded by Ralph Kimball, introduces Red Brick Warehouse, a database management system specifically for data warehousing.
  • 1991 — Prism Solutions, founded by Bill Inmon, introduces Prism Warehouse Manager, software for developing a data warehouse.
  • 1992 — Bill Inmon publishes the book Building the Data Warehouse.[3]
  • 1995 — The Data Warehousing Institute, a for-profit organization that promotes data warehousing, is founded.
  • 1996 — Ralph Kimball publishes the book The Data Warehouse Toolkit.[4]
  • 2000 — Daniel Linstedt releases the Data Vault, enabling real time auditable Data Warehouses warehouse.

Data warehouses versus operational systems

Operational systems are optimized for preservation of data integrity and speed of recording of business transactions through use of database normalization and an entity-relationship model. Operational system designers generally follow the Codd rules of database normalization in order to ensure data integrity. Codd defined five increasingly stringent rules of normalization. Fully normalized database designs (that is, those satisfying all five Codd rules) often result in information from a business transaction being stored in dozens to hundreds of tables. Relational databases are efficient at managing the relationships between these tables. The databases have very fast insert/update performance because only a small amount of data in those tables is affected each time a transaction is processed. Finally, in order to improve performance, older data are usually periodically purged from operational systems.

Data warehouses are optimized for speed of data analysis. Frequently data in data warehouses are denormalised via a dimension-based model. Also, to speed data retrieval, data warehouse data are often stored multiple times—in their most granular form and in summarized forms called aggregates. Data warehouse data are gathered from the operational systems and held in the data warehouse even after the data has been purged from the operational systems.

Evolution in organization use

These terms refer to the level of sophistication of a data warehouse:

Offline operational data warehouse
Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data
Offline data warehouse 
Data warehouses at this stage are updated from data in the operational systems on a regular basis and the data warehouse data are stored in a data structure designed to facilitate reporting.
On time data warehouse 
Online Integrated Data Warehousing represent the real time Data warehouses stage data in the warehouse is updated for every transaction performed on the source data
Integrated data warehouse 
These data warehouses assemble data from different areas of business, so users can look up the information they need across other systems.[5]

Sample applications

Some of the applications data warehousing can be used for are:

  • Decision support
  • Trend analysis
  • Financial forecasting
  • Churn Prediction for Telecom subscribers, Credit Card users etc.
  • Insurance fraud analysis
  • Call record analysis
  • Logistics and Inventory management
  • Agriculture [6]

Future

Data warehousing, like any technology, has a history of innovations that did not receive market acceptance.[7]

A 2009 Gartner paper predicted these developments in business intelligence/data warehousing market.[8]

  • Because of lack of information, processes, and tools, through 2012, more than 35 percent of the top 5,000 global companies will regularly fail to make insightful decisions about significant changes in their business and markets.
  • By 2012, business units will control at least 40 percent of the total budget for business intelligence.
  • By 2010, 20 percent of organizations will have an industry-specific analytic application delivered via software as a service as a standard component of their business intelligence portfolio.
  • In 2009, collaborative decision making will emerge as a new product category that combines social software with business intelligence platform capabilities.
  • By 2012, one-third of analytic applications applied to business processes will be delivered through coarse-grained application mashups.

References

  1. ^ “The Story So Far”. 2002-04-15. Retrieved 2008-09-21.
  2. ^ a b Kimball 2002, pg. 16
  3. ^ Inmon, Bill (1992). Building the Data Warehouse. Wiley. ISBN 0471569607.
  4. ^ Kimball, Ralph (1996). The Data Warehouse Toolkit. Wiley. ISBN 0471153370.
  5. ^ “Data Warehouse”.
  6. ^ Abdullah, Ahsan (2009). “Analysis of mealybug incidence on the cotton crop using ADSS-OLAP (Online Analytical Processing) tool , Volume 69, Issue 1”. Computers and Electronics in Agriculture 69: 59–72. doi:10.1016/j.compag.2009.07.003.
  7. ^ Pendse, Nigel and Bange, Carsten “The Missing Next Big Things”
  8. ^ “Gartner Reveals Five Business Intelligence Predictions for 2009 and Beyond”

Further reading

  • Davenport, Thomas H. and Harris, Jeanne G. Competing on Analytics: The New Science of Winning (2007) Harvard Business School Press. ISBN 978-1-4221-0332-6
  • Ganczarski, Joe. Data Warehouse Implementations: Critical Implementation Factors Study (2009) VDM Verlag ISBN 3-639-18589-7 ISBN 978-3-639-18589-8
  • Kimball, Ralph and Ross, Margy. The Data Warehouse Toolkit Second Edition (2002) John Wiley and Sons, Inc. ISBN 0-471-20024-7
  • Linstedt, Graziano, Hultgren. The Business of Data Vault Modeling Second Edition (2010) Dan linstedt, ISBN 978-1-4357-1914-9

Links

This article is licensed under the GNU Free Documentation License. It uses material from the Wikipedia.

Leave a Reply

Your email address will not be published. Required fields are marked *