Specifying Sets of Data

---

To understand how the AND, OR, UNION, INTERSECT, and MINUS operators work, it’s useful to review a bit of set theory. Figure 22-5 shows a Venn diagram with three sets of criteria: Country, Color, and Vintage. When you enter conditions in the Query Panel, the conditions are joined by a default operator, AND. All conditions must be met for the query to return results. You can say this is the intersection, or solid triangle, in Figure 22-5 where all three sets of criteria are met. For example, if you set the conditions as shown in the following screen, the query will return only American Red wines produced before 1980. Given the sample data in Table 22-1, only record 8 is selected.

Figure 22-5: Sets of criteria and how they intersect

Table 22-1: Sample Wine Data for Complex Conditions

Record	Producer	Country	Vintage	Color	Varietal
1	Haut Brion	France	1961	Red	Bordeaux Blend
2	Chateau Ste Michelle	USA	1995	White	Chardonnay
3	De Fieuzal	France	1978	Red	Bordeaux Blend
4	Vega Sicilia	Spain	1970	Red	Proprietary Blend
5	Storybook Mountain	USA	1995	Red	Zinfandel
6	Oxford Landing	Australia	1997	White	Sauvignon Blanc
7	Ramonet	France	1978	White	Proprietary Blend
8	Robert Mondavi	USA	1966	Red	Cabernet Sauvignon

OR Operand and Nested Conditions

The complexity arises when you want to join one set of conditions with an OR and the other with an AND. For example, you may want to select only Red wines produced before the year 1980 but want all wines for USA regardless of the color and regardless of the Vintage. Based on the sample data shown in Table 22-1, you want records 2, 5, 8, where the Country = USA, and only 1, 3, and 4, where the Color = Red and the Vintage <= 1980. Record 6 is not selected, because it satisfies none of the conditions; record 7 is not selected, because while the vintage is < 1980, the color is White, not Red. As shown in Figure 22-5, you want the intersection between color and vintage, shown with the dot-filled intersection; for the country criterion, you want the full circle with no intersection.

To change a condition connector from an AND to an OR, select the AND and double- click to change it to an OR. BusinessObjects will automatically indent two of the conditions. The indent has the same effect as putting a parenthesis around your condition statements. Unfortunately, it usually does not nest the conditions the way you want it to, and getting them exactly right can be a lesson in patience. I find it helpful to periodically look at the SQL (by clicking the SQL button from the Query Panel) to ensure the parentheses are in the right place. To change the indent in the Query Panel and thereby adjust the parenthesis in the SQL, right-click the operand to invoke the pop-up menu shown next and select Indent Left or Indent Right as needed.

Tip 

To nest two conditions with an AND and one condition with an OR, as shown in the following screen, first set all operators to OR, then correct the indents:

WHERE   ( WINE_FACT_PRICE_RATE.Country  =  'USA'   OR   ( WINE_FACT_PRICE_RATE.ColorClass  =  'Red'       AND WINE_FACT_PRICE_RATE.Vintage  <=  1980 )   )

In the preceding example, the OR operator allowed you to change one particular criterion. You also can use OR to search for the same value across multiple fields. For example, in SAP, the bill of lading may appear in several fields. With the following conditions, the OR operator allows you to search in multiple fields. By using the exact same prompt for each of the conditions, you only need to enter one bill of lading number and it is filled in each of the conditions.

UNION/INTERSECT/MINUS

In some cases, nesting the conditions will still not give you the desired results. This often happens when you want to test for multiple values against the same dimension object. For example, let’s say you are looking for wine producers who make both red wines and white wines. If you use the In List operator, you will get a list of producers that make either red or white wines. If you create two conditions and join them with AND, you will get no rows returned, as the conditions in the WHERE clause are mutually exclusive.

WHERE   (  WINE_FACT_PRICE_RATE.ColorClass  =  'Red'   AND  WINE_FACT_PRICE_RATE.ColorClass  =  'White' ) 

To retrieve the desired results, you need to create two queries within one data provider. BusinessObjects is one of the few BI tools that allows you to create multiple queries using the RDBMS’s set operators: UNION, INTERSECT, and MINUS. Table 22-2 shows the different operator icons and their purposes. In order to use these operators, the number of result columns and the data type for each of the columns must be the same. Often when using INTERSECT, the result objects are exactly the same and only the conditions change between the two queries. Chapter 7 discussed some of the problems with queries that contain loops, a join path that BusinessObjects does not allow; INTERSECT is a better alternative to answer a query that otherwise would contain a loop. For UNION and MINUS, you may want to change the measure column. It’s possible to do this, but in the report, BusinessObjects will use the variable name from the first query. MINUS is also useful for determining if there are data quality issues between a fact table and dimension table, rather than using an outer join (see Chapter 7). For example, if you want a list of products for which there are no corresponding sales transactions, the main query would contain products from the dimension table and the minus query would contain products from the fact table. The result set is a list of products for which there are no records in the fact table. Table 22-2 describes the available operators and combined query icons within the Query Panel. You can use these operators to combine two or more queries.

Table 22-2: Set Operators to Combine Queries

Query Tab	Operator	Explanation
	UNION	Combines the results of multiple queries. When the query contains a measure, the common rows are aggregated.
	INTERSECT	Selects the rows that intersect or overlap between the two queries. This is not recommended for use with measures as a result object. When the query contains a measure object, unless the measure values are exactly the same, there is no intersection.
	MINUS	Subtracts the rows in the second query from the main query. When a measure is included and the measure objects are the same, the aggregate from the subquery is subtracted from the main query measure.

To create a combined query, first evaluate the result objects in your main query. If you are using INTERSECT, ensure that it does not contain any result objects for dimensions that you will use as a condition. Select the Combine Queries button from the Query Panel. BusinessObjects inserts a new query with the same results objects as those in Query 1. The icon on the Query 2 tab indicates the type of operator. In Figure 22-6, Query 2 is combined with an INTERSECT operator as indicated with the U symbol. To change the type of operator, double-click the operator icon on the query tab or right-click the tab to invoke a pop-up menu.

Figure 22-6: Combined queries use UNION, INTERSECT, or MINUS operators.

Note that the dimension for which you want to retrieve multiple values, Color, appears only in the conditions. If it appeared in the result objects, no rows would return, as these values are mutually exclusive. Table 22-3 gives sample records to show how the different set operators work. The intersection between the two queries, as shown in Figure 22-6, consists of records 1, 2, 3, 4, 6, and 7. Record 5, Harlan Estates, is not selected, because they do not make both red and white wines. If Vintage were added to the result column, then only records 6 and 7 would be selected, as these are the only two records in which there is an intersection for all three results columns. If Varietal were added as a result object, no rows would be returned, as there is no Red and White Varietal that is the same (if available, it may return producers who make Red and White Zinfandel, as the varietal name Zinfandel would be the same on multiple rows).

Table 22-3: Sample Wine Data for Combined Queries

Record No.	Producer	Color	Varietal	Region	Vintage
1	Beringer	Red	Cabernet Sauvignon	California	1987
2	Beringer	Red	Merlot	California	1998
3	Beringer	White	Chardonnay	California	1996
4	Beringer	White	Sauvignon Blanc	California	2000
5	Harlan Estate	Red	Proprietary Blend	California	1997
6	Arrowood	Red	Malbec	California	1993
7	Arrowood	White	Chardonnay	California	1993

In the preceding example, if you change the operator to MINUS, the main query returns a list of producers of red wine and then removes the rows for those who also produce white wine. Therefore, only record 5 from Table 22-3 will be selected. Using the UNION operator in this example has the same effect as using IN List as the condition. With queries that contain multiple OR statements that are nested, you may find the query runs faster with UNION.

Caution 

You can change the result objects to aggregate two different measures using UNION. For example, you could use a UNION query to aggregate fixed cost + variable cost or if you have sales figures for two different companies that come from two different fact tables. However, when you use a MINUS operator and the measure objects are different, the rows from the second query are simply removed; the values are not subtracted unless the field names in the second query are the same.

Tip 

You also can use the condition operators BOTH or EXCEPT to generate the INTERSECT and MINUS queries. Remember when using BOTH / INTERSECT not to include the same condition objects as result objects or you will get no rows returned.

---