Filter Your Data Carefully

Filters are an effective way to restrict the amount of data for analysis. The less data to analyze, the faster the query executes. In Tableau, there are a variety of ways to filter your data. You can create a filter on a data source, you can implement a filter when you create an extract, you can filter dimensions, measures, and dates. You can create a context filter that acts as an independent filter—then any other filters you set are dependent filters because they process only the data that passes through the context filter. You can also show a filter in a view that users can interact with. For information about when filters are executed, see Tableau's Order of Operations.

Filters provide enormous flexibility, but you should use them carefully. They can be computationally expensive, so reducing the number of filters can improve workbook performance.

Filter your data in the data source

When you create a filter on a data source, you reduce the amount of data in the data source. For systems that rely heavily on partitions or indexing, data source filters may yield tremendous control over the performance of queries issued by Tableau. For more information, see Filter Data from Data Sources.

Use Keep Only filters instead of Exclude filters

Because Exclude filters load all of the data for a dimension, they run more slowly than Keep Only filters. For more information, see Select to keep or exclude data points in your view(Link opens in a new window).

Add filters first

If you are working with a large amount of data and have automatic updates turned off, you can inadvertently create a very slow query when you add filters to the view. Rather than building the view first, and then specifying filters, specify the filters first and then drag fields to the view. That way, when you run the update or turn on automatic updates, the filters are evaluated first.

Use context filters sparingly

Think of a context filter as an independent filter; any other filters you set process only the data that passes through the context filter. Only use context filters when they limit the size of the data set significantly. If you are setting filters that significantly reduce the data set size, and that will be used for more than several data views, you should set those filters as context filters. For more information, see Improve View Performance with Context Filters. For more information about performance improvement with context filters, see Speed up Context Filters.

Use a set to filter data

If you want to filter a dimension to remove members based on a range of measure values, you should create a set rather than using a quantitative filter. For instance, you can create a set that only returns the Top 50 items in a dimension, rather than all of the items in a dimension.

When you create a group from a selection as described in Group Your Data, make sure you include only the columns of interest. Each additional column in the set decreases performance.

Aggregate your data using custom SQL

As an alternative to filters, another effective way to restrict the amount of data for analysis is to use custom SQL to aggregate measures before you bring the data into Tableau. Suppose your reviews data is stored in a SQL Server table, and for your analysis, you’re only interested in the data from the “rating” and “reviewer ID” fields. To pre-aggregate the data so that you bring into Tableau only the fields you’re interested in, you might use the following custom SQL:

This custom SQL query reduces the amount of data brought into Tableau because it pre-aggregates the reviews and averages the values in the review ratings.

Filter your cube data

Here are some specific tips that apply to filtering cube data.

Filter on a specific dimension

If your cube has a single large dimension, you should set a filter directly on that dimension rather than setting a filter on another dimension or measure. For example, suppose you want to reduce the numbers of products being displayed in a view. It is much more efficient to set the filter directly on Products or to create a computed set based on Products (such as Top 10) rather to filter other fields such as Location or Profit.

Also, avoid selecting large numbers of members from a large dimension. When a dimension is large, keep the size of the filter to less than a thousand members.

Create a set

When creating a set from a selection as described in Create Sets, make sure you've included only the columns of interest in the Create Set dialog box. Each additional column in the group decreases performance. For example, if you create a set that contains all regions with sales between 8,000 and 15,000, but you include a column that doesn't affect the members of the set, you might notice a performance decrease. To remove extra columns, right-click the column and select Remove This Column from the context menu.

Don’t apply sorts to levels

Avoid applying sorts to levels within a very large hierarchy in a cube.

Use the Exclude command

If you are working with a dimension whose root level is greater than 1000 but less than 100,000, avoid using the Filter dialog box to filter the data. Instead, drag the dimension to a shelf and use the Exclude command in the headers context menus to limit the data that is displayed in the view.

Thanks for your feedback!