In this article, we will learn to create a Power BI report using MySQL data. The article will teach us to use the MySQL Workbench (something like the SSMS, but for MySQL). We will do the following in this article.
- First, we will connect and create a database in MySQL.
- Secondly, we will create a schema (database).
- Thirdly we will create and import data into a MySQL table.
- Also, we will connect to Power BI and load data from MySQL.
- Finally, we will create a Power BI report using MySQL data.
- First, you need to install MySQL on your Windows Machine. This tutorial is oriented to Windows environments make sure to install the MySQL Workbench which is included in the installer.
- Finally, you need to have Power BI Desktop installed.
First, open the MySQL Workbench and press the + icon to open a new connection.
Secondly, write a connection name of your preference, write the root password, and press OK. The root password is set during the MySQL installation.
Thirdly, the software will ask you for the root password. Write a password and press OK.
Finally, you will be connected to MySQL using Workbench
Now, create a new MySQL schema. First, go to the Schema tab.
Secondly, right-click and select the Create Schema option
Finally, write a name for the schema. In this example our schema name is games. Press the Apply button. In MySQL the schemas and the databases are synonymous.
To create a table, we will import data from the players.csv file. Download the file in your machine. First, right-click the Tables in the games schema and select the Table Data Import Wizard option.
Secondly, press the Browse button and select the players.csv file, and press Next.
Thirdly, select the Create new table option. Optionally, you can select the Drop table if exists if you want to delete an existing table with the same name.
In the configure import settings, you can set the encoding and the data type (field type). In this example, we will not modify these properties. Press Next. For more information about data types, refer to this link.
In addition, in the import data settings, press Next.
Next, in the Import Results windows press Finish.
Finally, to verify that the data was imported successfully, right-click on the player's table just created in Tables and select Select Rows – Limit 1000.