Row and time restrictions have been part of the BusinessObjects analytic arsenal since the original product was introduced in the early 1990s. IT departments depend on these controls as a safeguard against runaway queries that users might inadvertently set without proper limits. By restricting the number of rows returned or the time allowed to process the queries, these controls prevent any one user from monopolizing the organization’s BusinessObjects environment and the databases that can be queried. It is important that users and developers know how these restrictions work, especially in those situations that require multiple SELECT statements per query.

Knowing how BusinessObjects processes universe-based read-only (SELECT) statements is enlightening and unnerving at the same time. It’s enlightening because the knowledge can help anyone choose better values for these restrictions. It’s unnerving for those who realize that their reports may not have all the data that their audience expects. I examine cases derived from the sample data and universes distributed with BusinessObjects. A novice can re-create the scenarios presented.

I focus on the Universe Designer to explain the concepts. The Designer is available in BI 4.x and earlier versions of BusinessObjects. The rules presented, however, also apply to universes created using the Information Design Tool (IDT).

A Short Refresher on Restrictions

Many restrictions can be created as part of a universe. Of these, restrictions that control the time allowed for query processing and the number of rows retrieved are especially important. If queries are allowed to run with no cap on duration or result set size, one query can monopolize all resources on the database and prevent other users from running their submissions. These limits can be set as default values or added as universe restrictions. The latter acts like an override on the former and is perfect for groups that need more resources to perform their duties than others.

For example, the Finance group may need a higher row limit because its duties entail reconciling detailed statements line by line. To set a default limit of 10 minutes per query and 1,000 rows returned for the Islands Resorts Marketing universe distributed with the BusinessObjects software,  select File > Parameters ... from the main menu of the Universe Designer (not shown) and click the Controls tab (Figure 1). Unchecking the boxes next to each limit removes that limit, effectively making that value unlimited.

Figure 1

Default Universe Parameters

Figure 2 shows how you can change the number of rows from the default (1000) to a single row by checking the Limit size of result set to box and entering the value in the rows box. Notice how the software highlights the new value in red, alerting the designer to exactly which value has been changed.

Figure 2

Access restriction for overriding rows retrieved

After you create an access restriction, you need to associate it with at least one user group or user to be effective. Keeping track of rights for individual users is difficult, especially when searching for access rights buried within the universes the users use. To simplify, use the Add user or group link to find the desired groups or users (near the bottom right of Figure 3.) This link can be accessed using Tools > Manage security ... > Manage access restrictions. The best practice is to associate the groups and then add or remove users from that group using other tools such as the Central Management Console (BusinessObjects main administration tool). Figure 3 shows how a Row Restriction is associated with the Query Limited group. Association is simple. Select the access restriction from the left, select the groups from the right, and click the >>Apply button.

Figure 3

Apply an access restriction to a group

Restricting Simple Queries

Let's begin by understanding how BusinessObjects applies these time and row restrictions to a simple informational query. Simple queries require only one SELECT to process the request. For example, consider the following query from the Island Resorts Marketing universe (Figure 4). To show the SQL associated with the query, select the SQL tab on the Query toolbar.

Figure 4

A simple query (one SELECT)

In this case, the query is allowed to run for up to 10 minutes. This process includes submitting the SQL program to the database, database processing, and time to retrieve the resulting rows. The number of rows retrieved is 1,000 for everyone — except users who belong to the Query Restricted group. Those users are assigned only a single row.

Introducing Queries Using Multiple SELECT Statements

In many cases, queries created using multiple measures (also known as aggregation objects) or those that involve two or more contexts require more than one SELECT statement. To locate the setting within Universe Designer that controls this universe behavior select File > Parameters ... from the main menu and click the SQL tab. Figure 5 shows the settings that allow you to create multiple SELECT statements from a single query:

• Multiple SQL statements for each context (one SELECT statement per measure object from a different table)
• Multiple SQL statements for each measure (one SELECT statement per context used in the query)

Figure 5

Universe controls that allow multiple SELECT statements

With these Multiple Path controls set as shown in Figure 5, queries generate more than one SELECT statement. For example, to retrieve the number of current and future guests per country from the Islands Resorts universe, create a new document using the query shown in Figure 6. Choose the Island Resorts Marketing universe and add the Country, Number of guests, and Future guests objects to the result panel of the query. Note how BusinessObjects creates multiple statements based on the need for two contexts: Select 1 (Sales context) and Select 2 (Reservations context). Each SELECT is sent sequentially to the database and processed. Rows must be returned from the current statement before the next statement can be processed.

Figure 6

Multi-SELECT example

Once the query is created, view the generated SQL by selecting the View Script button in BI 4.x or View SQL button in BusinessObjects 3.1. The way that time and row restrictions are applied in multi-SELECT situations varies drastically from my previous simple example. I now discuss the effects of row and time restrictions on multi-SELECT queries. (Such queries are often used in corporate environments, and later I address an important point of Alerting that relates to such queries.) Each effect is shown as a case study that can be easily replicated using the sample data and universes provided with BusinessObjects.

Case 1: Getting More Rows than Expected

The proof for this case is fairly simple. Run the following queries with no row restrictions. Figure 7 shows the results.

• Query 1: Number of past guests per country
• Query 2: Number of future guests per country

Figure 7

Single queries without row restrictions

Next, add a universe restriction limiting the number of rows to 1. This can be either a default or access restriction. Create the following combined query (make sure that you export the universe after making the restriction change and create a new query on the revised Islands Resorts universe):

• Query 3: Number of past and future guests per country

Query 3 generates two queries based on the Sales context (former guests) and Reservations context (future guests). If the rows are restricted at a query level, only one row is returned. This row is the past guests for the first country that is retrieved — France. As shown in Figure 8, however, a row is returned per SELECT statement — one row for French former guests and another for French current guests. Note that BusinessObjects placed the results on one table row. The database returns two different rows.

Figure 8

Combined query showing more resultant rows than expected

Case 2: Out of Time Sooner than Expected

Because the row restriction is applied per SELECT, one expects time restrictions to work the same way — a statement at a time. However, time restrictions live by the following rules:

• The time restriction is enforced BY QUERY
• The time allowed is evenly distributed between SELECTs in that query

Although the first behavior is what most developers expect, the second can be surprising and leads to queries being terminated — especially in multi-SELECT situations.

The eFashion sample universe is used to illustrate this case because the universe is based on a data warehouse that has tens of thousands of rows that take more than a second to process. It is important to note that modern computers can process a sample database of tables as large as 100k in a few seconds — yet time restrictions are measured in minutes, which may not allow sufficient time for processing of each specific SELECT to occur.

Create the following query using eFashion. Create a new document, choose the eFashion universe, then select the SKU number, Sales revenue, and Promotion Cost USD objects as the results to be returned.  Figure 9 shows both SELECT statements that you create for this query Choose the View SQL (3.1) or View Script (4.x) buttons to view the statements yourself.

Figure 9

Multi-SELECT statement for unbalanced queries (more versus fewer rows)

The first SELECT is based on the Shop Facts table, which has almost 90,000 rows. The second SELECT is based on the Promotions table of 10 rows. A time restriction is split evenly between these two queries, even though the first query takes longer than a second. For example, a one-minute time restriction allots 30 seconds to each query for processing. In an actual situation, this time distribution may force the first query to retrieve less data than expected, generating a report that does not contain all possible data.

Case 3: Bad Alerting

Report users depend on BusinessObjects to tell them when their reports do not contain all possible data. They look to the bottom right corner of the report screen for a Partial Results indicator (Figure 10) that warns them that something happened that reduced the number of possible rows, usually because of a time restriction or because the report exceeded the maximum values of a row. However, what happens when the queries bring back less data than expected and the user is not warned? Users may make business decisions based on faulty or incomplete information.

Figure 10

The Partial Results indicator

These scenarios occur when you use prior versions of BusinessObjects. If you use a version prior to XI 3.1 Service Pack 6.1, you may be experiencing this problem today without knowing it. It is limited to multi-SELECT queries, but as I explained before, many corporate queries fall under this category. The specific problem is limited to row restrictions only. The Partial Results is triggered based on the last SELECT processed. Earlier SELECTs can have fewer rows retrieved, and the indicator never alerts the user.

Let's use Islands Resorts to illustrate. You don’t set a row restriction for the next three queries.

Query 1 (Figure 11): Number of guests per customer where service line is Accommodation.
Rows returned: 18

Figure 11

Guests per customer (no row restriction)

Query 2 (Figure 12): Number of future guests per customer where service line is Accommodation.
Rows returned: 21

Figure 12

Future guests per customer (no row restriction)

Query 3 (Figure 13): Number of guests and future guests per customer where service line is Accommodation.
Rows returned: 39

Figure 13

Past and future guests per customer (no row restriction)

The individual query row counts add up to the combined count. Add a row restriction to the Islands Resorts universe (either as a default or an access restriction) to limit the number of rows to 20.

Create a new Web Intelligence document with the following query:

Query 1 (Figure 14): Number of future guests and guests where service line is Accommodation.
Rows returned: 38

Figure 14

Past and future guests per customer (row restriction=20)

The Future Guests SELECT that normally returns 21 rows is truncated to 20. Number of Guests returned 18 rows, so this measure passes the restricted number rows. The big problem: The Partial Results indicator was not activated because it is triggered based on the last SELECT in a multi-SELECT statement.

The order of the measures can be rearranged in the query to test this statement. The following query does trigger the Partial Results indicator because the last SELECT based on Future guests is over the row restriction of 20.

Figure 15

Past and future guests per customer (measures reversed)

This problem existed in previous versions of BusinessObjects up through XI 3.1 Service Pack 5. It was fixed in a later pack. Service Pack 5 Fix Pack 4 (5.4) worked correctly and so did future versions through Service Pack 6 Fix Pack 1. If you are running one of the earlier versions, however, be warned. Your users may be using reports that have fewer rows than they expect because the alerter never appears to warn them.

Alan Mayer

Alan Mayer is president at Solid Ground Technologies, Inc. He has built customer-focused, BusinessObjects-based solutions for the last 20 years. His original company, Integra Solutions, was one of the first BusinessObjects partners that joined the program in 1995. His company provided the first authorized set of training manuals that were later purchased by the software vendor for nationwide distribution. Solutions from his firm have been adopted by a wide variety of industries, from healthcare to banking, manufacturing, and retail.

You may contact the author at alan.mayer@solidgrounded.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.