How to Get More from Your BI Tools with SQL

Are you frustrated with the limitations of your business analytics tool? Do you wonder if there are other features you could tap into? Would you like to know how SQL compliments your work with Power BI and Tableau? Read this article to learn more!

If you are a data analyst or if data analysis is part of your everyday job, Business Intelligence (BI) tools are crucial for your work. You also know that most databases run SQL queries in the background of such tools. This is not an accident, as SQL is the most widely used querying language for data analysis.

If you wonder how important these SQL processes are for your job, you are in the right place.

In this article, you will see how SQL is used in BI tools and why it is helpful to know SQL when you work with them. Knowing SQL unlocks features in these tools that you can’t access through the graphical interface only. We’ll review the two most popular BI tools, Power BI and Tableau, and explain how knowing SQL lets you work more efficiently with them.

BI Tools and Data Analysis

With the rise of data democratization, more companies are becoming data driven. If you are working in such a company, analyzing data is an essential part of your job.

If analytics is a regular feature of your job, tools like Power BI and Tableau are central to your everyday work. These tools provide convenient ways to connect to a database, visualize its content, and produce reports.

However, convenience comes with a price. Your BI tools’ graphical interface often allows asking some specific types of questions – but not others. With simple queries, this is not a problem. However, the default options can be too resource-hungry when you’re building a customer-facing or organization-wide reporting solution, resulting in low performance.

To customize your queries and improve your dashboards’ performance, you need to dig deeper into your BI tools’ background workings. Customization differs from tool to tool. Most often, it requires using a querying or scripting language. BI tools often have their scripting language, making the landscape quite complex. However, this fragmentation highlights the importance of the common denominator behind all these tools: SQL.

SQL is a versatile language that can be used for analyzing small datasets up to full-scale financial investigations. SQL’s long-term and widespread popularity is a testament to its usability. We can see its influence in BI tools in how they organize data primarily like a relational database model and how they connect to SQL databases to run queries.

Next, we’ll demonstrate how Power BI and Tableau (both widely used tools in the business analytics space) rely on SQL. We’ll also see how you can use SQL in your work.

SQL in Power BI

Power BI is very useful if you work with the Microsoft Office and Azure suite. It allows users to connect different data sources like Excel or MS SQL Server.

Power BI is similar to other BI tools in that it offers a visual querying tool, Power Query, as the main way to create and visualize views. For this reason, it hides most of the SQL work in the background.

For more complex Power Query imports, Power BI offers the M formula language that you can use in its advanced editor. The main use of M and the advanced editor is to combine data from different sources that otherwise would require other query languages.

If you want to control the M queries Power BI sends to the database, you can use the advanced editor and optimize it.

When to use M, SQL, or Dax (another Power BI language for modeling analytical data calculations) is a hard question that requires plenty of expertise. Here, we have space only to highlight the uses of SQL.

There are two main ways you can use SQL in PoweBI:

• To specify your database connection to a subset of your data.
• To examine the native queries Power BI sends to your databases.

Please note that most of the functionalities described here are available in the Power BI Desktop edition.

Specify Database Connections in Power BI

Whenever you query data from an SQL database, you have the option to write the SQL statements explicitly.

Doing this will import the selected part of the data so you do not overflow your workflow.

Writing your SQL queries can be useful when you’re working with a subset of a huge dataset. You can even combine this with Power BI’s DirectQuery option to entirely avoid data import and save memory, computation, and time while working with your report and at each (re)load.

Examine Native Query

You can examine the individual queries Power Query sends to your databases by checking the Native Query view.  Having a better understanding of the query, you can optimize it by removing unnecessary steps (e.g. transformations).

If you want to have a general picture of the queries generated by Power BI (e.g. for performance reasons), you can examine Power BI’s log and find the queries there.

Using SQL With Tableau

Tableau is one of the earliest and most established BI tools, outstanding for its impressive UI/UX capabilities. Because of its wide usage, Tableau provides extensive integration possibilities, making it a popular choice for many businesses.

Many of Tableau’s integrations are with SQL databases:

Tableau’s ease of use comes with limited customizability, however. The visualization-focused approach does not provide space for text-based querying; it relies mostly on the graphical interface.

Still, there are ways you can use SQL with Tableau:

• In data preparation.
• To create custom SQL connections.
• To set up the initial workbook query.
• To extract the SQL query from visualizations.

Data Preparation

Tableau does not provide tools for data preparation, so you are better off processing your data before ingesting it into your Tableau workbook. Knowing SQL allows you to access and examine the data beforehand and create new tables and views for easy visualization.

Write Custom SQL Queries

If your Tableau workbook connects to an SQL database, Tableau uses its VizQL engine to translate your actions into optimized SQL queries. However, this type of querying  does not fully support SQL sub-selects, UNIONs, or ON clauses.

If you want to execute such SQL commands, set up a custom data environment, or connect only to temporary tables during a session, you can write custom SQL queries.This can be especially useful when you need to:

• Combine tables vertically with UNION.
• Change field types for cross-database joins.
• Reduce data size.
• Create pivot tables by restructuring data.
• Create aggregate metrics from joined tables.

Another great use case for custom SQL is the inclusion of dynamic parameters into the query. For this, you must define and include a parameter when you write the custom query.

You will be able to use the parameter to filter your graphs based on the parameter values.

Set up the Initial SQL Query

Whenever you set up a new workbook that connects to an SQL database, you may want to define the initial query that future users will face whenever they open it. Setting the initial query allows you to benefit from custom connectivity without asking each user to do the SQL heavy lifting at each session.

Extract the SQL From Visualizations

You may occasionally want to see the SQL query behind a Tableau visualization. You can do this by creating a performance recording of your actions and copying the SQL query or queries from its log.

Please note that performance recording is available only for Tableau Desktop and Server editions.

Why Use SQL With BI Tools

You can use these tools without SQL. However, if you work with SQL databases and your reporting and Business Intelligence problems are in the scope where performance is an issue, knowing SQL is a great resource:

• If you know SQL, you can better utilize your BI analytics tools. You’ll have more options and be able to use additional features.
• Many BI tools try to silo you and your organization into their services; knowing SQL makes you more agile in shifting between them.
• Knowing SQL makes you understand these tools’ and your databases’ underlying logic, giving you better insight into data problems.

Learn SQL for BI Tools!

So, would you like to learn more about SQL and take your BI tools game to the next level? We have multiple options for you!

If you are working in a company, you may want to persuade your boss or employer of the benefits of using SQL and creating SQL reports.

If you are running a business, check out our offerings for businesses. Teaching SQL to your employees during their onboarding can strongly benefit your organization.

In any case, you can start to learn straight away! We have beginner courses covering the basics of SQL and advanced tracks that teach you everything from using SQL in your work to making effective business reports.