Table of Contents
About the Author
About the Technical Reviewers
Acknowledgments
Introduction
Chapter 1: Power BI Components
What Is Power BI?
The Power BI Desktop
Power Query for Data Transformation
The Power BI Report
Analytical Engine (Tabular Engine)
Datasets
The Power BI Dashboard
Dataflows
Datamarts
The DAX Language
The Power BI Service for Hosting Reports
The Power BI Mobile App
The Power BI Report Server
Paginated Reports
The Power BI Report Builder
Metrics and Scorecards
The On-Premises Gateway
Workspaces
Power BI App
Power BI Premium
Power BI Embedded
Developer API or REST API
Summary
Chapter 2: Tools and Preparation
Using the Power BI Desktop
The Power BI Desktop: Report Authoring Tool
The Power BI Desktop from the Microsoft Store
The Power BI Desktop as a Separate Download Link
Auto-Update from the Microsoft Store App
Flexibility on Installation: The Download Link
Development Work
Setting Up Power BI Accounts
Creating Power BI Accounts from Office 365
Is Your Account Pro, Free, or PPU?
Installing Power BI Mobile App
Power BI Report Server
On-Premises Gateway
Microsoft SQL Server Database and Management Tools
The Power BI Helper
Downloading Dataset Files
Summary
Chapter 3: Import Data or Scheduled Refresh
The Connection Type Is Not the Data Source Type
Import Data or Scheduled Refresh
A Closer Look at Import Data
Loading Data to the Model
How Power BI Stores Data in Memory
How xVelocity Compresses Data
Where Data Is Stored in Memory
How About Power BI Service?
Is There a Limitation on Size?
Combining Data Sources; Power Query Unleashed
DAX: A Powerful Analytical Expression Language
Publishing a Report
Gateway Configuration
Scheduled Refresh
Advantages and Disadvantages of Import Data
Advantages of Import Data
Disadvantages of Import Data
When Should You Use Import Data?
Summary
Chapter 4: DirectQuery
What Is DirectQuery?
Which Data Sources Support DirectQuery?
How to Use DirectQuery
No Data Tab in DirectQuery Mode
How DirectQuery Works
Performance
Performance Tuning on the Data Source
Query Reduction
Maximum Connections Per Data Source
Adding Extra Data Sources: Composite Mode
Limited Power Query
Limited Modeling and DAX
No Refresh Needed
Large Scale Dataset
Summary
Chapter 5: Live Connection
What Is Live Connection?
Create a Report with Live Connection
Live Connection Behind the Scenes
Performance
Single Data Source
No Power Query Transformations
Modeling and Report-Level Measure
Publishing the Report and Gateway Configuration
Create the Data Sources
Using EffectiveUserName
UPN Mapping
Live Connection to Power BI Service
How Live Connection Differs from DirectQuery
Summary
Chapter 6: Composite Model
What Is Composite Model?
Why Use Composite Model?
How Does Composite Model Work?
Dual Storage Mode
Modeling with the Power BI Composite Model
Relationships in the Power BI Composite Model
Calculations and DAX
DirectQuery to Power BI Datasets
Summary
Chapter 7: DirectQuery for Power BI Datasets and Analysis Services
Power BI with Import Data
One Important Problem with Import Data
Power BI Using Live Connection
Big Limitation of Live Connection
DirectQuery for Power BI Datasets or Analysis Services Datasets
Why Is This Feature a Big Deal?
How It Works Under the Hood
Not from My Workspace
Creating a DirectQuery to Power BI Dataset
DirectQuery to Power BI Dataset vs Live Connection to Power BI Dataset
Multiple DirectQuery Connections Are Supported
Creating a Relationship Between Different Data Sources
Publish to Service
Error: There Is No Gateway
Data Source Credentials
Lineage View
Sharing the Report
Building Access to the Source Power BI Datasets
Summary
Chapter 8: Choosing the Right Connection Type: DirectQuery, Live, Import, or Composite Model
Why Is This a Tough Decision?
What Is Import Data (Scheduled Refresh)?
Compression Engine of xVelocity
Important Pros and Cons of This Method
Power BI Is Fully Functional
Size Limitation
This Is the fastest Method
What Is DirectQuery?
Important Pros and Cons of This Method
Scalability: The Main Advantage
Limited Functionality: Few Power Query Operations, Mainly Visualization
Slow Connection
What Is Live Connection?
Important Pros and Cons of This Method
Big Model Size with OLAP or Tabular Engine
No Power Query, Just Visualization
Report-Level Measures
Composite Model: The Best of Both Worlds
Differences Between Live Connection and DirectQuery
Relationship Configuration
Report-Level Measures
No Power Query in Live Connection
Pros and Cons of Each Method
Import Data or Scheduled Refresh
DirectQuery
Live Connection
Which Method Is the Best and Fastest?
Which Method Is More Flexible?
Which Method Is More Scalable?
Which Architecture Scenarios Are Best for Each Method?
Import Data for Agility and Performance
Live Connection for an Enterprise Solution
Live Connection for Team Development
Direct Query for Non-Microsoft Sources
What Is the Role of the Gateway?
Summary
Chapter 9: Dataflows
What Is a Dataflow?
A Dataflow Is Service-Only (Cloud-Only) Object
A Dataflow Is Not Just for Power BI
Where Do Dataflows Store Data?
Standard vs. Analytical Dataflows
Dataflows Are Powered by Power Query Online
Dataflows Can Be Used in Power BI, Excel, and Other Services
Example Dataflow Scenarios
Using One Power Query Table in Multiple Power BI Reports
Different Data Sources with Different Refresh Schedules
Centralized Data Warehouse
Getting Started with Dataflows in Power BI
Administrator’s Control
Creating Your First Dataflow
Sample Datasets
Power Query Editor Online
Setting Up a Gateway
Blank Query as the Data Source
Scheduled Refreshes
Get Data from Dataflow in the Power BI Desktop
Get Data from Dataflows Using Excel
Summary
Chapter 10: Shared Datasets
What Is the Dataset in Power BI?
What Is Included in the Dataset?
What Is a Shared Dataset?
How Do You Create a Shared Dataset?
Sharing Datasets Across Multiple Workspaces
External Dataset: How Does Shared Dataset Work Behind the Scenes?
Why Use Shared Datasets?
Shared Datasets in the Power BI Architecture
Endorsement: Certified and Promoted Datasets
Summary
Chapter 11: Datamarts
Power BI for the Citizen Data Analyst
Governance and Reusability
What are Datamarts in Power BI?
Who Are Datamarts for?
Power BI Datamart for the Citizen Data Analyst
Power BI Datamart for Enterprises
Power BI Datamart for Developers
What Are the Features of Datamarts that Empower Power BI Development?
Datamarts Complete the BI Ecosystem
One Place to Manage and Build, No Other Tools Needed
One License Is Enough
The Vast Horizon and the Future of Datamarts
Governance
Getting Started with Power BI Datamarts
Premium Workspace
Creating a Datamart
Get Data: Power Query Online
The Datamart Editor
The Data View Tab
Creating a New Query
Writing T-SQL Queries
Model Diagram and Relationship Editor
Creating Measures and Writing DAX Expressions
Incremental Refresh: One Setup for All
Row-Level Security: In Databases and Datasets
Power BI Datamart Components
Power BI Datamarts Under the Hood
The Power BI Dataset
Connecting to the Azure SQL Database
Building Reports from Datamarts
Lineage View
Summary
Chapter 12: The Multi-Layer Architecture
Challenges of Using a Single PBIX File for Everything
Using Dataflows to Decouple the Data Preparation Layer
Shared Datasets for the Data Modeling Layer
Thin Power BI Reports, Paginated Reports, and the Analyze in Excel Option
Power BI Architecture with Dataflows, Shared Datasets, and Thin Reports
Benefits of a Multi-Layered Architecture
Decoupling the Data Preparation Layer Using Dataflows
A Multi-Layered Architecture Without the Need for Other Developer Tools
Multi-Developer Environments
Reusing Calculations and Modeling with Shared Datasets
Minimum Redundancy, Maximum Consistency, and Low Maintenance
Enhancing the Architecture Using Power BI Datamarts
Layered Architecture for Dataflow
Staging Dataflows
Transforming Dataflows
Layered Dataflow Architecture
Chained Datasets
What About Enterprise Architecture?
Summary
Chapter 13: Datamarts vs. Dataflows vs. Datasets
What Is a Dataflow?
What Is a Dataset?
What Is a Datamart?
Differences Between Dataflows and Datasets
Dataflows vs. Datasets
What About Datamarts?
Will Dataflows Be Replaced by Datamarts?
Are Datasets Being Replaced by Datamarts?
Summary
Chapter 14: Paginated Reports
What Is a Paginated Report in Power BI?
Why Paginated Reports?
Differences Between Power BI Reports and Paginated Reports
Do You Need a Paginated Report?
Architecture Best Practice: One Dataset to Rule Them All
The Power BI Report Builder
Summary
Chapter 15: Excel and Power BI Integration
The Analyze in Excel Feature
The Analyze in Excel Feature from the Power BI Service
Implicit Measures Don’t Work in Excel
Excel Is Connected Live to the Power BI Model in the Service
Getting Data from Power BI Dataset
Getting Data from a Power BI Dataflow
Why is Analyze in Excel Better than Using Export Data?
Analyze in Excel is Live
Export Data Is Limited to 150,000 Rows
Export Data Is Limited to Specific Visuals and Fields
Export Data Is Not Secure
Analyze in Excel Supports Row-Level Security
Analyze in Excel Uses Shared Datasets
Publish to Power BI from Excel
Upload Your Workbook to Power BI
Export Workbook Data to Power BI
Import Excel into the Power BI Desktop
Summary
Chapter 16: Real-Time Streaming Datasets in Power BI
Real-time vs. Live or DirectQuery
Sample Scenario
Capturing Inputs with a Microsoft Form
The Power BI Streaming Dataset
Power Automate Pushes Data to the Power BI Dataset
The Power BI Real-Time Dashboard
Streaming Dataset Types
Push Datasets
Streaming Datasets
Hybrid Datasets
Streaming Services
Power BI Streaming Dataset and REST API
Azure Stream Analytics
PubNub
Creating Calculations on Real-Time Datasets
Writing Calculations Using Q&A
Live Connection to a Streaming Dataset
Would it Be Real-Time?
DirectQuery to the Streaming Dataset: Even More Flexibility
Summary
Chapter 17: Performance Tuning with Aggregations
How Does Aggregation Work?
Aggregated Table
Layers of Aggregation
Step 1: Create the Aggregated Table
Sample Model
What Is an Aggregated Table?
Creating an Aggregated Table
Key Consideration for Upcoming Steps
An Aggregated Table Is an Import Table
Dual Storage Mode; The Most Important Configuration for Aggregations!
Step 2: Power BI Aggregations
What Is Storage Mode?
Import Data
DirectQuery
Query from Multiple DirectQuery Tables
Caution: Combining DirectQuery and Import
Dual Storage Mode
Step 3: Configure Aggregation Functions and Test Aggregations in Action
Hiding the Aggregated Table
Multiple Layers of Aggregations
Second Aggregation Layer
Precedence Setup
How about the Precedence of Execution?
Query Hits the First Aggregated Table in Memory
Query Hits the Second Aggregated Table in Memory
Query Hits the DirectQuery Table in the Source
Aggregation for Imported Data
DAX Measures Instead of Manage Aggregation
Automatic Aggregation
Summary
Chapter 18: Big Data with Incremental Refresh and Hybrid Tables
What Is Incremental Refresh?
Hybrid Tables
Configuring Incremental Refresh
Setting Up Incremental Refresh
Parameters in Power Query
Filter Data Based on the Parameters
Power BI Desktop Incremental Refresh Setup
Hybrid Table Setup
Incremental Refresh for Multiple Tables
Publish to Service
What Do the Partitions Look Like?
Detecting Data Changes
Only Refresh When Period Is Complete
Incremental Refresh for Dataflows or Datamarts
Summary
Chapter 19: Development Best Practices
Reuse Tables Generated in Power Query
Reuse DAX Calculations
Use Power Query Functions for Reusable Data Transformation
Parametrize the Data Transformation Process Using Power Query Parameters
Categorize Power Query Using Power Query Groups (or Folders)
Disable the Load of Temp Tables in Power Query
Only Load What You Need for Reporting: Filter the Data
Use Reference and Duplicate in Their Rightful Places
Multi-Layer Architecture Everywhere
Sync Slicers
Use a Theme File
Use a Background Image for Report Pages
Incorporate Conditional Formatting Using DAX, Parameters, and Tables
Create a Measure Table
Create and Use Hierarchies
Set Auto Summarization at the Field Level
Consider Using a Custom Date Table
Design Mobile Reports
Use Aggregations for Big Tables
Use Incremental Refresh and Hybrid Tables When Possible
Design Star-Schema Models
Avoid Both-Directional Relationships
Clean Up Your Models
Chapter 20: The Power BI Service
The Power BI Desktop: A Report Authoring Tool
Hosting Options for Power BI Reports
What Is the Power BI Service?
Publish Reports to the Service
Capacity-Based or User-Based
The Power BI Service
Template Apps
Apps
Workspaces
Dataflows
Shared Datasets
Datamarts
Deployment Pipelines
Dashboards
Metrics
Content Certification
Data Lineage
Summary
Chapter 21: The Power BI Report Server
What Is the Power BI Report Server?
Requirements for Setting It Up
Installing the Power BI Report Server
Configuring the Power BI Report Server
Database Set Up
Web URL Set Up
Web Service Set Up
Web Portal Set Up
Installing the Power BI Desktop Report Server
Developing Reports with the Power BI Report Server
Publish Report to the Report Server
Managing Datasets on the Report Server
Schedule Refresh Requirement
Pros and Cons of the Report Server
No Gateway Is Needed
All Types of Connections Are Supported
The Power BI Report Server Is a Fully On-Premises Solution
The Power BI Service Features Are Not Available
Licensing the Report Server
Summary
Chapter 22: Power BI Gateways
What Is a Gateway?
Do You Always Need a Gateway?
Types of Gateways
Personal Mode
On-Premises Standard Mode
Gateway Execution Flow
Important Points to Consider Before Installing a Gateway
How Many Gateways Are Required?
Installing Gateways
Adding Data Sources
Set Up a Gateway Connection to the Dataset
Users and Access Controls for Gateways
Gateway Installers
Gateway Users
Gateway Connection Creator
Gateway Connection Creator with Resharing
Gateway Admin
Data Source Users
Additional Settings for Gateway
Distribute Requests Across All Active Gateways in This Cluster
Allow User’s Cloud Data Sources to Refresh Through This Gateway Cluster
Allow User’s Custom Data Connectors to Refresh Through This Gateway Cluster
Troubleshooting
Summary
Chapter 23: Power BI Licensing
Two Types of Licensing
The Power BI Desktop
Why Do I Need a License if the Power BI Desktop Is Free?
User-Based Licensing
Power BI Free
Power BI Pro
Sharing
Developer Functions in the Service
Power BI Pro Licenses Are Designed for Developers
Power BI PPU: Premium Per User
Capacity-Based Licensing
Power BI Embedded
Power BI Premium
Sharing Content with Free Users
Extra Features in Premium
The Difference Between A SKUs and P/EM SKUs
SQL Server Enterprise Edition Plus Software Assurance
Summary
Chapter 24: Power BI Admin Portal and Tenant Settings
Power BI Administrator
Tenant Settings
Help and Support Settings
Workspace Settings
Information Protection
Export and Sharing Settings
External Users
Publish to Web
Export, Copy, Print, and Integrate Data
Certifications
Content Pack and App Settings
Push Apps to End Users
Integration Settings
Custom Visual Settings
Audit and Usage Settings
Create Audit Logs for Internal Activity Auditing and Compliance
Usage Metrics for Content Creators
Per-User Data in Usage Metrics for Content Creators
Developer Settings
Dataflow, Template Apps, and Datamarts
Usage Metrics
Embed Codes
Organization Visuals
Workspaces
Custom Branding
Summary
Chapter 25: PowerShell Cmdlets for Power BI
PowerShell
PowerShell Cmdlets for Power BI
Getting Started
Example: Export a List of All Workspaces in the Organization as a CSV File
Example: Deploy a Power BI Report from One Workspace to Another
Are PowerShell Cmdlets the Same as REST APIs in Power BI?
Summary
Chapter 26: Power BI REST API
The Power BI REST API
Getting Started
Calling the API from .NET
Embedding into a Custom Application
Power Automate
PowerShell to Call REST API
Summary
Chapter 27: Power BI Audit Log for the Tenant
The Problem
The Audit Log
Using PowerShell to Extract the Audit Log
The Power BI Report for the Audit Log
Summary
Chapter 28: XMLA Endpoint
Behind the Scenes of a Power BI Dataset
SSAS Is More Than What You See
Can I Access the SSAS Model of My Power BI Dataset?
XMLA Endpoint
How Does an XMLA Connection Work?
The XMLA Endpoint URL
Admin Control under Capacity Configuration
Client Tools
Sample Connection
The Power BI Helper
Summary
Chapter 29: Dashboard and Report Sharing
Power BI Content Owner
How Does Dashboard Sharing Work?
Three Levels of Access
Manage Permissions
Advantages of Dashboard Sharing
Disadvantages of Dashboard Sharing
No Edit Access
Share Objects One at a Time
Licensing for a Large User Base
Summary
Chapter 30: Power BI Workspaces
What Are Power BI Workspaces?
Creating Workspaces
Adding Content to a Workspace
Four Levels of Workspace Access
Tenant Admin Control for Workspaces
Advantages of Workspaces
Disadvantages of Workspaces
Summary
Chapter 31: Power BI Apps
Power BI Apps
App Workspaces
Creating an App
Setup
Installing Apps Automatically
Content
Audience
Getting Apps
Making Changes to an App
Isolated Environments for Developers and Users
What About Data Refresh?
Advantages of Power BI Apps
Cons of Power BI Apps
Summary
Chapter 32: Publish to Web
What Is Publish to Web?
Security Issues with Publish to Web
Removing Access
Central Monitoring for all Embed Codes
Who Can Publish Reports on the Web?
Differences with Other Sharing Methods
Summary
Chapter 33: Power BI Embedded
Using Power BI Embedded
Myths about Power BI Embedded
What Is Power BI Embedded?
Embedded Playground
Licensing Power BI Embedded
Some SKUs Can Be Turned On and Off
Power BI Users or Custom Application Users
Implementing Power BI Embedded
Summary
Chapter 34: Secure Embed
Why Another Embedding Method?
Publish to Web
Power BI Embedded
Secure Embed: Good Features from Both Worlds!
Using Secure Embed
Who Can View the Report?
Licensing Needs
Advantages of Secure Embed
Limitations
Scenarios for Using this Method
Summary
Chapter 35: Embed in SharePoint Online and Teams
How to Use Embed in SharePoint Online
Sharing a SharePoint Page with an Embedded Power BI Report
Access to SharePoint
Power BI Permissions
Advantages and Disadvantages of Embedding in SharePoint Online
Advantages of Embed in SharePoint Online
Disadvantages of Embed in SharePoint Online
Power BI and Teams
Power BI and PowerPoint
Summary
Chapter 36: Comparison of Sharing Methods for Power BI
Types of Sharing Methods
Basic Sharing for Dashboards and Reports
Advantages of Dashboard Sharing
Disadvantages of Dashboard Sharing
Workspaces
Advantages of Workspaces
Disadvantages of Workspaces
Power BI Apps
Advantages of Power BI Apps
Cons of Power BI Apps
The Publish to Web Option
Security Issues with Publish to Web
Embed in SharePoint Online
Advantages of Embed in SharePoint Online
Disadvantages of Embed in SharePoint Online
Power BI Embedded
Pros and Cons of Power BI Embedded
Secure Embed
Advantages of Secure Embed
Disadvantages and Limitations of Secure Embed
Summary
Cheat Sheet for Choosing a Method
Chapter 37: Types of Power BI Users
The Different Types of Power BI Users
Access Control
Training
The Layers and User Categories Are Different in Each Organization
Users Can Move Between Layers
Summary
Chapter 38: Best Practices for Setting Up Workspace Roles
The Modern Power BI Workspace
Roles in the Workspace
Viewer
Contributor
Member
Admin
Best Practices for Each Role
Viewer Role: Use Power BI Apps Instead
Contributor Role: Developers Only
Member Role: Deployment Group Only
Admin Role: Admin Group
Use Groups, Not Individual Accounts
Summary
Chapter 39: Build Access Level
Build Access Example
Are Build and Edit the Same? A Row-Level Security Example
Providing Build But Not Edit Access
Manage Permissions: Add Build Access
Create an App with Build Access
Who Should Have Build Access?
Licensing
Summary
Chapter 40: How to Organize Workspaces in a Power BI Environment
Considering a Power BI Workspace as a Single Development-Sharing Unit
Separating Audiences Using Power BI Apps
Separating Developers with Multiple Workspaces
Split the Load, Use the Capacity
Sharing Workspaces Among Multiple Developers
Layers of Shared Workspaces
Separating the Environments
Summary
Chapter 41: Content Certification and Endorsement
The Importance of Endorsement
Levels of Endorsement
What Kinds of Content Can Be Endorsed?
Who Can Endorse It?
Dataset Discoverability
An Example of an Endorsement and Content Certification
Summary
Chapter 42: Deployment Pipelines
Why Multiple Environments?
How Many Environments Do You Need?
What Is a Deployment Pipeline?
How Does a Deployment Pipeline Work?
Creating Deployment Pipelines
Assigning a Workspace
Comparing Content
Deploy
Deployment History
Rules and Connections
Automate the Deployment
What If There Is No Premium License?
Summary
Chapter 43: Data-Level Security
Introduction
Row-Level Security
Static Row-Level Security
Creating a Sample Report
Creating Roles
Testing Roles on the Desktop
Assigning Users to Roles in the Power BI Service
Testing Roles in the Power BI Service
Republishing Won’t Hurt
Dynamic Row-Level Security
Sample Data
Sample Report
The UserName() and UserPrincipalName() DAX Functions
Row-Level Security in the Power BI Desktop
Assigning Users to Power BI Security
Sharing the Report or Dashboard
Testing the Security
Column-Level Security
Object-Level Security
Page-Level Security
Summary
Chapter 44: Power BI Helper
Documenting a Power BI File and Report
Download and Install Power BI Helper for Free
Open Power BI Helper as an External Tool
Connect to Model
Visualization Information
Documentation
What Is Included in the Documentation?
Configurations
Configure the Style of the Documentation
Sections and Information to Document
Exporting the Data in a Power BI Table
Connect to Model
Export the Data
Any Size, Any Table Type
Consider Using Analyze in Excel
Reducing the Size of Power BI File
Steps to Reduce File Size
Step 1: Remove Large Columns
Step 2: Turning Off the Auto Date/Time
Step 3: Removing Unused Columns
Documenting Power BI Tenant Objects
Service Tenant Settings in Power BI Helper
Register the App
Set Up Permissions in the Azure Portal
Add the Application ID to Power BI Helper
Scan Service Objects
Configuring the Documentation’s Output
Configuring the Documentation’s Style
Sections and Information to Document
Export the Information as CSV Files (Export Metadata)
Exporting the Power BI Audit Log
What Is the Power BI Audit Log?
Difficulties Exporting the Audit Log
A Simple Way to Export the Audit Log Without Limitations
Logging In
Power BI File Cleanup in a Few Steps
Visualization Information
Removing Unused Fields
Hiding Technical Fields
Sample Output
Backup File
Sample Scenarios
Not Supported at Present
Summary
Index
df-Capture.PNG