Calculated measure is timing out

I have a report that is showing the percentage of parent (ie non-subtasks) committed to at the beginning of a sprint that we complete by the end of that sprint (I received help for the measure here). However, it seems like my “Completed committed issues” calculated measure is causing things to always time out even though I’m limiting things to this year (there aren’t that many tickets/sprints), and I cannot understand why. Attached is the exported definition of my report.

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Sprints completed as committed",
     "folder_name": "2021 Engineering OKRs",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Committed issues]","[Measures].[Completed committed issues]"],"members":null}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[All closed sprints]"],"members":null,"bookmarked_members":null}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Time","selected_set":["[Time].[Current year]"],"members":[{"depth":0,"name":"Current year","full_name":"[Time].[Current year]","annotations":{"group":"Default","predefined":"true"},"calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":null,"current_page_members":["[Time].[Current year]"]},{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all"}],"bookmarked_members":null,"current_page_members":["[Project].[All Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[All closed sprints]"],"members":[{"depth":0,"name":"All closed sprints","full_name":"[Sprint].[All closed sprints]","annotations":{"predefined":"true"},"calculated":true,"drillable":true,"dimension":"Sprint","expanded":true,"drilled_into":false}],"bookmarked_members":null,"current_page_members":["[Sprint].[All closed sprints]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":null}
  } ],
  "calculated_members": [{"dimension":"Time","name":"Current year","format_string":"","formula":"Aggregate({\n  [Time].[Year].CurrentDateMember\n})"},{"name":"All closed sprints","dimension":"Sprint","format_string":"","formula":"Aggregate(\n  Order(\n    Filter([Sprint].[Sprint].Members,\n      [Sprint].CurrentMember.getBoolean('Closed') AND\n      NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),\n    [Sprint].CurrentMember.get('Start date'),\n    BASC\n  )\n)\n"},{"name":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Closed],\n  [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was in a sprint at a sprint start time\n  [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Closed],\n  -- An issue was in a sprint at closing\n  [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","formula":"(\n  [Measures].[Sprint issues at closing],\n  [Transition Status.Category].[Done]\n)\n"},{"name":"Committed issues","dimension":"Measures","formula":"Sum(\n  Filter(\n    --Descendants([Issue].Currentmember, [Issue].[Issue]),\n    [Issue type].[Issue type].Members,\n    NOT [Issue type].CurrentMember.GetBoolean('Subtask')\n    AND [Measures].[Sprint issues committed] \u003e 0\n    -- Disregard 0 point tickets, which are for SDMs and aren't guaranteed to be worked on\n    AND [Measures].[Sprint Story Points at closing] \u003c\u003e 0\n  ),\n  [Measures].[Sprint issues committed]\n)","format_string":""},{"name":"Completed committed issues","dimension":"Measures","formula":"Sum(\n  Filter(\n    Descendants([Issue].CurrentMember, [Issue].[Issue]),\n    IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key'))\n    AND\n    -- Was committed to the sprint\n    [Measures].[Committed issues] \u003e 0\n  ),\n  [Measures].[Sprint issues completed]\n)","format_string":""}]
}

Hi @dave ,

It looks like the condition on the calculated measure “Committed issues” inside the calculated measure “Completed committed issues” is the cause of time out. The “Completed committed issues” already retrieves committed non-sub-task issues. The only condition it doesn’t have and the “Committed issues” has is the check on the “Sprint story points at closing”. Try the formula for “Completed committed issues” below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- is parent issue
    IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key'))
    AND
    -- Was committed to the sprint
    [Measures].[Sprint issues committed] > 0
    -- Disregard 0 point tickets, which are for SDMs and aren't guaranteed to be worked on
    AND Not IsEmpty([Measures].[Sprint Story Points at closing])
  ),
  [Measures].[Sprint issues completed]
)

Iterating through issues and for each retrieved issue, iterate through Issue type dimension members is the most likely cause of the time-out error. The above calculation should work without a timeout.

Best,
Roberts // support@eazybi.com

I noticed this is including issues that were closed prior to the sprint start, which I’d like to filter out. If I update it to include something like this in the filter:

AND DateCompare([Measures].[Issue resolution date], [Measures].[Sprint start date]) > -1
AND DateCompare([Measures].[Issue closed date], [Measures].[Sprint start date]) > -1

the query times out. How can I optimize this query?

Hi @dave,

That seems unusual. Are the issues added to the Sprint already in closed statuses or resolved? You may want to share specific information in a support ticket at support@eazybi.com with reference to this community post.

Instead of the conditions inside the Filter() function, I recommend working with the Sprint issues committed measure. You can view its formula by clicking on the “Show” button or below:

( [Measures].[Transitions to],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was in a sprint at a sprint start time
  [Issue Sprint Status Change].[Future => Active]
)

It looks at the Sprint issues when it was transitioned from status Future to Active, no matter the issue status. You may want to define the issue statuses acceptable for commitment and create a new calculated member in the Transition Status dimension with these statuses aggregated. See an example below for the calculated member “Commitment Statuses”:

Aggregate({
  [Transition Status].[To Do],
  [Transition Status].[Backlog],
  [Transition Status].[Selected for Development]
})

You can then define a new calculated measure based on the “Sprint issues committed” with the Transition Status dimension calculated member, in the example below it is the “Sprint issues committed #2”:

( [Measures].[Transitions to],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was in a sprint at a sprint start time
  [Issue Sprint Status Change].[Future => Active],
  [Transition Status].[Commitment Statuses]
)

Use this calculated measure instead of the “Sprint Issues committed” as a condition inside the Filter() function. See a picture of a sample report below:

The best example is the Sprint “Winter-PI4-S2” where 21 issues were committed. Still, only 17 of them were in the “Commitment Statuses” at the start of the Sprint, and that is taken into account in the updated “Completed parent issues with story points #2”.

Best,
Roberts // support@eazybi.com

Thanks, that did the trick for me! Is there a more performant way to query all committed tickets (per my new measure [Measures].[Committed issues]) that were completed by the end of the sprint? My following query is timing out:

NonZero(Sum(
  Filter(
    Descendants([Issue].Currentmember, [Issue].[Issue]),
    [Measures].[Committed issues] > 0
  ),
  [Measures].[Sprint issues completed]
))

Hi @dave,

I am sorry the report is still timing out.
What is the formula for the measure “Committed issues”? My suggestion is the one below to retrieve the number of completed issues at the end of the Sprint:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- is parent issue
    IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key'))
    AND
    -- Was committed to the sprint
    [Measures].[Sprint issues committed #2] > 0
    -- Disregard 0 point tickets, which are for SDMs and aren't guaranteed to be worked on
    AND Not IsEmpty([Measures].[Sprint Story Points at closing])
  ),
  [Measures].[Sprint issues completed]
)

Where the Sprint issues committed #2 is calculated with steps from my previous post.

Best,
Roberts // support@eazybi.com

So, I have the following calculated member named [Transition Status].[Not resolved] based on your response above:

Aggregate({
  [Transition Status].[Open],
  [Transition Status].[In Refinement],
  [Transition Status].[Ready for Dev],
  [Transition Status].[In Progress],
  [Transition Status].[Dev Complete],
  [Transition Status].[Ready for QA],
  [Transition Status].[In QA]
})

Here’s my definition for [Measures].[Committed issues]:

(
  [Measures].[Transitions to],
  [Transition Field].[Sprint status],
  [Sprint Status].[Active],
  -- An issue was in a sprint at a sprint start time
  [Issue Sprint Status Change].[Future => Active],
  -- Make sure the issue wasn't closed/done prior to the sprint starting
  [Transition Status].[Not resolved]
)

I’ve since realized I don’t need the original filtering I posted, eg IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key')) and Not IsEmpty([Measures].[Sprint Story Points at closing])). So, my [Measures].[Completed committed issues] is:

NonZero(Sum(
  Filter(
    Descendants([Issue].Currentmember, [Issue].[Issue]),
    [Measures].[Committed issues] > 0
  ),
  [Measures].[Sprint issues completed]
))

However, this times out consistently.

Any ideas @roberts.cacus? Thanks in advance!

For reference, here is my exported report definition:

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Sprints Completed As Committed",
     "folder_name": "2021 Engineering OKRs",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Committed issues]","[Measures].[Completed committed issues]"],"members":null}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[All Sprints]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Sprint].[Sprint])","members":null,"bookmarked_members":null}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Bug","full_name":"[Issue Type].[Bug]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Task","full_name":"[Issue Type].[Task]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Story","full_name":"[Issue Type].[Story]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Spike","full_name":"[Issue Type].[Spike]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":null,"current_page_members":["[Issue Type].[Bug]","[Issue Type].[Task]","[Issue Type].[Story]","[Issue Type].[Spike]"]},{"name":"Time","selected_set":["[Time].[Current year]"],"members":[{"depth":0,"name":"Current year","full_name":"[Time].[Current year]","annotations":{"group":"Default","predefined":"true"},"calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":null,"current_page_members":["[Time].[Current year]"]},{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":null,"current_page_members":["[Project].[All Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[All Sprints]"],"members":[{"depth":0,"name":"All Sprints","full_name":"[Sprint].[All Sprints]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":false},{"depth":1,"name":"DAM board","full_name":"[Sprint].[DAM board]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Sprint].[All Sprints]"}],"bookmarked_members":null,"current_page_members":["[Sprint].[All Sprints]"]}]},"options":{"nonempty":"rows"},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[{"dimension":"Measures","name":"TEST","formula":"NonZero(Sum(\n  Filter(\n    Descendants([Issue].Currentmember, [Issue].[Issue]),\n    [Measures].[Committed issues] \u003e 0\n  ),\n  [Measures].[Sprint issues completed]\n))","format_string":""},{"dimension":"Measures","name":"TEST2","formula":"NonZero(Sum(\n  [Measures].[Committed issues],\n  [Measures].[Sprint issues completed]\n))","format_string":""}]}
  } ],
  "calculated_members": [{"dimension":"Time","name":"Current year","format_string":"","formula":"Aggregate({\n  [Time].[Year].CurrentDateMember\n})"},{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was in a sprint at a sprint start time\n  [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Closed],\n  -- An issue was in a sprint at closing\n  [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","formula":"(\n  [Measures].[Sprint issues at closing],\n  [Transition Status.Category].[Done]\n)\n"},{"name":"Committed issues","dimension":"Measures","formula":"(\n  [Measures].[Transitions to],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Active],\n  -- An issue was in a sprint at a sprint start time\n  [Issue Sprint Status Change].[Future =\u003e Active],\n  -- Make sure the issue wasn't closed/done prior to the sprint starting\n  [Transition Status].[Not resolved]\n)","format_string":""},{"name":"Completed committed issues","dimension":"Measures","formula":"-- 2021-04-22: The commented out code is the previous, verified-to-work query\nNonZero(Sum(\n  Filter(\n    Descendants([Issue].Currentmember, [Issue].[Issue]),\n    [Measures].[Committed issues] \u003e 0\n  ),\n  [Measures].[Sprint issues completed]\n))\n--NonZero(Sum(\n--  Filter(\n--    Descendants([Issue].Currentmember, [Issue].[Issue]),\n--    -- Make sure this wasn't closed prior to sprint start\n--    NOT ([Measures].[Sprint issues committed], [Transition Status.Category].[Done]) \u003e 0\n--    AND [Measures].[Sprint issues committed] \u003e 0\n--  ),\n--  [Measures].[Sprint issues completed]\n--))","format_string":""},{"name":"Not resolved","dimension":"Transition Status","formula":"Aggregate({\n  [Transition Status].[Open],\n  [Transition Status].[In Refinement],\n  [Transition Status].[Ready for Dev],\n  [Transition Status].[In Progress],\n  [Transition Status].[Dev Complete],\n  [Transition Status].[Ready for QA],\n  [Transition Status].[In QA]\n})","format_string":""}]
}

Any help would be greatly appreciated!

Sorry for my impatience, but this is preventing me from being able to publish some dashboards that my company needs. @roberts.cacus any ideas?

Hi @dave ,

Sorry for the late response. Thank you for sharing the definition of the report.

One thing that will immediately improve the report load time is disabling the “Hide empty” option in the toolbar and instead, filter the report rows by one of the measures, “Committed issues” or “Completed committed issues,” to be greater than zero. See more details here on how to do that - Create reports - eazyBI for Jira.

The report then could look similar to the one below:

Another improvement could be adding a condition based on issue properties inside the Filter() function as the first condition. The IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key')) served that part in the previous versions of the calculated measure. Currently, I don’t have a suggestion.

The last performance hit comes from the multiple page selections in the Issue type dimension. For each selection, eazyBI creates a report and joins them together. Consider if the issue types play a big role in your use case.

Best,
Roberts // support@eazybi.com

Thank you so much! Adding IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key')) and removing the issue type page definitely gave me a noticeable performance boost. It’s still a little slow, but it’s not timing out anymore.