All eazyBI for Jira eazyBI for Confluence Private eazyBI

Optimize MDX calculated measures

MDX (Multidimensional Expressions) formulas are essential for advanced data analysis, enabling dynamic calculations across dimensions and hierarchies. This guide covers core principles, optimization strategies, and examples to help you create efficient and effective formulas.

When and what to optimize?

The report timeout limit is a safeguard to prevent inefficient reports from overloading the system. From a user perspective, regular reports should load within a few seconds, while complex reports should load within 20 seconds. In rare cases, very complex reports on large datasets may take up to 90 seconds, but users typically won't wait more than 120 seconds for a report to load.

Optimization should begin with the reports that fail the most. Use eazyBI usage statistics to identify these reports, focusing on those with the highest failure rates and longest execution times. Learn how to enable usage statistics on your Jira here.

Once problematic reports are identified, start by reviewing their configuration. To improve performance without immediately modifying MDX formulas, follow best practices for report creation. 

If you have reviewed the report configuration and it follows the best practices, then the next step is to review the MDX formulas and optimize them where possible. 

Retrieving measures from cube

Measures are numeric values or metrics in the data cube. They fall into three categories based on their execution speed and data source. When using those measures in your MDX formulas, pay attention to the fact that MDX with historical transition measures will perform slower than when you are using issue absolute measures. 

  • Standard numeric measures: Predefined metrics like issues created or resolved (fastest). See Jira Core measures.
  • Worklog Measures: Metrics based on logged work, e.g., hours spent (moderate speed). See Time tracking measures. 
  • Transition Measures: Workflow-related metrics, e.g., transition to status (slowest). See Issue change history measures.

Use tuples when possible

When possible, use tuples in your MDX formulas. A tuple is a collection of members from different dimensions, uniquely identifying a data cell in the cube. Tuples are efficient because they:

  • Reference dimension members directly without iterating through all issues.
  • Avoid the overhead of functions like Filter() and Descendants()
  • Allow the database to optimize queries more effectively.

See the following examples of how inefficient MDX formulas can be improved with optimized MDX formulas.

Highest priority issues in progress status

Show all highest priority issues that are currently in progress. 

INEFFICIENT FORMULA

Although this formula is correct, it is not efficient, as it iterates through all issues and filters them based on status and priority properties. 

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issue priority] = "Highest"
    AND
    [Measures].[Issue status] = "In Progress"
  ),
  [Measures].[Issues created]
)

OPTIMIZED FORMULA

This formula with tuple is much faster and better as it references each specific dimension member without iterating through all issues:

(
  [Measures].[Issues created],
  [Status].[In Progress],
  [Priority].[Highest]
)

Number of parent issues 

Show the number of parent issues (excluding sub-tasks)

INEFFICIENT FORMULA

Although this formula is correct, it is not efficient, as it iterates through all issues. 

NonZero(
  Count(
    Filter(
      -- Get all issues at Issue level
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      -- Filter only parent issues (no parent issue key)
      IsEmpty([Issue].CurrentMember.Get('Parent issue key'))
      AND
      -- Only count valid issues
      [Measures].[Issues created] > 0
    )
  )
)

OPTIMIZED FORMULA

Use tuple and reference issue type dimension hierarchy "By Type" member "Standard" that identifies all standard issue types (that are not sub-tasks):

(
  [Measures].[Issues created],
  [Issue Type.By type].[Standard]
)

Medium stories in specific statuses 

Show all medium priority issues with "T-shirt size" M, which are currently in one of multiple statuses (To Do, In Progress, Review, Approved, Done)

INEFFICIENT FORMULA

Although this formula is correct, it is not efficient, as it iterates through all issues and filters them based on issue type, custom field value, and status:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issue type] = 'Story' AND
    [Measures].[Issue T-shirt size] = 'M' AND
    (
      [Measures].[Issue status] MATCHES 'To Do' OR
      [Measures].[Issue status] MATCHES 'In Progress' OR
      [Measures].[Issue status] MATCHES 'Review' OR
      [Measures].[Issue status] MATCHES 'Approved' OR
      [Measures].[Issue status] MATCHES 'Done'
    )
  ),
  [Measures].[Issues created]
)

OPTIMIZED FORMULA

Instead, use Sum() function in which you can reference the status set that contains all the needed statuses and sum the tuple that uses specific dimension members:

Sum(
  {
    [Status].[To Do],
    [Status].[In Progress], 
    [Status].[Review],
    [Status].[Approved],
    [Status].[Done]
  },
  (
    [Measures].[Issues created],
    [Issue type].[Story],
    [T-shirt size].[M]
  )
)

When to descend

Tuples are great, but they cannot cover all situations. Most probably, you will need to use Filter() and DescendantsSet() when it comes to situations to count issues that have:

  • Multiple conditions in the same multi-value dimension
  • When issue must be filtered by different events/measures
  • Alternatives from different dimensions

If you are using heavy formulas in your eazyBI account, check if it is possible to reduce the data set - select only relevant projects or use the JQL filter in the import options to reduce the imported issue count.

See the following examples of how inefficient MDX formulas can be improved with optimized MDX formulas.

Highest priority issues or bugs

Show issues that are with the highest priority or are bugs. 

INEFFICIENT FORMULA

Although this formula is correct, it is not efficient, as it is filtering the issues by the tuple expression: 

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    (
      [Measures].[Issues created],
      [Priority].[Highest]
    ) > 0
    OR 
    (
      [Measures].[Issues created],
      [Issue Type].[Bug]
    ) > 0
  ),
  [Measures].[Issues created]
)

OPTIMIZED FORMULA

Use filtering by issue properties as they work faster:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    (
      [Measures].[Issue priority] = "Highest"
      OR 
      [Measures].[Issue type] = "Bug"
    )
    AND
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
  ),
  [Measures].[Issues created]
)

Created and resolved in the same time period 

Show all issues that are created and also resolved in the same time period 

INEFFICIENT FORMULA

The Count() the function is slower because it allows only one level of filtering — conditions are combined with AND, and all conditions are executed for the full dataset.

NonZero(
  Count(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issues created] > 0
      AND 
      [Measures].[Issues resolved] > 0
    )
  )
)

OPTIMIZED FORMULA

With IIF() function in the formula, the actual measure request is performed only on a virtual subset of data that satisfies the IIF() condition:

NonZero(
  Count(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      IIF(
        -- conditions on properties
        DateInPeriod(
          [Issue].CurrentHierarchyMember.Get('Created at'),
          [Time].CurrentHierarchyMember
        )
        AND
        DateInPeriod(
          [Issue].CurrentHierarchyMember.Get('Resolved at'),
          [Time].CurrentHierarchyMember
        ),
        -- genuine measure - executed if above conditions true
        [Measures].[Issues created],
        -- alternative if above conditions false
        0
      ) > 0
    )
  )
)

Count of issues assigned to the user, completed in the sprint with logged hours by the assignee

Show the number of issues that were assigned to the user, completed in the specific sprint and have hours logged by the assignee. In the report the "Sprint" dimension is on columns and "Assignee" on Rows.

INEFFICIENT FORMULA

Gets slow because Transition is taken from the Transitions data table, Hours are taken from the worklogs data table, and both measures are executed on all issues.

NonZero(
  Count(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      --issue was completed in this sprint
      [Measures].[Sprint issues completed] > 0
      AND
      -- hours were logged against the issue by the current assignee
      (
        [Logged by].[User].GetMemberByKey(
          [Assignee].CurrentHierarchyMember.Key),
        [Measures].[Hours spent]
      ) > 0
    )
  )
)

OPTIMIZED FORMULA 1

However, if issues are completed within the sprint, the Sprint remains the last sprint for the issue and is available as the property. That allows for reducing the dataset for the heavier measures.

Sum(
  Filter(
    DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
    -- primary condition - issue relates to this sprint as its latest
    [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name),
    -- numeric part for sum - executed on reduced dataset
  CASE WHEN 
    -- issue was completed in this sprint - further reduces dataset for the current sprint
    [Measures].[Sprint issues completed]>0
  THEN
    CASE WHEN
      -- third level condition - executed on issues that have been completed in specific past or current sprint
      (
        [Logged by].[User].GetMemberByKey(
          [Assignee].CurrentHierarchyMember.Key),
        [Measures].[Hours spent]
      ) > 0
    THEN
      -- the final number for sum
      1
    END
  END
)

OPTIMIZED FORMULA 2

As the "Sprint" and "Assignee" dimensions are used in the report, use numeric expressions to tie conditions to the report context before starting to iterate through all issues.

CASE WHEN
  -- primary filter for the context relevance - checking if iteration is justified
  [Measures].[Sprint issues completed] > 0
  AND
  (
    [Logged by].[User].GetMemberByKey([Assignee].CurrentHierarchyMember.Key),
    [Measures].[Hours spent]
  ) > 0
THEN
  Sum(
    Filter(
      DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
      -- primary condition - issue relates to this sprint as its latest
      [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
    ),
    -- numeric part for sum - executed on reduced dataset
    CASE WHEN 
      -- issue was completed in this sprint - further reduces dataset
      [Measures].[Sprint issues completed] > 0
    THEN
      CASE WHEN
        -- third level condition - executed on issues completed in sprint
        (
          [Logged by].[User].GetMemberByKey(
            [Assignee].CurrentHierarchyMember.Key),
          [Measures].[Hours spent]
        ) > 0
      THEN
        -- the final number for sum
        1
      END
    END
  )
END

Issues added to the sprint after it started and were not initially committed to it

Need a count of issues that were completed in past sprints or are in the ongoing sprint. Issues have been added during a sprint but were not committed to the displayed sprint.

INEFFICIENT FORMULA

The Count() function is slow because it allows only one level of filtering - all three conditions are joined with AND, requiring the calculation to evaluate all three conditions for every issue in the sprint. The "Sprint issues committed" and "Sprint issues added" conditions are particularly resource-intensive, significantly slowing down the overall query.

CASE WHEN
  [Sprint].CurrentHierarchyMember.Level.Name = "Sprint"
THEN
  Count(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
      AND
      IsEmpty(
        (
          [Measures].[Sprint issues committed],
          [Assignee].CurrentHierarchy.DefaultMember
        )
      )
      AND
      [Measures].[Sprint issues added] > 0
    )
  )
END

OPTIMIZED FORMULA 1

With Sum(), it is possible to split the set of issues and apply the more "heavy" conditions to a smaller subset of issues in the Sum() numeric part. 

CASE WHEN
  [Sprint].CurrentHierarchyMember.Level.Name = "Sprint"
THEN
  Sum(
    Filter(
      DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
    ),
    CASE WHEN
      [Measures].[Sprint issues added] > 0
      AND
      IsEmpty(
        (
          [Measures].[Sprint issues committed],
          [Assignee].CurrentHierarchy.DefaultMember
        )
      )
    THEN
      1
    END
  )
END

OPTIMIZED FORMULA 2

The same formula can be optimized even further if the "Project" dimension is used in "Pages" to filter the smaller data set. The iteration through projects should only be done if there is a selection on Project dimension and no selection on Issues.
That requires additional checks and conditional branches like this:

CASE WHEN
  -- nothing select on issues
  [Issue].CurrentHierarchyMember IS [Issue].CurrentHierarchy.DefaultMember
  AND
  -- some selection done on Projects
  NOT
    [Project].CurrentHierarchyMember IS [Project].CurrentHierarchy.DefaultMember
THEN
  -- optimized iteration through selected projects only
  <the improved iteration>
ELSE
  -- standard iteration through selected issues
  <the standard iteration>
END


The final optimized formula is this:

CASE WHEN
  [Sprint].CurrentHierarchyMember.level.name = "Sprint"
THEN
  CASE WHEN
    -- nothing selected on issues 
    [Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
    AND
    -- some selection done on Projects  
    NOT
      [Project].CurrentHierarchyMember IS [Project].CurrentHierarchy.DefaultMember
  THEN
    -- optimized iteration through selected projects only 
    SUM(
      Filter(
        Generate(
          -- creare set of selected projects only
          DescendantsSet(
            [Project].CurrentHierarchyMember,
            [Project].[Project]
          ),
          -- create the set of selected project issues only      
          DescendantsSet(
            [Issue].[Project].GetMemberByKey(
              [Project].CurrentHierarchyMember.Key
            ),
            [Issue].[Issue]
          )
        ),
        [Measures].[Issue Sprint] = [Sprint].CurrentMember.Name
      ),
      CASE WHEN
        [Measures].[Sprint issues added] > 0
      THEN
        CASE WHEN
          IsEmpty((
            [Measures].[Sprint issues committed],
            [Assignee].Currenthierarchy.Defaultmember
          ))
        THEN
          1
        END
      END
    )
  ELSE
    -- standard iteration through selected issue
    SUM(
      Filter(
        DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
        [Measures].[Issue Sprint] = [Sprint].CurrentMember.Name
      ),
      CASE WHEN
        [Measures].[Sprint issues added] > 0
      THEN
        CASE WHEN
          IsEmpty((
            [Measures].[Sprint issues committed],
            [Assignee].Currenthierarchy.Defaultmember
          ))
        THEN
          1
        END
      END
    )
  END   
END

Precalculate data using JavaScript custom fields

If the MDX formula is timing out and the above optimization options haven't helped, you can always check whether it is possible to precalculate all or part of the formula using the JavaScript custom field as it is calculated during import. 

See the following example of how inefficient MDX formulas can be improved with JavaScript custom fields.

Average number of days for issue prioritization

Calculate the average number of days it takes for issues to get their first priority assigned after creation. 

INEFFICIENT FORMULA

The formula is correct, but it uses historical measures to calculate the average days, and for larger accounts, this formula timed out. 

Avg(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    NOT IsEmpty(
      ([Measures].[Transition from first timestamp],
      [Transition Field].[Priority],
      [Priority].[No priority])
    )
  ),
  DateDiffDays(
    [Measures].[Issue created date],
    TimestampToDate((
      [Measures].[Transition from first timestamp],
      [Transition Field].[Priority],
      [Priority].[No priority]))
  )
)

OPTIMIZED FORMULA

In this case, the optimized option is to use JavaScript to precalculate the data during the import process. Then, newly created measures will be used to calculate the average of days.

The first configuration is for "Prioritization time" for the days until the prioritization is done.

The account-specific field settings are:

  • Internal name: days_till_priority 
  • Data type: decimal
  • Display name: Prioritization time
  • Measure: ✓
  • Additional advanced settings: multiple_dimensions = ["Time"] 

The JavaScript code:

var priorityDate = null;
var timeSpent = null;
var createdDate = new Date(Date.parse(issue.fields.created));

if (issue.fields.priority) {
  if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
    var histories = issue.changelog.histories;

    for (var i = 0; i < histories.length && !priorityDate; i++) {
      var history = histories[i];

      if (history.items && history.items.length > 0) {
        for (var n = 0; n < history.items.length && !priorityDate; n++) {
          var item = history.items[n];

          if (item.field == 'priority' && item.fromString == "Not Prioritized") {
            priorityDate = new Date(Date.parse(history.created));
          }
        }
      }
    }
  }
}

if (priorityDate) {
  timeSpent = (priorityDate - createdDate) / (24 * 60 * 60 * 1000);
  return priorityDate.toISOString().substr(0, 10) + "," + timeSpent;
}

The second configuration is for the field "Prioritizations" for the fact of prioritization. 

The account-specific field settings are:

  • Internal name: prioritization
  • Display name: Prioritizations
  • Data type: integer
  • Measure: ✓
  • Additional advanced settings: multiple_dimensions = ["Time"]

The JavaScript code:

var priorityDate = null;

if (issue.fields.priority) {
  if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
    var histories = issue.changelog.histories;

    for (var i = 0; i < histories.length && !priorityDate; i++) {
      var history = histories[i];

      if (history.items && history.items.length > 0) {
        for (var n = 0; n < history.items.length && !priorityDate; n++) {
          var item = history.items[n];

          if (item.field == 'priority' && item.fromString == "Not Prioritized") {
            priorityDate = new Date(Date.parse(history.created));
          }
        }
      }
    }
  }
}

if (priorityDate) {
  return priorityDate.toISOString().substr(0, 10) + ",1";
}

After both measures are imported, the MDX formula is straightforward and very efficient:

CASE WHEN
  [Measures].[Prioritizations] > 0
THEN
  [Measures].[Prioritization time] / [Measures].[Prioritizations]
END

Conclusion

  1. Prioritize Tuples: Use tuples to slice data wherever possible. Tuples avoid unnecessary iterations and reduce processing time.
  2. Minimize Descendants and Filters: Only use these functions when tuples cannot address the requirements.
  3. Order Conditions by Execution Speed:
    • Member properties (fastest)
    • Standard measures
    • Worklog measures
    • Transition measures (slowest)
  4. Leverage Numeric Measures: Use numeric expressions to tie conditions to the report context.
  5. Use Conditional "Stairs": When iterating through members, structure conditions hierarchically to minimize the number of members processed by slower calculations.