Microsoft Power BI Cookbook

Microsoft Power BI Cookbook

by Brett Powell
Released September 2017
Publisher(s): Packt Publishing
ISBN: 9781788290142

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

Get more out of Microsoft Power BI turning your data into actionable insights

About This Book

  • From connecting to your data sources to developing and deploying immersive, mobile-ready dashboards and visualizations, this book covers it all
  • Over 90 hands-on, technical recipes, tips, and use cases from across the Power BI platform including the Power BI Service and Mobile Applications
  • Proven development techniques and guidance for implementing custom solutions with DAX and M languages

Who This Book Is For

This book is for BI professionals who wish to enhance their knowledge of Power BI beyond and to enhance the value of the Power BI solutions they deliver to business users. Those who are looking at quick solutions to common problems while using Power BI will also find this book to be a very useful resource .Some experience with Power BI will be useful.

What You Will Learn

  • Cleanse, stage, and integrate your data sources with Power BI
  • Abstract data complexities and provide users with intuitive, self-service BI capabilities
  • Build business logic and analysis into your solutions via the DAX programming language and dynamic, dashboard-ready calculations
  • Take advantage of the analytics and predictive capabilities of Power BI
  • Make your solutions more dynamic and user specific and/or defined including use cases of parameters, functions, and row level security
  • Understand the differences and implications of DirectQuery, Live Connections, and Import-Mode Power BI datasets and how to deploy content to the Power BI Service and schedule refreshes
  • Integrate other Microsoft data tools such as Excel and SQL Server Reporting Services into your Power BI solution

In Detail

Microsoft Power BI is a business intelligence and analytics platform consisting of applications and services designed to provide coherent, visual and interactive insights of data.

This book will provide thorough, technical examples of using all primary Power BI tools and features as well as demonstrate high impact end-to-end solutions that leverage and integrate these technologies and services. Get familiar with Power BI development tools and services, go deep into the data connectivity and transformation, modeling, visualization and analytical capabilities of Power BI, and see Power BI’s functional programming languages of DAX and M come alive to deliver powerful solutions to address common, challenging scenarios in business intelligence.

This book will excite and empower you to get more out of Power BI via detailed recipes, advanced design and development tips, and guidance on enhancing existing Power BI projects.

Style and approach

This book consists of practical recipes on Power BI that target novices as well as intermediate Power BI users. It goes deep into the technical issues, covers additional protocols, and many more real-live examples.

Table of contents

  1. Preface
    1. What this book covers
    2. What you need for this book
    3. Who this book is for
    4. Conventions
    5. Reader feedback
    6. Customer support
      1. Downloading the example code
      2. Downloading the color images of this book
      3. Errata
      4. Piracy
      5. Questions
  2. Configuring Power BI Development Tools
    1. Introduction
    2. Configuring Power BI Desktop options and settings
      1. Getting ready
      2. How to do it...
        1. Installing and running Power BI Desktop
        2. Configuring Power BI Desktop options
      3. How it works...
      4. There's more...
      5. See also
        1. Power BI's advantages over Excel
        2. Power BI Security and Data Source Privacy
    3. Installing the On-Premises Data Gateway
      1. Getting ready
        1. Hardware and network configuration
      2. How to do it...
        1. Installation of on-premises gateway
      3. How it works...
        1. Gateway recovery key
      4. There's more...
      5. See also...
    4. Installing Power BI Publisher for Excel
      1. How to do it...
        1. Installation of Power BI Publisher for Excel
      2. There's more...
    5. Installing and Configuring DAX Studio
      1. How to do it...
        1. Installation of DAX Studio
        2. Configuration of DAX Studio
      2. How it works...
      3. There's more...
        1. Guy in a Cube video channel
  3. Accessing and Retrieving Data
    1. Introduction
    2. Viewing and analyzing M functions
      1. Getting ready
      2. How to do it...
        1. Formula Bar
        2. Advanced Editor window
      3. How it works...
        1. Query folding
        2. M query structure
        3. Lazy evaluation
      4. There's more...
        1. Partial query folding
        2. Limitations of query folding
      5. See also...
        1. M language references
    3. Establishing and managing connections to data sources
      1. Getting ready
      2. How to do it...
        1. Isolate data sources from individual queries
          1. Query groups
        2. Manage source credentials and privacy levels
      3. How it works...
        1. Data Source settings
        2. Data source privacy settings
      4. There's more...
      5. See also
    4. Building source queries for DirectQuery models
      1. Getting ready
      2. How to do it...
        1. Applying M transformations with DirectQuery models
      3. How it works...
      4. There's more...
        1. DirectQuery project candidates
        2. DirectQuery performance
      5. See also
    5. Importing data to Power BI Desktop models
      1. How to do it...
        1. Denormalize a dimension
        2. Provide automatic sorting
      2. How it works...
      3. There's more...
        1. One GB dataset limit and Power BI Premium
      4. See also
    6. Applying multiple filtering conditions
      1. Getting ready
      2. How to do it...
        1. Query filter example steps
      3. How it works...
      4. There's more...
        1. Filtering via the Query Editor interface
      5. See also
    7. Choosing columns and column names
      1. How to do it...
        1. Identify expensive columns
        2. Select columns
        3. Rename columns
      2. How it works...
        1. Column memory usage
      3. There's more...
        1. Fact table column eliminations
        2. Column orders
      4. See also
    8. Transforming and cleansing source data
      1. Getting ready
      2. How to do it...
        1. Remove duplicates
        2. Update a column through a join
      3. There's more...
      4. See also
    9. Creating custom and conditional columns
      1. How to do it...
        1. Create a dynamic banding attribute
        2. Create a formatted name column
        3. Comparing the current and previous rows
      2. How it works...
        1. Conditional expression syntax
        2. Case sensitivity
        3. Conditional expression evaluation
        4. Query folding of custom columns
      3. There's more...
        1. Add column from example
        2. Conditional columns interface
        3. DAX calculated columns
        4. Error handling and comments
    10. Integrating multiple queries
      1. Getting ready
      2. How to do it...
        1. Consolidate files
        2. Self-joining querying
      3. How it works...
        1. Nested join versus flat join
        2. Append multiple files
      4. There's more...
        1. Combine binaries
        2. Staging queries versus inline queries
      5. See also
    11. Choosing column data types
      1. How to do it...
        1. Remove automatic type detection steps
        2. Align relationship column data types
        3. Add numeric columns from text columns
        4. Use fixed decimal number for precision
      2. How it works...
        1. Automatic data type detection
        2. Numeric data types
        3. Power BI Desktop automatic time intelligence
      3. There's more...
        1. Data type impacts
        2. Date with locale
        3. Percentage data type
      4. See also
    12. Visualizing the M library
      1. How to do it...
      2. How it works...
      3. There's more...
  4. Building a Power BI Data Model
    1. Introduction
    2. Designing a multi fact data model
      1. Getting ready
        1. Setting business expectations
      2. How to do it...
        1. Four-step dimensional design process
          1. Data warehouse and implementation bus matrix
        2. Choose the dataset storage mode - Import or DirectQuery
          1. In-Memory mode
          2. DirectQuery mode
      3. How it works...
        1. DAX formula and storage engine
      4. There's more...
        1. Project ingestion questions
        2. Power BI delivery approaches
      5. See also
    3. Implementing a multi fact data model
      1. How to do it...
        1. SQL view layer
        2. M queries in Power BI Desktop
        3. Create model relationships
        4. Author DAX measures
        5. Configure model metadata
      2. There's more...
        1. Shared views
    4. Handling one-to-many and many-to-many relationships
      1. Getting ready
      2. How to do it...
        1. Single, bidirectional, and CROSSFILTER()
          1. Single direction relationships
          2. Bidirectional relationship
          3. CROSSFILTER() Measure
        2. Many-to-many relationships
          1. Bidirectional cross-filtering for many-to-many
      3. How it works...
        1. Ambiguous relationships
        2. CROSSFILTER()
      4. There's more...
        1. DirectQuery supported
      5. See also
    5. Assigning data formatting and categories
      1. How to do it...
        1. Data formats
        2. Data category
      2. How it works...
      3. There's more...
        1. Model level settings
      4. See also
    6. Configuring Default Summarization and sorting
      1. How to do it...
        1. Sort By Column
          1. DAX Year-Month sorting
          2. DAX Ranking Sort
        2. Default Summarization
      2. How it works...
        1. Default Summarization
      3. There's more...
        1. Quick measures
      4. See also
    7. Setting the visibility of columns and tables
      1. How to do it...
        1. Isolate measures from tables
      2. How it works...
        1. Measure home tables
      3. There's more...
        1. Hiding hierarchy columns
        2. Group visibility
        3. Row level security visibility
        4. Visibility features from SSAS
    8. Embedding business definitions into DAX measures
      1. Getting ready
      2. How to do it...
        1. Sales and cost metrics
        2. Margin and count metrics
        3. Secondary relationships
      3. How it works...
        1. Date relationships
      4. There's more...
        1. Measure definitions
        2. Measure names and additional measures
      5. See also
    9. Enriching a model with analysis expressions
      1. How to do it...
        1. Pricing analysis
        2. Geometric mean at all grains
      2. How it works...
        1. Pricing analysis
    10. Building analytics into data models with DAX
      1. How to do it...
        1. Cross-selling opportunities
          1. Accessories but not bike customers
          2. Bike only customers
        2. Active verus inactive customers
        3. Actual versus budget model and measures
      2. How it works...
        1. Filter Context Functions
      3. There's more...
        1. SUMMARIZECOLUMNS()
    11. Integrating math and statistical analysis via DAX
      1. How to do it...
        1. Correlation coefficient
        2. Goodness-of-Fit test statistic
      2. How it works...
        1. Correlation coefficient syntax
        2. Goodness-of-Fit logic and syntax
    12. Supporting virtual table relationships
      1. How to do it...
        1. Segmentation example
        2. Summary to detail example
          1. Actual versus plan
      2. How it works...
        1. Year and month selected
        2. Virtual relationship functions
      3. There's more...
        1. Multiple dimensions 
        2. Alternatives to virtual relationships
      4. See also
    13. Creating browsable model hierarchies and groups
      1. How to do it...
        1. Create hierarchy columns with DAX
        2. Implement a hierarchy
        3. Create and manage a group
      2. How it works...
        1. DAX parent and child functions
        2. Include other grouping option
        3. Model scoped features
      3. There's more...
        1. DAX calculated columns as rare exceptions
        2. Natural hierarchies versus unnatural hierarchies
        3. Grouping dates and numbers
        4. DirectQuery models supported
      4. See also
  5. Authoring Power BI Reports
    1. Introduction
    2. Building rich and intuitive Power BI reports
      1. Getting ready
        1. Stakeholder Matrix
      2. How to do it...
        1. Report planning and design process
        2. Report Design Example
          1. European Sales and Margin Report Page
          2. European country sales and margin report page
      3. How it works...
        1. European sales report design
      4. There's more...
        1. Power BI report design checklist
        2. Custom visuals
        3. Published Power BI datasets as data sources
      5. See also
    3. Creating table and matrix visuals
      1. How to do it...
        1. Table visual exceptions
          1. Identifying blanks in tables
        2. Matrix visual hierarchies
      2. How it works...
        1. Matrix visual navigation
      3. There's more...
        1. URL and mail to email support
        2. Percent of total formatting
        3. Measures on matrix rows
        4. Data bar conditional formatting
    4. Utilizing graphical visualization types
      1. Getting ready
        1. Choosing visual types
      2. How to do it...
        1. Waterfall chart for variance analysis
        2. Line chart with conditional formatting
        3. Shape map visualization
      3. How it works...
        1. Shape map
    5. Enhancing exploration of reports
      1. Getting ready
        1. Drillthrough report page requirements
        2. Enable Cortana integration and Q&A
      2. How to do it...
        1. Create featured Q&A questions
        2. Parameterized Q&A report
        3. Cortana integration
        4. Drillthrough Report Pages
        5. Report themes
      3. How it works...
        1. Report theme JSON files
      4. There's more...
        1. Conversational BI - mobile support for Q&A
      5. See also
    6. Integrating card visualizations
      1. Getting ready
      2. How to do it...
        1. KPI visual
        2. Multi-row card
      3. There's more...
        1. Gauge visualizations
    7. Controlling interactive filtering between visuals
      1. How to do it...
        1. Visual interaction control
      2. How it works...
        1. Current year Measures
    8. Associating slicers with report pages
      1. How to do it...
        1. Configure dimension slicers
          1. Horizontal slicers
        2. Customize a date slicer
        3. Relative date filters
      2. How it works...
        1. Date slicer
      3. There's more...
        1. Text search
        2. Numeric range slicers
    9. Applying filters at different scopes
      1. How to do it...
        1. Report and page level filters
        2. Visual level filter - top N
      2. How it works...
        1. DAX queries from report, page, and visual Filters
      3. There's more...
        1. Advanced report and page level filters
    10. Formatting reports for publication
      1. How to do it...
        1. Visual alignment and distribution
        2. Shapes as backgrounds and groups
      2. There's more...
        1. Snap objects to grid and keyboard shortcuts
        2. Textbox with email link
        3. Format painter
      3. See also
    11. Designing mobile report layouts
      1. Getting ready
        1. Plan for mobile consumption
      2. How to do it...
        1. Phone layout - Europe report page
        2. Phone layout - United Kingdom report page
      3. How it works...
      4. There's more...
        1. Slicers and drill-down on mobile devices
        2. Mobile-optimized dashboards
      5. See also
  6. Creating Power BI Dashboards
    1. Introduction
    2. Building a Power BI dashboard
      1. How to do it...
        1. Dashboard design process
        2. Dashboard development process
    3. Constructing an enterprise dashboard
      1. How to do it...
        1. Dashboard design process
      2. How it works...
        1. Dual KPI custom visual
        2. Supporting tiles
    4. Developing dynamic dashboard metrics
      1. How to do it...
        1. Dynamic date columns
        2. KPI target measures
      2. How it works...
        1. Target measure - trailing 6 months
    5. Preparing datasets and reports for Q & A natural language queries
      1. Getting ready
        1. Determine use cases and feasibility
      2. How to do it...
        1. Prepare a model for Q & A
          1. Model metadata
          2. Model design
        2. Apply synonyms
          1. Analyze Q & a use cases
          2. Apply synonyms
          3. Publish the dataset
    6. Embedding analytical context into visualizations
      1. How to do it...
        1. Design the visual
        2. Create the visual
      2. How it works...
        1. Color saturation rule
        2. Tooltip measures
      3. There's more...
    7. Exposing what matters - top N and percentage of total visualizations
      1. How to do it...
        1. Top 25 resellers with below -3% margin
        2. Last year's top 50 products with below -10% growth
      2. How it works...
        1. Prior year rank measure
    8. Visualizing performance relative to targets with KPIs and gauges
      1. How to do it...
        1. Create the visuals
        2. Grouping KPIs
        3. Publish KPIs to dashboard
      2. How it works...
        1. Current month filter
        2. Time intelligence measures
    9. Leveraging Power BI reports in Power BI dashboards
      1. How to do it...
        1. Define live page requirements
        2. Create and publish to the dashboard
        3. Refine dashboard layout
      2. How it works...
        1. Live page slicers
    10. Deploying content from Excel and SSRS to Power BI
      1. Getting ready
      2. How to do it...
        1. Publish and pin excel objects
        2. Pin SSRS report items
    11. Adding data alerts and email notifications to dashboards
      1. How to do it...
        1. Configure data alert
        2. Automate email notification
      2. How it works...
  7. Getting Serious with Date Intelligence
    1. Introduction
    2. Building a complete date dimension table
      1. Getting ready
      2. How to do it...
        1. Date dimension design
          1. Required date dimension columns
          2. Date dimension planning and design
        2. Add date intelligence columns via SQL
      3. How it works...
        1. Date intelligence columns
        2. Loading the date dimension
      4. There's more...
        1. Role playing date dimensions
        2. Surrogate key date conversion
    3. Prepping the date dimension via the Query Editor
      1. How to do it...
        1. Date dimension M Query
        2. Add the date intelligence column via join
      2. How it works...
        1. Date dimension M query
        2. DirectQuery support
    4. Authoring date intelligence metrics across granularities
      1. Getting ready
      2. How to do it...
        1. Current time period measures
        2. Prior time period measures
        3. Dynamic prior period measure
      3. How it works...
        1. Current and prior time period measures
    5. Developing advanced date intelligence metrics
      1. How to do it...
        1. Count of days without sales
        2. Dynamic Prior Year-to-Date
      2. How it works...
        1. Dynamic prior period intelligence
    6. Simplifying date intelligence with DAX queries and calculated tables
      1. How to do it...
        1. Role playing date dimensions via calculated tables
        2. Date table logic query
      2. How it works...
        1. Date table logic query
    7. Adding a metric placeholder dimension
      1. How to do it...
        1. Metric placeholder dimension query
        2. Measure group table
  8. Parameterizing Power BI Solutions
    1. Introduction
    2. Creating dynamic and portable Power BI reports
      1. Getting ready
      2. How to do it...
        1. Single and multiple URL parameters
        2. Dynamic embedded URLs
      3. There's more...
        1. Dashboards with custom URLs
      4. See also
    3. Filtering queries with parameters
      1. Getting ready
      2. How to do it...
        1. Trailing days query parameter filter
        2. Multi-parameter query filters
      3. How it works...
        1. Query folding of parameter value filters
      4. There's more...
        1. Power BI Service support
    4. Preserving report metadata with Power BI templates
      1. Getting ready
      2. How to do it...
        1. Template parameters
        2. Export template
    5. Converting static queries into dynamic functions
      1. How to do it...
      2. There's more...
        1. Local resource usage
    6. Parameterizing your data sources
      1. Getting ready
      2. How to do it...
        1. SQL Server database
        2. Excel filename and path
        3. Stored procedure input parameters
    7. Generating a list of parameter values via queries
      1. How to do it...
        1. Dynamic date parameter query
        2. Product subcategories parameter query
      2. There's more...
        1. DirectQuery support
    8. Capturing user selections with parameter tables
      1. How to do it...
        1. Sales plan growth scenarios
      2. There's more...
        1. Scenario specific measures
    9. Building a forecasting process with What if analysis capabilities
      1. Getting ready
      2. How to do it...
        1. Forecast variables from Excel
        2. Power BI Desktop forecast model
          1. Source connection and unpivoted forecast tables
          2. Apply the forecast to historical values
          3. Allocate the forecast according to the dimension variable inputs
          4. Create relationships, measures, and forecast visuals
          5. Test and deploy forecasting tool
      3. How it works...
  9. Implementing Dynamic User-Based Visibility in Power BI
    1. Introduction
    2. Capturing the current user context of Power BI content
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Power BI authentication
      4. There's more...
        1. USERNAME() versus USERPRINCIPALNAME()
      5. See also
    3. Defining RLS roles and filtering expressions
      1. Getting ready
      2. How to do it...
        1. United States online Bike Sales Role
        2. Europe reseller sales - mountain and touring
        3. Deploy security roles to Power BI
      3. How it works...
        1. Filter transfer via relationships
      4. There's more...
        1. Managing security
        2. Dynamic columns and central permissions table
    4. Designing dynamic security models in Power BI
      1. Getting ready
      2. How to do it...
      3. There's more...
        1. Performance impact
    5. Building dynamic security in DirectQuery data models
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Dynamic security via relationship filter propagation
      4. There's more...
        1. Bidirectional security relationships
    6. Displaying the current filter context in Power BI reports
      1. How to do it...
        1. Dimension values selected
        2. Dimension values remaining
      2. How it works...
        1. FILTERS() and CONCATENATEX()
    7. Avoiding manual user clicks with user-based filtering logic
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Personal filters feature coming to Power BI apps
  10. Applying Advanced Analytics and Custom Visuals
    1. Introduction
    2. Incorporating advanced analytics into Power BI reports
      1. How to do it...
        1. Clustered column chart
        2. Line chart
      2. How it works...
        1. Analytics pane measures
      3. There's more...
        1. Analytics pane limitations
      4. See also
    3. Enriching Power BI content with custom visuals and quick insights
      1. Getting ready
      2. How to do it...
        1. Bullet chart custom visual
        2. Scoped quick insights
      3. How it works...
      4. There's more...
        1. Quick insights in Power BI Desktop
        2. Quick insights on published datasets
    4. Creating geospatial mapping visualizations with ArcGIS maps for Power BI
      1. Getting ready
      2. How to do it...
        1. Single field address
        2. Customer clustering Map
      3. There's more...
        1. ArcGIS map field wells
        2. Conditional formatting logic
      4. See also
    5. Configuring custom KPI and slicer visuals
      1. Getting ready
      2. How to do it...
        1. Dual KPI - headcount and labor expense
        2. Chiclet Slicer - Sales Territory Country
      3. There's more...
        1. Chiclet slicer custom visual
    6. Building animation and story telling capabilities
      1. Getting ready
      2. How to do it...
        1. Scatter chart with play axis
        2. ArcGIS map timeline
        3. Pulse chart custom visual
      3. There's more...
        1. Bookmarks
        2. Play axis custom visual
        3. Storytelling custom visuals
    7. Embedding statistical analyses into your model
      1. Getting ready
      2. How to do it...
        1. Regression table and measures
        2. Residuals table and measures
        3. Regression report
      3. How it works...
        1. Statistical formulas
        2. DAX calculated tables
      4. See also
    8. Creating and managing Power BI groupings and bins
      1. How to do it...
        1. First purchase date grouping
        2. Days since last purchase grouping
    9. Detecting and analyzing clusters
      1. Getting ready
      2. How to do it...
        1. Create clusters
        2. Analyze the clusters
      3. How it works...
        1. RFM - recency, frequency, monetary
        2. Clustering algorithm and limits
      4. There's more...
        1. R clustering custom visuals
        2. Scatter chart-based clustering
    10. Forecasting and visualizing future results
      1. Getting ready
      2. How to do it...
        1. Monthly forecast via date hierarchy
        2. Weekly sales forecast analysis
      3. How it works...
        1. Exponential smoothing
        2. Dynamic week status column
      4. There's more...
        1. Forecast requirements
    11. Using R functions and scripts to create visuals within Power BI
      1. Getting ready
      2. How to do it...
        1. Base graphics histogram
        2. ggplot2 histogram
      3. How it works...
        1. Automatic duplicate removal
        2. Filter context
      4. There's more...
      5. See also
  11. Developing Solutions for System Monitoring and Administration
    1. Introduction
    2. Creating a centralized IT monitoring solution with Power BI
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Wait Stats and instance configuration data source setup
      4. There's more...
        1. Query Store integration
        2. DirectQuery real-time monitoring datasets
      5. See also
    3. Constructing a monitoring visualization and analysis layer
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Relative date filtering
      4. There's more...
        1. Top 10 slowest queries via Query Store
      5. See also
    4. Importing and visualizing dynamic management view (DMV) data of SSAS and Power BI data models
      1. How to do it...
      2. How it works...
        1. Memory structures
      3. See also
    5. Increasing SQL Server DBA productivity with Power BI
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Query Store
      4. See also
    6. Providing documentation of Power BI and SSAS data models to BI and business teams
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Power BI documentation reports via Excel
        2. SQL Server Analysis Services (SSAS) Metadata
    7. Analyzing performance monitor counters of the Microsoft on-premises data gateway and SSAS tabular databases
      1. Getting ready
      2. How to do it...
        1. SSAS tabular memory reporting
        2. On-premises data gateway counters
      3. How it works...
        1. SSAS tabular memory limits
        2. On-premises data gateway workloads
      4. There's more...
        1. High availability and load balancing for the on-premises data gateway
        2. Reduce network latency via Azure ExpressRoute and Azure Analysis Services
      5. See also
    8. Analyzing Extended Events trace data with Power BI
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Self-service Extended Events analysis
      4. There's more...
        1. SQL Server Profiler versus Extended Events
        2. Additional event session integration
      5. See also
    9. Visualizing log file data from SQL Server Agent jobs and from Office 365 audit searches
      1. Getting ready
      2. How to do it...
        1. Power BI Audit Log Integration
        2. SQL Server Agent log integration
      3. How it works...
        1. PowerShell search for Power BI audit log
        2. SQL Server agent tables
      4. There's more...
        1. Power BI usage reporting
      5. See also
  12. Enhancing and Optimizing Existing Power BI Solutions
    1. Introduction
    2. Enhancing the scalability and usability of a data model
      1. Getting ready
      2. How to do it...
        1. Identify expensive columns and quick wins
        2. Normalize large dimensions
        3. Sort imported fact tables
      3. How it works...
        1. Columnar database
        2. Run-length encoding (RLE) compression via Order By
        3. Segment elimination
      4. There's more...
        1. Minimize loaded and refreshed queries
    3. Revising DAX measures to improve performance
      1. Getting ready
      2. How to do it...
        1. Improper use of FILTER()
        2. Optimizing OR condition measures
      3. How it works...
        1. DAX query engine - formula and storage
      4. There's more...
        1. DAX variables for performance
        2. DAX as a query language
    4. Pushing query processing back to source systems
      1. Getting ready
      2. How to do it...
        1. Query folding analysis process
        2. Query folding redesign
      3. How it works...
        1. Query folding factors
        2. Native SQL queries
      4. There's more...
        1. Parallel loading of tables
        2. Improving folded queries
    5. Strengthening data import and integration processes
      1. How to do it...
        1. Data source consolidation
        2. Error handling, comments, and variable names
        3. Handling missing fields
      2. How it works...
        1. MissingField.UseNull
      3. See also
    6. Isolating and documenting DAX expressions
      1. Getting ready
      2. How to do it...
        1. Reseller Margin % with variables
        2. Variable table filters
      3. How it works...
        1. Reseller Margin % with variables
      4. There's more...
        1. DAX Formatter in DAX Studio
  13. Deploying and Distributing Power BI Content
    1. Introduction
    2. Preparing a content creation and collaboration environment in Power BI
      1. How to do it...
        1. Evaluate and plan for Power BI deployment
        2. Set up a Power BI service deployment
      2. How it works...
        1. Premium capacity nodes - frontend cores and backend cores
      3. There's more...
        1. Scaling up and scaling out with Power BI Premium
      4. See also
    3. Managing migration of Power BI content between development, testing, and production environments
      1. Getting ready
      2. How to do it...
        1. Staged deployment overview
          1. Development environment
          2. Production environment
      3. How it works...
        1. Automated report lifecycle - clone and rebind report APIs
        2. OneDrive for business synchronization
        3. Version restore in OneDrive for business
      4. See also
    4. Sharing Power BI dashboards with colleagues
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Managing shared dashboards
      4. There's more...
        1. Analyze shared content from Excel
        2. Sharing dashboards from Power BI mobile apps
    5. Configuring Power BI app workspaces
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. App workspaces and apps
        2. App workspaces replace group workspaces
      4. There's more...
        1. Power BI premium capacity admins
      5. See also
    6. Configuring refresh schedules and DirectQuery connections with the on-premises data gateway
      1. Getting ready
      2. How to do it...
        1. Scheduled refresh for import mode dataset
          1. Configure data sources for the on-premises data gateway
          2. Schedule a refresh
        2. DirectQuery dataset
          1. Configure data sources for the on-premises data gateway
          2. Configure the DirectQuery dataset
      3. How it works...
        1. Dataset refreshes
        2. Dashboard and report cache refreshes
      4. There's more...
        1. Refresh limits: Power BI premium versus shared capacity
        2. Trigger refreshes via data refresh APIs in the Power BI Service
      5. See also
    7. Creating and managing Power BI apps
      1. Getting ready
      2. How to do it...
        1. Publishing an app
        2. Distributing and installing the app
      3. How it works...
        1. App workspaces to apps
      4. There's more...
        1. Apps replacing content packs
    8. Building email subscriptions into Power BI deployments
      1. Getting ready
        1. Determine feasibility - recipient, distribution method, and content
      2. How to do it...
        1. Create dashboard and report subscriptions
        2. Manage subscriptions
      3. There's more...
      4. See also
    9. Publishing Power BI reports to the public internet
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Publish to web report cache
      4. There's more...
        1. Embed in SharePoint online
      5. See also
    10. Enabling the mobile BI experience
      1. How to do it...
        1. Enhance basic mobile exploration and collaboration
        2. Enable advanced mobile BI experiences
      2. How it works...
        1. Responsive visualizations
      3. There's more...
        1. Apple watch synchronization
        2. SSRS 2016 on-premises via Power BI mobile apps
        3. Filters on phone reports
      4. See also
  14. Integrating Power BI with Other Applications
    1. Introduction
    2. Integrating Excel and SSRS objects into Power BI Solutions
      1. Getting ready
      2. How to do it...
        1. SSRS
        2. Excel
      3. There's more...
        1. SSRS and Excel use cases
          1. SSRS
          2. Microsoft Excel
    3. Migrating a Power Pivot for Excel Data Model to Power BI
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Excel items imported
      4. There's more...
        1. Export or upload to Power BI from Excel 2016
          1. Upload Excel Workbook to Power BI
          2. Export Excel Workbook to Power BI
    4. Accessing and analyzing Power BI datasets from Excel
      1. Getting ready
      2. How to do it...
        1. Cube formulas
        2. DAX query to Power BI
      3. How it works...
        1. Cube Formulas
        2. DAX query data connection
      4. There's more...
        1. Sharing and distribution limitations
        2. New Excel visual types table requirement
    5. Building Power BI reports into PowerPoint presentations
      1. Getting ready
      2. How to do it...
        1. Prepare a report for PowerPoint
        2. Export report to PowerPoint
      3. How it works...
        1. High resolution images and textboxes
      4. There's more...
        1. Embed Power BI tiles in MS Office
      5. See also
    6. Migrating a Power BI Data Model to SSAS Tabular
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Azure analysis services pricing and performance
      4. There's more...
        1. Direct import to SQL server data tools
      5. See also
    7. Accessing MS Azure hosted services such as Azure Analysis Services from Power BI
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. Report level measures for live connections to SSAS
        2. Client libraries for Azure Analysis Services
      4. There's more...
        1. Power BI premium DirectQuery and SSAS live connection query limits
      5. See also
    8. Using Power BI with Microsoft Flow and PowerApps
      1. Getting ready
      2. How to do it...
        1. Streaming Power BI dataset via MS Flow
      3. How it works...
        1. Microsoft Flow
      4. There's more...
        1. Write capabilities and MS Flow premium
        2. PowerApps Studio and mobile applications
      5. See also

Product information

  • Title: Microsoft Power BI Cookbook
  • Author(s): Brett Powell
  • Release date: September 2017
  • Publisher(s): Packt Publishing
  • ISBN: 9781788290142