Very slow performance in report data processing

Dear esteemed eazyBI team,

I created a report based on the requirements provided to me but it’s very slow in loading and sometimes eazyBI crashes on opening the report for the first time, so I need your help to do a health check and let me know how I can improve the measures to make the report much faster and avoid crashes

Notes

  • Please note that I’m very new to eazyBI and this is my first report
  • We are using eazyBI for Jira on premise and Jira is Data Center edition

Business Requirements
Title: Team completion performance report

  1. Create a report on team tasks for a specific project (here I added to the Pages Project and Issue type so I can filter the report data set to show only Jira issues in this project and also with issue type task only to improve performance)
  2. For each team member (assignee) in rows show the following stats for only assigned tasks
    -Total Number of tasks assigned
    -Total Number of Open tasks (status = open or in-progress or reopened or on-hold)
    -Total Number of Completed tasks (status = Ready for validation or validated or done or dropped and progress = 100)
    -Total Number of tasks completed on-time (Baseline end date = End date.)
    -Total Number of tasks delayed (Baselined end date < End date)
    -Completion Score based on following table

Below are my measures which I believe are causing the slowness in the report:
Completed Tasks

(
 [Status].[Ready for Validation] + [Status].[Validated] + [Status].[Done] + [Status].[Dropped]
)

Open Tasks

(
 [Status].[Open]+[Status].[Reopened]+[Status].[In Progress]+[Status].[On Hold]
)

Ontime Tasks

Count
(
  Filter
  ( 
    Descendants([Issue].CurrentMember,[Issue].[Issue])
    ,
    [Measures].[Issues created] > 0
    AND
    (
      [Measures].[Issue status] = "Ready for Validation"
      OR
      [Measures].[Issue status] = "Validated"
      OR
      [Measures].[Issue status] = "Done"  
      OR
      [Measures].[Issue status] = "Dropped"  
    )
    AND
    [Measures].[Issue Progress] = 100
    AND    
    DateCompare
    (
      [Measures].[Issue Baseline end date]
      ,
      [Measures].[Issue End Date.]
    ) >= 0  
     
  )
)

Delayed Tasks

Count
(
  Filter
  ( 
    Descendants([Issue].CurrentMember,[Issue].[Issue])
    ,
    [Measures].[Issues created] > 0
    AND
    (
      [Measures].[Issue status] = "Ready for Validation"
      OR
      [Measures].[Issue status] = "Validated"
      OR
      [Measures].[Issue status] = "Done" 
      OR
      [Measures].[Issue status] = "Dropped"   
  
    )
    AND
    [Measures].[Issue Progress] = 100
    AND
    DateCompare
    (
      [Measures].[Issue Baseline end date]
      ,
      [Measures].[Issue End Date.]
    ) < 0  
     
  )
)

Task Completion Score

CASE 
  WHEN 
    [Measures].[Delayed Tasks] = 0
  THEN
    1

  WHEN
    [Measures].[Delayed Tasks] > 0
    AND
    [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] > 0.95
  THEN
    1

  WHEN
    [Measures].[Delayed Tasks] > 0
    AND
    [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] < 0.95 
    AND
    [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] >= 0.9 
  THEN
    0.95
  
  WHEN
    [Measures].[Delayed Tasks] > 0
    AND
    [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] < 0.9  
    AND
  [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] >= 0.85 
  THEN
    0.75
  
  WHEN
    [Measures].[Delayed Tasks] > 0
    AND
    [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] < 0.85 
    AND
  [Measures].[Delayed Tasks] / [Measures].[Completed Tasks] >= 0.80 
  THEN
    0.5

 ELSE
   0
   
END

Thanks in advance for your quick response and support to fix the performance issue in this report

Hello,

I can see a couple of ways to improve the performance in your report.

The first and easiest one is to use cache in your ontime tasks and delayed tasks measure. It will prevent the calculation to be done each time you need these measure in your Task Completion Score measure.

The second one is a bit tricky but should have more impact on the performances : use calculated javascript fields. The idea is to create two calculated fields , let’s call them “Is on Time” and “Is Delayed”. These fields will be calculated for each issue.
The syntax for the Delayed Task calculated field should be something like :
if (issue.fields && issue. fields.progress.value == 100 && (issue.fields.status.name == “Ready for Validation” || issue.fields.status.name == “Validated” || issue.fields.status.name == “Done” || issue.fields.status.name == “Dropped”) ) {
if (issue.fields.customfield_xxx && issue.fields.customfield_yyy ) {
baselineenddate = new Date(issue.fields.customfield_xxx);
issueenddate = new Date(issue.fields.customfield_yyy);
if (baselineenddate < baselineenddate ) {
return 1;
} else {
return null;
}
}
} else {
return null;
}
Declare the calculated fields as dimension so you will be able to have “Issue with xxx” measures. Then your report will just consist in having Assignee dimension in rows and your “Issues with xxx” and “Issues with yyy” measures as columns.
I hope it was clear enough :slight_smile:

Hello @motagaly ,
One option is to improve the “Delayed Tasks” and “On-time tasks” formulas, as they iterate through all your issues and can cause performance issues.
Here is an example of “Delayed tasks” of an optimized formula, and ina similar manner, you can improve your other calculation:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issue status] MATCHES "Ready for Validation|Validated|Done|Dropped"
  ),
  CASE WHEN 
    [Measures].[Issues created] > 0
    AND
    [Measures].[Issue Progress] = 100
    AND
    DateCompare(
      [Measures].[Issue Baseline end date],
      [Measures].[Issue End Date.]
    ) < 0
  THEN
    1
  END
)

Here you can read more about optimizing your MDX formulas: Optimize MDX calculated measures

If this doesn’t help then another approach is to use JavaScript calculated field as mentioned by @Sebastien_RATEL .
You can also use eazyBI Custom field AI assistant to help you with the code based on your fields: AI Assistants

Best,
Gerda // support@eazybi.com