What is a Fact Table?
A fact table stores quantitative data that can be aggregated, such as sales amount, units sold, or profit. It typically contains:
- Foreign key columns that reference dimension tables
- Numeric measure columns used for summarization
What is a Dimension Table?
A dimension table provides descriptive attributes that give context to the facts. It is usually static and contains:
- A primary key column (unique identifier)
- Descriptive columns used for filtering, grouping, and labeling (e.g., product name, region, date)
What is Normalized vs. Denormalized Data?
Normalized data reduces redundancy by splitting information into related tables. Denormalized data duplicates information to improve read performance, especially in analytical workloads.
- Normalized: Fewer duplicate values, more tables, complex joins.
- Denormalized: Redundant rows, fewer tables, faster query performance for reporting.
How to Design a Star Schema
A star schema places a single fact table at the center surrounded by multiple dimension tables. This layout is the preferred model for Power BI because it:
- Optimizes query performance
- Simplifies DAX calculations
- Reduces ambiguity in relationships
Steps to create a star schema:
- Identify transactional data that will become the fact table.
- Determine descriptive attributes and create corresponding dimension tables.
- Assign a surrogate key to each dimension and use it as a foreign key in the fact table.
- Import tables into Power BI, clean data in Power Query, and load them into the model.
How to Implement Relationships in Power BI
Relationships connect tables based on common columns, enabling accurate filtering and aggregation across the model.
- Cardinality: Define the relationship type – One‑to‑Many (most common), One‑to‑One, or Many‑to‑Many.
- Cross‑filter direction: Choose Single (default) or Both (bidirectional) based on the required filter flow.
- Active vs. Inactive: Only one active relationship can exist between two tables for direct filtering; inactive relationships can be used in DAX with USERELATIONSHIP.
Manual creation steps:
- Open the Manage Relationships dialog.
- Select the first table and the column that will serve as the key.
- Select the related table and its matching column.
- Set cardinality, cross‑filter direction, and activation status, then click OK.
Why Star Schema and Proper Relationships Matter
Using a star schema and correctly defined relationships yields several benefits:
- Performance: Power BI’s VertiPaq engine compresses and indexes star schemas efficiently.
- Maintainability: Clear separation of facts and dimensions simplifies model updates and documentation.
- Accurate analytics: Proper cardinality and filter direction prevent double‑counting and ensure slicers work as expected.
- Scalability: Adding new dimensions or measures requires minimal changes to the existing model.