In this tutorial, you will learn how Power BI connects to a SQL Server database and how to get data from there. SQL Server databases are one of the most common data sources for Power BI.
The first step is to open the SQL Server connector. Go to Get Data >> SQL Server.
The next step is to enter the SQL Server name and the database name. In this example, we will be using the AdventurWorks2014 database that can be found here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks. To use this database, you must download a backup version and then restore it into your SQL Server.
Note that there is another option that appears under Advanced options. Using this option, you have the ability to write a SQL query that will run in your SQL Server database.
We will not be writing any query in this example but just remember that the option is there. Click OK and the connection will be established. The Navigator window will show all Tables, Views and Functions that are in the database:
By scrolling down, you will be able to see all tables and functions.
From here, you can select a table to preview the items.
Choose the following two tables to load: Sales.SalesOrderDetail and Sales.SalesOrderHeader.
Click Load to load the data in the model.
To make sure the tables are related as they should be, go to the Relationship menu.
You will see that that there is a relationship automatically created. To see the relationship, double-click on the Relationship Like and review the tables in the Edit Relationship window. You will see that the tables are related based on the SalesOrderID column:
Now you are ready to start and create visualizations. Remember that if you need other data from other tables, you can easily go back and select the tables required. Go to Recent Sources and select the AdventurWorks2014 SQL database.
Select the Sales.Customer table.
And you can see below, this table is related to the previous two:
You can now use those customer details for creating visualizations.
It’s important to remember that you can go to the source at any time to load more tables necessary for your reports.
