Choose 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.
time code | order date | month code | month | quarter code | quarter | year |
---|---|---|---|---|---|---|
35276 | 07/31/2010 | 7 | july | 3 | third q | 2010 |
35277 | 08/01/2010 | 8 | aug | 3 | third q | 2010 |
35278 | 08/02/2010 | 8 | aug | 3 | third q | 2010 |
The previous table 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.
- Product dimension table
- The product code element joins the Sales fact table to the Product dimension table. The additional elements in the Product dimension table are: product name, vendor, vendor name, product line, and product line name.
- Time dimension table
- The time code element joins the Sales fact table to the Time dimension table. The additional elements in the Time dimension table are: order date, month, quarter, and year.
- Geography dimension table
- The district code element joins the Sales fact table to the Geography dimension table. The additional elements in the Geography dimension table are: district, state, state name, and region.
- Customer dimension table
- The customer code element joins Sales fact table to Customer dimension table. The additional elements in the Customer dimension table are: customer name and company.