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.
