Microsoft Power BI as the name suggests is a Microsoft service for business analytics and data science, with interactive GUI capabilities. With Power BI, users can find useful insights from data obtained through various sources, without writing a single line of code. See here for details of our Power BI courses in London.
Power BI (all licences) supports a variety of data sources such as Blank Queries, Flat Files, SQL Server Database, OData Feed, Azure Cloud Platform etc. In this article, you will see how to connect to Power BI Desktop with SQL Server Database source, in order to import and visualize data stored in SQL Server. See this article for details on importing data into Power BI from Excel and Access.
In this article where you see text with an orange background that is a snippet of code.
Creating a Dummy Dataset
In this section, a dummy dataset will be created in SQL Server. In the next section, you will see how to connect Power BI with SQL Server and then import the dummy dataset into Power BI. We will create a simple database for a fictional book store. The name of the database will be BookStore. The following script creates our database:
CREATE DATABASE BookStore
The database will contain three tables: Books, Categories and Authors. The following script creates the Books table:
The Books table contains four columns: Id, Name, Price CategoryId, and AuthorId.
Similarly, execute the following script to create the Categories and Authors tables:
Let’s now insert a few dummy records in the Categories table.
Similarly, run the following script to insert records in the Authors table.
Finally, to add dummy records in the Books table, run the following script.
Note: Since the Id column of the Books has Identity property, we do not need to specify the value for the Id column.
Connecting Power BI with SQL Server
We have created a dummy dataset; the next step is to connect Power BI with SQL Server and then import the BookStore dataset into Power BI.
Downloading and Installing Power BI
If you already have PowerBI installed on your system, you can skip this step. Else go to this link:
https://www.microsoft.com/en-us/download/details.aspx?id=58494 , and download executable file for Power BI according to your platform. Run the executable file to install desktop version of Power BI. The installation process extremely, you just have to click “Next” on all the dialogue boxes that appear. Once the installation is complete, you should see the following window:
Click on the Finish button to complete the installation. Once Power BI is installed, you will need to sign in to Power BI Desktop, using your Microsoft Account. The Sign in/ Sign up window looks like this:
If you have not setup your Microsoft account Power BI gives you an option to signup for then Microsoft account. Once you create your Microsoft account and login to Power BI desktop, you should see the following dashboard:
Connecting Power BI Desktop to SQL Server
Connect Power BI to SQL Server basically refers to importing dataset from an SQL Server database to Power BI. To do so, you have to follow these steps:
1. From the top menu, click on the “Get Data” option. A drop-down list will appear displaying all the available data sources for Power BI as shown in the following screenshot.
From the above figure, you can see that there are lot of different options for importing datasets depending upon the data source. We want to connect to SQL Server and fetch data from a SQL Server database, therefore click on SQL Server option from the above drop-down list.
2. You should see the following dialogue box.
Enter the name of the Server and the Database name that you want to access. Enter your server name in the Server field and in the Database field, enter “BookStore” i.e. the dataset that we created earlier. If you do not specify the database name, all the databases on your server will be loaded. Click OK button. If the connection is established successfully, you should see the following window:
3. Here you can select and load the tables you want by simply clicking the checkboxes. Click the “Load” button to load the dataset into Power BI. Look at the following screenshot for reference.
4. Once the database tables are loaded, you will be able to see them on the right-hand side of the screen. You can select any categories from table names. For instance, we select the id and name columns from the Books table as shown below:
5. Finally, from the visualization tab, you can select the type of plot that you want to plot against the selected columns of the books table. For instance, if you select the stacked column chart, you will see the following graph.
You can see the bar plot for book names against their prices.
Connecting Power BI to SQL Server Via SQL Queries
You can also execute SQL queries in Power BI to retrieve the data that you want from SQL Server databases. To do so, while connecting to SQL Server database, go to “Advanced Options” and then type your query in the query window. For instance, the following query selects all the records from the Books table of the BookStore database.
In the output, you will see all the records from the Books table as shown below.
You can click the “Load” button to load these records into Power BI.
Microsoft Power BI is a data visualization and analytics tool that receive input data from various data sources such as SQL Server, Flat File, OData Feed etc. In this article, you saw how to connect Power BI with SQL Server and load data from SQL Server into Power BI. You also saw how to use SQL Queries in Power BI to retrieve filtered data.