Expert Data Modeling with Power BI - Second Edition

Expert Data Modeling with Power BI - Second Edition

by Soheil Bakhshi, Christian Wade
Released April 2023
Publisher(s): Packt Publishing
ISBN: 9781803246246

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.

Start your free trial

Book description

Take your Power BI reports to the next level by learning various data modeling techniques and leveraging the latest features of Power BI effectively Purchase of the print or Kindle book includes a free eBook in PDF format.

Key Features

  • Get an understanding of data modeling techniques using Power BI with this up-to-date guide
  • Learn how to define the relationships between data sets to extract valuable insights
  • Explore best practices for data preparation and modeling and build optimal data models to solve a wide variety of real-world business challenges

Book Description

This book is a comprehensive guide to understanding the ins and outs of data modeling and how to create full-fledged data models using Power BI confidently.

In this new, fully updated edition, you'll learn how to connect data from multiple sources, understand data, define and manage relationships between data, and shape data models to gain deep and detailed insights about your organization. As you advance through the chapters, the book will demonstrate how to prepare efficient data models in the Power Query Editor and use simpler DAX code with new data modeling features. You'll explore how to use the various data modeling and navigation techniques and perform custom calculations using the modeling features with the help of real-world examples. Finally, you'll learn how to use some new and advanced modeling features to enhance your data models to carry out a wide variety of complex tasks. Additionally, you'll learn valuable best practices and explore common data modeling complications and the solutions to supercharge the process of creating a data model in Power BI and build better-performing data models.

By the end of this Power BI book, you'll have gained the skills you need to structure data coming from multiple sources in different ways to create optimized data models that support high-performing reports and data analytics.

What you will learn

  • Implement virtual tables and time intelligence functionalities in DAX to build a powerful model
  • Identify Dimension and Fact tables and implement them in Power Query Editor
  • Deal with advanced data preparation scenarios while building Star Schema
  • Discover different hierarchies and their common pitfalls
  • Understand complex data models and how to decrease the level of model complexity with different approaches
  • Learn advanced data modeling techniques such as calculation groups, aggregations, incremental refresh, RLS/OLS, and more
  • Get well-versed with datamarts and dataflows in PowerBI

Who this book is for

This MS Power BI book is for BI users, data analysts, and analysis developers who want to become well-versed with data modeling techniques to make the most of Power BI. Basic working knowledge of Power BI and the Star Schema functionality are required to help you to understand the concepts covered in this book.

Table of contents

  1. Preface
    1. Who this book is for
    2. What this book covers
    3. Get in touch
  2. Section I: Data Modeling in Power BI
  3. Introduction to Data Modeling in Power BI
    1. Understanding the Power BI layers
      1. The data preparation layer (Power Query)
      2. The data model layer
        1. The Data view
        2. The Model view
      3. The data visualization layer
        1. The Report view
      4. How data flows in Power BI
    2. What data modeling means in Power BI
      1. Semantic model
      2. Building an efficient data model in Power BI
      3. Star schema (dimensional modeling) and snowflaking
        1. Transactional modeling versus star schema modeling
        2. Snowflaking
        3. Understanding denormalization
    3. Power BI licensing considerations
      1. Maximum size of an individual dataset
      2. Incremental data load
      3. Hybrid tables
      4. Calculation groups
      5. Shared datasets
      6. Power BI Dataflows
      7. Power BI Datamarts
    4. The iterative data modeling approach
      1. Conducting discovery workshops
      2. Data preparation based on the business logic
      3. Data modeling
      4. Testing the logic
      5. Demonstrating the business logic in basic data visualizations
      6. Thinking like a professional data modeler
    5. Summary
  4. Data Analysis eXpressions and Data Modeling
    1. Understanding virtual tables
      1. Creating calculated tables
      2. Visually displaying the results of virtual tables
        1. Creating calculated tables in Power BI Desktop
        2. Using DAX Studio
      3. Understanding relationships in virtual tables
    2. Time intelligence and data modeling
      1. Detecting valid dates in the date dimension
      2. Period-over-period calculations
        1. Implementing dynamic measure selection with Fields Parameters
      3. Generating the Date dimension with DAX
        1. Marking a Date table as a date table
      4. Creating a time dimension with DAX
    3. Summary
  5. Section II: Data Preparation in Query Editor
  6. Data Preparation in Power Query Editor
    1. Introducing the Power Query M formula language in Power BI
      1. Power Query is CaSe-SeNsItIvE
      2. Queries
      3. Expressions
      4. Values
        1. Primitive values
        2. Structured values
      5. Types
        1. Primitive types
        2. Custom types
    2. Introduction to Power Query Editor
      1. Queries pane
        1. Tables
        2. Custom functions
        3. Query parameters
        4. Constant values
        5. Groups
      2. Query Settings pane
        1. Query Properties
        2. Applied Steps
      3. Data View pane
      4. Status bar
      5. Advanced Editor
    3. Introduction to Power Query features for data modelers
      1. Column quality
      2. Column distribution
      3. Column profile
    4. Understanding query parameters
    5. Understanding custom functions
      1. Recursive functions
    6. Summary
  7. Getting Data from Various Sources
    1. Getting data from common data sources
      1. Folders
      2. CSV/Text/TSV
      3. Excel
        1. Excel file stored in local drive
        2. Excel file stored in SharePoint Online
      4. Power BI datasets
      5. Power BI dataflows
      6. Power BI Datamarts
      7. SQL Server
      8. SQL Server Analysis Services and Azure Analysis Services
        1. SSAS multidimensional/tabular
        2. AAS
      9. OData feed
      10. Dataverse
    2. Understanding data source certification
      1. Bronze
      2. Silver
      3. Gold/Platinum
    3. Working with connection modes
      1. Data Import
        1. Applications
        2. Limitations
      2. DirectQuery
        1. Applications
        2. Limitations
      3. Connect Live
        1. Applications
        2. Limitations
    4. Working with storage modes
    5. Understanding dataset storage modes
    6. Summary
  8. Common Data Preparation Steps
    1. Data type conversion
    2. Splitting a column by delimiter
    3. Merging columns
    4. Adding a custom column
    5. Adding a column from examples
    6. Duplicating a column
    7. Filtering rows
    8. Working with Group By
    9. Appending queries
    10. Merging queries
    11. Duplicating and referencing queries
    12. Replacing values
    13. Extracting numbers from text
    14. Dealing with Date, DateTime, and DateTimeZone
    15. Pivoting tables
    16. Summary
  9. Star Schema Preparation in Power Query Editor
    1. Identifying dimensions and facts
      1. Understanding business requirements
        1. Number of tables in the data source
        2. The linkages between existing tables
        3. Finding the lowest required grain of Date and Time
      2. Defining dimensions and facts
        1. Determining the potential dimensions
        2. Determining the potential facts
    2. Creating Dimension tables
      1. Geography
      2. Sales order
      3. Product
      4. Currency
      5. Customer
      6. Sales Demographic
      7. Date
      8. Time
      9. Creating Date and Time dimensions – Power Query versus DAX
    3. Creating fact tables
    4. Summary
  10. Data Preparation Common Best Practices
    1. Consider loading a proportion of data
    2. Appreciate case sensitivity in Power Query
    3. Be mindful of query folding and its impact on data refresh
      1. Understanding query folding
      2. DirectQuery and Dual storage modes and query folding
      3. Data sources and query folding
      4. Indications for query folding
      5. Query folding best practices
        1. Using SQL statements
        2. Push the data preparation to the source system when possible
        3. Disabling View Native Query does not necessarily mean a transformation step is not folded
    4. Organize queries in the Power Query Editor
    5. Follow data type conversion best practices
      1. Data type conversion can affect data modeling
      2. Avoid having columns with any data type
      3. Include the data type conversion in the step when possible
      4. Consider having only one data type conversion step
    6. Optimize query size
      1. Remove unnecessary columns and rows
      2. Summarization (Group by)
      3. Disabling query load
    7. Use query parameters
      1. Parameterizing connections
      2. Restricting the row counts in development for large tables
    8. Define key columns in queries
    9. Use naming conventions
    10. Summary
  11. Section III: Data Modeling
  12. Data Modeling Components
    1. Data modeling in Power BI Desktop
    2. Understanding tables
      1. Table properties
      2. Featured tables
      3. Calculated tables
    3. Understanding fields
      1. Data types
      2. Custom formatting
      3. Columns
        1. Calculated columns
        2. Grouping and binning columns
        3. Column properties
      4. Hierarchies
      5. Measures
        1. Implicit measures
        2. Explicit measures
        3. Textual measures
    4. Using relationships
      1. Primary keys/foreign keys
      2. Handling composite keys
      3. Relationship cardinalities
        1. One-to-one relationships
        2. One to many relationships
        3. Many to many relationships
      4. Filter propagation behavior
      5. Bidirectional relationships
    5. Summary
  13. Star Schema and Data Modeling Common Best Practices
    1. Dealing with many-to-many relationships
      1. Many-to-many relationships using a bridge table
      2. Hiding the bridge table
    2. Avoiding bidirectional relationships
    3. Dealing with inactive relationships
      1. Reachability via multiple filter paths
      2. Multiple direct relationships between two tables
    4. Using configuration tables
      1. Segmentation
      2. Dynamic color coding with measures
    5. Avoiding calculated columns when possible
    6. Organizing the model
      1. Hiding insignificant model objects
        1. Hiding unused fields and tables
        2. Hiding key columns
        3. Hiding implicit measures
        4. Hiding columns used in hierarchies when possible
      2. Creating measure tables
      3. Using folders
        1. Creating a folder in multiple tables in one go
        2. Placing a measure in various folders
        3. Creating subfolders
    7. Reducing model size by disabling auto date/time
    8. Summary
  14. Section IV: Advanced Data Modeling
  15. Advanced Data Modeling Techniques
    1. Using aggregations
      1. Implementing aggregations for non-DirectQuery data sources
        1. Implementing aggregation at the Date level
        2. Implementing aggregation at the Year and Month level
      2. Using Agg Awareness
        1. Creating an aggregation table
        2. Loading tables in DirectQuery mode
        3. Creating relationships
        4. Setting the aggregation table and its related dimensions’ storage mode
        5. Managing aggregation
        6. Testing the aggregation
      3. Implementing multiple aggregations
      4. Important notes about aggregations
    2. Incremental refresh and hybrid tables
      1. Configuring incremental refresh policy and hybrid table in Power BI Desktop
      2. Testing the incremental refresh
      3. Important notes about incremental refresh and hybrid tables
    3. Parent-Child hierarchies
      1. Identify the depth of the hierarchy
      2. Creating hierarchy levels
    4. Implementing roleplaying dimensions
    5. Using calculation groups
      1. Requirements
      2. Terminology
      3. Implementing calculation groups to handle time intelligence
      4. Testing calculation groups
        1. Fixing the format string issue
      5. DAX functions for calculation groups
    6. Summary
  16. Row-Level and Object-Level Security
    1. What RLS and OLS mean in data modeling
      1. Terminology
        1. Roles
        2. Rules
        3. Enhanced row-level security editor
        4. Validating roles
      2. Assigning members to roles in the Power BI Service
      3. Assigning members to roles in Power BI Report Server
    2. RLS implementation flow
    3. Common RLS implementation approaches
      1. Static RLS implementation
      2. Dynamic RLS implementation
        1. Restricting unauthorized users from accessing data
        2. Managers can access their team members’ data in parent-child hierarchies
        3. Getting the user’s login data from another source
    4. Introduction to OLS
      1. OLS implementation flow
    5. OLS implementation
      1. Validating roles
      2. Assigning members and validating roles in the Power BI Service
      3. RLS and OLS implementation in a single model
      4. Considerations in using RLS and OLS
    6. Summary
  17. Dealing with More Advanced Data Warehousing Concepts in Power BI
    1. Dealing with SCDs
      1. SCD type zero (SCD 0)
      2. SCD type 1 (SCD 1)
      3. SCD type 2 (SCD 2)
    2. Dealing with degenerate dimensions
    3. Summary
  18. Introduction to Dataflows
    1. Introduction to Dataflows
      1. Scenarios for using Dataflows
      2. Dataflow terminology
      3. Create Dataflows
        1. Create new entities
        2. Create linked tables from other Dataflows
        3. Create computed entities
        4. Configure incremental data refresh in Dataflows
      4. Export/import Dataflows
        1. Export Dataflows
        2. Import Dataflows
      5. No-code/low-code experience
      6. Query plans in Dataflows
    2. Summary
  19. DirectQuery Connections to Power BI Datasets and Analysis Services in Composite Models
    1. Introduction to composite models
    2. Enabling DirectQuery for live connections
      1. Allow DirectQuery connections to Power BI datasets in the Power BI service
    3. New terminologies
      1. Chaining
      2. Chain length
    4. RLS in composite models with DirectQuery to Power BI datasets
    5. Setting dataset permissions for contributors (report writers)
    6. Summary
  20. New Options, Features, and DAX Functions
    1. Field parameters
    2. Introduction to Power BI Datamarts
      1. What is a Datamart?
      2. What is Power BI Datamarts?
      3. Demystifying Power BI Datamart misunderstandings
      4. The Datamart Editor
      5. Create a simple Power BI Datamart
        1. Load the data into the Datamart
        2. Build the data model in Datamarts
        3. Analyze Datamarts in the Datamart Editor
        4. Analyze Datamarts in SQL client tools
        5. RLS in Datamarts
    3. New DAX functions
      1. NETWORKDAYS()
      2. EVALUATEANDLOG()
      3. Window functions
        1. PARTITIONBY()
        2. ORDERBY()
        3. INDEX()
        4. OFFSET()
        5. WINDOW()
    4. Summary
  21. Other Books You May Enjoy
  22. Index

Product information

  • Title: Expert Data Modeling with Power BI - Second Edition
  • Author(s): Soheil Bakhshi, Christian Wade
  • Release date: April 2023
  • Publisher(s): Packt Publishing
  • ISBN: 9781803246246