Dashboard Performance - How to speed up reports loading on dashboard

Casper Stevenaar shared this question 22 days ago
Awaiting Reply

We are using a dashboard with multiple graphs/reports for KPI reporting. The view used by these reports is cached within Yellowfin. We find that the load times for each report are quite long (+/- 5 secs per graph) and reports are loaded sequentially, meaning that each page of the dashboard takes more than 30 seconds to load. We feel that that is too slow for an end-user facing report, esp. with all the data already sitting within Yellowfin.

Branch Report is not used and does not seem approprate because each graph has a different filter. Reason for this is that we want to use the filter in the name of the title in the Design.

Even if we don't change the filters and go from one Subtab to another all graphs are refreshed.


Do you have any guidance on how to improve the performance here? Is it possible to allow multiple graphs to load at the same time in a dashboard?


Our environment consists of a single container (database is hosted separately), where the container has 4 CPU available, and 15GB of RAM (of which max. 10GB is available as JVM)

Replies (7)

photo
1

Have you looked at the report loading option in Content Settings -> Dashboard Settings -> Loading Settings and change it to concurrent?

0699e8a1efcb3a012e1d80c9a53cfed6

photo
1

Hi Dean, have checked, and the Report Loading was already set to Concurrent.

photo
1

Hi Casper,

Thanks for reaching out to Yellowfin support.

The following article contains crucial trouble-shooting steps in order for us to investigate further. Please check out the link attached and let me know how it goes.

Regards,

Sri vamsi

photo
1

Hello,

I have followed the steps in de document and have found that the individual reports themselves seem to be pretty fast. Often, they even use the built-in caching from yellowfin and will not try to reach the database when retrieving the same report (with the same filters) a second time.

Navigation in YF is also good, so would think that it is not related to system resources.

Changing the connection settings (max connections) did not have any impact on the performance.

The only thing mentioned in the article that I am not able to investigate is the cached filter performance. Are there any ways to test this?


The way we set this dashboard up is as follows: there is a single view, which sits on a single table in a datasource. On top of this exists many reports, each of which requires only a small amount of data and runs fast individually(subsecond). Often these seem to make use of the report cache, as no query is visible in the source database. These reports are all collected in a dashboard, which has a few tabs. Every time a tab is opened, all reports take a while to load, even when switching back to a tab that was previously opened. Reports are populated one-by-one. It looks like no use is made of cache here, as we see queries running against the datasource (these take up to 0.5 secs).

Caching the View as described in YF does not seem possible, as the datasource uses pass-through authentication.

photo
1

Hi Casper,

It certainly is possible that caching filter sets could affect performance, the best way to prevent such a performance degradation is to schedule it to be outside of business hours and stagger it if possible, and another very good measure to take is to use a Secondary Connection Pool which is used to take care of filter caching and broadcasts, and it has its own configuration thus allowing the datasource timeout to be longer than normal.

When we select the 'Cached Values' in the value list setup, then a list of values are created based on current values of field (so no more refresh when a report is loaded). But if we select 'Cached Values On Demand' then the results are refreshed everytime we open reports.

5e79bc0a8d8dc777298c6033a1542911


And also there is a link related to cache which will explain about caching a view slowdown working within that view

https://community.yellowfinbi.com/topic/why-does-caching-a-view-slow-down-working-within-that-view

Regards,

Sri Vamsi

photo
1

Hi Sri Vamsi,

Thank you for your explanation. I have checked and it looks like the filters in the report are already set to 'Cached Values'. From that, I conclude that the Cached Filters are also not the reason for the slow performance of the dashboard.

Caching the view does not seem to be possible, since the view uses pass-through authentication, and the underlying datasource restricts data visibility based on the logged in user.

photo
1

Hi Casper,

Dashboard Design

The performance of a dashboard is directly correlated to the number and complexity of the queries needed to run against a database to support the tables and charts on that dashboard.

Follow this simple rule: The more reports you have on a dashboard, the more queries that have to occur. As a result, when you design your dashboard, you should always consider the performance at the same time.

The best practices for optimizing your dashboard design include:

Use subtabs: If you have many reports that you want to add to a dashboard consider the use of subtabs. This enables you to split what would be one big dashboard into multiple parts. Create smaller subject areas so that the dashboards make logical sense. Super complex dashboards are hard to use so when using subtabs, not only do you improve performance but you also make your dashboards easier to consume.

Use composite reports: A very good way to reduce the number of queries is to create multiple visualizations off a single query and then place those onto your dashboard. Given that most dashboards are designed around a specific functional area, the benefit of this is that only a single query is run, but the results can be used in many ways.

Cache filter values: Use these to limit the initial data set to be returned to the dashboard.

Default filter values: Use these to limit the initial data set to be returned to the dashboard. Allow the end user to manually, via filter widgets, extend the scope of analysis if they need to. This makes the initial load fast and caters to the most common use case for the end user.

Use drill through for detailed reports: Reports that contain thousands of rows can slow down your dashboard. Often your end users may not initially need access to row level data so use drill through functionality to allow your users to access the smaller slices of detail as needed.

Regards,

Sri Vamsi

Leave a Comment
 
Attach a file