Work With Query Limits

This topic provides information to help you understand and set up semantic model query limits.

Topics:

• Limit the Number of Rows in a Database Query

• Limit Database Queries by Maximum Run Time

• Allow or Disallow Direct Database Requests

• Override an Application Role's Query Limits

• Pause an Application Role's Query Limits

Limit the Number of Rows in a Database Query

You can control runaway queries for an application role assigned to a physical database by limiting queries to a specific number of rows.

Any query limits you set should exceed the Presentation Server settings for Maximum Number of Rows Processed when Rendering a Table View and Maximum Number of Rows to Download by at least 500 to avoid error messages. When you specify a max row query limit, then those users assigned to the application role may receive Max Row Limit Exceeded messages.

You can override the row limit that you set for an application role. See Override an Application Role's Query Limits.

The options for row limit are:

• Enable - Limits the number of rows to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated.

• Disable - Disables any limits set in the Max Rows field.

• Warn - Logs queries that exceed the set limit in the Query log. This option doesn't enforce limits.

• Inherit - Inherits limits from the parent application role. If there is no row limit to inherit, no limit is enforced.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, locate and double-click the database that you want to assign query limits to.
5. Click the Query Limits tab.
6. Locate the role name that you want to limit, double-click its Max rows field, and enter the maximum number of rows that members of the application role can retrieve from the source database object.
7. Double-click the Row Limit field and select a row limit.
8. Click Save.

Limit Database Queries by Maximum Run Time

You can specify the maximum time a query can run on a physical database for a particular application role.

You can override the time queries that you set for an application role. See Override an Application Role's Query Limits.

The options for time limit are:

• Enable - This limits the time to the value specified.

• Disable - Disables any limits set in the Max Time field.

• Warn - Logs queries that exceed the set time limit in the Query log. This option doesn't enforce the time limits.

• Inherit - Inherits limits from the parent application role. If there is no time limit to inherit, no limit is enforced.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, locate and double-click the database that you want to assign query limits to.
5. Click the Query Limits tab.
6. Locate the role name that you want to limit, double-click its Max time (minutes) field, and enter the maximum number of minutes rows that want queries to run on each database object.
7. Double-click the Time Limit field and select a time limit.
8. Click Save.

Allow or Disallow Direct Database Requests

You can specify if you want an application role to be able to run direct database requests.

What you specify in the Query Limits Execute Direct Database Requests field overrides what you selected in the Allow direct database requests by default field in the physical database's Advanced tab.

The options for the Execute Direct Database Requests field are:

• Allow - Grants the ability to run direct database requests for this database.

• Disallow - Denies the ability to run direct database requests for this database.

• Inherit - Inherits limits from the parent application role. If there is no limit to inherit, then direct database requests are allowed or disallowed based on the Allow direct database requests by default property for the database.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, locate and double-click the database that you want to assign query limits to.
5. Click the Query Limits tab.
6. Locate the role name that you want to specify direct database requests for, go to the Execute Direct Database Requests field, click it, and select an option.
7. Click Save.

Override an Application Role's Query Limits

You can specify when and how you want to override a database's query limits for a specific application role.

For each application role in a physical database, you can choose to override the row and time limits and the run direct database requests setting. You can also specify how many seconds to limit the application role's logical queries to.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, locate and double-click the database that you want to override assign query limits for.
5. Click the Query Limits tab.
6. Locate the click the role name with the query limits that you want to override.
7. Click Detail View.
8. In the day and time grid, click to select one or more day and time that you want the override to occur.
9. In the Row Limit, Time Limit, Execute Direct Database Requests, and Limit logical queries to fields, specify how to override the application role's query limit settings.
10. Click Available.
11. Click Save.

Pause an Application Role's Query Limits

You can specify when you want to pause a database's query limits for a specific application role.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Physical Layer.
4. In the Physical Layer pane, locate and double-click the database that you want to override assign query limits for.
5. Click the Query Limits tab.
6. Locate the click the role name with the query limits that you want to pause.
7. Click Detail View.
8. In the day and time grid, click to select one or more week days and times that you want to pause the query limits.
9. Click Unavailable.
10. Click Save.