Implementing the Database Design

After the fact and dimension tables have been designed, the final step is to physically implement the database in Microsoft® SQL Server™.

Creating the Database

When creating the database, consider the partitioning strategy, if any, that may be used. SQL Server offers filegroups that can be used to stripe data, in addition to the disk striping available with Microsoft Windows NT®, and hardware-based implementations.

Creating the Tables

When creating the tables used to store the fact and dimension data, consider creating the tables across the partitions available to the database, based on usage. For example, create separate fact tables containing data segmented by year or division on separate partitions (such as a SQL Server filegroup to improve read performance).

Creating Any User-defined Views

Create user-defined views if necessary. SQL Server views can be used to merge horizontally partitioned tables together logically, as interfaces to predefined queries or as a security mechanism.

Creating Indexes

Indexes should be created to maximize performance. Consider creating indexes on:

• Key columns.
• Columns involved in joins.
• Multiple columns, to take advantage of index coverage.
• All dimension table keys used by the fact table.

See Also