Power BI and MySQL. The beloved BI tool and dependable database go together like peanut butter and jelly...or data and analysis.
To help make that powerful partnership easier, we'll walk you through two ways to connect Power BI and MySQL so you can put your data to work rather than just letting it gather dust.
How to use Power BI’s native connection to MySQL
Power BI desktop has a built-in integration with MySQL, so it’s pretty easy to get started. Just follow these simple steps:
Step 1.
Download Power BI Desktop.
Step 2.
Ensure you have credentials to your MySQL database. This should involve a “host” in the form of an IP address or domain name, a database username with read privileges, and a password.
Step 3.
In Power BI Desktop, click “Get Data,” then choose the “More” option at the bottom:
Step 4.
Click the “Database” option on the left, then choose MySQL database.
Step 5.
Under “Server,” enter the hostname of your database. If you’re using an Amazon RDS database, it should end in “rds.amazonaws.com.” For Azure users, it’ll be “database.windows.net.”
Your hostname may also be an IP address that maps to the server that hosts your database. Be sure that the IP address from which you are connecting to the server has permissions to access the database via the firewall.
Step 6.
Enter your database username and password. This username should have read permissions on the database. If you are a new MySQL user, be sure to create a username within the database and execute a “GRANT PRIVILEGES” command for the username to give it read permissions.
Step 7.
Choose the option for DirectQuery or Import. Which you choose will depend on how often you want to ping the database for information, or if you prefer to import the data locally into Power BI.
Generally speaking, DirectQuery is the way to go. That’s because DirectQuery pulls data from the underlying data source so it’s always fresh without the hassle of manually reimporting previous data. The downside of DirectQuery is that because it queries the data source directly, you may end up with performance issues for larger data sets.
Step 8.
Select the tables you want to import into your data model.
All done! This is the most straightforward way to connect Power BI to MySQL, but bear in mind that you’ll need to repeat this process for every data source you want to push into Power BI…assuming it’s supported.
How to sync all your data to Power BI
If MySQL is just one of many different data sources that you use, DirectQuery could still be a good option, but you’ll likely want to pair it with a data warehouse like Panoply. Storing your data in Panoply makes it easier for you to connect data sources like Salesforce, Google Analytics, Shopify, and Facebook Ads to Power BI because all your data is in one place.
Taking this approach means that Power BI becomes a reporting and visualization layer on top of your data warehouse, so you can avoid replicating your data model and create a single source of truth for your business.
Panoply is easy to set up and a breeze to use. To connect your MySQL database to the Panoply service, follow these steps:
Step 1.
Create a free trial account on Panoply or log into your existing Panoply account.
Step 2.
Click the “Data Sources” tab.
Step 3.
Click “Add Data Source” and select MySQL from the list of options.
Step 4.
Enter your server name and port for MySQL along with your MySQL username and password.
Step 5.
Select your MySQL database and click “Save Changes,” then “Collect.”
Step 6.
While your MySQL data is syncing to Panoply, you can set up your Power BI connection. Start by opening the BI Connection tab in Panoply, then click “Get Data” in Power BI.
Step 7.
In Power BI, select “Database,” then “Amazon Redshift,” and click “Connect.”
Step 8.
In Power BI, enter the details from Panoply’s BI Connection tab. Just hover over the information you need, click “Copy,” and paste it into the correct field.
Step 9.
In Power BI, select your data connectivity mode and click “Connect.” Again, we recommend DirectQuery over Import for the reasons we explained above.
Step 10.
In Power BI, select the tables and views you’d like to use and click “Load.” And voila! Your MySQL data is now available to analyze.
Sure, using Panoply to connect your MySQL data to Power BI takes a whopping two additional steps compared to using a direct connection. But the payoff is worth it: You’ll now be able to query and visualize all of your business data stored in Panoply, regardless of whether or not Power BI has a native connector to it. And having all that data at your fingertips opens up a whole new world of insights to discover.
Plus, Panoply makes it easy to manage multiple users by enabling varying permissions for each of your data sources. For example, if you’re working with both a marketing professional who needs to run reports on Google Analytics data and a BI analyst who needs to create reports for various sales metrics from Shopify, you can use Panoply to give them the access they need.
Ready to give it a shot? You can set up a Panoply account, start syncing your MySQL data, and connect to Power BI, all in under 10 minutes. Give it a try today!
