Mastering Microsoft Power BI: Expert techniques to create interactive insights for effective data analytics and business intelligence, 2nd Edition

✍ Scribed by Greg Deckler, Brett Powell

• Publisher: Packt Publishing
• Year: 2022
• Tongue: English
• Leaves: 707
• Edition: 2
• Category: Library

✦ Synopsis

Plan, design, develop, and manage robust Power BI solutions to generate meaningful insights and make data-driven decisions

Key Features

• Master the latest dashboarding and reporting features of Microsoft Power BI
• Combine data from multiple sources, create stunning visualizations and publish Power BI apps to thousands of users
• Get the most out of Microsoft Power BI with real-world use cases and examples

Book Description

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.

What you will learn

• Build efficient data retrieval and transformation processes with the Power Query M language and dataflows
• Design scalable, user-friendly DirectQuery, import, and composite data models
• Create basic and advanced DAX measures
• Add ArcGIS Maps to create interesting data stories
• Build pixel-perfect paginated reports
• Discover the capabilities of Power BI mobile applications
• Manage and monitor a Power BI environment as a Power BI administrator
• Scale up a Power BI solution for an enterprise via Power BI Premium capacity

Who this book is for

Business Intelligence professionals and intermediate Power BI users looking to master Power BI for all their data visualization and dashboarding needs will find this book useful. An understanding of basic BI concepts is required and some familiarity with Microsoft Power BI will be helpful to make the most out of this book.

Table of Contents

1. Planning Power BI Projects
2. Preparing Data sources
3. Connecting to Sources and Transforming Data with M
4. Designing Import, DirectQuery, and Composite Data Models
5. Developing DAX Measures and Security Roles
6. Planning Power BI Reports
7. Creating and Formatting Visualizations
8. Applying Advanced Analytics
9. Designing Dashboards
10. Managing Workspaces and Content
11. Managing the On-Premises Data Gateway
12. Deploying Paginated Reports
13. Creating Power BI Apps and Content Distribution
14. Administering Power BI for an Organization
15. Building Enterprise BI with Power BI Premium

✦ Table of Contents

Cover
Copyright
Contributors
Table of Contents
Preface
Chapter 1: 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
Chapter 2: 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
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: 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
Chapter 7: 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
Chapter 8: 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
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: 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
Chapter 13: 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
Chapter 14: 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
Chapter 15: 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