Help reducing query to calculate completed of committed sprint story points! Keeps timing out at 120s

Questions & Answers
,
3

You are using Issue level calculation and several dimensions with multiple member selection on Pages.

1. Multiple selections or calculated members on Pages
Team(TIR Official) - pulls in in total 17 members
Project - pulls in 3 projects

Multiple pages selections will impact any calculation in the by number of members.
17 (teams) * 3 (projects)
If the calculation is applied to Issue level, then it will be multiplied by count of imported issues:
17 (teams) * 3 (projects) * number of issues

You may consider using some JavaScript calculated custom field to define a new custom field Team group to group those teams and narrow it down to less selections there. However, It might not work if you need to analyse sprint data by Team changes.

2. Issue level calculation
The definition includes a different formula for Sprint issues completed of committed. I assume you picked it up from and would like to apply it for Story Points posted in the top of the post.

Measure Sprint issues completed of committed has additional filters to improve the performance:

  1. It works on Sprint level only. You can apply the filter in your formula. You are using Sprints on Rows and the formula should work for your case with this filter.
  2. It validates if the sprint is the final sprint for an issue explicitly comparing Sprint member name with the Issue Sprint property. This is the main performance improvement in the report - it pulls in only issues for a particular sprint. Otherwise the complex calculations are applied to any issue imported into the account. For large accounts, I suggest using only issue properties in the filter and move any other validations outside of the filter.
  3. Any measure and comparison by measures are moved outside of the initial filter.

Here is a suggested formula with those improvements:

CASE WHEN
    [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(SUM(
    Filter(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
     -- filters issues for final sprint only - the main impact on performance
       [Sprint].CurrentMember.Name = [Measures].[Issue Sprint]      
      ),
      CASE WHEN
      [Measures].[Sprint issues committed] > 0
      THEN
      [Measures].[Original story points resolved]
      END
  ))
END

The formula above for each sprint will pick only a small set of issues (instead of all issues imported into the account) and will apply any calculation there.
For example with the same report setup and updated formula eazyBI will apply the calculations:
17 (teams) * 3 (projects) * number of issues in selected sprints only

The formula will ignore story points completed in sprint if an issue was reopened after sprint completion and resolved outside of the sprint or in the next sprint. Those issues will have issue property Issue sprint representing (no sprint) (resolved outside of the sprint). The issue will not be counted as completed of committed in any sprint. The property Issue sprint might show you another sprint (next sprint) if an issue was finally finished in some sprint. Then the issue will be counted as completed of committed in this final sprint.

If you would like to count issues committed and marked as completed but reopened later for any sprint, let me know the use case.

Daina / support@eazybi.com