Home | Previous Page | Next Page   Dimensional Databases > Building a Dimensional Data Model > Building a Dimensional Data Model >

Choosing the Attributes for the Dimension Tables

After you complete the fact table, you can decide the dimension attributes for each of the dimension tables. To illustrate how to choose the attributes, consider the time dimension. The data model for the sales business process defines a granularity of day that corresponds to the time dimension, so that each record in the time dimension table represents a day. Keep in mind that each field of the table is defined by the particular day the record represents.

The analysis of the sales business process also indicates that the marketing department needs monthly, quarterly, and annual reports, so the time dimension includes the elements: day, month, quarter, and year. Each element is assigned an attribute that describes the element and a code attribute (to avoid column values that contain long character strings). Table 10 shows the attributes for the time dimension table and sample values for each field of the table.

Table 10. Attributes for the Time Dimension
time code order date month code month quarter code quarter year
35276 07/31/1999 7 july 3 third q 1999
35277 08/01/1999 8 aug 3 third q 1999
35278 08/02/1999 8 aug 3 third q 1999

Table 10 shows that the attribute names you assign should be familiar business terms that make it easy for end users to form queries on the database. Figure 43 shows the completed data model for the sales business process with all the attributes defined for each dimension table.

Figure 43. The Completed Dimensional Data Model for the Sales Business Process
begin figure description - This figure is described in the surrounding text. - end figure description

Tip:
The number of attributes that you define on each dimension table should generally be kept to a minimum. Dimension tables with too many attributes can lead to excessively wide rows and poor performance. For more information, see Minimizing the Number of Attributes in a Dimension Table.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]