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

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

User_5OKPE

Jan 28, 2025

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!

---

View Post

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

Bhaskar Konar

Jan 28, 2025

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.

XML.txt

Uploaded Jan 28, 20258.95 KB

Thank you!

Bhaskar Konar

User_5OKPE

Jan 28, 2025

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

https://us.v-cdn.net/6037859/uploads/DSULJ0PQTXPE/xml.txt

Thank you!

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

Uploaded Jan 28, 20254.31 KB

Cheers,

User_5OKPE

Bhaskar Konar

Jan 28, 2025

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!

https://us.v-cdn.net/6037859/uploads/SK0VLY0QNKA8/image.png

https://us.v-cdn.net/6037859/uploads/AEZHRIWLG6C2/session-query.txt

Cheers,

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

User_5OKPE

Jan 29, 2025

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?

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

Fusion Applications: How to Get nqquery Using Manage Session

Hope this help.

Cheers,

Bhaskar Konar

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

User_5OKPE

Jan 28, 2025

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!

---

View Post

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!