0%

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
    2. The data preparation layer (Power Query)
    3. The data model layer
    4. The data visualization layer
    5. How data flows in Power BI
    6. What data modeling means in Power BI
    7. Semantic model
    8. Building an efficient data model in Power BI
    9. Star schema (dimensional modeling) and snowflaking
    10. Power BI licensing considerations
    11. Maximum size of individual dataset
    12. Incremental data load
    13. Calculation groups
    14. Shared datasets
    15. Power BI Dataflows
    16. The iterative data modeling approach
    17. Information gathering from the business
    18. Data preparation based on the business logic
    19. Data modeling
    20. Testing the logic
    21. Demonstrating the business logic in a basic data visualization
    22. Thinking like a professional data modeler
    23. Summary
  9. Chapter 2: Data Analysis eXpressions and Data Modeling
    1. Understanding virtual tables
    2. Creating a calculated table
    3. Using virtual tables in a measure – Part 1
    4. Using virtual tables in a measure – Part 2
    5. Visually displaying the results of virtual tables
    6. Relationships in virtual tables
    7. Time intelligence and data modeling
    8. Detecting valid dates in the date dimension
    9. Period-over-period calculations
    10. Generating the date dimension with DAX
    11. Creating a time dimension with DAX
    12. 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
    2. Power Query is CaSe-SeNsItIvE
    3. Queries
    4. Expressions
    5. Values
    6. Types
    7. Introduction to Power Query Editor
    8. Queries pane
    9. Query Settings pane
    10. Data View pane
    11. Status bar
    12. Advanced Editor
    13. Introduction to Power Query features for data modelers
    14. Column quality
    15. Column distribution
    16. Column profile
    17. Understanding query parameters
    18. Understanding custom functions
    19. Recursive functions
    20. Summary
  12. Chapter 4: Getting Data from Various Sources
    1. Getting data from common data sources
    2. Folder
    3. CSV/Text/TSV
    4. Excel
    5. Power BI datasets
    6. Power BI dataflows
    7. SQL Server
    8. SQL Server Analysis Services and Azure Analysis Services
    9. OData Feed
    10. Understanding data source certification
    11. Bronze
    12. Silver
    13. Gold/Platinum
    14. Working with connection modes
    15. Data Import
    16. DirectQuery
    17. Connect Live
    18. Working with storage modes
    19. Understanding dataset storage modes
    20. 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
    2. Number of tables in the data source
    3. The linkages between existing tables
    4. Finding the lowest required grain of Date and Time
    5. Defining dimensions and facts
    6. Creating Dimensions tables
    7. Geography
    8. Sales order
    9. Product
    10. Currency
    11. Customer
    12. Sales Demographic
    13. Date
    14. Time
    15. Creating Date and Time dimensions – Power Query versus DAX
    16. Creating fact tables
    17. Summary
  15. Chapter 7: Data Preparation Common Best Practices
    1. General data preparation considerations
    2. Consider loading a proportion of data while connected to the OData data source
    3. Appreciating case sensitivity in Power Query saves you from dealing with issues in data modeling
    4. Be mindful of query folding and its impact on data refresh
    5. Organizing queries in Query Editor
    6. datatype conversion
    7. Data conversion can affect data modeling
    8. Include the datatype conversion in a step when possible
    9. Consider having only one datatype conversion step
    10. Optimizing the size of queries
    11. Removing unnecessary columns and rows
    12. Summarization (Group by)
    13. Disabling query load
    14. Naming conventions
    15. Summary
  16. Section 3: Data Modeling
  17. Chapter 8: Data Modeling Components
    1. Data modeling in Power BI Desktop
    2. Understanding tables
    3. Table properties
    4. Featured tables
    5. Calculated tables
    6. Understanding fields
    7. Data types
    8. Custom formatting
    9. Columns
    10. Hierarchies
    11. Measures
    12. Using relationships
    13. Primary keys/foreign keys
    14. Handling composite keys
    15. Filter propagation behavior
    16. Bidirectional relationships
    17. Summary
  18. Chapter 9: Star Schema and Data Modeling Common Best Practices
    1. Dealing with many-to-many relationships
    2. Many-to-many relationships using a bridge table
    3. Hiding the bridge table
    4. Being cautious with bidirectional relationships
    5. Dealing with inactive relationships
    6. Reachability via multiple filter paths
    7. Multiple direct relationships between two tables
    8. Using configuration tables
    9. Segmentation
    10. Dynamic conditional formatting with measures
    11. Avoiding calculated columns when possible
    12. Organizing the model
    13. Hiding insignificant model objects
    14. Creating measure tables
    15. Using folders
    16. Reducing model size by disabling auto date/time
    17. Summary
  19. Section 4: Advanced Data Modeling
  20. Chapter 10: Advanced Data Modeling Techniques
    1. Using aggregations
    2. Implementing aggregations for non-DirectQuery data sources
    3. Using the Manage Aggregations feature
    4. Incremental refresh
    5. Configuring incremental refresh in Power BI Desktop
    6. Testing the incremental refresh
    7. Understanding Parent-Child hierarchies
    8. Identifying the depth of the hierarchy
    9. Creating hierarchy levels
    10. Implementing roleplaying dimensions
    11. Using calculation groups
    12. Requirements
    13. Terminology
    14. Implementing calculation groups to handle time intelligence
    15. Testing calculation groups
    16. DAX functions for calculation groups
    17. Summary
  21. Chapter 11: Row-Level Security
    1. What RLS means in data modeling
    2. What RLS is not
    3. RLS terminologies
    4. Assigning members to roles in the Power BI service
    5. Assigning members to roles in Power BI Report Server
    6. RLS implementation flow
    7. Common RLS implementation approaches
    8. Implementing static RLS
    9. Implementing dynamic RLS
    10. Summary
  22. Chapter 12: Extra Options and Features Available for Data Modeling
    1. Dealing with SCDs
    2. SCD type zero (SCD 0)
    3. SCD type 1 (SCD 1)
    4. SCD type 2 (SCD 2)
    5. Introduction to OLS
    6. Implementing OLS
    7. Validating roles
    8. Assigning members to roles in the Power BI service
    9. Validating roles in the Power BI service
    10. Introduction to dataflows
    11. Scenarios for using dataflows
    12. Dataflow terminologies
    13. Creating dataflows
    14. Introduction to composite models
    15. New terminologies
    16. Summary
    17. Why subscribe?
  23. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think
3.230.147.225