Connect SQL Server to Power BI: 4 Easy Methods

on Data Integration, ETL, Tutorials • July 5th, 2020 • Write for Hevo

Are you looking for an easy way to connect SQL Server to Power BI? Does the process of analyzing your SQL Server data look difficult? If this applies to you, then this blog will answer your queries. SQL Server is a relational database management system (RDBMS) by Microsoft. It is capable of holding petabytes of data and running 100 parallel cores. The comprehensive querying layer aids in business analysis and also tightly integrates with reporting tools like Power BI.

Power BI is used for providing analysis, visualization, and business intelligence capabilities to its users, making it an appropriate choice as a BI. This blog will take you through the different available approaches to connect SQL Server to Power BI and also, mentioning their merits and demerits. 

Let’s see how this blog is structured:

Hevo Data: Integrate your Data for Better Analysis

Hevo is a No-code Data Pipeline. It offers a fully automated service that extracts your data from the source, transforms it, and then loads it to your destination in real-time. Hevo supports data integration from 100+ data sources, including the SQL server. Hevo can combine data from SQL Server and other data sources and simplify the visualization process in the Power BI.

Here are some salient features of Hevo:

  1. Simple: Hevo offers a very simple and easy-to-use interface to its user. 
  2. Minimal Set-Up Time: To work with Hevo, you just have to invest a few minutes in set-up, and then it’s ready to use. 
  3. Fault-Tolerant: Hevo offers a fault-tolerant architecture. It automatically detects any anomalies and notifies you instantly. If there is any affected record, then it is set aside for correction.
  4. Scalability: Hevo can handle millions of records per minute easily and without any latency.
  5. Zero Maintenance: Set up once, and then Hevo will take over all the remaining work. You don’t have to write ETL scripts and cron jobs anymore.  

Do all these features make Hevo fascinating to use? If yes, then sign up for a 14-day free trial today

Method 1: Using Custom Scripts

You can either use python or R scripts to import data from SQL Server. We have used python scripts for this module.

Let’s see what you will cover here:

Prerequisites

  • Install python on your computer.
  • Basic python knowledge.

Steps to Connect SQL Server to Power BI Using Custom Scripts

Follow these steps to connect SQL Server to Power BI using python script.

Step 1: Pip install these libraries in the command prompt:

pip install pyodbc
pip install pandas
pip install matplotlib

pyodbc is a python library used to access ODBC databases.
pandas is a python library used for data manipulation and analysis. The imported data from python should be in a data framework. 
matplotlib is a python library used for plotting graphs and charts. You can directly plot data frames from pandas using this library. 

Note: We also need to check the location of the python home directory. Go to ‘Files’ then ‘Option and Settings’. Click ‘Python scripting’ and verify the ‘Python Home Directory’ which points to your local python installation path and ‘Python IDE’ on which your system will execute the script.

Step 2: Select ‘Get Data’ in the home ribbon.

SQL SERVER TO POWER BI: GET DATA

Step 3: In the Get Data Panel, select ‘Others’ and then select ‘Python Script’. Select ‘Ok’.

SQL SERVER TO POWER BI: SELECT PYTHON SCRIPT

Step 4: In the python script dialog box, write the following code, and then click ‘Ok’:

import pandas as pd	
import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                     'Server=servername;'
                     'Database=databasename;'
                     'Trusted_Connection=yes;')
cur= conn.cursor()
sqlquery = pd.read_sql_query('SELECT * FROM tablename',conn)
print(sqlquery)
SQL SERVER TO POWER BI: PYTHON SCRIPT

This query connects to the SQL Server using the pyodbc, then uses that connection to fetch data from our desired table. This data is then converted to a data frame before loading it to Power BI. 

Step 5: In the navigation panel, select your query result and click ‘Load’.

SQL SERVER TO POWER BI: SELECT TABLE

Step 6: On the right-hand side of the screen, you will get your data. Now, you can visualize the data as you want.

SQL SERVER TO POWER BI: VISUALIZE DATA

Drawbacks

  • The installation of several python libraries needs to take place.
  • You need to check your python script correctly otherwise it will throw errors.
  • Connecting to the SQL server using python is a complex process.   

Method 2: Using Connector

Power BI supports connectors that can directly link your SQL Server data to it.

Let’s see what you will cover here:

Prerequisites

  • Basic SQL knowledge.
  • A ready to use data in SQL Server.

Steps to Connect SQL Server to Power BI Using Connector

Use the following steps to achieve it:

Step 1: Either click on ‘Get Data’ or select ‘SQL Server’ in home ribbon.

SQL SERVER TO POWER BI: GET DATA

If you have selected ‘Get Data’, then in the panel select ‘Database’. Select the ‘SQL Server database’ and click ‘Connect’.

SQL SERVER TO POWER BI: SELECT SQL SERVER

Step 2: In the SQL Server database dialog, write the name of your server, and select ‘DirectQuery’. You can also specify the name of your database. Click ‘Ok’.  

SQL SERVER TO POWER BI: SQL SERVER

Step 3: In the navigation panel, select your data which you want to analyze and visualize. Click ‘Load’. 

SQL SERVER TO POWER BI: SELECT TABLE

Step 4: On the right-hand side of your screen, you will get your table data. Now you can visualize it, according to your need.

SQL SERVER TO POWER BI: VISUALIZE DATA

Drawbacks

  • Connecting SQL Server to Power BI is a laborious task as you have to establish a connection between these platforms before you start your visualization process. 
  • If you select ‘Import’ as your data connectivity mode, then the entire dataset will be imported to Power BI, limiting the functionality of Power BI. 

Method 3: Exporting Data to Excel

We can export our SQL Server data to excel and then import this excel sheet in Power BI.

Let’s see what you will cover here:

  1. Prerequisites
  2. Steps to Connect SQL Server to Power BI by Exporting Data to Excel
  3. Drawbacks

Prerequisites

  • Basic SQL knowledge.
  • Basic knowledge about excel.

Steps to Connect SQL Server to Power BI by Exporting Data to Excel

Follow these steps to achieve it:

Step 1: In SQL Server, right-click the database containing your desired table. Select ‘Task’, and then select ‘Export Data’.

SQL SERVER TO POWER BI: EXPORT DATA

Step 2: In the Choose a Data Source panel, select your data source as ‘SQL Server Native Client 11.0’ and click ‘Next’.

SQL SERVER TO POWER BI: CHOOSE DATA SOURCE

Step 3: In the Choose a Destination panel, select the destination as ‘Microsoft Excel’. Specify your file path and name of the file in the ‘Excel file path’ dialog. Click ‘Next’.  

SQL SERVER TO POWER BI: CHOOSE DESTINATION

Step 4: Select ‘Write a query to specify the data to transfer’ if you just want to export specific data. Click ‘Next’. 

SQL SERVER TO POWER BI: SELECT WRITE A QUERY

Step 5: Write the following query in SQL statement dialog and click ‘Next’:

SELECT * FROM tablename;
SQL SERVER TO POWER BI: WRITE QUERY

Step 6: Click ‘Next’ in ‘Select source tables and views’ and ‘Review data mapping type’. Click the dialog ‘Run immediately ‘ in ‘Save and Run Package’ and then click ‘Finish’.

Step 7: Now in Power BI, select ‘Excel’ from the home ribbon.

SQL SERVER TO POWER BI: GET DATA

Browse the exported excel file.

Step 8: In the navigator panel, you will see the query result. Select your data and click ‘Load’.

SQL SERVER TO POWER BI: SELECT TABLE

Step 9: On the right-hand side of the screen, you will see your data. Now you can visualize it.

SQL SERVER TO POWER BI: VISUALIZE DATA

Drawbacks

  • It is a time-consuming process as you first export your data from SQL Server and then load it in Power BI.
  • The exporting process from SQL Server is complex as compared to other databases. 

Method 4: Using Enterprise Gateway

A gateway fills the gap between the on-premise data and Microsoft cloud services. An on-premise gateway in Power BI lets multiple users connect to multiple on-premise data sources. 

Let’s see what you will cover here:

  1. Prerequisites
  2. Steps to Connect SQL Server to Power BI Using Enterprise Gateway
  3. Drawbacks
SQL SERVER TO POWER BI: ENTERPRISE GATEWAY
Image source: https://docs.microsoft.com/

Prerequisites

  • Install Power BI standard gateway from here.
  • Keep your gateway name and recovery key handy. 
  • A Power BI account. 

Steps to Connect SQL Server to Power BI Using Enterprise Gateway

Let’s see the steps to achieve this process:

Step 1: Go to Power BI Desktop and select ‘SQL Server’ in the Home ribbon.

Step 2: In the SQL Server database dialog, write the name of your server, and select ‘DirectQuery’. You can also specify the name of your database. Click ‘Ok’.  

SQL SERVER TO POWER BI: SQL SERVER

Step 3: In the navigator panel, select your data which you want to analyze and visualize. Click ‘Load’. 

SQL SERVER TO POWER BI: SELECT TABLE

Step 4: On the right-hand side of your screen, you will get your table. Save this at your desired location.

SQL SERVER TO POWER BI: VISUALIZE DATA

Step 5: Click on ‘Publish’ in the Home ribbon. Select your workspace.

SQL SERVER TO POWER BI: PUBLISH DATA

Step 6: Select the URL as shown.

SQL SERVER TO POWER BI: COPY URL

Step 7: You need to login to your Power BI account to access it. Now select ‘Setting’ on the top right-hand side corner. Select ‘Manage gateways’.

SQL SERVER TO POWER BI: MANAGE GATEWAY

Step 8: Select the gateway that you created while downloading the Power BI gateway. Select ‘ADD DATA SOURCE’. 

SQL SERVER TO POWER BI: ADD DATA SOURCE

Step 9: Now, in ‘Data Source Name’, you will specify the name. In ‘Data Source Type’ select ‘SQL Server’. Write the name of the server and database under ‘Server’ and ‘Database’ dialog, respectively. Select your authentication mode, and provide the username and password for your data source. Select ‘Add’. 

SQL SERVER TO POWER BI: DATA SOURCE SETTINGS

Step 10: Now go to your workspace and select ‘Dataset’. Select your dataset from the list of datasets and in ‘More Options’ select ‘Settings’. 

Step 11: Select ‘Use a data gateway’ and then specify your gateway in the dropdown. Select ‘Apply’. 

SQL SERVER TO POWER BI: GATEWAY CONNECTION

Step 12: Now you can update your data and see the live changes. 

Drawback

  • You need to install Power BI gateway for this process.
  • Creating and managing a new data source is a difficult task.

Conclusion 

In this blog, you have learned various ways to connect SQL Server to Power BI. SQL Server is a database management system that maintains and manages your data. Power BI is used to take this data for further analysis and visualization and obtain better results. All the approaches discussed in this blog have their merits and demerits. You can use them according to your need. But if you want to avoid the complexities of ETL and concentrate more on your analysis and visualization, then let Hevo do that for you. 

Hevo, a No-code Data Pipeline offers a fully automated service for you. It supports pre-built data integrations from 100+ data sources. You just need to configure your source and destination and voila you are done!

Give Hevo a try by signing up for a 14-day free trial today

Let us know about your experience of connecting SQL Server to Power BI in the comment section below. 

No-code Data Pipeline for SQL Server