I need help optimizing this

Questions & Answers
1

My report runs sometimes, but most of the times it runs out of memory and times out.

We have an Issue Type called “punch List. With several customer fields. These are the relevant ones here:

  • PL Severity Level (Independent form the Severity field)
  • PL Report Date

I want to be able to run the following report of the Average ticket Age, viewed by PL Severity:

image
image1097×710 37.4 KB

So I created the following Calculated Members:

“Punch List” type tickets that are open:

[Measures].[Punch List Active Issues]

(
[Measures].[Open issues],[Issue Type].[Punch List]
)

Age of the ticket. If “PL Report Date” is blank, then use the Created Date:

[Measures].[Age - from Report date]

CASE WHEN IsEmpty([Measures].[Issue PL Report Date])
     THEN DateDiffDays([Measures].[Issue created date],now())
ELSE DateDiffDays([Measures].[Issue PL Report Date],now())
END

Calculate the Average Age:

[Measures].[Average Age - from Report Date]

Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Punch List Active Issues]>0
  ),
[Measures].[Age - from Report date]
)

As mentioned above, the report crashes most of the time, as it needs to gather and calculate data from all ticket.

How can I optimize it?

Thank you,

2

Some tips to improve speed: How to improve performance of the query used in calculated measure - #3 by aherz

3

Hi @guillolb

If in Jira you utilize the “Resolution” field, you can optimize the formula like this by not using the “Open issues” measure:

Avg(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    IsEmpty([Measures].[Issue resolution date]
    AND
    DateCompare(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember.StartDate
    ) > -1
  ),
  CASE WHEN
  (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember
  ) > 0
  THEN
  DateDiffDays(
    CoalesceEmpty(
      [Measures].[Issue PL Report Date],
      [Measures].[Issue created date]
    ),
    Now()
  )
  END
)

Let me know if this improves performance and returns the expected results!

​Best regards,

Nauris / eazyBI support