Kirk Munroe: New Tableau 2025.1 - Table-Level Filters
Kevin and I are
pleased to welcome back a regular contributor, Kirk Munroet. Kirk lives in
Halifax, Nova Scotia, Canada and is a business analytics and performance
management expert. He is currently one of the owners and principal consultants
at Paint with Data,
a visual analytics consulting firm, along with his business partner and wife,
Candice Munroe, a Tableau User Group Ambassador and former board member for Viz for Social Good.
Kirk is also the author of Data Modeling with Tableau, an extensive guide, complete with step-by-step explanations of essential concepts, practical examples, and hands-on exercises. The book details the role that Tableau Prep Builder and Tableau Desktop each play in data modeling. It also explores the components of Tableau Server and Cloud that make data modeling more robust, secure, and performant.
Recent
Data Modeling Improvements
Tableau has been
making significant strides in data modeling since the 2024.2 release and
Tableau Conference 2024.
In 2024.2,
Tableau introduced multi-fact relationships, a groundbreaking feature I’ve
described as the
most significant innovation in business intelligence since Tableau’s launch in
2005.
At TC24, Tableau
unveiled another potential game-changer: composable
data sources. This feature allows users to create relationships with
published data sources (PDS) without needing to modify the original PDS. There
has been a lot of buzz around composable data sources in the Tableau
community—many are even more excited about this than multi-fact relationships.
While I wouldn’t rank it higher, I can see why—it simplifies data strategy,
governance, and access in transformative ways.
To enable
composable data sources, Tableau had to overhaul its query layer and redesign
parts of its user interface and experience. Multi-fact relationships weren’t
just impactful on their own—they were a crucial foundation for bringing
composable data sources to life.
The next step was
exposing the data model (or graph) of published data sources in the UI, which
Tableau delivered in the 2024.3 release. For now, this feature provides a
visual representation of the data model, but it’s already a big win for data
governance. Before 2024.3, connecting to a PDS was a complete black box,
offering no visibility into its tables and relationships. Tableau Visionary,
Sean Miller highlighted this in a post
for Tableau when the feature launched.
Introducing
Table-Level Filters
Next up on the
roadmap to composable data source is table-level
filters, which I’ll be diving into today. I’ve previously written about how
much I love relationships and how they enable Tableau to write your SQL for
you. They enable less data and more answers when compared to (pre-)joining your
data. There has been one significant exception to this rule: when you add a
data source (or extract) filter to a model based on relationships, Tableau will
take that filter and apply the WHERE clause across all related tables (i.e. it
will “force” inner joins even though the relationships aren’t supposed to join
data until you create a viz). Let me demonstrate what this means.
Let’s start with
a simple data model:
This model
connects our sales orders with customers who place those orders, the line items
that make up each order, and the product associated with each line item from
the various orders.
Let’s check to
see how many customers we have. Dragging Count
of Customer ID to Text shows us that we have 794 unique customers. (The
great thing about relationships is that Count or Count Distinct give us the
same number because we haven’t “exploded” our customers with a join).
Now, let’s go
back to the data source page and add a filter to category and select Technology:
Then, if we go
back to the sheet we created the number is now 687:
Huh? Somehow 107
customers were lost. This shouldn’t make sense—why are there less customers
now? Tableau filtered out all the customers who have only bought furniture.
This intuitively doesn’t feel right. The big advantage of relationships is more
answers with less data—something that can only be achieved by not pre-joining our data. This was a
constraint at the Tableau query level that needed to be addressed. However, it
is important to note that there are times when we do want to force that data
source filter to apply to all the tables in the data model. For example, if
we’re implementing row-level securing using an entitlements table, we want to
force that data to be pre-joined. So, the best case scenario is that we’d have
the ability to specify which tables we want to filter.
Good news!!
Tableau 2025.1 introduces a new feature that allows us to do exactly this!!
Going back to the
data source page and opening the filter dialogue, there is a new option
called, “Scope”:
The default is
effectively, “this table and related tables”, which was the only behavior before 2025.1. This
enables backward compatibility—if you don’t touch this setting moving forward,
it’ll just work the same way as it always has. But it also gives us the ability
to specify which tables are filtered.
To get the
desired behavior in 2024.3 and earlier, we would have to convert the product
table to custom SQL and add a WHERE clause to filter out furniture. This isn’t
ideal because we can’t use custom SQL with all sources (including the csv files
used in this example) as well as other performance implications.
After making this
change, our Count of Customer ID is
back to 794!
If you’ve used
relationships over the last several years, I’m guessing you can already see the
value of this feature. This, of course, was a very simple example, but imagine
how tricky this would have gotten with more complex data models! This new
feature removes the guesswork and eliminates the need for hacks to get things
to work just so.
Generated
SQL
Who wants to see
some SQL? As someone with a SQL background, I personally find it informative to
review the generated SQL as it helps me to really understand what’s going on
under the hood. So, if you’re interested, let me share the SQL Tableau
generated in each of these filters.
When you choose “this and related tables” and simply open the workbook, the following SQL is generated and run (edited for readability):
And when you choose just the products table, the following SQL is generated and run:
You probably
think I forgot to share the SQL, but I didn’t. That’s right—no query is run at
all when the workbook opens. The query is only run when something on the view
requires it. For example, if I add Sales
and Category, Tableau generates the
following:
The key is that
the WHERE clause from the filter is only run when it needs to build the view
and it’s only applied to the products table.
Thanks so much
for reading! If you have any questions or comments, please share them in the
comments section below.
Kirk Munroe, March 24, 2025
No comments: