Connect Power BI to SQL Server: Step-by-step Guide

Written by Andy McDonald
Reviewed by Johannes Åkesson
Dec 24, 2024
8 min read

Power BI makes it easy to visualize your data, but first, you need to connect it to SQL Server. This guide walks you through the process step -by -step. With SQL Spreads Excel Add-In, business users can bring in and update SQL Server data themselves — all from Excel — ensuring that your Power BI dashboards include all necessary data.

How to connect Power BI to SQL Server

Building dashboards and reports in Power BI requires two key steps:

  1. Get and transform your data — This involves getting all your data into a SQL Server database and specifically, how to connect Power BI to SQL Server.
  2. Design your dashboards and reports — Once your data is ready, you can create the visualizations your team needs.

This guide focuses on the first step: getting your data into SQL Server and connecting it to Power BI. We’ll walk you through the process, making sure you can centralize your data and use it effectively in Power BI. 

Here’s a preview of the steps we’ll cover in this guide:

By the end of this guide, you’ll have the knowledge to connect SQL Server to Power BI and keep your dashboards accurate and up to date.

Tired of business users’ requests to get data into SQL Server for their Power BI dashboards? Get them to update SQL data themselves with SQL Spreads Excel Add-In – easy and controlled.

Use Case

To demonstrate this in practice, let’s consider a use case: creating a KPI dashboard in Power BI using data collated into a single SQL Server table

KPI dashboards often require data from multiple, scattered sources, and while many organizations aim to automate the process of gathering, calculating, and collating KPIs, much of it is still done manually — often in Excel.

 This is our sample data that we will use:

Sample KPI Data in Excel
  • This set of KPIs relates to customer service, with target values and monthly updates for actual values and comments on variances. 
  • Each month, the Customer Services Manager updates this data and presents the results to upper management through a dashboard.

Centralizing the data in SQL Server makes it easier to manage, keep up to date, and integrate into your Power BI reports.

Step 1: Get all your data into SQL Server

We’re going to create the data in an Excel spreadsheet and then import it into a table in SQL Server using the SQL Spreads Add-in for Excel.

Here’s how SQL Spreads makes this step easy:

  • It’s an Excel Add-In that connects to SQL Server. No coding required: set up is quick and easy, with out of the box functionality, saving you both time and money.
  • An intuitive interface using familiar Excel.
  • Once your table is created, you can share the document with your business users who can start to update the data in the SQL Server table as often as required.
  • Worried about users messing up your data? With SQL Spreads, Designers control everything, while Data Editors are limited to safe, specific edits. Find out more about the Designer and Data Editor roles.

First, install the SQL Spreads Add-in for Excel. You can download a free trial of SQL Spreads from here.

  1. Run the SQLSpreadsSetup.exe file and follow the instructions.
  2. Restart Excel and accept the Add-In confirmation.
  3. You will now find SQL Spreads in the tab menu in Excel:
    SQL Spreads menu tab in Excel
For more details about installation, check out the Installing SQL Spreads section of our Knowledgebase.

Next, we need to create our data in Excel. There are 8 KPIs that we’re going to report on each month. Here is a copy of the Excel file if you want to follow along.

Once the data has been created in Excel, we can use the SQL Spreads ‘Table Creator’ feature to create the table in SQL Server. The SQL Spreads’ “Table Creator” feature is a much easier way to create tables in SQL Server.

  1. Convert the data that you’ve added into an Excel table by simply selecting one of the data cells and pressing CTRL + T.
    Sample KPI Data in Excel - Table
  2. Click the Create new SQL Table button. If this is the first time you are using SQL Spreads or the Table Creator, you will see the dialog to connect to your SQL Server.
    SQL Spreads Table Creator
    Fill out your server details and click OK.
  3. Once you are connected to SQL Server, you will see the Create SQL Server Table dialog. Enter a table name and choose which SQL Server database to create the table in. SQL Spreads will always remember the last used table. If you need to change to another SQL Server click the Edit button next to the server name.
    Create SQL Server Table kpi_demo
  4. Click OK. A confirmation message will appear confirming the table was created and how many rows of data were inserted into the table.
    SQL Spreads New Table Created Confirmation
    We can check that the new table has been created and that our data is there by running a query in SQL Server Management Studio.
    SQL Server New Table Created SSMS
  5. You can now close Excel. In Step 3 later on, we’ll explain how to use SQL Spreads to connect to the table in SQL Server that we just created and perform updates in Excel that we can save back to SQL Server.

The next step is to connect Power BI to SQL Server and create a Power BI report.

Did you see how easy that was to create a SQL Server table? Quick, easy, and well on your way, that’s the power of SQL Spreads.

Ready to take the plunge?

Try out the features

Step 2: Connect Power BI to SQL Server

– Create a new report in Power BI Desktop

Open Power BI Desktop, click on ‘Get Data’ and then ‘Import Data from SQL Server’.

Add data to your Power BI report

In the dialog, select the Server and Database (optional). The Data Connectivity mode defaults to ‘Import’ – leave it on this option.

The import option actually loads the data into the Power BI file, making it quicker to work on, but is not suitable for very large datasets. The alternative Direct Query option doesn’t load the data into Power BI, but instead runs queries against the source data each and every time a visualization is refreshed or a filter changed
Connect Power Bi to SQL Server

You can now select the SQL table that we just created. We won’t do any transformations in this example, so you can click ‘Load’.

Power BI Connect to SQL Navigator

Once the data has been loaded, you’ll see the tables in the Fields pane on the right-hand side. We also have a blank canvas to work with and the Visualizations pane to select our charts and other visualization types from.

Power BI Blank Canvas

We can now go ahead and create the visualizations that we need for our report. Check out our earlier article on creating reports using Power BI for more information on how to add the most common visualization types.

Here’s a simple dashboard based on the sample customer service KPI data:

Power BI Customer Services KPI Report

We now need to publish the report to the web-based Power BI Service so that our business users can access the report.

– Publish the Report to the Power BI Service

Make sure you have saved your report and then from the Home tab, click on Publish > My Workspace > Select. Sign in to the Power BI service if you’re asked to do so.

Once the report has been published you can click on the link in the dialog box.

Power BI Publish to service

– Configure the dataset settings

In Power BI Desktop, we connected directly to our on-premises SQL Server database. However, because the Power BI service is a cloud-based app, it needs a data gateway to act as a bridge between the cloud and our on-premises network. We therefore need to create a gateway on a local machine, add our SQL Server database as a data source, and then connect our Power BI dataset to this gateway.

To install a gateway on your local machine, follow the instructions here – for testing purposes you can install the ‘On-premises data gateway (personal mode)’ option.

Once you’ve completed the process of installing and configuring the gateway you can review details and troubleshoot issues by clicking on the ellipsis to the right of a dataset name and selecting ‘Settings’, or by viewing from the My Workspace > Datasets page.

Power BI Gateway Connection

– Configure the refresh interval

Now that we have our Power BI dataset connected to our on-premises SQL Server database via the gateway, we need to create a refresh schedule. The refresh schedule means that our report will remain up to date as the source data changes.

Go to My Workspace > Datasets. Select the ellipsis (. . .) for your dataset, then select Schedule refresh.

Power BI Scheduled Refresh

You can select the refresh frequency on a regular schedule, e.g. daily, or customize to several time slots.

  • Normal Power BI subscription allows: up to 8 daily time slots
  • Power BI Premium subscription allows: 48 time slots

Enabling the scheduled refresh feature also allows you to perform a manual refresh on-demand (note that doing this will contribute to the count of daily refreshes you are allowed). To perform a manual refresh, select the dataset in the navigation pane, click the ellipses menu and select ‘Refresh Now’.

With SQL Spreads, it’s easy for your business users to add valuable data to your Power BI reports straight from Excel. 

Step 3: Ensure the data is up to date

In the previous steps, we loaded our data into a SQL table and created a report in Power BI. We’re now going to look at the process for ensuring that the data for our Power BI report is up to date.

Open Excel and click on the Design Mode button in the SQL Spreads menu. A list of databases will be displayed, and you can select the Demos database and then the kpi_demo table. When you select the table, SQL Spreads will connect to SQL Server and import the data from the table into Excel.

SQL Spreads Load KPI_Demo data
With the data loaded in Excel, we can now make changes to any of the existing records or add new rows. In our example, we’re going to assume that we’re submitting the report for the month of December.

We therefore need to copy the rows for each of the KPIs from November and paste them as new rows at the bottom of the table. We can then change the month for each of the new rows to be December, and also add in the new actual KPI values and comments.

SQL Spreads Save to Database KPI_demo

Once you’ve made all the changes, click ‘Save to Database’ – all of the updates will be written to SQL Server.

We can now go to our dataset in the Power BI service and perform a manual refresh. The KPIs for December, including the actual values and any comments that were added can now be viewed.
Power BI Customer Services KPI Report updated

Get all your data in Power BI using SQL Spreads

In this article, we’ve shown how to connect Power BI to SQL Server. Often, a challenge associated with SQL Server data is that there is usually important data that lies outside of the database. It is easy to lose your mind trying to centralize this data yourself.

With the SQL Spreads Excel Add-in, business users can easily update and insert this data into SQL Server directly from Excel, no complex development required. Once your data is in SQL Server, connecting and creating a Power BI report is quick and straightforward.

The real value of SQL Spreads is how it lets your team update data in SQL Server from within Excel, with changes instantly reflected in your Power BI reports. This ensures all the necessary data is in SQL Server, streamlining your dashboard creation and reducing errors.

Get started today. Download a free trial of SQL Spreads to start getting all of your data into SQL Server for accurate Power BI dashboards.

Try it for free

To learn more about how SQL Spreads can help you with your data needs, download a free trial and get started straight away.

 

Try SQL Spreads for free

Try SQL Spreads during 14 days for free. The free trial is the fully
functional, time-limited only product.
Try Excel Add-In for Free download-image
stars-image
14-days Free Trial. No credit card required.