A dimension table or dimension entity is a table or entity in a star, snowflake, or starflake schema that
stores details about the facts. For example, a Time dimension table
stores the various aspects of time such as year, quarter, month, and
day.
Dimension table store descriptive information about the
numerical values in a fact table. For example, dimension tables for
a marketing analysis application might include Time Period, Marketing
Region, and Product Type.
Dimension tables describe the different
aspects of a business process. For example, if you are looking to
determine the sales targets, you can store the attributes of the sales
targets in a dimension table. Dimension tables group the data in the
database when the business creates reports. For example, you can group
sales targets by country, product, or retailer, and those groupings
are stored in dimension tables.
Each dimension table contains
a number of columns and attributes that are used to describe business
processes.
Since the data in a dimension table is often denormalized,
dimension tables have a large number of columns. The dimension tables
contain fewer rows of data than the fact table. The columns of a dimensional
table are used to create reports or display query results. For example,
the textual descriptions of a report are created from the column labels
of a dimension table.
Consider the following points when you
create your dimension tables:
- Grain
- Each dimension table has only one element at the lowest level
of detail, and this element is known as the grain of
the dimension.
- Non-key elements
- Each non-key element should appear in only one dimension table.
- Time and date dimensions
- You will typically have multiple time and date dimensions in your
dimensional model.
- Number of dimensions
- Dimensional models typically contain only 10 to 15 dimension tables.
If you need more dimensions, merge those dimension tables into a single
table.
- Creating one-to-many relationships
- The rows in a dimension table establish a one-to-many relationship
with the fact table or outriggers.
- Shared dimensions
- Typically, dimension tables that are shared by multiple fact tables
(or multiple dimensional models) are called shared dimensions. If shared dimensions already exist for any of the dimensions in
the data warehouse or dimensional model, you should use the shared
dimensions. If you are developing new dimensions that may be used
across the entire enterprise warehouse, you should develop a design
that anticipates the needs of the enterprise warehouse.