WHAT IS A FACT Table?

A Fact is the central table in dimensional data modelling that stores the measurable outcomes of a business process. These measures are typically numeric and are designed to be aggregated, analysed, and reported across multiple perspectives provided by dimension tables.

The primary goal of a fact table is to capture business events in a structured format that supports performance analysis and decision-making. Facts answer the question: “What happened?” They represent transactions, events, or measurable states that the organization wants to analyse.

A fact table typically contains:

• Numeric measures (e.g., LoanAmount, ApprovalCount, RepaymentAmount)
• Foreign keys to dimension tables (e.g., Customer_ID, Officer_ID, Branch_ID, Time_ID)
• Minimal descriptive attributes of its own

For example, in a loan approval environment, a Fact_LoanApproval table may contain:

• Loan_ID (Primary Key)
• Customer_ID (Foreign Key)
• Officer_ID (Foreign Key)
• Branch_ID (Foreign Key)
• Time_ID (Foreign Key)
• LoanAmount
• InterestRate
• ApprovalFlag

Each row in the fact table represents one clearly defined business event. This level of detail is called the grain of the fact table. Defining the grain precisely is critical. For instance, the grain may be:

• One row per loan approval event
• One row per repayment transaction
• One row per daily loan balance snapshot

Without a clearly defined grain, reports may produce incorrect totals or duplicate counts.

Facts can be categorized based on how they aggregate:

• Additive facts can be summed across all dimensions (e.g., LoanAmount).
• Semi-additive facts can be summed across some dimensions but not others (e.g., Loan Balance over customers but not across time).
• Non-additive facts cannot be summed (e.g., Interest Rate), though averages or ratios may be calculated.

In a practical scenario, a bank’s Fact_LoanApproval table may store one row per loan approval. Analysts can then ask:

• “Total Loan Amount approved by quarter”
• “Approval count by branch and product type”
• “Average interest rate by customer segment”

These queries rely on aggregating fact measures while slicing by dimension attributes.

The strengths of fact tables lie in their efficiency for aggregation and reporting. They form the quantitative backbone of BI systems and are optimized for OLAP workloads. However, fact tables cannot stand alone. Without dimensions providing context, numeric measures have little meaning. Additionally, if the grain is poorly defined or if too many non-additive metrics are mixed together, confusion can arise.

From a modelling perspective, fact tables belong to the Dimensional Modelling family, including Star, Snowflake, and Unified Star Schema designs. They are intentionally denormalized for analytical performance. While they contain many foreign keys to dimensions, they usually have relatively few intrinsic attributes beyond numeric measures.

In summary, a fact table records measurable business events. It captures what happened, while dimensions explain under what circumstances it happened. Together, they enable structured, high-performance analytics.