Guide for Data Warehouse Concept
A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for management's decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries and analysis, such as data mining, on the information without slowing down the operational systems...
Bill Inmon, an early and influential practitioner, has formally defined a data warehouse in the following terms;
- Subject-oriented
- The data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
- Time-variant
- The changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
- Non-volatile
- Data in the database is never over-written or deleted - once committed, the data is static, read-only, but retained for future reporting; and
- Integrated
- The database contains data from most or all of an organization's operational applications, and that this data is made consistent.
A data warehouse might be used to find the day of the week on which a company sold the most widgets in May 1992, or how employee sick leave the week before the winter break differed between California and New York from 2001–2005.
While operational systems are optimized for simplicity and speed of modification (see OLTP) through heavy use of database normalization and an entity-relationship model, the data warehouse is optimized for reporting and analysis (online analytical processing, or OLAP). Frequently data in data warehouses are heavily denormalised, summarised or stored in a dimension-based model. However, this is not always required to achieve acceptable query response times
Architecture
The concept of "data warehousing" dates back at least to the mid-1980s, and possibly earlier. In essence, it was intended to provide an architectural model for the flow of data from operational systems to decision support environments. It attempted to address the various problems associated with this flow, and the high costs associated with it. In the absence of such an architecture, there usually existed an enormous amount of redundancy in the delivery of management information. In larger corporations it was typical for multiple decision support projects to operate independently, each serving different users but often requiring much of the same data. The process of gathering, cleaning and integrating data from various sources, often legacy systems, was typically replicated for each project. Moreover, legacy systems were frequently being revisited as new requirements emerged, each requiring a subtly different view of the legacy data.
Advantages
There are many advantages to using a data warehouse, some of them are:
- Data warehouses enhance end-user access to a wide variety of data.
- Decision support system users can obtain specified trend reports, e.g. the item with the most sales in a particular area within the last two years.
- Data warehouses can be a significant enabler of commercial business applications, particularly customer relationship management (CRM) systems.
Concerns
- Extracting, transforming and loading data consumes a lot of time and computational resources.
- Data warehousing project scope must be actively managed to deliver a release of defined content and value.
- Compatibility problems with systems already in place.
- Security could develop into a serious issue, especially if the data warehouse is web accessible.
- Data Storage design controversy warrants careful consideration and perhaps prototyping of the data warehouse solution for each project's environments.
