In this informative article, we learn about the Fact table and Dimensional table.
DIMENSION MODEL
- A dimension model is a database structure technique. The dimensional model contains two entities: Facts and Dimensions.
- The Dimensional model is to optimize the database for the fast retrieval of data.
- The center of the star can have one fact table and several associated dimension tables.
FACT TABLE
- The fact table is central in a star or snowflake schema.
- The primary key in the fact table is mapped as foreign keys to dimensions.
- It contains fewer attributes and more records.
- The fact table comes after the dimension table.
- It has a numeric and text data format.
- It is utilized for analysis and reporting.
TYPES OF FACT:-
- A Snapshot Fact Table stores some kind of measurements and is captured against a specific time.
- Cumulative Fact Table describes what has happened over a while.
- The Transaction Fact Table represents an event that occurred at an instantaneous point in time.
DIMENSIONAL TABLE
- The dimensional table is located at the edge of a star or snowflake schema.
- Dimension tables are used to describe dimensions; they contain dimension keys, values, and attributes.
- When we create a dimension, we logically define a structure for our projects.
- The foreign key is mapped to the facts table.
- The dimensional table is in text data format.
TYPES OF DIMENSIONS:-
- Junk Dimensions used to implement the rapidly changing dimension where we can store the attribute that changes rapidly.
- A Conformed Dimension is shared across multiple data marts.
- A Degenerated Dimension derived from the fact table and does not have its dimension table.
- Role-playing Dimensions are often used for multiple purposes within the same database.
Based on the frequency of data change below represent the types of Dimension tables:-
- Static Dimension values are static and will not change.
- Slowly Changing Dimensions attribute values changes slowly based on the frequency of data change and historic preservation.
- Rapidly Changing Dimensions attribute values change rapidly.
If you are new to database learning SQL Server recommended is the following must-watch the video: -
