Power Bi — Fact Table vs Dimension Table
“A fact table holds numerical data (facts) for analysis, while a dimension table provides descriptive context for the facts.”
In Power Bi, and data modelling in general, think of a fact table like a storage place for items that we want to analyze, and a dimension table as a helper that gives more information about those items. They team up to help us understand and make sense of data better.
Fact Table
A fact table in Power BI is a central table that holds quantitative, numerical, or measurable data, often referred to as “facts.” These facts are typically metrics, measurements, or performance indicators that provide the basis for analysis. Fact tables store data at a detailed level, capturing specific events or transactions over time. These events can include sales, orders, transactions, or any other business activities that generate measurable data points. Fact tables are often associated with date and time dimensions to enable time-based analysis.
Key Characteristics of a Fact Table:
- Contains quantitative data (facts).
- Captures specific business events or transactions.
- Includes foreign keys that connect to dimension tables.
- Typically has large volumes of data.
- Used as the basis for aggregation and analysis.
Dimension Table
A dimension table in Power BI complements the fact table by providing context and descriptive attributes for the facts stored in the fact table. Dimension tables contain categorical or textual data that help categorize, filter, and provide context to the numerical data in the fact table. These attributes provide the “who,” “what,” “where,” “when,” and “how” aspects of the data. Dimension tables are used for slicing, dicing, and filtering data, making it easier to answer specific business questions and understand the significance of the facts.
Key Characteristics of a Dimension Table:
- Contains descriptive attributes.
- Provides context to the facts in the fact table.
- Used for filtering and categorizing data.
- Typically has smaller volumes of data compared to fact tables.
- Can have hierarchical structures (e.g., product categories, geographic regions).
Example
Fact Table: “Sales” table containing order quantities and revenue. Dimension Table: “Product” table containing product names and categories.
In summary, the fact table and dimension table work hand in hand to enable powerful data analysis in Power BI. The fact table holds the quantitative data that is the focus of analysis, while the dimension table provides the necessary context and categorization for that data. By properly defining relationships between these tables in Power BI’s data model, you create a foundation for creating insightful visualizations, reports, and dashboards that help stakeholders make informed decisions based on their data-driven insights.
