Minimize the number of attributes in a dimension table

Dimension tables that contain customer or product information might easily have 50 to 100 attributes and many millions of rows. However, dimension tables with too many attributes can lead to excessively wide rows and poor performance. For this reason, you might want to separate out certain groups of attributes from a dimension table and put them in a separate table called a minidimension table. A minidimension table consists of a small group of attributes that are separated out from a larger dimension table. You might choose to create a minidimension table for attributes that have either of the following characteristics:
  • The fields are rarely used as constraints in a query.
  • The fields are frequently compared together.
The following figure shows a minidimension table for demographic information that is separated out from a customer table.
Figure 1: A Minidimension Table for Demographics Information

Three tables are shown: "Customer Table", "Demographics Table", and "Fact table". Two arrows that have points on both ends illustrate the joins between the fact able and the other two tables. The "Customer Table" is joined to the "Fact Table" by the column "customer code". Other columns in "Customer Table" are: "customer name" and "demographics code". There is a vertical ellipses in the table indicating that there are more columns than are shown here. The "Demographics Table" is joined to the "Fact Table" by the column "demographics code". Other columns in "Demographics Table" are: "income level" and "marital status". There is a vertical ellipses in the table indicating that there are more columns than are shown here. The "Fact Table" contains the columns "customer code", which joins to "Customer Table", and "demographics code", which joins to "Demographics Table". There is a vertical ellipses in the table indicating that there are more columns than are shown here.

In the demographics table, you can store the demographics key as a foreign key in both the fact table and the customer table, which allows you to join the demographics table directly to the fact table. You can also use the demographics key directly with the customer table to browse demographic attributes.