• Articles
  • Tutorials
  • Interview Questions

Fact Table Vs. Dimension Table: Differences Between Them

Tutorial Playlist

In this blog article, we will look at the major distinctions between Fact Tables and Dimension Tables, as well as their functions in data warehousing.

Unlock valuable insights for your business with business intelligence. Watch this video now

What are Fact Tables and Dimension Tables?

A Fact Table is a key table in data warehousing that maintains quantitative data about a business process or event, such as sales transactions, inventory levels, or customer orders. It covers the major indicators and measures that are used to analyze and assess a company’s performance. Fact Tables feature numerous rows and fewer columns because each row represents a unique instance of an event or transaction, and the columns indicate the many measurements connected with that occurrence.

A Dimension Table, on the other hand, is a supporting table that gives descriptive information about the data in the Fact Table. It comprises traits or features that aid in categorizing or filtering the data in the Fact Table. A Dimension Table for sales transactions, for example, can have attributes such as date, product, store, and customer, which offer context and meaning to the sales data in the Fact Table. 

Dimension Tables often feature fewer rows and more columns than Fact Tables because each row represents a distinct dimension value or category, and the columns indicate the many properties associated with that value.

Relationship between Fact Tables and Dimension Tables:

In data warehousing, the relationship between Fact Tables and Dimension Tables is a fundamental aspect of both the star schema and snowflake schema. These schemas are widely used to organize data for efficient querying and reporting in business intelligence applications.

In the star schema, the Fact Table contains a composite key that incorporates keys from the relevant Dimension Tables. These Dimension Tables, in turn, have foreign keys that link back to the corresponding primary key in the Fact Table. This star schema structure simplifies querying and data analysis, as users can easily filter and aggregate data based on the attributes present in the Dimension Tables.

On the other hand, the snowflake schema extends the star schema by normalizing the Dimension Tables. This means that Dimension Tables in the snowflake schema are broken down into multiple related tables to eliminate data redundancy. Despite this normalization, the relationship between the Fact Table and the Dimension Tables is still maintained through the use of keys.

Differences between Fact Tables and Dimension Tables

Here’s a table enumerating the fundamental distinctions between Fact Tables and Dimension Tables:

BasisFact TableDimension Table
ContentsNumeric values and transactional data.Categorical data and descriptive attributes.
PurposeStores quantitative measures and metrics.Provides descriptive attributes and context.
SizeLarger in terms of data volume.Smaller in terms of data volume.
AggregationAggregates data for analysis and reporting.Provides context for data aggregation.
QueryingProvides data for analysis and calculations.Used for filtering and categorization
ExamplesSales transactions and inventory levels.Date, product, store, and customer dimensions
RowsMany rows.Fewer rows.

Examples of Fact Tables and Dimension Tables 

Here are a few examples of Fact Tables and Dimension Tables:

An Example of a Fact Table 

Consider an Indian e-commerce firm that sells things online and wishes to analyze sales data to get business insights. A Fact Table for this firm may be the “Order” table, which would contain quantifiable information about each order transaction, such as the following:

  • Order ID
  • Date of order
  • Product ID
  • Customer ID
  • Quantity ordered
  • Price per unit
  • Total order amount

Each row in this Fact Table will represent an order transaction, while the columns will provide the measurements and metrics related to that particular transaction. The following is how a single row in the Fact Table would look:

Fact Table Example

This row represents an order that occurred on April 12th, 2000, where the customer purchased three units of Product ID 1555 at a price of INR 1000 per unit, which resulted in a total order amount of INR 3000.

An Example of a Dimension Table 

Continuing with the e-commerce scenario, the corporation may wish to provide extra context and classification for its sales data. Dimension Tables come in handy here. One Dimension Table for this corporation may be the “Product” table, which would contain descriptive information about each product sold, such as the following:

  • Product ID
  • Product Name
  • Category
  • Sub-category
  • Brand
  • Price

Each row in this Dimension Table will represent a unique product, while the columns will provide the attributes related to that particular product. This is how a single row in the Dimension Table would look:

Dimension Table Example

This row represents a product with an ID of 1555, named “chair”, belonging to the “Furniture” category, specifically the “Household” sub-category, is manufactured by the brand “ABC,” and priced at INR 1000.

The company can organize and categorize its sales data by attaching the “Product” Dimension Table to the “Order” Fact Table using a common Product ID key. They may, for example, analyze sales by product category or brand or compute measures such as average product price or profit margin.

Enroll now in Tableau training to learn more about this Business Intelligence tool.

Types of Dimension Tables

Here are the types of Dimension Tables:

Types of Dimension Tables

Slowly Changing Dimension (SCD) Table

Slowly Changing Dimension (SCD) tables are used to track historical changes in dimensional properties across time. They record changes in qualities such as customer information, product features, or geographic information. Type 1, Type 2, and Type 3 SCDs are the most common, and they are mentioned in brief below:

  • Type 1: Replaces the old attribute value with the new value, with no history preservation.
  • Type 2: Generates a new row for each modification while maintaining past values via effective date ranges or version numbers.
  • Type 3: Inserts columns to keep track of restricted historical changes, often storing the current and past attribute values.

Role-Playing Dimension Table

When a single Dimension Table is connected with various roles or views, Role-Playing Dimension Tables are employed. By generating various aliases or views of the same Dimension Table, a “Date” Dimension Table, for example, may be used to analyze both order dates and shipment dates.

Hierarchy Dimension Table

Hierarchy Dimension Tables provide the hierarchical connection between dimension characteristics. A “Product” dimension, for example, might include a hierarchy with levels such as “Category,” “Sub-category,” and “Product.” This allows for data digging down or rolling up at various degrees of granularity.

Junk Dimension Table

A Junk Dimension Table is used to combine low-cardinality flags or indications into a single Dimension Table. It aids in lowering the number of columns in the Fact Table, simplifying the schema design, and boosting query efficiency.

Conformed Dimension Table

Conformed Dimension Tables are those s that are shared across numerous Fact Tables in a data warehouse. They maintain uniformity and facilitate the integration of data from many sources or topic areas. For example, a “Customer” dimension may be utilized in both sales and marketing data tables.

Types of Fact Tables

The following are the types of Fact Tables:

Transactional Fact Table

Transactional Fact Tables store information about specific events or transactions. They’re utilized to save granular, atomic-level data like sales transactions, purchase orders, and site clicks. Transactional Fact Tables often have a high number of rows and use foreign keys to connect to other Dimension Tables.

Periodic Snapshot Fact Table

Periodic Snapshot Fact Tables collect aggregated data at regular intervals (e.g., daily, weekly, and monthly). They save summarized information regarding a specific time period, such as total monthly sales or weekly website visitors. When compared to Transactional Fact Tables, Periodic Snapshot Fact Tables feature fewer rows, but they provide a greater degree of aggregation for analysis and reporting.

Accumulating Snapshot Fact Table

Accumulating Snapshot Fact Tables capture the state or progress of a process or workflow. They document significant milestones or events that occur during the lifespan of a business process, such as order processing or project management. Accumulating Snapshot Fact Tables allow for the recording and analysis of Key Performance Indicators (KPIs) at various phases of a process.

Factless Fact Table

Factless Fact Tables only include foreign keys that correlate with Dimension Tables with no measurements or metrics. They are helpful when it is necessary to track non-occurring events or combinations of occurrences, such as tracking consumers who did not make a purchase or tracking product pairings that were not sold together. Factless Fact Tables aid in the analysis of data patterns, trends, and exceptions.

Conclusion

In conclusion, Dimension Tables and Fact Tables are critical components of data warehousing. Dimension Tables give descriptive features, whereas Fact Tables provide quantitative data. Slowly changing, role-playing, hierarchy, junk, and conforming dimensions are examples of Dimension Tables. 

Fact Tables can be transactional, periodic snapshots, accumulating snapshots, or factless. Understanding these tables is critical for successful data analysis and decision-making. They assist organizations in getting useful insights and understanding their business operations by working together.

Course Schedule

Name Date Details
Business Intelligence Course 25 May 2024(Sat-Sun) Weekend Batch
View Details
Business Intelligence Course 01 Jun 2024(Sat-Sun) Weekend Batch
View Details
Business Intelligence Course 08 Jun 2024(Sat-Sun) Weekend Batch
View Details