Star Schema and Dimension Tables in Power BI
A star schema is a popular data modeling design used in Power BI. It organizes data into a central fact table connected to surrounding dimension tables. This structure helps users analyze business performance using categories such as products, customers, dates, locations, and sales regions.
In this lesson, you will learn how to identify fact and dimension tables, understand star schema relationships, and build a simple sales model in Power BI Desktop.
Learning Objectives
- Understand the purpose of a star schema.
- Identify fact tables and dimension tables.
- Understand table grain and business keys.
- Create one-to-many relationships between dimensions and facts.
- Recognize the difference between star and snowflake schemas.
- Apply modeling best practices to a sales reporting example.
1. What Is a Star Schema?
A star schema is a data model in which a central fact table connects directly to multiple dimension tables. The diagram resembles a star because the fact table sits in the center and dimension tables surround it.
For example, a retail business might store transaction records in a Sales fact table. Product, Customer, Date, and Store tables provide descriptive information that helps users analyze those transactions.
2. Understanding Fact Tables
A fact table stores measurable business events. In a sales model, each row might represent a sales transaction line, an order line, or an aggregated sales record for a defined period.
Example: Sales Fact Table
| SalesID | DateKey | ProductKey | CustomerKey | Quantity | SalesAmount |
|---|---|---|---|---|---|
| 1001 | 20260101 | P01 | C01 | 2 | 50,000 |
| 1002 | 20260102 | P02 | C02 | 3 | 4,500 |
| 1003 | 20260102 | P01 | C03 | 1 | 25,000 |
The Sales table contains keys that connect transactions to dimensions, along with numerical values that can be analyzed in reports.
Common Fact Table Columns
- Foreign keys: DateKey, ProductKey, CustomerKey, and StoreKey.
- Measures: Quantity, SalesAmount, CostAmount, and DiscountAmount.
- Transaction identifiers: SalesID or OrderLineID, depending on the data source.
3. Understanding Dimension Tables
Dimension tables store descriptive attributes used to group, filter, and label business measurements. They help report users answer questions such as which product sold the most, which city generated the most revenue, or how sales changed over time.
| Dimension | Example Attributes | Business Questions |
|---|---|---|
| Product | ProductName, Category, Brand | Which category generated the highest sales? |
| Customer | CustomerName, City, Segment | Which city has the highest revenue? |
| Date | Date, Year, Month, Quarter | How did sales change over time? |
| Store | StoreName, Region, City | Which region performs best? |
4. Fact Tables vs Dimension Tables
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Main Purpose | Stores business events and measurements. | Stores descriptive business information. |
| Typical Columns | Keys, quantities, amounts, transaction identifiers. | Keys, names, categories, dates, regions. |
| Typical Row Count | Often many transaction or event rows. | Often fewer rows than the related fact table. |
| Repeated Keys | Foreign keys usually repeat across transactions. | The relationship key on the one side should be unique. |
| Reporting Role | Provides the values being measured. | Provides the categories used to filter and group measures. |
5. How Relationships Work in a Star Schema
Each dimension usually connects directly to the fact table through a matching key. The dimension key is unique, while the corresponding foreign key can repeat in the fact table.
Relationship Design Rules
- Identify the unique key in each dimension table.
- Find the corresponding foreign key in the fact table.
- Confirm that both columns use compatible data types.
- Create a one-to-many relationship where the data supports it.
- Use single-direction filtering from dimensions to the fact table as a common starting point.
- Test the model using slicers, table visuals, and totals.
6. Star Schema vs Snowflake Schema
In a star schema, dimensions generally connect directly to the fact table. In a snowflake schema, some dimensions are split into related tables, creating additional relationship levels.
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Structure | Dimensions usually connect directly to the fact table. | Some dimensions are divided into related sub-dimension tables. |
| Complexity | Generally simpler to understand and navigate. | Can require more relationships to understand. |
| Dimension Data | Descriptive attributes are often grouped in one dimension. | Some attributes are stored in separate normalized tables. |
| Common Use | A strong starting point for many Power BI reporting models. | Useful when normalized structures or specific data-management requirements justify them. |
7. Build a Star Schema in Power BI Desktop
Follow these steps to create a basic sales model using separate dimension tables and a central fact table.
- Prepare the Sales, Product, Customer, and Date tables in your data source.
- Load the tables into Power BI Desktop using Get Data.
- Open Model view and inspect the available tables.
- Check that ProductKey, CustomerKey, and DateKey are unique in their respective dimension tables.
- Confirm that the corresponding keys exist in the Sales table.
- Create relationships between each dimension and the Sales fact table.
- Use one-to-many cardinality where the keys support it.
- Set appropriate cross-filter direction, commonly single direction from dimensions to Sales.
- Create measures for sales and quantity.
- Build visuals and verify the results against the source data.
8. Create a Basic Sales Measure
After creating the relationships, add a measure to calculate the total sales amount. Select the Sales table and choose New measure.
Total Sales = SUM(Sales[SalesAmount])
This measure can be used in a card, chart, or table visual. Add Product[Category] to a chart axis and the Total Sales measure to the values area to compare sales across product categories.
Suggested Report Visuals
- Card: Display Total Sales.
- Bar chart: Compare Total Sales by Product Category.
- Line chart: Analyze Total Sales by Month.
- Table: Display Product Name, Quantity, and Total Sales.
- Slicers: Filter the report by Year, Region, or Category.
9. Common Modeling Mistakes
| Mistake | Why It Matters | Recommended Action |
|---|---|---|
| Duplicate keys in a dimension | The table may not work as the one side of a one-to-many relationship. | Investigate duplicates and identify the correct business key. |
| Unclear fact table grain | Calculations may combine rows that represent different levels of detail. | Document what one row represents before building measures. |
| Using descriptive text as a relationship key unnecessarily | Names may change or be inconsistent between data sources. | Prefer stable, matching keys when available. |
| Too many bidirectional relationships | Filters can follow multiple paths and produce confusing behavior. | Start with single-direction filtering and enable Both only when justified. |
| Unmatched foreign keys | Some fact records may not map to the expected dimension member. | Check missing keys and reconcile the source data. |
10. Practical Exercise
Create a simple sales star schema using the following tables.
| Product | Sales |
|---|---|
| P01 — Laptop — Electronics | SaleID 1 — P01 — 50,000 |
| P02 — Mouse — Accessories | SaleID 2 — P02 — 1,500 |
| P03 — Keyboard — Accessories | SaleID 3 — P03 — 2,500 |
| SaleID 4 — P01 — 25,000 |
Exercise Tasks
- Create the Product and Sales tables in Excel or another data source.
- Import both tables into Power BI Desktop.
- Create a one-to-many relationship using ProductID.
- Create the Total Sales measure.
- Build a bar chart showing sales by category.
- Add a category slicer and test the filtering behavior.
- Compare the visual totals with the original Sales table.
11. Knowledge Check
Question 1
What is the central table in a typical star schema?
Answer: The fact table.
Question 2
Which table usually stores product names and categories?
Answer: The Product dimension table.
Question 3
What does the grain of a fact table describe?
Answer: What one row in the fact table represents.
Question 4
What is a common relationship pattern between a dimension and a fact table?
Answer: One-to-many, with the dimension on the one side.
Question 5
What is one difference between a star schema and a snowflake schema?
Answer: A star schema generally connects dimensions directly to the fact table, while a snowflake schema may split dimensions into additional related tables.
Summary
In this lesson, you learned how to organize a Power BI data model using a star schema, identify fact and dimension tables, and understand the role of table grain and relationship keys.
You also explored how dimension tables connect to a central fact table, compared star and snowflake schemas, and practiced creating relationships and a basic sales measure. Always validate key uniqueness, relationship behavior, and report totals before relying on the model for analysis.