How to Use Tableau Custom SQL Queries for Better Performance
Custom SQL in Tableau allows you to write Structured Query Language (SQL) queries directly within Tableau’s data connection interface. This powerful feature lets you tailor and refine the data you bring into your visualizations with a level of precision not always possible through Tableau Desktop’s standard drag-n-drop options. By writing your own Tableau Custom SQL code, you have the flexibility to join data in specific ways, filter it, or create calculated fields before the data enters Tableau.
Despite the power of custom SQL, you’ll wan to use this feature strategically to maintain the efficiency and performance of your Tableau workbooks. This is one of the 10 reasons SQL is a key skill for a data analyst – so many tools use it! Having some familiarity with the basics of SQL & database structures helps here.
But there are also alternatives, too. The most efficient, simplest solution helps with performance and your ability to maintain what you build!
Key Takeaways
- A custom query allows you to roll up data at the level you need to improve performance.
- Use custom SQL with an understanding of best practices to maintain performance.
- Evaluate alternatives to custom SQL to maximize efficiency and simplicity.
Introduction to Tableau Custom SQL
Tableau has excellent built-in connectors and using the drag & drop relationships and joins take some of the heavy lifting on for you. When the data sources get complex, sometimes you need more flexibility than a standard drag-and-drop can offer, though. That’s where custom SQL comes into play.
Need to do cross-database joins? Internal customers complaining of slow Tableau dashboards? Using SQL instead can be a a good idea.
You simply start by connecting to a data source. You can use Microsoft SQL Server, Oracle, or any database that lets you run SQL queries. Instead of building your data sources out using Tableau’s interface, you write a SQL query. Knowing the basics of SQL is enough to get started.
Here’s what the basic SQL syntax looks like:
SELECT column1, column2
FROM your_table
WHERE your_conditions
Some things to note before you dig in:
- SQL access is required before you can connect to the database & use this functionality.
- You’ll also need to have a bit of info on the database you’re connecting to. Is it SQL Server, ODBC connection, etc? Whoever gives you access or a peer can be a great reference since this is highly organizationally specific.
- You are limited in your data sources with Tableau Public, so don’t count on SQL there.
- There are nuances in how different versions of SQL expect the code to be written. When you get them wrong, it’s called a syntax error. They’re no big deal, but read more about SQL mistakes to watch out for here.
Advantages of Using Custom SQL in Tableau
Using custom SQL in Tableau lets you access specific data directly. This means you have increased control over the data you pull into the dashboard. This is why you can see better performance for your users – you’re pulling in only what you need. Extra rows & columns add up to more that Tableau has to process which can increase the load time for your customers.
Using SQL to connect your Tableau dashboard is particularly helpful in the following scenarios:
- Complex Joins: When the default join options in Tableau are limiting you. Working with multiple tables even across different databases can be easier in with SQL.
- Data Preprocessing: To perform data manipulation steps directly within SQL.
- Performance Optimization: When custom SQL is used to limit the data set to exactly what is needed for analysis which can improve dashboard performance (aka speed). It also reduces network load by processing data on Tableau server before importing it into your dashboards
Sometimes you need to use more complex calculations & transform data in ways that may not be readily available within Tableau’s standard connection screen. The flexibility & precision to get what you need to come back from relational databases is where SQL shines.
- Execute pre-aggregations and window functions for complex statistical analyses.
- Concatenate, parse, and transform text fields more efficiently than Tableau calculations.
- Embed conditional logic within their queries to handle diverse data scenarios.
Creating Custom SQL Queries in Tableau
Custom SQL queries in Tableau allow you to directly manipulate the data connected to the workbook. Want to pull back a certain number of columns or rows? You can reduce the data the Tableau data source looks to through SQL.
If you’re trying to replace an existing data source to improve performance, you’ll want to benchmark performance first. I like to do this by using Tableau’s performance recording.
Changing the data model to SQL CAN make performance worse sometimes. If you’re trying to overcome a complicated transformation, you may deem that worth it. If you’re working on speed issues for your users, then it might now. Make a copy & test.
Step-By-Step Guide: How To Insert a Custom SQL Query in Tableau
Ready to unlock the power of SQL for your data visualization journey? You’ll need the right access to connect to any data warehouse, so be sure you have that information before diving in here.
- Navigate to New Data Source: In Tableau, navigate to the ‘Data‘ menu & then ‘New Data Source‘.
- Connect to the Data Source: Then, choose the data source that supports your version of SQL. This is the type of database that you’re connecting to. Put in your credentials (user info & database connection info).
- Open Custom SQL Dialog: Once connected, double-click the ‘New Custom SQL’ option.
- Enter the Custom SQL: In the dialog box that appears, enter the SQL query & then ‘OK‘
- Test the Query: Assuming you have no syntax errors to work through, validate the query results in the screen at the bottom. Are those records you expect?
- Name the Custom SQL Table: Once validated, click on the arrow to the right of the ‘Custom SQL Query‘ box & select ‘Rename‘. This makes it easier to know what data source you’re working with when you’re building out your visuals.
Tableau’s interface can differ based on the version, so reference the specific documentation if needed.
Tip: The best way to work with the Tableau custom SQL connection? Create and refine your queries outside of Tableau first!
I like to play in another SQL tool to write queries. I find it easier to perfect the query & test the results better. SQL can be tricky so validating the results is key as your visualization is only as good as the data you’re using for it!
Once I’m satisfied that the results coming through as expected, I simply copy & paste into Tableau’s custom SQL dialog box.
Note: Not all things you an do outside of Tableau will copy over simply. For example, you can’t copy temp tables (temporary tables) like you’d build in SQL Server. You’d need to use initial SQL for this which would change how queries get pasted in. That’s more complicated than this blog post needs to go. Initial SQL covered by Tableau here.
Basic Syntax
In Tableau, your custom SQL query should begin with a SELECT
statement, which defines the columns you want to include in your result set. A simple query might look like this:
SELECT column1, column2
FROM table_name
WHERE condition;
Remember to check the data types of the fields you’re selecting to ensure they align with what Tableau expects. Use JOIN
clauses to combine data from multiple tables, and WHERE
clauses to filter your data as needed.
Need to edit the Tableau Custom SQL?
Simply navigate click on the arrow to the right of the ‘Custom SQL Query‘ box & select ‘Edit Custom SQL Query’. The same dialog box where you entered the initial SQL will appear, and you can make whatever changes you need for your data analysis. Click ‘OK‘ & cross your fingers that you don’t have any syntax errors to deal with. 😀
You Can Use Parameters in Tableau’s Custom SQL
Parameters allow users to input values that change the results of visuals. This dynamic value can also change the results of a query without making manual adjustments to underlying SQL code each time. Your dashboard customers can make changes on the fly, as needed, if you insert a parameter into the workbook AND custom SQL statement.
- Define a Parameter: You have to create the parameter before calling it in the ‘Custom SQL Query’ dialog box.
- Incorporate the Parameter within the Query: In the custom SQL query dialog, you reference the parameter using the @ParameterName syntax wherever it’s needed – in the SELECT, WHERE or anywhere else you need it added in. ‘ParameterName’ is the name you gave the Parameter when you created it.
That’s it! Test it out by changing the parameter value & see how the information changes to create an interactive experience for your dashboard customers!
Troubleshooting
For troubleshooting the connection to your database tables, consider the following steps:
- Verify syntax: Make sure your SQL syntax is right for your database. Syntax discrepancies between different SQL dialects could be the culprit.
- Test in segments: Break down your query into smaller parts and test each segment independently. This lets you isolate the error source.
- Parameter checks: When using parameters within your queries, verify that they are functioning as intended. Incorrect parameters can lead to unexpected results or errors. Sometimes I’ll take these out in favor of a constant value when troubleshooting Tableau custom SQL.
- Examine joins: If your query involves joins, make sure the keys and join conditions have the same data type of a field across sources.
- Review calculations: Mistakes in expressions or calculations can also trigger errors. Double-check any arithmetic or logical operations.
Following these steps and use the errors as clues (even if they’re hard to decipher) to systematically fix error messages in the Tableau custom SQL.
Optimizing Query Performance
Tableau custom SQL can make working with large datasets faster, but that’s not necessarily true. Complex joins, subqueries, & other SQL in Tableau can cause your customers a slow-down each time the information is accessed.
1. Simplify Joins: Use inner over outer joins where possible & filter before joining across different data sources.
2. Leverage Database Functions: Built-in functions & stored procedures in the underlying database are usually optimized for performance
3. Avoid unnecessary subqueries: If a subquery is not essential, removing it can lead to faster execution times.
5. Limit Data: Use only what you need for the dashboard you’re building. Extra columns are rows = more time to process. The more extra you include just compounds slowness depending on the size of your data.
6. Don’t overuse Tableau’s custom SQL: Native data connections are built for optimization. Mostly. So use the custom SQL option when there is something that is too messy or impossible to do another way.
7. Summarize in the query: Aggregations or summaries of the data can be handled in SQL, so do it! You’re already using SQL, so lean on it to do the work that Tableau will add time to process if you can work with the data aggregated instead of relying on calculations.
Applying these optimization techniques can lead to more efficient and faster-performing Tableau dashboards, yielding a smoother experience for the end user you’re building for.
These query optimizations are more than just a Tableau conversation. Read more about mistakes to avoid in SQL here.
Tableau’s custom SQL has its limitations, such as performance impacts, maintenance complexity, and limited usage for non-technical users. These constraints should be weighed carefully against the need for complex data manipulation within Tableau.
Alternatives to Custom SQL in Tableau
There are different ways to approach how to manipulate and prepare data without using Tableau custom SQL. These methods leverage Tableau’s own capabilities, external tools, and decision-making based on specific needs and constraints. Unless done well, the engine that fuels the native data connections is going to perform better.
Built-In Tableau Functions: USE Tableau’s Native Capabilities for Simpler Operations
Tableau offers a variety of built-in functions that can replace many common operations. You can perform calculations, string manipulations, and data aggregations directly within the software without using Tableau custom SQL. For example:
- Aggregation Functions:
SUM()
,AVG()
,MIN()
,MAX()
- Calculations: Use Tableau’s calculation editor to create custom fields.
- Parameters: Control data inputs and visualizations interactively.
- Groups and Sets: Organize data categories and subsets for analysis.
- String Functions:
LEFT()
,RIGHT()
,LEN()
,TRIM()
These functions can be dragged and dropped into calculations, making data transformation processes both intuitive and efficient. This built-in functionality the native connection easier to lean on to access your raw data.
Integration with Other Tools: Preprocessing Data
Sometimes, it’s preferable to prepare data before it reaches Tableau. External ETL (Extract, Transform, Load) tools can be employed for more complex data preparation tasks. Tools like Alteryx, Talend, or Informatica allow for extensive data transformation and cleaning. Here, data is often transformed and made ready for analysis in a staging area, which can lead to better performance in Tableau:
- Alteryx can handle spatial data and predictive analytics.
- Talend is useful for integrating data from various sources.
- Informatica excels in enterprise data integration and management.
Once processed, the data can be connected to Tableau in an optimized format, which may enhance responsiveness and interactivity.
When to Use Tableau Custom SQL vs Built-in Options
The decision to use built-in features versus Custom SQL often depends on:
- Complexity: Simpler transformations are efficiently handled by Tableau’s native functions.
- Performance: Tableau may perform better with its built-in capabilities, especially for large datasets. Test to be certain.
- Data Source Limitations: Some data sources do not support custom SQL, making native functions the primary option.
- Tableau Limitations: You can’t use sub-selects, UNIONS, or ON clauses for filtering in Tableau, so custom SQL would be required in these instances.
For instance, if using a file-based source like Excel where custom SQL isn’t possible, Tableau’s native pivot and split functionalities become essential over adding calculations to a large Excel file. Conversely, with a large complex database, preprocessed data through ETL tools can limit your need to work with SQL queries within Tableau.
Fast Track to Learning SQL
Every step you take to grow as an analyst like learning SQL to use in Tableau or before you loaded data into Tableau is a HUGE win! It will pay dividends throughout your career. That’s why it’s a key part of the path I recommend to become a data analyst.
Mastery of SQL doesn’t come easy, but I’ve turned the process into a power-packed, quick win style workshop to teach you about databases & SQL in just an hour. Seriously – you’ll be writing your own queries, without fear of crashing the database, in just an hour.
Grab the pre-launch special now for $10 to master the basics in less time than your evening commute.
Analyze, Visualize, Thrive!