Mastering Microsoft Power BI

Mastering Microsoft Power BI

by Brett Powell
Released March 2018
Publisher(s): Packt Publishing
ISBN: 9781788297233

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

Design, create and manage robust Power BI solutions to gain meaningful business insights

Key Features

  • Master all the dashboarding and reporting features of Microsoft Power BI
  • Combine data from multiple sources, create stunning visualizations and publish your reports across multiple platforms
  • A comprehensive guide with real-world use cases and examples demonstrating how you can get the best out of Microsoft Power BI

Book Description

This book is intended for business intelligence professionals responsible for the design and development of Power BI content as well as managers, architects and administrators who oversee Power BI projects and deployments. The chapters flow from the planning of a Power BI project through the development and distribution of content to the administration of Power BI for an organization.

BI developers will learn how to create sustainable and impactful Power BI datasets, reports, and dashboards. This includes connecting to data sources, shaping and enhancing source data, and developing an analytical data model. Additionally, top report and dashboard design practices are described using features such as Bookmarks and the Power KPI visual.

BI managers will learn how Power BI’s tools work together such as with the On-premises data gateway and how content can be staged and securely distributed via Apps. Additionally, both the Power BI Report Server and Power BI Premium are reviewed.

By the end of this book, you will be confident in creating effective charts, tables, reports or dashboards for any kind of data using the tools and techniques in Microsoft Power BI.

What you will learn

  • Build efficient data retrieval and transformation processes with the Power Query M Language
  • Design scalable, user-friendly DirectQuery and Import Data Models
  • Develop visually rich, immersive, and interactive reports and dashboards
  • Maintain version control and stage deployments across development, test, and production environments
  • Manage and monitor the Power BI Service and the On-premises data gateway
  • Develop a fully on-premise solution with the Power BI Report Server
  • Scale up a Power BI solution via Power BI Premium capacity and migration to Azure Analysis Services or SQL Server Analysis Services

Who this book is for

Business Intelligence professionals and existing Power BI users looking to master Power BI for all their data visualization and dashboarding needs will find this book to be useful. While understanding of the basic BI concepts is required, some exposure to Microsoft Power BI will be helpful.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Mastering Microsoft Power BI
  3. Dedication
  4. Packt Upsell
    1. Why subscribe?
    2. PacktPub.com
  5. Contributors
    1. About the author
    2. About the reviewer
    3. Packt is searching for authors like you
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  7. 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 admin
      4. Project role collaboration
    4. Power BI licenses
      1. Power BI license scenarios
        1. Power BI Premium features
    5. Data warehouse bus matrix
    6. Dataset design process
      1. Selecting the business process
      2. Declaring the grain
      3. Identifying the dimensions
      4. Defining the facts
    7. Data profiling
    8. Dataset planning
      1. Data transformations
      2. Import versus DirectQuery
        1. Import mode
        2. DirectQuery mode
    9. Sample project analysis
    10. Summary
  8. Connecting to Sources and Transforming Data with M
    1. Query design per dataset mode
      1. Import mode dataset queries
      2. DirectQuery dataset queries
    2. 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
    3. 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
    4. M queries
      1. Data Source Parameters
      2. Staging Queries
        1. DirectQuery staging
      3. Fact and dimension queries
        1. Source Reference Only
      4. M query summary
        1. Excel workbook – Annual Sales Plan
      5. Data types
        1. Item access in M
        2. DirectQuery report execution
      6. Bridge Tables Queries
      7. Parameter Tables
      8. Security Tables
    5. Query folding
      1. Partial query folding
    6. M Query examples
      1. Trailing three years filter
      2. Customer history column
        1. Derived column data types
      3. Product dimension integration
      4. R script transformation
    7. M editing tools
      1. Advanced Editor
      2. Visual Studio Code
      3. Visual Studio
    8. Summary
  9. Designing Import and DirectQuery Data Models
    1. Dataset layers
      1. Dataset objectives
        1. Competing objectives
        2. External factors
    2. The Data Model
      1. The Relationships 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. Bridge tables
      7. Parameter tables
        1. Measure groups
        2. Last refreshed date
        3. Measure support logic
    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
      2. Column metadata
        1. Default Summarization
        2. Data format
        3. Data category
      3. Field descriptions
    5. Optimizing performance
      1. Import
        1. Columnar compression
        2. Memory analysis via DMVs
      2. DirectQuery
        1. Optimized DAX functions
        2. Columnstore and HTAP
    6. Summary
  10. Developing DAX Measures and Security Roles
    1. DAX measures
      1. Filter context
        1. SQL equivalent
      2. Measure evaluation process
      3. Row context
      4. Scalar and table functions
      5. The CALCULATE() function
        1. Related tables
        2. 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. Dimension metrics
      1. Missing dimensions
    5. Ranking metrics
      1. Dynamic ranking measures
    6. Security roles
      1. Dynamic row-level security
    7. Performance testing
      1. DAX Studio
        1. Tracing a Power BI dataset via DAX Studio
    8. Summary
  11. Creating and Formatting Power BI Reports
    1. Report planning
      1. Power BI report architecture
    2. Live connections to Power BI datasets
      1. Customizing Live connection reports
      2. Switching source datasets
    3. Visualization best practices
      1. Visualization anti-patterns
    4. Choosing the visual
      1. Tables versus charts
      2. Chart selection
    5. Visual interactions
      1. Edit interactions
      2. What-if parameters
    6. Slicers
      1. Slicer synchronization
      2. Custom slicer parameters
    7. Report filter scopes
      1. Report filter conditions
      2. Report and page filters
        1. Page filter or slicer?
      3. Relative date filtering
      4. Visual-level filtering
        1. Top N visual-level filters
    8. Visualization formatting
      1. Visual-level formatting
      2. Line and column charts
      3. Tooltips
        1. Report page tooltips
      4. Column and line chart conditional formatting
        1. Column chart conditional formatting
        2. Line chart conditional formatting
      5. Table and matrix
        1. Table and matrix conditional formatting
        2. Values as rows
      6. Scatter charts
    9. Map visuals
      1. Bubble map
      2. Filled map
    10. Mobile-optimized reports
      1. Responsive visuals
    11. Report design summary
    12. Summary
  12. Applying Custom Visuals, Animation, and Analytics
    1. Drillthrough report pages
      1. Custom labels and the back button
      2. Multi-column drillthrough
    2. Bookmarks
      1. Selection pane and the Spotlight property
      2. Custom report navigation
      3. View mode
    3. ArcGIS Map visual for Power BI
      1. ArcGIS Maps Plus subscriptions
    4. Waterfall chart breakdown
    5. Analytics pane
      1. Trend Line
      2. Forecast line
    6. Quick Insights
      1. Explain the increase/decrease
    7. Custom visuals
      1. Adding a custom visual
      2. Power KPI visual
      3. Chiclet Slicer
      4. Impact Bubble Chart
      5. Dot Plot by Maq Software
    8. Animation and data storytelling
      1. Play axis for scatter charts
      2. Pulse Chart
    9. Summary
  13. Designing Power BI Dashboards and Architectures
    1. Dashboards versus reports
    2. Dashboard design
      1. Visual selection
      2. Layout
        1. Navigation pane
        2. Full screen mode
      3. Supporting tiles
      4. Custom date filters
    3. Multi-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. Images and text boxes
      3. SQL Server Reporting Services
      4. Excel workbooks
    5. Live report pages
    6. Mobile-optimized dashboards
    7. Summary
  14. Managing Application Workspaces and Content
    1. Application workspaces
      1. Workspace roles and rights
        1. Workspace admins
        2. Workspace members
      2. My Workspace
    2. Staged deployments
      1. Workspace datasets
      2. Power BI REST API
        1. Client application ID
        2. Workspace and content IDs
        3. PowerShell sample scripts
    3. Dashboard data classifications
    4. Version control
      1. OneDrive for Business version history
      2. Source control for M and DAX code
    5. Metadata management
      1. Field descriptions
        1. Creating descriptions
        2. View field descriptions
      2. Metadata reporting
        1. Query field descriptions
        2. 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
    6. Summary
  15. 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
      2. On-premises data gateway versus personal mode
    2. Gateway clusters
    3. Gateway architectures
    4. Gateway security
    5. Gateway configuration
      1. The gateway service account
      2. TCP versus HTTPS mode
    6. Managing gateway clusters
      1. Gateway administrators
      2. Gateway data sources and users
      3. PowerShell support for gateway clusters
    7. Troubleshooting and monitoring gateways
      1. Restoring, migrating, and taking over a gateway
      2. Gateway log files
      3. Performance Monitor counters
    8. Scheduled data refresh
    9. DirectQuery datasets
      1. Single sign-on to DirectQuery sources via Kerberos
    10. Live connections to Analysis Services models
      1. Azure Analysis Services refresh
    11. Dashboard cache refresh
    12. Summary
  16. Deploying the Power BI Report Server
    1. Planning for the Power BI Report Server
      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
      6. Report Server reference topology
    2. Installation
      1. Hardware and software requirements
        1. Analysis Services Integrated
      2. Retrieve the Report Server product key
      3. Migrating from SQL Server Reporting Services
    3. Configuration
      1. Service Account
      2. Remote Report Server Database
      3. Office Online Server for Excel Workbooks
    4. Upgrade cycles
    5. Report Server Desktop Application
      1. Running desktop versions side by side
    6. Report Server Web Portal
      1. Scheduled data refresh
      2. Data source authentication
    7. Power BI mobile applications
    8. Report server administration
      1. Securing Power BI report content
      2. Execution logs
    9. Scale Power BI Report Server
    10. Summary
  17. 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
      4. Publishing apps
      5. Installing apps
      6. Apps on Power BI mobile
      7. App updates
      8. Dataset-to-workspace relationship
    3. Self-Service BI workspace
      1. Self-Service content distribution
      2. Risks to Self-Service BI
    4. Sharing dashboards and reports
      1. Sharing scopes
      2. Sharing versus Power BI apps
    5. SharePoint Online embedding
    6. Custom application embedding
    7. Publish to web
    8. Data alerts
      1. Microsoft Flow integration
    9. Email Subscriptions
    10. Analyze in Excel
      1. Power BI Publisher for Excel
    11. Summary
  18. Administering Power BI for an Organization
    1. Data governance for Power BI
      1. Implementing data governance
    2. Azure Active Directory
      1. Azure AD B2B collaboration
        1. Licensing external users
      2. Conditional access policies
    3. Power BI Admin Portal
      1. Usage metrics
      2. Users and Audit logs
      3. Tenant settings
      4. Embed Codes
      5. Organizational Custom visuals
    4. Usage metrics reports
    5. Audit logs
      1. Audit log monitoring solutions
      2. Audit logs solution template
    6. Power BI Premium capacities
      1. Capacity allocation
      2. Create, size, and monitor capacities
        1. Change capacity size
        2. Monitor premium capacities
      3. App workspace assignment
      4. Capacity admins
    7. Summary
  19. Scaling with Premium and Analysis Services
    1. Power BI Premium
      1. Power BI Premium capabilities
      2. Corporate Power BI datasets
        1. Limitation of Corporate BI datasets – Reusability
      3. Premium capacity nodes
        1. Frontend versus backend resources
    2. Power BI Premium capacity allocation
      1. Corporate and Self-Service BI capacity
      2. Power BI Premium resource utilization
        1. Data model optimizations
        2. Report and visualization optimizations
    3. Premium capacity estimations
    4. Analysis Services
      1. Analysis Services Models versus Power BI Desktop
        1. Scale
        2. Usability
        3. Development and management tools
      2. Azure Analysis Services versus SSAS
        1. SSAS to Azure AS Migration
      3. Provision Azure Analysis Services
    5. Migration of Power BI Desktop to Analysis Services
    6. Summary
  20. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Mastering Microsoft Power BI
  • Author(s): Brett Powell
  • Release date: March 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781788297233