MicroStrategy Intelligence Server

This article explains how to connect from MicroStrategy Intelligence Server (running on Linux) to an Exasol database using either JDBC or ODBC.

This is an expanded version of the article Exasol 6.x on the MicroStrategy community website.

Prerequisites

  • MicroStrategy Secure Enterprise must be installed and configured, including an instance of MicroStrategy Developer, MicroStrategy Web, and the MicroStrategy Intelligence Server.

  • If you are using AWS, the best practice is to deploy the environment using a platform instance (Linux) running MicroStrategy Web and Intelligence Server, and an additional developer instance (Windows) running MicroStrategy Developer and other tools.

In this document, platform instance refers to the Linux instance running the Intelligence Server, and developer instance refers to the Windows instance running MicroStrategy Developer.

Connect Using JDBC

  1. Download the JDBC driver to the MicroStrategy Platform instance. You can access the latest version of the Exasol JDBC driver from the Exasol Downloads portal.
  2. Copy the exajdbc.jar file to the following directory on the instance:
    3. /opt/mstr/MicroStrategy/install/JDBC/
  3. Open MicroStrategy Developer, navigate to Administration > Configuration Managers > Database Instances.

  4. Right click Database Instances and select New > Database Instance to create a new database instance.

  5. The Database Instances window is displayed. Enter a name for the database instance, and under Database Connection type, select EXASolution. Click Ok.

  6. The Database Connections window is displayed. Add a new database connection, as shown below: 

  7. In the Default Login Name section, click New. On the Database logins window, enter your credentials.

  8. Select the Advanced tab and modify the advanced setting for the database connection as follows: 
    • Set Character set encoding for Unix Drivers to: Non UTF-8
    • Add the following additional connection string parameters: 
      • JDBC;DRIVER=com.exasol.jdbc.EXADriver;URL={jdbc:exa:<hosts:port>}

  9. Click Ok to save the changes.

Connect Using ODBC

  1. Download and configure the ODBC driver on the MicroStrategy Platform instance. You can access the latest version of the Exasol ODBC driver from the Exasol Downloads page.
  2. Copy and unpack the tar.gz archive to a directory on the instance. For example: 
    3. /opt/mstr/MicroStrategy/exasol/
  3. Run the config_odbc configuration script as shown below:
    4. ./config_odbc --host=<hosts:port> --user=<your_user> --password=<your_password> --odbcini=/opt/mstr/MicroStrategy/exasol/odbc.ini

    The configuration script requires Perl and Digest.MD5 perl module to be installed. If these are not installed, you can install then by executing the following commands: 

    sudo yum install perl
sudo yum install perl-Digest-MD5 -y
  4. Next, copy the contents of 
    5. /opt/mstr/MicroStrategy/exasol/odbc.ini

    into 

    /opt/mstr/MicroStrategy/odbc.ini
  5. Open MicroStrategy Developer, navigate to Administration > Configuration Managers -> Database Instances.

  6. Right click on Database Instances and select New > Database Instance to create a new database instance.

  7. The Database Instances window is displayed. Enter a name for the database instance, and under Database Connection type, select EXASolution. Click Ok.

  8. Select the DSN that was created in step 3.

  9. In the Default Login Name section, click New to add login information. On the Database logins window, enter the login information.

  10. Select the Advanced tab and set character encoding for Unix Drivers to Non-UTF-8.
  11. Click Ok to save the changes.

Connect Using MicroStrategy Web

To use Exasol as a data source in MicroStrategy Web, you can either add data from DB instances configured through MicroStrategy Developer (see configuration for JDBC / ODBC above) or create a new data source. This new data source can either be DSN-Less or configured with DSN. To add a new data source:

  • Select Add External Data option and select Database source.
  • Pick a query type (Table, Type a Query, Build a Query).
  • Choose the radio button for DSN-less or DSN Data Sources. Refer to the below section for detailed steps.

Steps to Add Data Source

  1. Open MicroStrategy Web on a browser by entering your company's MicroStrategy URL.
  2. Open your project, and select Add External Data.

  3. From the list of data sources in the Connect to Your Data window, select either Databases or EXASolution.

  4. The Select Import Options window is displayed. Select the radio button next to Select tables, and click Next.

  5. The Import from Table window is displayed. Click the plus icon (+) next to DATA SOURCES to add new data source

  6. The Data Source window is displayed. Here, you can select DSN-less Data Sources or DSN Data Sources.
    1. If you select DSN-less Data Sources, then enter the following details: 
      • Make sure the database and version field is set to EXASolution.
      • Enter the Connection String. Add the following JDBC connection string:
        • JDBC;DRIVER=com.exasol.jdbc.EXADriver;URL={jdbc:exa:<hosts:port>}

        Make sure the Exasol JDBC driver is available in a folder on the platform instance, as described in Connect Using JDBC section.

      • Click Save, and then click Finish.

    2. If you select DSN Data Sources, then enter the following details:
      • Make sure to select a previously created DSN, from OBDC section.
      • Make sure the version field is set to EXASolution.
      • Enter the user name and password.
      • Enter a name for the data source.

      • Click Save.
      • Next, click Edit Catalog Options. For the field Name Spaces, select All. If the table overview consecutively displays only the first letters of each table name, then use a data source created by Developer instead.