How to set up PowerBI for reporting from AWS Aurora MySQL Database

By Anuj Tuli, CTO

Many organizations that use PowerBI for business insights and analytics have a need to run their reports against various data sources, including workloads that they may have residing in Amazon AWS. There can be a number of various data sources configured for AWS; this blog walks through how to set up connectivity between PowerBI and AWS Aurora MySQL Database.  

Assumptions:  

• You have access to AWS Control Tower 
• You have an account for access PowerBI SaaS (powerbi.com) and to download the client 
• You have Administrator access to the Windows machine that we will use as our MS Data Gateway server within AWS.  

First, let’s look at various configurations that we need to set up on the AWS side –  

• Create a new keypair specifically for this work. This is optional. You can choose to use an existing keypair for AWS access.  
• Create Aurora DB with Dev/Test template. This is optional. You can choose to use an existing DB cluster instance. 
• If you don’t have a public internet gateway set up for the VPC, we will need to set one up.  
• Create RDS instance connectivity subnet (or leverage one directly inherited via the VPC) set up with the appropriate inbound connection. 
• If you plan on connecting to your Database instance from an external client (i.e. external to private AWS subnet), then you will need to make the Database publicly accessible. This can be done by going to your RDS console, selecting the database instance, select ‘Modify’ button, and enable ‘publicly accessible’ property under Additional Networking options. 
• Capture the endpoint name of the Database instance (Reader or Writer) under the Connectivity and Security tab from the RDS console. 

Next, we will configure the PowerBI components –  

• If you don’t have an account already, sign up for PowerBI SaaS (powerbi.com)  
• We will spin up a new Microsoft Windows server (we used Server 2019, with .NET framework 4.7+) within our AWS EC2 environment. This Windows server will work as a jump box, as well as the MS Data Gateway that talks to PowerBI SaaS. The AWS keypair will be used to decrypt the Windows ‘Administrator’ password, so we will associate the public internet gateway with this machine, so we can RDP directly onto this box and download the following components: 
  • Download and install Visual Studio 2019 x64 redistributable (https://aka.ms/vs/16/release/vc_redist.x64.exe) 
  • Download and install MySQL 8.0.21 ODBC connector (https://dev.mysql.com/downloads/connector/odbc/) 
  • Download and install MS Data Gateway installer (https://go.microsoft.com/fwlink/?LinkId=2116849&clcid=0x409) 
  • Download and install PowerBI Desktop Client (https://www.microsoft.com/en-us/download/confirmation.aspx?id=58494) 
• Once the ODBC connector is installed, we will open up the console and add the data source under ‘System DSN’ tab. This is where we will be defining the connection to AWS Aurora MySQL Database. Click on ‘Add’ and enter the Database endpoint gathered from AWS, port (default 3306) and associated credentials. You also have the option to ‘Test’ the connection string. 
• If the Test connection is successful, you are now ready to configure the Gateway instance in PowerBI SaaS.  
• You can use the PowerBI Desktop Client to now create reports and publish them to your PowerBI SaaS tenant. Upon configuring the Gateway instance in PowerBI SaaS, you can also control the schedule for report generation from the Aurora Datasource. 

One of the most common ODBC errors we’ve seen is when the ODBC connector is unable to connect to the database. This usually happens either because the public subnet for the VPC is not associated with the Windows EC2 instance, or the public accessibility flag for the database is not set.  

If you have any questions or comments on the tutorial content above, or run in to specific errors not covered here, please feel free to reach out to [email protected] 

Anuj Tuli is the chief technology officer at Keyva. In this role, he specializes in developing and delivering vendor-agnostic solutions that avoid the “rip-and-replace” of existing IT investments. Tuli helps customers chart a prescriptive strategy for Application Containerization, CI/CD Pipeline Implementations, API abstraction, Application Modernization, and Cloud Automation integrations. He leads the development and management of Cloud Automation IP and related professional services. With an application developer background, he provides a hands-on perspective towards various technologies.

Like what you read? Follow Anuj on LinkedIn.