Scheduled OTBI Reports Missing Records

Question

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?

Nathan CCC · Answer

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.

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)

ChristineT. · Answer

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

ChristineT. · Answer

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