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!