Set Operator Optimization

Set Operator Optimization is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Set Operator Optimization property determines whether to use set operators, such as EXCEPT and INTERSECT, to combine multiple filter qualifications rather than their equivalent logical operators such as AND NOT and AND. Set operators can be used to combine two or more of the following types of set qualifications:

Relationship qualifications
Metric qualifications when combined with other types of set qualifications with the logical operators AND, NOT, or OR
Report as filter qualifications when combined with the logical operators AND, NOT, or OR

Set operators can only be used to combine the filter qualifications listed above if they have the same output level. For example, a relationship qualification with an output level set to Year and Region cannot be combined with another relationship qualification with an output level of Year.
Metric qualifications and report-as-filter qualifications, when combined with AND, render as inner joins by default to avoid a subquery in the final result pass. When Set Operator Optimization is enabled, the inner joins are replaced by subqueries combined using INTERSECT.
Metric qualifications at the same level are combined into one set qualification before being applied to the final result pass. This is more efficient than using a set operator. Consult MicroStrategy Tech Note TN13536 for more details.
For more information on filters and filter qualifications, see the Advanced Filters section of the MicroStrategy Advanced Reporting Guide.

Along with the restrictions described above, SQL set operators also depend on the subquery type and the database platform. For more information on sub query type, see Set Operator Optimization. Set Operator Optimization can be used with the following sub query types:

WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN
WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)
WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN
Use Temporary Table, falling back to IN (SELECT COL) for correlated sub query
If either of the two sub query types that use fallback actions perform a fallback, Set Operator Optimization is not applied.

The following database platforms support SQL set operators:

Database	Intersect	Intersect ALL	Except	Except ALL	Union	Union ALL
ANSI 92	Yes	Yes	Yes	Yes	Yes	Yes
DB2 UDB	Yes	Yes	Yes	Yes	Yes	Yes
Informix	No	No	No	No	Yes	Yes
Oracle	Yes	No	Yes (Minus)	No	Yes	Yes
RedBrick	Yes	Yes	Yes	Yes	Yes	Yes
SQL Server	Yes (2005 and later)	No	Yes (2005 and later)	No	Yes	Yes
Tandem	No	No	No	No	No	No
Teradata	Yes	Yes	Yes	Yes	Yes	Yes

If you enable Set Operator Optimization for a database platform that does not support operators such as EXCEPT and INTERSECT, the Set Operator Optimization property is ignored.

The Set Operator Optimization property provides you with the following options:

Disable Set Operator Optimization (default): Operators such as IN and AND NOT are used in SQL sub queries with multiple filter qualifications.
Enable Set Operator Optimization (if database support and [Sub Query Type]): This setting can improve performance by using SQL set operators such as EXCEPT, INTERSECT, and MINUS in SQL sub queries to combine multiple filter qualifications that have the same output level. All of the dependencies described above must be met for SQL set operators to be used. If you enable SQL set operators for a database platform that does not support them, this setting is ignored and filters are combined in the standard way with operators such as IN and AND NOT.
For a further discussion on the Set Operator Optimization VLDB property, refer to MicroStrategy Tech Note TN13530.

Levels at Which You Can Set This

Database instance, report, and template