Difference between Fact Table and Dimension Table (original) (raw)
Last Updated : 18 Jul, 2025
In data warehousing, **fact tables and **dimension tables are key components of **star or **snowflake schemas. Fact tables store **numeric data like sales or order amounts and include **foreign keys linking to dimension tables. Dimension tables provide **context with descriptive details like product names or customer demographics. The main difference between Fact or Reality table and the Dimension table is that dimension table contains attributes on that measures are taken actually table.
Difference Between Fact Table and Dimension Table
| Fact Table | Dimension Table |
|---|---|
| Fact table contains the measuring of the attributes of a dimension table. | Dimension table contains the attributes on that truth table calculates the metric. |
| In fact table, There is less attributes than dimension table. | While in dimension table, There is more attributes than fact table. |
| In fact table, There is more records than dimension table. | While in dimension table, There is less records than fact table. |
| Fact table forms a vertical table. | While dimension table forms a horizontal table. |
| The attribute format of fact table is in numerical format and text format. | While the attribute format of dimension table is in text format. |
| It comes after dimension table. | While it comes before fact table. |
| The number of fact table is less than dimension table in a schema. | While the number of dimension is more than fact table in a schema. |
| It is used for analysis purpose and decision making. | While the main task of dimension table is to store the information about a business and its process. |
Also let us see what Aggregate Fact Tables are,
Aggregate Fact Tables
- Aggregate fact tables are a special kind of fact tables in a data warehouse which contains new metrics which are been derived from one or more aggregate functions (COUNT, AVERAGE, MIN, MAX, etc.) or from some specialized functions whose outputs are totally derived from a grouping of base data.
- Aggregates are basically summarization of the fact related data which are been used as a purpose to improve the performance.
- These new metrics, called as "aggregate facts" or "summary statistics" are been stored and maintained in database of the data warehouse in special fact table at the grain of the aggregation.
- In similar way, the corresponding dimensions are been rolled up and compressed to match the new grain of the fact.
- These specialized tables are been used as an substitutions whenever possible for returning user queries. The reason is the speed.
- Querying a neat aggregate table is much faster and uses less of the disk I/O than the base, atomic fact table, especially when the dimensions are large as well.
- If you want to amaze your users then start adding the aggregates.
- Even you can use this technique in your **operational systems as well, giving boost to the foundational reports.
**Example:
EXAMPLE
Limitations of Aggregate Fact Tables
- Does not support exploratory analysis.
- Must be reaggregated each and every time when there is been certain change in source data so that the changes can be reflected in the data warehouse.
- The narrow capability leads to low and limited interactive use