Mastering Microsoft Power BI – Second Edition E-Book

Gregory Deckler

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 909

Veröffentlichungsjahr: 2022

Beschreibung

Mastering Microsoft Power BI, Second Edition, provides an advanced understanding of Power BI to get the most out of your data and maximize business intelligence. This updated edition walks through each essential phase and component of Power BI, and explores the latest, most impactful Power BI features.

Using best practices and working code examples, you will connect to data sources, shape and enhance source data, and develop analytical data models. You will also learn how to apply custom visuals, implement new DAX commands and paginated SSRS-style reports, manage application workspaces and metadata, and understand how content can be staged and securely distributed via Power BI apps. Furthermore, you will explore top report and interactive dashboard design practices using features such as bookmarks and the Power KPI visual, alongside the latest capabilities of Power BI mobile applications and self-service BI techniques. Additionally, important management and administration topics are covered, including application lifecycle management via Power BI pipelines, the on-premises data gateway, and Power BI Premium capacity.

By the end of this Power BI book, you will be confident in creating sustainable and impactful charts, tables, reports, and dashboards with any kind of data using Microsoft Power BI.

Leseprobe

Mastering Microsoft Power BI

Second Edition

Expert techniques to create interactive insights for effective data analytics and business intelligence

Greg Deckler

Brett Powell

BIRMINGHAM—MUMBAI

Mastering Microsoft Power BI

Second Edition

Copyright © 2022 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 authors, 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.

Senior Publishing Product Manager: Devika Battike

Acquisition Editor – Peer Reviews: Saby Dsilva

Project Editor: Amisha Vathare

Content Development Editor: Rebecca Robinson

Copy Editor: Safis Editing

Technical Editor: Aditya Sawant

Proofreader: Safis Editing

Indexer: Rekha Nair

Presentation Designer: Ganesh Bhadwalkar

First published: March 2018

Second edition: June 2022

Production reference: 3141022

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80181-148-4

www.packt.com

Foreword

Microsoft Power BI is the best analytics platform that empowers every employee to ask and explore questions, uncover insights, and gain intelligence through modern business intelligence tools. 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-premise via the Power BI Report Server and scale their deployments with Power BI Premium capacity.

This book provides an end-to-end analysis of Power BI tools and features, from planning a Power BI project to distributing Power BI apps to large groups of users. You will 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 inspire you to take advantage of these powerful features and follow consistent practices in deploying Power BI for your organization. I highly recommend this book to any new or existing Power BI users who want to learn what features and functionalities Power BI provides, as well as how they can quickly take advantage of their data.

- Leon Gordon, Thought Leader at Forbes Technical Council, Microsoft Data Platform MVP and Partner at Pomerol Partners

Contributors

About the authors

Greg Deckler is a Vice President at Fusion Alliance and has been a consulting services professional for over 27 years. Recognized as an expert in Power BI, Greg is a six-time Microsoft MVP for the Data Platform and an active member of the Power BI Community site with over 5,000 solutions authored and hundreds of Quick Measures Gallery submissions. Greg founded the Columbus Azure ML and Power BI User Group in Columbus, OH in 2016 and holds regular monthly meetings. Greg is also the author of numerous external tools for Power BI available for free on his gdeckler GitHub repository.

I would like to deeply thank my family, son Rocket, my extended Fusion Alliance family, the dynamic and vibrant Power BI community as a whole and especially all of the Super Users as well as my user group members. A special thanks to Brett Powell for all of his support and guidance on this book as well as Power BI Cookbook, 2nd Edition.

Brett Powell is a Microsoft business intelligence consultant and author. Brett has over 12 years of business intelligence experience across many industries as a developer, architect, and administrator. Although most known for his Insights Quest blog and the first editions of the Microsoft Power BI Cookbook and Mastering Microsoft Power BI, Brett primarily focuses on the needs of clients and project engagements though Frontline Analytics LLC, a Microsoft BI consultancy and Power BI partner.

I give all glory and praise to my Lord and Savior Jesus Christ. I was alone and lost in sin, but Christ has forgiven me and granted me a new peace and purpose for my life.

About the reviewer

Eugene Meidinger has been working in business intelligence for over 8 years now, focusing heavily on business reporting. He speaks regularly at technical conferences, including Pass Summit and SQLBits. Eugene started his own company in 2018, SQLGene Training, and now produces training videos for Pluralsight and consults on Power BI.

I would like to thank my husband Miles for supporting me during the nights and weekends I worked on reviewing this book.

Join our community on Discord

Join our community’s Discord space for discussions with the author and other readers: https://discord.gg/q6BPbHEPXp

Contents

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Planning Power BI Projects

Power BI deployment modes

Corporate BI

Self-Service Visualization

Self-Service BI

Choosing a deployment mode

Project discovery and ingestion

Sample Power BI project template

Sample template – Adventure Works BI

Power BI project roles

Dataset designer

Report authors

Power BI administrator

Project role collaboration

Power BI licenses

Shared capacity

Free

Pro

Dedicated capacity

Premium

Premium Per User

Embedded

Power BI license scenarios

Dataset design

Data warehouse bus matrix

Dataset design process

Select the business process

Declare the grain

Identify the dimensions

Define the facts

Data profiling

Data profiling with Power BI Desktop

Dataset planning

Data transformations

Import, DirectQuery, Live, and Composite datasets

Import mode

DirectQuery/Live mode

Composite mode

Sample project analysis

Summary

Preparing Data Sources

Query folding

Partial query folding

Query design per dataset mode

Import mode dataset queries

DirectQuery dataset queries

DirectQuery report execution

Composite datasets

Table storage modes

Data sources

Authentication

Data source settings

Privacy levels

Power BI as a data source

Power BI Desktop options

Global options

CURRENT FILE options

SQL views

SQL views versus M queries

SQL view examples

Date dimension view

Mark as date table

Product dimension view

Slowly changing dimensions

Summary

Connecting to Sources and Transforming Data with M

Types of Power Query M queries

Organizing queries

Data source parameters

Staging queries

DirectQuery staging

Fact and dimension queries

Source reference only

Excel workbook – Annual Sales Plan

Parameter tables queries

Security table queries

Custom function queries

Creating Power Query M queries

Numeric data types

Item access in M

Power Query M query examples

Trailing three years filter

Combining queries

Incremental refresh for fact tables

Customer history column

Product dimension integration

R and Python script transformation

Dataflows

Power BI Premium dataflow features

Power Query M editing tools

Advanced Editor

Visual Studio Code

Visual Studio

Summary

Designing Import, DirectQuery, and Composite Data Models

Dataset layers

Power BI as a superset of Azure Analysis Services

Dataset objectives

Competing objectives

External factors

The Data Model

The Model view

The Data view

The Report view

Fact tables

Fact table columns

Fact column data types

Fact-to-dimension relationships

Dimension tables

Hierarchies

Custom sort

Parameter tables

Measure groups

Last refreshed date

Measure support logic

Display folders

Relationships

Uniqueness

Ambiguity

Single-direction relationships

Direct flights only

Bidirectional relationships

Shared dimensions

Date dimensions

The CROSSFILTER function

Model metadata

Visibility

Column and measure metadata

Default summarization

Data format

Data category

Description

Optimizing data model performance

Import

Query caching

Columnar compression

Memory analysis via DMVs and the VertiPaq Analyzer

DirectQuery

Columnstore and HTAP

Automatic aggregations

Composite

Aggregation tables

Summary

Developing DAX Measures and Security Roles

DAX measure basics

Filter context

SQL equivalent

Measure evaluation process

Row context

Scalar and table functions

Related tables

The CALCULATE() function

The FILTER() function

DAX variables

Base measures

Measure support expressions

KPI targets

Current and prior periods

Date intelligence metrics

Current versus prior and growth rates

Rolling periods

Calculation groups

Dimension metrics

Missing dimensions

Ranking metrics

Dynamic ranking measures

Security roles

Dynamic row-level security

Performance testing

Performance analyzer

DAX Studio

Summary

Planning Power BI Reports

Report planning process

Identify the audience

Define the business questions to answer

Confirm that the dataset supports the business questions

Determine interactivity

Define access and distribution

Sketch the report layout

Report architecture diagram

Visualization best practices

Choosing the right visual

Tables and matrices versus charts

Chart selection

Visualization anti-patterns

Visual interactions

Editing interactions

Drillthrough report pages

Custom labels and the back button

Multi-column drillthrough

Report filter scopes

Report filter conditions

Report and page filters

Relative date filtering

Visual-level filtering

Top N visual-level filters

Bookmarks

Selection pane and the Spotlight property

Custom report navigation

View mode

Live connections to Power BI datasets

Customizing Live connection reports

Switching Live source datasets

Switching between import mode and Live mode datasets

Report design summary

Summary

Creating and Formatting Visualizations

The Visualizations pane

Slicers

Slicer synchronization

Custom slicer parameters

What-if parameters

Page filter or slicer?

Single-value visuals

The Card visual

The KPI visual

Gauge visual

Map visuals

Bubble map

Filled map

Waterfall chart

Power Platform visuals

Power Apps for Power BI

Power Automate for Power BI

Premium visuals

Scorecard

Paginated reports

Elements

Formatting visualizations

Tooltips

Report page tooltips

Column and line charts

Column and line chart conditional formatting

Table and matrix visuals

Custom format strings

Table and matrix conditional formatting

Sparklines

Values as rows

Scatter charts

Summary

Applying Advanced Analytics

AI visuals

Key influencers

Decomposition tree

Q&A

Smart narrative

R and Python visuals

R visual

Python visual

ArcGIS Maps for Power BI

Custom visuals

Adding a custom visual

Animation and data storytelling

Play axis for Scatter charts

Pulse chart

Analytics pane

Trend line

Forecasting

Quick insights/Analyze

Explain the increase/decrease

Mobile-optimized report pages

Summary

Designing Dashboards

Dashboards versus reports

Dashboard design

Visual selection

Layout

Navigation pane

Fullscreen mode

Supporting tiles

Dashboard architectures

Single-dashboard architecture

Multiple-dashboard architecture

Organizational dashboard architecture

Multiple datasets

Dashboard tiles

Tile details and custom links

Real-time data tiles

Dashboard themes

Paginated reports

Excel workbooks

Live report pages

Mobile-optimized dashboards

Summary

Managing Workspaces and Content

Workspaces

Workspace roles and rights

Viewer role

Contributor role

Member role

Admin role

Datasets across workspaces

My workspace

Staged deployments

Workspace datasets

Power BI REST API and PowerShell module

Power BI REST API

Power BI PowerShell module

Workspace and content identifiers

PowerShell sample scripts

Power BI deployment pipelines

Content sensitivity and protection

Information protection

Data loss prevention

Version control

OneDrive for Business

Source control for M and DAX code

MSHGQM

Metadata management

Field descriptions

Creating descriptions

View field descriptions

Metadata Mechanic

Metadata reporting

Standard metadata reports

Server and database parameters

Querying the DMVs from Power BI

Integrating and enhancing DMV data

Metadata report pages

Summary

Managing the On-Premises Data Gateway

On-premises data gateway planning

Top gateway planning tasks

Determining whether a gateway is needed

Identifying where the gateway should be installed

Defining the gateway infrastructure and hardware requirements

Defining gateway roles and permissions

Planning for recovery keys

Standard versus personal mode

Gateway concepts

Gateway clusters

Gateway architectures

Gateway security

Gateway installation and configuration

The gateway service account

TCP versus HTTPS mode

Connectors

Recovery Keys

Managing gateway clusters

Gateway administrators

Gateway data sources and users

PowerShell support for gateway clusters

Troubleshooting and monitoring gateways

Restoring, migrating, and taking over a gateway

Gateway diagnostics

Gateway monitoring reports

Data refresh

Scheduled data refresh

DirectQuery datasets

Live connections to Analysis Services models

Dashboard cache refresh

Summary

Deploying Paginated Reports

Paginated reports in the Power BI service

Planning paginated reports

Building and publishing paginated reports

Identifying and interacting with paginated reports

Printing, exporting, subscribing, and sharing

Migrating reports to the Power BI service

Inventory

Assess

Plan

Migrate

User Acceptance Testing and final deployment

Planning the Power BI Report Server (PBRS)

Feature differences with the Power BI service

Parity with SQL Server Reporting Services

Data sources and connectivity options

Hardware and user licensing

Pro licenses for report authors

Alternative and hybrid deployment models

PBRS reference topology

Scale PBRS

Installing and upgrading PBRS

Retrieve the PBRS product key

Upgrade cycles

PBRS client applications

Running desktop versions side by side

Power BI mobile applications

Summary

Creating Power BI Apps and Content Distribution

Content distribution methods

Power BI apps

Licensing apps

App deployment process

User permissions and security

Publishing apps

Installing apps

App updates

Apps on Power BI mobile

Sharing content

Sharing scopes

Sharing versus Power BI apps

Embedding

Licensing embedding

Publish to web

Secure URL embedding

Microsoft 365 apps

Teams

SharePoint Online

Custom applications

Data alerts

Power Automate integration

Email subscriptions

Analyze in Excel

Self-service BI workspaces

Self-service content distribution

Risks of self-service BI

Summary

Administering Power BI for an Organization

Power BI administrator role

Data governance for Power BI

Implementing data governance

Azure Active Directory

AAD B2B collaboration

Licensing external users

Conditional access policies

Power BI admin portal

Tenant settings

Usage metrics

Users and Audit logs

Premium Per User

Capacity settings

Embed codes

Organizational visuals

Azure connections

Workspaces

Custom branding

Protection metrics

Featured content

Usage metrics reports

Audit logs

Audit log monitoring solution

The Power BI REST API for admins

Summary

Building Enterprise BI with Power BI Premium

Power BI Premium

Power BI Premium capabilities

Premium capacity nodes

Frontend versus backend resources

Premium capacity estimations

Premium capacity administration and allocation

Capacity allocation

Corporate and Self-Service BI capacity

Create, size, and monitor capacities

Changing capacity size

Monitoring Premium capacities

Workspace assignment

Premium capacity resource optimization

Data model optimizations

Report and visualization optimizations

Workloads

Life cycle management with Premium

ALM Toolkit deployment

Dataset management with SSMS

Backing up Premium capacities

Summary

Other Books You May Enjoy

Index

Landmarks

Cover

Index

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:

Scan the QR code or visit the link below

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

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly

1

Planning Power BI Projects

Power BI is a robust, flexible business intelligence platform enabling organizations to deploy data analysis and reporting solutions according to their individual policies and use cases. Organizations can utilize Power BI to enable self-service data analytics and visualization for business analysts, as well as deploying enterprise-grade solutions involving technical expertise and advanced security and scalability features. Likewise, Power BI fully supports both cloud and on-premises data sources as well as all primary types of reports, ranging from interactive visualizations to pixel-perfect paginated reports to Excel-based reports.

While specific organizational goals, the data landscape, and specific resource responsibilities can vary greatly, the underlying concepts, deployment choices, roles, and planning processes for business intelligence projects remain the same. The long-term success or failure of most Power BI projects is most highly correlated to the planning, organization, and effective collaboration of the different stakeholders. Solutions that deliver the most value to the business over time are the result of thoughtful decisions around the people and processes involved in data governance, data quality, data modeling, and finally data visualization and distribution.

This chapter explores the various project planning decision topics, roles, and processes critical to the success of all Power BI projects.

In this chapter, we review the following topics:

Power BI deployment modesProject discovery and ingestionPower BI project rolesPower BI licensesDataset designData profilingDataset planning

To begin, we first explore the different deployment modes for Power BI.

Power BI deployment modes

Prior to the existence and adoption of BI tools capable of supporting self-service scenarios, business analysts were effectively relegated to the role of “end user” of solutions developed and maintained from end to end by their information technology department. While this top-down approach helped ensure that the solution would be secure, accurate, and resource-efficient, it was also relatively slow and inflexible to adjust to changing requirements.

As a consequence, business analysts commonly utilized the IT-owned solutions as merely a starting point or data source to their own MS Office-based solutions that business analysts could maintain. The perceived lack of flexibility and extended timelines sometimes associated with IT-owned solutions often frustrated business teams, resulting in a lack of adoption and “shadow IT” scenarios in which business users created their own solutions via Excel and other tools.

Modern business intelligence platforms such as Power BI provide increased opportunities for the business to participate in the creation and deployment of data assets for the organization. Organizations can deliver Power BI solutions that require the resources and technical expertise of a Corporate BI approach, as well as empowering business teams to leverage the self-service capabilities of the platform. This “self-service” can range widely from enabling teams to access and analyze certain certified Power BI datasets to empowering business analysts to create their own end-to-end solutions including their own data transformation workflows and semantic models.

In many scenarios, a combination of corporate IT resources, such as the on-premises data gateway and Power BI Premium capacity, can be combined with the business users’ knowledge of requirements and familiarity with data analysis and visualization in order to increase the velocity of data asset development. More experienced organizations may even utilize multiple deployment modes depending on the distinct requirements and use cases for Power BI across different teams and projects.

For example, solutions involving highly sensitive data or targeted at executive leadership are generally owned from end to end by Corporate BI/IT personnel. However, in scenarios involving rapidly changing requirements where deep business knowledge is essential, business analysts familiar with the data are often empowered with sufficient Power BI licenses and resources to develop their own datasets and reports.

We refer to standard deployment mode as Corporate BI, a deployment mode where the IT department controls all aspects of the business intelligence platform. Alternative approaches are called self-service, where the business controls some or all aspects of the business intelligence platform.

Self-service approaches can benefit both IT and business teams, as self-service can reduce IT resource constraints and project timelines, and provide the business with greater flexibility and control as analytical needs change. Additionally, Power BI projects can be migrated across deployment modes over time as required skills and resources change. However, greater levels of self-service and shared ownership structures generally increase the risk of miscommunication and introduce issues of version control, quality, and consistency.

These deployment modes are summarized in Figure 1.1:

Figure 1.1: Power BI deployment modes

A Power BI dataset is a semantic data model primarily comprised of data source queries, relationships between fact and dimension tables, and measure calculations. A semantic data model adds meaning to the physical, underlying data by adding relationships between data entities, allowing organizations to extract truth and understanding from their data.

Datasets often contain hierarchies, row-level security roles, and often other metadata such as calculation groups, detailed row expressions, and other metadata that supports usability and analysis. Power BI datasets share the heritage and concepts of Analysis Services tabular mode models and are generally developed using Power BI Desktop, a Windows application.

Microsoft has now positioned Power BI Premium-hosted datasets as their flagship semantic modeling tool and a “superset” of Analysis Services models. As the “superset” term implies, Power BI Premium-hosted datasets now support all of the enterprise-grade modeling features of Analysis Services and there are a number of powerful modeling features, such as composite models and incremental refresh policies, that are only available via Power BI datasets. Chapter 15, Building Enterprise BI with Power BI Premium, covers Power BI Premium in more detail.

Now that deployment modes are understood at a high level, let’s take a look at each of the three deployment modes in greater detail.

Corporate BI

The Corporate BI delivery approach in which the BI team develops and maintains both the Power BI dataset (sometimes called a data model) and the required report visualizations is a common deployment option, particularly for large-scale projects and projects with executive-level sponsors or stakeholders. This is the approach followed in this chapter and throughout this book, as it offers maximum control over top BI objectives, such as version control, scalability, usability, and performance.

Corporate BI can be visualized as shown in Figure 1.2:

Figure 1.2: Corporate BI

As shown in Figure 1.2, all data and Power BI assets are owned by corporate IT and business users simply consume reports and dashboards published by corporate IT to the Power BI service.

Again, with the Corporate BI approach, business users are solely consumers of corporate business intelligence assets. Next, we compare this approach with self-service approaches where business users are more engaged with the creation and deployment of business intelligence assets.

Self-Service Visualization

In the Self-Service Visualization approach, the dataset is created and maintained by the IT organization’s BI team, but certain business users with Power BI Pro licenses create reports and dashboards for consumption by other users. In many scenarios, business analysts are already comfortable with authoring reports in Power BI Desktop (or, optionally, Excel) and can leverage their business knowledge to rapidly develop useful visualizations and insights.

With ownership of the dataset, the BI team can be confident that only curated data sources and standard metric definitions are used in reports and can ensure that the dataset remains available, performant, and updated or refreshed as per business requirements.

Self-Service Visualization is shown in Figure 1.3:

Figure 1.3: Self-Service Visualization

As shown in Figure 1.3, dataset designers within corporate IT still create and manage the Power BI datasets but business users author and publish reports and dashboards to the Power BI service. In the next section, we explore the Self-Service BI approach driven entirely by the business.

Self-Service BI

In the Self-Service BI approach, the BI organization only contributes essential infrastructure and monitoring, such as the use of an on-premises data gateway and possibly Power BI Premium capacity to support the solution. Since the business team maintains control of both the datasets and the visualization layer, the business team has maximum flexibility to tailor its own solutions including data source retrieval, transformation, and modeling.

This flexibility, however, can be negated by a lack of technical coding skills and a lack of technical knowledge such as the relationships between tables in a database. Additionally, business-controlled datasets can introduce version conflicts with corporate semantic models and generally lack the resilience, performance, and scalability of IT-owned datasets. Self-Service BI can be visualized as shown in Figure 1.4:

Figure 1.4: Self-Service Visualization

As shown in Figure 1.4, with a completely self-service approach to business intelligence with Power BI, the business, and not IT, performs all of the functions of dataset design and report authoring. Now that the three different deployment modes are understood in greater detail, next we cover choosing between them.

Choosing a deployment mode

Organizations generally choose a standard deployment mode used throughout the business or choose a particular deployment mode based upon the unique requirements and goals of each individual Power BI project.

It’s usually necessary or at least beneficial for Corporate BI organizations to own the Power BI datasets or at least the datasets that support important, widely distributed reports and dashboards. This is primarily due to the value of providing a single source of truth built on top of a curated data source such as a data warehouse as well as the technical skills involved in developing and managing large or complex datasets.

Additionally, BI organizations require control of datasets to implement security and to maintain version control. Security and version control often factor into corporate governance policies or are necessary to maintain compliance with regulations imposed by government agencies. Therefore, small datasets initially created by business teams are often migrated to the BI team and either integrated into larger models or rationalized given the equivalent functionality from an existing dataset.

Larger organizations with experience in deploying and managing Power BI often utilize a mix of deployment modes depending on the needs of the project and available resources. For example, a Corporate BI solution with a set of standard IT-developed reports and dashboards distributed via a Power BI app may be extended by assigning Power BI Pro licenses to certain business users who have experience or training in Power BI report design. These users could then leverage the existing data model and business definitions maintained by IT to create new reports and dashboards and distribute this content in a separate Power BI workspace and/or app.

A workspace is simply a container of datasets, reports, and dashboards in the Power BI cloud service that can be distributed to large groups of users. A Power BI app represents the published version of a workspace in the Power BI service and workspace. Members can choose which items in the workspace are included in the published Power BI app. See Chapter 10, Managing Application Workspaces and Content, and Chapter 13, Creating Apps and Content Distribution, for greater detail on app workspaces and apps, respectively.

Another common scenario is a Proof of Concept (POC). A POC is a small-scale self-service solution developed by a business user or a team designed to be transitioned to a formal, IT-owned, and managed solution. Power BI Desktop’s rich graphical interfaces at each layer of the application (query editor, data model, and report canvas) make it possible and often easy for users to create useful models and reports with minimal experience and little to no code.

It’s much more difficult, of course, to deliver consistent insights across business functions (that is, finance, sales, and marketing) and at scale in a secure, governed environment. The IT organization can enhance the quality and analytical value of these assets, as well as providing robust governance and administrative controls to ensure that the right data is being accessed by the right people.

The following list of fundamental questions help guide a deployment mode decision:

Who will own the data model?

Experienced dataset designers and other IT professionals are usually required to support complex data transformations, analytical data modeling, large data sizes, and security rules, such as RLS roles, as described in Chapter 5, Developing DAX Measures and Security Roles.

If the required data model is relatively small and simple, or if the requirements are unclear, the business team may be best positioned to create at least the initial iterations of the model.

The data model could be created with Analysis Services or Power BI Desktop.

Who will own the reports and dashboards?

Experienced Power BI report developers with an understanding of corporate standards and data visualization best practices can deliver a consistent user experience.

Business users can be trained on report design and development practices and are well positioned to manage the visualization layer, given their knowledge of business needs and questions.

How will the Power BI content be managed and distributed?

A staged deployment across development, test, and production environments, as described in Chapter 8, Managing Application Workspaces and Content, helps to ensure that quality, validated content is published. This approach is generally exclusive to Corporate BI projects.

Sufficient Power BI Premium capacity is required to support distribution to Power BI free users and either large datasets or demanding query workloads.

Self-Service BI content can be assigned to Premium capacity, but organizations may wish to limit the scale or scope of these projects to ensure that provisioned capacity is being used efficiently.

As covered in this section, deployment modes represent the overall manner in which Power BI is used within an organization. Now that the different deployment modes for Power BI are fully understood, we next move on to covering the processes and roles for implementing individual Power BI projects.

Project discovery and ingestion

An organization’s business intelligence assets are the result of individual projects designed to accomplish a specific set of goals or answer a specific set of business questions. Thus, the successful initiation and execution of business intelligence projects is vital to all organizations.

Power BI projects often begin with answering a set of standard questions within a project template form. Business guidance on these questions informs the BI team of the high-level technical needs of the project and helps to promote a productive project kickoff. By reviewing the project template, the BI team can ask the project sponsor or relevant Subject Matter Experts (SMEs) targeted questions to better understand the current state and the goals of the project.

A sample Power BI project template is provided in the following section.

Sample Power BI project template

The primary focus of the project planning template and the overall project planning stage is on the data sources and the scale and structure of the Power BI dataset required. The project sponsor or business users may only have an idea of several reports, dashboards, or metrics needed but, as a Corporate BI project, it’s essential to focus on where the project fits within an overall BI architecture and the long-term Return on Investment (ROI) of the solution. For example, BI teams would look to leverage any existing Power BI datasets or Analysis Services tabular models applicable to the project and would be sensitive to version control issues.

The following section provides a completed template for a Power BI project.

Sample template – Adventure Works BI

The template is comprised of two tables. The first table, Table 1.1, answers the essential who and when questions so that the project can be added to the BI team’s backlog. The BI team can use this information to plan their engagements across multiple ongoing and requested Power BI projects and to respond to project stakeholders, such as Vickie Jacobs, VP of Group Sales, in this example:

Date of Submission

6/6/2022

Project Sponsor

Vickie Jacobs, VP of Group Sales

Primary Stakeholders

Adventure Works Sales

Adventure Works Corp

Power BI Author(s)

Mark Langford, Sales Analytics Manager

Table 1.1: Project sponsors, stakeholders, and participants

Identifying stakeholders is critical to the success of business intelligence projects. Stakeholders define the goals and requirements of the business intelligence project and ultimately determine success or failure in meeting identified goals. There are often multiple stakeholders for business intelligence projects and these stakeholders may even span multiple business domains. Start by identifying the business domains as stakeholders for the business intelligence project and then identify specific individuals within those domains who can provide the goals and requirements for the project.

It is always advantageous to identify a single individual as a special kind of stakeholder, a project sponsor. Project sponsors secure the funding and assist in the prioritization of resources for business intelligence projects.

The following table, Table 1.2, is a list of questions that describe the project’s requirements and scope. It is critical to discover and answer as many of these questions as possible early on in a business intelligence project in order to set expectations in terms of the cost and duration of the project. For example, the number of users who are read-only consumers of Power BI reports and dashboards and the number of self-service users who need Power BI Pro licenses to create Power BI content largely impact the total cost of the project. Likewise, the amount of historical data to include in the dataset (2 years, 5 years?) can significantly impact performance scalability:

Topic

#

Question

Business Input

Data sources

1

Can you describe the required data? (For example, sales, inventory, shipping)

Internet Sales, Reseller Sales, and the Sales and Margin Plan. We need to analyze total corporate sales, online and reseller sales, and compare these results to our plan.

Data sources

2

Is all of the data required for your project available in the data warehouse (SQL Server)?

No.

Data sources

3

What other data sources (if any) contain all or part of the required data (for example, Web, Oracle, Excel)?

The Sales and Margin Plan is maintained in Excel.

Security

4

Should certain users be prevented from viewing some or all of the data?

Yes, sales managers and associates should only see data for their sales territory group. VPs of sales, however, should have global access.

Security

5

Does the data contain any PCII, HIPAA, GDPR, or other sensitive data?

No, not that I’m aware of.

Scale

6

Approximately, how many years of historical data are needed?

3-4.

Scale

7

Is it necessary to track the history of certain dimensions such as customers or products? For example, if a customer’s address changes, is it necessary to store and report on both the prior address and the new address?

Yes, it would be helpful to track product history.

Data refresh

8

How often does the data need to be refreshed?

Daily.

Data refresh

9

Is there a need to view data in real time (as it changes)?

No.

Distribution

10

Approximately, how many users will need to view reports and dashboards?

200.

Distribution

11

Approximately, how many users will need to create reports and dashboards?

3-4.

Distribution

12

Will the users be viewing the reports and dashboards on mobile devices such as phones or tablets?

Yes, users need the ability to access the information on their phones.

Version control

13

Are there existing reports on the same data? If so, please describe.

Yes, there are daily and weekly sales snapshot reports available on the portal. Additionally, our team builds reports in Excel that compare actuals to the plan.

Version Control

14

Is the Power BI solution expected to replace these existing reports?

Yes, we would like to exclusively use Power BI going forward.

Version Control

15

Is there an existing Power BI dataset that targets the same business processes (fact tables)?

Not to our knowledge.

Table 1.2: Project questions regarding project’s scope

A business analyst inside the IT organization often partners with the business on completing the project ingestion template and reviews the current state in order to give greater context to the template. Prior to the project kickoff meeting, the business analyst usually meets with the BI team members to review the template and any additional findings or considerations.

Many questions with greater levels of detail are required as the project moves forward and therefore the template shouldn’t attempt to be comprehensive or overwhelm business teams. The specific questions to include should use business-friendly language and serve to call out the top drivers of project resources and Corporate BI priorities, such as security and version control.

Now that you understand the process and requirements that drive project discovery and ingestion, we next cover the different roles involved in Power BI projects.

Power BI project roles

Following the review of the project template and input from the business analyst, members of the Power BI team directly engage the project sponsor and other key stakeholders to officially engage in the project. These stakeholders include SMEs on the data source systems, business team members knowledgeable about the current state of reporting and analytics, and administrative or governance personnel with knowledge of organizational policies, available licenses, and current usage.

New Power BI projects of any significant scale and long-term adoption of Power BI within organizations require Dataset Designers, Report Authors, and Power BI Admin(s), as illustrated in the following diagram:

Figure 1.5: Power BI team roles

Each of the three Power BI project roles and perhaps longer-term roles as part of a business intelligence team entail a distinct set of skills and responsibilities. It can be advantageous in a short-term or POC scenario for a single user to serve as both a dataset designer and a report author. However, the Power BI platform and the multi-faceted nature of Corporate BI deployments are too broad and dynamic for a single BI professional to adequately fulfill both roles.

It’s recommended that team members either self-select or are assigned distinct roles based on their existing skills and experience and that each member develops advanced and current knowledge relevant to their role. For example, individuals with a user experience and user interface (UX/UI) background are generally best suited to fulfill the Report Author role. Conversely, more technical developers with a background in coding and data modeling often fulfill the Dataset Designer role. A BI manager and/or a project manager can help facilitate effective communication across roles and between the BI team and other stakeholders, such as project sponsors.

Let’s now take a closer look at each of the three roles involved in Power BI projects.

Dataset designer

The dataset designer is responsible for the data access layer of the Power BI dataset, including the authentication to data sources and the M queries used to define the tables of the data model. Additionally, the dataset designer defines the relationships of the model and any required row-level security roles and develops the DAX measure expressions for use in reports, such as year-to-date (YTD) sales.

A Power BI dataset designer often has experience in developing Analysis Services models, particularly Analysis Services models in tabular mode, as this aligns with the semantic modeling engine used in Power BI. For organizations utilizing both Analysis Services and Power BI Desktop, this could be the same individual. Alternatively, business analysts experienced with Power Pivot for Excel or with the modeling features of Power BI Desktop may also prove to have the skills required of Power BI dataset designers for self-service scenarios.

Datasets (semantic models) have always been the heart of Power BI solutions as they serve as the data source responsible for rapidly resolving the report queries generated by reports and analysis sessions. Power BI datasets can be designed to import copies of data from multiple data sources into a compressed, in-memory cache, as well as merely passing report queries back to a data source system such as Azure Synapse Analytics. Additionally, Power BI dataset designers can mix both import (in-memory) and DirectQuery storage modes across different tables of a dataset thus balancing the tradeoffs between the two storage modes.

In addition to providing a performant and scalable data source that efficiently utilizes resources (CPU, RAM), datasets must provide a user-friendly interface for report authors and analysts to quickly produce effective content. Moreover, datasets also typically contain Row-Level Security (RLS) roles that limit what certain users or groups of users can see and can also contain complex logic to support certain business rules or report requirements. Datasets are therefore a critical component of Power BI projects and their design has tremendous implications regarding user experience, query performance, source system and Power BI resource utilization, and more.

Given the importance of Power BI datasets and the implications of dataset design decisions for entire environments, many organizations choose to dedicate one or multiple developer roles to Power BI datasets. These individuals are expected to have advanced- to expert-level knowledge of Data Analysis eXpressions (DAX) as well as experience with enterprise features such as aggregation tables, partitions and incremental refresh, and other supporting third-party tools such as ALM Toolkit. All of these topics are explained in later chapters.

Business analysts or “power users” can often independently learn or receive essential training to build basic Power BI datasets that meet the needs of their department. However, business analysts can also struggle to learn coding languages like M and DAX and can fail to appreciate other goals of a dataset such as resource usage. For this reason, organizations are well advised to regularly monitor the datasets developed by business teams/analysts and consider adopting a process for migrating ownership of these datasets from a business team to a Corporate BI team.

It can’t be emphasized strongly enough that Power BI project teams should carefully distinguish between datasets and reports and maintain a goal of supporting many related reports and dashboards via high-quality, well-tested or certified datasets. This can be challenging as teams are generally tasked with developing reports regardless of the source dataset, thus creating a temptation to simply create a dataset dedicated to the needs of a single report. Over the long term this “report factory” approach results in both inefficient use of resources (CPU) as well as confusion and manageability issues with many datasets having slightly different logic and all needing to be maintained.

Dataset designers should regularly communicate with data source owners or SMEs, as well as report authors. For example, the dataset designer needs to be aware of changes to data sources so that data access queries can be revised accordingly, and report authors can advise of any additional measures or columns necessary to create new reports. Furthermore, the dataset designer should be aware of the performance and resource utilization of deployed datasets and should work with the Power BI admin on issues such as Power BI Premium capacity.

As per Figure 1.5, there are usually relatively few dataset designers in a team compared with the number of report authors. This is largely due to the organizational objectives of version control and reusability, which leads to a small number of large datasets. Additionally, robust dataset development requires knowledge of the M and DAX functional programming languages, dimensional modeling practices, and business intelligence. Database experience is also very helpful. If multiple dataset designers are on a team, they should look to standardize their development practices so that they can more easily learn and support each other’s solutions.

With the crucial role of the dataset designer understood, we next explore the report author role.

Report authors

Report authors interface directly with the consumers of reports and dashboards or a representative of this group. In a self-service deployment mode or a hybrid project (business and IT), a small number of report authors may themselves work within the business.

Above all else, report authors must have a clear understanding of the business questions to be answered and the measures and attributes (columns) needed to visually analyze and answer these questions. The report author should also be knowledgeable of visualization best practices, such as symmetry and minimalism, in addition to any corporate standards for report formatting and layout.

Power BI Desktop provides a rich set of formatting properties and analytical features, giving report authors granular control over the appearance and behavior of visualizations. Report authors should be very familiar with all standard capabilities, such as conditional formatting, drilldown, drillthrough, and cross-highlighting, as they often lead demonstrations or training sessions.

It’s important for report authors to understand the use cases and essential features of the two alternative report types available in Power BI – paginated reports and Excel reports. For example, given the requirements to export or print detail-level data, a report author should be comfortable in building a paginated report via the Power BI Report Builder. Additionally, report authors should understand the organization’s policies on custom visuals available in the MS Office store and the specific use cases for top or popular custom visuals.

It should be clear now that report authors have distinct responsibilities and skillsets compared to dataset designers. The ability to design intuitive reports and dashboards that are easily understood by the business is also of critical importance to the success of every Power BI project. Next, we look at the last critical role, the Power BI administrator.

Power BI administrator

As Power BI has grown its capabilities and become a mission-critical tool for organizations, the role of a Power BI administrator (admin) has become increasingly common. Power BI administrators are responsible for ensuring Power BI is utilized effectively and according to the organization’s policies. For example, Power BI administrators monitor and troubleshoot dataset refresh failures, performance issues, user access requests and issues, and the overall health of an organization’s Premium capacities.

A Power BI administrator is assigned the Power BI administrator role in Azure Active Directory, the identity and access control service at the heart of Microsoft’s cloud-based Software as a Service (SaaS) products. Assignment of the Power BI administrator role is done in the Microsoft 365 admin center and only Global administrators of Office 365 can assign users to the role.

Users assigned to the Power BI administrator role obtain access to the Power BI admin portal and the rights to configure Power BI tenant settings. The Power BI admin portal and tenant settings are used to enable or disable features, such as exporting data and printing reports and dashboards. BI and IT managers that oversee Power BI deployments are often assigned to this role, as the role also provides the ability to manage Power BI Premium capacities and access to standard monitoring and usage reporting.

The Power BI admin should have a clear understanding of the organizational policy on the various tenant settings, such as whether content can be shared with external users. For most tenant settings, the Power BI administrator can define rules in the Power BI admin portal to include or exclude specific security groups. For example, external sharing can be disabled for the entire organization except for a specific security group of users.

Power BI admins must also have a thorough knowledge of permissions, roles, sharing, and licensing of Power BI in order to resolve common issues related to access. For example, a Power BI admin would know that build permission to a dataset could be granted to a business analyst as a less permissive alternative to membership in the workspace of the source dataset.

Most organizations should assign two or more users to the Power BI administrator role and ensure these users are trained on the administration features specific to this role. Chapter 14, Administering Power BI for an Organization, contains details on the Power BI admin portal and other administrative topics.

While Power BI admins are not involved in the day-to-day activities of specific projects, the role is ultimately critical to the success of all projects, as is the overall collaboration between all project roles, which we cover in the next section.

Project role collaboration

Communicating and documenting project role assignments during the planning stage promotes the efficient use of time during the development and operations phases. For organizations committed to the Power BI platform and perhaps migrating away from a legacy or different BI platform, project roles may become full-time positions.

For example, BI developers with experience in DAX and Analysis Services tabular models may transition to permanent dataset designer roles while BI developers experienced in data visualization and report development may become report authors:

Name

Project role

Brett Powell

Dataset Designer

Jennifer Lawrence

Report Author

Anna Sanders

Power BI Administrator

Mark Langford

Report Author

Stacy Loeb

QA Tester

Table 1.3: Project role assignments

It is important for the individuals within all of the various roles to work together and communicate effectively in order to deliver a successful project outcome. Proper communication and collaboration are important to all projects but are perhaps even more crucial within the realm of business intelligence given the distinct nature of the roles involved and the criticality of accurate, effective reporting to the success of organizations.

With project roles and responsibilities now understood, we next cover the various forms of licensing for Power BI deployments.

Power BI licenses

Power BI provides a number of different licensing options that provide flexible and affordable pricing for individuals and organizations. These licensing options come in two primary categories:

Shared capacityDedicated capacity

Let us first have a look at shared capacity.

Shared capacity

Shared capacity is like an apartment building. While each tenant in the building has their own personal living quarters accessible to only themselves, certain infrastructures such as plumbing, electrical wiring, and stairways are common to everyone in the building. Shared capacity for Power BI is similar. Each tenant within the Power BI service has its own area for publishing data and reporting assets but infrastructure such as memory and processing capacity are shared among the tenants. Thus, just like a noisy neighbor in an apartment building can affect other tenants, so too can tenants within shared capacity in the Power BI service impact the performance for other tenants.