Expert data modeling with Power BI : get the best out of Power BI by building optimized data models for reporting and business needs

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 Introduction to data modelling in Power BI Data Analysis eXpressions and Data Modeling Data Preparation in Power Query Editor Getting data from various sources Common data preparation steps Star Schema preparation in Power Query Editor Data preparation common best practices Data Modeling Components Star Shema and Data Modeling Common Best Practices Advanced Data Modeling Techniques Row Level Security Extra Options and Features Available for Data Modeling Cover Title Page Copyright and Credits Dedicated Foreword Contributors Table of Contents Preface Section 1: Data Modeling in Power BI Chapter 1: Introduction to Data Modeling in Power BI Understanding the Power BI layers The data preparation layer (Power Query) The data model layer The data visualization layer 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 Power BI licensing considerations Maximum size of individual dataset Incremental data load Calculation groups Shared datasets Power BI Dataflows The iterative data modeling approach Information gathering from the business Data preparation based on the business logic Data modeling Testing the logic Demonstrating the business logic in a basic data visualization Thinking like a professional data modeler Summary Chapter 2: Data Analysis eXpressions and Data Modeling Understanding virtual tables Creating a calculated table Using virtual tables in a measure – Part 1 Using virtual tables in a measure – Part 2 Visually displaying the results of virtual tables Relationships in virtual tables Time intelligence and data modeling Detecting valid dates in the date dimension Period-over-period calculations Generating the date dimension with DAX Creating a time dimension with DAX Summary Section 2: Data Preparation in Query Editor Chapter 3: Data Preparation in Power Query Editor Introduction to the Power Query M formula language in Power BI Power Query is CaSe-SeNsItIvE Queries Expressions Values Types Introduction to Power Query Editor Queries pane Query Settings pane 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 Chapter 4: Getting Data from Various Sources Getting data from common data sources Folder CSV/Text/TSV Excel Power BI datasets Power BI dataflows SQL Server SQL Server Analysis Services and Azure Analysis Services OData Feed Understanding data source certification Bronze Silver Gold/Platinum Working with connection modes Data Import DirectQuery Connect Live Working with storage modes Understanding dataset storage modes Summary Chapter 5: Common Data Preparation Steps Data type conversion Splitting column by delimiter Merging columns Adding a custom column Adding 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 Summary Chapter 6: Star Schema Preparation in Power Query Editor Identifying dimensions and facts 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 Creating Dimensions tables Geography Sales order Product Currency Customer Sales Demographic Date Time Creating Date and Time dimensions – Power Query versus DAX Creating fact tables Summary Chapter 7: Data Preparation Common Best Practices General data preparation considerations Consider loading a proportion of data while connected to the OData data source Appreciating case sensitivity in Power Query saves you from dealing with issues in data modeling Be mindful of query folding and its impact on data refresh Organizing queries in Query Editor datatype conversion Data conversion can affect data modeling Include the datatype conversion in a step when possible Consider having only one datatype conversion step Optimizing the size of queries Removing unnecessary columns and rows Summarization (Group by) Disabling query load Naming conventions Summary Section 3: Data Modeling Chapter 8: Data Modeling Components Data modeling in Power BI Desktop Understanding tables Table properties Featured tables Calculated tables Understanding fields Data types Custom formatting Columns Hierarchies Measures Using relationships Primary keys/foreign keys Handling composite keys Filter propagation behavior Bidirectional relationships Summary Chapter 9: 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 Being cautious with bidirectional relationships Dealing with inactive relationships Reachability via multiple filter paths Multiple direct relationships between two tables Using configuration tables Segmentation Dynamic conditional formatting with measures Avoiding calculated columns when possible Organizing the model Hiding insignificant model objects Creating measure tables Using folders Reducing model size by disabling auto date/time Summary Section 4: Advanced Data Modeling Chapter 10: Advanced Data Modeling Techniques Using aggregations Implementing aggregations for non-DirectQuery data sources Using the Manage Aggregations feature Incremental refresh Configuring incremental refresh in Power BI Desktop Testing the incremental refresh Understanding Parent-Child hierarchies Identifying 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 DAX functions for calculation groups Summary Chapter 11: Row-Level Security What RLS means in data modeling What RLS is not RLS terminologies 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 Implementing static RLS Implementing dynamic RLS Summary Chapter 12: Extra Options and Features Available for Data Modeling Dealing with SCDs SCD type zero (SCD 0) SCD type 1 (SCD 1) SCD type 2 (SCD 2) Introduction to OLS Implementing OLS Validating roles Assigning members to roles in the Power BI service Validating roles in the Power BI service Introduction to dataflows Scenarios for using dataflows Dataflow terminologies Creating dataflows Introduction to composite models New terminologies Summary About Packt Other Books You May Enjoy Index **Manage and work with business data effectively by learning data modeling techniques and leveraging the latest features of Power BI** * 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 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. * 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 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. 1. Introduction to data modelling in Power BI 2. Data Analysis eXpressions and Data Modeling 3. Data Preparation in Power Query Editor 4. Getting data from various sources 5. Common data preparation steps 6. Star Schema preparation in Power Query Editor 7. Data preparation common best practices 8. Data Modeling Components 9. Star Shema and Data Modeling Common Best Practices 10. Advanced Data Modeling Techniques 11. Row Level Security 12. Extra Options and Features Available for Data Modeling 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. -- page 4 of cover This book shows you how to effectively use Power BI, covering everything from Fact tables and Dimension tables to different data modeling techniques. With the help of real-world scenarios, you'll be able to identify when, why, and how to prepare data to support an efficient Star Schema to satisfy business requirements.