Preface

Microsoft Power BI is a leading business intelligence and analytics platform that supports both self-service data visualization and exploration as well as enterprise BI deployments. Power BI consists of cloud services, mobile applications, a data modeling and report authoring application, and other utilities, including the on-premises data gateway. Additionally, organizations can deploy Power BI reports on-premises via the Power BI Report Server and scale their deployments with Power BI Premium capacity.

This revised and expanded edition provides an end-to-end analysis of the latest Power BI tools and features, from planning a Power BI project to distributing Power BI apps to large groups of users. You’ll be familiarized with all the fundamental concepts and see how Power BI datasets, reports, and dashboards can be designed to deliver insights and rich, interactive experiences. You’ll also become knowledgeable about management and administration topics such as the allocation of Power BI Premium capacities, Azure Active Directory security groups, conditional access policies, and staged deployments of Power BI content. This book will encourage you to take advantage of these powerful features and follow thoughtful, consistent practices in deploying Power BI for your organization.

Who this book is for

This book is intended for business intelligence professionals responsible for either the development of Power BI solutions or the management and administration of a Power BI deployment. BI developers can use this as a reference guide to features and techniques to enhance their solutions. Likewise, BI managers interested in a broad conceptual understanding, as well as processes and practices to inform their delivery of Power BI, will find this a useful resource. Experience in creating content using Power BI Desktop and sharing content on the Power BI service is helpful.

What this book covers

Chapter 1, Planning Power BI Projects, discusses alternative deployment modes for Power BI, team and project roles, and licensing. Additionally, an example project template and its corresponding planning and dataset design processes are described.

Chapter 2, Preparing Data Sources, explains foundational concepts such as query folding, query design, data source preparation and important Power BI Desktop settings.

Chapter 3, Connecting to Sources and Transforming Data with M, depicts the data access layer supporting a Power BI dataset, including data sources and fact and dimension table queries. Concepts of the Power Query M language, such as parameters, are explained and examples of custom M queries involving conditional and dynamic logic are given.

Chapter 4, Designing Import, DirectQuery, and Composite Data Models, reviews the components of the data model layer and design techniques in support of usability, performance, and other objectives. These topics include relationship cross-filtering, custom sort orders, hierarchies, and metadata.

Chapter 5, Developing DAX Measures and Security Roles, covers the implementation of analysis expressions reflecting business definitions and common analysis requirements. Primary DAX functions, concepts, and use cases such as date intelligence, row-level security roles, and performance testing are examined.

Chapter 6, Planning Power BI Reports, describes a report planning process, data visualization practices, and report design fundamentals, including visual selection and filter scopes. In addition, it covers drillthrough report pages, visual interactions, bookmarks, and Live connections.

Chapter 7, Creating and Formatting Visualizations, reviews many standard visuals including slicers, single-number visuals, maps, waterfall charts, scatter charts, Power Platform visuals and Premium visuals, as well as how to format visuals, including the use of tooltips, conditional formatting, custom format strings, and sparklines.

Chapter 8, Applying Advanced Analytics, examines powerful interactive and analytical features, including AI visuals, R and Python visuals, ArcGIS Maps, custom visuals, animation, and the Analytics pane. Additionally, it covers Quick Insights and mobile optimized report pages.

Chapter 9, Designing Dashboards, provides guidance on visual selection, layout, and supporting tiles to drive effective dashboards. Alternative multi-dashboard architectures, such as an organizational dashboard architecture, are reviewed, as well as the configuration of dashboard tiles and mobile optimized dashboards.

Chapter 10, Managing Workspaces and Content, features the role and administration of workspaces in the context of Power BI solutions and staged deployments. Additionally, the Power BI REST API, content management features, and practices are reviewed, including field descriptions and version history.

Chapter 11, Managing the On-Premises Data Gateway, covers top gateway planning considerations, including alternative gateway architectures, workloads, and hardware requirements. Gateway administration processes and tools are described, such as the manage gateways portal, gateway log files, and PowerShell gateway commands.

Chapter 12, Deploying Paginated Reports, explains how to deploy and migrate paginated reports to the Power BI service and compares and contrasts the Power BI Report Server with the Power BI service and provides guidance on deployment topics such as licensing, reference topology, installation, upgrade cycles, and client applications.

Chapter 13, Creating Power BI Apps and Content Distribution, walks through the process of publishing and updating apps for groups of users. Additionally, other common distribution methods are covered, such as the sharing of reports and dashboards, email subscriptions, data-alert-driven emails, and embedding Power BI content in SharePoint Online, Teams, and custom applications.

Chapter 14, Administering Power BI for an Organization, highlights data governance for self-service and corporate BI, Azure Active Directory features such as Conditional Access policies, and the Power BI admin portal. Details are provided about configuring Power BI service tenant settings and the tools available to monitor Power BI activities.

Chapter 15, Building Enterprise BI with Power BI Premium, reviews the capabilities of Power BI Premium and alternative methods for allocating premium capacity. Additionally, administration and optimization topics are discussed as well as lifecycle management using the ALM Toolkit and SQL Server Management Studio.

To get the most out of this book

A Power BI Pro license and access to the Power BI service is necessary to follow many of the topics and examples in this book. The assignment of the Power BI Service Administrator role within the Microsoft 365 admin center, as well as administrative access to an on-premises data gateway, would also be helpful. It’s assumed that readers are familiar with the main user interfaces of Power BI Desktop and have some background in business intelligence or information technology.

The primary data source for the examples in this book was the AdventureWorks data warehouse sample database for SQL Server 2019. A SQL Server 2019 Developer Edition database engine instance was used to host the sample database. For the import mode dataset, an Excel workbook stored the sales plan data. For the DirectQuery dataset, the sales plan data was stored in the sample SQL Server database.

The original AdventureWorksDW2019 was customized by adding a schema and multiple views. The customized version of this database is included in the code bundle for this book as are the Power BI Desktop files and specific queries and scripts used.

Download the example code files

The code bundle for the book is hosted on GitHub at https://github.com/PacktPublishing/-Mastering-Microsoft-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://static.packt-cdn.com/downloads/9781801811484_ColorImages.pdf

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; “Mount the downloaded WebStorm-10*.dmg disk image file as another disk in your system.”

A block of code is set as follows:

let CalculateAge = (BirthDate as date) =>
        Date.Year(CurrentDayQuery) - Date.Year(BirthDate)
in CalculateAge

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are highlighted:

let CalculateAge = (BirthDate as date) =>
        Date.Year(CurrentDayQuery) - Date.Year(BirthDate)
in CalculateAge

Any command-line input or output is written as follows:

Install-Module MicrosoftPowerBIMgmt -Force

Bold: Indicates a new term, an important word, or words that you see on the screen, for example, in menus or dialog boxes, also appear in the text like this. For example: “All workspaces and content within those workspaces are provided a globally unique identifier (GUID).”

Warnings or important notes appear like this.

Tips and tricks appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: Email [e-mail], and mention the book’s title in the subject of your message. If you have questions about any aspect of this book, please email us at [e-mail].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book we would be grateful if you would report this to us. Please visit, http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [e-mail] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit http://authors.packtpub.com.

Share your thoughts

Once you’ve read Mastering Microsoft Power BI, Second Edition, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere? Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

1. Scan the QR code or visit the link below

   

   https://packt.link/free-ebook/9781801811484

2. Submit your proof of purchase
3. That’s it! We’ll send your free PDF and other benefits to your email directly