Enhancing and Optimizing Existing Power BI Solutions

Power BI projects often begin by focusing on specific functional requirements, such as a set of dashboards and reports for a given business area and team. With relatively narrow requirements and small datasets, sufficient performance and reliability is often achievable without design and code enhancements to the data retrieval, model, and reporting layers. Additionally, Power BI Premium capacity and in certain cases Analysis Services resources provide viable options to enhance the scalability of a dataset.

For larger Power BI projects—particularly when the options of Power BI Premium and Analysis Services are not available—it becomes important to identify opportunities to improve report query performance and to more efficiently use system resources to store and refresh the dataset.

Additionally, the data import process can often be made more resilient and less resource intensive. Furthermore, the application...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop
• SQL Server with the AdventureWorksDW2019 database installed. This database and instructions for installing are available here: http://bit.ly/2OVQfG7
• Files for this chapter and can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition

Enhancing Data Model Scalability and Usability

The performance of all Power BI reports is impacted by the design of the data model. The DAX queries executed upon accessing a report and when dynamically updating report visuals in interactive, self-service user sessions all rely on the relationships defined in the model and optimizations applied to the model's tables. For in-memory models, the cardinality of the columns imported and the compression of these columns contribute to the size of the dataset and query duration. For DirectQuery data models, the referential integrity of the source tables and the optimization of the relational source largely drive query performance.

This recipe includes three optimization processes, all focused on a Reseller Sales fact table with 11.7 million rows. The first example leverages the DMVs and Power BI memory report created in Chapter 10, Administering and Monitoring Power BI, to identify and address the most expensive columns. The second...

Improving DAX Measure Performance

Just as specific columns and relationships can be optimized for performance per the prior recipe, frequently used DAX measures can also be targeted for performance improvements. Existing DAX measures may contain inefficient data access methods that generate additional, unnecessary queries or that largely execute in a single CPU thread. Revising measures to better leverage the multi-threaded storage engine and to avoid or reduce unnecessary queries and iterations can deliver significant performance improvements without invasive, structural modifications to the model.

In this recipe, DAX queries executed by Power BI visuals are captured and analyzed using the Performance Analyzer feature of Power BI Desktop. The first example highlights a common misuse of the FILTER function for basic measures. In the second example, two alternative approaches to implementing an OR filter condition across separate tables are described relative to a common but less...

Pushing Query Processing Back to Source Systems

During the scheduled refresh of datasets retrieving from on-premises sources, any query transformations not executed by the source system will require local resources of the M (Mashup) engine of the on-premises data gateway server. With larger datasets, and potentially with other scheduled refreshes occurring on the same gateway server at the same time, it becomes important to design M queries that take full advantage of source system resources via query folding. Although transformations against some sources such as files will always require local resources, in many scenarios M queries can be modified to help the engine generate an equivalent SQL statement and thus minimize local resource consumption.

In this recipe, a process and list of items is provided to identify queries not currently folding and the potential causes. Additionally, a query based on an existing SQL statement is redesigned with M expressions to allow query folding...

Strengthening Data Import and Integration Processes

Many Power BI datasets must be created without the benefit of a data warehouse or even a relational database source system. These datasets, which often transform and merge less structured and governed data sources such as text and Excel files, generally require more complex M queries to prepare the data for analysis. The combination of greater M query complexity and periodic structural changes and data quality issues in these sources can lead to refresh failures and challenges in supporting the dataset. Additionally, as M queries are sometimes initially created exclusively via the Query Editor interface, the actual M code generated may contain unexpected logic that can lead to incorrect results and unnecessary dependencies on source data.

This recipe includes practical examples of increasing the reliability and manageability of data import processes including data source consolidation, error handling and comments, and accounting...

Isolating and Documenting DAX Expressions

Isolating expressions into independent and interchangeable DAX measures or as variables within measures is recommended to simplify development and ownership of the dataset. Independent measures can be hidden from the Fields list yet contain core business definitions and efficient filtering logic to drive the results and performance of many other measures in the model. Although scoped to each measure, DAX variables provide a self-documenting coding style and, unlike scalar-valued measures, also support table values thus allowing for even greater modularity.

In this recipe, DAX variables, measures, and comments are used in different examples that demonstrate best practices around the creation and use of DAX expressions.

Getting ready

To prepare for this recipe, follow these steps:

1. Open a Power BI Desktop file locally and access the Power Query Editor by clicking on Transform Data in the ribbon of the Home tab
2. Create...

Improving Data Load Speeds with Incremental Refresh

Incremental refresh is a feature originally released for Power BI Premium capacities but has become a feature for Pro licenses as well. Prior to incremental refresh, Power BI only had a single mode of operation when refreshing datasets, full load. In other words, the existing data in the dataset was removed and entirely replaced each time the dataset refreshed. The full load refresh process could take a long time when dealing with large fact tables with millions of rows. Incremental refresh solves this problem by only refreshing new and changed data within the dataset. Since incremental refresh is relatively new, older datasets are likely still using the full load process and thus might benefit from being retrofitted with incremental refresh.

This recipe demonstrates how to set up and configure incremental refresh in Power BI.

Getting ready

To prepare for this recipe, follow these steps:

1. Open a Power BI Desktop...

Conclusion

This chapter's recipes contained top data modeling, DAX measure, and M query patterns to enhance the performance, scalability, and reliability of Power BI datasets. This included performance tuning examples of both data models and measures, error handling, and query folding examples of M queries in addition to supporting details on the DAX and M query engines. The next chapter is all about deploying and distributing Power BI content using the Power BI service, Teams, and mobile devices.