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.
Table of Contents
Introduction to Tableau
Image Source
Professional experts can find unlimited BI tools like IBM Cognos Analytics, SAP BI tool, Oracle Business Intelligence, and many more. But the main problem starts with the non-technical people and beginners when they want to select BI tools for driving Data Analytics and actionable insights.
Now imagine, if you get a tool that lets non-technical people see and understand data by transforming raw data into both readable and understandable format, then businesses would be simple and cost-effective. One such tool was founded by an American company, namely Tableau software in Mountain View, California, in 2003. Nevertheless, it was later attained by Salesforce for $15.7 billion.
Tableau Desktop brings groundbreaking features through which you can make the right decisions at the right time. For instance, you can identify existing pain points, spot trends, predict the future of current strategies, and get AI supervision to bring out the best for your business.
Unlike other BI tools in the market, Tableau software doesn’t make a compulsion for data analysts to learn BI tools — Data analysts should ask questions instead of learning. Tableau Desktop’s users, therefore, ask simple or complex questions and receive an instantaneous response from software. Tableau is integrated with Einstein AI tool that provides reliable predictive models to make the decision-making process steadfast and accurate.
Here are a few benefits of Tableau that make it such an indispensable tool:
- Handling a Large Amount of Data: Tableau is capable of handling millions of rows of data. This is done without disturbing the performance of the data dashboards. Tableau also provides the option of connecting to different data sources like SQL Server in real-time.
- Mobile Responsive Dashboard: The reporting feature of Tableau allows you to customize your dashboards for different devices like laptops or for mobiles. Tableau is flexible enough to recognize the device viewing the report and make corresponding adjustments to ensure effective report delivery.
- Simplify Complex Tasks With Other Languages: To perform complex table calculations and avoid performance issues, Tableau allows you to leverage R or Python to simplify tasks. By leveraging Python Script, users can reduce their workload by having packages take care of tasks like data cleansing.
- Ease of Implementation: Tableau offers a number of visualization templates to choose from which enhance the user experience. Tableau has a simple user interface that encourages non-technical people to undertake data analysis without any prior experience or knowledge in coding.
- Data-Driven Business Decisions: You can make better business decisions when you have swift and efficient reporting capabilities along with accurate data. For instance, MillerCoors built personalized mobile dashboards for their sales team that allowed them to look at real-time data and sales forecasts prior to their client meetings. This led them to be better prepared to speak about the needs of the prospects or the clients, which then translates to a better Lead Conversion Rate. Gone are the times of relying on outdated data, with the increasing popularity of Tableau.
- Better Collaboration: Tableau allows you to share the data visualizations of your customer data across departments. This data can be used by the different departments to extract actionable insights and make better decisions to improve the performance of their department and the whole company as a whole. Pfizer for example has developed models to optimize patient diagnosis by leveraging the Tableau visualizations shared across departments. It has also helped Pfizer discover better ways to conduct clinical trials. Thus companies can engage in innovation by saving time on report compilation and data analysis with Tableau.
- Better Customer Experience: Business Intelligence Tools like Tableau have been built keeping in mind the customer experience and customer satisfaction. For example, Verizon significantly improved its efficiency by focusing on a reduction of customer support calls. Verizon achieved this by creating over 1,500 dashboards for the employees across departments. These dashboards then used the data from operations and the text data from customer support chat sessions to locate areas of improvement. This measure led to a reduction of 43 percent in customer support calls.
- Faster Data Analysis: Tableau allows you to perform heavy-duty processing of data in the cloud or the company’s servers. It can ingest data from various sources into a data warehouse and analyze the data based on user queries, dashboards, and drag-and-drop reports. For instance, the HR department at Lenovo boosted reporting efficiency by 95 percent through the condensation of multiple monthly reports to a single snapshot dashboard.
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.
A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources to Tableau in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line.
GET STARTED WITH HEVO FOR FREE
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
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.
VISIT OUR WEBSITE TO EXPLORE HEVO
Want to take Hevo for a spin?
SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Syeda is a freelance writer having passion towards wiriting about data industry who creates informative content on data analytics, machine learning, AI, big data, and business intelligence topics.