OBIEE - How to limit the number of records fetch

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

There are 2 standard ways for an administrator to limit the number of rows returned :

3 - Repository Level

3.1 - Max Rows

At the rpd level, you can Manage Query Execution Privileges to limit queries by number of rows received by a user or group with the max rows parameter. It determines the number of rows that can be returned from the database. If the number of rows exceeds this limit, an ODBC error occurs.

From the Administration Tool menu bar, choose Manage > Security.

  • In the Security Manager dialog box, in the tree pane, select Users or Groups.
  • In the right pane, right-click the name that you want to change and select Properties.
  • In the User or Group dialog box, click the Permissions tab.
  • In the User/Group Permissions dialog box, click the Query Limits tab and expand the dialog box to view all columns.
  • To specify or change the maximum number of rows each query can retrieve from a database, in the Query Limits tab, perform the following steps:
  • In the Max Rows column, type the maximum number of rows.
  • In the Status Max Rows field, select a status.
  • Click OK twice to return to the Security Manager dialog box.

If a query exceeds the limit set by the Administrator, the end user will get the message:

[nQSError: 60008] The query for user 'user1'
exceeded the maximum query governing rows n from the database x
Advertising

3.2 - Status Max Rows or Status Max Time Description

Status Description
Disable When selected, disables any limits set in the Max Rows or Max Time fields
Enable This limits the number of rows or time to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated.
Ignore Limits will be inherited from the parent group. If there is no row limit to inherit, no limit is enforced

3.3 - Max Time

Max Time (minutes) will not count the fetch time from database. It only counts the query execute time. For example: If your query is running for 45 seconds on database but the result fetch is taking 2 minutes, the query Max time limit won't cancel the query because your limit is 1 minute. This is because the query execution time is still less than the time limit.

4 - Presentation Server level

You can also limit the number of rows at the Presentation Server level.

You can override the maximum number of rows that can appear in a view by modifying the Oracle BI Presentation Services configuration file (instanceconfig.xml) with the following entries.

In instanceconfig.xml, the node below limit the number of rows returned.

<ResultRowLimit>100</ResultRowLimit>

To set :

  • DefaultRowsDisplayed,
  • DefaultRowsDisplayedInDelivery,
  • or DefaultRowsDisplayedInDownload

to a value that exceeds the current value of ResultRowLimit, you also must increase the value of ResultRowLimit to equal or exceed the value that you want to set.

See the table Common Elements for Manually Changing the Display of Data in Views for a description of each parameters.

To display a message, set up the "No result" view in your report.

For the end user to limit the result set they would have to set a filter in the query.

Advertising

5 - Documentation / Reference