Connect SQL Server to Power BI: 4 Easy Methods

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.

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
• Steps to Connect SQL Server to Power BI Using Custom Scripts
• Drawbacks

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.

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

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)

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’.

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

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
• Steps to Connect SQL Server to Power BI Using Connector
• Drawbacks

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.

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

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’.  

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

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.

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’.

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

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’.  

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

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

SELECT * FROM tablename;

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.

Browse the exported excel file.

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

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

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

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’.  

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

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

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

Step 6: Select the URL as shown.

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’.

Step 8: Select the gateway that you created while downloading the Power BI gateway. Select ‘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’. 

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’. 

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. 

Let us know about your experience of connecting in the comment section below.