Expert Data Modeling with Power BI E-Book

Soheil Bakhshi

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 495

Veröffentlichungsjahr: 2021

Beschreibung

This book is a comprehensive 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 to gain deep and detailed insights about your organization.

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.

Leseprobe

Expert Data Modeling with Power BI

Get the best out of Power BI by building optimized data models for reporting and business needs

Soheil Bakhshi

BIRMINGHAM—MUMBAI

Expert Data Modeling with Power BI

Copyright © 2021 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kunal Parikh

Publishing Product Manager: Sunith Shetty

Senior Editor: Mohammed Yusuf Imaratwale

Content Development Editor: Nazia Shaikh

Technical Editor: Devanshi Deepak Ayare

Copy Editor: Safis Editing

Project Coordinator: Aishwarya Mohan

Proofreader: Safis Editing

Indexer: Rekha Nair

Production Designer: Shankar Kalbhor

First published: June 2021

Production reference: 1120521

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80020-569-7

www.packt.com

I dedicate this book to my lovely wife, Elica Mehr, and our precious daughter, Avina.

Without their unconditional support, this matter would not have been possible.

I also owe this success to my parents, who always believed in me and encouraged me to follow my dreams.

Foreword

I am happy to know that Soheil, who I have seen grow as an MVP with expert knowledge of Power BI, has authored this hands-on book that covers a range of topics. Learning by example is something every committed student wants to do. The effort needed to go through online documentation and extract useful information can be prohibitive. Many students can become frustrated during the process and leave the task unfinished because it is easy for any beginner to lose sight of the learning task while trying to find good, real-world, example-based content.

Soheil's book includes many resources from beginner to expert level for Power BI. Power BI is the leading platform and tool for enterprise and self-service BI. It is a joy to use and is very flexible. This book is appealing both to beginners and to experts who build systems for thousands of users. I'm happy to recommend Soheil's book to any enthusiast, regardless of their level of expertise. His book navigates the world of Power BI in a very engaging way. It starts from the basic building blocks and elaborates on many aspects of data modeling, including star schema, managing bi-directional relationships, many-to-many relationships, calculated tables, preparing the data structure in Power Query to achieve a star schema design, RLS, OLS, and composite models. Any student will appreciate the hands-on approach of this book. Every concept is explained with examples.

Christian Wade

Principal Program Manager, Microsoft

Contributors

About the author

Soheil Bakhshi is the founder of Data Vizioner and is a sought-after BI consultant. Having worked in data and analytics for more than 20 years, Soheil's experience lies in Microsoft BI, data warehousing, and the Power BI platform. He possesses MSCE and MCSA certifications and is a Microsoft MVP (Most Valuable Professional). He has a passion for sharing knowledge via his website www.biinsight.com and for speaking at conferences and Power BI community events locally and globally. In following his desire for simplicity and efficiency, he has been behind Power BI community tools and commercial products such as Power BI Exporter and Power BI Documenter.

About the reviewers

Felipe Vilela worked for many years with system development and then started working in BI/data warehousing, mainly using MicroStrategy, more than 8 years ago. He worked with many companies in Brazil and the United States, implementing MicroStrategy projects, customizing, and administrating MicroStrategy. He taught BI/data warehousing and MicroStrategy to many companies using the company's, personal, and MicroStrategy's official courses. He also has a blog (www.vilelamstr.com). He was one of the MicroStrategy mobile app developers at MicroStrategy World 2016 and 2017. He has more than 30 MicroStrategy certifications, including the MCEP certification.

Nikita Barsukov is an experienced data scientist, focusing on delivering end-to-end analytical solutions. After growing up in Ukraine and studying in Finland and Sweden, he started his career in IT as a software developer in testing, only to realize his true passion lies in data science and building analytics tools that bring others joy and enhance data discovery. Nikita is currently employed by Microsoft; he is a part of a team of like-minded colleagues that develop analytics solutions for Power Platform and Dynamics 365. Outside of work, Nikita listens to podcasts and audiobooks; plays board games with friends, and occasionally even with himself; and enjoys a good run, craft beer, and a great book. Nikita lives in Copenhagen with his wife and three kids.

Ana Maria is a BI consultant and trainer as well as a Microsoft Data Platform MVP, Microsoft Partner in Power BI, and LinkedIn Learning Trainer. She has more than 25 years of industry experience ranging from the development of desktop solutions in FoxPro in the 1990s to data analysis and BI consulting and training. She has a degree in economic cybernetics from Moscow's State University of Management, in the former USSR, and also a master's degree in BI from the University of Alcala, in Spain. She is focused on the data world in Microsoft technology, modeling, and data analysis with SQL Server BI, Excel BI, Azure Machine Learning, R, and Power BI. You can find her at technical events and forums, as a speaker, organizer, or attendee.

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

Other Books You May Enjoy

Section 1: Data Modeling in Power BI

In this section, we quickly introduce data modeling in Power BI from a general point of view. We assume you know what Power Query is, what DAX is, and that you know the basic concepts of the star schema. In this section, you will learn about virtual tables and time intelligence functionalities in DAX and how you can implement a powerful model with real-world scenarios.

This section comprises the following chapters:

Chapter 1, Introduction to Data Modeling in Power BIChapter 2, Data Analysis eXpressions and Data Modeling