Optimize a Model for Performance in Powerbi



Performance optimization/Performance tuning 

In order to improve the performance of the data model Performance optimization or Performance tuning is necessary. It involves making changes to the current state of the data model so that it runs more efficiently. Essentially, when data model is optimized, it performs better.



Techniques for optimize the performance

1. Minimize the size of data : The performance optimization process involves minimizing the size of the data model and making the most efficient use of the data in the model, which includes:

    •  Ensuring that the correct data types are used.

    • Deleting unnecessary columns and rows.

    • Avoiding repeated values.

    • Replacing numeric columns with measures.

    • Reducing cardinalities.

    • Analyzing model metadata.

    • Summarizing data where possible.

2.  Review the performance of measures, relationships, and visuals : If data model has multiple tables, complex relationships, intricate calculations, multiple visuals, and redundant data, a potential exists for poor report performance. The poor performance of a report leads to a negative user experience.

To optimize performance, we must first identify where the problem is coming from; in other words, find out which elements of your report and data model are causing the performance issues. Afterward, we can take action to resolve those issues and, therefore, improve performance.


3.  Use variables to improve performance and troubleshooting : 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. If we use variables, we can save query processing time. This change is a step in the right direction toward optimizing the performance of a data model.

The use of variables in data model provides the following advantages:

  • Improved performance 

  • Improved readability 

  • Simplified debugging 

  • Reduced complexity 


4.  Improve performance by reducing cardinality levels : 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. That is one to one, one to many, may to one and many to many.

5.  Optimize DirectQuery models with table level storage : DirectQuery is one way to get data into Power BI Desktop. The DirectQuery method involves connecting directly to data in its source repository from within Power BI Desktop. It is an alternative to importing data into Power BI Desktop.

6.  Create and manage aggregations : When aggregating data, we summarize that data and present it in at a higher grain (level). For example, we can summarize all sales data and group it by date, customer, product, and so on. The aggregation process reduces the table sizes in the data model, allowing us to focus on important data and helping to improve the query performance.



Conclusion 

In this article I have discussed different techniques for optimizing performance of data model in Power BI.

Comments

Post a Comment

Popular posts from this blog

Use DAX iterator functions in Power BI Desktop models

Data Analysis Expressions (DAX) include a set of functions known as  iterator functions . Iterator functions enumerate all rows of a given table and evaluate a given expression for each row. They provide you with flexibility and control over how your model calculations will summarize data. Some DAX Functions are Complex summarization 1. SUMX e.g. : 1.  Revenue = SUMX (Sales, Sales[Sales Amount]) 2. Revenue = SUMX (Sales, Sales[Order Quantity] * Sales[Unit Price] * ( 1 - Sales[Unit Price Discount Pct.])) 3. Discount = SUMX ( Sales, Sales[Order Quantity] *( RELATED ('Product'[List Price]) - Sales[Unit Price] ) ) 2. AVERAGEX e.g :1. Revenue Avg = AVERAGEX ( Sales, Sales[Order Quantity] * Sales[Unit Price] * ( 1 - Sales[Unit Price Discount Pct.]) ) 2. Revenue Avg Order = AVERAGEX ( VALUES ('Sales Order'[Sales Order]), [Revenue] ) 3. RANKX The  RANKX  DAX function is a special iterator function you can use to calculate ranks. Its syntax is as follows: RANKX (<...
Read more

Different charts in Power BI

Image
  An overview of Chart Types in Power BI As everyone might be aware, Power BI is one of the SaaS offerings from Microsoft in the field of BI and Analytics. It provides a very intuitive way for non-technical users to do analyses and create reports on their own without having to rely on a technical team. One of the main purposes of Microsoft behind Power BI is to promote self-service BI and it is one of the BI tools in the market with a comparatively lower learning curve as there are many similarities compared to Microsoft Excel.  In this blog, I am going to give an overview of the most widely used chart types in Power BI.   Let's look at some of the most frequently used chart types in Power BI Bar and column charts Stacked Bar and Stacked Column Chart Clustered Bar and Clustered Column Chart Line Chart Area Chart Stacked Area Chart Line and Stacked Column Chart/ Combo Chart Line and Clustered Column Chart/ Combo Chart Ribbon Chart Waterfall Chart Funnel Chart Scatter Chart...
Read more

Modify DAX filter context in Power BI Desktop models

Summary CALCULATE  DAX function to modify filter context in the formulas. The syntax for the CALCULATE function is as follows: CALCULATE (<expression>, [[<filter1>], <filter2>]…) I learned about filter context. And also learned why and how to work with the CALCULATE function to modify filter context by passing in filters. These filters can add or overwrite the filter context, and they can modify filter context when we are passing in special functions like REMOVEFILTERS or KEEPFILTERS. Additionally, I learned that the CALCULATE function can transition row context to filter context, which can be required when we are using measure expressions in calculated columns or iterator functions.
Read more