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

Questions & Answers
,
1

I found this in a prev thread and it did warn of the query size. Hoping someone can figure out a way to reduce the depth of the query.

NonZero(Sum(
--set of issues committed AND completed in a sprint
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
--filter conditions for issues
[Measures].[Sprint issues committed] > 0
AND [Measures].[Sprint issues completed] > 0
AND [Measures].[Sprint Story Points committed] > 0
),
--sum up original estimate
([Measures].[Original story points resolved],
[Sprint].DefaultMember)
))

Here is the definition of the report as well:

{
  "cube_name": "Platform Delivery Data",
  "cube_reports": [ {
     "name": "Platform Commit Reliability (Combined by Sprint)",
     "result_view": "bar_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Sprint Story Points Committed -Cached]","[Measures].[Story Commit Reliability %]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Last 6 closed platform sprints]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Sprint].[Sprint])","members":[],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Sprint start date]"],"order":"ASC"},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[Platform Projects]"],"members":[{"depth":0,"name":"Platform Projects","full_name":"[Project].[Platform Projects]","calculated":true,"drillable":true}],"bookmarked_members":[],"current_page_members":["[Project].[Platform Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[2020 closed sprints]","[Sprint].[Plat Sprints]","[Sprint].[Last 6 closed platform sprints]"],"members":[{"depth":0,"name":"2020 closed sprints","full_name":"[Sprint].[2020 closed sprints]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"removed":true},{"depth":0,"name":"Plat Sprints","full_name":"[Sprint].[Plat Sprints]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"removed":true},{"depth":0,"name":"Last 6 closed platform sprints","full_name":"[Sprint].[Last 6 closed platform sprints]","calculated":true,"drillable":true}],"bookmarked_members":[],"current_page_members":["[Sprint].[Last 6 closed platform sprints]"]},{"name":"Team(TIR Official)","selected_set":["[Team(TIR Official)].[All Platform by tower (HWB/HWS)]"],"members":[{"depth":0,"name":"All Platform by tower (HWB/HWS)","full_name":"[Team(TIR Official)].[All Platform by tower (HWB/HWS)]","calculated":true,"drillable":true}],"bookmarked_members":[],"current_page_members":["[Team(TIR Official)].[All Platform by tower (HWB/HWS)]"]}]},"options":{"nonempty":true},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":false,"vertical":true,"swap_axes":false,"data_labels":true,"series_options":{"Commit Reliability % (Completed/Committed)":{"type":"column","separateAxis":1,"symbol":"diamond","dataLabelType":"top horizontal","color":"#89A54E"},"Sprint commitment changes":{"type":"line","separateAxis":2,"dataLabelType":"top horizontal"},"Sprint Story Points committed":{"dataLabelType":false,"color":"#4572A7"},"Story Points resolved":{"dataLabelType":false,"color":"#AA4643"},"Commitment Reliability Trend":{"type":"line-Dash","separateAxis":3},"Sprint Issues remaining trend":{"type":"line"},"Running reliability last 6 sprints":{"type":"line-Dash","separateAxis":1,"color":"#89A54E"},"Reliability trend 6 sprints":{"type":"line","separateAxis":1,"dataLabelType":false},"Commit Reliability % (#Points)":{"separateAxis":1,"dataLabelType":"top horizontal"},"Story Commit Reliability %":{"separateAxis":1}},"y_axis":{"0":{"min":"0","max":"400"},"1":{"max":1.25},"2":{"max":350}},"font_size":"large"},"table":{"freeze_header":true}},"description":"## Platform Commitment Reliability\n\n**Calculated by sprint. Story points committed / Story Points completed**"}
  } ],
  "calculated_members": [{"name":"Issue Sprint","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Sprint')","format_string":"","annotations":{"group":"Issue properties","predefined":true}},{"name":"Sprint start date","dimension":"Measures","format_string":"mmm dd yyyy","annotations":{"group":"Agile","predefined":true},"formula":"[Sprint].CurrentMember.get('Start date')"},{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","annotations":{"group":"Agile","predefined":true},"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  [Time].CurrentHierarchy.Levels('Day').DateMember(\n    [Sprint].CurrentMember.get('Start date')\n  )\n)"},{"name":"Plat Sprints","dimension":"Sprint","formula":"Aggregate(\n  Filter(\n    [Sprint].[Sprint].Members,\n    [Sprint].CurrentMember.Name MATCHES \"^EPSS Sprint.*\" OR\n    [Sprint].CurrentMember.Name MATCHES \"^EPC Sprint.*\"\n  )\n)","format_string":""},{"name":"2020 closed sprints","dimension":"Sprint","formula":"Aggregate(\n  Filter([Sprint].[Sprint].Members,\n    NOT IsEmpty([Sprint].CurrentMember.get('Start date')) AND \n    DateBetween(\n      [Sprint].CurrentMember.get('End date'),\n      'Jan 1 2020',\n      'Today') AND\n    ([Sprint].CurrentMember.Name MATCHES '^EPSS Sprint.*' OR\n    [Sprint].CurrentMember.Name MATCHES '^EPC Sprint.*')\n  )\n))","format_string":""},{"name":"Last 6 closed platform sprints","dimension":"Sprint","formula":"Aggregate({\n[Sprint].[EPSS Last 6 Closed],\n[Sprint].[EPC Last 6 Closed]\n})","format_string":""},{"name":"How We Build - ALL","dimension":"Team(TIR Official)","formula":"Aggregate(\n  {\n  [Team(TIR Official)].[How We Build - Scrum]\n  }\n)","format_string":""},{"name":"How We Ship - ALL","dimension":"Team(TIR Official)","formula":"Aggregate({\n  [Team(TIR Official)].[How We Ship - Scrum],\n  [Team(TIR Official)].[How We Ship - Kanban]\n})","format_string":""},{"name":"All Platform by tower (HWB/HWS)","dimension":"Team(TIR Official)","formula":"Aggregate({\n  [Team(TIR Official)].[How We Build - ALL] ,\n  [Team(TIR Official)].[How We Ship - ALL] \n  })","format_string":""},{"name":"How We Build - Scrum","dimension":"Team(TIR Official)","formula":"Aggregate(\n  {\n  [Team(TIR Official)].[Silverbolt],\n  [Team(TIR Official)].[Skydive],\n  [Team(TIR Official)].[X-wing],\n  [Team(TIR Official)].[Panama],\n  [Team(TIR Official)].[Enki],\n  [Team(TIR Official)].[Athena],\n  [Team(TIR Official)].[Labyrinth]\n  }\n)","format_string":""},{"name":"How We Ship - Scrum","dimension":"Team(TIR Official)","formula":"Aggregate({\n  [Team(TIR Official)].[Blitzwing],\n  [Team(TIR Official)].[Cadence],\n  [Team(TIR Official)].[EASE TechX],\n  [Team(TIR Official)].[FireWatch],\n  [Team(TIR Official)].[SpyderCo]\n})","format_string":""},{"name":"How We Ship - Kanban","dimension":"Team(TIR Official)","formula":"Aggregate({\n  [Team(TIR Official)].[Caffeine],\n  [Team(TIR Official)].[Ghost],\n  [Team(TIR Official)].[Meeseeks],\n  [Team(TIR Official)].[Blackjack],\n  [Team(TIR Official)].[Mobius]\n})","format_string":""},{"name":"Platform Projects","dimension":"Project","formula":"Aggregate({\n[Project].[EASE Platform CORE],\n[Project].[EASE Platform Shared Services],\n[Project].[Caffeine - Mobile CICD Platform (iOS and Android)]\n}\n)","format_string":""},{"name":"EPSS Last 6 Closed","dimension":"Sprint","formula":"Aggregate(\n  Filter([Sprint].[Sprint].Members,\n    NOT IsEmpty([Sprint].CurrentMember.get('Start date')) AND \n    DateBetween(\n      [Sprint].CurrentMember.get('End date'),\n      '12 weeks ago',\n      'Now') AND\n    [Sprint].CurrentMember.Name MATCHES '^EPSS Sprint.*'\n  )\n))","format_string":""},{"name":"EPC Last 6 Closed","dimension":"Sprint","formula":"Aggregate(\n  Order(\n  Filter([Sprint].[Sprint].Members,\n    NOT IsEmpty([Sprint].CurrentMember.get('Start date')) AND \n    DateBetween(\n      [Sprint].CurrentMember.get('End date'),\n      '12 weeks ago',\n      'Now') AND\n    [Sprint].CurrentMember.Name MATCHES '^EPC Sprint.*'\n  ),\n  [Sprint].CurrentMember.get('Start Date'),\nBASC\n))","format_string":""},{"name":"Sprint issues completed of committed","dimension":"Measures","formula":"-- this measure will calculate the resolved issues in the last sprint where an issue was commited and finally resolved\n-- this measure excludes committed and completed issues if they were reopened after sprint completion and were resolved in later sprint or outside of sprint\nCASE WHEN\n    [Sprint].Currentmember.Level.name = \"Sprint\"\nTHEN\n  NonZero(SUM(\n    Filter(\n      Descendants([Issue].Currentmember, [Issue].[Issue]),\n       [Sprint].CurrentMember.Name = [Measures].[Issue Sprint]      \n      ),\n      CASE WHEN\n      [Measures].[Sprint issues committed] \u003e 0\n      THEN\n      [Measures].[Issues resolved]\n      END\n  ))\nEND","format_string":""},{"name":"Story Commit Reliability %","dimension":"Measures","formula":"CASE WHEN\n[Measures].[Sprint issues committed] \u003e 0\nTHEN\n[Measures].[Sprint issues completed of committed]/\n[Measures].[Sprint issues committed]\nELSE\n0\nEND","format_string":"#0%"},{"name":"Sprint Story Points Committed -Cached","dimension":"Measures","formula":"Cache(\n  ( [Measures].[Story Points added],\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  [Time].CurrentHierarchy.Levels('Day').DateMember(\n    [Sprint].CurrentMember.get('Start date')\n  )\n)\n)","format_string":""}]
}
2

@martins.vanags @daina.tupule Thanks in advance!!

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