Explainer: How Can I Optimize Report Performance in Insights?

Learn about how you can best optimize your report performance in Vena Insights.

How can I design my dataset to ensure optimal performance?

• Filter data at the source whenever possible to reduce the amount of data loaded into the datasets, i.e., use MQL to minimize the size of the dataset, this can be accessed via the Edit Dataset drawer:
  
• Create relationships between tables using primary and foreign keys, you can do this by using our manage relationships functionality:
  
• Apply appropriate data types to columns for efficient storage and processing. You can modify data types in our manage calculations modal. This only applies to columns:
  
• Remove unnecessary columns or tables from your dataset to reduce the data size.
• Use calculated columns sparingly and prefer measures for aggregations.

Advanced Dataset Optimizations

• Check the DAX measures. The DAX measures can be resource-intensive and slow down loading. Optimize the DAX measures by using smarter functions. Some of the most computationally intensive ones to avoid are:
  • Iterating Functions: DAX functions like SUMX, COUNTX or AVERAGEX that iterate over a table or a set of values, especially with large datasets. These functions perform calculations row by row, which can result in slower performance when processing large volumes of data.
  • Advanced Aggregation Functions: DAX functions like SUMMARIZE or GROUPBY are used for advanced aggregation and grouping operations. These functions involve aggregating data across multiple dimensions, and depending on the complexity of the expressions used within them, they can be resource-intensive.
  • Multiple Nested Functions: DAX allows nesting functions within functions, and multiple levels of nesting can significantly impact performance. When using complex nested functions, especially with large datasets, the computational load can increase due to the iterative nature of the calculations.
  • Time Intelligence Functions: DAX includes time intelligence functions like DATESYTD, TOTALYTD, or SAMEPERIODLASTYEAR, which enable calculations based on time periods. These functions involve iterating over date ranges and performing calculations, which can be resource-intensive when dealing with large date tables or complex time-based calculations.
  • Calculated Columns with Complex Logic: Calculated columns in DAX allow you to define custom expressions that create new columns in your data model. If the logic behind a calculated column involves complex calculations or relies on multiple related tables, it can lead to increased complexity during data refresh or query execution.
• Minimize nested DAX queries where one query references another.
• Use star schema instead of a snowflake schema. Learn more about snowflake vs star schemas in this article.

How can I design my report to ensure optimal performance?

• Limit the use of visuals and avoid using too many visuals on a single page. You can find more information on best practices in this article.
• Use slicers and filters to limit the amount of data displayed at once.
• Avoid unnecessary slicers and filters as these are computationally heavy.
• Use the Edit Interactions feature to control how visuals interact with each other.
• Use PPT backgrounds instead of cards. You can find more information about this in the Create a background with shapes section of this article.
• Consolidate card elements. You can find more information on consolidating report elements in this article. 
• Create summary tables or calculated tables to store pre-calculated aggregations.

How do visuals impact my report performance?

Ranking the visuals, from most computationally heavy to lightest can vary based on the specific configurations, data volumes and calculations involved. Avoiding the use of visuals with heavy calculations or complex DAX expressions can optimize your report performance. However, here's a general ranking based on the complexity and computational requirements of the visuals (ranked from most complex to least):

1. Matrix Visual
2. Table Visual
3. Map Visual
4. Scatter Chart Visual
5. Line Chart Visual
6. Bar Chart Visual
7. Card Visual
8. Gauge Visual

More links

• Performance tips -  https://guyinacube.com/tag/performance/
• PowerBI tips - https://powerbi.tips/
• PowerPivotPro – www.powerpivotpro.com
• Excelerator BI - https://lnkd.in/dZeZkeA5
• SQLBI - https://www.sqlbi.com/
• Power BI Community - https://lnkd.in/dAZgQhAW
• Data Stories Gallery - https://lnkd.in/dFPZsNUU
• Power BI Ideas Forum - https://ideas.powerbi.com
• Power BI Documentation - https://lnkd.in/dbJYjidW
• DAX Function Reference - https://lnkd.in/d9BR2ekU

Questions? Comments? Reach out to us directly at support@venasolutions.com.
(Please include the link to the article for reference.)