Dimension Table

A dimension table contains part of the context that describes the facts. There are multiple dimension tables in a star or snowflake schema, one for each logical grouping of the contextual elements. For example, in this star schema the elements that describe the time period to which the salary dollars belong are in the DIM_MONTH table, the elements that describe the people to which the salary dollars belong are in the DIM_EMPLOYEE table, and the elements that describe the type of compensation (i.e., net pay, gross pay, etc.) are in the DIM_PAY_TYPE table.

When a SQL statement is written to analyze data in the star or snowflake schema, the analyst will aggregate the facts and group by the dimensions.

Notice that the fact table in this particular star schema contains a field called DD_PAYCHECK_NO. Occasionally it is appropriate for a dimension value to be placed directly into the fact table. This is called a degenerate dimension. Sometimes a single dimension value is needed but does not fit into any other logical grouping of contextual elements. Other times, a dimension value applies to only one fact table row, meaning that if that element is placed into a dimension table, that table will contain the same number of rows as the fact table. In cases like these a degenerate dimension may be a part of the design.