Optimization of a query for "drill through issue"

Questions & Answers
1

Hi there,
I have got a report, which is providing me with the number of resolved issues by employee for a certain category of projects. This is working fine and shows the correct results.
However, when I drill through issues on a specific member, I am getting a timeout.
I am assuming this might be related to the number of issues or an inefficient query.
Unfortunately, I am not able to figure out which part of the query is inefficient and might get optimized.
Can you please have a look and guide me into the right direction?
This is the error message from the logs:
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] [pivot_table.query.eazybi] (60563.0ms) {:account_id=>1, :user_id=>160, :cube=>“Issues”, :cube_table=>“jira_issues_measures”}
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] WITH SET VisibleColumns AS ‘{[Measures].[Issues resolved]}’
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] SET VisibleRows AS ‘FILTER(CROSSJOIN(CROSSJOIN({[Assignee].[Maxime Menault]}, {[Project.Category].[ServiceDesk by Category]}), Generate(NonEmptyCrossJoin([Assignee].[Maxime Menault] * CascadingChildrenSet([Project.Category].[ServiceDesk by Category]), [Issue].[Issue].Members), [Issue].CurrentMember)), ( ([Measures].[Issues resolved]) <> 0 ))’
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] MEMBER [Time].[AggregatePageMembers] AS ‘Aggregate({CascadingChildrenSet([Time].[Last 12 months])})’, $caption = ‘Last 12 months’
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] SELECT VisibleColumns ON COLUMNS,
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] NON EMPTY VisibleRows ON ROWS
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] FROM [Issues]
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] WHERE ([Time].[AggregatePageMembers])
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] [Mondrian::OLAP::Error] org.olap4j.OlapException: Mondrian Error:Query timeout of 60 seconds reached / Query timeout of 60 seconds reached
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.olap.QueryTimeoutException: Mondrian Error:Query timeout of 60 seconds reached
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.resource.MondrianResource$_Def13.ex(MondrianResource.java:1229)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.server.Execution.checkCancelOrTimeout(Execution.java:206)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.rolap.RolapResultShepherd.shepherdExecution(RolapResultShepherd.java:171)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.rolap.RolapConnection.execute(RolapConnection.java:599)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.olap4j.MondrianOlap4jCellSet.execute(MondrianOlap4jCellSet.java:88)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.olap4j.MondrianOlap4jStatement.executeOlapQueryInternal(MondrianOlap4jStatement.java:415)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: mondrian.olap4j.MondrianOlap4jPreparedStatement.executeQuery(MondrianOlap4jPreparedStatement.java:72)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: jdk.internal.reflect.GeneratedMethodAccessor5049.invoke(Unknown Source)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from root cause: java.base/java.lang.reflect.Method.invoke(Method.java:566)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from mondrian.olap4j.MondrianOlap4jConnection$Helper.createException(mondrian/olap4j/MondrianOlap4jConnection.java:859)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from mondrian.olap4j.MondrianOlap4jStatement.executeOlapQueryInternal(mondrian/olap4j/MondrianOlap4jStatement.java:420)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from mondrian.olap4j.MondrianOlap4jPreparedStatement.executeQuery(mondrian/olap4j/MondrianOlap4jPreparedStatement.java:72)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from jdk.internal.reflect.GeneratedMethodAccessor5049.invoke(jdk/internal/reflect/GeneratedMethodAccessor5049)
2024-07-25 10:11:03 +0200 ERROR: [B8500FCF:df6dd463] from jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)

Thanks a lot for your help!
Stefan

2

Hello @Stefan_Stadler,
Welcome to the eazyBI Community! :wave:

To help resolve your report timeout issue, could you please export and share your eazyBI report definition: Export and import report definitions ?
You can share it directly with support@eazybi.com

Kindly,
Gerda // support@eazybi.com

3

Hi all,
just to summarize and to potentially provide with a workaround for others:
We have managed to improve the performance of our drill trough issue by factor 10 by just adding the issue dimension to the rows of the report.
We have not been able to explain this fully and we are using a relatively old version without the option to update. However, this has already helped to get rid of all the timeouts.
Thanks again to the wonderful support of eazyBI!
Stefan