Building performant reports is a critical aspect of data analysis and presentation. With the increasing amount of data generated every day, it’s essential to have reports that are not only informative but also fast and reliable. Here are some tips to help you build performant reports:
▪Overall
▪If it is slow in the data source, it will be slow in the Desktop version of the tool. If it is slow in Desktop, it will (almost always) be slow on the Server.
▪Performance tuning is highly individualized to the context of your environment, data, analysis, and report design. What works in one situation may not work in another. Test and iterate to see what works in your specific case.
▪Data Architecture
▪Data Volume
▪Only load the lowest level of detail needed. Consider pushing aggregation to the source database or in the query.
▪Limit the number of tables & columns to only the ones you need in your data model. Delete tables and columns if they are not being used in any of your reports or calculations.
▪Consider using Row Level Security when applicable to reduce the amount of data each user is interacting with.
▪Consider using restrictive filters for the initial state of the report. The fewer rows the initial query returns, the quicker your visuals will load. End users can always expand the ranges and values of the filters to return more data.
▪Data Architecture
▪Query Model
▪Try to use the Import storage mode whenever possible instead of In-Database/Direct Query. The more that you can rely on the tool’s in-memory tabular engine, the better performance will typically be compared to an In-database query for all visuals on a page.
–Note on row level security and CSS
–the preference regarding use of restricted data in CSS (such as GSR) is to use In-Database/Direct Query mode so that the row-level security is handled on the database side instead of in the dashboard tools.
–If Import mode is needed (such as for performance reasons) then the security tables can be imported into the tool. In this scenario, the row-level security model must be implemented in the tool.
–EXCEPTION: for finance data (in the FIN_MAIN schema), only In-Database/Direct Query mode is allowed
▪Query Design
▪Moving data manipulations to database queries can help performance. Whether you’re doing calculations, formatting, or grouping at the appropriate granularity in your SQL, this will eliminate the tool spinning its wheels on these items.
▪Incremental Refresh
▪If the tool supports incremental refresh, using this feature can reduce load times especially when using scheduled updates.
▪Data Model Complexity
▪Star Schema
▪If your data model is essentially a single wide table, try converting your data model into a star schema model. Power BI, for example, is optimized for Star Schema data models and converting from a flat format to a Star Schema can have a significant impact on performance. Star Schema designs are a best practice design.
▪Cardinality
▪Minimize use of tables with high cardinality (high numbers of unique values like transaction IDs, for example)
▪Transformations
▪If you have the option to transform data in a source like a database, consider doing so. These speeds up data refreshes and reduces resources required to transform data in the tool.
▪Data Types
▪DateTimeformat (mm/dd/yyyy12:00:00am) can be converted to Date if the time is not being used.
▪If you have numeric data with a large number of digits to the right of the decimal, then consider reducing the precision.
▪If you have text showing TRUE or FALSE, you can change these to be binary, so 1 and 0.
▪Data Architecture
▪Calculation Design
▪Consider pushing calculations to the source database to improve performance.
▪Strings and dates are slow; numbers and Booleans are fast for the data engine.
▪Implement complicated column and aggregations calculations to the source database where possible.
▪Filter Design
▪Hide or Delete unused filters.
▪Minimize filters that have high cardinality values.
▪Minimize on-canvas filters since they can drain performance. If the tool provides a fly-out filter panel, use that when possible.
▪Caching and Scheduling
▪Understand how the tool caches query results an
▪Consider using the tools server-side scheduling features to pre-load your report into the server’s memory on a scheduled basis.
▪Make sure this schedule aligns with the source database refresh frequency to eliminate wasted loads.
▪Visualizations
▪Chart Data Density
▪Be aware of the tool’s chart data density limitations to avoid slow-rendering charts.
▪Avoid showing too many data elements in a chart for the initial load and/or consider only showing this detail when drilling to details.
▪Charts and other visual elements per page
▪Try to use as few visual elements in your report as possible. This will decrease the number of calculations being performed when rendering your report.
▪Limiting the number of visualizations per page doesn’t mean eliminating them. You can add related visuals on additional report pages. Keep it simple. Allow your users to incrementally drill down to details, rather than trying to show everything then filter. Use the M-A-D principle!
▪Chart elements
▪Practice minimalism. Display only the chart elements that are necessary for the consumer to interpret the chart easily. Avoid unnecessary ink. Avoid unnecessary precision of numbers on Axes and Labels. These all save computing resources.
▪Custom Visuals
▪Be sure to put a custom visual through its paces to ensure high performance. Poorly optimized custom visuals can negatively affect the performance of the entire report.
▪Source Database Location
▪Ensure the report and data source are in the same region. With the report and data source in the same region, you can reduce network latency. The results are faster data transfer and faster query execution.
▪Source Database Resources
▪If the source database has limited resources (CPU, RAM, Storage), query execution speed can be impacted.
▪Source Database Data Architecture
▪Similar to the data architecture of a report in the visualization tool, the source database architecture can impact performance.
▪A Star Schema data model design is often the right solution to optimize database performance if the source is a relational database.
▪Desktop
▪Use your tools’ desktop performance monitoring features to help identify causes of slowness
▪Query mode (Import vs In-Database)
▪Calculations
▪Visual rendering
▪Server
▪Work with your server admin to use the server’s performance monitoring features to help diagnose:
▪Query wait times / long running queries
▪Refresh duration
▪Note: sometimes the overall capacity (CPU and/or memory) settings on the server/cloud may be impacting your report’s performance.
▪Testing
▪Test performance on the Desktop first. Then the server. Then make changes and iterate.
▪Make sure to do this before launching to users.
Conclusion
building performant reports requires a combination of data optimization, query optimization, report design, report caching, and hardware considerations. By following these tips, you can ensure that your reports are fast, reliable, and effective in delivering insights and decision-making information.
Interested in custom designed high quality reports or would like to speak with an expert? Anyon Consulting – BI group can help! Our experts can answer any questions on customize dashboards, help with your database implementation, optimize your database platform, and much more. Contact us today to learn more about our Custom Report Development.
