MySQL and Power BI, How Does It Work?

MySQL and Power BI

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.

MySQL and Power BI

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:

MySQL and Power BI

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

MySQL and Power BI

  • Enter the server and database names then click OK

MySQL and Power BI

  • Click “Database” the n pass username and password then click Connect

MySQL and Power BI

  • For this sample I selected the following tables:
      1. FactResellerSales
      2. DimDate
      3. DimProduct
      4. DimProductCategory
      5. DimProductSubCategory
      6. DimSalesTerritory
      7. DimGeography
  • Click Load

MySQL and Power BI

MySQL and Power BI

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.

MySQL and Power BI

You can also modify relationships by clicking on the “Manage Relationships” button from the ribbon.

MySQL and Power BI

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.

MySQL and Power BI

I also successfully published the reports to my Power BI cloud account.

MySQL and Power BI

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
  • MySQL and Power BI
  • Click “Schedule Refresh”
  • MySQL and Power BI
  • 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”

MySQL and Power BI

All done!

10 thoughts on “MySQL and Power BI, How Does It Work?

  1. 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?

    1. 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.

      Connecting from Power BI Desktop to MySQL using IP Address and port number

      Power BI Desktop connection to MySQL using IP addrss and port number

      So as you can see you don’t have to always connect to a MySQL instance using localhost.

      Cheers

  2. 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

  3. 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

  4. 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”.

  5. 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

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.