Mastering Microsoft Power BI - Second Edition

Mastering Microsoft Power BI - Second Edition

by Greg Deckler, Brett Powell
Released June 2022
Publisher(s): Packt Publishing
ISBN: 9781801811484

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Plan, design, develop, and manage robust Power BI solutions to generate meaningful insights and make data-driven decisions. Purchase of the print or Kindle book includes a free eBook in the PDF format.

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. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Get in touch
  2. Planning Power BI Projects
    1. Power BI deployment modes
      1. Corporate BI
      2. Self-Service Visualization
      3. Self-Service BI
      4. Choosing a deployment mode
    2. Project discovery and ingestion
      1. Sample Power BI project template
        1. Sample template – Adventure Works BI
    3. Power BI project roles
      1. Dataset designer
      2. Report authors
      3. Power BI administrator
      4. Project role collaboration
    4. Power BI licenses
      1. Shared capacity
        1. Free
        2. Pro
      2. Dedicated capacity
        1. Premium
        2. Premium Per User
        3. Embedded
      3. Power BI license scenarios
    5. Dataset design
      1. Data warehouse bus matrix
      2. Dataset design process
        1. Select the business process
        2. Declare the grain
        3. Identify the dimensions
        4. Define the facts
    6. Data profiling
      1. Data profiling with Power BI Desktop
    7. Dataset planning
      1. Data transformations
      2. Import, DirectQuery, Live, and Composite datasets
        1. Import mode
        2. DirectQuery/Live mode
        3. Composite mode
    8. Sample project analysis
    9. Summary
  3. Preparing Data Sources
    1. Query folding
      1. Partial query folding
    2. Query design per dataset mode
      1. Import mode dataset queries
      2. DirectQuery dataset queries
        1. DirectQuery report execution
      3. Composite datasets
        1. Table storage modes
    3. Data sources
      1. Authentication
      2. Data source settings
      3. Privacy levels
      4. Power BI as a data source
      5. Power BI Desktop options
        1. Global options
        2. CURRENT FILE options
    4. SQL views
      1. SQL views versus M queries
      2. SQL view examples
        1. Date dimension view
        2. Mark as date table
        3. Product dimension view
      3. Slowly changing dimensions
    5. Summary
  4. Connecting to Sources and Transforming Data with M
    1. Types of Power Query M queries
      1. Organizing queries
      2. Data source parameters
      3. Staging queries
        1. DirectQuery staging
      4. Fact and dimension queries
        1. Source reference only
        2. Excel workbook – Annual Sales Plan
      5. Parameter tables queries
      6. Security table queries
      7. Custom function queries
    2. Creating Power Query M queries
      1. Numeric data types
      2. Item access in M
    3. Power Query M query examples
      1. Trailing three years filter
        1. Combining queries
        2. Incremental refresh for fact tables
        3. Customer history column
        4. Product dimension integration
        5. R and Python script transformation
    4. Dataflows
      1. Power BI Premium dataflow features
    5. Power Query M editing tools
      1. Advanced Editor
      2. Visual Studio Code
      3. Visual Studio
    6. Summary
  5. Designing Import, DirectQuery, and Composite Data Models
    1. Dataset layers
      1. Power BI as a superset of Azure Analysis Services
      2. Dataset objectives
        1. Competing objectives
        2. External factors
    2. The Data Model
      1. The Model view
      2. The Data view
      3. The Report view
      4. Fact tables
        1. Fact table columns
        2. Fact column data types
        3. Fact-to-dimension relationships
      5. Dimension tables
        1. Hierarchies
        2. Custom sort
      6. Parameter tables
        1. Measure groups
        2. Last refreshed date
        3. Measure support logic
        4. Display folders
    3. Relationships
      1. Uniqueness
      2. Ambiguity
      3. Single-direction relationships
        1. Direct flights only
      4. Bidirectional relationships
        1. Shared dimensions
        2. Date dimensions
      5. The CROSSFILTER function
    4. Model metadata
      1. Visibility
    5. Column and measure metadata
      1. Default summarization
        1. Data format
        2. Data category
      2. Description
    6. Optimizing data model performance
      1. Import
        1. Query caching
        2. Columnar compression
        3. Memory analysis via DMVs and the VertiPaq Analyzer
      2. DirectQuery
        1. Columnstore and HTAP
        2. Automatic aggregations
      3. Composite
        1. Aggregation tables
    7. Summary
  6. Developing DAX Measures and Security Roles
    1. DAX measure basics
      1. Filter context
        1. SQL equivalent
      2. Measure evaluation process
      3. Row context
      4. Scalar and table functions
        1. Related tables
      5. The CALCULATE() function
        1. The FILTER() function
      6. DAX variables
    2. Base measures
      1. Measure support expressions
        1. KPI targets
        2. Current and prior periods
    3. Date intelligence metrics
      1. Current versus prior and growth rates
      2. Rolling periods
    4. Calculation groups
    5. Dimension metrics
      1. Missing dimensions
    6. Ranking metrics
      1. Dynamic ranking measures
    7. Security roles
      1. Dynamic row-level security
    8. Performance testing
      1. Performance analyzer
      2. DAX Studio
    9. Summary
  7. Planning Power BI Reports
    1. Report planning process
      1. Identify the audience
      2. Define the business questions to answer
      3. Confirm that the dataset supports the business questions
      4. Determine interactivity
      5. Define access and distribution
      6. Sketch the report layout
      7. Report architecture diagram
    2. Visualization best practices
    3. Choosing the right visual
      1. Tables and matrices versus charts
      2. Chart selection
      3. Visualization anti-patterns
    4. Visual interactions
      1. Editing interactions
    5. Drillthrough report pages
      1. Custom labels and the back button
      2. Multi-column drillthrough
    6. Report filter scopes
      1. Report filter conditions
      2. Report and page filters
      3. Relative date filtering
      4. Visual-level filtering
        1. Top N visual-level filters
    7. Bookmarks
      1. Selection pane and the Spotlight property
      2. Custom report navigation
      3. View mode
    8. Live connections to Power BI datasets
      1. Customizing Live connection reports
      2. Switching Live source datasets
      3. Switching between import mode and Live mode datasets
    9. Report design summary
    10. Summary
  8. Creating and Formatting Visualizations
    1. The Visualizations pane
    2. Slicers
      1. Slicer synchronization
      2. Custom slicer parameters
      3. What-if parameters
      4. Page filter or slicer?
    3. Single-value visuals
      1. The Card visual
      2. The KPI visual
      3. Gauge visual
    4. Map visuals
      1. Bubble map
      2. Filled map
    5. Waterfall chart
    6. Power Platform visuals
      1. Power Apps for Power BI
      2. Power Automate for Power BI
    7. Premium visuals
      1. Scorecard
      2. Paginated reports
    8. Elements
    9. Formatting visualizations
      1. Tooltips
        1. Report page tooltips
      2. Column and line charts
        1. Column and line chart conditional formatting
      3. Table and matrix visuals
        1. Custom format strings
        2. Table and matrix conditional formatting
        3. Sparklines
        4. Values as rows
      4. Scatter charts
    10. Summary
  9. Applying Advanced Analytics
    1. AI visuals
      1. Key influencers
      2. Decomposition tree
      3. Q&A
      4. Smart narrative
    2. R and Python visuals
      1. R visual
      2. Python visual
    3. ArcGIS Maps for Power BI
    4. Custom visuals
      1. Adding a custom visual
    5. Animation and data storytelling
      1. Play axis for Scatter charts
      2. Pulse chart
    6. Analytics pane
      1. Trend line
      2. Forecasting
    7. Quick insights/Analyze
      1. Explain the increase/decrease
    8. Mobile-optimized report pages
    9. Summary
  10. Designing Dashboards
    1. Dashboards versus reports
    2. Dashboard design
      1. Visual selection
      2. Layout
        1. Navigation pane
        2. Fullscreen mode
      3. Supporting tiles
    3. Dashboard architectures
      1. Single-dashboard architecture
      2. Multiple-dashboard architecture
      3. Organizational dashboard architecture
      4. Multiple datasets
    4. Dashboard tiles
      1. Tile details and custom links
      2. Real-time data tiles
      3. Dashboard themes
      4. Paginated reports
      5. Excel workbooks
    5. Live report pages
    6. Mobile-optimized dashboards
    7. Summary
  11. Managing Workspaces and Content
    1. Workspaces
      1. Workspace roles and rights
        1. Viewer role
        2. Contributor role
        3. Member role
        4. Admin role
      2. Datasets across workspaces
      3. My workspace
    2. Staged deployments
      1. Workspace datasets
      2. Power BI REST API and PowerShell module
        1. Power BI REST API
        2. Power BI PowerShell module
        3. Workspace and content identifiers
        4. PowerShell sample scripts
      3. Power BI deployment pipelines
    3. Content sensitivity and protection
      1. Information protection
      2. Data loss prevention
    4. Version control
      1. OneDrive for Business
      2. Source control for M and DAX code
        1. MSHGQM
    5. Metadata management
      1. Field descriptions
        1. Creating descriptions
        2. View field descriptions
      2. Metadata Mechanic
    6. Metadata reporting
      1. Standard metadata reports
        1. Server and database parameters
        2. Querying the DMVs from Power BI
        3. Integrating and enhancing DMV data
        4. Metadata report pages
    7. Summary
  12. Managing the On-Premises Data Gateway
    1. On-premises data gateway planning
      1. Top gateway planning tasks
        1. Determining whether a gateway is needed
        2. Identifying where the gateway should be installed
        3. Defining the gateway infrastructure and hardware requirements
        4. Defining gateway roles and permissions
        5. Planning for recovery keys
      2. Standard versus personal mode
    2. Gateway concepts
      1. Gateway clusters
      2. Gateway architectures
      3. Gateway security
    3. Gateway installation and configuration
      1. The gateway service account
      2. TCP versus HTTPS mode
      3. Connectors
      4. Recovery Keys
    4. Managing gateway clusters
      1. Gateway administrators
      2. Gateway data sources and users
      3. PowerShell support for gateway clusters
    5. Troubleshooting and monitoring gateways
      1. Restoring, migrating, and taking over a gateway
      2. Gateway diagnostics
      3. Gateway monitoring reports
    6. Data refresh
      1. Scheduled data refresh
      2. DirectQuery datasets
      3. Live connections to Analysis Services models
      4. Dashboard cache refresh
    7. Summary
  13. Deploying Paginated Reports
    1. Paginated reports in the Power BI service
      1. Planning paginated reports
      2. Building and publishing paginated reports
      3. Identifying and interacting with paginated reports
        1. Printing, exporting, subscribing, and sharing
    2. Migrating reports to the Power BI service
      1. Inventory
      2. Assess
      3. Plan
      4. Migrate
      5. User Acceptance Testing and final deployment
    3. Planning the Power BI Report Server (PBRS)
      1. Feature differences with the Power BI service
      2. Parity with SQL Server Reporting Services
      3. Data sources and connectivity options
      4. Hardware and user licensing
        1. Pro licenses for report authors
      5. Alternative and hybrid deployment models
        1. PBRS reference topology
        2. Scale PBRS
    4. Installing and upgrading PBRS
      1. Retrieve the PBRS product key
      2. Upgrade cycles
    5. PBRS client applications
      1. Running desktop versions side by side
      2. Power BI mobile applications
    6. Summary
  14. Creating Power BI Apps and Content Distribution
    1. Content distribution methods
    2. Power BI apps
      1. Licensing apps
      2. App deployment process
      3. User permissions and security
      4. Publishing apps
      5. Installing apps
      6. App updates
      7. Apps on Power BI mobile
    3. Sharing content
      1. Sharing scopes
      2. Sharing versus Power BI apps
    4. Embedding
      1. Licensing embedding
      2. Publish to web
      3. Secure URL embedding
      4. Microsoft 365 apps
        1. Teams
        2. SharePoint Online
      5. Custom applications
    5. Data alerts
      1. Power Automate integration
    6. Email subscriptions
    7. Analyze in Excel
    8. Self-service BI workspaces
      1. Self-service content distribution
      2. Risks of self-service BI
    9. Summary
  15. Administering Power BI for an Organization
    1. Power BI administrator role
    2. Data governance for Power BI
      1. Implementing data governance
    3. Azure Active Directory
      1. AAD B2B collaboration
        1. Licensing external users
      2. Conditional access policies
    4. Power BI admin portal
      1. Tenant settings
      2. Usage metrics
      3. Users and Audit logs
      4. Premium Per User
      5. Capacity settings
      6. Embed codes
      7. Organizational visuals
      8. Azure connections
      9. Workspaces
      10. Custom branding
      11. Protection metrics
      12. Featured content
    5. Usage metrics reports
    6. Audit logs
      1. Audit log monitoring solution
    7. The Power BI REST API for admins
    8. Summary
  16. Building Enterprise BI with Power BI Premium
    1. Power BI Premium
      1. Power BI Premium capabilities
    2. Premium capacity nodes
      1. Frontend versus backend resources
    3. Premium capacity estimations
    4. Premium capacity administration and allocation
      1. Capacity allocation
        1. Corporate and Self-Service BI capacity
      2. Create, size, and monitor capacities
        1. Changing capacity size
        2. Monitoring Premium capacities
      3. Workspace assignment
    5. Premium capacity resource optimization
      1. Data model optimizations
      2. Report and visualization optimizations
      3. Workloads
    6. Life cycle management with Premium
      1. ALM Toolkit deployment
      2. Dataset management with SSMS
      3. Backing up Premium capacities
    7. Summary
  17. Other Books You May Enjoy
  18. Index

Product information

  • Title: Mastering Microsoft Power BI - Second Edition
  • Author(s): Greg Deckler, Brett Powell
  • Release date: June 2022
  • Publisher(s): Packt Publishing
  • ISBN: 9781801811484