Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

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.

Date DateKey Year, Month, Day Product ProductKey Name, Category Brand Sales DateKey, ProductKey Quantity, SalesAmount Central Fact Table Customer CustomerKey Name, City Segment Store StoreKey, Region
Diagram 1: A central Sales fact table connected directly to four dimension tables.
Key Idea: Fact tables describe business events or measurements. Dimension tables describe the people, products, dates, and locations associated with those events.

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.
Important: Define the grain of a fact table before modeling it. The grain explains exactly what one row represents. For example, one row per order line is different from one row per complete order or one row per month.

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.

Product Dimension ProductKey ProductName Category Brand UnitPrice Customer Dimension CustomerKey CustomerName City State CustomerSegment Date Dimension DateKey FullDate Year MonthName Quarter Descriptive attributes support filtering, grouping, and reporting.
Diagram 2: Dimension tables contain descriptive columns for business analysis.
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.

Product ProductKey (Unique) Customer CustomerKey (Unique) Date DateKey (Unique) Sales Fact Table ProductKey, CustomerKey, DateKey, SalesAmount 1 : * 1 : * 1 : *
Diagram 3: Dimension keys connect to repeating foreign keys in the fact table.

Relationship Design Rules

  1. Identify the unique key in each dimension table.
  2. Find the corresponding foreign key in the fact table.
  3. Confirm that both columns use compatible data types.
  4. Create a one-to-many relationship where the data supports it.
  5. Use single-direction filtering from dimensions to the fact table as a common starting point.
  6. Test the model using slicers, table visuals, and totals.
Remember: A relationship does not correct bad data automatically. Duplicate dimension keys and unmatched foreign keys should be investigated before relying on report results.

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.

Star Schema Sales Fact Measures Product Customer Date Store Snowflake Schema Sales Fact Measures Product Customer Category City Date Store
Diagram 4: A star schema has direct dimension-to-fact links; a snowflake schema may normalize dimension attributes into additional tables.
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.

  1. Prepare the Sales, Product, Customer, and Date tables in your data source.
  2. Load the tables into Power BI Desktop using Get Data.
  3. Open Model view and inspect the available tables.
  4. Check that ProductKey, CustomerKey, and DateKey are unique in their respective dimension tables.
  5. Confirm that the corresponding keys exist in the Sales table.
  6. Create relationships between each dimension and the Sales fact table.
  7. Use one-to-many cardinality where the keys support it.
  8. Set appropriate cross-filter direction, commonly single direction from dimensions to Sales.
  9. Create measures for sales and quantity.
  10. Build visuals and verify the results against the source data.
Prepare Source Tables Keys and Grain Load Into Power BI Validate Types Relate Dimensions To Fact Table Test and Report Measures, Slicers Validate Totals Build the model in stages and verify each stage before creating reports.
Diagram 5: A practical workflow for preparing and validating a star schema.

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

  1. Create the Product and Sales tables in Excel or another data source.
  2. Import both tables into Power BI Desktop.
  3. Create a one-to-many relationship using ProductID.
  4. Create the Total Sales measure.
  5. Build a bar chart showing sales by category.
  6. Add a category slicer and test the filtering behavior.
  7. Compare the visual totals with the original Sales table.
Expected Total: The total sales amount is 79,000. Electronics should total 75,000, and Accessories should total 4,000.

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.