ODBC connectivity
Before you start
- You need your Interact Advanced Analytics configuration details, provided during technical onboarding for Advanced Analytics.
- You need permission to install software on the machine and to create an ODBC data source (DSN).
Overview
This page covers the ODBC connectivity options for Interact Advanced Analytics, which uses AWS Data Lake technologies for data querying, analysis and retrieval. The tool-specific ODBC guides reference the connection steps here.
Step 1: Install the AWS Athena ODBC driver
Download the latest ODBC driver from the AWS Athena ODBC driver page.
If you need an older version, see the previous ODBC driver versions.
Once downloaded, install the file. Full setup and configuration instructions are in the Simba Athena ODBC install and configuration guide.
Step 2: Gather your Advanced Analytics configuration
Your Interact Advanced Analytics configuration details are provided during technical onboarding and include:
- Server: for example, athena.eu-west-1.amazonaws.com (can be retrieved from the AWS Athena endpoints page, and can change over time)
- S3 Staging Directory: s3://{{my_bucket}}-results/ (where the query results are saved)
- Access Key ID: equivalent to the username
- Secret Access Key: equivalent to the password
- Workgroup: the workgroup name required for AWS Athena queries, for example
Inc_Ltd - Database: the Athena database where your analytics data is located
- Table Name: the Athena table where your analytics data is located
Step 3: Configure the ODBC connection
Follow the instructions below with your Interact Advanced Analytics configuration details to hand.
To make the ODBC connector configuration specific to a single Windows user or available system-wide, choose User DSN or System DSN accordingly. This guide uses User DSN for demonstration.
Select the Simba Athena ODBC Driver data source.
The default configuration page appears.
Fill in the form as shown below, replacing the {....} placeholders with the relevant Interact Advanced Analytics configuration details.
- Data Source Name: for display only. This appears in your BI tool (for example, Power BI) when selecting the data source, so choose something recognisable such as Interact Advanced Analytics.
- Description: for display only. For example, if you have Advanced Analytics on the live site and the sandbox, use this field to record which connection is for which site.
- AWS Region: provided to you, for example
us-east-1oreu-west-1. - Catalog: leave as the default (
AwsDataCatalog). - Schema: the Database name from the Interact Advanced Analytics configuration details.
- Workgroup: the Workgroup name from the Interact Advanced Analytics configuration details.
- S3 Output Location: the S3 Staging Directory from the Interact Advanced Analytics configuration details.
- Encryption Options: set this to
SSE_S3.
Click Authentication Options to provide the username and password.
On the next screen, set Authentication Type to IAM Credentials.
Populate the fields as follows:
- User: the Access Key ID from the Interact Advanced Analytics configuration details.
- Password: the Secret Access Key from the Interact Advanced Analytics configuration details.
Click OK.
Step 4: Test the connection
On the configuration page, click Test. If everything is configured correctly, you see a SUCCESS! message. If the test fails, recheck the previous setup steps.
The example below shows a FAILED message.
Advanced ODBC options
The ODBC connector is highly configurable. See the Athena ODBC v2 driver advanced options.
Ports
Port 443 is used to communicate with the Data Lake, and port 444 is used to stream query results back from the Data Lake to the BI tool. Both ports must be open. See the Athena ODBC v2 driver documentation.