We have the answers to your questions! - Don't miss our next open house about the data universe!

Seamlessly Connect Power BI and SQL Server: A Step-by-Step Guide

- Reading Time: 3 minutes
Power BI and SQL Server

SQL Server and Power BI are two complementary tools for data analysis. On one hand, SQL Server is a database management system (DBMS) that allows for storing, controlling, sorting, and updating databases using the SQL language. On the other hand, Power BI is a business intelligence tool designed to help organizations make informed decisions using data. So, how can you connect SQL Server and Power BI? Discover three approaches to achieve your goals.

SQL Server and Power BI connected by a script

To connect Power BI and SQL Server using a script, you can use R or Python. Here, you will find a method based on the Python language.

Step 1: Install these libraries using Pip in the command terminal:

pip install pyodbc

pip install pandas

pip install matplotlib

  • The pyodbc library provides access to ODBC (Open Database Connectivity) databases.
  • The pandas library allows for data manipulation and analysis.
  • Finally, matplotlib allows you to plot graphs and charts directly from pandas.

Make sure to check where the Python Home Directory points to. To do this, go to ‘Files’ and then ‘Options and Settings’. Click on ‘Python Scripting’ and verify that ‘Python Home Directory’ points to your Local Python Installation Path and your ‘Python IDE‘ on which the system executes the script.

Step 2: Select ‘Get Data’ from the panel at the top of your screen.

The ‘Get Data’ panel opens. Then, select ‘Other’ and then ‘Python script’. Click on ‘Connect’.

Step 3: In the dialog box, enter the following script:

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 SQL Server using the ‘pyodbc’ library installed earlier and uses this connection to retrieve data from our table. These data are then converted and loaded into Power BI.

Step 4: In the ‘Navigator’ panel, select the result and click on ‘Load’.

You will then have your data, which you can visualize as desired.

Using a connector for Power BI and SQL Server

In this method, you are using only Power BI and a connector.

1. Open Power BI Desktop on your computer.
2. In the panel at the top of your screen, click on “SQL Server.”
3. Enter the server name in the “Server” dialog box of SQL Server database. Select “DirectQuery.” In the “Name” dialog box, you can specify the name of your database.
4. Click “OK.”

You will then have your data table, and you can visualize the data according to your needs.

This approach is convenient if you have already established a connection between Power BI and SQL Server.

Creating the connection between these two entities can sometimes be a bit cumbersome. The other drawback occurs when you select ‘Import’ as your Connectivity Mode. In this case, all the data is imported, making it challenging to fully utilize Power BI’s visualization potential.

Export SQL Server data to Excel and import into Power BI

This process can indeed be time-consuming, especially if you perform this task regularly. Nevertheless, this old-school method works well.

Step 1: In SQL Server, right-click on the database containing your future table. Then, click on ‘Tasks’ and ‘Export Data’.

Step 2: A panel opens: ‘Choose a Data Source’. Select ‘SQL Server Native Client 11.0’ as the data source and click ‘Next’.

Step 3: Click on ‘Microsoft Excel‘ for the destination. Specify the file path and name in the ‘Excel file path’ dialog box. Then, click ‘Next’.

Step 4: If you want to export specific data, select ‘Write a query to specify the data to transfer’. Click ‘Next’.

Step 5: In the ‘Provide a Source Query’ panel, enter the following script:

SELECT * FROM tablename;

Click ‘Next’.

In the ‘Select source tables and views’ and ‘Review data mapping type’ panels, click ‘Next’. Then, select ‘Run immediately’ in the ‘Save and Run Package’ panel and click ‘Finish’.

Step 6: Open Power BI and select Excel in the panel at the top of your screen. Find the exported file from SQL Server.

Step 7: In the ‘Navigator’ panel, select your data and click ‘Load’.

You will then see your data appear in Power BI.

Conclusion

There are several ways to connect Power BI and SQL Server, each with its own advantages and disadvantages. You can use an R or Python script to establish the connection, use a specific connector, or export SQL data to Excel and import it into Power BI. The method to use depends on your preferences and specific needs.

By using one of these approaches, you can easily connect SQL Server and Power BI to access your data and gain insights to improve your decision-making.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox