Behaviour of Web Intelligence when Universe imposed limits are reached

[Former Member]

I am trying to understand exactly what is meant by the following extracts from the Universe Designer guide. Any calrification much appreciated.

"Limit size of result set to a specified value

The number of rows that are returned in a query are limited to the number that you specify. This limits the number of rows returned to Web Intelligence, but does not restrict the RDBMS from processing all rows in the query. It only limits the number once the RDBMS has started to send rows."

Q1This seems to imply that the database returns the result set in batches, and that as soon as Web I has received sufficient batches to exceed the row limit, it won't accept any more?

Q2 Presumably this will terminate the database processing for that query?

Q3 What happens to the data that has been returned. Is the user notified that the result set is imcomplete?

"Limit execution time to a specified value

Query execution time is limited to the number of minutes that you specify. This limits the time that data is sent to Web Intelligence, but does not stop the process on the database."

Q1 So if limit is set to 2 mins, and query takes 3 mins to return very few rows, then query cannot be interrupted?

Q2 But if database is returning a lot of rows (in batches) then as soon as Web I has received sufficient batches to exceed the time limit, it won't accept any more?

Q2 Presumably this will terminate the database processing for that query?

Q3 What happens to the data that has been returned. Is the user notified that the result set is imcomplete?

[Former Member · Accepted solution]

Steve,

Here are some comments to your questions:

"Limit size of result set to a specified value

Q1This seems to imply that the database returns the result set in batches, and that as soon as Web I has received sufficient batches to exceed the row limit, it won't accept any more?

A1. Correct

Q2 Presumably this will terminate the database processing for that query?

A2. Not necessarily so. The query achieves normal execution and termination and dumps its data to temporary storage on the BO server, however, once WebI has received sufficient batches (rows), then no more data flows to the client.

Q3 What happens to the data that has been returned. Is the user notified that the result set is imcomplete?

A3. The user is notified of an incomplete set by way of a yellow "yield" sign that appears in the lower right portion of the browser along with the verbiage "Partial results".

"Limit execution time to a specified value

Q1 So if limit is set to 2 mins, and query takes 3 mins to return very few rows, then query cannot be interrupted?

A. The query on the RDBMS continues to run even when the BO server determines that the 2 mins are up. The query can be interrupted but only by a DBA using RDBMS server tools to end off the running (runaway) query, the BO server does not reach into the RDBMS to stop the query....

Q2 But if database is returning a lot of rows (in batches) then as soon as Web I has received sufficient batches to exceed the time limit, it won't accept any more?

A2. Not so. If the query takes one minute and returns 100,000 rows, but WebI limits the return to 50,000 rows, the query has completed, data is sent to temp storage, and the BO server controls the flow to the client (RDBMS activity is over with and there is no more timeout criteria on the part of the RDBMS and BO server interaction). If the query runs for 3 mins. and BO server timeout is 2 mins, then no data is brought to the client (most RDBMS don't provide partial results, they package up the entire data set once the query finishes and then hands the data over to the server...), and the client is notified that the query has taken too long (exceeded 2 mins) and no data is brought to the client.

Q2 Presumably this will terminate the database processing for that query?

A2. No, once the BO server reaches its time limit, it informs the client of the timeout and ignores the RDBMS. The RDBMS finishes its run (unless the query is manually killed by the DBA), and the RDBMS output goes to the bit bucket.

Q3 What happens to the data that has been returned. Is the user notified that the result set is imcomplete?

A3. On a BO Server timeout situation, no data comes to the client. On a RDBMS successful run which finishes under the timeout limit then data is brought to the client (by way of the BO server). However, if the BO server has limited the number of rows to process, then partial results are brought to the client, along with the yellow "yield" sign...

Thanks,

John

[Former Member · Accepted solution]

Dear John,

I have a question with regarding to the imposed limit.

One of our application is using Crystal Report as the designer, and BO Universe as the data source.

If the limit of the Universe is set to 5000 rows, and the actual no. of records from the DB is larger than that, will there be any kind of notification that I can put into the report to notify the report user, just like the yellow symbol of WEBI?

Thanks

Clarence

[PPaolo · Accepted solution]

Hello,

Crystal Reports does not support the following query governors:

Limit size of result set to

Limit execution time

Limit size of column of long text objects

Warn if cost estimate exceed

In this document you'll find detailed information about the support of Universes by Crystal Reports:

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20984c2b-17f1-2b10-1091-d18977f7...

Hope that it helps

PPaolo