In a database where multiple tables exist, you might have to join a few tables to get the desired data from the database. Visualization tools like Tableau use JOINs to get access to data while working on a large database. Tableau supports connectivity to several data sources like SQL Server which helps to load the desired data. These may not be optimized in complex situations which is where Custom SQL in Tableau comes in to align data extraction operations.
In this blog, you will look at how to work with Custom SQL in Tableau in great detail. This blog will first explore a brief introduction to Tableau, Relational Database Management Systems, and Custom SQL before diving into the main focus.
Introduction to Tableau
Tableau is a powerful data visualization and business intelligence tool that helps users transform raw data into interactive, visually appealing dashboards and reports. It enables users to easily connect to various data sources, including spreadsheets, databases, cloud services, and big data platforms, providing a seamless way to analyze and interpret data. Designed with a user-friendly interface, Tableau is widely adopted by businesses of all sizes to make data-driven decisions without the need for extensive technical expertise.
With its drag-and-drop functionality, Tableau allows users to explore data and uncover insights quickly. The tool supports various visualization types such as charts, graphs, and maps, helping users convey complex information in a simple, digestible format. Tableau’s flexibility and wide range of integrations make it a top choice for data professionals, analysts, and business users alike.
Here are a few benefits of Tableau that make it such an indispensable tool:
- Easy Data Integration: Connects to multiple data sources, including cloud services, spreadsheets, and databases, allowing users to blend data for more comprehensive analysis.
- Interactive Visualizations: Offers a variety of dynamic, interactive charts and dashboards that make data easier to understand and explore.
- User-Friendly Interface: Drag-and-drop functionality makes it accessible for both technical and non-technical users to analyze data without coding.
- Real-Time Data Analysis: Supports real-time data updates, enabling users to make faster, data-driven decisions.
Introduction to Relational Database Management Systems (RDBMS)
Relational Database Management Systems or RDBMS, in short, rely on a relational model to represent data in an instinctive and comprehensible way. RDBMS presents tables having rows and columns and shows a relationship between these tables, helping you to understand the connection between records available in the table.
In RDBMS, rows are used to maintain records, whereas columns are required for storing fields or attributes. As all the rows provide a unique record, RDBMS uses keys or IDs to identify a particular row. Without these IDs, a relationship can’t be created between two or more tables.
Introduction to Custom SQL
Structured Query Language (or SQL) is a language of databases. The purpose of this domain-specific language is to store, retrieve, manipulate and access data from relational databases. With SQL, you can perform the following functions:
- You can insert records in a database.
- You can extract records from a database.
- You can update records in a database.
- You can delete records from a database.
- You can retrieve records from a database.
- You can create relations between tables.
- You can join two or more tables in a database.
- You can monitor user’s access by using statements.
And thanks to the format of SQL query, which is simple yet powerful. It permits you to write simple and declarative statements to preserve the reliability, accuracy, and security of both data and database.
Every database is different from others, so does their SQL syntax. It means that two different databases can’t allow you to use the same or custom-made SQL syntax. Custom SQL, however, is invaluable too.
- Join two tables.
- Recast attributes for performing cross-database unions.
- Model data for data analysis.
- Reduce the size of data for data analysis.
- And much more.
Exploring the best databases for 2024? Hevo makes it simple to migrate data from any of these top databases to your desired destinations. Our no-code platform automates data pipelines, ensuring smooth and efficient data transfer.
Why Use Hevo?
- Broad Database Support: Seamlessly connect with a wide range of databases and load data to your preferred destinations.
- Automated Data Migration: Reduce manual effort with automated data syncing and transformation.
- Real-Time Data Flow: Keep your data up-to-date and ready for analysis.
Don’t just take our word for it – see why we’re rated 4.3/5 on G2.
GET STARTED WITH HEVO FOR FREE
Understanding the Working of Custom SQL in Tableau
The Custom SQL functionality in Tableau enables you to reveal secrets of your data which is present under the slack of complex reports and old-decades processors. Let’s start off with an example showing the working of Custom SQL in Tableau.
Step 1: Setting up Tableau Account
First of all, you need to download Tableau Desktop software from the official website. After complete installation, register your company by entering the following fields:
Image Source
Image Source
Step 2: Working With a Sample Workbook for Custom SQL in Tableau
Tableau Desktop software comes up with three sample workbooks: Superstore, Regional, World Indicators. In order to understand the functionality of Tableau Desktop software, a sample is used and a database of SuperstoreUs that can be accessed through Microsoft Management Studio.
Image Source
Step 3: Microsoft Management Studio Installation for Custom SQL in Tableau
After downloading Tableau Desktop software, install SQL Server Management Studio (SSMS). You can also use any other Management Studio and database depending upon your business need.
Connect to the SQL Server by entering the following details:
- Server Type: Database Engine
- Server name: ec2-52-14-205-70.us-east-2.compute.amazonaws.com
- Authentication: SQL Server Authentication
- User name: SQL
- Password: SQL
Image Source
Now, expand the Database option and explore the SuperstoreUS option as shown below:
Image Source
Open the Tableau Desktop software, select the Microsoft SQL Server, use the following details, and press enter:
Image Source
Select the SuperstoreUS option, and you’ll get all the tables, charts, diagrams as shown in the picture below:
Image Source
Step 4: Setting up Custom SQL in Tableau
Select the New Custom SQL option from the Data source page and an empty dialogue box will appear in front of your screen.
Image Source
Retrieving Data through Custom SQL in Tableau
Custom SQL Query makes it easy to access data instead of exploring every record or field one by one. If you want to search the customer name and its respective segment. In order to get the data, you will use the following Custom SQL Query, click on the preview results option to analyze outcomes:
Image Source
Press the OK button and you’ll see the following details in the grid section:
Image Source
You can also apply conditions to access only a limited portion of data. For example, you want to pull out only those customers who’ve Home Office segments like shown below:
Image Source
The grid will display the following entities:
Image Source
Joining Tables for Custom SQL in Tableau
You can also use a Custom SQL Query for the union of two or more tables. SuperstoreUS has two tables: People_Multiple and People Multiple. You can perform the union of these tables by entering the following query:
Image Source
Press the OK button to receive the union of two tables in the grid.
Image Source
Table Relationships for Custom SQL in Tableau
In addition to union, you can also create relationships between two tables, as shown below. For instance, drag the orders table from the data source page to canvas. After this, drag the people’s table in the same way. The Tableau Desktop automatically determines the relationship between two tables (see the picture below). Moreover, the software also enables you to edit relationships by adding more fields. Click on the link icon to determine which type of join is required between the selected tables.
Image Source
Step 5: Modifying Queries with Custom SQL in Tableau
Tableau Desktop lets users edit a Custom SQL Query. Drag any table from the left side of the screen to the canvas screen and double-click over it. Change the name of the table to Custom SQL Query as depicted below.
Image Source
Now to edit the Custom SQL Query, click on the arrow and select the edit Custom SQL Query option from the drop-down menu.
Image Source
Renaming Queries with Custom SQL in Tableau
If you want to change the name of Custom SQL Query, double-click over it, then select the rename option.
Image Source
This will allow you to change the name of the query.
Image Source
Removing Queries with Custom SQL in Tableau
If you want to remove Custom SQL Query, double-click over it, then select the remove option.
Image Source
Using Parameters with Custom SQL in Tableau
Another prominent feature of the Tableau Desktop is that it lets you create parameters. To use or create parameters, double-click over the new Custom Query icon, select the Insert Parameter option and then choose to create a new Parameter option.
Image Source
A new dialogue box will appear, allowing you to define a name, data type, current value, and other options. The option of New Parameter doesn’t change the existing or replace current parameters, the purpose of this option is to create custom-built parameters for your business needs.
Image Source
If you want to explore more about parameters, check the official blog post of Tableau related to creating parameters.
Conclusion
This blog post has explored the functionality and working of Custom SQL in Tableau in great detail. Although the blog is only touching on the basics of Custom SQL Query in Tableau, it will help you to understand the primary functions of this feature. In conclusion, a Custom SQL Query permits you to retrieve, add, join, remove and update data instantaneously. The Tableau Desktop also enables you to define your queries, along with parameters, and to make them part of your database.
Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool such as Tableau. Hevo is fully automated and hence does not require you to code.
FAQ
Can you use custom SQL in Tableau?
Yes, you can use custom SQL in Tableau to write queries that retrieve specific data from your database instead of relying on the drag-and-drop interface for data connections.
Why avoid custom SQL in Tableau?
Custom SQL in Tableau can lead to performance issues, as Tableau has to send the entire SQL query each time the dashboard is refreshed, which can slow down large or complex queries.
What is the difference between initial SQL and custom SQL in Tableau?
Initial SQL is used to set up the environment before the actual data connection, such as setting session variables, while custom SQL retrieves specific data for analysis within Tableau.
Syeda is a technical content writer with a profound passion for data. She specializes in crafting insightful content on a broad spectrum of subjects, including data analytics, machine learning, artificial intelligence, big data, and business intelligence. Through her work, Syeda aims to simplify complex concepts and trends for data practitioners, making them accessible and engaging for data professionals.
