All Courses
AI Career Planner
1:1 Coaching/Mentoring
Job Board
SQL
HTML/CSS/JS
Coding
Settings
LogoutFacts are numeric measures of data. They are stored in fact tables. Fact tables store that type of data that will be analyzed by dimension tables. Fact tables have foreign keys associating with dimension tables.
Dimensions are descriptive attributes of data. Those will be stored in the dimensions table. For example, customer’s information like name, number, and email will be stored in the dimension table.
Fact table contains measurements, metrics, and facts about a business process while the Dimension table is a companion to the fact table which contains descriptive attributes to be used as query constraining. Fact table helps to store report labels whereas Dimension table contains detailed data.
Difference between Dimension table vs. Fact table
| 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 table is mapped as foreign keys to Dimensions. | Dimension table has a primary key columns that uniquely identifies each dimension. |
| 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. |