Power BI Configuration (ODBC)

Interact Advanced Analytics uses the AWS Data Lake technologies to power the data querying, analysis, and retrieval. To connect Power BI to Interact's Advanced Analytics Solution, you will need:

Power BI Desktop
AWS Athena ODBC Driver
Interact Advanced Analytics credentials and configuration details
Configured ODBC Connection to use in Power BI

Power BI Desktop

You will need a version of Power BI Desktop installed, with a valid subscription (or trial.)

Download versions can be found here.
https://powerbi.microsoft.com/en-us/downloads/

AWS Athena ODBC Driver

You can find the latest ODBC drivers on this page.
https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html

If you require an older version of the ODBC driver, you can find them here.
https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html#odbc-previous-versions

Once downloaded, you will need to install the downloaded file.

Full instructions for setup and configuration can be found here.
https://s3.amazonaws.com/athena-downloads/drivers/ODBC/SimbaAthenaODBC_1.0.5/Simba+Athena+ODBC+Install+and+Configuration+Guide.pdf

Interact Advanced Analytics Configuration

This will be provided to you as a part of the technical onboarding for Advanced Analytics, and will include the following details:

Server: eg. athena.eu-west-1.amazonaws.com (can be retrieved from here https://docs.aws.amazon.com/general/latest/gr/athena.html <- can change over time)
S3 Staging Directory: s3://{mybucket}-results/ (this is where the query results will be saved)
Access Key ID: This is equivalent to the username
Access Secret Key: this is equivalent to the password
Workgroup: This is the workgroup name required for AWS Athena queries. eg. Inc_Ltd
Database: This is the Athena database where your analytics data will be located.
Table Name: This is the Athena table where your analytics data will be located.

Configuring the ODBC Connection for Interact Advanced Analytics

Please follow the instructions below, and have your Interact Advanced Analytics Configuration details to hand.

If you want the ODBC connector configuration to be Windows user-specific, or system-wide, chose User DSN or System DNS accordingly. The guide will use User DNS for demonstration purposes.

Select the Simba Athena ODBC Driver data source.

You will be presented with the default configuration page (shown below.)

Fill in the form as shown below, with the {....} placeholders replaced with the relevant Interact Advanced Analytics Configuration details

Data Source Name: this is purely for display purposes only - feel free to get creative - this will display in Power BI when selecting the data source, so something easily recognizable like Interact Advanced Analytics is a good choice
Description: this is purely for display purposes only - feel free to get creative - eg. if you have Advanced Analytics on the live site, and the sandbox, this could be a good field to record which connection is for which site.
AWS Region: this will be provided - eg. us-east-1, eu-west-1, etc.
Catalog: leave as the default (AwsDataCatalog)
Schema: this is the Database name provided in the Interact Advanced Analytics Configuration details
Workgroup: this is the Workgroup name provided in the Interact Advanced Analytics Configuration details
S3 Output Location: this is the S3 Staging Directory provided in the Interact Advanced Analytics Configuration details
Encryption Options: make sure this is set to SSE_S3

Then please click Authentication Options, so that we can provide the Username, and Password.

You will see the following screen - make sure the Authentication Type* is set to IAM Credentials**.

You want to populate the fields based on this information:

User: this is the Access Key ID provided in the Interact Advanced Analytics Configuration details
Password: this is the Secret Access Key provided in the Interact Advanced Analytics Configuration details

Click OK .

Then click Test on the configuration page, and if everything was configured correctly, you should see a SUCCESS! message. If it has failed, please go back and re-check the previous setup steps.

eg. FAILED message.

Connecting via Power BI

Now that you have the ODBC driver installed, and you have configured a working connection to the Interact Advanced Analytics, we can load the data into Power BI.

Once you open Power BI - click on Get Data. You will be presented with the following screen.

Find ODBC and click Connect.

On the From ODBC page, select the newly created ODBC connection - eg. Interact Advanced Analytics. Then hit OK.

You may be asked for credentials again - simply use the Access Key ID as the Username, and Secret Access Key as Password. Then hit Connect.

Now you will be able to see the databases and tables you have access to - select the tables you want to load into Power BI and hit Load.

You will now have the data loaded, and be able to use it in Power BI.