Issues aging report problem

hello guys,

I’m struggling with performance of JIRA aging report.

having in mind, that i do not have an access to EazyBI administative console thus i cannot manage Age Interval dimension, so to satisfy the requirements, I prepared the list of the columns for counting the particlar issues age (1d, 2d, 3d… 9d, >9d).

so, the setup:

pages: project, issue type, status
rows: priority
columns: list of columns including 1d, 2d … more10d

MDX spec of the calculated measure (1d, 2d, 3d… 9d, >9d):

CASE 
  WHEN [Measures].[Workdays in transition] > 0
  THEN
    Sum(
      Filter(
        Descendants([Issue].CurrentMember,[Issue].[Issue]),
        [Measures].[Workdays in transition] > {NUM_OF_DAYS-1} AND
        [Measures].[Workdays in transition] <= {NUM_OF_DAYS}
      ),
     [Measures].[Issues created]
    )
END

where NUM_OF_DAYS is 1, 2,3…>10 correspondingly for each of the columns mentioned above.

The report works, however the columns are not responsible and often fail with 60 seconds operation’s timeout (nonempty is disabled for rows, columns).

Your help with performance is very appreciated!

thanks.

Hello @XaHDpE,

​The current setup indicates that you are looking for the time that the issue has spent in the current issue type and priority. The measure “Workdays in transition” records the data when the issue leaves the specific combination. An issue that has not been touched for a long time, would not have the record of the last status/type/priority time.

​The dimensions “Issue Type” and “Priority” are parts of the historical measure context.
​The dimension “Status” refers to the current status of the issue.
The use of the measure “Issues created” in the depth of calculation makes the current expression ignore issues that no longer comply with the Issue Type and Priority combination.

​Since the issue history contains multiple records for each issue, the retrieval of relevant historical measures from the database is even slower than the retrieval of the primary issue measures.
​​
Depending on your requirement, there are several options.

​If you are looking for the time issue spent in a specific issue type and priority combination, and these are frequently changed fields - the optimization would include the reduction of the issue dataset before the query for time in priority + issue type.

​The expression might then be as follows.​

CASE
​--there are issues relevant for the current selection
  WHEN [Measures].[Workdays in transition] > 0
  THEN
    Sum(
      Filter(
        DescendantsSet(
​--since a project is selected in pages - reducing the dataset to iterate through that project only
​--a slightly different approach is needed if several projects are expected from multi-select list
​         [Issue].[Project].GetMemberByKey(
​            [Project].CurrentHierarchyMember.Key),
        ​[Issue].[Issue]),
​--primary condition - check if issue is relevant for the report context
​       ([Measures].[Issues created],
--resetting dimensions that are relevant for historical context, but not relevant to issue current context​
​--the less dimensions are reset - the smaller the dataset for heavier calculations
​       [Priority].CurrentHierarchy.DefaultMember,
​       [Issue Type].CurrentHierarchy.DefaultMember
​)>0),
​--the numerical value for Sum - executed on reduced filtered issue set
​   CASE WHEN
        [Measures].[Workdays in transition] > {NUM_OF_DAYS-1} AND
        [Measures].[Workdays in transition] <= {NUM_OF_DAYS}
​    THEN
​     1
   ​END
    )
END

​Alternatively, if you want to see time in … for the issues that currently comply with the filters, you might remove lines 15-18, which made the above expression ignore some dimensions.

​Yet another alternative is possible if you are interested in the total age of issues that comply with the filter of project, issue type, status, and priority.
​You might summarize the number of issues created in N workdays before today.

​The expression might be as follows.​

​Sum(
--set for sum - days
​   [Time].[Day].DateMembersBetween(
--the start date is a bit trickier to include weekend dates
   DateAddDays(
    DateAddWorkdays("today", -{Num_OF_DAYS+1}),
    1),
   DateAddWorkdays("today", -{NUM_OF_DAYS})),
​--numeric value for sum
​ [Measures].[Issues created])​

​The final measure for “>10 workdays” might use a slightly different Time function.

Sum(
  PreviousPeriods(
    [Time].[Day].DateMember(
      DateAddWorkdays(
        "today",
        -10))),
 [Measures].[Issues created])

​Regards,
​Oskars / support@eazyBI.com