Connecting to third party tools

Automated Data Analytics on AWS provides native integrations with third party business intelligence tools such as Tableau and PowerBI. This allows users to visualize data within data products using these tools.

To integrate with these solutions, you must first establish a private connection through an API Key.

1. To find your connection URL and API keys, select Profile from the username dropdown on the top right.

2. On the My Profile page, copy the Athena Proxy URL in a text file.

   

   Athena Proxy URL

3. Select Create API Key. The Create API Key dialog box is displayed.

4. On the Create API Key dialog, add a name, expiration date for the API Key, and select Enable to indicate the API Key is active.

   

   Create API key

5. Select Submit. An API key is created for you.

6. Copy the API Key to a text file along with the Athena Proxy URL.

   

   API key details

After you have generated an API Key, you need to connect to it via the AWS Athena driver.

Tableau configuration

1. Download the JDBC Driver with AWS SDK (AthenaJDBC42_2.0.25.1001.jar) from the Amazon Athena User Guide on Amazon's website.

2. Extract the contents of the .zip file and copy the JDBC 4.2 version of the extracted .jar file to:

   • For Mac, copy the .jar file to the ~/Library/Tableau/Drivers directory. You may need to create the directory if it doesn't already exist. 

   • For Windows, copy the jar file to C:\Program Files\Tableau\Drivers.

   • For other operating systems, refer to the Tableau Direct Download page for more details.

3. To proxy through an Athena Proxy, you need to configure the JDBC driver by creating a file athena.properties and copy it to the following locations:

   • Mac: ~/Documents/My Tableau Repository\Datasources

   • Windows: C:\Users\<user-name>\Documents\My Tableau Repository\Datasources

   • Linux: ~/Documents/My Tableau Repository/Datasources

   Example of the file on MacOS.

   

   MacOS Athena properites file

4. Enter the following values in the athena.properties file.

   Note

   Replace the EndpointOverride value with the Athena Proxy URL above.

   MetadataRetrievalMethod=ProxyAPI
   EndpointOverride=[Replace the endpoint with the Athena Proxy URL]
   UseResultsetStreaming=0

   For more information, refer to the athena.properties example.

5. Restart Tableau, and search and select Amazon Athena as the connector.

   

   Search for Athena connector

6. Configure Amazon Athena using the following information:

   • Server: athena.ap-southeast-2.amazonaws.com

   • Port: 443

   • S3 staging directory: s3://

   • Access Key ID: api-key [Replace with the API key copied from the solution web UI]. Ensure that you copy the API key without the quotation marks and do not include any spaces before or after the API key value.

   • Secret Access Key: Any 4 digit characters

7. Select Sign In. This displays the default AwsDataCatalog along with Automated Data Analytics on AWS domains in the Catalog drop down. If you select the Domain, it will list available data products within the Databases drop down.

8. Select the desired data product to display all tables within the data product.

Display all tables

You can use New Custom SQL to run queries from Automated Data Analytics on AWS directly within Tableau.

PowerBI configuration

1. To configure integration with PowerBI using the Athena ODBC driver, download and install the driver from the Connecting to Amazon Athena with ODBC page.

2. Under the User DSN section, create a new ODBC data source using ODBC Data Source Administrator instructions.

   

   ODBC data source administrator

3. From the driver list, select the installed Simba Athena ODBC Driver, and select Finish.

   

   Simba Athena ODBC driver

4. Enter the data source name, e.g. Automated Data Analytics on AWS.

   • AWS Region: ap-southeast-2

   • Catalog: AwsDataCatalog

   • Schema: default

   • Workgroup: primary

   • Metadata Retrieval Method: ProxyAPI

   • S3 Output Location: s3://

   • Endpoint Override: Paste the Athena Proxy endpoint from Automated Data Analytics on AWS.

5. Select Authentication Options and select IAM Credentials for the Authentication Type. 

   • User: api-key [Paste the API-Key from Automated Data Analytics on AWS copied above]

   • Password: any digits

6. Select OK.

7. In the Advanced Options, uncheck the Use Resultset Streaming and select OK .

   

   Advanced options

8. Test and select OK. Automated Data Analytics on AWS will be added as a new Data Source.

9. Open the Power BI application, and select ODBC as the data source type and select the Data source name created previously. (for example, Automated Data Analytics).

10. Enter the api-key [API Key copied from the previous step].

11. Enter a 4 digit password and select Save.You will see Domains loaded in the PowerBI navigator.

12. Expand the domain to see data products and tables. Select the table to import as a dataset into PowerBI for further analysis.

Expand domain

Select table to import