Prerequisites

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

• MicroStrategy Desktop version 11.1.1
• Either the MySQL 64-bit ODBC driver (version 8.0.15) or the MariaDB 64-bit ODBC driver (version 3.1.0). 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 MemSQL

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 MemSQL via this data source. For TCP/IP Server, specify the IP address or server name where the MemSQL master aggregator node resides. Specify the User and Password to use to connect to MemSQL.

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 MemSQL 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 step 4 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.

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

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-MemSQL solution.

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 MemSQL 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 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:

   su – mstruser
   

   vncpasswd
   

   Set it to mstr123.

6. Start the vncserver as root.

   /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.

2. Open a terminal window and connect to the VNC server.

   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 mstr123. If successful, a Tiger VNC window will open with a built-in terminal window.

<div class="callout-title">
    <i class="fas fa-info-circle icon" aria-hidden="true"></i> Info
</div>

Install MicroStrategy 2019 on Red Hat 7.x

1. On your server, download MicroStrategy 2019 Intelligent Enterprise from MicroStrategy. 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.

   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).

   sudo yum install perl
   

10. Install the following Linux packages on the Red Hat machine as shown below:

    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 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.

   

   

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

   

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.

   

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.

   

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

   

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

   

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

   

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

    

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

    

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

    

    

13. On the next screen, click Next.

    

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

    

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.

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.

   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:

   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.

   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:

   JAVA_OPTS="-Xms1024m -Xmx2048m"
   

4. Start Tomcat.

   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.

<div class="callout-title">
    <i class="fas fa-info-circle icon" aria-hidden="true"></i> Info
</div>

sudo ./mstrctl -s IntelligenceServer  start

Configure MicroStrategy Project Source

Once the MicroStrategy metadata is built and the Intelligence Server has beenconfigured, 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 menu -> 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:

   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 MemSQL database through MicroStrategy web interface

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

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

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

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

4. Select Add External Data under the Analyze section.
   

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

6. Select Select Tables option and then click Next.
   

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

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:

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