Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Error running OTBI report - The user request exceeded the maximum query governing execution time.

Received Response
21
Views
5
Comments
Abinaya P
Abinaya P Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

There is an OTBI report which gives results when I run using my ID but the same report is giving error - "The user request exceeded the maximum query governing execution time" when we try to run via another user ID.

We have also added all the respective subject area duty roles.

Please suggest why are we receiving this error.

Answers

  • Shankar-Oracle
    Shankar-Oracle Rank 4 - Community Specialist

    Do you have OTBI admin role assigned to your username?

    What about the other user?

  • Arti Gupta-Oracle
    Arti Gupta-Oracle Rank 1 - Community Starter

    I have also faced this issue multiple times, when I run a particular report it gives results quickly(I have admin role) while other users who are not admin, but having required access to fetch the data are facing this error. Could you please help in understanding and resolving the issue.

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics & AI Strategist
    https://community.oracle.com/products/oracleanalytics/discussion/comment/73565#Comment_73565

    Hi @Arti Gupta-Oracle,

    Welcome to the Oracle Analytics Community!

    If a report is working fine for Admin User but taking long amount of time for non-admin user, then the security filters that are getting applied for non-admin user is creating performance issue.

    You need to generate both queries (Admin & Non-Admin) and compare to see the differences. After analyzing the query, need to see if adding any particular filter or an index column make situation better.

    Hope this help.

    Thank you.

  • Aarthi Natarajan-Oracle
    Aarthi Natarajan-Oracle Rank 3 - Community Apprentice

    Hi Team,

    I am no expert but based on my experience, i have faced this issue when i have added lot of columns in criteria and then when i try to view the graph, it gives me this error. So, when i remove unwanted additional column save and then click on results, it doesnt give this error.

    But @Abinaya, your case is bit different where you are saying you are not facing this issue but another user is, then please compare the roles between you both and try to add the missing roles and see if that might be helpful?

  • Rich Merkel
    Rich Merkel Rank 6 - Analytics & AI Lead

    What subject area are you using?

    Admin users will have query run much more quickly, as the security layer is not applied.

    When users have role to access, there is additional overhead to check security layer. For example I have admin role, user has project accountant role. Query will not complete runs over 10 minutes, but admin user runs in two minutes. This is for good reason as security is very important for non-admin users.

    I have found that you need to optimize you query further. For example project costs - actual cost real time, if using a date/accounting period filter, you must use accounting date under folders Expenditure Item and Cross Distribution Details → Expenditure Item and Cross Distribution Record Information → Accounting date as compared to accounting date in fiscal year folder.

    In some cases I created Oracle SR to help me optimize by providing nqquery logs.

    When some analysis exceeds maximum time (around 9-10 minutes), for non-admin user you will need to optimize the analysis.