In this blog page I will show how I get reporting from MySQL database.

It is possible that there are other ways to get reporting too. This is just how I did it.

To start with, you have a simple sales report.

The dimensions are running ID number, material code, sales region and sales volume.

We could simply use the Excel as source of data for our data model. Instead of copy pasting the data to Excel, we use MySQL database.

Create a database and a table corresponding to data in the report.

Right-click the table and select table data import wizard.

Select the database table.

The fields in the file are mapped with fields in the empty table.

Now the table is populated. A good thing with SQL database, is that we can check the database with an SQL script. Note, that the database is called excel_demo.

Now we should have connection with database and Excel to get reporting done in Excel.

https://dev.mysql.com/downloads/connector/odbc/

I downloaded here the open database connector.

This is how the page looked like for me.

Search for ODBC data sources.

Press add.

Add the excel_demo database.

Select MySQL ANSI driver and press finish.

Data source is our database. TCP/IP and port are fixed values.

Press test.

The test was successful.

Excel_demo appeared to the list.

Now we can open Excel and start working on reporting side.

In Excel take data – get data – from other sources – from ODBC.

Select excel_demo from the dropdown list. Click OK.

Select database from left hand side. Enter the user name and password. Press connect.

All the available databases in MySQL are visible. We are interested in excel_demo.

Press transform data.

Check the data and data types for fields.

Close & Load – Close & Load To

Only create connection and add this data to the data model.

Create a measurement to sum up the sales volumes.

Sales per region.

Let’s check the data in MySQL.

The values match.

Another option is to use data directly from MySQL database without ODBC.

Select get data – from database – from MySQL dataset.

Localhost as server and excel_demo as database.

Transfrom data.

Close & Load – Close & Load to.

Only create connection and add this data to the data model.

Now the table name is database name + table name.

Creating a measure to sum up the sales volume.

The process is pretty similar in Power BI side, too.

Connection between Excel/Power BI and MySQL can be done via both ODBC and directly to database.

An advantage with MySQL is that you can paste data with CSV-files to MySQL tables. When you have the data in MySQL, the data can be manipulated and reviewed with SQL script. Still, the reporting can be done in Excel or in Power BI.

After the connection is in place, we can briefly analyse data.

Even though the sales were divided quite evenly between the regions, there are differences per product. None of the products are sold evenly between the regions. Also volumes per per product are varying between 9 and 45.