Metric Filtering: Report Filter or Report Limit?

The most common way of filtering a Report in MicroStrategy is via the Report Filter.  Anything placed into this filter appears in the SQL Statement that is sent to the database.  But what exactly happens to the SQL when you place a Metric in the Report Filter?  How does that compare to a Report Limit which also filters on Metric values?  Which is more efficient?  Today we’ll explore.

Since a Metric can have aggregation depending on the level of the Report Template, MicroStrategy resolves this as a Set Qualification, which means it will run a multipass query to first determine the set of Attribute Elements that should be filtered on in the WHERE clause.  This results in multiple hits to the fact table, which could hurt performance.

By comparison, a Report Limit is essentially a HAVING clause.  This would result in simply tagging on the HAVING command to the end of the query that’s already there, clean and simple.

Here’s an example from my system:

No Filter – 32sec – 452 rows

select a12.Attribute,
sum(a11.Fact)  Metric
from FactTable a11
join DimAttribute a12
 on (a11.AttributeKey = a12.AttributeKey)
join DimDate a13
 on (a11.DateKey = a13.DateKey)
where a13.Year in (2011)
group by a12.Attribute

Report Filter – 3min 17sec – 242 rows

select a12.Attribute ,
sum(a11.Fact)  Metric
from FactTable a11
join DimAttribute a12
 on (a11.AttributeKey = a12.AttributeKey)
join (select a12.Attribute  Attribute
from FactTable a11
join DimAttribute a12
 on (a11.AttributeKey = a12.AttributeKey)
join DimDate a13
 on (a11.DateKey = a13.DateKey)
where a13.YearID in (2011)
group by a12.Attribute
having sum(a11.Fact) > 1000.0
) pa13
 on (a12.Attribute = pa13.Attribute)
join DimDate a14
 on (a11.DateKey = a14.DateKey)
where a14.YearID in (2011)
group by a12.Attribute

Report Limit – 34sec – 242 rows

select    a12.Attribute,
    sum(a11.Fact) Metric
from    FactTable    a11
    join    DimAttribute    a12
     on     (a11.AttributeKey = a12.AttributeKey)
    join    DimDate    a13
     on     (a11.DateKey = a13.DateKey)
where    a13.YearID in (2011)
group by    a12.Attribute
having    sum(a11.Fact) > 1000.0

In this example, there is a pretty significant difference in performance.  Report Limits aren’t always the fastest, but in my experience they usually are for the reports I’m doing.  At the very least, they should be considered and tested for your report and environment.

Reports Limits aren’t just for breaking ranking ties!