KB20060: What is the difference between a report filter and a report limit in MicroStrategy SQL Generation Engine?

Some of the filtering features and behaviors of the Strategy SQL Generation Engine depend on the distinction between filter qualifications and limit qualifications. Both types of qualifications place restrictions on the data returned by a query, but they do so at different points in the data processing flow.
The general rule is: filter first, limit last.
Filter qualifications
Filter qualifications apply conditions directly to physical rows in database tables, prior to aggregation of metric values. In this example, the query filters on years 2005 and 2006, and then aggregates (sums) the unit sales over Category. This is analogous to a Strategy Tutorial report with Category and Unit Sales on the template, and a report filter defined as Year in (2005, 2006).

select CATEGORY_ID,
   sum(TOT_UNIT_SALES) TOT_UNIT_SALES
from YR_CATEGORY_SLS
where YEAR_ID in (2005, 2006)
group by CATEGORY_ID

Filter qualifications generally go into the WHERE clause in SQL. (Set qualifications, discussed below, are the exception.) Databases evaluate the WHERE clause before performing other operations. By evaluating most or all of the restrictions on the data set prior to aggregation, the database can save time in the aggregation phase by performing the calculations over a smaller data set.
 

Limit qualifications
 
By contrast, limit qualifications apply their conditions to the results of aggregation. In SQL, limit conditions go into the HAVING clause, to distinguish them from filter conditions. All conditions based on aggregate expressions must be placed in the HAVING clause, meaning that they must be limit conditions. Also, SQL does not allow limit conditions (HAVING) unless an aggregate function (Sum, Avg, etc.) is used in the query. This requirement makes sense; since limits evaluate after aggregation, they are meaningless in queries that do not aggregate.
For example, using the same fact table, the following query retrieves the Units Sold for those categories that sold over 200,000 units across all years.

select CATEGORY_ID,
   sum(TOT_UNIT_SALES) TOT_UNIT_SALES
from YR_CATEGORY_SLS
group by CATEGORY_ID
having sum(TOT_UNIT_SALES) > 200000

 

It should be evident that the limit condition must follow the aggregation. It is impossible to know which categories sold enough units without knowing the unit sales for all categories.
The order of clauses in SQL reflects the order of evaluation:

1. Select
2. From
3. Where
4. Group by
5. Having
6. Order by

That is, apart from "select" and "from," the order of operations is: filter (WHERE), aggregate (GROUP BY), limit (HAVING) and finally sort (ORDER BY).
Limits in Strategy SQL Generation Engine
Strategy reports can define a report limit based on one or more metric conditions. The report limit editor is found in the Report Data Options dialog box (Data menu > Report Data Options > Report Limit). The report limit applies to the final result pass: As a report limit it should apply to the complete report results, not to individual metrics.
Report limits may be based only on metric conditions. Other types of qualifications are based directly or indirectly on attribute elements, which by definition are not subject to aggregation functions. Since filters allow for more efficient metric calculation, there is no benefit to placing valid where-clause conditions into the HAVING clause. Metric conditions depend on the aggregate results and make sense as a report limit.
Report limits may be used to qualify on a metric with only one pass of SQL, provided that all of the following are true:

• All metrics on the template or in the Report Objects window are at report level.
• The report limit is defined on metric(s) also at report level.
• All metrics (including metrics used in the report limit) have the same condition (filter).
• All metrics (including report limit metrics) come from the same fact table and can all be calculated with the same FROM clause.

For example, a report with Subcategory and report-level Revenue on the template, and the condition Revenue >= 2,850,000 in the report limit, generates the following SQL. One pass is produced because all prerequisites for one pass are met.

select a11.SUBCAT_ID SUBCAT_ID,
   max(a12.SUBCAT_DESC) SUBCAT_DESC,
   sum(a11.TOT_DOLLAR_SALES) Revenue
from CITY_SUBCATEG_SLS a11
   join LU_SUBCATEG a12
      on (a11.SUBCAT_ID = a12.SUBCAT_ID)
group by a11.SUBCAT_ID
having sum(a11.TOT_DOLLAR_SALES) >= 2850000.0

If any of the above conditions are false, multipass SQL is necessary and the report limit cannot be written in the same way. In that case, the report limit metric is calculated in its own pass and joined into the final pass with the other metrics. The report limit condition then appears in the WHERE clause of the final pass. (Even though it is in the WHERE clause, it is still conceptually a limit because the template metrics have already been aggregated into intermediate tables; the limit condition thus follows the aggregation, by definition.)
A report with Subcategory and report-level Revenue on the template, but limiting on Units Received >= 30779, generates the following three passes because Units Received comes from an inventory fact table while Revenue comes from a sales fact table.

Template metric
select a11.SUBCAT_ID SUBCAT_ID,
   sum(a11.TOT_DOLLAR_SALES) Revenue
into #ZZSP00
from CITY_SUBCATEG_SLS a11
group by a11.SUBCAT_ID

Report limit metric
select a12.SUBCAT_ID SUBCAT_ID,
   sum(a11.UNITS_RECEIVED) WJXBFS1
into #ZZSP01
from INVENTORY_ORDERS a11
   join LU_ITEM a12
      on (a11.ITEM_ID = a12.ITEM_ID)
group by a12.SUBCAT_ID

Report limit condition in WHERE clause, but still after aggregation
select pa11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   pa11.Revenue Revenue
from #ZZSP00 pa11
   join #ZZSP01 pa12
      on (pa11.SUBCAT_ID = pa12.SUBCAT_ID)
   join LU_SUBCATEG a13
      on (pa11.SUBCAT_ID = a13.SUBCAT_ID)
where (pa12.WJXBFS1 >= 30779.0)

drop table #ZZSP00
drop table #ZZSP01

 

Metric qualifications in filters in Strategy SQL Generation Engine 8.x
 
As noted earlier, filter qualifications apply prior to metric aggregation. Even though a metric qualification depends on the result of an aggregation, it still must apply before template metrics, or metrics used in compound metrics. For this reason, a metric qualification in a filter is a type of set qualification. A set qualification first produces a set of attribute elements. Subsequent metric calculations include only those fact table rows that correspond to the set.
The procedure is as follows.

1. Calculate the filtering metric at the level of the qualification.
2. Apply a limit to that metric, preserving the attribute elements that meet the qualification.
3. Aggregate the metric to be filtered at its own level, intersecting fact rows with the filtering set (by inner joining the fact table to the table holding the set of attribute elements passing the filter).

Because the condition must be evaluated before aggregating the resulting metric, multipass SQL is a requirement for any type of set qualification. Even if a metric qualification exists at report level, involves no other filtering conditions and comes from the same fact table as the resulting metric, the metric qualification still must have its own SQL pass. Otherwise, the filter would be converted into a limit, potentially changing the meaning of the report.
However, in cases where multiple passes would be required even for a report limit, metric filter qualifications may be more efficient. When a metric limit is used, template metrics are calculated without respect to the metric condition. This means that intermediate tables will be larger and the database will have to do more work to populate them. With a metric filter, only the filtering metric need be evaluated without the metric condition. All subsequent metrics to which the condition applies will restrict the fact rows before aggregation, reducing the size and resource-intensity of those calculations. The more template metrics share the same metric qualification, the more efficient the metric filter becomes.
For example, a report to show the profit and units received for the top 10 grossing items could be built with a filter or a limit. Although the report limit generates fewer passes, the intermediate tables for the metrics are significantly larger. This is not so much of a gain in the Strategy Tutorial project where the number of Items is small. When reporting over hundreds of thousands of items in a more typical warehouse, the metric filter has a good chance of performing better.
 

Calculation differences between metric filters and limits
 
In most cases, the result of a filter is the same as that of the equivalent limit. Cases do exist where the order of filter evaluation does impact the results. Generally, such cases involve metrics at different levels, with the same metric condition applying to both.
An example of this scenario may be found in the following Strategy Knowledgebase document.
KB20062: In what kind of report does a report limit produce a different result from a metric qualification in Strategy SQL Generation Engine?