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

Scheduled OTBI Reports Missing Records

Received Response
47
Views
4
Comments
ChristineT.
ChristineT. Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

We have a couple of OTBI Scheduled jobs that run monthly. We're not sure why, but when two of the scheduled agents run they are missing records. Example: One of the scheduled jobs produced 304 records. When we ran the report manually it returned 623 records.

Has anyone else encountered this issue? Do you have any suggestions on how to fix the issue?

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited May 8, 2024 7:41AM

    Hi,

    Assume when you say ran the "report" manually you actually mean that you "ran the analysis manually" because this is an analysis in OTBI not a report in OTBI. So we need to debug 2 scenario; 1 analysis executed online in browser and 1 analysis scheduled as an intelligence robot "agent".

    Was it exactly the same query SQL? With exactly the same parameters? Executed as exactly the same user with the same roles and data access setup? Remember that when you create an agent you have the option for it to run as the user who created the agent at design time or as the user who executes it at runtime. Who is the agent running as?

    To find out then you need to debug by reading your session logs.

    Go to page /analytics/saw.dll?Sessions then View Log. If the difference is the user then debug the same query as each user using page /analytics/saw.dll?IssueRawSQL then View Log. To use these pages you must have demanded from your bi administrator to use page /analytics/saw.dll?PrivilegeAdmin to grant user or a role user has to privileges Issue SQL Directly and Manage Sessions.

    Use your favourite text compare tool to compare the 2 logs. What was the difference? Was it that your users have different security context setup on task "manage data access for users" so the metadata repository database added a different where clause onto the physical sql(s) it sent to the application database to answer the query.

  • ChristineT.
    ChristineT. Rank 1 - Community Starter

    Hi @Nathan CCC ,

    Sorry I'm not sure what happened to my first reply so I'll repost. Thank you for your help and tips!

    Yes, that's correct, I ran the analysis manually with my ID and then exported the data to Excel. The agent is saved under my ID and it's also running with my ID. The parameters are filters in the report so it's confusing as to why running the analysis manually and exporting and having the agent run with my ID would produce different results.

    Thank you for sending the steps for troubleshooting!

    Question….when I run the analysis manually, there are several pages of data. Does something need to be tweaked on the agent job so that it will export all pages of data? Could that be why some data is getting dropped?

    Thanks,
    Christine

  • ChristineT.
    ChristineT. Rank 1 - Community Starter

    Hi @Nathan CCC,

    Thank you very much for your reply and help! Yes, that is correct, I ran the analysis manually and then I exported the data to Excel. The parameters are filters that are saved to the analysis so I'm not understanding why the output would be different if we run an agent vs we run the analysis manually and export the data.

    The agent is running under my ID, same ID that is used to manually run the analysis.

    Thank you for sending steps on how to troubleshoot this issue!

    Question….when I run the analysis manually with my ID, there are a few pages worth of data. Do we need to do something to the agent so it will export all pages of data? Could that be why some records are missing?

    Thanks,

    Christine

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited May 21, 2024 7:31AM

    Hi,

    Yes if the analysis is not prompted but has hard coded values in filters then assume the logical SQL is probably the same in both cases. So the only difference must be the user who runs it. You can check in the session logs to validate that the logical sql passed to the metadata repository database was the same but that it generated different where clauses in the phyiscal sql(s) sent to the application database? (generally WITH statements in he log) which returned more or less rows (the row counts are at the bottom of the log)?

    So an important setting on the agent is whether the agent instance "Run as" you the "recipient" user at runtime or always the same user as "specified user" setup at design time? When you run it manually then it is your signed in user at runtime obviously.

    image.png

    If you are using the default view "table" then the number of rows per page is set here so the number of "pages" you get is up to you up to maximum setup by in system by default 500 (no pages)

    image.png