Subledger OTBI Analysis - Report ran for one transaction number spinning until time-out

User_5OKPE

The issue with this report is with applying a filter to this field: "Subledger Accounting - Journals Real Time"."Journal Details"."Transaction Number". This is not a new report and we've just started having issues with that field post 24D. I've tested it under 3 scenarios in the edit screens/running the report with the same results:

1. Filter for a single transaction # in the Transaction Number field (a Payables transaction for this example), and it just spins until I get auto-logged out of Oracle.
2. Filter on the Payables Invoice Number field for the same transaction number, and it completes in about 15 seconds, including populating the Transaction Number field in #1.
3. Create a new analysis with this subject area, then only add the Journal Details.Transaction Number field and filter for the same transaction #, which takes ~45 second to populate that single cell.

Anyone have suggestions on what the issue is, why it popped up now, and how to workaround it?

Thank you!

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24438/subledger-otbi-analysis-report-ran-for-one-transaction-number-spinning-until-time-out

It seems the report is using complete different path / not using indexes etc when it is being filtered on 'Transaction Number' field.

Can you please attach the report xml / catalog so that we can try to replicate the issue in our instances and see if any resolution possible or not.

User_5OKPE

https://community.oracle.com/products/oracleanalytics/discussion/comment/63261#Comment_63261

XML.txt

Thank you!

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/63263#Comment_63263

Hi @User_5OKPE,

Thanks for the details.

I tried in my instances (which is NOT post 24D) and it is returning results within seconds. PFB screenshots.

I'll get access to post 24D env around this weekend and shall try again to see how it is behaving in that env.

I've collected the session query, PFA.

Can you please track the session query in your post 24D env and then compare to see if the query is getting generated differently.

Thank you!

Session Query.txt

Cheers,

User_5OKPE

https://community.oracle.com/products/oracleanalytics/discussion/comment/63265#Comment_63265

Thank you for your analysis. Where do I find the session query, or is it an analysis using the OTBI performance subject area?

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/63293#Comment_63293

Please take a look into the following Oracle Note, it has the steps & details.

https://support.oracle.com/knowledge/Oracle%20Fusion%20Applications/2225909_1.html

Hope this help.

Cheers,

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24438/subledger-otbi-analysis-report-ran-for-one-transaction-number-spinning-until-time-out

Hi @User_5OKPE,

I'm able to replicate the issue partially in Post 24D environment. Still it is working fine with filter on 'Transaction Number' compare to having no filter on 'Transaction Number'

Now the report with out filter is running more than 20 minutes.

But when I queried the 'Transaction Number' it came within 1 min 51 Sec.

When I add a filter to the 'Transaction Number' it also came quickly around 16 Sec.

Bhaskar Konar

Hi @User_5OKPE,

Are you still facing the same issue or managed to resolve or had some work around?

Please do share.

Thank You!