Microsoft Power BI Complete Reference电子书

Title Page

Copyright

Microsoft Power BI Complete Reference

About Packt

Why subscribe?

PacktPub.com

Contributors

About the authors

About the reviewers

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Conventions used

Get in touch

Reviews

Getting Started with Importing Data Options

Getting started

Importing data

Excel as a source

SQL Server as a source

Web as a source

DirectQuery

Limitations

Live Connection

Limitations

Which should I choose?

Summary

Data Transformation Strategies

The Power Query Editor

Transform basics

Use First Row as Headers

Remove Columns

Change type

Add Column From Examples

Advanced data transformation options

Conditional Columns

Fill Down

Unpivot

Merging Queries

Appending Queries

Leveraging R

Installation and configuration

The R Script transform

M formula language

#shared

Summary

Building the Data Model

Building relationships

Editing relationships

Creating a new relationship

Working with complex relationships

Many-to-many relationships

Cross-filtering direction

Enabling filtering from the many side of a relationship

Role-playing tables

Importing the date table

Usability enhancements

Hiding tables and columns

Renaming tables and columns

Default summarization

How to display one column but sort by another

Data categorization

Creating hierarchies

Summary

Leveraging DAX

Building calculated columns

String functions – Month, Year

Format function – Month Year

Age calculation

SWITCH() – age breakdown

Navigation functions – RELATED

Calculated measures – the basics

Calculated measure – basic aggregations

Total Sales

Total Cost

Profit

Profit Margin

Optional parameters

Filter context

Calculate

Percentage of total calculation

Time intelligence

Year to Date Sales

YTD Sales (Fiscal Calendar)

Prior Year Sales

Summary

Visualizing Data

Data visualization basics

Visuals for filtering

Interactive filtering

The Slicer visual

Visualizing tabular data

The table visual

The Matrix visual

Visualizing categorical data

Bar and Column charts

Pie and Donut charts

The Treemap visual

The Scatter chart

Visualizing trend data

Line and Area charts

Combo charts

The Ribbon Chart

The Waterfall Chart

The Funnel Chart

Visualizing KPI data

The Gauge visual

The KPI visual

Visualizing geographical data

The Map visual

The Filled Map visual

The Shape Map visual

The ArcGIS Map visual

Leveraging Power BI custom visuals

Data visualization tips and tricks

Edit interactions

The Analytics pane

The Top N filter

Show value as

Summary

Using a Cloud Deployment with the Power BI Service

Deploying to the Power BI service

DATASETS

WORKBOOKS

Creating and interacting with dashboards

Creating your first dashboard

Asking your dashboard a question

Subscribing to reports and dashboards

Sharing your dashboards

Workspaces

Setting up row-level security

Scheduling data refreshes

Summary

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 admin

Project role collaboration

Power BI licenses

Power BI license scenarios

Power BI Premium features

Data warehouse bus matrix

Dataset design process

Selecting the business process

Declaring the grain

Identifying the dimensions

Defining the facts

Data profiling

Dataset planning

Data transformations

DirectQuery mode

Sample project analysis

Summary

Connecting to Sources and Transforming Data with M

Query design per dataset mode

Import mode dataset queries

DirectQuery dataset queries

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

M queries

Data Source Parameters

Staging Queries

DirectQuery staging

Fact and dimension queries

Source Reference Only

M query summary

Excel workbook – Annual Sales Plan

Data types

Item access in M

DirectQuery report execution

Bridge Tables Queries

Parameter Tables

Security Tables

Query folding

Partial query folding

M Query examples

Trailing three years filter

Customer history column

Derived column data types

Product dimension integration

M editing tools

Advanced Editor

Visual Studio Code

Visual Studio

Summary

Designing Import and DirectQuery Data Models

Dataset layers

Dataset objectives

Competing objectives

External factors

The Data Model

The Relationships View

The Data View

The Report View

Fact tables

Fact table columns

Fact column data types

Fact-to-dimension relationships

Bridge tables

Parameter tables

Measure groups

Last refreshed date

Measure support logic

Relationships

Uniqueness

Ambiguity

Model metadata

Visibility

Column metadata

Default Summarization

Data format

Data category

Field descriptions

Optimizing performance

Import

Columnar compression

Memory analysis via DMVs

DirectQuery

Optimized DAX functions

Columnstore and HTAP

Summary

Developing DAX Measures and Security Roles

DAX measures

Measure evaluation process

Row context

Scalar and table functions

The CALCULATE() function

Related tables

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

Dimension metrics

Missing dimensions

Ranking metrics

Dynamic ranking measures

Security roles

Dynamic row-level security

Performance testing

DAX Studio

Tracing a Power BI dataset via DAX Studio

Summary

Creating and Formatting Power BI Reports

Report planning

Power BI report architecture

Live connections to Power BI datasets

Customizing Live connection reports

Switching source datasets

Visualization best practices

Visualization anti-patterns

Choosing the visual

Tables versus charts

Chart selection

Visual interactions

Edit interactions

What-if parameters

Slicers

Slicer synchronization

Custom slicer parameters

Report filter scopes

Report filter conditions

Report and page filters

Page filter or slicer?

Relative date filtering

Visualization formatting

Visual-level formatting

Line and column charts

Tooltips

Report page tooltips

Column and line chart conditional formatting

Column chart conditional formatting

Line chart conditional formatting

Table and matrix

Table and matrix conditional formatting

Values as rows

Scatter charts

Map visuals

Bubble map

Filled map

Mobile-optimized reports

Responsive visuals

Report design summary

Summary

Applying Custom Visuals, Animation, and Analytics

Drillthrough report pages

Custom labels and the back button

Multi-column drillthrough

Bookmarks

Selection pane and the Spotlight property

Custom report navigation

View mode

ArcGIS Map visual for Power BI

ArcGIS Maps Plus subscriptions

Waterfall chart breakdown

Analytics pane

Trend Line

Forecast line

Quick Insights

Explain the increase/decrease

Custom visuals

Adding a custom visual

Power KPI visual

Chiclet Slicer

Impact Bubble Chart

Dot Plot by Maq Software

Animation and data storytelling

Play axis for scatter charts

Pulse Chart

Summary

Designing Power BI Dashboards and Architectures

Dashboards versus reports

Multi-dashboard architectures

Single-dashboard architecture

Multiple-dashboard architecture

Organizational dashboard architecture

Multiple datasets

Dashboard tiles

Tile details and custom links

Images and text boxes

SQL Server Reporting Services

Excel workbooks

Live report pages

Mobile-optimized dashboards

Summary

Managing Application Workspaces and Content

Application workspaces

Workspace roles and rights

Workspace admins

Workspace members

My Workspace

Staged deployments

Workspace datasets

Power BI REST API

Client application ID

Workspace and content IDs

PowerShell sample scripts

Dashboard data classifications

Version control

OneDrive for Business version history

Source control for M and DAX code

Metadata management

Field descriptions

Creating descriptions

View field descriptions

Metadata reporting

Query field descriptions

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

On-premises data gateway versus personal mode

Gateway clusters

Gateway architectures

Gateway security

Gateway configuration

The gateway service account

TCP versus HTTPS mode

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 log files

Performance Monitor counters

DirectQuery datasets

Single sign-on to DirectQuery sources via Kerberos

Live connections to Analysis Services models

Azure Analysis Services refresh

Dashboard cache refresh

Summary

Deploying the Power BI Report Server

Planning for the Power BI Report Server

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

Report Server reference topology

Installation

Hardware and software requirements

Analysis Services Integrated

Retrieve the Report Server product key

Migrating from SQL Server Reporting Services

Configuration

Service Account

Remote Report Server Database

Office Online Server for Excel Workbooks

Upgrade cycles

Report Server Desktop Application

Running desktop versions side by side

Report Server Web Portal

Scheduled data refresh

Data source authentication

Power BI mobile applications

Report server administration

Securing Power BI report content

Execution logs

Scale Power BI Report Server

Summary

Creating Power BI Apps and Content Distribution

Content distribution methods

Power BI apps

Licensing apps

App deployment process

User permissions

Publishing apps

Installing apps

Apps on Power BI mobile

App updates

Dataset-to-workspace relationship

Self-Service BI workspace

Self-Service content distribution

Risks to Self-Service BI

Sharing dashboards and reports

Sharing scopes

Sharing versus Power BI apps

SharePoint Online embedding

Custom application embedding

Publish to web

Data alerts

Microsoft Flow integration

Email Subscriptions

Analyze in Excel

Power BI Publisher for Excel

Summary

Administering Power BI for an Organization

Data governance for Power BI

Implementing data governance

Azure Active Directory

Azure AD B2B collaboration

Licensing external users

Conditional access policies

Power BI Admin Portal

Usage metrics

Users and Audit logs

Tenant settings

Embed Codes

Organizational Custom visuals

Usage metrics reports

Audit logs

Audit log monitoring solutions

Audit logs solution template

Power BI Premium capacities

Capacity allocation

Create, size, and monitor capacities

Change capacity size

Monitor premium capacities

App workspace assignment

Capacity admins

Summary

Scaling with Premium and Analysis Services

Power BI Premium

Power BI Premium capabilities

Corporate Power BI datasets

Limitation of Corporate BI datasets – Reusability

Premium capacity nodes

Frontend versus backend resources

Power BI Premium capacity allocation

Corporate and Self-Service BI capacity

Power BI Premium resource utilization

Data model optimizations

Report and visualization optimizations

Premium capacity estimations

Analysis Services

Analysis Services Models versus Power BI Desktop

Scale

Usability

Development and management tools

Azure Analysis Services versus SSAS

SSAS to Azure AS Migration

Provision Azure Analysis Services

Migration of Power BI Desktop to Analysis Services

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think