Tips to improve reporting performance
Since Holistics runs all of the calculations against your database, your reports' performance largely depends on the processing power of your database and data warehouse.
While we are continuously working on improving Holistics's performance, you can also use the following tips to optimize your datasets, reports and dashboards.
Avoid using large tables in datasets
It is not advisable to include large tables in an end-user facing dataset. Models creating from large tables (for example, events or log tables) should only serve as a base for you to build other derived models upon.
Large tables often have indexes that are well-known to analysts and engineers. However, end-users of datasets may not know this and can generate poor performance queries while exploring.
Pre-aggregate your data
Instead of exploring from large raw tables, it is better to write Transform Models to filter out data relevant to a particular question, pre-aggregate it to a grain that can satisfy most of your end-users' data questions, and persist the result back to the database for smoother exploration experience.
Avoid creating datasets with complicated relationships
When using a dataset, if you realize you have to drag in fields from three or four models to create a report, then you should pre-join those tables with a Transform Model and persist the result.
This way you can have better control of your join performance, and provide an easier interface for your end-users to explore the data.
Beware of potential fan-out when creating relationships
When you set relationships between models (many-to-one or one-to-one ), we assume that the fields at the "one" end is already unique.
If the field is not unique, when you drag in fields from those two models, a fan-out will happen. The result set will be a Cartesian product of the two models and may "explode" into millions of rows.
Use Holistics's modeling syntax in Transform Models
When writing Transform Models, instead of using the normal model_alias.field_name notion to select fields from tables, use {{ #model_alias.field_name }} so Holistics will only select the necessary fields to be included in the generated queries.
Reduce the number of widgets in a dashboard
When you open a dashboard without cache or refresh a dashboard, each widget will spawn a report query job that takes up a slot in your tenant's job queue. If the number of widgets triggered is greater than the job queue limit, some widgets will be blocked until other widgets are loaded, and free slots become available.
Besides, Drill-Through is a great way to provide additional details without jamming more visuals onto the current dashboard.
Therefore, it is advisable to have maximum 10 - 15 widgets per dashboard for better report performance. This will also make your dashboards easier to navigate.
Report poor performance incidents with Holistics
If you believe the above steps are not the underlying causes of your performance issues, feel free to reach out to our support team. It is helpful to refer to this document on Best practices when reporting slow-running queries when creating a support ticket.