Table of Contents
Dimension & Dimension table
Dimension
A dimension is a structure that categorizes facts and measures in order to enable users to answer business questions.
- Commonly used dimensions are people, products, place and time.
- The dimension is a data set composed of individual, non-overlapping data elements.
- In a data warehouse, Dimensions provide structured labeling information to otherwise unordered numeric measures.
- The primary functions of dimensions are threefold:
- to provide filtering,
- grouping and
- labelling.
- These functions are often described as “slice and dice”. Slicing refers to filtering data. Dicing refers to grouping data.
- A common data warehouse example involves sales as the measure, with customer and product as dimensions. In each sale a customer buys a product. The data can be sliced by removing all customers except for a group under study, and then diced by grouping by product.
Typically dimensions in a data warehouse are organized internally into one or more hierarchies. “Date” is a common dimension, with several possible hierarchies:
- “Days (are grouped into) Months (which are grouped into) Years”,
- “Days (are grouped into) Weeks (which are grouped into) Years”
- “Days (are grouped into) Months (which are grouped into) Quarters (which are grouped into) Years”
etc.
In data warehousing, a dimension is a collection of reference information about a measurable event.
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.
Fact vs Dimension
- Fact tables and dimension tables are related to each other.
- For example, in the retail scenario, purchases, returns and calls are facts. On the other hand, customers, employees, items and stores are dimensions and should be contained in dimension tables.
- The fact table for a customer transaction would likely contain a foreign key reference to the item dimension table, where the entry corresponds to a primary key in that table for a record describing the item purchased.
Types of Dimension
- Confirmed Dimension
- Junk Dimension
- Degenerate Dimension
- Role Playing Dimension
Conformed Dimension
Is a set of data attributes that have been physically referenced in multiple database tables using the same key value to refer to the same structure, attributes, domain values, definitions and concepts.
A conformed dimension cuts across many facts.It is a dimension that has exactly the same meaning and content when being referred from different fact tables.
It can refer to multiple tables in multiple data marts within the same organization.
For two dimension tables to be considered as conformed, they must either be identical or one must be a subset of another.
There cannot be any other type of difference between the two tables.
For example, two dimension tables that are exactly the same except for the primary key are not considered conformed dimensions.
Junk Dimension
- A junk dimension is a convenient grouping of typically low-cardinality flags and indicators.
- Junk Dimensions are the dimensions which do not have any business meaning.
- By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.
- A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables.
- These kinds of attributes are typically remaining when all the obvious dimensions in the business process have been identified and thus the designer is faced with the challenge of where to put these attributes that do not belong in the other dimensions.
- The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators.
Degenerate Dimension
A degenerate dimension is a key, such as a transaction number, invoice number, ticket number, or bill-of-lading number, that has no attributes and hence does not join to an actual dimension table.
Degenerate dimensions are very common when the grain of a fact table represents a single transaction item or line item because the degenerate dimension represents the unique identifier of the parent.Degenerate dimensions often play an integral role in the fact table’s primary key.
Role-Playing Dimension
Dimensions are often recycled for multiple applications within the same database.
For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a “role-playing dimension”.
