In data warehousing, a dimension is a collection of reference information about a measurable event. In this context, events are known as "facts." Dimensions categorize and describe data warehouse facts and measures in ways that support meaningful answers to business questions. They form the very core of dimensional modeling.
A data warehouse organizes descriptive attributes as columns in dimension tables. For example, a customer dimension’s attributes could include first and last name, birth date, gender, etc., or a website dimension would include site name and URL attributes.
A dimension table has a primary key column that uniquely identifies each dimension record (row). The dimension table is associated with a fact table using this key. Data in the fact table can be filtered and grouped (“sliced and diced”) by various combinations of attributes. For example, a Login fact with Customer, Website, and Date dimensions can be queried for “number of males age 19-25 who logged in to funsportsite.com more than once during the last week of September 2010, grouped by day.”
Many dimensions contain a hierarchy of attributes that support drilling up and down. For example, a Date dimension could contain a hierarchy of year > quarter > month > week > date. A report displaying the number of website logins for 2009 by month could drill up to display logins by year, or drill down to display logins by day.
Dimensions are used in data warehouse star and snowflake schemas, OLAP cubes, and business intelligence (BI) and business analytic (BA) applications.The following dimensions can be used to meet specific data warehousing needs:
- junk dimensions - a collection of miscellaneous attributes that are unrelated to any particular dimension.
- degenerate dimensions - data that is dimensional in nature but stored in a fact table.
- role playing dimensions - a dimension that can play different roles in a fact table depending on the context.
- conformed dimensions - a dimension that has exactly the same meaning and content when being referred to from different fact tables.