New Measure Performance is much slower than OOTB measures. How do I improve it?

Questions & Answers
1

Hello

I have created a new measure to count “Customer Facing” issues creation.
The new measure, namely [Measures].[Customer Facing Issues Created], is defined below. It basically counts all the issues where the custom measures [Measures].[Customer Count created] OR [Measures].[Case Number Count created] are > 0.

NonZero(Count(
Filter(
– iterate through set of issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
– apply filter criteria to each issue
[Measures].[Customer Count created] > 0 OR
[Measures].[Case Number Count created] > 0
)))

The new measure work correctly but the performance is poor. A report using [Measures].[Customer Facing Issues Created] takes about 20 seconds to open, whereas a report with [Measures].[Issues Created] takes< 5 seconds.

Any suggestions how to improve this? There are 4 associated fields coming from Jira, “Customer” names and/or “Case” numbers. A background process counts the number of Customers and Cases and adds these counts to new fields, i.e. Customer Count & Case Number Count.
Is there a better configuration to get these calculations to be quicker? Or index these columns?

Thanks

2

You are using Issue level calculation for this measure. The calculation will be applied to any issue imported into the account for any cell where this measure works.

We suggest using Issue properties as filters for any issue level calculations to improve the performance. In this case, you can use properties Issue customer count and Issue case number count instead of respective measures to validate data per issue. The filter by an issue date property, for example, Issue created date, is often used to improve reports with Time dimension. You would like to use some measure (for example, Issues created) as an additional filter to pull valid issues for any cell. I will switch function Count to Sum and use the measure Issues created both as a counter and a filter for those changes.

Here is a suggested formula with those changes:

NonZero(sum(
  Filter(
  – iterate through set of issues
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    – apply filter criteria to each issue
    ([Measures].[Issue Customer Count] > 0 OR
    [Measures].[Issue Case Number Count] > 0)
    AND
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
  ),
  [Measures].[Issues created]
))

Daina / support@eazybi.com

3

Thanks Daina for your response and your explanation of the problems with my calculation.

I implemented your suggested formula on my system with a small change. The performance is much improved so I really appreciate your input. The reports now generate in less than 10 seconds from previous timings of over 1 minute.
I used “NOT IsEmpty([Measures].[Issue Customers])” on the Customer Field and the Case Numbers field as these are string fields. Does this seem ok?
We do have Case Count/Customer Count fields in Jira but they don’t seem to be being imported into EazyBI so I will need to talk to the eazybi adminstrator to see about getting them imported.

NonZero(sum(
Filter(
– iterate through set of issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
– apply filter criteria to each issue
(NOT IsEmpty([Measures].[Issue Customers]) OR NOT ISEmpty([Measures].[Issue Case Numbers]))
AND
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)
),
[Measures].[Issues created]
))

Kind Regards,
Clare

4

You can use Not isEmpty instead of comparison to 0 for any numeric fields. Some string custom fields imported as dimension might have value (none) there, though, and the comparison Not IsEmpty will not work. For those cases, you can use a comparison to value (none) instead of Not IsEmpty.

You would like to import the custom field as a property in addition to other import options for the field to get the formula to pick up the values.

Daina / support@eazybi.com

5

Thank you for your reply. I will work with administrator to get those custom fields imported as a property and then use them.
The solution above works so thank you