Update 1: At the time of writing this blog post (Aug 2015) Power BI Service called Power BI Web. I hope it doesn’t make any confusions.
Update 2: MySQL data source is available in “On-premises Data Gateway – Enterprise Mode” as well. So if you are setting this up for an organisation, then “Personal Mode” (AKA Power BI Personal Gateway) would not be suitable. When I wrote this blog post only “Power BI Personal Gateway” was available.
In this post I explain how to use MySQL and Power BI. This post covers the following areas:
- Get data from MySQL
- Schedule refresh on-premises MySQL from power BI web app
First of all I’d like to mention that in this post I use AdventureWorksDW which is imported into MySQL. If you want to do so you can use “Migration Wizard” from “Database” menu on MySQL Workbench.
I’m not going to explain the migration process as it’s out of scope.
How MySQL and Power BI work together
MySQL is one of the world’s most popular relational database management systems (RDBMS) widely used by the industry. It’s open source, works with many different system platforms including Microsoft Windows and Linux. So it is worth to have a look at it and see how it works with Power BI.
Luckily Microsoft provided the built-in connector in Power BI Desktop. This is how it works all together:
I’d like to say that it’s not necessary to create reports in Power BI Desktop. You can get data from a MySQL database then publish it to the Power BI cloud then setup a schedule data refresh in the Power BI web app. Then you can create your reports and dashboards on the cloud and share them with your colleagues very easily.
As we discussed before in one of my previous posts you can schedule a data refresh on several different data sources including MySQL. Loading data from MySQL is 99% similar to what we’ve done before on Data Visualisation with Power BI Desktop. As I stated earlier, I’m not going to cover all aspects of data visualisations on this post again, so if you are looking for a detail discussion about how to create reports and visualise your data with Power BI Desktop you can find it here.
- Select MySQL Database then click connect
- Enter the server and database names then click OK
- Click “Database” the n pass username and password then click Connect
- For this sample I selected the following tables:
- Click Load
So far we got data from MySQL. In real world we’ll need to tidy up the tables and fields to make the report more readable and more user friendly. I leave it to you.
Power BI Desktop automatically detects table relationships after getting data from MySQL. To see the relationships just click on the “Relationships” view.
You can also modify relationships by clicking on the “Manage Relationships” button from the ribbon.
Now it’s time to create some reports with Power BI Desktop.
OK, as you can see I created “Total Sales Amount”, “Sales by Product Category” and “Sales by Territory” reports.
I also successfully published the reports to my Power BI cloud account.
I encourage you to have a look at this post which expresses much more details about relationship management, creating and publishing reports and much more.
Schedule Refresh MySQL Database from Power BI Service
To be able to setup a schedule data refresh you need to install “Power BI Personal Gateway” on a machine in your network. For more information about “Power BI Personal Gateway” you can see “Refreshing On-Prem SQL Server Database On Power BI Using Power Bi Personal Gateway”. I also suggest you have a look at “Power BI Personal Gateway, Five Things You Must Know” as well.
To setup a schedule data refresh follow the steps below:
- Login to your Power BI account on Power BI website
- Find your MySQL from Datasets
- Click on “Open Menu” ellipsis
- Click “Schedule Refresh”
- Expand “Schedule Refresh”
- Click “Keep your data up-to-date”
- Select “Refresh frequency” as desired
- Choose your time zone
- Setup the time
- You can also add more refresh times by clicking on “Add another time”
- Tick “Send refresh failure notification email to me” if necessary then click “Apply”