😎PowerBi - Data Modeling

📘 Power BI Notes – Chapter: Data Modeling

🔶 What is Data Modeling?

Data Modeling in Power BI is the process of creating relationships between different data tables to build an efficient and logical structure for reporting and analysis. A well-designed model enables you to create powerful visuals, write accurate DAX expressions, and ensure optimal performance.

🔶 Why Data Modeling is Important

• It connects unrelated data tables meaningfully.
• Improves performance of reports.
• Helps in creating meaningful measures using DAX.
• Ensures accurate and scalable reports.
• Enables complex analytics like time intelligence, segmentation, etc.

🔶 Core Components of Data Modeling

1. Tables

• Represent the data from your data sources.
• Can be fact or dimension tables.

2. Relationships

• Define how tables connect via keys.
• Use primary (dimension) and foreign (fact) keys.

3. Keys

• Primary Key: Uniquely identifies each record in a dimension table.
• Foreign Key: Used in the fact table to link to a dimension table.

4. Cardinality

• One-to-One (1:1)
• One-to-Many (1:*)
• Many-to-One (*:1)
• Many-to-Many (:)

🔶 Fact vs Dimension Tables

➤ Fact Table

• Contains measurable data (metrics).
• Examples: Sales Amount, Quantity, Profit.
• Contains foreign keys to dimension tables.

➤ Dimension Table

• Contains descriptive attributes (text fields).
• Examples: Customer Name, Product Category, Date.
• Each row describes a unique item.

🔶 Star Schema in Power BI

A Star Schema is the most recommended modeling technique for Power BI. It includes:

• A central Fact Table.
• Surrounding Dimension Tables.

📌 Advantages of Star Schema:

• Simple structure.
• Easier DAX formulas.
• Better performance.
• Easier to expand and maintain.

✅ Example:

Dimension Table	Key Field	Attributes
Date	DateID	Year, Month, Day
Product	ProductID	Product Name, Category
Customer	CustomerID	Customer Name, Country
Store	StoreID	Store Name, City

Diagram:

        +-------------+
        |   Product   |
        +-------------+
              |
              |
+-------------+    +-------------+
|   Customer  |----|    Date     |
+-------------+    +-------------+
              |
        +-------------+
        |    Sales    |
        +-------------+
              |
        +-------------+
        |   Store     |
        +-------------+
  

🔶 Snowflake Schema

• Extension of Star Schema.
• Dimension tables are normalized into multiple related tables.
• Used for storage efficiency but not recommended for Power BI due to performance overhead.

🔶 Creating Relationships

Go to Model View → Drag a column from one table to another OR use “Manage Relationships”.

• Cardinality: One-to-Many, One-to-One, Many-to-One.
• Cross Filter Direction: Single or Both.
• Make relationship active (only one active between two tables at a time).

🔶 Cross Filter Direction

Type	Description	Example
Single	Default. Filter flows from dimension to fact.	Product → Sales
Both	Used in complex models. May create ambiguity.	Sales ↔ Region ↔ Budget

🔶 Inactive Relationships

Power BI allows multiple relationships, but only one can be active at a time. Others are inactive, and can be activated using:

CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], Date[Date]))

🔶 DAX in Data Modeling

• Calculated columns
• Measures
• Filtering tables
• Time intelligence

Total Sales = SUM(Sales[SalesAmount])
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
  

🔶 Measures vs Calculated Columns vs Tables

Feature	Measures	Calculated Columns	Calculated Tables
Evaluated on	Aggregation (on report)	Row-level	Table level
Storage	Not stored (on-the-fly)	Stored in model	Stored in model
Use case	Aggregations, KPIs	Row calculations	Custom tables, filtering
Example	SUM(Sales[Amount])	Price * Quantity	FILTER(Sales, ...)

🔶 Role Playing Dimensions

When one dimension (like Date) is used multiple times in a table.

Example: Sales table has:

• OrderDate
• ShipDate
• InvoiceDate

Solution: Duplicate Date table for each role.

🔶 Hierarchies

Create custom drill-downs:

Example: Date hierarchy → Year → Quarter → Month → Day

Used in visuals like bar and line charts.

🔶 Data Modeling Best Practices

• Use Star Schema.
• Hide unnecessary columns from report view.
• Name relationships clearly.
• Avoid calculated columns unless necessary.
• Create measures for aggregations.
• Use surrogate keys for better performance.
• Minimize use of bi-directional filtering.
• Use Date Table for time-based functions.
• Use Manage Relationships instead of relying only on auto-detect.
• Document your model with descriptions.