The primary goal in designing dimension tables is to denormalize the data that references the fact tables into single tables. The most commonly used dimension data should reference the fact tables directly, rather than indirectly through other tables. This approach minimizes the number of table joins, and speeds up performance. For example, the order entry star schema should support the business queries:
Currently, the dimension data describing a region (City, StateOrProvince, and Country) is part of the Customers table. However, Customers references Order_Details (fact data) using Orders.
To better support the business analysis required, the region data should be placed into a new table, Region, directly referencing Order_Details. To implement this, a foreign key from the Region dimension table is added to Order_Details (now renamed to Sales). Any queries involving sales per region now require only a two-table join between the Region dimension table, and the Sales fact table.
Note The existing relationship between the Sales fact table and the Products dimension data is unchanged.
Date information is a common requirement in a data warehouse. To minimize the fact table width, a foreign key is often created in the fact table referencing a dimension table containing a representation of the date and/or time. The representation of the date depends on business analysis requirements.
For example, the business analysis to be performed on the order entry system requires product sales summarized by month, quarter, and year. The date information should be stored in a form that represents these increments. This is achieved by creating a foreign key in the fact table referencing a date dimension table (named Period) containing the date of the sale in a month, quarter, year format. To increase the flexibility of this data, additional dimension tables are created, referenced by the Period dimension table, that contain months, quarters, and years in more detail. When designing dimension tables for use with Microsoft® SQL Server™ OLAP Services, only a date is needed. The OLAP Services Time Wizard enables dates to be summarized into any combination of weeks, months, quarters, and years.
