Data Modeling and Schema Design in Power BI - Textnotes

Data Modeling and Schema Design in Power BI

This module teaches learners how to structure data in Power BI using tables and relationships. Students will learn about cardinality, cross-filtering, and schema design, including star and snowflake schemas. Hands-on exercises focus on creating relationships, using lookup tables, and building efficient data models for reporting and analysis.

1. Understanding Tables and Relationships

Tables store data in rows and columns. In Power BI, relationships link tables together using keys.

Example:

  1. Orders Table: OrderID, CustomerID, ProductID, SalesAmount
  2. Customers Table: CustomerID, Name, Region
  3. Relationship: Orders.CustomerID → Customers.CustomerID

This allows combining sales data with customer details in visuals.

2. Cardinality: One-to-One (1:1), One-to-Many (1:M)

  1. 1:1 Relationship: Each row in Table A matches one row in Table B
  2. 1:M Relationship: Each row in Table A matches multiple rows in Table B (common in fact-dimension modeling)

Example:

  1. 1:M: Customer table (dimension) → Orders table (fact)

3. Cross-Filter Direction: Single vs Both

  1. Single: Filter flows in one direction
  2. Both: Filter flows in both directions (used carefully to avoid ambiguity)

Example:

  1. Single direction: Filter Customers → Orders
  2. Both direction: Filter can flow Customers ↔ Orders

4. Star Schema vs Snowflake Schema

Star Schema:

  1. Fact table at the center
  2. Dimension tables connected directly
  3. Easy for reporting and DAX calculations

Example:

  1. Fact Table: Sales
  2. Dimension Tables: Customers, Products, Dates

Snowflake Schema:

  1. Dimension tables normalized into multiple related tables
  2. More complex, used for large datasets

Example:

  1. Product Dimension → Category Dimension → Sub-Category

5. Lookup Tables and Fact-Dimension Tables

  1. Fact Table: Contains transactional data (Sales, Orders)
  2. Dimension Table: Provides context (Customers, Products, Dates)
  3. Lookup tables act as bridges to connect fact tables with multiple dimensions

Example:

  1. Fact Table: Orders (OrderID, CustomerID, ProductID, Amount)
  2. Dimension Tables: Customers, Products, Date
  3. Relationship: Orders.CustomerID → Customers.CustomerID

6. Hands-On Exercise

  1. Import a sample Sales dataset with Orders, Customers, and Products
  2. Identify Fact and Dimension tables
  3. Create relationships manually:
  4. Orders → Customers
  5. Orders → Products
  6. Set cardinality (1:M) and cross-filter direction (Single)
  7. Avoid many-to-many issues by using lookup tables
  8. Test relationships by creating a simple table or matrix visualization