Power BI is a great way to visualize your data. In previous articles, we’ve introduced Power BI dashboards and gone through a step-by-step example to create a Power BI report using data from SQL and Excel. In this article, we’re going to look in more detail at the process for how to connect Power BI to SQL Server.
Introduction
Creating dashboards and reports in Power BI is a two-step process. The first step involves getting and (optionally) transforming your data. The second step is where you create the dashboards and reports for your users to view. We’re going to focus on getting the data, and specifically how to connect Power BI to SQL Server.
The example that we’ll work through is a typical use case: we’re going to create a KPI dashboard in Power BI using KPI data sourced from various places and collated into a single table in SQL Server. It’s often the case with things like KPI dashboards that there isn’t a single neat place or places from which to pull the data. Whilst a lot of organizations aspire to an automated process to gather, calculate and collate KPIs, it’s common for there to be a large amount of manual processing to be done. This manual collection and processing is often done in Excel.
Here is a sample of the data that we’re going to use:
As you can see, we have a set of KPIs relating to customer service. Each of these has a target value, and every month the actual value needs to be entered, as well as comments to explain any variances. We’re going to assume that every month the Customer Services Manager updates this information and presents the results to upper management through a dashboard.
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.
First, install the SQL Spreads Add-in for Excel. You can download a free trial of SQL Spreads from here.
- Run the SQLSpreadsSetup.exe file and follow the instructions.
- Restart Excel and accept the Add-In confirmation.
- You will find SQL Spreads in the tab menu 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.
- Convert the data that you’ve added into an Excel table – just select one of the data cells and press CTRL + T (hold down the CTRL-key and press T)
- 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.
Fill out your server details and click OK. - 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.
- Click OK. A confirmation message will appear confirming the table was created and how many rows of data were inserted into the table.
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.
- 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.
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’.
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).
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’.
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.
We can now go ahead and create the visualizations that we need for our report. Check out our earlier article 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:
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.
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.
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.
You can select the refresh frequency (e.g. daily) and configure up to 8 daily time slots if your dataset is on the normal Power BI subscription or 48 time slots on Power BI Premium.
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’.
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.
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.
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.
Summary
In this article, we’ve looked at how to connect Power BI to SQL Server.
To get our data in SQL Server we used the SQL Spreads Excel Add-in to easily create a new SQL table. We then created a basic report in Power BI using the standard SQL Server data source connector. This is a quick and easy process.
The great advantage of using SQL Spreads is that it makes it easy for your business users to update the underlying data in SQL Server from within Excel and see the changes straight away in their Power BI reports. This is a powerful feature and makes it quicker and easier to manage your SQL data and Power BI reports.
To learn more about how SQL Spreads can help you with your data needs, download a free trial and get started straight away.
