Day 8: Power Pivot – Data Modeling in Power BI

On Day 8, we dive into Power Pivot, which is essential for data modeling in Power BI. Data modeling allows you to define relationships between tables, enabling complex analysis and reporting.

1. Building Relationships

In Power Pivot, you can create virtual relationships between tables using a common column.

• You can create N number of relationships between tables.

• Only one relationship can be active at a time; the others remain inactive but can be used in calculations with DAX functions.

2. Types of Tables

Understanding different table types is crucial for modeling:

• Fact Table:

  • A centralized table containing transactional or quantitative data such as product sales, revenue, or customer interactions.

• Dimensional Table:

  • A table that contains descriptive attributes related to the fact table, e.g., product details or customer information.

• Sub-Dimensional Table:

  • A further breakdown of a dimension table for detailed attributes.

• Factless Fact Table:

  • A fact table without numeric measures, typically used to capture events or relationships.

• Role-Playing Dimension:

  • A single dimension used multiple times in the model for different purposes, e.g., “Order Date” and “Ship Date” from the same date table.

3. Schema Types

Schemas define the logical structure of data and table connections:

Star Schema

• Consists of a fact table connected to multiple dimension tables.

• Simple design and easy to understand.

• High data redundancy as normalization is not applied.

• Suitable for simple reporting needs.

Snowflake Schema

• Consists of a fact table, dimension tables, and sub-dimensional tables.

• More complex design, takes time to understand.

• Low data redundancy as normalization and de-normalization are applied.

• Suitable for large datasets requiring detailed analysis.

Galaxy Schema

• A combination of multiple fact tables sharing dimension tables.

• Often used in complex enterprise data models.

4. Managing Relationships in Power BI

In Model View, you can manage relationships efficiently:

• New: Create a relationship manually between tables.

• Autodetect: Power BI automatically detects relationships based on common columns.

• Edit: View or modify an existing relationship.

• Delete: Remove unnecessary relationships.

Making an inactive relationship active:

• Use the DAX function USERELATIONSHIP() to activate an inactive relationship for specific calculations.

5. Cardinality Types

Cardinality defines the type of relationship between tables:

1. One-to-One – Each value in the first table corresponds to one value in the second table.

2. One-to-Many (*) – A value in the first table can have multiple corresponding values in the second table.

3. Many-to-One (*) – Reverse of one-to-many.

4. Many-to-Many (*) – Multiple values in both tables correspond to multiple values in the other table.

Power Pivot and data modeling provide the foundation for accurate and efficient reporting in Power BI. Understanding tables, relationships, schemas, and cardinality is essential for creating robust and scalable BI solutions.