Relational databases are the underlying engines that provide power to data warehouses. Many of the characteristics and features that have been developed and enhanced to make relational databases the workhorses of online transactional processing (OLTP) systems are directly applicable to data warehouses.
Relational databases are used in data warehouse systems to stage, cleanse, and transform incoming data in the data preparation database, contain and manage the massive quantities of data in the data warehouse database, and support data marts.
Data warehouses store, manage, and manipulate huge quantities of data, often on the order of hundreds of millions of rows of historical information. The relational database must provide rapid data transfer and update, flexible and efficient indexing, and sophisticated and effective query capabilities to organize and retrieve data warehouse data. Sophisticated locking mechanisms and high multi-table transaction throughput may be more important in OLTP systems than in data warehouses, but such features are often based on extremely efficient relational engine design, which is very important in data warehouse operations.
Microsoft® SQL Server™ 2000 provides an extremely powerful relational database for OLTP systems and data warehouse data storage. It also includes many powerful features critical to data warehouses, such as Data Transformation Services (DTS), replication management, SQL Server 2000 Analysis Services with its multidimensional online analytical processing (OLAP) and data mining server and management support, SQL Server 2000 Meta Data Services, and English Query for natural language querying of both relational and multidimensional data.