This article focuses on the step-by-step processes of installing and configuring the ODBC driver for MySQL. In particular, it will cover the following issues:
- Installing the ODBC driver on Windows 10.
- Configuring the ODBC data source with ODBC driver for MySQL.
- Creating a basic list report with PowerBI that uses the ODBC driver for the data population from the MySQL database.
First, let’s clarify the process of installing MySQL ODBC drivers.
Using MySQL community installer, you can view, install, update, configure, and remove various MySQL products. That’s why we install ODBC drivers from the MySQL Community installer – it is available for download here. Once the process is complete, open the installer. On the initial screen, you see the list of MySQL software installed on the computer.
To install the ODBC driver, click Add.
On the new Select Products and a Features screen, you will see the list of MySQL products in various categories. ODBC drivers are available under the MySQL Connectors category.
Expand MySQL Connectors > Expand Connector/ODBC
Two versions of ODBC drivers are available. We want to install the latest driver.
Expand Connector/ODBC 8.0 and choose the most recent version of the ODBC driver. Click on the arrow sign.
The ODBC driver will move from Available Products to Products to be installed. Click Next.
On the Installation screen, you can view the list of MySQL products we are installing. Click Execute.
The installation starts. Wait for its completion, and once it’s done, click Next.
Now you can see the ODBC driver in the list of DSN.
Configure ODBC Driver for MySQL
Open Control Panel > Administrative tools > ODBC Data Sources (64-bit) > System DSN tab > Add.
This will launch the Create New Data Source wizard.
Select MySQL ODBC 8.0 ANSI driver from the list and click Finish.
The configuration wizard opens. There, specify the following connection parameters:
- Data Source name: the desired data source name.
- TCP/IP Server: the hostname or IP Address of the server where the MySQL database server is installed.
- Port: the appropriate port number of the server where the MySQL database server is installed.
- Named Pipe: If this parameter to connect to the database server is configured, choose it and specify its appropriate name.
- User: the username to connect to the MySQL database server.
- Password: the appropriate password for the user specified in the User field.
- Database: the MySQL database you want to connect.
When all parameters are set, click Test to check the connectivity to the MySQL database server:
If the configured parameters are correct, the connection will be established successfully.
Click OK to save the configuration and close the ODBC configuration wizard.
You will see the configured MySQL ODBC data source under the System DSN:
Generate a PowerBI report using MySQL ODBC driver
At this stage, we are going to create a PowerBI report using the MySQL ODBC driver. For demonstration purposes, I have installed MySQL 8.0 on my workstation and created a database named eltechDB on MySQL. Then, I created a table named tblemployee in that database. We will use the database view to generate the report, and I have also created a view named vwEmployees.
The following script creates the eltechDB database:
Create database eltechDBTo create the tblEmployees table, run this script:
CREATE TABLE `tblemployees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
)
This script creates the vwEmployees view:
CREATE
VIEW `eltechdb`.`vwemployees` AS
SELECT
`eltechdb`.`tblemployees`.`emp_no` AS `emp_no`,
`eltechdb`.`tblemployees`.`birth_date` AS `birth_date`,
`eltechdb`.`tblemployees`.`first_name` AS `first_name`,
`eltechdb`.`tblemployees`.`last_name` AS `last_name`,
`eltechdb`.`tblemployees`.`gender` AS `gender`,
`eltechdb`.`tblemployees`.`hire_date` AS `hire_date`
FROM
`eltechdb`.`tblemployees`
Once the database and objects are ready, download the PowerBI desktop version, install it, and open. Note: I don’t have a PowerBI license or subscription, but if you own it, log in your account with your credential.
On the Welcome screen, click Get data:
The new window that will open will display the list of various data sources, supported by the PowerBI report is here. To find the necessary data source, you can type the initials of its name. We are using ODBC drivers, so, type ODBC in the search field, select ODBC, and click Connect:
Select the EltechDB database from the Data Source Name (DSN) drop-down menu, and click OK.
On the Navigator screen, you can view the list of MySQL databases under the ODBC connection string. We are going to generate a PowerBI report to display the list of the employees from vwEmployees.
To select the necessary view, expand ODBC data source > EltechDB. Under the EltechDB database, you will see the list of available tables and views.
When you click on vwEmployees, you can preview the data in the right pane. Click Load to load the data into the PowerBI report:
After you’ve loaded the data in the report, you can view it by clicking on the table icon located on the left pane. If needed, you can add more fields from the report. For that, drag and drop the data fields located on the right pane of the report:
Summary
Thus, we have explored the process of installing the MySQL ODBC driver and learned to configure the data source using the MySQL ODBC driver. We’ve also created a basic list report in the PowerBI using the ODBC data source. Hope that this article was of help to you.
- A Quick Guide on Installing MySQL on Ubuntu - February 18, 2021
- Backup and Restore MySQL Database Using mysqldump - February 17, 2021
- Understanding the Process of an Azure Database Deployment on MySQL - February 17, 2021
