The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

3.3. Architecture of a Microsoft DW/BI System

All DW/BI systems consist of several major components, as pictured in Figure 3.2: sources of data, an ETL system, data warehouse databases, and a wide variety of uses. Metadata is the glue that binds together the complete DW/BI system.

As we explained in Chapter 2, the data warehouse databases should be in a dimensional form, consisting of fact tables and their associated dimension tables. Dimensions should be conformed across the enterprise. All business processes that are described by the customer dimension should use the same customer dimension with the same keys.

The primary place to store and manage the dimensional model is in the relational data warehouse database. In Microsoft terms, this is the SQL Server 2005 database engine. You will write an ETL system that populates that database, performing inserts and updates, and perhaps also managing system resources such as disk space and indexes.

The second place to store and manage the dimensional model is in the OLAP data warehouse database. In Microsoft terms, this is the Analysis Services OLAP engine. We recommend that you always build the OLAP database from a clean, conformed relational data warehouse database.

As we describe in Chapter 8, there are many kinds of BI applications, ranging from standard predefined reports to complex analytic applications that use data mining technology to affect business operations. Microsoft offers many technologies here, from Reporting Services ...