Tableau Doctor: Map Your Database for Easier Navigation

Note: This is an occasional series that provides answers to some of the most commonly-asked questions to Tableau Doctor.

Every week, we hold an internal Tableau Doctor session that anyone in the company can come to with questions. As a volunteer Tableau Doctor, I’ve noticed that many users come to these sessions facing the same roadblock: navigating and understanding our databases. The most successful solution has been to enable users with data sources using Tableau Data Server.

While this works for many users, I still find that people ask unique questions of their data that require slight changes to the models. In these scenarios, there are two questions that continuously arise:

1. Which tables are available and what data do they contain?
2. How do these tables connect with one another?

Tired of not having an efficient way to navigate our database to help answer these questions, I began researching the topic of visualizing databases. Databases are populated with many large tables of tabular data, making them problematic to conceptualize. The ability to see and understand the data within a database easily is crucial to beginning any analysis. It turns out there are many database administrative tools that map out data models, but mapping hundreds of tables in a database tends to be a mess. Plus they are generally not available to the end users.

So I set out to make a tool for quickly answering the above questions. In this example, I have used a small database of sales data with seven tables to demonstrate how to create a similar tool.

First I wanted to select a primary table to see all available columns. The solution turned out to be fairly easy. If you are using a brand of SQL, you can write a simple query to pull all of the tables and associated columns within those tables:

SELECT table_name, column_name FROM information_schema.columns

I ran this from the custom SQL dialog to get it directly in Tableau. Using a dashboard and an action filter, I can now view all of the available tables and the data that lives in each one. This by itself is a useful tool for exploring our databases. You can quickly see all the columns in different tables without having to connect to each table individually.

The second part—selecting a secondary table to see the join clause between the two tables—proved slightly more difficult. This step depends on your database administrator. In order to map out which tables connect to one another and on which fields, we need a data set that is often referred to as a foreign key table.

This is the Rosetta Stone of the database. Unfortunately, this table is not always available due to the manual nature of maintaining it. This data may be stored in the sys.foreign_key_columns table, but it is also possible that your database administrator maintains this somewhere else. Try accessing it using the formula below:

SELECT * FROM sys.foreign_key_columns

Notice that the relationships are only stored in one direction in this table. If we look at the referring tables we know that Orders links to Customer Address, but not vice versa. Using custom SQL to Union the table to itself, the referring and referenced columns can be switched to account for all possible relationships, no matter how you view the data.

SELECT
['foreign key table$'].[Referenced Column] AS [Referenced Column],
['foreign key table$'].[Referenced Table] AS [Referenced Table],
['foreign key table$'].[Referring Column] AS [Referring Column],
['foreign key table$'].[Referring Table] AS [Referring Table]
FROM ['foreign key table$']

UNION

SELECT
['foreign key table$'].[Referring Column] AS [Referenced Column],
['foreign key table$'].[Referring Table] AS [Referenced Table],
['foreign key table$'].[Referenced Column] AS [Referring Column],
['foreign key table$'].[Referenced Table] AS [Referring Table]
FROM ['foreign key table$']

The rest of the tool can be created using actions in a dashboard to link the information from the two tables. I have included additional directions in the workbook to help you reverse-engineer the tool.

Relying on key players to be the masters of our database is not scalable. My goal in creating this tool is to make navigating and understanding a database accessible to more people.

What are your thoughts? Do you have a good solution? I would love to hear your ideas. Do share them in the comments below.

Got a question you’d like Tableau Doctor to address in an upcoming blog? Email us at ideas@tableau.com.