Query timeout error with Descendants filter

Questions & Answers
1

Hi,
I keep getting query timeout error on the below query. Can someone suggest how to improve this? I’m using jira server with EazyBI.

Note: [Measures].[Open Bugs] is a user defined measure.

/* Gives the total open bugs that have exceeded beyond the specified number of SLA days -
P0 = 1 day, P1 = 7 days, TBD a.k.a. Untriaged bugs = 7 days */

CASE WHEN
[priority].CurrentMember.Name = “P0”
THEN
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]), [Measures].[Open Bugs] > 0 AND
DateDiffDays([Issue].CurrentMember.Get(‘Created at’), Now()) > 1
)))

WHEN
([priority].CurrentMember.Name = “P1” OR [priority].CurrentMember.Name = “TBD”)
THEN
NonZero(
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]), [Measures].[Open Bugs] > 0 AND
DateDiffDays([Measures].[Issue created date], Now()) > 7
)))

END

2

Hi @Sahana_Rao

Welcome to the eazyBI community!

Yes, the calculation is quite heavy as it iterates through all issues.
Instead of doing that, I would suggest importing and using Age interval dimension.

  1. Select interval dimensions to be imported from the Import options screen and run data import.
    https://docs.eazybi.com/eazybijira/data-import/jira-issues-import#JiraIssuesImport-AdditionaldimensionsforusergroupsandIntervaldimensions

  2. From the dimension, split intervals into the most detailed level (0-1, 2-7, …) in a way that, combining them, you would be able to get all needed intervals for you.
    https://docs.eazybi.com/eazybi/analyze-and-visualize/interval-dimensions

  3. Finally, you would use those intervals in the measure forming tuples of intervals and measure Issues created instead of those iterations through issues. As only currently unresolved issues are included in Age interval intervals, then you can use measure “Issues created”.
    For larger intervals, you would use a sum of smaller intervals (see the case for P1)

    CASE 
 [Priority].CurrentMember.Name 
WHEN "P0"
THEN
 Val(([Measures].[Issues created],
    [Age interval].[00 - 01]))
when "P1"
THEN
 Val(([Measures].[Issues created],
   [Age interval].[00 - 01])
  +
  ([Measures].[Issues created],
   [Age interval].[02 - 07]))
END

Please use your intervals (the name of them are created automatically based on the number of intervals) in the formula!

Hope it would work for you!

Best,
Ilze / support@eazybi.com

3

Hi ilze,
the age intervals are set to 3 digits as follows -
|000 - 009
|010 - 019
|020 - 029
|030 - 039
|040 - 049 and so on… How can I modify this to 1 day interval and 7 day interval and 60 days interval?

4

Hi,

great that you imported the interval dimension! Now, you have default intervals (by 10 days), but you can change them.

You can modify intervals from the Age interval dimension --> All hierarchy level members section by clicking on “edit” for member level [Age interval]. Then write in the condition you need (the main principles of how to define different intervals are described https://docs.eazybi.com/eazybi/analyze-and-visualize/interval-dimensions).

As you need 1, 7, and 60-day intervals, I would suggest creating 0-1, 2-7, 8-60, 61-… intervals.
Then you would be able to combine them ([00 - 01] for 1 day; [00 - 01] and [02 - 07] for 7 days, [00 - 01], [02 - 07] and [08 - 60] for 60 days) as I described in the 3rd step.

See in the screenshot below how I would define the members.

image
image2268×1146 239 KB

Best,
ilze / support@eazybi.com

5

thanks for the detailed explanation. I don’t seem to have edit permission on the age interval. I have reached out to our admins to help change the interval.

6

Hi Ilze, I got the average age fixed by the admins to what you had suggested. The query works now, but the output is not correct though. It is pulling all the issues types vs just the bugs and when I change the [Issues created] to [Open Bugs] the numbers don’t match with Jira.

[Measures].[Open Bugs] is defined as ([Measures].[Issues due], [Issue type].[Bug])

If I just use [Measures].[IssueType].[Bug] then all I get is 0.

Any suggestions on how to get just the bugs?

thanks in advance.