Data Modeling with Microsoft Power BI

Data Modeling with Microsoft Power BI

by Markus Ehrenmueller-Jensen
Released June 2024
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098148553

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

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

Publisher resources

View/Submit Errata

Table of contents

  1. Foreword
  2. Preface
    1. Who Is This Book For?
    2. What Is Data Modeling?
    3. What Is Power BI?
    4. What Is So Special About a Power BI Data Model?
    5. What Is DAX?
    6. What Is Power Query?
    7. What Is SQL?
    8. A New Release Every Few Weeks
    9. How to Read This Book
    10. Installing Necessary Software
    11. Additional Tools
    12. Demo Files
    13. Conventions Used in This Book
    14. Using Code Examples
    15. O’Reilly Online Learning
    16. How to Contact Us
    17. Acknowledgments
  3. I. Data Modeling 101
  4. 1. What Is a Data Model?
    1. Data Model
    2. Basic Components
      1. Entity
      2. Tables
      3. Relationships
      4. Primary Keys
      5. Surrogate Keys
      6. Foreign Keys
      7. Cardinality
    3. Combining Tables
      1. Set Operators
      2. Joins
      3. Join Path Problems
      4. Entity Relationship Diagrams
    4. Data Modeling Options
      1. Types of Tables
      2. A Single Table to Store It All
      3. Normal Forms
      4. Dimensional Modeling
      5. Granularity
      6. Extract, Transform, Load
      7. Ralph Kimball and Bill Inmon
      8. Data Vaults and Other Anti-Patterns
    5. Key Takeaways
  5. 2. Building a Data Model
    1. Normalizing
    2. Denormalizing
    3. Calculations
    4. Flags and Indicators
    5. Time and Date
    6. Role-Playing Dimensions
    7. Slowly Changing Dimensions
      1. Type 0: Retain Original
      2. Type 1: Overwrite
      3. Type 2: Add New Row
      4. Type 3: Add New Attributes
      5. Type 4: Add Mini-Dimensions
      6. Types 5, 6, and 7
    8. Hierarchies
    9. Key Takeaways
  6. 3. Real-World Examples
    1. Binning
      1. Adding a Column to a Fact Table
      2. Creating a Lookup Table
      3. Describing the Ranges of the Bins
    2. Budget
      1. Identifying the Granularity
      2. Handling Fact Tables of Different Cardinality
    3. Multi-Language Model
    4. Key-Value Pair Tables
    5. Combining Self-Service and Enterprise BI
    6. Key Takeaways
  7. 4. Performance Tuning
    1. Key Takeaways
  8. II. Data Modeling in Power BI
  9. 5. Understanding a Power BI Data Model
    1. Data Model
    2. Basic Concepts
      1. Tables and Columns
      2. Relationships
      3. Primary Keys
      4. Surrogate Keys
      5. Foreign Keys
      6. Cardinality
    3. Combining Tables
      1. Set Operators
      2. Joins
      3. Join Path Problems
      4. Entity Relationship Diagrams
    4. Data Modeling Options
      1. Types of Tables
      2. A Single Table to Store It All
      3. Normal Forms
      4. Dimensional Modeling
      5. Granularity
      6. Extract, Transform, Load
    5. Key Takeaways
  10. 6. Building a Data Model in Power BI
    1. Normalizing and Denormalizing
    2. Calculations
    3. Time and Date
      1. Turning off Auto Date/Time
      2. Marking the Date Table
    4. Role-Playing Dimensions
    5. Slowly Changing Dimensions
    6. Hierarchies
    7. Key Takeaways
  11. 7. Real-World Examples Using Power BI
    1. Binning
      1. Lookup Table
      2. Range Table
    2. Budget
    3. Multi-Language Model
      1. Dimension Table for the Available Languages
      2. Visual Elements
      3. Text-Based Content
      4. Numerical Content
      5. Data Model’s Metadata
      6. UI of Power BI Desktop (Standalone)
      7. UI of Power BI Desktop (Windows Store)
      8. UI of the Power BI Service
      9. UI of Power BI Report Server
    4. Key-Value Pair Tables
    5. Combining Self-Service and Enterprise BI
    6. Key Takeaways
  12. 8. Performance Tuning in the Power BI Data Model
    1. Storage Mode
    2. Partitioning
    3. Pre-Aggregating
    4. Composite Models
    5. Dual Mode
    6. Hybrid Tables
    7. Key Takeaways
  13. III. Data Modeling for Power BI with the Help of DAX
  14. 9. Understanding a Data Model from the DAX Point of View
    1. Data Model
    2. Basic Components
      1. Tables
      2. Relationships
      3. Primary Keys
    3. Combining Queries
      1. Set Operators
      2. Joins
      3. Extract, Transform, Load
    4. Key Takeaways
  15. 10. Building a Data Model with DAX
    1. Normalizing
    2. Denormalizing
    3. Calculations
      1. Simple Aggregations for Additive Calculations
      2. Semi-Additive Calculations
      3. Re-create the Calculation as a DAX Measure
      4. Time-Intelligence Calculations
    4. Flags and Indicators
      1. IF Function
      2. SWITCH Function
      3. SWITCH TRUE Function
      4. Lookup Table
      5. Treating BLANK values
    5. Time and Date
    6. Role-Playing Dimensions
    7. Slowly Changing Dimensions
    8. Hierarchies
    9. Key Takeaways
  16. 11. Real-World Examples Using DAX
    1. Binning
      1. Lookup Table
      2. Range Table
    2. Budget
    3. Multi-Language Model
    4. Key-Value Pair Tables
    5. Combining Self-Service and Enterprise BI
    6. Key Takeaways
  17. 12. Performance Tuning with DAX
    1. Storage Mode
    2. Pre-Aggregating
    3. Aggregation-Aware Measures
    4. Key Takeaways
  18. IV. Data Modeling for Power BI with the Help of Power Query
  19. 13. Understanding a Data Model from the Power Query Point of View
    1. Data Model
    2. Basic Components
      1. Tables or Queries
      2. Relationships
      3. Primary Keys
      4. Surrogate Keys
    3. Combining Queries
      1. Set Operators
      2. Joins
      3. Query Dependencies
      4. Types of Queries
      5. Extract, Transform, Load
    4. Key Takeaways
  20. 14. Building a Data Model with Power Query and M
    1. Normalizing
      1. Column Quality
      2. Column Distribution
      3. Column Profile
      4. Identifying the Columns to Normalize
      5. Creating a Query per Dimension
      6. Creating One Common Dimension Query
    2. Denormalizing
    3. Calculations
    4. Flags and Indicators
    5. Time and Date
    6. Role-Playing Dimensions
    7. Slowly Changing Dimensions
    8. Hierarchies
    9. Key Takeaways
  21. 15. Real-World Examples Using Power Query and M
    1. Binning
      1. Create a Bin Table by Hand
      2. Deriving the Bin Table from the Facts
      3. Create a Bin Table in M
      4. Create a Bin Range Table in M
    2. Budget
    3. Multi-Language Model
    4. Key-Value Pair Tables
      1. Using the GUI
      2. Using M Code
      3. Writing an M Function
    5. Combining Self-Service and Enterprise BI
    6. Key Takeaways
  22. 16. Performance Tuning the Data Model with Power Query
    1. Storage Mode
    2. Partitioning
    3. Pre-Aggregating
    4. Key Takeaways
  23. V. Data Modeling for Power BI with the Help of SQL
  24. 17. Understanding a Relational Data Model
    1. Data Model
    2. Basic Components
      1. Tables
      2. Relationships
      3. Primary Keys
      4. Surrogate Keys
      5. Foreign Keys
    3. Combining Queries
      1. Set Operators
      2. Joins
      3. Join Path Problems
      4. Entity Relationship Diagrams
      5. Extract, Transform, Load
    4. Key Takeaways
  25. 18. Building a Data Model with SQL
    1. Normalizing
      1. Persisting into a Table
      2. Creating a View
      3. Creating a Function
      4. Creating a Procedure
      5. Creating a Filter Dimension
    2. Denormalizing
    3. Calculations
    4. Flags and Indicators
    5. Time and Date
    6. Role-Playing Dimensions
    7. Slowly Changing Dimensions
      1. Type 0: Retain Original
      2. Type 1: Overwrite
      3. Type 2: Add New Row
    8. Hierarchies
    9. Key Takeaways
  26. 19. Real-World Examples Using SQL
    1. Binning
      1. Deriving the Lookup Table from the Facts
      2. Generating a Lookup Table
      3. Range Table
    2. Budget
    3. Multi-Language Model
    4. Key-Value Pair Tables
    5. Combining Self-Service and Enterprise BI
    6. Key Takeaways
  27. 20. Performance Tuning the Data Model with SQL
    1. Storage Modes
      1. Table
      2. Index
      3. Compression
      4. View
      5. Function
      6. Stored Procedure
    2. Partitioning
    3. Pre-Aggregating
    4. Key Takeaways
  28. Epilogue
  29. Index
  30. About the Author

Product information

  • Title: Data Modeling with Microsoft Power BI
  • Author(s): Markus Ehrenmueller-Jensen
  • Release date: June 2024
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098148553