Reducing Report Load Time 7~8 sec – Help Optimizing

Questions & Answers
1

Hello eazyBI Community everyone!

I am currently building a report in EazyBI.
The Rows contains Measures dimensions, and the Columns contains the Group dimensions.

The report includes the same SUM calculation about four times, which results in a noticeable load‑time delay of roughly 7–8 seconds. The account I am working with contains approximately 40,000 issues.

Could anyone suggest ways to reduce the report’s loading time?
Do you have any ideas for optimizing the MDX code?

Below is the MDX for the custom measure [Measures].[Per %] that I am using:

Sum(
    Filter(
        Descendants(
            [Issue].CurrentMember,
            [Issue].[Issue]
        ),
        (
            DateDiffDays(
                [Measures].[Issue S-A],
                [Measures].[Issue S-A Complated]
            ) > 0
            AND
            [Measures].[Issue type] = "Sub‑Task"
        )
        OR
        (
            DateDiffDays(
                [Measures].[Issue A],
                [Measures].[Issue A Complated]
            ) > 0
            AND
            [Measures].[Issue type] = "Project"
        )
    ),
    [Measures].[Issues created]
)
+
Sum(
    Filter(
        Descendants(
            [Issue].CurrentMember,
            [Issue].[Issue]
        ),
        DateDiffDays(
            [Measures].[Issue S‑D],
            [Measures].[Issue S‑D Complated]
        ) > 0
        AND
        [Measures].[Issue type] = "Sub‑Task"
    ),
    [Measures].[Issues created]
)
+
Sum( ... ... )
2

Hi @NextLife

Descending through issue dimension members at issue level creates a very complex query that requires a lot of computing power to calculate results. Because the same steps are repeated for every row in your report

The best way to optimize this query would be to introduce a string type of account-specific calculated fields where you calculate the difference in days between two dates and check if that difference is more than 0, and then import both with the “dimensions” checkbox to create new dimensions for filters.

In your case, it would be required to create two fields and import as dimensions

  1. Days between A and A complated more than 0

  2. Days between SA and SA complated more than 0

Logic could be something like this: if A exists and A Complated exists and difference between them > 0, then return “Yes”, otherwise return “No”.

Similar for the second field.

See some examples here:

Once imported with the dimension checkbox, eazyBI would create new dimensions that you can use in your calculation formula and it would be many times faster

(
[Days between SA and SA complated more than 0].[Yes],
[Issue type].[Sub-task]
)
+
(
[Days between A and A complated more than 0].[Yes],
[Issue type].[Project]
)

Martins / eazyBI support