In data warehousing, a
dimension table is one of the set of companion tables to a fact table.
The fact table
contains business facts (or measures), and foreign keys which refer to
candidate keys (normally primary keys) in the dimension tables.
Contrary to fact
tables, dimension tables contain descriptive attributes (or fields) that are
typically textual fields (or discrete numbers that behave like text). These
attributes are designed to serve two critical purposes: query constraining
and/or filtering, and query result set labeling.
Dimension attributes
should be:
Verbose (labels consisting of full words)
Descriptive
Complete (having no missing values)
Discretely valued (having only one value
per dimension table row)
Quality assured (having no misspellings or
impossible values)
Dimension table rows
are uniquely identified by a single key field. It is recommended that the key
field be a simple integer because a key value is meaningless, used only for
joining fields between the fact and dimension tables. Dimension tables often
use primary keys that are also surrogate keys. Surrogate keys are often
auto-generated.
The use of surrogate
dimension keys brings several advantages, including:
Performance. Join processing is made much
more efficient by using a single field (the surrogate key)
Buffering from operational key management
practices. This prevents situations where removed data rows might reappear when
their natural keys get reused or reassigned after a long period of dormancy
Mapping to integrate disparate sources
Handling unknown or not-applicable
connections
Tracking changes in dimension attribute
values
Although surrogate key
use places a burden put on the ETL system, pipeline processing can be improved,
and ETL tools have built-in improved surrogate key processing.
The goal of a
dimension table is to create standardized, conformed dimensions that can be
shared across the enterprise's data warehouse environment, and enable joining
to multiple fact tables representing various business processes.
No comments:
Post a Comment