How Does Tableau Connect to a Database?

Connecting Tableau to a live database is the first step toward transforming raw numbers into clear, actionable insights for your business. This direct line to your data powers the real-time dashboards and reports that help you make smarter decisions. This article will guide you through exactly how Tableau connects to a database, explaining the different connection types and providing a step-by-step walkthrough.

Understanding Tableau's Data Connectors

Think of a data connector as a specialized translator. It's a piece of software that understands the language of both Tableau and your specific database, allowing them to communicate effectively. Tableau comes with a huge library of these native connectors built right in, designed to seamlessly link to dozens of the most common data sources you use every day.

This means you can connect directly to:

• Relational Databases: Such as Microsoft SQL Server, PostgreSQL, MySQL, and Oracle.
• Cloud Data Warehouses: Services like Amazon Redshift, Google BigQuery, and Snowflake are fully supported.
• File-Based Data: You're not limited to servers. You can easily connect to Microsoft Excel files, CSV files, JSON files, and PDFs.
• Cloud Applications: Tableau can also pull data directly from platforms like Salesforce, Google Analytics, and Marketo, letting you analyze your marketing, sales, and web performance data.

In the rare case that Tableau doesn't have a native connector for your specific database, it provides generic ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) drivers. These act as universal translators, allowing you to connect to almost any database that follows these standard protocols, though they may require a bit more configuration than a native connector.

The Two Core Connection Types: Live vs. Extract

Once you've chosen a connector, you have a crucial decision to make: should you use a Live connection or an Extract? This choice fundamentally impacts your dashboard's performance, data freshness, and the load on your database. Neither is universally "better" - the right one depends entirely on your specific needs.

Live Connection

A live connection means Tableau queries your database directly every time you interact with a dashboard. When you filter, sort, or change a date range, Tableau sends a new query to the source database and waits for the results before updating the visualization.

Pros:

• Real-Time Data: Your dashboards always reflect the most current information in the database. This is essential for operational monitoring dashboards where split-second accuracy matters.
• No Data Duplication: You aren't creating a separate copy of your data, which can be important for data governance and saving disk space.

Cons:

• Performance is Database-Dependent: A slow or overloaded database will result in a slow, frustrating dashboard experience for your users.
• Can Strain the Database: A popular dashboard with many users can generate thousands of queries, potentially impacting the performance of the source database, which might also be used for other critical business operations.

When to use a Live connection: Use a live connection when you absolutely need up-to-the-second data and you're connecting to a high-performance analytical database (like Snowflake, BigQuery, or a well-optimized SQL Server) that can handle the query load without breaking a sweat.

Extract Connection

An extract connection takes a completely different approach. Instead of querying your database live, Tableau takes a "snapshot" of your data and pulls it into its own proprietary, high-performance TDE (Tableau Data Extract) or .hyper file. The dashboard then queries this hyper-optimized file instead of the live database.

Pros:

• Blazing-Fast Performance: Tableau's .hyper engine is designed for rapid analytics. Complex dashboards that would take minutes to load with a live connection can often load in seconds with an extract.
• Reduced Database Load: Since Tableau is querying its own local file, it's not putting any strain on your production database after the initial extract is created.
• Portability and Offline Access: Since the data is stored within your Tableau workbook, you can analyze it and interact with your dashboard even without an active internet connection to the database.

Cons:

• Data Isn't Real-Time: The data in your dashboard is only as up-to-date as your last extract refresh. You can schedule these refreshes to happen as frequently as every 15 minutes with Tableau Server/Cloud, but it will never be truly "live."
• Requires Storage Space: Extracts can be quite large, especially with massive datasets, consuming server disk space.

When to use an Extract connection: Extracts are the default choice for most scenarios. If your underlying database is slow, if you have a large dataset, or if near-real-time data (e.g., updated every hour or every night) is perfectly acceptable, an extract will provide a far superior user experience.

Step-by-Step Guide: How to Connect to a SQL Database

Let's walk through the exact steps of connecting Tableau to a common relational database like PostgreSQL. The process is very similar for other SQL databases like MySQL or SQL Server.

Step 1: Open the Connect Pane

When you first open Tableau Desktop, you're greeted with the Connect Pane. This is your hub for connecting to all your data sources. On the left side, you'll see a list titled "Connect" with options to connect "To a File" or "To a Server."

Step 2: Select Your Database Connector

Under the "To a Server" section, you’ll find an alphabetical list of common database-as-a-service providers and database types. Click the "More…" link to see the full list of connectors. Find and click on PostgreSQL.

Step 3: Enter Your Connection Details

A dialog box will appear asking for the server's connection information. You will need to get this from your database administrator, development team, or cloud provider.

• Sign In: Click the "Sign In" button at the bottom of the form after entering the connection info.
• Server: This is the hostname or IP address of your database server (e.g., prod-db.us-east-1.rds.amazonaws.com or 172.16.0.10).
• Port: The specific network port the database is listening on. The default for PostgreSQL is 5432.
• Password: Enter the password associated with this database user.
• Database: The name of the specific database you want to connect to on the server.
• Username: The username for authentication.

Step 4: Design Your Data Model on the Data Source Page

After successfully connecting, Tableau will take you to the Data Source page. Here, you define what data you want to work with.

1. Select a Schema: Use the dropdown to choose the correct schema within your database. A list of all available tables in that schema will appear in the left-hand panel.
2. Drag and Drop Tables: Drag the tables you need from the left panel onto the canvas in the center. For example, you might pull over an Orders table and a Customers table.
3. Configure Joins: Tableau will automatically try to create a join between the tables if it recognizes a common field name (like CustomerID). You can click on the join icon (the overlapping circles) to change the join type (Inner, Left, Right, Full Outer) or the fields being used to create the relationship. This is how you tell Tableau how your tables relate to one another.

Step 5: Choose a Live or Extract Connection

In the top right corner of the Data Source page, you'll see the Connection options. This is where you select either "Live" or "Extract." If you choose "Extract," you'll have the option to add filters to reduce the amount of data you're pulling in before you create the extract.

Step 6: Go to a Worksheet

Once you've set up your data model, click on the "Sheet 1" tab at the bottom of the screen. Tableau will now be ready for you to start building visualizations! If you selected an extract connection, it may take a few moments to import the data. Once complete, all the fields from your selected tables will be available in the Data Pane on the left side of your worksheet, ready to be dragged and dropped to create compelling charts and graphs.

Best Practices for Reliable Connections

Following a few best practices can save you major headaches and ensure your dashboards are fast, accurate, and efficient.

• Minimize your data: Don’t pull in an entire 10-million-row table if you only need the last month’s data. Use Data Source filters in Tableau to bring in only the columns and rows required for your analysis. This drastically improves performance for both live and extract connections.
• Simplify your joins: The more complex your joins are, the slower your queries will be. Whenever possible, collaborate with your data team to create simplified views or materialized tables in the database itself. A single, optimized table is always faster to query than a fly-join of five large ones.
• Use Extracts by default: Unless you have a specific, justifiable need for real-time data, start with an extract. The performance gains are almost always worth the slight data latency. Schedule overnight or hourly refreshes to keep the data reasonably up-to-date.
• Use Custom SQL with caution: Tableau allows you to write your own custom SQL queries instead of using the visual join builder. While this offers immense flexibility, be aware that a poorly written query can be much less performant than the queries Tableau generates on its own. Only use this if you know your SQL skills are sharp.

Final Thoughts

Connecting your data is the foundational skill for all analysis in Tableau. By understanding the wide array of available connectors and, most importantly, the strategic difference between live connections and extracts, you can build dashboards that are not only insightful but also fast, stable, and efficient for your users.

Of course, even with a powerful tool like Tableau, there's often still a significant amount of setup time involved in connecting sources and manually building out each report. We built Graphed to simplify this entire process. Instead of navigating connection menus and drag-and-drop report builders, you can securely link your data sources - like databases, Google Analytics, or Salesforce - and then build new dashboards and charts just by describing what you need in plain English. This allows your team to get from data to decision in seconds, not hours.