Model - Optimise model performance

Question 1

How does cardinality impact model performance?

Answer 1

• Power BI uses a columnstore database
• This means that it is the number of distinct values that matters not the number of rows

Example
- You could have 3 distinct values or 300 rows but it is the distinct values that impacts performance

Question 2

Name some data loading best practice?

Answer 2

• Only include data that is needed for reporting
• You can add more data later
• Only load queries that you need

Question 3

What is the best practice way to remove rows?

Answer 3

• Necessary only = only load data necessary for you analysis (e.g., UK sales for UK demand analysis instead of global sales)
• Use Parameters = can use parameters to make this more manageable AND also makes it possible to change filters once the dataset is published in the service
• Filter dates = only uses time-period you want (i.e., usually the most recent time period)
• Filtering rows after you create reports won’t break any visuals in existing reports

Question 4

What is the best practice way to remove columns?

Answer 4

• Remove primary keys for fact tables = some data warehouses contain primary keys for fact tables…primary keys in fact tables can occupy up to 50% of a data model without any benefit
• Use COUNTROWS = this is a more efficient way of counting rows in a fact table
• Removing columns will break data report visuals

Question 5

What do columns in a data model do?

Answer 5

Columns in a data model serve one of two purposes: a) support visuals or calculations b) support both

Question 6

How can you prevent new columns from breaking reports?

Answer 6

• Use the ‘remove all other columns’ function in Power Query

Question 7

What is Performance Analyser?

Answer 7

• A feature that allows you to track performance bottlenecks
• Can track slow-performing visuals and see the DAX queries behind them

Question 8

How does Performance Analyser work?

Answer 8

• It works by recording traces and then telling you how long each visual took to render

Question 9

Explain how to use Performance Analyser?

Answer 9

• View > Performance analyser
• Start recording > perform actions (e.g., apply filters, refresh visuals) > see how long it takes it visual to render
• Identify the slowest visuals in the Performance Analyser pane in (ms)
• Each visual has a DAX query behind it which you can copy by expanding the line of the visual in Performance Analyser

Question 10

How can you improve cardinality by changing data types?

Answer 10

• You can decrease the data size by reducing the cardinality of columns by changing data types

In Power BI two data types can be used for decimal numbers:

• Decimal number: can store more than four decimal numbers
• Fixed decimal numbers: can store only four

HOW:

• 1) If you don’t need more than four decimal places then change your data type to Fixed Decimal numbers
• 2) Split decimals into two columns (as close to data source as possible) this can decrease cardinality and decrease data model size and then aggregate the two using SUMX
• 3) Split Date/Time this will increase the number of duplicates but reduce cardinality

Recognise which columns could be split to reduce cardinality

Question 11

How can you improve cardinality through summarisation?

Answer 11

• If your report contains too much detail (you want months but have daily) then you can reduce cardinality by summarising the data
• This can reduce size of the data model but make analysis of the lower grain impossible and this trade-off depends on business requirements

HOW

• Summarise data as close to the data source as possible
• Can use the Group by function in the Transform ribbon

Question 12

How can aggregations impact data model performance?

Answer 12

• By using composite mode you can optimise the performance of data sources that use DirectQuery mode by using aggregations (use it as a transform)

Question 13

How can you create an aggregation table?

Answer 13

• Aggregation table has to have some of the same keys and columns you want to aggregate (doesn’t have to come from data source)

3 ways include

• Import M query
• Calculated table in DAX
• Another table in DirectQuery mode

Question 14

How can you manage an aggregation table?

Answer 14

Fields pane > Manage aggregations

For each column you select the appropriate summarisation and corresponding details table:

• Count
• GroupBy: used for relationship keys
• Max: correspond to max value
• Min: corresponds to min value
• Sum
• Count table rows

Select apply all

Question 15

What happens if your queries cannot be resolved using an aggregation table?

Answer 15

In case the aggregation table is insufficient the detail table will be used

Question 16

What are the benefits of efficient data models?

Answer 16

Fast running queries in the report

Fast running measures and calculations

Question 17

What is the cause of bad model performance?

Answer 17

Bad performance is usually a result of:
Bad data models (too much unnecessary data)
Bad DAX (too many unnecessary row calculations)
Mix of the two

Question 18

Name the characteristic of bad report performance? What’s the consequence of this?

Answer 18

From a report user’s perspective, poor performance is characterized by:
Loading pages into report taking too long
Visuals not responding fast enough
Tables not refreshing quickly enough
Large files put strain on organisations resources
The poor performance of a report leads to a negative user experience.
This poor performance results in a negative user experience.

Question 19

Name the query optimisation process steps:

Answer 19

Minimising the size of the data model
Deleting unnecessary columns and rows.
Summarizing data where possible (must ensure correct data types are applied to enable this by analysing model metadata)
Reducing cardinalities.
Replacing numeric columns with measures.
Avoiding repeated values.

Question 20

What are the limitations of data model optimisation practices?

Answer 20

• Need for a data engineer: keep in mind that, while the basic performance and best practices guidance in
• Power BI will lead you a long way, to optimize a data model for query performance, you will likely have to partner with a data engineer to drive data model optimizing in the source data sources.

Question 21

Explain how data model size can impact performance?

Answer 21

• Power BI desktop file size (.pbix)
• The data that you load into Power BI Desktop is compressed and stored to the disk by the VertiPaq storage engine.
• The size of your data model has a direct impact on its performance; a smaller sized data model uses less resources (memory) and achieves faster data refresh, calculations, and rendering of visuals in reports.

Question 22

When is a report likely to have poor performance?

Answer 22

If your data model has:
 multiple tables
 complex relationships
 intricate calculations
 multiple visuals
 redundant data

Question 23

How to run performance analyser?

Answer 23

To ensure you get the most accurate results in your analysis (test) you need to clear the visual cache and data engine cache

Visual cache – (a) When you load a visual, you can’t clear this visual cache without closing Power BI Desktop and opening it again. To avoid any caching in play, you need to start your analysis with a clean visual cache. (b) To ensure that you have a clear visual cache, add a blank page to your Power BI Desktop (.pbix) file and then, with that page selected, save and close the file. Reopen the Power BI Desktop (.pbix) file that you want to analyze. It will open on the blank page.

Data engine cache: (a) When a query is run, the results are cached, so the results of your analysis will be misleading. You need to clear the data cache before rerunning the visual. (b) To clear the data cache, you can either restart Power BI Desktop or connect DAX Studio to the data model and then call Clear Cache.

After you have cleared cache go to performance analyser

Start recording and select the page of the report you want to analyse and select the elements you want to measure

When you are finished press stop

Question 24

How to review the performance analyser results?

Answer 24

Check the performance analyser pane

Sort the results

DAX query - The time it took for the visual to send the query, along with the time it took Analysis Services to return the results.

Visual display - The time it took for the visual to render on the screen, including the time required to retrieve web images or geocoding.

Other - The time it took the visual to prepare queries, wait for other visuals to complete, or perform other background processing tasks. If this category displays a long duration, the only real way to reduce this duration is to optimize DAX queries for other visuals, or reduce the number of visuals in the report.

Question 25

Explain how you can improve the performance of visuals?

Answer 25

DON’T reduce user experience: you should find a way to improve performance with minimal impact to user experience.

Use visuals sparingly: Consider the number of visuals on the report page; less visuals means better performance.

Use visual alternatives: Rather than using multiple visuals on the page, consider other ways to provide additional details, such as drill-through pages and report page tooltips.

Examine the number of fields in each visual.

120 seconds: Investigate DAX queries taking longer than 120 milliseconds

Question 26

What is the problems with performance analyser on DAX and how can you solve this?

Answer 26

Highlights issues but does not tell you how to improve them

You can use DAX Studio to investigate your queries in more detail.

Try different DAX functions to see if it improves performance

significantly reduce the evaluation duration time for this query.

Question 27

What is the auto date/time feature?

Answer 27

By default, this feature is enabled globally, which means that Power BI Desktop automatically creates a hidden calculated table for each date column, provided that certain conditions are met. The new, hidden tables are in addition to the tables that you already have in your dataset.

The Auto date/time option allows you to work with time intelligence when filtering, grouping, and drilling down through calendar time periods.

Question 28

Auto date/time best practice?

Answer 28

We recommend that you keep the Auto date/time option enabled only when you work with calendar time periods and when you have simplistic model requirements in relation to time.

If your data source already defines a date dimension table, that table should be used to consistently define time within your organization, and you should disable the global Auto date/time option. Disabling this option can lower the size of your data model and reduce the refresh time.

You can enable/disable this Auto date/time option globally so that it applies to all of your Power BI Desktop files, or you can enable/disable the option for the current file so that it applies to an individual file only.

Question 29

Why are variables important?

Answer 29

Some expressions involve the use of many nested functions and the reuse of expression logic. These expressions take a longer time to process and are difficult to read and, therefore, troubleshoot.

Using variables in your DAX formulas can help you write more complex and efficient calculations. Variables can improve performance and reliability, and readability, and reduce complexity.

Question 30

What are the benefits of variables?

Answer 30

• Improve performance
• Improve readability
• Simplify debugging
• Reduce complexity

Question 31

How do variables work?

Answer 31

The VAR keyword introduces the definition of a variable.

You can have as many variables as needed in a single expression, and each one has its own VAR definition.

The RETURN keyword defines the expression to return as the result. Inside RETURN expression, you can use the variables, which are replaced by the computed value.

Question 32

What are the two meanings of cardinality?

Answer 32

Cardinality is a term that is used to describe the uniqueness of the values in a column.

Cardinality is also used in the context of the relationships between two tables, where it describes the direction of the relationship.

Question 33

How can you identify cardinality levels in columns?

Answer 33

Low Cardinality: A column that has a lot of repeated values in its range (distinct count is low) will have a low level of cardinality.

High Cardinality: Conversely, a column that has a lot of unique values in its range (unique count is high) will have a high level of cardinality.

Question 34

What is the caveat of reducing cardinality levels (i.e., reduce the data loaded into models)?

Answer 34

Lose informational value: Deciding to summarize fact-type data will always involve a tradeoff with the detail of your data.

Lose drill-downs: A disadvantage is that you may lose the ability to drill into data because the detail no longer exists. This tradeoff could be mitigated by using a mixed model design.

Question 35

Best practice for reducing cardinality?

Answer 35

In Power BI Desktop, a Mixed mode design produces a composite model. Essentially, it allows you to determine a storage mode for each table. Therefore, each table can have its Storage Mode property set as Import or DirectQuery.

An effective technique to reduce the model size is to set the Storage Mode property for larger fact-type tables to DirectQuery. This design approach can work well in conjunction with techniques that are used to summarize your data.

Question 36

Factors impacting DirectQuery query performance?

Answer 36

Underlying data source

Network latency; faster networks return data quicker.

The performance of the data source’s server and how many other workloads are on that server. For example, consider the implications of a server refresh taking place while hundreds of people are using the same server for different reasons.

Question 37

What are the limitations of DirectQuery connections?

Answer 37

Performance - As previously discussed, your overall user experience depends heavily on the performance of the underlying data source.

Security - If you use multiple data sources in a DirectQuery model, it is important to understand how data moves between the underlying data sources and the associated security implications. You should also identify if security rules are applicable to the data in your underlying source because, in Power BI, every user can see that data.

Data transformation - Compared to imported data, data that is sourced from DirectQuery has limitations when it comes to applying data transformation techniques within Power Query Editor. For example, if you connect to an OLAP source, such as SAP BW, you can’t make any transformations at all; the entire external model is taken from the data source. If you want to make any transformations to the data, you will need to do this in the underlying data source.

Modelling - Some of the modelling capabilities that you have with imported data aren’t available, or are limited, when you use DirectQuery.

Reporting – Almost all the reporting capabilities that you have with imported data are also supported for DirectQuery models, provided that the underlying source offers a suitable level of performance. However, when the report is published in Power BI service, the Quick Insights and Q&A features are not supported. Also, the use of the Explore feature in Excel will likely result in poorer performance.

Question 38

What are the steps in optimising data models?

Answer 38

Optimise data source as much as possible (i.e., tune database)

Take further action within Power BI desktop using performance analyser

Question 39

What is a query reduction option?

Answer 39

Power BI Desktop gives you the option to send fewer queries and to disable certain interactions that will result in a poor experience if the resulting queries take a long time to run. Applying these options prevents queries from continuously hitting the data source, which should improve performance.