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