How can we help?

How to connect to PowerBI using a Data Connector

Teresa
Teresa
  • Updated

Connecting PowerBi Desktop to AWS Athena through the Internet

Connecting Power BI Desktop to Amazon Athena requires an ODBC connector to establish a connection. This allows users to analyze and visualize data stored in Amazon S3 using a serverless interactive query service. By following a few simple steps and configuring the ODBC driver, users can easily connect to Amazon Athena from Power BI and begin exploring and analyzing their data visually and interactively.

Prerequisites

  1. Power BI Desktop installed on your local machine.
  2. The latest version of the Athena ODBC driver shall be downloaded and installed on your machine. You can download it from the following link:
    For Windows, please download and use the 64-bit version.
  3. AWS IAM credentials provide programmatic read access to each customer's distinct AWS resources, including Athena and S3.

Note:

  • For system requirements and downloading PowerBi Desktop, please refer to this Microsoft doc.
    Also, for detailed installation steps, please refer to this doc.

  • For PowerBi Plans and Pricing, please refer to this page.

  • Unfortunately, Power BI Desktop is not natively available for Mac operating systems. However, you can use Power BI Desktop on a Mac by using a virtual machine to run a Windows environment. Here are the general steps to install Power BI Desktop using a virtual machine:

    1. Install a virtual machine software such as Parallels Desktop, VMWare Fusion, or VirtualBox on your Mac.

    2. Create a virtual machine and install a Windows operating system on it.

    3. Install the latest version of Microsoft Power BI Desktop on the Windows virtual machine.

    4. Launch the virtual machine and open Power BI Desktop to start using it.

    Note that running a virtual machine on a Mac requires a Windows license, and it may also require additional hardware resources to run smoothly. It is important to ensure that your Mac meets the minimum requirements for running a virtual machine and Power BI Desktop.

    Also note that using a virtual machine to run Power BI Desktop on a Mac may result in slower performance compared to running it natively on a Windows computer. Additionally, using a virtual machine may also result in additional complexities and maintenance requirements.

    For detailed steps on how to install PowerBI desktop on Mac, please refer to this doc.

Steps to Connect

Install and Configure the Athena ODBC Driver

  1. Open the ODBC Data Source Administrator on your local machine.

  2. Click on the System DSN tab.

     

     

    • image-20230425-201119.png
  3. Click on the Add button to add a new data source.
  4. Select the Amazon Athena ODBC driver from the list and click on Finish.

    • image-20230425-201119.png

  5. In the Amazon Athena ODBC Driver DSN Setup window, enter the following details:

    • Data Source Name: A name for the data source

    • Description: An optional description

    • AWS Region: The AWS region where your Athena instance is located, ex: us-west-2

    • Catalog: AwsDataCatalog

    • Schema: As provided by Allbound team, this will be per customer

    • Workgroup: As provided by Allbound team, this will be per customer

    • Metadata Retrieval Method: Select “Auto”

    • S3 Output Location: As provided by Allbound team, this will be per customer

    • Encryption Options: Select “SSE-S3”

       

    • image-20230425-203139.png

       

  6. Then click on Authentication Options and enter the following details:

    • Authentication Type: Select “IAM Credentials“

    • User: Your AWS access key ID

    • Password: Your AWS secret access key

    • image-20230425-204022.png

  7. Then click on “OK“

  8. Then, click on "Test" to test the connection.

  9. Once you have tested the connection, click on "OK" to save the data source.

Connect Power BI Desktop to Amazon Athena

  1. Launch Power BI Desktop.

  2. Do one of the following:

    • Choose File, Get Data

    • From the Home ribbon, choose Get Data.

  3. In the search box, enter Athena.

  4. Select Amazon Athena, and then choose Connect.

    • connect-with-odbc-and-power-bi-1.png

  5. On the Amazon Athena connection page, enter the following information.

    • For DSN, enter the name of the ODBC DSN that

    • For Data Connectivity mode, choose a mode that is appropriate for your use case, following these general guidelines:

      • For smaller datasets, choose Import. When using Import mode, Power BI works with Athena to import the contents of the entire dataset for use in your visualizations.

      • For larger datasets, 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. For more information about DirectQuery, see Use DirectQuery in power BI desktop in the Microsoft documentation.

      • image-20230425-205146.png

  6. Choose OK.

  7. At the prompt to configure data source authentication, choose Use Data Source Configuration and then choose Connect.

    Your data catalog, databases, and tables appear in the Navigator dialog box

    • image-20230425-205740.png

  8. In the Display Options pane, select the check box for the dataset that you want to use.

  9. If you want to transform the dataset before you import it, go to the bottom of the dialog box and choose Transform Data. This opens the Power Query Editor so that you can filter and refine the set of data you want to use.

  10. Choose Load. After the load is complete, you can create visualizations like in the following images. If you selected DirectQuery as the import mode, Power BI issues a query to Athena for the visualization that you requested.image-20230425-210416.png

 

image-20230425-210620.png

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request