Data Table Indexing to help with Performance Issues - Clarification Please!!!

[jstoessel]

Currently we are looking at improvements to the performance of our reports and dashboards.

The topic of the recently released dashboard data refresh was brought up.  The question is this:

Is the data refresh something that potentially helps performance only at the local user browser level?

Continuing down, or maybe up the performance path - an Indicator will run on a schedule and pull a specific set of data from table.  Then when the dashboard widget is built on that indicator, the user doesn't have to wait for the refresh from the table, they are getting the data directly from the indicator generated table.

The slowest potential performance of a dashboard widget, would be anything that is directly pulling from the table.  I also believe that this is the only place that completing table level indexing would potentially improve the performance of the widgets appearing on a dashboard.

My real use case:

• Incidents Closed on This Fiscal Year

and the breakdown is by

• Business Service>Owned by>Department

Obviously this is not a standard way of looking at the incident data, but it is the requirement for where we are in our maturity path of moving from managing to keep incident assignment group queues empty toward looking at the stability of our business services.

Because this is a pretty unique way of looking at the data - I wrote Indicators to help narrow the scope of data processing.  I wasn't able to just do a single report with these breakdowns, I would get errors around too long to generate the report, etc.

We have 33 departments in IT, and over 1,500 Business Service named owners, and we create about 1,500 - 2,000 new incidents per day.  This means, I pretty much have 33 versions of every report at the Indicator level, which is an effort to improve performance as well.

So my bottom line question - would going through the process of performing indexing on the incident portion of the task table make PAE widgets built on indicators perform better?

Also - please correct anything I am obviously not understanding about how these things work!  Thanks!

@Thomas Davis 
@olgaredkina 

@Adam Stout 

[David Hepburn · Accepted solution]

Is the data refresh something that potentially helps performance only at the local user browser level?

If you have the cache period set to 24h, the first load of the dashboard in that period will pull data from the scores tables.  Subsequent loads within that period will pull data from the cache & should be faster.

This may assist with your question as well:  Data caching in Platform Analytics (SN Docs) 

So my bottom line question - would going through the process of performing indexing on the incident portion of the task table make PAE widgets built on indicators perform better?

No, re-indexing [incident] will not affect PA widgets negatively nor positively because PA scores are not stored in [task] nor any child tables of [task].

[David Hepburn]

@jstoesselhow long is it taking your dashboard to load, widgets included?

[jstoessel]

I built a new dashboard yesterday - 8 widgets, all based on Indicators

From a fresh browser it takes 10-12 seconds on average (Example Dashboard attached)

I have one that is a pivot table with 8 columns and that alone takes between 25-40 seconds

Very inconsistent, and super frustrating for users.

[David Hepburn]

@jstoessel 

Yeah, in my experience, pivot tables always take a while to do their thing.  I haven't found a way around that yet. 

I see that you're using Platform Analytics, which is awesome 🙂  the nomenclature for dashboard components is a bit different when using Platform Analytics vs Perf Analytics.  When using Platform Analytics, your "widgets" are properly called visualizations 🙂 

• Looks like your dashboard is including stats for the entire year.  IMHO not a great approach to have YTD data load upon initial dashboard load.
  
  • Consider having all visualizations set to show data from the past month, for example, with date range pickers to allow that range to be adjusted. 
  • This would allow your db to load more quickly; then if someone wants to see a wider date range, it may take a few seconds.  In my experience, users prefer something that loads quickly initially & then are more forgiving if they've adjusted an option that results in extra time loading data.
• Make sure your visualizations using criteria based on indexed fields.  (In other words, are the viz / indicators looking at indexed fields?  If not, they should be.  And make sure none of your indicators / sources are doing string search, for example).
• Have you been able to identify any particular visualization that's taking a while to load?
• Any warnings or errors in the job logs?
• Is your PA cleanup job running regularly?  (Perf Analytics > Automation > Schedules > "Clean PA collections" job).

Hope these ideas are helpful.  I'm really curious to see how things progress w/ your dashboard.