In Power BI data modeling, organizing data into fact tables and dimension tables is a best practice. This structure helps in building efficient models, improving performance, and creating meaningful reports.
What is a fact table?
A fact table contains measurable, quantitative data used for analysis. These are typically numeric values that can be aggregated.
Key Characteristics:
- Contains metrics or measures
- Stores transactional data
- Includes foreign keys to connect with dimension tables
- Usually large in size
Examples of Fact Data:
- Sales Amount
- Quantity Sold
- Profit
- Revenue
Example: Sales Fact Table
| Product ID | Customer ID | Date | Sales Amount | Quantity |
|---|---|---|---|---|
| 101 | 1001 | 2025-01-01 | 500 | 2 |
This table records actual business events.
What is a dimension table?
A dimension table contains descriptive or categorical information that provides context to the data in the fact table.
Key Characteristics:
- Contains descriptive attributes
- Used for filtering and grouping
- Smaller in size compared to fact tables
- Contains unique values (primary keys)
Examples of Dimension Data:
- Product Name
- Customer Name
- Region
- Date
Example: Product Dimension Table
| Product ID | Product Name | Category |
|---|---|---|
| 101 | Laptop | Electronics |
This table describes the data stored in the fact table.
Fact vs Dimension Table
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Definition | Stores measurable, quantitative data | Stores descriptive, categorical data |
| Purpose | Used for calculations and analysis | Used for filtering and grouping |
| Data Type | Numeric (e.g., sales, revenue) | Text or categorical (e.g., name, category) |
| Content | Business events or transactions | Details about entities |
| Keys | Contains foreign keys | Contains primary keys |
| Size | Usually large (more rows) | Usually small (fewer rows) |
| Example | Sales, Orders, Transactions | Product, Customer, Date |
| Usage | Aggregation (SUM, COUNT, AVG) | Slicing and filtering data |
Why This Structure is Important
Using fact and dimension tables:
- Improves query performance
- Simplifies data modeling
- Supports better analysis
- Enables scalable report design
