The following steps document how to add a connector to EXAsolution 6 to Strategy. This connector enables Strategy Web based connectivity workflows and enhances the Relational Engine with support for EXAsolution 6

Applies to

• Strategy Secure Enterprise 10.4 (hotfix 5 or later applied)
• Strategy Secure Enterprise 10.9 or later
• Unified Quoting as per KB483540 is supported as of Strategy 2021

Table of Contents

1. Prerequisites
2. Installation
3. Install database object
4. Install driver on each Intelligence Server in the cluster
5. Install connector in all MicroStrategy Web machines in the cluster
6. Validating correct installation
7. Uninstall
8. Remove database object
9. Remove driver
10. Remove connector from MicroStrategy Web

1. Prerequisites

1. Download and copy the connector file(EXAsolution6.zip) onto the machine hosting the Strategy Secure Enterprise installation 
2. Extract the zip file to its own directory, separate from the existing Strategy installation files (example: /tmp/exasol or C:\exasol). This directory will be referred to as <MSTR_CONN_PATH> for the remainder of this technical note.
3. Strategy Secure Enterprise should be installed and configured, including an instance of Strategy Developer, Strategy Web, and the Strategy Intelligence Server

2. Installation

Use the files attached below to complete this three part installation: 

1. Install database object
2. Install driver
3. Install connector in Strategy Web

3. Install database object

1. Copy database.pds file onto a machine with Strategy Developer installed(A newer version new_database_m2021.pds has been uploaded to the attachment. Please check the comment for more details). 
2. Follow the steps in KB43537 to load the database object in the database.pds file into Strategy metadata.

KB43537: How to install DBMS objects provided by Strategy Technical Support
 

4. Install driver on each Intelligence Server in the cluster

1. Download EXAsolution JDBC driver files (can be found here) to all the Intelligence Server machines in the cluster
2. Copy JDBC files into Strategy JDBC folder
   
• On Windows the default location is "C:\Program Files (x86)\Common Files\Strategy\JDBC" 
• On Linux the default location is “/opt/mstr/MicroStrategy/install/JDBC”

Alternatively, it is possible to use 64-bit ODBC drivers both on Windows and Linux. Please create Data Source Names (DSN) and test connectivity using the Strategy DB Query tool. If you need assistance creating the odbc.ini entry for Exasol, use Exasol config_odbc tool to create a new odbc.ini file containing all the required information

cd  EXASolution_ODBC-*
./config_odbc --host=10.0.0.1 --port=1234 --user=doe --password=test --odbcini=exasolodbc.ini

sudo yum install perl
sudo yum install perl-Digest-MD5 -y

1. Restart Intelligence Server.
2. To validate a correct installation to this point
   
1. Open Developer
2. Create a new DBinstance selecting the new connector,
3. Open WHCatalog, select the newly created DBinstance from last step
4. Expected behavior: a list of tables from database instance are listed on the left panel
5. Right-click on any table and select “Table Structure”.
6. Expected behavior: a new window displays all columns from the table with correctly mapped data types – no data type shows as “unknown”

5. Install connector in all Strategy Web machines in the cluster

1. Copy the file dbproperties.xslt and AddConnector.jar files to all the machines that run Strategy Web. Use the location of the files to replace <location1> for the command in step 3.
2. Find the location of the DBProperties.xml file. Use this location to replace <location2> as the path to the DBProperties.xml file in the command in step 3. The file can be found in the directory where Strategy Web is deployed. This path will be different depending on the application server used. The following are common examples:
   
• Microsoft IIS: C:\Program Files (x86)\Strategy\Web ASPx\WEB-INF\xml\DBproperties.xml
• Tomcat (on Windows): C:\Program Files (x86)\Common Files\Strategy\Tomcat\apache-tomcat-8.0.30\webapps\Strategy\WEB-INF\xml\DBProperties.xml
• Tomcat (on Linux): /opt/apache/tomcat/apache-tomcat-8.0.43/webapps/MicroStrategy/WEB-INF/xml/DBproperties.xml
3. Run the following command replacing <location2> with the folder where DBProperties.xml file is located.
   
• Note: Running the command requires the privileges to copy and modify the existing dtmapping.pds file
• Note: on Windows the <location> needs to finish with a backslash (“\”) on Linux with a forward slash (“/”) character
• Note: the command will create a backup of the original DBProperties file in <location2>.

  java -jar <location1>AddConnector.jar --target <location2>DBProperties.xml --file <location1>dbproperties
 

1. After running the above command, please restart your application server.

6. Validating correct installation

1. Connect to Strategy Web, select ‘Add External Data’ and choose the option for Database sources.
2. Choosing a query type (Pick Table, Type a Query, Build a Query).
3. Choose the radio button for DSN-less Data Sources.
4. Open the “Database” drop down list. You should be able to see EXAsolution as an option.

7. Uninstall

To remove the installation of this connector it is necessary to reverse the installation steps.

1. Remove database object
2. Remove driver
3. Remove data type mapping
4. Remove connector from Strategy Web

8. Remove database object

1. Open Strategy Developer
2. Remove any dependency on the connector. This might require modifying or deleting any DBinstance that uses this connector.
3. Open any DBinstance for editing, click on Upgrade
4. Select the database object from the list of available database objects on the lower right panel
5. Click on the “left arrow” to remove the database object

9. Remove driver

• Delete JDBC driver files from all Intelligence Server machines

10. Remove connector from Strategy Web

• Connect to Strategy Web machine and find the backup file created as part of the installation of this connector. E.g. DBProperties.xml.bak20170913185806
• Remove file DBProperties.xml
• Rename DBProperties.xml.bak20170913185806 to DBProperties.xml
• Restart Application Server