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

4.4. Physical Data Warehouse Database Design

Before you start the relational database physical design process, you should have completed the logical model. There is very little to do between the logical and physical models. Here are the basic steps, which we discuss in greater detail in the pages to follow:

• Ensure that object names match the naming conventions. Your logical model should already be using good, clear, sensible names that conform to your naming conventions. You should already have defined naming conventions for database objects such as tables and columns.

• Ensure that each column has the correct data type. Start off with the column definitions from the modeling process, but you may need to modify data types later, after you've completed the data profiling discussed in Chapter 2. For example, you may learn that some customer surnames take more than the 35 characters you originally assumed would suffice.

• Specify how to handle a changed value for each dimension attribute.

• Identify the levels of natural hierarchies, such as the familiar Year to Quarter to Month to Day.

• Decide whether you will declare foreign keys in the database for all, some, or none of the logical foreign key relationships.

• Develop your initial indexing plan.

• Develop your fact table partitioning plan.

• Specify dimension and fact metadata: process-related metadata that you may choose to include in each dimension and fact row. These meta-data elements were introduced in Chapter 2, and are discussed further in ...