Comparison of various types of MicroStrategy filters

Comparison of various types of MicroStrategy filters

Main reason to use Report Limit, else it doesn’t give any performance improvement compared to Report Filter (Metric based) in case of big data set to be SQLed.

Report Filter Vs 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!