You can connect Power BI to AWS Athena by using Athena ODBC driver. Download the driver from here and install it.
Then configure the driver to connect to AWS Athena service. Once configured, Power BI can use this ODBC to connect to Athena.
AWS Athena ODBC Driver Configuration:
>>Download and install the ODBC driver.
>>Open “ODBC data source administrator” and make sure the Drivers tab shows the “Simba Athena ODBC driver” installed
>>Now click on “System DSN” tab and select “Simba Athena” under System Data Sources and click Configure button
>>Provide the values as shown in the below screen.”Description” could be any details. “S3 Output Location“should be valid bucket where the temp files will be saved by PowerBI. AWS Region should be the region which you want to access. Then click “Authentication Options” button
>>Make sure you have a valid AWS IAM user created which has access to AWS Athena and S3 (if output is saved in S3). Also get Access_Key and SecretAccessKey values for this user. These will be used is different ways based on what “Authentication Type” you select
We will use: Authentication Type as IAM Credentials
>> Now you can test the connection by clicking Test button, if everything is fine then it will show screen like below
>>Now we know that ODBC driver is providing successful connection to AWS Athena. Open Power BI desktop and click on Get Data
>>On the new pop-up, select “Other“–>ODBC and click Connect
>>On the new screen select “Simba Athena” under Data Source Name and click OK
>>On new pop-up click on Windows on left menu options
>>Select the options as shown and click Connect
>>You are now connected to AWS Athena using ODBC driver
