Minimum Version Required: 22.03
Pre-Conditions
Required Fusion Edit permission to be able to edit fusion reports, to optimize them.
You must be the owner of the report you would like to optimize to save back to the original report ID
Steps
Introduction
This guide provides best practices for optimizing reports in the Fusion reporting engine to improve performance, reduce timeouts, and enhance user experience.
Performance Optimization Techniques
Always start with...
Simplify the business case: Reports tend to grow in complexity over time; columns, filters, and options being added as new needs arise. Likely, consumers of your report are only looking at 2-3 columns on a daily basis, everything else may be clutter.
The largest performance gains are often found by checking that old items are still needed, removing columns (and related joins) and filters will both improve performance, and simplify/improve the experience for the consumer of your report. Things can always be added back later.
Report Structure Optimization
Break Down Large Reports: Split complex reports into smaller, focused reports to improve processing time and maintainability. Add multiple reports to a dashboard if you need to see the data together. Utilize dashboard tabs to break up large dashboards into smaller, more consumable sets.
Reduce Complex Expressions: Simplify calculations and expressions where possible to minimize processing overhead.
Flatten Query Structure: Eliminate sub-queries and sub-reports whenever possible. Most SQL queries can be restructured into a single layer. Consider using an AI LLM to help optimize complex query structures, you can copy the SQL from the 'Show Report SQL' tab and ask an AI to "reduce down to a single layer query without any sub-queries or CTEs".
Data Volume Management
Use Default Filters: Always configure default filters to prevent unfiltered report executions that can cause timeouts.
Filter Early on Date Ranges: Apply date range filters as early as possible in your query to limit the amount of data being processed.
Limit Row Returns: Configure your report to return only the necessary number of rows. Large result sets significantly impact performance.
Leverage Filters and Drill-Downs: Utilize these features to allow users to narrow down data as needed rather than loading everything at once.
Join Optimization
Enable "Join Required = False": Where appropriate, use this setting for joins in your report. This allows the Fusion engine to implement automatic join-pruning, only including joins when they're needed for user-selected filters or drill-downs.
Advanced Performance Tuning
Use the "Explain" Tab: When editing reports, check the Explain tab to view the Snowflake query profile. This helps identify which nodes are scanning the most data, allowing you to target optimization efforts. Filter these node more to reduce data scanning.
Avoid Pivoting Features: Minimize use of Child/Detail/Column/Column Totals features. When these are enabled, the report runs as a 'PIVOT' or 'ROLLUP' operation, which is significantly slower than standard queries.
Report Timeout Disable Behavior
Reports in Encompass that run beyond the timeout are marked as a timeout.
Reports that timeout are disabled using an exponential timeout calculation.
The equation is: 10 minutes * (2 ^ Fail Count)
For example:
1 failure = disabled for 20 minutes
2 failures = disabled for 40 minutes
3 failures = disabled for 80 minutes
4 failures = disabled for 160 minutes (2.7 hours)
5 failures = disabled for 320 minutes (5.3 hours)
6 failures = disabled for 640 minutes (10.7 hours)
7 failures = disabled for 1280 minutes (21.3 hours)
8 failures = disabled for 2560 minutes (42.6 hours)
9 failures = disabled for 5120 minutes (3.6 days)
10 failures = disabled for 10240 minutes (7.1 days)
continued...
This behavior is part of the limitless capabilites of Fusion reporting, and ensures that no single report causes slowness for all other users of the system.