Expert Data Modeling with Power BI

Book description

Manage and work with business data effectively by learning data modeling techniques and leveraging the latest features of Power BI

Key Features

  • Understand data modeling techniques to get the best out of data using Power BI
  • Define the relationships between data to extract valuable insights
  • Solve a wide variety of business challenges by building optimal data models

Book Description

Microsoft Power BI is one of the most popular business intelligence tools available on the market for desktop and the cloud. This book will be your guide to understanding the ins and outs of data modeling and how to create data models using Power BI confidently. You'll learn how to connect data from multiple sources, understand data, define and manage relationships between data, and shape data models.

In this book, you'll explore how to use data modeling and navigation techniques to define relationships and create a data model before defining new metrics and performing custom calculations using modeling features. As you advance through the chapters, the book will demonstrate how to create full-fledged data models, enabling you to create efficient data models and simpler DAX code with new data modeling features. With the help of examples, you'll discover how you can solve business challenges by building optimal data models and changing your existing data models to meet evolving business requirements. 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.

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 reporting 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
  • Explore best practices for data preparation and data modeling
  • Discover different hierarchies and their common pitfalls
  • Understand complex data models and how to decrease the level of model complexity with different data modeling approaches

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 knowledge of Power BI and Star Schema will help you to understand the concepts covered in this book.

Table of contents

  1. Expert Data Modeling with Power BI
  2. Foreword
  3. Contributors
  4. About the author
  5. About the reviewers
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Download the example code files
    5. Download the color images
    6. Conventions used
    7. Get in touch
    8. Reviews
  7. Section 1: Data Modeling in Power BI
  8. Chapter 1: 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
      3. The data visualization layer
      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
    3. Power BI licensing considerations
      1. Maximum size of individual dataset
      2. Incremental data load
      3. Calculation groups
      4. Shared datasets
      5. Power BI Dataflows
    4. The iterative data modeling approach
      1. Information gathering from the business
      2. Data preparation based on the business logic
      3. Data modeling
      4. Testing the logic
      5. Demonstrating the business logic in a basic data visualization
      6. Thinking like a professional data modeler
    5. Summary
  9. Chapter 2: Data Analysis eXpressions and Data Modeling
    1. Understanding virtual tables
      1. Creating a calculated table
      2. Using virtual tables in a measure – Part 1
      3. Using virtual tables in a measure – Part 2
      4. Visually displaying the results of virtual tables
      5. Relationships in virtual tables
    2. Time intelligence and data modeling
      1. Detecting valid dates in the date dimension
      2. Period-over-period calculations
      3. Generating the date dimension with DAX
      4. Creating a time dimension with DAX
    3. Summary
  10. Section 2: Data Preparation in Query Editor
  11. Chapter 3: Data Preparation in Power Query Editor
    1. Introduction to the Power Query M formula language in Power BI
      1. Power Query is CaSe-SeNsItIvE
      2. Queries
      3. Expressions
      4. Values
      5. Types
    2. Introduction to Power Query Editor
      1. Queries pane
      2. Query Settings pane
      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
  12. Chapter 4: Getting Data from Various Sources
    1. Getting data from common data sources
      1. Folder
      2. CSV/Text/TSV
      3. Excel
      4. Power BI datasets
      5. Power BI dataflows
      6. SQL Server
      7. SQL Server Analysis Services and Azure Analysis Services
      8. OData Feed
    2. Understanding data source certification
      1. Bronze
      2. Silver
      3. Gold/Platinum
    3. Working with connection modes
      1. Data Import
      2. DirectQuery
      3. Connect Live
    4. Working with storage modes
    5. Understanding dataset storage modes
    6. Summary
  13. Chapter 5: Common Data Preparation Steps
    1. Data type conversion
    2. Splitting column by delimiter
    3. Merging columns
    4. Adding a custom column
    5. Adding 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. Summary
  14. Chapter 6: Star Schema Preparation in Power Query Editor
    1. Identifying dimensions and facts
      1. Number of tables in the data source
      2. The linkages between existing tables
      3. Finding the lowest required grain of Date and Time
      4. Defining dimensions and facts
    2. Creating Dimensions 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
  15. Chapter 7: Data Preparation Common Best Practices
    1. General data preparation considerations
      1. Consider loading a proportion of data while connected to the OData data source
      2. Appreciating case sensitivity in Power Query saves you from dealing with issues in data modeling
      3. Be mindful of query folding and its impact on data refresh
      4. Organizing queries in Query Editor
    2. datatype conversion
      1. Data conversion can affect data modeling
      2. Include the datatype conversion in a step when possible
      3. Consider having only one datatype conversion step
    3. Optimizing the size of queries
      1. Removing unnecessary columns and rows
      2. Summarization (Group by)
      3. Disabling query load
    4. Naming conventions
    5. Summary
  16. Section 3: Data Modeling
  17. Chapter 8: 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
      4. Hierarchies
      5. Measures
    4. Using relationships
      1. Primary keys/foreign keys
      2. Handling composite keys
      3. Filter propagation behavior
      4. Bidirectional relationships
    5. Summary
  18. Chapter 9: 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. Being cautious with 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 conditional formatting with measures
    5. Avoiding calculated columns when possible
    6. Organizing the model
      1. Hiding insignificant model objects
      2. Creating measure tables
      3. Using folders
    7. Reducing model size by disabling auto date/time
    8. Summary
  19. Section 4: Advanced Data Modeling
  20. Chapter 10: Advanced Data Modeling Techniques
    1. Using aggregations
      1. Implementing aggregations for non-DirectQuery data sources
      2. Using the Manage Aggregations feature
    2. Incremental refresh
      1. Configuring incremental refresh in Power BI Desktop
      2. Testing the incremental refresh
    3. Understanding Parent-Child hierarchies
      1. Identifying 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
      5. DAX functions for calculation groups
    6. Summary
  21. Chapter 11: Row-Level Security
    1. What RLS means in data modeling
      1. What RLS is not
      2. RLS terminologies
      3. Assigning members to roles in the Power BI service
      4. Assigning members to roles in Power BI Report Server
    2. RLS implementation flow
    3. Common RLS implementation approaches
      1. Implementing static RLS
      2. Implementing dynamic RLS
    4. Summary
  22. Chapter 12: Extra Options and Features Available for Data Modeling
    1. Dealing with SCDs
      1. SCD type zero (SCD 0)
      2. SCD type 1 (SCD 1)
      3. SCD type 2 (SCD 2)
    2. Introduction to OLS
      1. Implementing OLS
      2. Validating roles
      3. Assigning members to roles in the Power BI service
      4. Validating roles in the Power BI service
    3. Introduction to dataflows
      1. Scenarios for using dataflows
      2. Dataflow terminologies
      3. Creating dataflows
    4. Introduction to composite models
      1. New terminologies
    5. Summary
    6. Why subscribe?
  23. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Expert Data Modeling with Power BI
  • Author(s): Soheil Bakhshi, Christian Wade
  • Release date: June 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781800205697