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