Skip to main content

SQL with Power BI

In this tutorial, you'll learn how to connect Power BI to a Microsoft SQL Server database and then analyze the data to get insights.
Mar 2019  · 12 min read

Power BI is a cloud-based business analytics service from Microsoft that enables anyone to visualize and analyze data, with better speed, understanding an efficiency. It is a powerful as well as a flexible tool for connecting with and analyzing a wide variety of data. Apart from the various visualization advantages that Power BI offers, it has an amazing out of the box connection capabilities like easily integrating with Databases. This offers increased advantages regarding functionalities and comes in handy for Data Scientists who are used to working in SQL. Power BI provides an optimized, live connector to SQL Server so that we can easily create charts, reports, and dashboards by directly working with a large amount of data.

Pre-requisites

Since this article will be focused only on SQL with Power BI, we suggest going through the following existing DataCamp courses and tutorial, which serve as an excellent primer:

banner

1. Power BI

Power BI gives the ability to analyze and explore data on-premise as well as in the cloud. Power BI gives the ability to collaborate and share customized dashboards and interactive reports across colleagues and organizations, easily and securely.

graphic

Source

Advantages of using Power BI

Power BI provides certain advantages which makes it superior to the existing analytical tools:

  • Provides a cloud-based along with a desktop interface.
  • Provides capabilities like data warehousing, data discovery and interactive dashboards.
  • Ability to load custom visualizations
  • Easily scalable across the entire organization.

Due to the immense capabilities of Power BI, Microsoft has been recognized as a Leader in the “Analytics and Business Intelligence Platform”, by Gartner, for 12 consecutive years.

Gartner Magic Quadrant for Analytics and Business Intelligence Platform

2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform

Power BI Components

Power BI consists of various components which are available in the market separately and can be used exclusively.

powerbi components

Content Source

Choosing which component to work with depends mainly on the project or a team. We, however, will be working with Power BI desktop since this is a component primarily used for Business report generation and desktop creation. Also, the other works usually begin with Power BI desktop, where the report creation takes place.

2. Power BI Desktop

Power BI Desktop is a free application that can be downloaded and installed on the system. It can be connected to multiple data sources. Typically, an analysis work begins in Power BI Desktop where report creation takes place. The report is then published to Power BI service from where it can be shared to the Power BI Mobile apps so that people can view the reports even on mobiles.

Power BI Desktop

Source

Installation

Power BI only runs on Windows Machines. Mac users could spin up a Windows VM in Azure and load Power BI onto that or use Turbo.net, which can stream Power BI to the Mac directly from the cloud.

Power BI can be accessed in two ways:

  • We can get it as an app from the Microsoft store and just sign in to get started. This is the online version of the tool.
  • Sometimes, we need to work in offline mode with our data. In such situations, download the software locally and then install it. Make sure you read all the installation instructions.

Depending upon the choice of product, download the software on to the computer. After accepting the license agreement, verify the installation by clicking the Power BI Icon/App. If the following screen appears, you are good to go.

Power BI Desktop

3. Getting Started

Let us now get an idea about working of Power BI Desktop. In this section, we shall explore it a bit to get accustomed to its interface.

Workspace

The image below highlights the major components of the workspace of Power BI.

Workspace

Power BI Desktop workspace

Data Source

Power BI can be connected to a number of data sources. The Get Data icon displays all the possible available options from where data can be imported into Power BI.

Data Source

4. Connecting to SQL Server

Let us walk through an example depicting how to connect SQL server database to Power BI Desktop and then use it to analyze the database.

Sample Database

For the demonstration purpose, we will be using a publicly accessible SQL Server instance on AWS and a database which has been created based on the Superstore dataset.

This dataset contains information about products, sales, profits, etc. and our aim as Data Analysts is to analyze the data and find critical areas of improvement within this fictitious company. This SQL server instance has been hosted by Ken Flerlage.

Accessing the sample database

  • Install the SQL Server Management Studio(SSMS). SSMS is a free and integrated environment for managing any SQL infrastructure. With SSMS one can deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.
  • Once downloaded and installed on to your system, you will see the following screen asking for specific credentials.

Accessing the sample database

Enter the following credentials:

Server Name: ec2-52-14-205-70.us-east-2.compute.amazonaws.com
Authentication: SQL Server Authentication
Login: SQL
Password: SQL

You will now be granted a ‘Read Only’ access to the ‘SuperStoreUS’ database.

Accessing the sample database

For our example, ec2–52–14–205–70.us-east-2.compute.amazonaws.com is the name of the instance, SuperstoreUS and Test are the databases, and Orders, Customers etc. are the tables within the SuperstoreUS database. Thus there can be multiple instances, and each instance can further contain numerous databases which can also have multiple tables.

5. Importing SQL data into Power BI

Power BI Desktop organizes the data into queries. This means all the data is laid out into a table like structure.

Setting up the connection

Open the Power BI Desktop and navigate to the start screen. Here the Get Data tab pane offers a lot of choices in terms of the data sources that can be connected to Power BI Desktop. We will connect to the SQL Server.

Setting up the connection

On Clicking the SQL Server option, a new screen will open up which will ask for the Server to which we want our Power BI Desktop to be connected. Enter the details and its done.

Setting up the connection

We can now click on the desired table and view its contents. To load a particular table, simply tick the checkbox next to it and load it.

Data Connectivity Modes

SQL database can be connected to Power BI Desktop in two ways, both the options which appear on the main screen.

Import

As the name suggests, import method ‘imports’ the selected tables into Power BI Desktop. Power BI then uses this imported data for creating a visualization or doing any manipulations. To see any changes in the underlying data, we need to refresh the data which imports the entire data set again.

DirectQuery

If DirectQuery is used as an option, no data is imported or copied into Power BI Desktop. While we create or interact with data through visualizations, Power BI Desktop queries the underlying data source, which means we are always working with the current data. However, this method provides limited options as to data manipulation, unlike the import method.

DirectQuery

Here is a link that goes in depth regarding the Direct Query method.

6. Query Editor

All the selected tables will be loaded into the Power BI Desktop and will be displayed as individual datasets in the Data view.

Query Editor

From here, we can modify our datasets. For this, we will take the help of the Query Editor. Query editor can be used for modifying datasets irrespective of their data source. We can do manipulations like renaming a dataset, removing a single or multiple columns, etc. in the query editor.

Query Editor

The Left pane displays the number of active queries while the right pane is called the Query Settings pane and displays all the steps associated with a query.

The Advanced Editor

The Advanced Editor displays the code for the query that is being executed against the data source. The syntax corresponds to M, the Power Query Formula Language. One can also create their own code.

The Advanced Editor

Saving your work

After having performed the necessary modifications in data through the query editor, select Close & Apply from Query Editor’s File menu. This will apply the changes to the data in the Power BI Desktop.

Saving your work

7. Merging Datasets

Merging datasets comes in handy when we want to combine one or more datasets into one. This merging is also facilitated through the Query Editor. This time we will load in three tables from the SuperstoreUS database. The tables are Orders, Customers, and Returns.

Merging Datasets

The Fields column is populated with the three selected tables. Now, click on the Edit Queries button and navigate to Combine option, where the dropdown will expose the Merge option. We shall use the create a new query by merging existing queries.

Merging Datasets

The Merge dialog box opens up (as shown in the following figure), and we select the tables to be merged and the type of join we want. We need to select columns which are common to both tables.

Let’s create a new query and name it as Orders and customers. We will combine the Orders and Customers table through this query.

Merging Datasets

This new query contains all the primary columns from the orders’ table and relationship columns from the customers’ table. Delete all the relationship columns except the last one, which represents the Customers query. Then select columns from Customers query to add to the new merged query.

The following demo will make the process more clear.

Merging Datasets

This merged query consists of a single database with all the desired columns. We can now easily work with this single database instead of working with multiple data sources which can lead to confusions.

8. Building & Publishing a Dashboard

Once we have the dataset ready with all the manipulations done, we can proceed for the Dashboard creation process. A Power BI dashboard, also known as canvas, consists of many visualizations on a single page which helps to tell a story. These visualizations called tiles are pinned to the dashboard from the reports.

Let’s now try to understand what insights we can get using superstore data set (Source: Power BI Dashboard):

  • Profit by States

Profit by States

  • Sales & Profit by segment

Sales & Profit by segment

  • Sales & Profit by Region

Sales & Profit by Region

  • Sales by Sub-Category

Sales by Sub-Category

  • Profit by region

Profit by region

  • Quantity

Quantity

After formatting the size, appearance, and color, we will get a dashboard which resembles the one below.

dashboard

Superstore Dashboard

Publishing

Data is only useful when it can be shared among people or organization. The generated Dashboard or reports can also be shared by publishing it to the Power BI Service. We can then use the Power BI Apps to view or interact with the Dashboards/Reports.

Publishing

9. Conclusion

Using SQL and Power BI together takes the data analysis to the next level. We can easily connect the SQL Server to Power BI and extract the data directly into it. Power BI enables the users to toggle connections with a click to apply in-memory queries to a larger dataset. SQL is a pretty useful tool and when leveraged with the expertise of Power BI can help to make the analysis more powerful and insightful.

References

Intermediate SQL Queries

Beginner
4 hours
1,485,243
Master the basics of querying tables in relational databases such as MySQL, SQL Server, and PostgreSQL.
See DetailsRight Arrow
Start Course

Exploratory Data Analysis in SQL

Beginner
4 hours
70,628
Learn how to explore what's available in a database: the tables, relationships between them, and data stored in them.

Data Manipulation in SQL

Beginner
4 hours
156,073
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See all coursesRight Arrow
Related
Data Science Concept Vector Image

How to Become a Data Scientist in 8 Steps

Find out everything you need to know about becoming a data scientist, and find out whether it’s the right career for you!
Jose Jorge Rodriguez Salgado's photo

Jose Jorge Rodriguez Salgado

12 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

DC Data in Soccer Infographic.png

How Data Science is Changing Soccer

With the Fifa 2022 World Cup upon us, learn about the most widely used data science use-cases in soccer.
Richie Cotton's photo

Richie Cotton

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
DataCamp Team's photo

DataCamp Team

10 min

How to Make a Gantt Chart in Python with Matplotlib

Learn how to make a Gantt chart in Python with matplotlib and why such visualizations are useful.
Elena Kosourova 's photo

Elena Kosourova

17 min

Working with Geospatial Data: A Guide to Analysis in Power BI

Discover what geospatial data analysis is, the different types of geospatial data, and how to analyze geospatial data using Power BI.
Joleen Bothma's photo

Joleen Bothma

10 min

See MoreSee More