Microsoft PowerBI integrates with MySQL out of the box. However, loading data into PowerBI to create MySQL visualizations is not straightforward—especially if MySQL is running in the cloud. Learn how the PowerBI integration works, including a quick guide to setting up MySQL as a data source, and a few alternative options to visualize MySQL data quickly and easily.What is PowerBI?
PowerBI is Microsoft’s popular Business Intelligence platform, available both on-premise (PowerBI Desktop) and on the cloud (PowerBI Online).
PowerBI’s capabilities include direct access to multiple data sources, real-time data updates, easy analysis with natural language queries, and ability to share reports and dashboards across the organization. PowerBI visualizations include charts, KPIs, maps, scatter plots and bubble charts.
How Does the PowerBI-MySQL Integration Work?
PowerBI integrates out of the box with MySQL, a popular open source relational database. This makes it possible to import MySQL data, analyze and visualize it within the PowerBI platform.
PowerBI’s MySQL connector works with the desktop version of PowerBI only. If you want to create reports and scheduled data updates on PowerBI Online, the process is:
- From PowerBI Desktop, connect to a MySQL database (deployed locally or in a location accessible from the desktop computer) and pull a selected table into a .PBIX file.
- You can already analyze the data within PowerBI Desktop.
- Login to PowerBI Online and pull the PBIX file from your local desktop instance.
- Create a report based on the data from MySQL and add it to the PowerBI dashboard.
- Schedule a regular data refresh to update the report from the PBIX file.
Important to note—PowerBI desktop is an essential step in the process. You have to maintain the desktop instance and its connection to the local MySQL database to enable ongoing data refreshes, for up-to-date MySQL data visualizations.
Step by Step: Connecting PowerBI Online to a MySQL Database
- Follow these steps to connect PowerBI Online to a MySQL Database:Install PowerBI Desktop on a machine that has access to your MySQL database, open PowerBI Desktop and select Get Data > Database > MySQL Database.
- Enter the network hostname on which the MySQL database is running, the database name, and your credentials.
- The PowerBI Navigator shows a list of all tables in the database—select the checkboxes for the tables you want to load into PowerBI.
- Create a report in PowerBI using the MySQL data, and save it with a meaningful name. The report will generate a PBIX file. You can Publish the PBIX file from PowerBI Desktop to make it more easily accessible from PowerBI Online.
- Create a simple report using the data in the PBIX file.
- Schedule a Refresh.
You're done! The data is available in PowerBI Online and will be automatically refreshed from the MySQL database at the times you specified.
Challenges with PowerBI MySQL Connector
There are a few inherent limitations of the PowerBI MySQL connection:
- MySQL integration requires PowerBI Desktop—data sources can only be accessed via PowerBI Desktop, which has restrictive system requirements—according to documentation, it only runs on Windows 7 or 2008, .NET 4.5 and Internet Explorer 9.
- MySQL data cannot be accessed directly from the cloud—PowerBI can pull data from data sources directly accessible from the desktop machine. Microsoft also offers an on-premise gateway which allows remote access to a local machine. To access data stored on the cloud, the local machine must connect remotely to a cloud service like Amazon RDS or Aurora, and pull data over the network, which is highly inefficient.
- Data connection is unstable—a fault in PowerBI Desktop (or the gateway), a disrupted connection between the local software and local data source, or a disrupted connection between PowerBI Desktop and PowerBI Online, can cause data not to refresh.
- PowerBI Desktop and Gateway are sometimes unreliable—some users report difficulty installing the software, or report errors while connecting to MySQL data sets.
Alternatives to PowerBI Connector when Running MySQL in the Cloud
Because it can be difficult and unreliable to connect PowerBI to MySQL data in the cloud, consider using a cloud-based data integration service. There are several services that are pre-integrated with common data sources, and can grab data from MySQL and move it seamlessly into PowerBI. Three examples:
- Stitch—a cloud data service that pulls data from multiple pre-integrated data sources, including MySQL, transforms and cleans it as necessary, and can move it into PowerBI or other popular BI tools (or other destinations such as data warehouses).
- FiveTran—an automated data pipeline which can connect to MySQL and pull data to a data warehouse. From there FiveTran can move data into PowerBI or many other integrated systems.
- Cluvio—works with MySQL on AWS, accesses MySQL data and provides a SQL editor, interactive dashboards and scripting in R.
An Easier Way—Moving MySQL Data to an Automated Data Warehouse
Pulling data via the PowerBI-MySQL integration is not straightforward, and it’s only the beginning. You’ll rarely see data sets that are perfectly suited for analysis and visualization. In most cases you’ll need to clean and transform the data for analysis, and while PowerBI provides the ability to wrangle and transform data, it requires a significant manual effort.
A more efficient option is to pull your MySQL data, and any other data sets you wish to analyze in PowerBI, into an automated data warehouse like Panoply.
Panoply removes complexity by automatically pulling data from any source, including MySQL—whether on-premise or cloud-based. It then uses machine learning and natural language processing to automatically clean the data, optimize it and prepare it for analysis. Panoply comes pre-integrated with PowerBI, so after it ingests and processes the data, you can visualize it seamlessly within PowerBI.