What this book covers

Chapter 1, Introduction to Data Modeling in Power BI, briefly describes different functionalities of Power BI and why data modeling is important. This chapter also reveals some important notes to be considered around Power BI licensing, which could potentially affect your data model. This chapter introduces an iterative data modeling approach, which guarantees an agile Power BI implementation.

Chapter 2, Data Analysis eXpressions and Data Modeling, does not discuss DAX in detail, as in Part 3 and 4 of this book DAX is heavily used to solve different data modeling challenges. Therefore, we’ll only focus on the DAX functionalities that are harder to understand and are very relevant to data modeling. This chapter starts with a quick introduction to DAX, then we jump straight into virtual tables and time intelligence functionalities and their applications in real-world scenarios.

Chapter 3, Data Preparation in Power Query Editor, quickly explains the components of Power Query and their application. It expresses the importance of creating query parameters and user-defined functions along with real-world use cases and scenarios to demonstrate how powerful they are in building much more flexible and maintainable models.

Chapter 4, Getting Data from Various Sources, explains how to get data from different data sources that are more commonly used in Power BI. Then, the importance of data source certification is explained, which helps you set your expectations on the type of data you’re going to deal with. This is especially helpful in estimating data modeling efforts. Different connection modes are also explained in this chapter.

Chapter 5, Common Data Preparation Steps, explains common data preparation steps along with real-world hands-on scenarios. A combination of what you have learned so far in this book and the steps discussed in this chapter gives you a strong foundation to go on to the next chapters and build your data models more efficiently.

By learning these functionalities, you can deal with a lot of different scenarios to implement different data models.

Chapter 6, Star Schema Preparation in Power Query Editor, explains how to prepare your queries based on the star schema data modeling approach with real-life scenarios. The Power Query M language will be heavily used in this chapter, so you will learn how to deal with real-world challenges along the way. As you have already learned common data preparation steps in the previous chapter, the majority of Power Query scenarios explained in this chapter will be easier to implement. You’ll also learn how to build dimension tables and fact tables, and how to denormalize your queries when needed.

Chapter 7, Data Preparation Common Best Practices, explains common best practices in data preparation. Following these practices will help you build more efficient data models that are easier to maintain and more flexible to make changes to. By following these practices, you can also avoid common mistakes, which can make your life much easier.

Chapter 8, Data Modeling Components, explains data modeling components from a Power BI perspective, along with real file examples. In this chapter, we heavily use DAX when applicable, so having a basic understanding of DAX is essential. We also have a complete star schema model in Power BI. The concept of config tables is covered, which unlocks a lot of possibilities for handling more complex business logic in the data model. The chapter ends with data modeling naming conventions.

Chapter 9, Star Schema and Data Modeling Common Best Practices, explains common data modeling best practices to help you make better decisions while building your data model to prevent facing some known issues down the road. For instance, dealing with data type issues in key columns that are used in relationships is somewhat time-consuming to identify, but it’s very easy to prevent. So, knowing data modeling best practices helps you save a lot of maintenance time and consequently saves you money.

Chapter 10, Advanced Data Modeling Techniques, explains special modeling techniques that solve special business requirements. A good data modeler is one who is always open to new challenges. You may face some of the advanced business requirements discussed in this chapter or you may face something different but similar. The message we want to send in this chapter is to think freely when dealing with new business challenges and try to be innovative to get the best results.

Chapter 11, Row-Level and Object-Level Security, explains how to implement Row-Level Security (RLS) and Object-Level Security (OLS) in a Power BI data model. Dealing with RLS and OLS can be complex, and knowing how to deal with different situations requires deep knowledge of data modeling and filter propagation concepts. Our aim in this chapter is to transfer that knowledge to you so you can design and implement high-performing and low-maintenance data models.

Chapter 12, Dealing with More Advanced Data Warehousing Concepts in Power BI, explains two concepts coming from data warehousing, Slowly Changing Dimensions (SCDs) and degenerate dimensions. This chapter also demonstrates when and how we can implement these concepts in a Power BI data model.

Chapter 13, Introduction to Dataflows, briefly introduces Dataflows, another available feature in Power BI. This chapter is designed to cover the basics and help you to build robust building blocks for your learning journey. This chapter also explains how to export/import Dataflows, and how the no-code/low-code experience and query plan work in Dataflows.

Chapter 14, DirectQuery Connections to Power BI Datasets and Analysis Services in Composite Models, introduces new terminologies related to composite models and discusses how to resolve more complex scenarios with fewer issues. This chapter also covers RLS challenges in composite models with DirectQuery connections to either a Power BI dataset or Azure Analysis Services and explains the challenges around setting dataset permissions for contributors after RLS is created.

Chapter 15, New Options, Features, and DAX Functions, introduces field parameters, Power BI Datamarts, and some new DAX functions. One of the main features is Field Parameters, which allow developers to create dynamic reports where users can switch between different fields using a slicer. Additionally, the chapter introduces Power BI Datamarts, a subject-oriented subset of a data warehouse focusing on a particular business unit, department, subject area, or business functionality. This chapter also explains how RLS Power BI Datamarts are implemented. The chapter also highlights new DAX functions, including NETWORKDAYS() and EVALUATEANDLOG(), and window functions such as INDEX(), OFFSET(), and WINDOW().

To get the most out of this book

You will need to download and install the latest version of Power BI Desktop. All expressions have been tested in the March 2023 release of Power BI Desktop and should work in the later versions released on later dates. Power BI Desktop is a free application that lets you connect to various data sources, transform and model data, create visualizations, and share your work by publishing to the Power BI service. You can get Power BI Desktop in two ways:

• As a Windows app from the Microsoft Store
• As an executable desktop application downloadable directly from the Power BI website

In addition to Power BI Desktop, you will need to install and use DAX Studio, Tabular Editor, SQL Server Management Studio SSMS, and DAX Debug Output. All of these applications are free of charge. Here are the minimum requirements for the required applications to work properly:

Power BI Desktop:

• Hardware: A Personal Computer (PC) with at least a 1-Gigahertz (GHz) 64-bit (x64) processor, 2 GB of RAM, and 1 GB of available disk space. At least 1440x900 or 1600x900 (16:9) resolution is required. Lower resolutions such as 1024x768 or 1280x800 aren’t supported because some controls (such as closing the startup screens) display beyond those resolutions. For optimal performance, having 8 GB of RAM and a 64-bit processor is recommended.
• Software: Install the latest version of Power BI Desktop from the Microsoft Store or the Power BI website on a Windows machine with Windows 8.1 or Windows Server 2012 R2 or later. You need to have .NET Framework 4.6.2 or higher and the Microsoft Edge browser (Internet Explorer is no longer supported). Power BI Desktop automatically installs WebView2. It is recommended to use a client version of Windows, such as Windows 10, instead of Windows Server. Power BI Desktop doesn’t support Internet Explorer Enhanced Security Configuration.
• Security: You need to have a valid Power BI account to sign in to Power BI Desktop and publish reports. You also need to have permission to access the data sources and services that you want to use in your reports.

The following are download links for Power BI Desktop:

• Power BI Desktop from the Microsoft Store: https://aka.ms/pbidesktopstore
• Power BI Desktop from the web: https://www.microsoft.com/en-us/download/details.aspx?id=58494

You can sign up for a Power BI Service as an individual. Read more here: https://learn.microsoft.com/en-us/power-bi/fundamentals/service-self-service-signup-for-power-bi?WT.mc_id=DP-MVP-5003466.

Since January 31, 2021, Power BI Desktop is no longer supported on Windows 7.

Tabular Editor:

Tabular Editor is a powerful tool to create and manage tabular models in Microsoft Analysis Services and Power BI. The vendor, Kapacity, currently offers the tool in two flavours, Tabular Editor 2.x, which is the free version, and Tabular Editor 3.x, which is the commercial paid version. In this book, we use the free version. The following are the minimum requirements for running Tabular Editor:

• Hardware: A Windows PC with at least 4 GB of RAM and 100 MB of free disk space.
• Software: Windows 7, Windows 8, Windows 10 (recommended), Windows Server 2016, Windows Server 2019 or newer, and .NET Framework 4.6 or later. Microsoft Analysis Services client libraries (installed by default with SQL Server Management Studio or Visual Studio) are also required.
• Security: Administrator rights on the PC where Tabular Editor is installed or run. Read and write permissions on the tabular model that you want to edit are also required.

You can download Tabular Editor 2.x from here: https://github.com/TabularEditor/TabularEditor/releases/.

DAX Studio:

DAX Studio is a free, powerful tool from SQLBI for analyzing and optimizing DAX queries. It can connect to various data sources, such as Power BI, SQL Server Analysis Services Tabular Models, Azure Analysis Services, and Power Pivot. To use DAX Studio effectively, you need to meet some hardware, software, and security requirements as follows:

• Hardware: A Windows PC with a minimum of 4 GB of RAM and 100 MB of disk space.
• Software: Windows 7, Windows 8, Windows 10 (recommended), .NET Framework 4.7.1 or later, and Office primary interop assemblies are required. Office 2016 and later should have this enabled by default.
• Security: DAX Studio requires administrator privileges to install and update unless its portable version is used. It also requires access to the data sources that you want to connect to.

You can download DAX Studio from here: https://daxstudio.org/downloads/.

SQL Server Management Studio (SSMS):

SQL Server Management Studio (SSMS) is a Graphical User Interface (GUI) tool for developing, managing, and administrating SQL Server databases and servers. You can use SSMS to create and modify database objects, run queries and scripts, monitor performance and activity, configure security and backup options, and more. SSMS is a free and downloadable offering from Microsoft that works with any edition of SQL Server. SSMS can also be used to run DAX queries when connected to an instance of SQL Server Analysis Services (SSAS) Tabular Models, Azure Analysis Services (AAS), or a premium Power BI dataset via XMLA endpoints. The following are the prerequisites to run SSMS efficiently throughout this book:

• Hardware: A Windows PC with at least a 1.8 GHz or faster x86 (Intel, AMD) processor (dual-core or better recommended), 2 GB of RAM is required (4 GB of RAM recommended), and 2.5 GB is the minimum if run on a Virtual Machine (VM). It also requires a minimum of 2 GB of available space (up to 10 GB).
• Software: Windows 10 (64-bit) version 1607 (10.0.14393) or later, Windows 11 (64-bit), Windows Server 2016 (64-bit), Windows Server 2019 (64-bit), or Windows Server 2022 (64-bit).
• Security: SSMS requires administrator privileges to install.

SSMS is available to download from here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16&WT.mc_id=DP-MVP-5003466.

DAX Debug Output:

DAX Debug Output is a free community tool that allows developers to view the results of DAX expressions run in Power BI Desktop. This can help troubleshoot and optimize complex DAX formulas and measures. This tool is currently in public preview and the developer has not released much information about its system requirements yet. It currently works with the Power BI Desktop August 2022 release or later. DAX Debug Output is available to download from here: https://github.com/pbidax/DAXDebugOutput/releases/tag/v0.1.0-beta.

If you are using the digital version of this book, we advise that you type the code yourself or access the code via the GitHub repository (link available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Download the example code files

The code bundle for the book is hosted on GitHub at https://github.com/PacktPublishing/Expert-Data-Modeling-with-Power-BI-Second-Edition. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://packt.link/XAVMa.

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. For example: “Open the Adventure Works, Internet Sales.pbix file.”

A block of code is set as follows:

Sequential Numbers =
SELECTCOLUMNS(
    GENERATESERIES(1, 20, 1)
    , "ID"
    , [Value]
    )

Bold: Indicates a new term, an important word, or words that you see on the screen. For instance, words in menus or dialog boxes appear in the text like this. For example: “Click New table from the Modeling tab.”

Warnings or important notes appear like this.

Tips and tricks appear like this.