MicroStrategy Intelligence Server

This section describes how to connect from MicroStrategy Intelligence Server (running on Linux) to Exasol, either using JDBC or ODBC. This is an extended version of the following MicroStrategy article - Exasol 6.x .

Prerequisite

  • MicroStrategy Secure Enterprise should 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 such an environment with a platform instance (Linux) running MicroStrategy Web and Intelligence Server, as well as the additional developer instance (Windows) running MicroStrategy Developer and other tools.

In this document, we follow the best practice where 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 file exajdbc.jar to the following directory on the instance:
  3. /opt/mstr/MicroStrategy/install/JDBC/
  4. Open MicroStrategy Developer, navigate to Administration -> Configuration Mangers -> Database Instances.
  5. Right click Database Instances and select New -> Database Instance to create a new database instance.
  6. The Database Instances window is displayed. Enter a name for the database instance, and under Database Connection type, select EXASolution. Click Ok.
  7. Optionally, for an older version of MicroStrategy, you can update the VLDB object for Exasol according to the Exasol 6.x article on MicroStrategy Community.

  8. The Database Connections window is displayed. Add a new database connection, as shown below: 
  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 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 (replace everything within the <hosts:port> including the bracket with correct parameters): 
    • JDBC;DRIVER=com.exasol.jdbc.EXADriver;URL={jdbc:exa:<hosts:port>}
  11. 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 JDBC driver from the Exasol downloads portal.
  2. Copy and unpack the tar.gz archive to a directory on the instance. For example: 
  3. /opt/mstr/MicroStrategy/exasol/
  4. Run the config_odbc configuration script as shown below (replace everything within the <> brackets including the bracket with correct parameters)
  5. ./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
  6. Next, copy the contents of
  7. /opt/mstr/MicroStrategy/exasol/odbc.ini 

    into

    /opt/mstr/MicroStrategy/odbc.ini
  8. Open MicroStrategy Developer, navigate to Administration -> Configuration Mangers -> Database Instances.
  9. Right click on Database Instances and select New -> Database Instance to create a new database instance.
  10. The Database Instances window is displayed. Enter a name for the database instance, and under Database Connection type, select EXASolution. Click Ok.
  11. Optionally, for an older version of MicroStrategy, you can update the VLDB object for Exasol according to the Exasol 6.x article on MicroStrategy Community.

  12. Select the DSN that was created in step 3.
  13. In the Default Login Name section, click New to add login information. On the Database logins window, enter the login information.
  14. Select the Advanced tab and modify the advanced settings for the database connection as follows:
    • Set Character set encoding for Unix Drivers to: Non-UTF-8.
  15. 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 (replace everything within the <hosts:port> including the bracket with correct parameters):
      • 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.