Is the dimension that you want to filter on the row/column shelf, level of detail or anywhere else in the view (other than filter shelf)?
If the filter is only on the filter shelf, then each time a selection is made, say a or b, then a new query would be sent to the database similar to for example select sum(some measure) where dimension not a or b. This is highly inefficient as the aggregation has to be computed at the database each time the selection is changed. Furthermore, this type of query with the where clause, goes line by line to compute the summation where the matches appear. This is slower than using a group by clause mentioned below.
However if the filter is part of the view, then the query would be sent once to the database amounting to something like select sum(some measure) group by dimension. This would produce an aggregated already grouped by the members of the dimension. Subsequently any selection changes would not resend the query to the database, but calculated locally. Also, group by queries are much faster than the one in the paragraph above.
Also, if there any other quick filters with relevant values set, that would also send queries to the database to populate that filter list whenever a selection is made.
Thank you Daniel,
That really helps, thank you for taking the time to explain it. I have one follow on question:
I have previously heard that quick filters can be inefficent. Does what you've explained mean that if the item that is being used as a quick filter is as well included in the view that after initial viz population, quick filter calculations would take place locally, and be more efficent?
1 of 1 people found this helpful
You're welcome, Nathan.
Yea generally too many quick filters can slow down the workbook. If it is included in the view, then as you select the different options in the filter, it will not resend queries to database, and the 'slicing' will be done locally.
An alternative to quick filters is using action filters.
In v8, we will see more improvements in the efficiency of quick filtering.