1.     Use Summarized data instead of Detailed data

In many parts of the system, information is held at a very granular level – for example timesheets, expenses, demand, supply etc. – whereas the report only requires summaries or totals in order to display. This is very often the case for charts, which are typically always showing totals.

In order to avoid extracting all the detailed data, when only the summary is required, ensure that the Data – Row Display Settings – Row Format field is set to Summarized Data instead of Detailed Data. In one real-life example, this had the effect of reducing run time from 2 minutes to 2 seconds!

2.     Ensure data is filtered to return only what’s required

Filters within widgets and reports have a huge impact on the run time, and ensuring just the data of interest is extracted is again key. The following best practice should avoid the main pitfalls:

• Where data is date dependent, ensure there is a suitable Start and End date specified, so typically something like :

Note the use of the Tokens to ensure that the date “window” is relative to “today” so that the window moves through time automatically.

• Ensure that where, for example, Forecast data is referenced, the correct Forecast State is specified to ensure that only the relevant data values are extracted, rather than all version variations over time. In the example below, only the current live forecast (for level 1 projects) will be extracted for the period from the current month for 9 months. 

Note also that some entities also have the ability to filter on fields such as “Is Latest” to reduce data returned :

3.     Minimize and review the use of “Drill down” within chart widgets

These, by their nature, require huge amounts of data to be loaded before they can be run – needing all the detailed data to support however many drill-downs are provided (up to 5 levels!). Over-ambitious drill-down is one common reason why dashboards take a long time to load. This, coupled with specifying them at higher levels of the project hierarchy, is a recipe for huge amounts of data to be loaded & processed…all with the associated wait times.

So while the drill-down functionality is very useful, there are ways to ensure that it doesn’t detract from the user experience:

• Remove the drill-down widgets from any common landing pages (My Work, Projects Home etc.) avoiding reloading every-time you navigate around.
• Avoid drill-downs on Projects dashboards above level 2 – thus minimizing the data that needs to be consolidated.
• Where drill-downs are required:
  • limit the number of levels to what’s absolutely necessary.
  • Position any widgets with drill-down on subordinate, “non default”, views that require selection when required. This avoids always refreshing them on main views.
  • Consider separating the drill-down elements into their own separately configured widgets and utilize context filters to tie them to the current project data

4.     Minimize, where possible, the use of “OR” in advanced filters.

“Or”, by its nature, will multiply the number of rows returned and in some more complex scenarios result in slowing response times – especially if the fields are custom field look-ups (eg Resource or Project) that will not be automatically indexed. As an alternative consider specifying separate widgets to display the separate elements of the join.

In a recent example a complex filter in the format below had 3 “ORs” to cover various situations for the user running the widget:

• Team Manager code #OWNRESOURCE# would bring back results for a team that you are the manager of.
• Line Manager code #OWNRESOURCE# would bring back any resources that have you logged as their line manager (not necessarily in the department/team that you manage)
• Resource Team Code #OWNRESOURCE# would bring back all the team members for a team (department) that you are part of i.e. you don't have to be a manager to see the rest of your team's availability

The last of these three especially would mean that if you managed one team but were part of a team of managers then the combination of the three filters would mix all your subordinates along with all the other managers in your "manager team" which could be data heavy (and also confusing!). 

The solution here is to separate into 3 widgets to provide the results : Team Manager, Line Manager and Team Members on a separate Resourcing View. This is both much more performant as well as much less confusing!

5.     Avoid specifying unused fields.

This may sound obvious, but any fields specified as Fields, Group by or Sort by will result in data and processing….so if they are not needed then don’t include them. This is often the case when configuring a chart – only summary data is usually required, plus the Sorting is not usually relevant or is done automatically in the Chart

6.     Avoid creating widgets that return a lot of data.

This could be 100’s of rows of summarised data or a few rows with dozens of columns. In both cases this could require a huge amount of data to be extracted and downloaded and therefore takes time. As an alternative, consider creating these as a context report instead – accessible as a pop-out from the relevant view (Projects example shown below).

7.     Use Excel or the reporting API for large data extract reports.

When it is necessary to extract large amount of data, the processing and wait times can be reduced or managed better by :

• Specifying the report to output in Excel. This will significantly reduce the time spent rendering the data on screen plus avoids the need to repeat the process if you decide to ultimately extract into Excel.
• Consider using the reporting API to pull the data directly into the target tool (eg Business Objects or Excel etc.). This avoids the on-screen rendering completely.

Resource Requests

This is a resource hungry view as in addition to Demand, the system also loads in Supply and calculates Remaining Demand. This can be loading data over a lot of planning periods (around 100 for a full 18 month timeframe including split periods). Coupled with this, data could be for as many forecast rows as there are in the system, so the data can quickly multiply.

The key here is to utilize the filter (including Saving useful ones for re-use) in order to:

• Only view as many periods as are necessary to process – eg the next 3 to 6 months. Don’t view closed months as they are not relevant.
• Use the filters to reduce the rows. Especially useful are :
  • Projects – perhaps limiting to one program or sub-set of the hierarchy
  • Teams – perhaps limiting to just the teams you are responsible for
  • Roles -  perhaps limiting to just the Role (or roles) you are interested in

If you find that the number of rows being processed exceeds around 100, the performance will be impacted – so try to use a filter that will return a number of rows below that number.