In our previous Power Bi tutorial, you learnt how to import data from the Excel workbook to Power BI.
However, in practice, you will seldom land a client using MS Excel for his data in this dynamic age. Therefore, you need to look into how to import data from database management systems.
In this tutorial, you will learn about importing the Data from MySQL and SQL Server.
Importing Data from SQL Server
SQL Server is a relational database management system used to manage databases on the same computer or across a network of computers.
If this is your first time using an SQL server, create a dummy database by pasting the following script.
CREATE DATABASE StudentDB
USE StudentDB
CREATE TABLE Students
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Age INT,
Gender VARCHAR (50),
Department VARCHAR (50) NOT NULL,
)
Now, add some records to the student table. For this, execute the following script:
INSERT INTO Students
VALUES ( 'Jon', 18, 'Male', 'Engineering'),
( 'Mike', 19, 'Male', 'Law'),
( 'Nick', 19, 'Male', 'Literature'),
( 'Sally', 20, 'Female','Engineering'),
( 'Laura', 22, 'Female','Engineering'),
( 'Elisabeth', 18, 'Female','Law'),
( 'Jane', 17, 'Female','Law'),
( 'Josh', 20, 'Male','Engineering'),
('Will', 19, 'Male','Literature'),
('Rachel', 22, 'Female','Literature')
Now, add some records to the student table. For this, execute the following script;
Now import this data into the Power BI with the following steps.
Step 1:
Choose options from the top of the screen “SQL Server” or the screen from the option “Import from SQL Server.”
Step 2:
Enter the name of your Server and Database name and connect to the database.
Here you can see two options as “Import” and “Direct Query”.
- Import option means that you are copying an image of the database. Any changes that you will do the database will not get updated in the Power BI.
- Whereas Direct Query means that if any queries are executed on the database, they will also change the data imported in Power BI. This way, you will always have an up to date data to work.
Click on “Ok.”
Step 3:
Power BI will display the Dataset that it will import, giving you options to “Load” or “Transform.”
Choose Transform if you want to make any changes. For this moment, we are going to “Load” it simply in the system.
Importing from MySQL
MySQL is another commonly used DBMS around the Globe. You may have learnt it in your academic years in Computer Science. It is better if you already have a database, which you can use to import data. If not, you can execute the following script.
CREATE DATABASE studentDB
CREATE TABLE students
(
Id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT,
Gender VARCHAR(50),
Department VARCHAR(50) NOT NULL
)
INSERT INTO students (Name, Age, Gender, Department)
VALUES ( 'Jon', 18, 'Male', 'Engineering'),
( 'Mike', 19, 'Male', 'Law'),
( 'Nick', 19, 'Male', 'Literature'),
( 'Sally', 20, 'Female','Engineering'),
( 'Laura', 22, 'Female','Engineering'),
( 'Elisabeth', 18, 'Female','Law'),
( 'Jane', 17, 'Female','Law'),
( 'Josh', 20, 'Male','Engineering'),
('Will', 19, 'Male','Literature'),
('Rachel', 22, 'Female','Literature')
The following steps to import the database are also quite similar to SQL Server.
Choose options from the top of the screen “Get Data.” A window will open displaying all the possible connections you can make from Power BI.
Choose “MySQL Database” from this list.
You may get the error of not having the connector so that you can download it via the following link: https://dev.mysql.com/downloads/file/?id=412152.
Step 2: Enter the name of your Server and Database name and connect to the database.
Here you can only import the image of your data. Hence, it doesn’t show you the option of the direct query.
Click on “Connect.”
Step 3:
Specify the username and password of your server.
Step 4:
Power BI will display the Dataset that it will import, giving you options to “Load” or “Transform.”
Choose Transform if you want to make any changes. For this moment, we are going to “Load” it simply in the system.
And here you have it, your data ready for reporting.
See below to read more about Power BI:
- Power BI Tutorial: (01) Introduction to Microsoft Power BI
- Power BI Tutorial: (02) Features of Power BI
- Power BI Tutorial: (03) Installing Power BI Desktop the Best Way Possible
Power BI Tutorial: (4.1) Data Sources and How to Import from Excel Workbook in Power BI
About the Author:
Arsala Zahid is a Software Engineering graduate and a passionate content writer. She aims to make these technologies easy to learn so that you get motivated to gain a skill up your sleeve.
