I'm trying to run a simple analysis that should yield approximately 700K records. It doesn't make sense to me why it keeps timing out. Does anyone have any tips I can use to overcome this issue??
Hi,
Post you sql to the forum might help us help you! Go to the advanced tab to get the logical sql for your analysis.
By default all analysis in OTBI are limited to 75k rows - so that is your first problem. Too many rows.
To debug yourself try to imagine the data model in your head. Build it up step by step as a "star". Your query must include a measure from a fact as the heart of the star (if you do not select one the system will pick the default one for you at runtime). Then attributes from one or more dimensions as the points of the star . If you have used more than one logical/physical fact table then repeat this process for each fact. Start with just the measures from the fact. You get 1 row with a big number. Then add the unique identifier from a dimension. Still ok? If you want an aggregate in your final query now replace the unique identifier with the grain as unique identifier of the level in the hierarchy you want to aggregate at with that dimension. Still ok? Now repeat for each dimension. Still ok? Also play around with different filters turning them on and off dimension by dimension. Still ok? Basic idea is start with something "good" then keep adding bits until it goes "bad".
You can also look "under the hood". Use page issuerawsql to get the the physical sqls from your manage session logs to see if the metadata repository database has generated any bad performance WITH. Cut paste the physical sql into an adhoc otbi report data model then comment bits in and out to see which bit is causing the problem. Actually sometimes the start and end times at the bottom of the session log shows you that the physical sql was fast but then the presentation layer spent lots of time after that on the middleware application server doing stuff after it had fetched the data from the application database tables and views.
Hi Nathan, Thanks for the reply. I could have sworn I've pulled reports that yield results greater than 75K. I have played around with filters and removed some unnecessary columns. And now I the error referring to too many records.
I know I could limit the number of records if I could just get the And statement in the filter to work like I need it to. But no one has replied as to how to get this work or if it is even possible.
I need to pull a report based on sales rep assignments. We many team members on an account. I need it pull based on the function. For example, Territory function = CE and Territory Function = AM. So i add the filter twice for each function and get the following error:
No Results The specified criteria didn't result in any data. This is often caused by applying filters and/or selection steps that are too restrictive or that contain incorrect values. Please check your analysis filters and selection steps, and try again. The filters or selection steps currently being applied are shown below.
Filters
Account Active Flag is equal to / is in Y
and
Customer Segment is equal to Strategic, Enterprise
Corporate Account Name is not equal to / is not in GP - UPS
TP Category is equal to / is in SET-CE
TP Category is equal to / is in SET-AM
Would you happen to have any advice on how to pull this?
Hi, Yes I think no data found is your expected result. It is not an error message. It is the successfull result to the query you asked it to do/ You have basically done a where true=false so you will always get no rows.
You have added 2 filters saying
where "category" = 'A' and "category" = 'B'
so for any 1 row category cannot be both A and B at the same time (that is just "math").
Change you filter either
1) by toggle the and/or to
where "category" =/in 'A' OR "category" =/in 'B'
2) or delete one and edit the other to have one clause with multiple select values
where category =/in 'A;B'
Thanks again.. There is where the issue lies. If I use the or statement there are too many records. BTW, I checked and out the SQL data and we should be able to pull 500K. So now i don't under stand why the report won't do it!
Thanks again!
FETCH FIRST 500001 ROWS ONLY
Description: Join the OCI Enterprise AI Product Management team for a deep dive into the latest product roadmap updates and upcoming innovations. During this webinar, we’ll cover key roadmap highlights, showcase product demos, and share insights into what’s ahead for OCI Enterprise AI. The session will conclude with a live…
We have a Talent Community Pool configured with Organization and Job Family criteria. Some candidates were added to the pool, but we are unable to determine which specific criterion they matched. Is there a report, analysis subject area, audit history, or backend table that shows the attribute values that resulted in a…
Hi, We are looking for PVOs for below tables in BICC Can you guide us on the approach to extract data for these? PON_AGREEMENT_LINES_ALL PON_AGREEMENTS_ALL PON_APPROVAL_HISTORY PON_CONTRACT_PARTIES PON_PRICING_TERMS PON_TERMS_AND_CLAUSES Thanks, Nitin
Hi All, We are using Oracle Fusion Cloud Procurement and would like to secure the Purchase Order (PO) PDF that is generated from Oracle Fusion. Our requirement is to have the generated PO PDF password-protected, so that the recipient must enter a password before the PDF can be opened. Can someone please advise? We are…
Does Fusion release supports propagating an HTTP XML Feed authentication exception into the BIP-based ESS job’s completion text.
