35,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
In today’s data-driven business world, advanced analytics set organizations apart. Basic visualizations no longer suffice for Tableau developers tackling complex data challenges. Written by Tableau experts who've trained Fortune 500 companies and led global analytics initiatives, this cookbook delivers battle-tested techniques with the perfect blend of technical depth and practical application.
You’ll master advanced techniques such as geospatial analysis, data modeling for optimized workflows, and enterprise-scale content management. This book shows you how to leverage Tableau cloud’s Data Management capabilities to centralize data sources and ensure data quality for consistent analytics. You’ll also explore advanced management features such as the Content Migration Tool in Tableau 2025.1 and beyond.
Bridging the gap between fundamentals and cutting-edge practices, this book extends Tableau’s capabilities with APIs, custom LOD expressions, virtual connections, data apps, and TabPy. You’ll gain the skills to solve complex business problems, create high-impact dashboards, and seamlessly integrate Tableau into your data strategy, all while adhering to security and governance best practices.
This isn’t just another Tableau cookbook—it’s your blueprint for mastering the platform and driving meaningful data-powered transformation.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 408
Veröffentlichungsjahr: 2025
Ähnliche
Tableau Cookbook for Experienced Professionals
Over 60 advanced recipes for maximizing performance, interactivity, and platform potential
Pablo Sáenz de Tejada
Daria Kirilenko
Tableau Cookbook for Experienced Professionals
Copyright © 2025 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
The author acknowledges the use of cutting-edge AI, such as ChatGPT, with the sole aim of enhancing the language and clarity within the book, thereby ensuring a smooth reading experience for readers. It’s important to note that the content itself has been crafted by the author and edited by a professional publishing team.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors nor Packt Publishing or its dealers and distributors will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Portfolio Director: Sunith Shetty
Relationship Lead: Nilesh Kowadkar
Project Manager: Hemangi Lotlikar
Content Engineer: Ayushi Bulani
Technical Editor: Seemanjay Ameriya
Copy Editor: Safis Editing
Proofreader: Ayushi Bulani
Indexer: Tejal Soni
Production Designer: Nilesh Mohite
Growth Lead: Bhavesh Amin
DevRel Marketing Coordinator: Shruthi Shetty
First published: April 2025
Production reference: 2180425
Published by Packt Publishing Ltd.
Grosvenor House
11 St Paul’s Square
Birmingham
B3 1RB, UK.
ISBN 978-1-83546-974-3
www.packtpub.com
To my mother, Pilar, and to the memory of my father, Nacho, for their constant support and for teaching me the value of hard work and dedication.
– Pablo Sáenz de Tejada
To the incredible Tableau community, whose passion for discovery and innovation continues to inspire. And to my students and colleagues – your curiosity and dedication make this journey worthwhile.
– Daria Kirilenko
Contributors
About the authors
Pablo Sáenz de Tejada is a Tableau specialist with over 10 years of experience helping people and organizations improve their data and Tableau skills and 20 years of experience as a consultant. He began his journey in data as a market research analyst specializing in survey data and analysis. Between 2015 and 2020, Pablo was part of The Information Lab (UK), helping people make sense of data as a data analyst, consultant, and certified Tableau trainer. In 2020, he became the managing director of The Information Lab (Spain). In 2022, Pablo joined Salesforce as a Tableau lead solution engineer, and then in 2024, he joined Snowflake as a senior partner sales engineer.
I would like to express my gratitude to my co-author, Daria, for her invaluable contributions and support during this journey.
A special thank you to the entire team at Packt for their professionalism and dedication in bringing this book to life.
Lastly, to my partner, Alexandra, thank you for your patience, encouragement, and love.
Contributors
About the authors
Pablo Sáenz de Tejada is a Tableau specialist with over 10 years of experience helping people and organizations improve their data and Tableau skills and 20 years of experience as a consultant. He began his journey in data as a market research analyst specializing in survey data and analysis. Between 2015 and 2020, Pablo was part of The Information Lab (UK), helping people make sense of data as a data analyst, consultant, and certified Tableau trainer. In 2020, he became the managing director of The Information Lab (Spain). In 2022, Pablo joined Salesforce as a Tableau lead solution engineer, and then in 2024, he joined Snowflake as a senior partner sales engineer.
I would like to express my gratitude to my co-author, Daria, for her invaluable contributions and support during this journey.
A special thank you to the entire team at Packt for their professionalism and dedication in bringing this book to life.
Lastly, to my partner, Alexandra, thank you for your patience, encouragement, and love.
Daria Kirilenko is a Tableau consultant with over 8 years of experience in business intelligence and analytics. As the founder of DSCOVR Analytics, she leads a dynamic consulting firm dedicated to delivering data-driven solutions to Fortune 500 companies across North America. In 2022, she led a global Tableau training initiative for Salesforce employees, which was widely recognized for accelerating skill development and driving the adoption of Tableau tools in the organization. Daria also serves as a seasoned instructor at Stanford University, where she conducts regular training sessions and customized workshops. Her approach combines technical depth with real-world applications, enabling participants to transform how they work with data.
This book would not have been possible without the dedication, expertise, and support of many incredible people.
First and foremost, I want to thank my co-author, Pablo. Your expertise, dedication, and willingness to take on the heavy lifting made this collaboration both productive and truly enjoyable. I couldn’t have asked for a better partner in this endeavor and I’m grateful for the opportunity to have worked alongside you.
A heartfelt thank you to our content editors, whose sharp insights and thoughtful feedback elevated this book to its best possible form. Your commitment to accuracy and quality is what makes this book a trusted resource for Tableau professionals.
I’m also deeply grateful to the technical editors who meticulously reviewed each recipe – testing code, verifying results, and making sure each technique was practical and well-explained. Your contributions have strengthened this book in countless ways.
A huge thank you to the product managers who made the authoring process as seamless as possible. Your guidance and support kept us on track and helped bring this project together smoothly.
Lastly, I want to thank my partner, Mitchel, for his unwavering support throughout this journey. From patiently listening to my endless Tableau talk to pretending to be fascinated by calculated fields at dinner, you deserve a medal – or at least a Tableau certificate of achievement.
To everyone who contributed to bringing this book to life, I am incredibly grateful. Your efforts have made this project something I am truly proud to share with the Tableau community.
About the reviewers
Amber Reed has a bachelor’s in healthcare administration from the University of Northwestern Ohio and is currently pursuing a master’s in data and analytics at Franklin University. Her career began in clinical roles within healthcare and pharmacy, transitioning over the past five years to data analytics. Currently, she is a data analyst at CoverMyMeds, a healthcare technology company. Amber’s expertise in analytics focuses on data visualization and storytelling, particularly using Tableau, where she excels in transforming complex datasets into clear, impactful insights.
Eric Summers has been called the Swiss Army knife of data products—able to source data, uncover its value, and build the tools and data products that deliver clear answers. He brings what colleagues call an unfair advantage: a rare mix of technical range, common sense, and the ability to talk with both engineers and business leaders. As the creator of TableauAdmin.com, Eric helps teams simplify how they manage content, metadata, and monitoring in Tableau. Whether building dashboards, automations, or self-service tools, he focuses on delivering work that’s not just useful—but valuable, trusted, and easy to act on.
Join our community on Discord
Join our community’s Discord space for discussions with the authors and other readers:
https://packt.link/ds
Table of Contents
Preface
Part 1: Scaling Tableau Capabilities – Data, Security, and Cloud Excellence
1
Building Advanced and Efficient Tableau Data Models
Technical requirements
Building a basic data model
Getting ready
How to do it...
Combining logical and physical layers in our data model
Getting ready
How to do it...
How it works...
See also
Building multi-connection schemas
Getting ready
How to do it...
See also
Optimizing data model relationships
Getting ready
How to do it...
How it works...
Building multi-fact models with Tableau 2025.1
Getting ready
How to do it...
How it works...
See also
Crafting data models with Tableau Desktop versus Tableau Prep
How to do it...
How it works...
Join our community on Discord
2
Managing Content and Security at Scale in Tableau
Technical requirements
Understanding governance and permissions best practices
Getting ready
How to do it...
Setting up row-level security (RLS) the traditional way
Getting ready
How to do it...
How it works…
Setting up centralized RLS with virtual connections
Getting ready
How to do it...
There’s more…
Configuring column-level security
Getting ready
How to do it...
There’s more…
See also
Managing content description, metadata, and data labels with Tableau Data Management
How to do it…
Using data lineage and data catalog to perform impact analysis
Getting ready
How to do it...
Join our community on Discord
3
Leveraging Tableau Cloud’s Data Management and Advanced Management
Technical requirements
Using the CMT to back up content at scale
Getting ready
How to do it...
How it works…
Scheduling a migration plan to execute it periodically
Getting ready
How to do it...
Updating data source connection details at scale to change from development to production databases
Getting ready
How to do it...
How it works...
Keeping data curated and user’s trust – data certification and quality warnings with Data Management
Getting ready
How to do it...
There’s more...
Adding column description metadata from a data dictionary to Tableau Cloud with the Metadata API
Getting ready
How to do it...
How it works…
Centralizing data access with virtual connections
Getting ready
How to do it...
How it works…
Part 2: Optimizing Performance and Mastering Complex Calculations
4
Maximizing Workbook Performance
Technical requirements
Getting started with workbook optimization
How to do it...
There’s more…
Enhancing performance through data source and modeling strategies
Getting ready
How to optimize extracts
How to optimize live connections
There’s more…
See also
Reducing complexity and rendering time
How to do it...
Optimizing filter performance
Getting ready
How to do it...
How it works...
There’s more…
See also
Improving calculation efficiency
Getting ready
How to do it...
There’s more…
See also
Designing efficient workbooks
Getting ready
How to do it…
Utilizing the Workbook Optimizer
Getting ready
How to do it…
How it works…
There’s more…
See also
Using View Acceleration
Getting ready
How to do it…
How it works...
See also
Assessing workbook performance
Getting ready
How to do it…
How it works...
There’s more…
Join our community on Discord
5
Mastering Advanced Calculations to Answer Complex Business Questions
Technical requirements
Understanding Tableau’s Calculation Framework
How it works…
See also
Explore Table Calculation uses and learn to optimize their scope
Getting ready
How to do it…
There’s more…
See also
Create advanced table calculations
Getting ready
How to do it…
How it works…
Implement LOD expressions
Getting ready
How to do it…
How it works…
See also
Working with and creating advanced LOD expressions
Getting ready
How to do it…
How it works
There’s more…
Manipulate Processing using Tableau’s Order of Operations
Getting ready
How to do it…
How it works…
See also
Join our community on Discord
Part 3: Building Interactive Dashboards and Data Apps
6
Creating Interactive Dashboards
Technical requirements
Adding interactivity with Tableau actions
How to do it…
See also
Setting up filter actions to navigate through dashboards
Getting ready
How to do it…
How it works...
There’s more…
Creating an interactive temporal analysis with parameter actions
Getting ready
How to do it…
How it works...
There’s more...
Creating interactive sheets to switch between different measures with parameter actions
Getting ready
How to do it...
How it works...
Building part-to-whole and proportional brushing analysis with set actions
Getting ready
How to do it…
How it works…
See also
Analyzing different levels of granularity with sets and set actions
Getting ready
How to do it...
How it works...
There’s more…
Join our community on Discord
7
Interactivity and Zone Visibility: From Dashboards to Data Apps
Technical requirements
Understanding containers and why they are important
Getting ready
How to do it...
See also
Showing and hiding content based on a basic parameter with DZV
Getting ready
How to do it...
How it works...
See also
Showing content only to specific users and groups with DZV
Getting ready
How to do it…
How it works…
Advanced guided analytics with parameter actions
Getting ready
How to do it…
Advanced guided analytics with DZV
Getting ready
How to do it...
How it works…
See also
Join our community on Discord
Part 4: Exploring Geospatial Solutions, Developer Tools, and Design Best Practices
8
Advanced Geospatial and Mapping Use Cases
Technical requirements
Performing geographic analysis using Shapefile data in Tableau
Getting ready
How to do it…
Building advanced data models with spatial joins
Getting ready
How to do it…
Adding multiple layers in Tableau’s maps
Getting ready
How to do it…
There’s more…
Using store and customer location data to run a store influence area analysis
Getting ready
How to do it…
Using spatial data and clustering to find optimal store locations
Getting ready
How to do it…
9
Extending Tableau with Developer Tools and APIs
Technical requirements
Authenticating in Tableau’s REST API
Getting ready
How to do it...
See also
Updating a user’s role using the REST API
Getting ready
How to do it...
How it works...
There’s more...
See also
Updating a workbook’s owner using the REST API
Getting ready
How to do it...
See also
Using your data sources beyond visualizations with VizQL Data Service (VDS)
Getting ready
How to do it...
There’s more...
See also
Geocoding addresses with TabPy, Tableau Prep, and openrouteservice
Getting ready
How to do it…
Finding all workbooks connected to a specific database with the Metadata API and GraphQL
Getting ready
How to do it...
There’s more...
See also
Using the Metadata API and GraphQL to document calculated fields created by your users
Getting ready
How to do it...
How it works...
Join our community on Discord
10
Core Techniques for Impactful Data Design
Design for your audience
How to do it…
There’s more…
See also
Use color with purpose
How to do it…
See also
Choose correct chart types
How to do it…
See also
Develop a style guide
How to do it…
See also
Enhance your tables
Getting ready
How to do it…
There’s more…
See also
Join our community on Discord
Index
Other Books You May Enjoy
Part 1: Scaling Tableau Capabilities – Data, Security, and Cloud Excellence
In this part, you will learn how to build well-structured data models, manage content and security at scale, and strengthen governance with Tableau Cloud’s specialized tools, such as the Content Migration Tool and the Metadata API. You will also explore how to secure and simplify data connectivity with virtual connections to ensure that users can access the information they need with confidence.
This part includes the following chapters:
Chapter 1, Building Advanced and Efficient Tableau Data ModelsChapter 2, Managing Content and Security at Scale in TableauChapter 3, Leveraging Tableau Cloud’s Data Management and Advanced Management1
Building Advanced and Efficient Tableau Data Models
It might sound obvious, but without data, there is not much we might be able to analyze and visualize in Tableau. To start using data in Tableau, first, we need to create a data source.
Each data source has a data model. A data model is a diagram needed before starting an analysis to allow Tableau to understand what data we want to use, any specific adjustments we might want to make to the fields, such as renaming or changing the data type of any of them, and, if we want to use more than one table at the same time, how the different tables are related in order to query the data correctly and get meaningful results.
It’s common not to spend too much time on the data modeling and data source creation phase and jump to the analysis and visualization as quickly as possible. But creating a good data model is key, especially if we have different tables that we need to combine and use together.
Additionally, it can save us a lot of time if we find out afterward that something was not correct initially and a good data model can improve our dashboard and query performance in more complex scenarios.
In this chapter, we’re going to cover the following recipes:
Building a basic data modelCombining logical and physical layers in our data modelBuilding multi-connection schemasOptimizing data model relationshipsBuilding multi-fact models with Tableau 2025.1Crafting data models with Tableau Desktop versus Tableau PrepTechnical requirements
For this chapter, we will use six different tables available on GitHub at https://github.com/PacktPublishing/Tableau-Cookbook-for-Experienced-Professionals/tree/main/Chapter_01.
For this chapter, all the images and examples will be provided using Tableau Desktop Public Edition, version 2024.3. Everything we will cover can also be done through the web interface of Tableau Cloud or Tableau Server, but we recommend using Tableau Public Edition or Tableau Desktop to follow along.
Note
The look and feel of the Data Source page are a bit different in Tableau’s most recent version, 2025.1. Additionally, one of the topics covered in the chapter – multi-fact data models – has only been supported since version 2024.2. For this reason, we recommend updating your version of Tableau Desktop or installing Tableau Public Edition version 2024.2 or higher for this chapter. If you can’t have the latest version, keep in mind that some images might look slightly different from what you see in your Tableau version.
Additionally, for the last recipe, Crafting data models with Tableau Desktop versus Tableau Prep, you will need to have a full Tableau Creator license to be able to use Tableau Prep Builder or Tableau Prep in the web browser.
Building a basic data model
Data models can be very simple (a single table) or more complex (dozens of tables). For this recipe, we will start with a simple data model relating two different tables together to better understand the data source page and how to create relationships and what they are.
There are two main ways to create a data model:
Using Tableau Desktop or Tableau Desktop Public EditionUsing the web interface from Tableau Cloud or Tableau ServerIn this recipe, we will use the first option, but you can follow along using the web interface if you prefer. To create a workbook and connect to new data from the Tableau Cloud or Tableau Server web interface, check the first two steps in this guide if you don’t know how to do it: https://help.tableau.com/current/pro/desktop/en-us/getstarted_web_authoring.htm.
Getting ready
You can download the tables we will be using for this recipe in the GitHub repository shared in the Technical requirements section at the beginning of this chapter.
More specifically, we will use these files:
ch1_visits.csv: This table has a row for each visit in a hospital, with fields specifying the ID of the visit, the date the visit took place, the patient ID and doctor ID for the visit, and the duration of the visit in minutesch1_patients.csv: This table has a row for each patient, with fields specifying the patient ID, gender, blood type, and date of birth (DOB) of each patientLet’s start by opening the ch1_visits.csv file in Tableau Desktop or Tableau Public Edition.
How to do it...
Building your first data model is easy. Let’s explore the main areas of the Tableau user interface when we connect to data and create a data model.
To connect to the visits table, open Tableau and click on Text file in the Connect panel on the left side, as shown in Figure 1.1.Figure 1.1 – Connecting to a CSV file
Then, select the ch1_visits.csv file that you downloaded from the GitHub repository and click Open.If you have connected to the CSV file correctly, you should see the Data Source page.
The Data Source page has four main sections:
The left pane shows the connection or connections we have set up in the workbook and the different files or tables available.Figure 1.2 – The left pane of the Data Source page
The canvas shows the logical layer of the data model, the tables added to it, and the relationships between them.Figure 1.3 – The canvas of the Data Source page
The data grid shows the first rows of the table selected in the canvas.Figure 1.4 – The data grid of the Data Source page
The metadata grid shows a list of the fields in the table selected on the canvas and their main properties.Figure 1.5 – The metadata grid of the Data Source page
Now that we better understand the Data Source page, let’s create our first model combining the two tables mentioned before.
Drag the ch1_patients.csv file from the left pane to the canvas next to the ch1_visits table until you see a node connecting both tables. Drop the patients table there, as shown in Figure 1.6.Figure 1.6 – Adding the second table to our data model
Click on the node or line connecting both tables. At the bottom, you can configure the relationship between both tables. In our case, the relationship between them is the Patient ID field from each table. Review that the relationship is correct.Figure 1.7 – Checking the relationship between the visits and patients tables
There are a few more things we can adjust in our data model. At the top of the canvas, you should see a database icon with the default name that Tableau assigns to our data source. In our case, it is ch1_visits+. Click on the name to rename it to something more meaningful such as Hospital visits and patients.Additionally, right-click in each of the rectangles representing the tables in the canvas and click on rename to rename ch1_visits.csv to Visits and ch1_patients.csv to Patients. Your data model should look as shown in Figure 1.8.Figure 1.8 – Final stage of our data model after renaming the data source and tables
You have just created a data model with a relationship between two tables!
There are several more things you can do from the Data Source page, including the following:
Add a data source filter from the top-right Add Filters button. Adding a data source filter means less data will be available in workbook. Think about data source filters as a WHERE clause in your SQL query if you are more familiar with SQL.Change the field names and types from the data or metadata grids.Include additional actions for specific columns, from hiding them to changing aliases, creating groups, and so on by right-clicking on each field in the data grid.For now, we have done everything we need for our first model. You can now go to the Sheet 1 tab at the bottom and start your analysis. Let’s dig into the data model a bit further to understand the differences between the logical layer and the physical layer in the next recipe.
Note
Tableau has a data type for geographic information. Relationships can’t be defined based on geographic fields so it’s important to have this in mind when creating your data model. We will see different ways to handle geographic data in Chapter 8.
Combining logical and physical layers in our data model
In the previous recipe, we related two tables using what we called the logical layer, but Tableau’s data model allows us to use two different layers:
The logical layer is the default view in the Data Source page. When you add tables to your model, those are related through relationships, which have been the default way of combining tables in our data model since Tableau version 2020.2.The physical layer allows us to combine tables using traditional joins and unions. Before 2020.2, this was the only layer available. Each logical layer can contain one or more physical tables.Figure 1.9 – Tableau’s data model
The main benefit of having a logical and physical layer is the higher flexibility to build data models based on your needs.
In most scenarios, working with the logical layer should be enough, but if you need to perform traditional joins or unions, you’ll need to do them in the physical layer. How can we then use the physical layer of each table?
Getting ready
We will add more data to the model built in the previous recipe. The Visits table also has a Doctor ID field that we would like to combine with the ch1_doctors table. And the ch1_doctors table is also related to the ch1_hospitals and ch1_specialties tables.
What we are going to do is add the Doctors table as another logical table related to the Visits table based on the Doctor ID field in the same way we add the Patients table in the previous recipe.
From the Data Source page, drag and drop the ch1_doctors.csv file from the left pane to the canvas, making sure it’s connected to the Visits table. Review the relationship to make sure it’s based on the Doctor ID field of both tables, as in Figure 1.10. Feel free to also update the name of the ch1_doctors.csv logical table to Doctors.
Figure 1.10 – Adding the Doctors table to the data model as a third logical table
How to do it...
Now, let’s access the physical layer of the Doctors table to add the hospitals and specialties files as traditional joins:
Double-click the Doctors table in the canvas, or right-click on it and select Open to access the physical layer of the Doctors logical table. You should then see in the canvas the Doctors table’s physical layer, as in Figure 1.11.Figure 1.11 – The physical layer of the Doctors table
Drag the ch1_hospitals.csv file from the left pane and drop it next to the ch1_doctors.csv table. You’ll see that this time, there’s no relationship shown before dropping the table. By default, Tableau will create an inner join between both tables. Click on the Venn diagram to check the type of joins available and the field used in the join, as in Figure 1.12.Figure 1.12 – Configuring the join in the physical layer
Make sure the relationship between both tables is based on the Hospital ID field and change the join to a left join. Close the join window with the Venn diagram.Repeat Steps 2 and 3 but with the ch1_specialties.csv table. The join between ch1_doctors.csv and ch1_specialties.csv should be based on the Specialty ID fields of each table. The physical layer of the Doctors table should now look like Figure 1.13.Figure 1.13 – Final state of the physical layer of the Doctors table
Close the physical layer of the Doctors table by clicking on the X in the top-right corner. Your updated data model should look like Figure 1.14.Figure 1.14 – The end state of our data model combining logical and physical layers
How it works...
With our data model ready, we could now start our analysis. The best thing about it is that for end users, there is no difference at all when analyzing data from different tables, no matter whether they are related in the logical layer or the physical layer.
The combination of the logical layer and the physical layer allows you to build different data model schemas, from a single table to star schemas and snowflake schemas.
Use the one that best fits your needs for your analysis, but it’s always a good idea to think about the best way to organize your data model.
Note
When creating relationships or joins, it’s important to remember that the data types of the fields used must match. One of the most common errors when creating data models is not paying attention to the data types of the fields being used. Make sure to keep an eye on the data types when configuring your relationships.
See also
For more information about star and snowflake schemas in Tableau, check this page from Tableau’s documentation: https://help.tableau.com/current/pro/desktop/en-us/datasource_datamodel.htm#star-and-snowflakeFor a detailed explanation of the differences between relationships and traditional joins, read this article: https://help.tableau.com/current/pro/desktop/en-us/datasource_relationships_learnmorepage.htmBuilding multi-connection schemas
So far, we have seen how to build data models and data sources using one single data connection type. Our example has been with CSV files, but the same logic will apply when using data from Snowflake, Google BigQuery, Amazon Redshift, Azure SQL Database, or any other connection type.
In a lot of use cases, data might not be in the same database type or in the same file type. Very frequently, analysts need to combine data from a database with data from a CSV file; data from an Excel file with data from a CSV file; or even data from Snowflake with data from Amazon Redshift for instance.
How can we create a data model in Tableau that combines different connection types? Learning how we can build multi-connection or cross-database schemas is the main objective of this recipe.
Getting ready
For this exercise, I’ll use some of the datasets we have used already during this chapter. If you don’t have them, please go to the beginning of the chapter to find the link from which you can download the data. In my case, I’ll also use a Snowflake database, but you could do the same with any database you might use or even save a copy of the Doctors and Hospital tables as Excel files instead of CSV.
In my case, I’ll use the Visits table and combine it with Doctors and Hospital located in a Snowflake database.
If you don’t have access to a database, you can make a copy of the Doctors and Hospital tables and save them as .xslx files to have different data types and build your own multi-connection data source.
How to do it...
The process is very simple, but a lot of Tableau users don’t know how to do it because they might have missed where they can add several connection types to a data source:
First, let’s start by connecting to our visits.csv file as the first table of our data model. Open Tableau Desktop or Tableau Public, go to Connect | Text File, and select the visits.csv file provided at the beginning of the chapter.In the Data Source window, look at the top of the left panel where it says Connections and click on the Add button next to it, as shown in Figure 1.15.Figure 1.15 – Adding a second connection to our data model
Select the new connection you want to add to your data model. As mentioned earlier, I’ll use a Snowflake database, but you can use a copy of the Doctors table saved as an Excel file. In that case, select Microsoft Excel as your new connection. Then, find the Doctors file or the Doctors table in the database you want to use if you have access to one.Now, you should see two different connections in the left pane, one with a blue line and the second one with an orange line, as in Figure 1.16. Click on each to show the files or tables available in each connection.Figure 1.16 – Working with data from multiple connections on the left pane
Drag and drop the Doctors table from the second connection from the left panel to the canvas and build the relationship as we have already learned in this chapter based on the Doctor ID fields.Congratulations! You have just built a data model with a different connection type, also called a multi-connection or cross-database model. This can be very useful in scenarios where users need to combine their curated company data with some local Excel files to enrich the analysis or when data lives in different databases or is stored in different file types.
Note
When creating data models with cross-database joins, it’s important to keep in mind that performance might be affected. For each connection in a cross-database join, Tableau sends queries independently for each data source and stores the results in a temporary table. This means that during your analysis, the speed of your results will depend on the slowest source used.
See also
Multi-connection or cross-database models have some limitations and considerations to keep in mind when combined with joins. Have a look at the documentation to improve performance for cross-database joins: https://help.tableau.com/current/pro/desktop/en-us/joins_xbd_perf.htm.
Optimizing data model relationships
In many scenarios, when creating relationships between tables, you’ll not need to do anything other than specify the related fields between them. This will guarantee you get meaningful results and, in general, a good performance.
Sometimes, with complex data models with a lot of different tables or big datasets and complex relationships, you might want to adjust how the tables are related to let Tableau know specifically how to perform those relationships and improve query performance and overall speed.
Tableau allows us to do this by manually setting the cardinality and referential integrity of each table in a relationship. It is important to keep in mind that setting these options incorrectly can also translate into wrong results during your analysis.
A best practice is to only adjust the following settings when you have a deep understanding of your data, especially of the fields you are using to relate your tables.
Getting ready
Before learning how we can set the tables’ cardinality and referential integrity, let’s understand what those options mean and what they are used for.
Cardinality between tables refers to whether a row from one table could be related to more than one row from another table. You can set up the cardinality for each table you are using in a relationship by selecting one of the two options available: Many or One. Because cardinality can be set up for each table in a relationship, this will allow you to have four possible scenarios for a relationship between two tables: one-to-one, one-to-many, many-to-one, or many-to-many. Let’s understand what each scenario means:
A one-to-one cardinality means each value from the related field in one table is related to no more than one value of the shared field in the related table. For example, imagine you have the following tables:Table A, with information about different hospitals in your city. Each row represents a hospital and there are no duplicate rows. The table has two columns: Hospital ID and Hospital Name.Table B, with details of those hospitals. Again, each row represents a hospital and there are no duplicate rows. The table has the following columns: Hospital ID,Address, Number of rooms, and Number of patients attended during the current year.Because the Hospital ID value is unique in each table, we could specify one has the cardinality of each table in the data model, creating a one-to-one cardinality between the tables. Because we know both tables have unique values, a Hospital ID value in Table A can only appear once in Table B, and vice versa.
The one-to-many and many-to-one scenarios are similar, and they just depend on where in the data model each table is. These configurations might improve performance in scenarios where one of the tables has unique values for the shared field, and the other table can have multiple rows for the same value in the related table. Let’s see an example:Table A is the same as in the previous scenario: hospitals in your city. Each row represents a hospital and there are no duplicate values. The fields in the table are Hospital ID and Hospital Name.The second table, Table C, has information about doctors in those hospitals. Each row represents a doctor, and the fields available are Doctor Name, Specialty, Main Hospital ID, Age, and Years Working at the Hospital.Both tables are related by Hospital ID. However, because several doctors work in the same hospital, the same Hospital ID value will appear several times on the Table C, the Doctors table. This is a one-to-many cardinality between Table A and Table C, and setting up the cardinality accordingly might improve query processing and performance.
Finally, we have the many-to-many scenario. This is also the default configuration Tableau defines for every relationship if we don’t update the cardinality configuration. In this case, multiple values of the shared field of one table can be related to multiple values in the other table. Here is an example:Table D has information about patient visits. Each row represents a visit from a patient to a doctor. The fields available are Visit ID, Visit Date, Doctor ID, and Patient ID.Table E has prescription information. Each row represents a medical prescription made by a doctor to a patient during a visit. The fields available are Visit ID, Product ID, and Product Name.The relationship between both tables is the Visit ID value. But because, in a visit, a doctor can prescribe multiple products and a patient can see more than one doctor and specialist during the same visit, the cardinality between tables is many-to-many.
A related concept to cardinality in data models is referential integrity, which is used to specify whether every value in the shared field of one table has a matching value in the other table.
This configuration setting has only two options for each table in our data model: Some records match or All records match.
The naming is quite self-explanatory but let’s see an example to better understand both options.
Imagine the last scenario we explained: Table D has information about patient visits, and Table E has prescription information. Every single prescription should have a Visit ID value (if there are no data quality issues) because each prescription in a hospital visit has to be done by a doctor during a medical appointment. On the contrary, not every patient who visits a doctor receives a medical prescription so some Visit ID values will appear in Table D but not in Table E.
In this scenario, in Table D, our visits table, referential integrity should be Some records match and in Table E, our prescription table, it should be All records match.
