What this book covers
Chapter 1, Setting Targets and Identifying Problem Areas, describes a Power BI solution as a stream of data from multiple sources reaching consumers in a consolidated fashion. We'll look at how data can be stored in Power BI and the different paths it can take before reaching a user. Many of the initial architectural design choices made in the early stages of the solution are very difficult and costly to switch later. That's why it is important to have a solid grasp of the implications of those choices and how to decide what's best at the start.
Chapter 2, Exploring Power BI Architecture and Configuration, provides general guidance to improve throughput and latency. This chapter also looks at data storage modes in Power BI and how the data reaches the data model since choices here affect dataset size and freshness. This chapter also covers how best to deploy Power BI gateways, which are commonly used to connect to external data sources. This is important because users often demand up-to-date data, historical data, and can number thousands in parallel.
Chapter 3, DirectQuery Optimization, explores DirectQuery, which relies on an external data source. It is a common storage choice in Power BI when organizations have very large datasets. These sources are often not designed for analytical queries and significantly reduce report and data refresh performance. We will cover optimizations that can be made in both Power BI and the external source to avoid hitting limits too quickly.
Chapter 4, Analyzing Logs and Metrics, describes how performance can only be improved if it can be measured objectively. Therefore, this chapter covers all the sources of performance data and how to make sense of the information provided to identify the parts of the solution that are bottlenecks. This includes useful native and third-party utilities. We also provide guidelines to help monitor and manage performance continuously.
Chapter 5, Desktop Performance Analyzer, describes the easiest way to see where time is being spent in reports. This is done using Power BI Desktop Performance Analyzer to get detailed breakdowns for every user action, on a per-visual basis. This chapter details all the features, highlights, and issues to be aware of, explains what the metrics mean, and demonstrates how to interpret and act on the data.
Chapter 6, Third-Party Utilities, describes some popular third-party utilities that are effective in performance investigation and tuning. This chapter walks through typical use cases around connecting them to Power BI, collecting metrics, and what to look for when diagnosing performance problems.
Chapter 7, Governing with a Performance Framework, describes how the metrics and tools covered in earlier chapters are essential building blocks for performance management. However, success is more likely with a structured and repeatable approach to build performance-related thinking into the entire Power BI solution life cycle. This chapter provides guidelines to set up data-driven processes to avoid sudden scale issues for new content and prevent degradations for existing content. We also discuss typical roles in analytics projects, ranging from self-service to centrally governed analytics, and we suggest what they can do to help with performance improvement efforts.
Chapter 8, Loading, Transforming, and Refreshing Data, introduces how loading data periodically is a critical part of any analytical system, and in Power BI, this applies to Import mode datasets. Data refresh operations in Import mode are some of the most CPU- and memory-intensive, which can lead to long delays or failures, especially with large datasets. This can leave users with stale data, slow down development significantly, or even overload capacities with expensive data refreshes. Therefore, data transformations should be designed with performance in mind.
Chapter 9, Report and Dashboard Design, focuses on how reports and dashboards are the "tip of the iceberg" in a Power BI solution since that's what consumers interact with regularly. Regardless of how it's exposed, this core visual layer of Power BI is a JavaScript application running in a browser. This chapter covers important considerations and practices to apply regarding visual layout, configuration, and slicing/filtering. It also looks at paginated reports, which behave differently from interactive reports and have special performance considerations.
Chapter 10, Data Modeling and Row-Level Security, describes how the Power BI dataset is where data lands after being shaped, and from where data is retrieved for analysis. Hence, it is arguably the most critical piece, at the core of a Power BI solution. Power BI's feature richness and modeling flexibility provide alternatives when modeling data. Some choices can make development easier at the expense of query performance and/or dataset size. This chapter provides guidance on model design, dataset size reduction, and faster relationships. We then cover recommended practices to optimize row-level security.
Chapter 11, Improving DAX, describes how DAX formulas allow BI developers to add a diverse range of additional functionality to the model. The same correct result can be achieved by writing different DAX formulas without realizing that one version may be significantly slower in a certain query or visual configuration. This chapter highlights common DAX issues and recommended practices to get calculations performing at their best.
Chapter 12, High-Scale Patterns, describes how the amount of data that organizations collect and process is increasing all the time, leading to challenges with big data. Even with Power BI's data compression technology, it isn't always possible to load and store massive amounts of data in an Import mode dataset in a reasonable amount of time. This problem is worse when you need to support hundreds or thousands of users in parallel. This chapter covers the options available to deal with such issues by using Power BI Premium, Azure technologies, composite models, and aggregations.
Chapter 13, Optimizing Premium and Embedded Capacities, covers how Power BI Premium offers dedicated capacity, higher limits, and many additional capabilities, such as paginated reports and AI. We cover Premium Gen2 in this chapter and explain how the system deals with excessive load and how autoscale works. We will also cover available workload settings that can affect performance. We will learn how to plan for the right capacity size and how to perform load testing. We'll also learn how to use the Capacity Metrics app to identify and troubleshoot capacity load issues.
Chapter 14, Embedding in Applications, covers how embedding Power BI content in a custom web app is a great way to expose data analytics within a completely customized UI experience, along with other non-Power BI-related content. This pattern introduces additional considerations since the Power BI application is hosted externally via API calls. This chapter looks at how to do this efficiently.