Below is the difference between them
|
Parameters |
Fact Table |
Dimension Table |
|
Definition |
Measurements, metrics
or facts about a business process. |
Companion table to the
fact table contains descriptive attributes to be used as query constraining. |
|
Characteristic |
Located at the center
of a star or snowflake schema and surrounded by dimensions. |
Connected to the fact
table and located at the edges of the star or snowflake schema |
|
Design |
Defined by their grain
or its most atomic level. |
Should be wordy,
descriptive, complete, and quality assured. |
|
Task |
Fact table is a
measurable event for which dimension table data is collected and is used for
analysis and reporting. |
Collection of
reference information about a business. |
|
Type of Data |
Facts tables could
contain information like sales against a set of dimensions like Product and
Date. |
Evert dimension table
contains attributes which describe the details of the dimension. E.g.,
Product dimensions can contain Product ID, Product Category, etc. |
|
Key |
Primary Key in fact is
mapped as foreign keys to Dimensions. |
Foreign key to the
facts table |
|
Storage |
Helps to store report
labels and filter domain values in dimension tables. |
Load detailed atomic
data into dimensional structures. |
|
Hierarchy |
Does not contain
Hierarchy |
Contains Hierarchies.
For example Location could contain, country, pin code, state, city, etc. |
