Data Modeling with Microsoft Power BI

Foreword
Preface
Who Is This Book For?
What Is Data Modeling?
What Is Power BI?
What Is So Special About a Power BI Data Model?
What Is DAX?
What Is Power Query?
What Is SQL?
A New Release Every Few Weeks
How to Read This Book
Installing Necessary Software
Additional Tools
Demo Files
Conventions Used in This Book
Using Code Examples
O’Reilly Online Learning
How to Contact Us
Acknowledgments

I. Data Modeling 101
1. What Is a Data Model?
Data Model
Basic Components
Entity
Tables
Relationships
Primary Keys
Surrogate Keys
Foreign Keys
Cardinality
Combining Tables
Set Operators
Joins
Join Path Problems
Entity Relationship Diagrams
Data Modeling Options
Types of Tables
A Single Table to Store It All
Normal Forms
Dimensional Modeling
Granularity
Extract, Transform, Load
Ralph Kimball and Bill Inmon
Data Vaults and Other Anti-Patterns
Key Takeaways

2. Building a Data Model
Normalizing
Denormalizing
Calculations
Flags and Indicators
Time and Date
Role-Playing Dimensions
Slowly Changing Dimensions
Type 0: Retain Original
Type 1: Overwrite
Type 2: Add New Row
Type 3: Add New Attributes
Type 4: Add Mini-Dimensions
Types 5, 6, and 7
Hierarchies
Key Takeaways

3. Real-World Examples
Binning
Adding a Column to a Fact Table
Creating a Lookup Table
Describing the Ranges of the Bins
Budget
Identifying the Granularity
Handling Fact Tables of Different Cardinality
Multi-Language Model
Key-Value Pair Tables
Combining Self-Service and Enterprise BI
Key Takeaways

4. Performance Tuning
Key Takeaways

II. Data Modeling in Power BI
5. Understanding a Power BI Data Model
Data Model
Basic Concepts
Tables and Columns
Relationships
Primary Keys
Surrogate Keys
Foreign Keys
Cardinality
Combining Tables
Set Operators
Joins
Join Path Problems
Entity Relationship Diagrams
Data Modeling Options
Types of Tables
A Single Table to Store It All
Normal Forms
Dimensional Modeling
Granularity
Extract, Transform, Load
Key Takeaways

6. Building a Data Model in Power BI
Normalizing and Denormalizing
Calculations
Time and Date
Turning off Auto Date/Time
Marking the Date Table
Role-Playing Dimensions
Slowly Changing Dimensions
Hierarchies
Key Takeaways

7. Real-World Examples Using Power BI
Binning
Lookup Table
Range Table
Budget
Multi-Language Model
Dimension Table for the Available Languages
Visual Elements
Text-Based Content
Numerical Content
Data Model’s Metadata
UI of Power BI Desktop (Standalone)
UI of Power BI Desktop (Windows Store)
UI of the Power BI Service
UI of Power BI Report Server
Key-Value Pair Tables
Combining Self-Service and Enterprise BI
Key Takeaways

8. Performance Tuning in the Power BI Data Model
Storage Mode
Partitioning
Pre-Aggregating
Composite Models
Dual Mode
Hybrid Tables
Key Takeaways

III. Data Modeling for Power BI with the Help of DAX
9. Understanding a Data Model from the DAX Point of View
Data Model
Basic Components
Tables
Relationships
Primary Keys
Combining Queries
Set Operators
Joins
Extract, Transform, Load
Key Takeaways

10. Building a Data Model with DAX
Normalizing
Denormalizing
Calculations
Simple Aggregations for Additive Calculations
Semi-Additive Calculations
Re-create the Calculation as a DAX Measure
Time-Intelligence Calculations
Flags and Indicators
IF Function
SWITCH Function
SWITCH TRUE Function
Lookup Table
Treating BLANK values
Time and Date
Role-Playing Dimensions
Slowly Changing Dimensions
Hierarchies
Key Takeaways

11. Real-World Examples Using DAX
Binning
Lookup Table
Range Table
Budget
Multi-Language Model
Key-Value Pair Tables
Combining Self-Service and Enterprise BI
Key Takeaways

12. Performance Tuning with DAX
Storage Mode
Pre-Aggregating
Aggregation-Aware Measures
Key Takeaways

IV. Data Modeling for Power BI with the Help of Power Query
13. Understanding a Data Model from the Power Query Point of View
Data Model
Basic Components
Tables or Queries
Relationships
Primary Keys
Surrogate Keys
Combining Queries
Set Operators
Joins
Query Dependencies
Types of Queries
Extract, Transform, Load
Key Takeaways

14. Building a Data Model with Power Query and M
Normalizing
Column Quality
Column Distribution
Column Profile
Identifying the Columns to Normalize
Creating a Query per Dimension
Creating One Common Dimension Query
Denormalizing
Calculations
Flags and Indicators
Time and Date
Role-Playing Dimensions
Slowly Changing Dimensions
Hierarchies
Key Takeaways

15. Real-World Examples Using Power Query and M
Binning
Create a Bin Table by Hand
Deriving the Bin Table from the Facts
Create a Bin Table in M
Create a Bin Range Table in M
Budget
Multi-Language Model
Key-Value Pair Tables
Using the GUI
Using M Code
Writing an M Function
Combining Self-Service and Enterprise BI
Key Takeaways

16. Performance Tuning the Data Model with Power Query
Storage Mode
Partitioning
Pre-Aggregating
Key Takeaways

V. Data Modeling for Power BI with the Help of SQL
17. Understanding a Relational Data Model
Data Model
Basic Components
Tables
Relationships
Primary Keys
Surrogate Keys
Foreign Keys
Combining Queries
Set Operators
Joins
Join Path Problems
Entity Relationship Diagrams
Extract, Transform, Load
Key Takeaways

18. Building a Data Model with SQL
Normalizing
Persisting into a Table
Creating a View
Creating a Function
Creating a Procedure
Creating a Filter Dimension
Denormalizing
Calculations
Flags and Indicators
Time and Date
Role-Playing Dimensions
Slowly Changing Dimensions
Type 0: Retain Original
Type 1: Overwrite
Type 2: Add New Row
Hierarchies
Key Takeaways

19. Real-World Examples Using SQL
Binning
Deriving the Lookup Table from the Facts
Generating a Lookup Table
Range Table
Budget
Multi-Language Model
Key-Value Pair Tables
Combining Self-Service and Enterprise BI
Key Takeaways

20. Performance Tuning the Data Model with SQL
Storage Modes
Table
Index
Compression
View
Function
Stored Procedure
Partitioning
Pre-Aggregating
Key Takeaways

Epilogue
Index
About the Author