Facts and dimensions are data warehousing terms. A fact is a quantitative piece of information - such as a sale or a download. Facts are stored in fact tables, and have a foreign key relationship with a number of dimension tables. Dimensions are companions to facts, and describe the objects in a fact table.
The key differences between Fact and Dimension Tables are as follows: The Dimension table is a partner to the fact table and contains descriptive qualities that can be used as query constraints. The fact table includes measurements, metrics, or facts about business operations.
While facts correspond to events, dimensions correspond to people, items, or other objects. In the retail scenario used in the example, we discussed that 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 two types of tables used in
dimensional modeling are facts and dimensions. A dimension table contains descriptive attribute fields whereas a fact table contains only measures and key relationships. This is why requirements gathering is very important when building a data warehouse in its early stages.
A fact table is defined by its grain or most atomic level, whereas a Dimension table should be wordy, descriptive, complete, and of assured quality. The fact table helps to store report labels, whereas Dimension table contains detailed data. The fact table does not contain a hierarchy, whereas the Dimension table contains hierarchies.
Dimensions, on the other hand, are collections of reference information about the facts in a data warehouse. Dimensions categorize and describe the facts recorded in a data warehouse to provide meaningful, categorized, and descriptive answers to business questions.Generally, a numeric data field which is constant in nature and is not involved in calculations and measurements is considered to be a
dimension while a data field which is involved in measurements and calculations is a
fact. It depends on the designer for deciding the
facts and
dimensions.
