Data Modeling with Microsoft Power BI Self-Service and Enterprise Data Warehouses with Power BI (Markus Ehrenmueller-Jensen) (Z-Library)

Markus Ehrenmueller-Jensen Data Modeling with Microsoft Power BI Self-Service and Enterprise Data Warehouse with Power BI

DATA “This book is a comprehensive tutorial that covers the subject in language that is easy to understand yet thorough, concise, and accurate. Markus’s mastery of the art and science of data modeling provides value for any data professional working with Power BI.” —Paul Turley Microsoft Data Platform MVP Data Modeling with Microsoft Power BI linkedin.com/company/oreilly-media youtube.com/oreillymedia 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. You’ll learn how to: • Normalize and denormalize • 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 tables • Discover and tackle performance issues via the data model • Work with tables, relations, set operations, normal forms, dimensional modeling, and ETL Markus Ehrenmueller-Jensen, founder of Savory Data, has worked as a project leader, trainer, and consultant for data engineering, business intelligence, and data science since 1994. He’s a software engineer and professor at HTL Leonding (technical college), teaching databases and project engineering. He has several Microsoft certifications and is a Microsoft Data Platform MVP. 9 7 8 1 0 9 8 1 4 8 5 5 3 5 6 9 9 9 US $69.99 CAN $87.99 ISBN: 978-1-098-14855-3

Praise for Data Modeling with Microsoft Power BI This excellent book will tell you why to “star schema all the things.” It explains in great depth why data modeling is important and provides ample examples. Reading this book will make your life as a Power BI developer easier. —Koen Verbeeck, Senior BI Architect, Star Schema Aficionado, Microsoft Data Platform MVP Markus’s book Data Modeling with Microsoft Power BI provides a very good introduction to data modeling principles for an effective data model in Power BI as well as in Excel’s data model, Power Pivot. The book is written in an explanatory way, using clear language that can be read by both novices and experts alike. It is very accessible and a must-have for those who want to learn more about data modeling. I especially like Markus’s division of the book into a kind of matrix, where each of the five main sections is divided into four chapters dealing with the same four subtopics— understanding the data model, building a data model, examples from the real world, and performance optimization—which become more and more complex throughout the book so you gradually get more and more insight into the many facets of data modeling. —Jørgen Koch, Innovate, Microsoft Power BI and Office Enthusiast (SME), author and Microsoft Certified Trainer

Creating a fancy report and tinkering with DAX or M-Code in the times of AI is not hard—creating a high-performing model that will work is. Markus is a “Model Wizard” and has fixed more of my work than I would like to admit. This hands-on guide will give you a shot at mastering the model-building challenges ahead of you. Although Power BI has evolved in leaps and bounds, the challenge of building a solid and performing model has not. Markus has seen endless environments and setups. I am a witness to his magic; he helped me fix broken models just by taking a quick look. With his new book he shares his knowledge with all of us and with his hands-on approach, he will guide you to becoming a Model Wizard yourself. —Joel Ruh, Digital Transformation Lead at SkyFrame Data Modeling with Microsoft Power BI is a must-read for anyone looking to master this powerful tool. Markus Ehrenmueller-Jensen has created an easy-to-read and fun guide that will help you unlock the full potential of Power BI. —Carola Seyr, Business Intelligence Team Lead at Porsche Holding Salzburg

Markus Ehrenmueller-Jensen Data Modeling with Microsoft Power BI Self-Service and Enterprise Data Warehouses with Power BI Boston Farnham Sebastopol TokyoBeijing

978-1-098-14855-3 [LSI] Data Modeling with Microsoft Power BI by Markus Ehrenmueller-Jensen Copyright © 2024 Savory Data Gmbh. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institu‐ tional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Michelle Smith Indexer: WordCo Indexing Services, Inc. Development Editor: Shira Evans Interior Designer: David Futato Production Editor: Katherine Tozer Cover Designer: Karen Montgomery Copyeditor: Liz Wheeler Illustrator: Kate Dullea Proofreader: M & R Consultants Corporation June 2024: First Edition Revision History for the First Edition 2024-06-11: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098148553 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Data Modeling with Microsoft Power BI, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

I want to dedicate this book to the best thing that ever happened to me: my lovely children Clara (Alex) and Victor.

Table of Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Part I. Data Modeling 101 1. What Is a Data Model?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Data Model 4 Basic Components 5 Entity 5 Tables 6 Relationships 6 Primary Keys 7 Surrogate Keys 8 Foreign Keys 9 Cardinality 10 Combining Tables 11 Set Operators 11 Joins 13 Join Path Problems 20 Entity Relationship Diagrams 25 Data Modeling Options 28 Types of Tables 28 A Single Table to Store It All 28 Normal Forms 29 vii

Dimensional Modeling 33 Granularity 35 Extract, Transform, Load 36 Ralph Kimball and Bill Inmon 38 Data Vaults and Other Anti-Patterns 40 Key Takeaways 42 2. Building a Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Normalizing 44 Denormalizing 45 Calculations 46 Flags and Indicators 47 Time and Date 47 Role-Playing Dimensions 48 Slowly Changing Dimensions 49 Type 0: Retain Original 49 Type 1: Overwrite 50 Type 2: Add New Row 51 Type 3: Add New Attributes 52 Type 4: Add Mini-Dimensions 53 Types 5, 6, and 7 53 Hierarchies 53 Key Takeaways 55 3. Real-World Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Binning 58 Adding a Column to a Fact Table 58 Creating a Lookup Table 58 Describing the Ranges of the Bins 59 Budget 60 Identifying the Granularity 60 Handling Fact Tables of Different Cardinality 61 Multi-Language Model 63 Key-Value Pair Tables 65 Combining Self-Service and Enterprise BI 67 Key Takeaways 67 4. Performance Tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Key Takeaways 71 viii | Table of Contents

Part II. Data Modeling in Power BI 5. Understanding a Power BI Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Data Model 75 Basic Concepts 78 Tables and Columns 78 Relationships 88 Primary Keys 94 Surrogate Keys 94 Foreign Keys 94 Cardinality 95 Combining Tables 97 Set Operators 97 Joins 97 Join Path Problems 98 Entity Relationship Diagrams 100 Data Modeling Options 101 Types of Tables 101 A Single Table to Store It All 103 Normal Forms 106 Dimensional Modeling 107 Granularity 107 Extract, Transform, Load 108 Key Takeaways 108 6. Building a Data Model in Power BI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Normalizing and Denormalizing 109 Calculations 112 Time and Date 116 Turning off Auto Date/Time 116 Marking the Date Table 121 Role-Playing Dimensions 123 Slowly Changing Dimensions 127 Hierarchies 129 Key Takeaways 130 7. Real-World Examples Using Power BI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Binning 134 Lookup Table 134 Table of Contents | ix

Range Table 135 Budget 135 Multi-Language Model 139 Dimension Table for the Available Languages 139 Visual Elements 140 Text-Based Content 141 Numerical Content 142 Data Model’s Metadata 143 UI of Power BI Desktop (Standalone) 146 UI of Power BI Desktop (Windows Store) 147 UI of the Power BI Service 148 UI of Power BI Report Server 148 Key-Value Pair Tables 149 Combining Self-Service and Enterprise BI 150 Key Takeaways 152 8. Performance Tuning in the Power BI Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Storage Mode 153 Partitioning 160 Pre-Aggregating 166 Composite Models 168 Dual Mode 169 Hybrid Tables 170 Key Takeaways 170 Part III. Data Modeling for Power BI with the Help of DAX 9. Understanding a Data Model from the DAX Point of View. . . . . . . . . . . . . . . . . . . . . . 175 Data Model 175 Basic Components 176 Tables 176 Relationships 179 Primary Keys 180 Combining Queries 180 Set Operators 180 Joins 182 Extract, Transform, Load 185 Key Takeaways 186 x | Table of Contents

10. Building a Data Model with DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Normalizing 187 Denormalizing 190 Calculations 190 Simple Aggregations for Additive Calculations 195 Semi-Additive Calculations 195 Re-create the Calculation as a DAX Measure 196 Time-Intelligence Calculations 198 Flags and Indicators 200 IF Function 200 SWITCH Function 201 SWITCH TRUE Function 201 Lookup Table 202 Treating BLANK values 202 Time and Date 203 Role-Playing Dimensions 206 Slowly Changing Dimensions 207 Hierarchies 210 Key Takeaways 214 11. Real-World Examples Using DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 Binning 216 Lookup Table 216 Range Table 217 Budget 220 Multi-Language Model 222 Key-Value Pair Tables 229 Combining Self-Service and Enterprise BI 232 Key Takeaways 233 12. Performance Tuning with DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235 Storage Mode 235 Pre-Aggregating 235 Aggregation-Aware Measures 236 Key Takeaways 237 Table of Contents | xi

Part IV. Data Modeling for Power BI with the Help of Power Query 13. Understanding a Data Model from the Power Query Point of View. . . . . . . . . . . . . . . 241 Data Model 242 Basic Components 244 Tables or Queries 244 Relationships 248 Primary Keys 248 Surrogate Keys 249 Combining Queries 251 Set Operators 251 Joins 252 Query Dependencies 253 Types of Queries 255 Extract, Transform, Load 256 Key Takeaways 256 14. Building a Data Model with Power Query and M. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Normalizing 258 Column Quality 258 Column Distribution 259 Column Profile 260 Identifying the Columns to Normalize 261 Creating a Query per Dimension 265 Creating One Common Dimension Query 269 Denormalizing 270 Calculations 273 Flags and Indicators 275 Time and Date 279 Role-Playing Dimensions 284 Slowly Changing Dimensions 285 Hierarchies 286 Key Takeaways 295 15. Real-World Examples Using Power Query and M. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 Binning 298 Create a Bin Table by Hand 298 Deriving the Bin Table from the Facts 299 Create a Bin Table in M 301 xii | Table of Contents

Create a Bin Range Table in M 307 Budget 308 Multi-Language Model 313 Key-Value Pair Tables 315 Using the GUI 316 Using M Code 319 Writing an M Function 320 Combining Self-Service and Enterprise BI 324 Key Takeaways 325 16. Performance Tuning the Data Model with Power Query. . . . . . . . . . . . . . . . . . . . . . . . 327 Storage Mode 327 Partitioning 328 Pre-Aggregating 329 Key Takeaways 331 Part V. Data Modeling for Power BI with the Help of SQL 17. Understanding a Relational Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 Data Model 335 Basic Components 336 Tables 336 Relationships 338 Primary Keys 338 Surrogate Keys 339 Foreign Keys 340 Combining Queries 341 Set Operators 341 Joins 344 Join Path Problems 351 Entity Relationship Diagrams 356 Extract, Transform, Load 357 Key Takeaways 359 18. Building a Data Model with SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Normalizing 362 Persisting into a Table 367 Creating a View 369 Creating a Function 370 Table of Contents | xiii

Creating a Procedure 371 Creating a Filter Dimension 373 Denormalizing 375 Calculations 376 Flags and Indicators 379 Time and Date 383 Role-Playing Dimensions 385 Slowly Changing Dimensions 387 Type 0: Retain Original 388 Type 1: Overwrite 389 Type 2: Add New Row 392 Hierarchies 395 Key Takeaways 397 19. Real-World Examples Using SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399 Binning 399 Deriving the Lookup Table from the Facts 400 Generating a Lookup Table 401 Range Table 402 Budget 403 Multi-Language Model 404 Key-Value Pair Tables 408 Combining Self-Service and Enterprise BI 414 Key Takeaways 414 20. Performance Tuning the Data Model with SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417 Storage Modes 417 Table 418 Index 418 Compression 420 View 420 Function 421 Stored Procedure 421 Partitioning 421 Pre-Aggregating 429 Key Takeaways 430 Epilogue. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 xiv | Table of Contents

Foreword No topic in the data industry is more debated than data modeling. It is the source of memes, T-shirts, and endless debates at conferences, and its demise has been predic‐ ted for years. Yet here we are with a new book about data modeling. And it is sorely needed; data modeling is a foundational skill with many applications. It makes tough problems easier to solve, data easier to work with, and Data Analysis Expressions (DAX) easier to write. It improves performance and eventually saves costs. However, you must be willing to put in the work; it’s necessary to start thinking about data modeling early in the process, whether you’re designing a data warehouse, lakehouse, or semantic model in Power BI. The data model is the cornerstone of your project. I have learned this from working with customers on all variations of analysis services over the years (from Power Pivot to SSAS and Power BI). With proper data modeling, you won’t have to resort to as many DAX gymnastics. A good data model simplifies your calculations. I’ve known Markus for many years and always enjoy his sessions at conferences. He explains tough topics in a simple manner, and this book is no exception. In Data Modeling with Microsoft Power BI, Markus explores the many facets and long history of data modeling (who doesn’t have the Kimball data warehousing book on the shelf?): how we need to think about data, and how we can translate requirements into entities and attributes. Markus does a great job applying these theoretical practi‐ ces to real life. How do these data modeling practices help you with everyday Power BI and SQL challenges? Markus explains the basics of data modeling in Power BI by looking at tables, relationships, and analysis of data granularity. He then shows how to translate common requirements like role-playing dimensions, slowly changing dimensions, binning, and translations into SQL, DAX, M, or in the model itself so you can use them at any step of your project. xv

The lessons in this book are very valuable, helping you simplify your day-to-day work as a data engineer, and it all starts with the model. — Kasper de Jonge Principal Program Manager at Microsoft Fabric xvi | Foreword

Preface Welcome to this journey into data modeling concepts and practical examples for Power BI, including DAX, Power Query and T-SQL. This book is your companion on your journey to gain a comprehensive understanding about the steps needed to make building reports in Power BI Desktop and Power BI Report Builder, and creat‐ ing measures in DAX, easier. Power BI supports a wide variety of data sources (covering databases from different vendors, like Microsoft, Oracle or Teradata; flat files, like CSV, text, or Excel; web services like an https link to a web page, etc.). The only way to get data into Power BI is through Power Query. It’s best practice to add calculations as (explicit) measures in DAX (as opposed to calculated columns in DAX or as columns in Power Query or in the data source). Creating calculated tables in DAX should be an exception; depend‐ ing on your skills and preferences, you will implement transformations to shape the data model either in Power Query (in the user interface or by writing code in the M language) or in the data source. For example, in the case of a relational data ware‐ house implemented in Microsoft’s relational database engines, you might use T-SQL in the data warehouse, as laid out in Figure P-1. The first part of this book, which is written in an agnostic way, introduces the neces‐ sary concepts in a general way: you can apply this to any analytical system. The sec‐ ond part of the book explains the properties of a data model in Power BI. The rest of this book addresses DAX, Power Query, and SQL. The book is designed for you, the reader, to have an individual experience based on your knowledge. You may not know DAX, Power Query, and SQL, but you may have familiarity with one or two of them; you can pick and choose to fill in gaps in your knowledge. Maybe you need a refresher on the composition of a data model. Part I has you covered. Maybe you struggle with dealing with a bunch of Excel files from which you need to create reports? The part on Power Query will be your starting point. Maybe your task is to build a data warehouse to which other people connect xvii

with Power BI Desktop? Then the part about SQL will present you with solutions to typical problems. Figure P-1. Power BI data-shaping architecture Data modeling is definitely the single most underestimated task when working with Power BI Desktop. It is a crucial part in your steps, from raw data to business intelli‐ gence and analytics. Decisions made during data modeling will influence how much detail your reports can show, how user-friendly the database or semantic model is for creating reports and analysis, and how easy it is to add more data and implement cal‐ culations on existing data. Wrong decisions at the start are very expensive to fix later, as changes to the data model will break existing reports. I speak from experience: I had to learn this the hard way—and I see also other people struggling with the reper‐ cussions almost every day in my work as a trainer and consultant. This book is your guide to getting data modeling right from the beginning. You will learn that it is less important how complex the steps done in the “back end” (DAX, Power Query, or T-SQL) are, as long as the result is an easier-to-understand and easier-to-use data model for the user who creates reports and does analytics based on this data model: report authors, business analysts, data scientists, etc. These steps can be as simple as changing technical names (e.g., CSTNM4711) into user- friendly names (e.g., Customer Name) or as complicated as combining or splitting tables into a whole new structure. You will learn how to add calculations to the data model and enrich plain data with metadata (hierarchies, translations, etc.). This book is full of practical examples from challenges I faced over more than 25 years in the field. Keep in mind that the essential goal is to remove the burden from the report creator. xviii | Preface