Export (0) Print
Expand All

Creating the Data Warehouse Database

SQL Server 2000

You can create the data warehouse database after the data warehouse schema has been designed. You will need to create tables for facts and dimensions, and establish indexes on key fields in all tables.

The data warehouse database schema is often quite simple compared to those of OLTP databases or the data preparation area. A star schema consists of a single fact table and a number of dimension tables. A snowflake schema adds secondary dimension tables. More complex data warehouses may contain multiple fact tables and a number of dimension tables, some of which are common to all fact tables and others that are specific to a single fact table.

For example, a data warehouse may contain both sales information and inventory information. Because sales data and inventory data are different in nature, they should be stored in different fact tables. Some dimension tables, such as a product dimension table, might be common to both sales and inventory, whereas others, such as sales force or warehouse location, might be specific to individual fact tables.

The FoodMart 2000 sample database provided with Microsoft® SQL Server™ 2000 Analysis Services illustrates a data warehouse that contains both inventory and sales data. For more information, see Analysis Services Overview.

See Also

Designing a Data Warehouse

Creating and Maintaining Databases Overview

© 2014 Microsoft