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.
Get Data
- 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:
-
- FactResellerSales
- DimDate
- DimProduct
- DimProductCategory
- DimProductSubCategory
- DimSalesTerritory
- DimGeography
-
- 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”
All done!
This is missing info about connecting to a remote database. fields such as connection method and port are missing from PowerBI. Is microsoft assuming that all databases are localhost?
Hi Bill.
Power BI Desktop supports On-Prem MySQL that means you can connect from Power BI Desktop to a server in your local network or a network with trust relationship.
If you want to connect to a MySQL instance other than localhost then you can put the IP address follows by 3306 which is MySQL default port number.
So the server name would be something like this: 192.168.xxx.xxx:3306.
So as you can see you don’t have to always connect to a MySQL instance using localhost.
Cheers
please explain how we can do this getting data from Postgresql
i have a web App on a linux vps, the web App using Postgresql as the db. I want to visualise matrix reports from the web App.
i have a Microsoft Power BI Pro web App account.
i also have a free Microsoft Power BI Desktop app on a local windows pc.
if i create a .pbix file using Microsoft Power BI Desktop and it setup the .pbix file to grab data from my linux vps web App’s Postgresql db, and then upload the .pbix file to my Microsoft Power BI Pro web App account, then setup auto-refresh every 5mins so that Microsoft Power BI Pro web App account only grabs data from my vps Postgresql db through the .pbix file
is this possible ??
if its possible, please walk me through the steps
everyone will benefit from your knowledge
Hi Soheil Bakhshi,
First up all thanks for the nice post, which one i was expecting too.
while i am trying to connect to my local MySql it shows the below exception :
we encounter an error while trying to connect.
Details: “MySQL: We were unable to find a database provider with invariant name ‘MySql.Data.MySqlClient’.
This error could’ve been the result of provider-specific client software being required, but missing on this computer. To download client software for this provider, visit the following site and choose the 64-bit (x64) version of, at minimum, ‘MySQL Connector/Net’: https://go.microsoft.com/fwlink/?LinkId=278885“.
it’s not working after i was installed the client software in my laptop.
can you please share the remedy for this one.
Thanks
Palani Om
I want to update more than 8 times. How do I use MYSQL?
Hi Eduardo,
Welcome to BIInsight.
Not entirely sure what you mean by updating more than 8 times.
If your question is in regards to the number of scheduling data refresh, then 8 refreshes is the best you get under Power BI Pro license unless you require an Enbedded or Premium capacity. But the latter two are quite expensive and may not be suitable for everyone.
Read more here: https://docs.microsoft.com/en-us/power-bi/service-premium
i try to insert IP address for local server to get mysql database but it popup message error “MySQL: Host ‘DESKTOP-S64DFGP’ is not allowed to connect to this MariaDB server”.
Hi Shukri and welcome to BIInsight.com.
Please make you can the IP address following by 3306 which is MySQL default port number.
So the server name would be something like this: 192.168.xxx.xxx:3306.
If that doesn’t work doubleckeck with your IT department and make sure the port number hasn’t been changed.
Cheers.
Hello Soheil,
The power BI cannot connect to mysql database. Here is what it says:
“We encountered an error while trying to connect.
Details: “An error happened while reading data from the provider: ‘Could not load file or assembly ‘Renci.SshNet, Version=2016.1.0.0, Culture=neutral, PublicKeyToken=1cee9f8bde3db106’ or one of its dependencies. The system cannot find the file specified.'”
I am using xampp. What do you think are possible solutions? Thank you
Hi Jeff,
Welcome to BIInsight.com.
Please check this post in the Power BI community forum for the same issue.
Hope it helps,
Cheers.