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.

849

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.

609

Select the Simba Athena ODBC Driver data source.

609

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

427

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

427
  • 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**.

401

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
390

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.

413

eg. FAILED message.

392

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.

2642

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

622

Find ODBC and click Connect.

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

713

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.

705

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.

889

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

1918