Time Out Issue

Donna Fazio

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??

Nathan CCC

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.

Donna Fazio

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

and

Corporate Account Name is not equal to / is not in GP - UPS

and

TP Category is equal to / is in SET-CE

and

TP Category is equal to / is in SET-AM

Would you happen to have any advice on how to pull this?

Nathan CCC

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' 

Donna Fazio

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