Skip to content
Infoveave Learning

Fact and Dimension Tables

Fact and dimension tables are the backbone of a data warehouse, working together to store and organize data for meaningful analysis. Each serves a specific purpose in the data warehouse design and plays a unique role in enabling businesses to extract insights.

Fact Tables

Fact tables store measurable, numerical data that represents business processes. They contain key performance metrics, such as sales revenue, profit, and quantities sold, that analysts use to evaluate business performance.

Key Characteristics

Fact tables are designed to hold numerical metrics and connect them with relevant descriptive details. The fact table contains the core data you want to analyze. It typically includes:

  • Measurable Data: Numbers or quantities like loan amounts, interest rates, or loan tenures that you can sum, average, or analyze.
  • Key columns to Dimension tables: Links to dimension tables to provide context to the data.

Their key characteristics are:

  • Numerical Data: Store figures like revenue, quantities, or profit.
  • Foreign Keys: Link to dimension tables using identifiers such as product or customer IDs.
  • Aggregation: Allow for calculations like totals, averages, or counts for analysis.
  • Centralized: Serve as the main repository for quantitative data.

In the below diagram:

  • The Fact Loan table is the fact table. It includes:
    • Measurable Data: Loan Amount, Loan Tenure, Interest Rate.
    • References to Dimensions: Customer ID, Product ID, Branch ID, Channel ID. These link the facts to additional descriptive information in other tables.

Dimension Tables

Dimension tables provide descriptive information to give context to the metrics in fact tables. These tables store attributes like product categories, customer details, or date attributes, which make the data easier to interpret.

Key Characteristics

Dimension tables describe the who, what, when, and where of the data, making it easy to filter, group, and analyze. Their key characteristics are:

  • Descriptive Data: Include details such as customer names, product categories, branch regions or channels.
  • Filtering and Grouping: Allow data to be segmented and analyzed by specific attributes.
  • Primary Keys: Use unique identifiers that link to foreign keys in fact tables.
  • Focus on Details: Capture qualitative information rather than numerical metrics.

In the below diagram

  • Dim Customer (Customer Dimension)

    • Describes the who: Information about the customer.
    • Example fields: Gender, Marital Status.
    • Each loan is linked to a specific customer through Customer ID.

  • Dim Product

    • Describes what: Information about the loan product.
    • Example fields: Product Name, Loan Name.
    • Each loan is associated with a product using Product ID.

  • Dim Branch

    • Describes the where: Information about the branch that issued the loan.
    • Example fields: Branch Name, State.
    • Each loan is linked to a branch using Branch ID.

  • Dim Channel

    • Describes the how: Information about the channel through which the loan was processed.
    • Example fields: Channel Name.
    • Each loan is connected to a channel using Channel ID.
Fact and Dimension Table

Fact and dimension tables form the foundation of a data warehouse by organizing data into measurable metrics and descriptive details.

When data in dimension tables changes over time, like a customer’s marital status or a product’s name, we need a way to handle and track these changes. This is where Slowly Changing Dimensions (SCD) come in, helping us manage updates while maintaining historical accuracy.