Data Access and Transactions

Data Warehousing and Online Analytical Processing

As organizations collect increasing volumes of data, the need to analyze and derive conclusions from raw data becomes more acute. A data warehouse is often used as the basis for a decision-support system (also referred to as a business intelligence system). It is designed to overcome some of the problems encountered when an organization attempts to perform strategic analysis using the same database that is used to perform online transaction processing (OLTP).

Typically, OLTP systems are designed specifically to manage transaction processing and minimize disk storage requirements using a series of related and normalized tables. However, when users need to analyze their data, a myriad of problems often prohibits the data from being used:

Application databases can be segmented across multiple servers, making it difficult for users to find the data in the first place.
Users may not understand the complex relationships among the tables, and therefore cannot generate ad hoc queries.
Security restrictions may prevent users from accessing the detailed data they need. Database administrators prohibit ad hoc querying of OLTP systems. This prevents analytical users from running queries that could slow down the performance of mission-critical production databases.

Data warehousing offers a viable solution to these problems. Basically, data warehousing is an approach to storing data in which heterogeneous data sources from across the enterprise (typically from multiple OLTP databases) are migrated to a common homogenous data store. Sometimes organizations maintain smaller, more topic-oriented data stores called data marts. Whereas data warehouses or data marts are the stores for data, online analytical processing (OLAP) is the technology that enables client applications to efficiently process the data. Data warehouses (combined with OLAP) provide the following benefits to analytical users:

Differences among data structures across multiple heterogeneous databases can be resolved. Data transformation rules can be applied to validate and consolidate data, when data is moved from the OLTP database into the data warehouse.
Data is organized to facilitate analytical queries rather than transaction processing. Frequently-queried data is pre-aggregated and the results are stored as “cubes,” which are table-like structures that enable very fast response time to ad hoc queries.
Security and performance issues can be resolved without requiring changes in the production systems. OLAP is a powerful tool for creating new views of data, based upon a rich array of ad hoc calculation functions.