About Data Warehouses

A data warehouse is often used as the basis for a decision support system. Data warehouses are designed to overcome problems encountered when an organization attempts to perform strategic analysis using the same database that is used for online transaction processing (OLTP).

OLTP systems typically:

• Support large numbers of concurrent users who are actively adding and modifying data.
  
  
• Represent the constantly changing state of an organization but don't save its history.
  
  
• Contain large amounts of data, including extensive data used to verify transactions.
  
  
• Have complex structures.
  
  
• Are tuned to be responsive to transaction activity.
  
  
• Provide the technology infrastructure to support the day-to-day operations of an organization.

Difficulties often encountered when OLTP databases are used for online analysis include the following:

• Analysts do not have the technical expertise required to create ad hoc queries against the complex data structure.
  
  
• Analytical queries that summarize large volumes of data adversely affect the ability of the system to respond to online transactions.
  
  
• System performance when responding to complex analysis queries can be slow or unpredictable, providing inadequate support to online analytical users.
  
  
• Constantly changing data interferes with the consistency of analytical information.
  
  
• Security becomes more complicated when online analysis is combined with online transaction processing.

Data warehousing provides one of the keys to solving these problems, by organizing data for the purpose of analysis. Data warehouses:

• Can combine data from heterogeneous data sources into a single homogenous structure.
  
  
• Organize data in simplified structures for efficiency of analytical queries rather than for transaction processing.
  
  
• Contain transformed data that is valid, consistent, consolidated, and formatted for analysis.
  
  
• Provide stable data that represents business history.
  
  
• Are updated periodically with additional data rather than frequent transactions.
  
  
• Simplify security requirements.
  
  
• Provide a database organized for OLAP rather than OLTP.

A data mart is a special form of data warehouse, typically containing a topic-oriented subset of enterprise data appropriate to a specific business function.

Microsoft® SQL Server™ 2000 provides many essential tools for building data warehouses and data marts, including Data Transformation Services (DTS).