SQL - How to set up ODBC Plugin for Excel and SQL Analytics

How to set up Excel Plugin for SQL Analytics

Introduction

This guide is written to support developers who want to integrate with Kleer. The document is an addition to the main database documentation available upon request from your contact at Kleer.

It is assumed that you have received an API/database token.

Setup SQL database

1. Identify which version of Excel that is running, 32 or 64 bit.
2. Download and install Visual Studio 2015 C++ redistributable for the correct architecture (select x86 for 32 bit and x64 for 64 bit).
3. Download and install the MySQL ODBC connector for the relevant Excel version.
4. Install with installation type “Typical”.
5. Press the Windows button and type “odbc”.
   • Select Konfigurera ODBC-datakällor (XX bitar) with the corresponding 32 or 64 bit as selected in the previous steps.
   • Click “Lägg till” and select “MySQL ODBC 8.0 Unicode Driver”.
6. Input:
   • Data Source Name: PE SQL Analytics
   • Description: (leave blank)
   • TCP/IP Server: data.kleer.se
   • Port: 3306
   • User: provided by Kleer
   • Password: provided by Kleer
   • Database: report_external_v1
7. Download the CA certificate and import it.
   • Select the CA certificate file directory in ODBC under “SSL CA File” on the “Details” → “SSL” tab.

Setup Excel (Swedish Excel)

1. Go to the “Data” tab.
2. Press “Hämta data” and select “Från andra källor”, then “Från ODBC”.
3. Select PE SQL Analytics in “Namn på datakällan (DSN)”.
4. Input SQL code in “SQL-instruktion”.
5. As an example, you could put:

select * from dimension;

which asks Kleer to list all dimensions, also called categories.

6. Press “OK”.
7. The first time you use the connection, Excel will ask for user credentials — type the same username and password as before.
8. You will now see a preview of the data.
9. Press “Save and load” to place the data in an Excel table.

To update imported tables, press “Uppdatera alla”.

ODBC connection when using SSH tunnel

If a static IP address cannot be used, there is always the option to connect to the Kleer database using an SSH tunnel.

When using an SSH tunnel, TCP/IP Server is set to localhost and Port stays 3306.