Data Modeling with Microsoft Power BI: Self-Service and Enterprise Data Warehouses with Power BI

✍ Scribed by Markus Ehrenmueller-Jensen

• Year: 2024
• Tongue: English
• Leaves: 485
• Category: Library

✦ Synopsis

Data modeling is the single most overlooked feature in Power BI Desktop, yet it's what sets Power BI apart from other tools on the market. This practical book serves as your fast-forward button for data modeling with Power BI, Analysis Services tabular, and SQL databases. It serves as a starting point for data modeling, as well as a handy refresher.

Author Markus Ehrenmueller-Jensen, founder of Savory Data, shows you the basic concepts of Power BI's semantic model with hands-on examples in DAX, Power Query, and T-SQL. If you're looking to build a data warehouse layer, chapters with T-SQL examples will get you started. You'll begin with simple steps and gradually solve more complex problems.

This book shows you how to
• Normalize and denormalize with DAX, Power Query, and T-SQL
• Apply best practices for calculations, flags and indicators, time and date, role-playing dimensions and slowly changing dimensions
• Solve challenges such as binning, budget, localized models, composite models, and key value with DAX, Power Query, and T-SQL
• Discover and tackle performance issues by applying solutions in DAX, Power Query, and T-SQL
• Work with tables, relations, set operations, normal forms, dimensional modeling, and ETL

✦ Table of Contents

Cover
Copyright
Table of Contents
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
Part I. Data Modeling 101
Chapter 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
Chapter 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
Chapter 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
Chapter 4. Performance Tuning
Key Takeaways
Part II. Data Modeling in Power BI
Chapter 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
Chapter 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
Chapter 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
Chapter 8. Performance Tuning in the Power BI Data Model
Storage Mode
Partitioning
Pre-Aggregating
Composite Models
Dual Mode
Hybrid Tables
Key Takeaways
Part III. Data Modeling for Power BI with the Help of DAX
Chapter 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
Chapter 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
Chapter 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
Chapter 12. Performance Tuning with DAX
Storage Mode
Pre-Aggregating
Aggregation-Aware Measures
Key Takeaways
Part IV. Data Modeling for Power BI with the Help of Power Query
Chapter 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
Chapter 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
Chapter 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
Chapter 16. Performance Tuning the Data Model with Power Query
Storage Mode
Partitioning
Pre-Aggregating
Key Takeaways
Part V. Data Modeling for Power BI with the Help of SQL
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Colophon