How To Connect From a Power BI Desktop to a MySQL AWS RDS Instance
For your business, one way to optimize your process and maximize performance would be by connecting your Power BI desktop to a MySql AWS (Amazon Web Services) RDS (Relational Database Service) instance. Achieving this may seem daunting, but don’t worry! This article will explain the steps of how to go about it.
Why Connect Power BI to an RDS in AWS?
Connecting Power BI to an Amazon RDS will make it simpler for your company to set up, manage, and scale a relational database in the cloud. It offers scalable capability at a low cost while automating tedious administrative functions like hardware provisioning, database configuration, patching, and backups.
How To Connect Power BI to an RDS
There are a couple of approaches that you can work with to connect Power BI to RDS. These methods are through the embedded MySQL database and the ODBC driver:
- MySQL is already integrated into Power BI. MySQL is one of the most widely used open-source relational databases, with a large number of businesses using it. MySQL data can be easily integrated, visualized, and analyzed thanks to an in-built connector.
- Using the ODBC (Open Database Connectivity) integration in Power BI, you can import information from any third-party ODBC driver by simply providing a Data Source Name (DSN) or a connection string.
Here are the steps of how to do it both ways:
Validate That You Have an RDS Created in AWS
Before you can make a connection, you first need to validate that you have an RDS in AWS by doing the following:
- Navigate to the AWS console and verify that there is a database (DB) instance running and that it is indeed a MySQL instance.
- Validate that there is, in fact, a functioning database as the endpoint.
- Confirm that the default port is used, or take note if you need to modify it.
- Copy the endpoint from the AWS console and use the MySQL workbench to validate that you can connect with the credentials provided to the RDS, that you have permissions to run a SQL statement, and that it is returning data.
Create a New Connection on Power BI Using the Embedded MySQL Database Component
- Start the Power BI component.
- Access the data in the RDS using the built-in MySQL database connection that is provided with Power BI. You will first be asked for your server and database. For the server, provide your endpoint, and the database name can be up to you.
- The next component involves setting up the credentials of the database. Select database credentials. You may use the credentials created for this user on the RDS. Then, click connect.
- Now Power BI will be able to connect to the cloud, connect to the RDS, and show you a list of tables provided in that Power BI report database. You can load the files from the database to create a data model, bring all the data down into the model, and start developing and using Power BI as usual.
Connect an RDS in the Cloud Using an ODBC Driver
- First, create a new DSN by following these steps:
- Click “Add.”
- To create a new ODBC, select “MySQL.”
- Click “Finish.”
- In the next window, provide a Data Source Name and your server name. You can also change the port if the RDS has changed its default port. Then, give it a username. Finally, test your connection.
- Now, you come in through Power BI, and instead of choosing the MySQL component, scroll to the bottom and choose ODBC.
- Select which ODBC you want to use, then click “Okay.”
- In the next window, provide your database credentials. Then, you will be able to access all the databases in that RDS, most of which are system databases. You can also preview the data this way.
Possible Issues
Like most systems, there are a few possible issues with connecting Power BI to an RDS in AWS. If there are any problems, they will most likely fall into one of these groups:
- The first is an AWS error protection category that is preventing you or your IP from accessing the RDS.
- Another instance is if you have a network ACL or network ECL on your local computer that is causing you to be blocked from connecting to a port or a particular subnet, among others. As a result, you will be unable to connect to the RDS.
- Another explanation may be that you lack the required credentials.
- The last issue we sometimes see is that your firewall is blocking communication between the RDS and the cloud.
Takeaway Points
Companies are always searching for new ways to improve their processes and boost their productivity with actionable insights. Connecting your Power BI desktop to a MySQL AWS RDS instance would be one solution. By doing this, you can make setting up, managing, and scaling a relational database in the cloud much easier for your business user. This article discusses two ways to go about it: through the MySQL database component and the ODBC driver.
We’ve got you covered if you’re lost and need assistance with Amazon Web Services. WCI has been bringing data to decision-makers since 1998, so we know what we’re doing. Contact us today if you want to learn more or if you have any questions. We’d be glad to help you with any of your concerns.
Sources:
https://wciconsulting.com/resources/aws-tutorials/power-bi-to-rds-mysql-connection-tutorial/
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-using-generic-interfaces
