Cache dashboard results to improve performance when switching tab?

[db_pdx]

Hi folks: I am searching for suggestions to improve reporting performance in dashboards.

We have a couple of simple, executive dashboards that consist of 5-10 tabs of cube views. These work great for managing a common set of reports for end users.  Parameters are managed within the dashboard and persist when switching cube views (tabs); all that works great.

However, users complain that it is slow to switch between cube views (tabs).  It appears that each cube view is re-run when switching tabs rather than simply displaying the data that was previously refreshed.  While most reports take <2 seconds to run, a few can take in the 8-15 second range which is deemed unacceptable.

Are there any methods (formal or hacked together) for caching cube view results so that switching tabs simply displays already refreshed data rather than re-running the cube view?

Thanks,   -db

[RobbSalzmann]

My first area I would want to focus on is how to improve the loading performance of the slower CVs,  What things are slowing them down? Things like too many rows/columns and member formulas that are dynamically calculated can adversely affect reporting performance, the latter being significant in it's effect on query performance.  15 seconds to load a CV seems excessive.

[db_pdx]

A bit of everything.  Granular balsh report; with E#Entity.Tree expansions; lots of time views for comparing CurMth to PrMth, and to Forecast, with variances. All per user request.

They are fine with the longer initial load time if it means they have the data available for analysis.  But the switching back and forth between tabs is what is frustrating them due to the full re-run of the CV.

[RobbSalzmann]

yep, sounds pretty typical.  And probably a good reason for all.
I'd be looking to pre-calc those variances into stat accts.  Usually no need to calc them every time the CV loads.  It might be surprising how much that helps.

I agree with you, it would be nice to cache parts of the dashboard so every click isn't a full round trip reload to the server

[tomdarnall]

It takes a little more effort to set up, but have you considered radio buttons?  They should improve your overall performance.  Also, they don't continually revert back to the first tab when you refresh.

[db_pdx]

Hi Tom: could you elaborate on how this would work? The Radio Button would...drive a dynamic dashboard selection?

Edit: I believe that would still require a refresh, correct?  That leaves me in the same position I'm trying to avoid.

[tomdarnall]

So radio buttons should only refresh the page you're on...whereas tabs try to refresh everything.  This is where I think it could provide some time savings for you.  

When you set up the component, you define the buttons options with a param.  Typically I'd just use a param type = delimited list.  The param can be hardcoded with the various options, or can contain variables if you want the list to be dynamic based on other selections.

[db_pdx]

Understood. I should clarify, the issue is not with making parameter selections.  That is handled at the start. After the user sets their appropriate reporting dimensions, they won't change.

It's about a user navigating to the P&L to review something, then to the balance sheet (8-15 to display it), then back to the P&L, and back to the balance sheet (another 8-15 seconds because it doesn't cache the result and re-runs the CV), all within the same overall dashboard.

[tomdarnall]

Gotcha, yeah...unfortunately I don't think the radio buttons natively prevent a re-running of the CV.

[JackLacava]

I'd say there are two main approaches:

1. As @RobbSalzmann said, one can turn those dynamic calcs into stored formulas. You add a little more work to your calculations/consolidations, but you gain in reporting speed. This can be game-changing on some views.
2. You could export those views to reports, then display them as embedded files in the dashboard. No calculation, only visualization. If they don't need to actually interact with the numbers, this is a simple option.
3. Another, somewhat more convoluted but potentially less invasive approach, is to pump those calculated numbers into another database, and then report from there. At the end of the day, a CV is just a table... To export it you can use FdxExecuteCubeView or custom SQL, triggered by some automated job; and then you can retrieve it with regular adapters and stuff it into a BiViewer or Pivot Grid. The advantage of FdxECV is that maintenance of the layout logic remains in the CV itself.

They are both legitimate approaches, each with pros and cons. What to choose will depend on specific considerations for each Cube View.