Minimizing 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.

Figure 44 shows a minidimension table for demographic information that is separated out from a customer table.

Figure 44. A Minidimension Table for Demographics Information

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.