Creating a connector to Power BI from AWS Athena
“ In this era of the information economy, data has become the most critical asset of every organization and hence Data-driven decision-making should be at the heart of every digital transformation initiative. ”
Microsoft Power BI is a Data consumer that offers Data visualization and BI tools such as software services, apps, and data connectors, it provides the resources to aggregate, visualize, analyze, and share data to make lucrative and effective decisions at the right time. It supports a wide variety of data sources and then helps with data analysis, visualization, and publication.
This Power BI setup guide will walk you through the different steps involved in connecting Power BI to AWS data sources, to process data. This tour demonstrates how AWS data sources can be viewed using Power BI by following a few simple steps such as; downloading the ODBC driver, creating a data source, and setting up Power BI. Integrating Microsoft Power BI with the AWS Cloud can accelerate connectivity, security, performance and cost optimization for organizations. Ready to give it a try? Let’s get started then.
STEP 1: Downloading the ODBC driver
On 64-bit Windows operating systems, you can execute both 32- and 64-bit applications. However, 64-bit applications must use 64-bit connectors, and 32-bit applications must use 32-bit connectors. Make sure that you use a connector whose bitness matches the bitness of the client application.
Simba Athena 1.1 32-bit.msi for 32-bit applications and Simba Athena 1.1 64-bit.msi for 64-bit applications.
You can install both versions of the connector on the same machine.
To install the Simba Amazon Athena ODBC Connector on Windows,
- Depending on the bitness of your client application, double-click to run Simba Athena 1.1 32-bit.msi or Simba Athena 1.1 64-bit.msi.
- Click Next.
- Select the check box to accept the terms of the License Agreement if you agree, and then click Next.
- To change the installation location, click Change, then browse to the desired folder, and then click OK. To accept the installation location, click Next.
- Click Install.
- When the installation completes, click Finish.
STEP 2: Creating a datasource
After installing the Simba Amazon Athena ODBC Connector, you need to create a Data Source Name (DSN).
To create a Data Source Name on Windows:
- From the Start menu, go to ODBC Data Sources. (Image 1)
- In the ODBC DataSource Administrator, click the Driverstab, and then scroll down as needed to confirm that the Simba Amazon Athena ODBC Connector appears in the alphabetical list of ODBC connectors that are installed on your system . (Image 2)
- To create a DSN that all users who log into Windows can use, click the System DSN tab. (Image 3)
- In the Create DataSource dialog box, select Simba Amazon Athena ODBC Connector and then click Finish. The Simba Amazon Athena ODBC Connector DSN Setup dialog box opens.
- In the Data Source Name field, type a name for your DSN.
- Optionally, in the Description field, type relevant details about the DSN.
- In the AWS Regions field, type the AWS region the Athena instance that you want to connect to. (Image 4)
- To enter authentication details, click on authentication and enter the AWS credentials provided as csv., Secret key and Access key. (Image 5)
- To test the connection, click Test, Review the results as needed,and then click OK.
- To save your settings and close the Simba Athena ODBC Driver DSN Setup dialog box, click OK.
STEP 3 : Set-up in power BI
To connect Power BI desktop to your Amazon Athena data, perform the following steps.
To connect to Athena data from power BI desktop
- Launch Power BI Desktop.
- Do one of the following:
- Choose File, Get Data, or
- From the Home ribbon, choose Get Data.
- Select Get data from another source and then in the search box, enter Athena.
- Select Amazon Athena, and then choose Connect (Image 6)
- On the Amazon Athena connection page, enter the following information. (Image 7)
- For DSN, enter the name of the ODBC DSN that you want to use. For Data Connectivity mode, choose a mode that is appropriate for your use case, following these general guidelines:
- Choose DirectQuery. In DirectQuery mode, no data is downloaded to your workstation. While you create or interact with a visualization, Microsoft Power BI works with Athena to dynamically query the underlying data source so that you’re always viewing current data (Image 8)
- Choose OK.
- At the prompt to configure data source authentication, choose Use Data Source Configuration, and then choose Connect. (Image 9)
- Choose Load. After the load is complete, you can create visualizations like the one in the following image (Image 10)
In this guide, we learned that Power BI can be connected to AWS data sources, query directly from it, and share Data visualizations using it. Now, you can use the guide to know how to integrate and use Microsoft Power BI with the AWS Cloud with a focus on connectivity, security, performance, and cost optimization.