Definitions: Fact table, Dimension table

PDF
In the context of data warehousing, fact & dimension tables hold very important part. It is essential to understand what they are and where they are being used. A data warehouse typically consists of large number of tables, each table is designed with specific style/method to represent the specific data. Most of these tables fall into category of fact or dimension tables, though there are few more table categories.

Fact table

A fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.

Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined.
  • Additive - Measures that can be added across all dimensions.
  • Non Additive - Measures that cannot be added across all dimensions.
  • Semi Additive - Measures that can be added across few dimensions and not with others.
A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called "Factless Fact tables".

Dimension table

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. The dimension tables contain attributes (or fields) used to constrain and group data when performing data warehousing queries.

Over time, the attributes of a given row in a dimension table may change. For example, the shipping address for a company may change. Kimball refers to this phenomenon as Slowly Changing Dimensions. Strategies for dealing with this kind of change are divided into three categories:
  • Type One - Simply overwrite the old value(s).
  • Type Two - Add a new row containing the new value(s), and distinguish between the rows using Tuple-versioning techniques.
  • Type Three - Add a new attribute to the existing row.
 
Home Define: Facts, Dimensions table

BI & DW - Interview questions | Insomnia articles | Job interview questions with answers