# Connect with MicroStrategy

MicroStrategy is a business intelligence software, which offers a wide range of data analytics capabilities. As a suite of applications, it offers data discovery, advanced analytics, data visualizations, embedded BI, and banded reports and statements.

This topic shows you how to integrate MicroStrategy Desktop and MicroStrategy 2019 with SingleStore.

## MicroStrategy Desktop

## Prerequisites

Prior to configuring MicroStrategy Desktop to connect to SingleStore, you will need the following software installed.

* MicroStrategy Desktop version 11.1.1
* Either the [MySQL 64-bit ODBC driver (version 8.0.15)](https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.15-winx64.msi) or the [MariaDB 64-bit ODBC driver (version 3.1.0)](https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.0/mariadb-connector-odbc-3.1.0-win64.msi). Click on either link to download the install file for the driver. After the download completes, run the install.

## Creating a Data Source Name (DSN) to Connect to SingleStore

1. Open the **ODBC Data Source Administrator**. To find this program on Windows 7, select the **Control Panel** from the Start Menu. Then click **Administrative Tools**, followed by **Data Sources (ODBC)**. To find this program on Windows 10, type **ODBC Data Sources** in the search bar on the Start Menu.

2. Click the **System DSN** tab.

3. Click **Add…** on the right. Select either the MySQL ODBC 8.0 Driver or the MariaDB ODBC 3.1 Driver and click **Finish**.

4. A window appears, where you will configure the settings needed to create the data source. Specify a **Data Source Name**. In the next section, you will configure MicroStrategy to connect to SingleStore via this data source. For **TCP/IP Server**, specify the IP address or server name where the SingleStore master aggregator node resides. Specify the **User** and **Password** to use to connect to SingleStore.

5. If using the MySQL driver, click **Details >>**. For **Authentication** type `mysql_native_password`. Then click **Details <<**.

6. Click **Test** to test the connection. The connection should be successful.

7. Click **OK**. The data source appears on the **System DSN** tab.

## Connecting MicroStrategy to SingleStore via your ODBC Data Source

1. Open MicroStrategy Desktop. Click the **New Dossier** button.

2. Click on the **New Data** button, which is located on the lower part of the right pane.

3. For the **Data Source**, select either **MySQL** or **MariaDB**.

4. The **Select Import Options** screen appears. Choose **Select Tables** and click **Next**.

5. On the **Import from Table - Select** screen, click the **`+`** button, which is located to the right of **Data Sources**.

6. The **Data Source** window appears. Select **DSN Data Sources**. For **DSN**, select the data source name that you created in [Creating a Data Source Name (DSN) to Connect to SingleStore](https://docs.singlestore.com/db/v9.1/query-data/connect-with-analytics-and-bi-tools/connect-with-microstrategy/#UUID-658f0648-7064-7a52-faff-9caba6e70571.md) in the section above. After you select the **DSN**, specify a **Data Source Name**, which is how you will refer to the data source when using it in MicroStrategy. Click **Save**.

7. On the **Import from Table - Select** screen, the data source you created in the previous window appears in the **Data Sources** Section on the left. Click on this data source. Its tables appear in the **Available Tables** pane on the right. You can now explore the data in these tables.

## MicroStrategy 2019

When integrating MicroStrategy 2019 with SingleStore, the recommended versions are shown below:

| Certification Matrix      | Versions |
| ------------------------- | -------- |
| MicroStrategy Enterprise  | 2019     |
| SingleStore               | 7.1      |
| MySQL ODBC Unicode driver | 8.0      |

## MicroStrategy three-tier architecture

A MicroStrategy system is built around a multi-tier structure. This section provides instructions on how to set up a three-tier architecture. The diagram below shows a four-tier system including the use of MicroStrategy Developer. The instructions below will not cover how to set up or use MicroStrategy Developer as part of a MicroStrategy-SingleStore solution.

![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt9445cac445dc8be4/6a2c42bbce0ebe5f38fe7c46/16083b577c2228-6expfw.gif)

**Tier 1**

The first tier consists of two databases: the data warehouse, which contains the information that your users analyze; and the MicroStrategy metadata, which contains information about your MicroStrategy projects. This topic shows how to use a MySQL server for storing the metadata and how to use a SingleStore cluster as a data warehouse.

**Tier 2**

The second tier consists of MicroStrategy Intelligence Server also called I-server, which runs your reports, dossiers, and documents against the data warehouse.

**Tier 3**

The third tier is MicroStrategy Web, which delivers the reports to a client.

## Install Tiger Virtual Network Computing (VNC)

TigerVNC is a high-performance, platform-neutral implementation of VNC (Virtual Network Computing), a client/server application that allows users to launch and interact with graphical applications on remote machines. You will use this for all MicroStrategy interactions in GUI mode.

The following instructions describe how to perform and start a VNC server on the machine that will run MicroStrategy 2019. In this guide, the OS for that machine is RHEL 7.x.

1. First, follow the instructions from [RedHat’s documentation](https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/system_administrators_guide/ch-tigervnc) to install TigerVNC.

2. As the root user, uncomment the last two lines in `/etc/sysconfig/vncservers` and save the file.

3. Run `echo "kernel.sem=250 32000 100 2048" >> /etc/sysctl.conf`.

4. Reboot the machine.

5. Create the `mstruser` user. Set a password as follows:
   ```shell
   su – mstruser

   ```
   ```shell
   vncpasswd

   ```
   Set the password.

6. Start the vncserver as root.
   ```shell
   /etc/rc.d/init.d/vncserver start

   ```
   Check the output messages and confirm the server is listening on port 5901 or 5902. Also check the `vnc<ip>.log` to confirm the port.

After you have a VNC server set up, you must set up the TigerVNC client application on a client machine.

1. On your client machine, download and install the [latest stable version of TigerVNC](https://github.com/TigerVNC/tigervnc/releases).

2. Open a terminal window and connect to the VNC server.
   ```shell
   ssh -i "<pem file>" -L 5901:localhost:5901 mstruser@<domain name of server>

   ```

3. Start the Tiger client application and set the VNC server address to localhost:5901.

4. Click **Connect**.

5. Enter the password you set above. If successful, a Tiger VNC window will open with a built-in terminal window.

> **📝 Note**: The Tiger VNC client will render only if the VNC terminal is active on the server.

## Install MicroStrategy 2019 on Red Hat 7.x

1. On your server, download MicroStrategy 2019 Intelligent Enterprise from [MicroStrategy](https://www.microstrategy.com). The filename will be similar to MicroStrategy\_11.1\_Linux.tar.gz.

   **Note** You will need a license from MicroStrategy to use MicroStrategy 2019 Intelligent Enterprise.

2. Create the directory `/var/lib/mstr`. Make sure sufficient space is available.

3. Extract the tar file mentioned above into `/var/lib/mstr`.

4. Change directories into `/var/lib/mstr/Installations/QueryReportingAnalysis_Linux`.
   ```shell
   cd /var/lib/mstr/Installations/QueryReportingAnalysis_Linux

   ```
   **Note**: Please ensure that all the directories belong to the `mstruser`.

5. Run `./setup.sh`. This script will load the GUI window. **Note**: The user configuring and starting VNC must be same as user installing MicroStrategy, which in this case is `mstruser`.

6. Follow the prompts on the screen for the installation.

7. Specify `/var/lib/mstr/MicroStrategy` as the location for all the software components.

8. Disable SELinux and then reboot the machine.

9. Install Perl (if not already present).
   ```shell
   sudo yum install perl

   ```

10. Install the following Linux packages on the Red Hat machine as shown below:
    ```shell
    sudo yum install libXcursor libXdamage libXext \
    libXfixes libXi libXrender libXtst nss nss-util \
    nspr cups-libs dbus-libs expat libXScrnSaver libXrandr \
    alsa-lib pango cairo atk at-spi2-atk gtk3 gdk-pixbuf2 libgcc -y  

    ```

11. Install only the necessary components. (MicroStrategy will automatically indicate if there are dependencies.)

12. Ensure that the installation was successful by checking the onscreen messages and reviewing `./MicroStrategy/log/install.log`.

## Install MySQL Driver for MicroStrategy

1. On your MicroStrategy Intelligence server, follow instructions from [here](https://tecadmin.net/install-mysql-on-centos-redhat-and-fedora/) to install the MySQL 5.7 suite (MySQL Community Server and MySQL Service).

   **Note**: MicroStrategy 2019 does not work with MySQL 5.2 ODBC driver or MySQL 8.0 (mysql80-community-release-el7-1.noarch.rpm). Also, check `/usr/lib64/` for the file `libmyodbc8w.so`. This must be on your Intelligence Enterprise server before continuing.

2. Run `/home/mstruser/MicroStrategy/bin/mstrodbcadx` and configure an ODBC connection.

3. Click **Add** and enter the details on the next screen.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt4ee78c45a0869627/6a2c43cd5aa373861c18d64b/16083b577ca650-JOckqV.png)

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blta6f2420638a168da/6a2c437cbad3304fa96a858c/16083b577d1b01-1UpBlr.png)

4. Enter the username and password for the MySQL server and click **Test**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltf5b1b65f805e901a/6a2c43ba41a310ec4e004e10/16083b577d9511-aaxJzl.png)

## Configure MicroStrategy Intelligence Server on Red Hat

1. Run `/home/mstruser/MicroStrategy/bin/mstrlicmgr` and check the first screen (shown below). The license status should be **Active**; otherwise, click on the **License Administration** tab and follow the screen prompts to request an activation code. MicroStrategy will send the code to your registered email. Using the same screen enter the Activation code to activate the installation.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltecd1b70541134317/6a2c423a41a3106b0d004dc3/16083b577e1521-vjcdum.png)

2. Run `/home/mstruser/MicroStrategy/bin/mstrcfgwiz`.

3. Choose the first option “Create Metadata,….”.

4. Uncheck **History and Statistics** option and click **Next**.

5. Select **MYSQL8** in the next screen for DSN and provide your user name and password (same as ODBC connection). Click **Next**.

6. On “Metadata Repository Connection Information”, click **Apply**.

   **Note**: MicroStrategy metadata will be created in the metadata2019 database in MySQL Server.

7. Click **Configure Intelligence Server**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt98eca0fa51b674f7/6a2c42398db798e6096bcfe1/16083b577e896f-iztPTt.png)

   On the next screen the DSN information will be pre-populated.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt4e153202757f9f58/6a2c438df29ff5a82fb649d7/16083b577f084e-v6lbn3.png)

8. On the next screen do not enter any password, just click **Next**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8028d027a957fc3d/6a2c425003b37352d6908152/16083b578035d9-IJTRNg.png)

9. On the next screen enter `MSTR2019_IServer` as the Name and click **Next**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltf42e596c7e214839/6a2c438609d823673829c486/16083b5780ad0b-wve1Ar.png)

10. On the next screen leave the port numbers as the default selected and click **Next**.

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8ef863595344d905/6a2c4254f29ff565c4b64988/16083b5781195c-KGC7p0.png)

11. On the next screen no need to check “Configure SSL”, so click **Next**.

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt65f427ad7bb76d31/6a2c424acace3acf647ba1c4/16083b57818c46-a84BJK.png)

12. On the next screen check the “Enable…” checkbox for statistics and provide the same credentials of MySQL as you did before. Click **Next**.

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt5f416d9834e776b5/6a2c4387f29ff5fdd7b649d5/16083b5781f8f8-TsEsLE.png)

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt27ee364b6f957526/6a2c42518db798bf126bcfe5/16083b578277bb-4DS02N.png)

13. On the next screen, click **Next**.

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt6101b30d900babe3/6a2c425503b373dceb908154/16083b5783043f-1QmO3K.png)

14. On the next screen, click **Apply**. MicroStrategy will run the scripts and confirm completion. Click **Finish**.

    ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltcfe2dd390a781d64/6a2c438e7b02a476086455d3/16083b57838393-O6YmBW.png)

## Install JDK on Red Hat

MicroStrategy Web Interface requires Java to be installed, so you must download and install the JDK on the RedHat server that is running MicroStrategy 2019 Intelligent Enterprise.

1. Navigate to the [Oracle JDK download page](https://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html).

2. Download jdk-6u45-linux-x64-rpm.bin.

   **Note**: You may need an Oracle account to download the JDK.

3. Login to the Red Hat server which has MicroStrategy installed already and run `sudo su -` to assume root privileges.

4. Copy the file `jdk-6u45-linux-x64-rpm.bin` to the `/var/lib/mstr` directory and give the file execute permissions.
   ```shell
   chmod 744

   ```

5. Execute the file as `./jdk-6u45-linux-x64-rpm.bin`. It will unpack all components under `/opt/sun/javadb`.

6. Update `/home/mstruser/.bash_profile` with the following:
   ```shell
   export JAVA_HOME=/opt/sun/javadb
   export PATH=$JAVA_HOME/bin:$PATH

   ```

7. Verify that Java was installed correctly by running `java -version` and `javac -version`. Version 1.6.0\_45 should be listed.

## Install Tomcat 7 on Red Hat

Now you will need to install Tomcat 7 on your RedHat server running the MicroStrategy Intelligence server software.

1. Login to the Intelligence server.

2. Install Tomcat 7.
   ```shell
   sudo yum install tomcat

   ```
   **Note**: All important files will be installed in the `/usr/share/tomcat` directory.

3. Edit the `/usr/share/tomcat/conf/tomcat.conf` file and change the value for `JAVA_OPTS` as follows:
   ```shell
   JAVA_OPTS="-Xms1024m -Xmx2048m"

   ```

4. Start Tomcat.
   ```shell
   sudo systemctl start tomcat

   ```

## Start MicroStrategy Intelligence Server

Start the Intelligence Server by running `/home/mstruser/MicroStrategy/bin/mstrsvcmgr` and then click **Start**.

The Intelligence Server will start and the status will change to **Running**.

> **📝 Note**: Tiger VNC window may hang when the MicroStrategy Intelligence Server is running and hence make that terminal unusable.An alternate way of starting Intelligence server is from the command line by running the following from the `MicroStrategy/bin` directory:```shell
> sudo ./mstrctl -s IntelligenceServer  start
>
> ```

## Configure MicroStrategy Project Source

Once the MicroStrategy metadata is built and the Intelligence Server has been configured, it is necessary to create one or more Project Sources. A Project Source could be defined as a direct or indirect access point to the metadata. It is used by MicroStrategy Developer and other client interfaces to build, interact, and manage the contents of the MicroStrategy metadata.

You need to have a machine running MicroStrategy Developer for creating the project source. Follow these steps for creating a project source:

1. Go to the **Tools** > **Project Source Manager**.

2. Provide a Project Source name.

3. Click **Add** and then select **Server** for the connection mode.

4. Provide the Intelligence Server IP address, or server name, and a port number if applicable.

5. To open the web interface, open the following URL:
   ```shell
   https://<IP address of the machine running the I-server>:8080/ MicroStrategy/servlet/mstrWebAdmin

   ```

6. Use `Administrator` as the login username with no password.

## Accessing SingleStore database through MicroStrategy web interface

Login into the web interface and follow the steps below to access the SingleStore database:

1. Click on the MicroStrategy Web Home on the Administrator Page:

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt02f496fdc09d9927/6a2c42410fc2f430103f068e/16083b5783f429-EjW1tE.png)

2. You will be able to see the source project created above. Click on the project source.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt289fd014de3d1b79/6a2c425af756466087060d7b/16083b57846824-zKiUgu.png)

3. Enter the user name and password. A successful login will take you to the Home page of your project source.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt33fef84e7fb3e494/6a2c434d56043c80220ac192/16083b5784ea06-i76m63.png)

4. Select **Add External Data** under the Analyze section.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt0bece5a1e1c762a9/6a2c42bea36d0a5ea9b8212f/16083b57857613-WtyOIY.png)

5. On the Connect to your Data page, select **Databases**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt376e88edc3985088/6a2c424304f23bba85273a2d/16083b5785eae8-D20eue.png)

6. Select **Select Tables** option and then click **Next**.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt87b45352f04ea004/6a2c4374f29ff5662cb649d3/16083b5786635a-O2nn2i.png)

   You will be able to see the existing SingleStore data sources along with the databases.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt38728485132cb7a1/6a2c434e41a310007f004e0a/16083b5786de5a-4nsGam.png)

7. Select the required database and then drag drop the required tables on to the canvas. Click **Finish**.

8. Based upon your requirement, select the data access mode:

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt0cd959e7d3c6b073/6a2c4380a36d0aa8aab82157/16083b57875e86-w7EdJ1.png)

9. Save the connection and you are ready to create dossiers, documents, and much more.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt172fcc6252cc1b17/6a2c42445aa37379cd18d613/16083b5787de2c-TmAFcj.png)

***

Modified at: April 6, 2023

Source: [/db/v9.1/query-data/connect-with-analytics-and-bi-tools/connect-with-microstrategy/](https://docs.singlestore.com/db/v9.1/query-data/connect-with-analytics-and-bi-tools/connect-with-microstrategy/)

(An index of the documentation is available at /llms.txt)