Using Tableau’s relationship model to tame survey data

Part 1 – Getting the right structure for Tableau and survey data

Survey data in Tableau has always been a bit… tricky.

It’s not that it can’t be done. Steve Wexler’s excellent blog posts on DataRevelations.com have shown time and again that it can. His work has helped many people, including me, reshape their data to get meaningful insights. Steve talks about getting the data “just so,” which usually means transforming it into one long, tall table: one row per response per question where the context such as the question text, desired filters, and responses are all joined into one big table.

That approach works. But anyone who has done this on complex projects knows it has some limitations.

The maintenance problem: While Steve’s traditional approach handles data volume well (he’s successfully worked with 30M+ rows), the real challenge comes with survey complexity. When surveys use hierarchical structures, need supplemental data sources, include additional information or require complex metadata relationships, the single table model struggles to adapt. You end up either duplicating data extensively or creating increasingly complex workarounds to maintain the “one big table” structure.

The missing data problem: If no one answered an option, it won’t appear in the analysis. Due to the way response data is joined to question data, if a response doesn’t exist in the responses, it simply won’t show in the analysis. We can develop scaffolding techniques to address this, but they can get complex and confusing quickly.

My classic example: What would you rather do on a weekend? Sit and drink in the sunshine? Or get attacked by a swarm of bees? In an honest survey, no one will respond to getting attacked by bees. But that piece of information is important—it adds context to anyone reviewing the data.

This is where Tableau’s data model becomes a game changer. Instead of flattening everything into a single table, Tableau’s data model (the relationship-based data model introduced in 2020.2) lets us define relationships between tables while preserving their original structure. This approach dynamically adjusts aggregations based on context—including showing those all-important missing categories.

Part 2 – A scalable survey structure

At the heart of this approach is a dimensional model, a structure that’s common in data warehousing and well-suited to analytical tools like Tableau. This approach is also known as Kimball-style dimensional modeling, named after Ralph Kimball who pioneered the methodology for organizing data into fact and dimension tables in “The Data Warehouse Toolkit” (co-authored with Margy Ross). If you’re new to dimensional modeling concepts, Tableau’s documentation on The Tableau Data Model provides excellent background reading.

Here is what each table contains:

fact_responses

This is your long-form data: one row per respondent per variable. Each row captures what one person answered to one question. This table will typically run into the millions of data points. We typically extend the table to include a value_str field to hold open-end text filled in by the survey respondent and a value_float to store any numeric fields the survey respondent

respondent_id	variable_id	value_id
101	Q1	1
101	Q2	3
102	Q1	2

dim_respondent

This holds information about each respondent: filtering variables like gender or generation, and weighting variables for representative analysis.

respondent_id	country	gender	weight
101	UK	Male	1.2
102	UK	Female	0.8

dim_codebook

This contains the metadata: variable names, question text, and all possible answer options (e.g., “Region” with options “North”, “South”, “East”, “West”—even if no one selected “North”).

variable_id	value_id	question_text	value_label
Q1	1	What is your region?	North
Q1	2	What is your region?	South
Q1	3	What is your region?	East
Q1	4	What is your region?	West

We use standard dimensional modelling terminology (fact/dim) because it helps keep things organised and scalable as your survey project grows. Anyone familiar with this way of structuring data will automatically knows that the fact table has all the transactional data whilst the dim tables are dimension/reference/lookup tables.

In real-word projects, you will expand these tables with extra columns, for example in a longitudinal study a date_id (linked to a dim_dates table) or adding addition columns to our dim_codebook to add more context to our questions. (Steve Wexler typically has a column called question_type, which helps dictate the analysis and charts when we get to Tableau. More on both these concepts in future blogs.

In Tableau, our base model looks clean and straightforward:

The key to this structure is how the tables relate:

fact_responses links to dim_codebook on variable_id and value_id
- Cardinality: Many to One (many responses can reference the same question/answer combination)
- Referential Integrity: Some records match (not every possible answer option will have responses)
fact_responses links to dim_respondent on respondent_id
- Cardinality: Many to One (each respondent can have many responses)
- Referential Integrity: All records match (every response belongs to a known respondent)

What’s crucial is that we don’t join these tables in the traditional sense.

We relate them in Tableau using the data model, preserving granularity and providing more flexibility in how we work with the data.

And from here… everything just works.

Easier maintenance: Need to update question labels? Change them once in dim_codebook rather than rebuilding the entire merged table
Cleaner interpretation: When tables are separated, it’s easier to understand what each piece of data represents
Simpler missing data handling: No need for complex scaffolding techniques—missing categories appear naturally
Reusable structure: Add new surveys without restructuring your entire data model
Using respondent weights: Calculating weighted counts of respondents is as simple as dragging on the weight measure into your view

Part 3 – How this works in practice

You can follow along using this example dataset Relationship survey datasource.

The data source is a modified version of Steve Wexler’s example survey dataset, restructured to work with the relationship model.

A video walkthrough of how to build the relationship model is available here:

The video gives a quick overview of how to get started using Tableau’s data model with survey data.

When the traditional approach might be better:

Small, one-off surveys where simplicity trumps reusability
Teams already comfortable with Steve Wexler’s proven methodology
Projects where the single table approach is already working well

When the relationship model excels:

Ongoing survey projects with regular updates
Large surveys with frequent metadata changes
Teams familiar with dimensional modelling concepts
Projects requiring complex survey structures (which we’ll explore in future posts)

The Bottom Line

This relationship-based approach is particularly valuable for “classically trained” data engineers and analysts who are comfortable with dimensional modelling concepts. It provides a familiar structure that scales well with growing survey projects.

In upcoming posts, I’ll explore how this model handles looped questions, multi-wave surveys, changing question definitions, and creating side-by-side filters with “all respondents” columns—the kind of traditional crosstab tables that clients love, but made simple with the relationship model.

Have questions or want to share how you are using Tableau with survey data? Drop me a message, I’d love to hear from you and learn about your challenges and successes.

Andrew Le Breuilly

Andrew has over 15 years of experience working in Data Visualisation and Market Research. He has worked on some of the most complex surveys and has delivered 200+ Tableau dashboards for clients. He holds the Market Research Advanced Certificate and has a passion for delivering high quality, visually appealing survey analysis.