Microsoft Power BI Data Analyst Certification Guide by Ed Corcoran Orrin Edenfield

Author:Ed Corcoran, Orrin Edenfield
Language: eng
Format: epub
Publisher: Packt Publishing Pvt Ltd
Published: 2022-06-22T00:00:00+00:00

Next, we'll look at other ways of optimizing using measures, relationships, and visuals.

Optimizing measures, relationships, and visuals

Measures in Power BI are driven by DAX queries. To optimize DAX queries (and measures), we must understand how Power BI uses both a Formula Engine (FE) and an SE, which make up the backend technology. Simply put, the SE is where imported data is stored in the highly compressed columnar VertiPaq format, and the FE is where data can be calculated based on the requirements in DAX queries.

The SE provides a single interface for the FE to query and retrieve data. It functions to store data or provide a conduit for underlying data stores in cases where data is connected using DirectQuery. The SE is built into Power BI Desktop and is also part of the underlying technology used in SQL Server Analysis Services and Azure Analysis Services.

The FE is the query processor that takes DAX queries as input, interacts with the SE, and then returns data to Power BI visuals in reports and dashboards. The FE does not have direct access to every kind of data source that Power BI supports; instead, the FE only interacts with the SE. It's the SE that has both the ability to store data in-memory in VertiPaq storage and the myriad of different data stores supported by Power BI. The FE serves the purpose of understanding DAX queries, converting to xmSQL (the SQL dialect used by VertiPaq), and performing calculations on data received from the SE to get to the end result as directed by the DAX query.

When optimizing measures, you will want to use an open source tool called DAX Studio (available for free from https://daxstudio.org). DAX Studio is an open source DAX client tool that allows you to do an in-depth analysis of DAX queries and how they are executed in the Power BI environment:

To use DAX Studio, you will need the DAX query used by the specific operation of the report in Power BI Desktop. You can acquire this query easily using the Performance analyzer pane in Power BI Desktop, accessed from under the View menu on the ribbon. Click Performance Analyzer and then click Refresh visuals, select a slicer, or apply a filter. Power BI will generate and run the DAX query (or queries) to generate the requested data for each of the visuals on the report being analyzed.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.