Microsoft Azure Cosmos DB

The steps in this article describe how to add a connector of Azure Cosmos DB to Strategy. This connector enables Strategy Web based connectivity workflows and enhances the Relational Engine with support for Azure Cosmos DB.
 
 
 

Prerequisites

• An Azure Cosmos DB instance has already been provisioned through the following site: 

• https://azure.microsoft.com/en-us/services/cosmos-db/

• The Azure Cosmos DB ODBC driver has been installed on the windows machine containing your Strategy Web server and i-server.
  
• For more information on installing the Azure Cosmos DB ODBC driver, please refer to the following link:
  
• https://docs.microsoft.com/en-us/azure/cosmos-db/odbc-driver

This article applies to the following Strategy versions:

• Strategy Secure Enterprise 10.4 (hotfix 5 or later applied)
• Strategy Secure Enterprise 10.9 or later

Contents

How to Install the Microsoft Azure Cosmos DB connector
Pre-requisites
Installation
Install database object
Install driver on each Intelligence Server in the cluster
Install data type mapping on each Intelligence Server in the cluster
Install connector in all MicroStrategy Web machines in the cluster
Validating correct installation
Uninstall
Remove database object
Remove driver
Remove data type mapping from all Intelligence Server machines
Remove connector from MicroStrategy Web
 
 

Pre-requisites

1. Download and copy the connector file(AzureCosmosDB.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/AzureCosmosDB or C:\AzureCosmosDB). 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
4. Strategy Intelligence Server must be installed on Windows machine, as Azure Cosmos DB ODBC driver is only available on Windows.

Installation

The installation of this connector comprises of 4 steps.

1. Install database object
2. Install driver
3. Install data type mapping
4. Install connector in Strategy Web

Install database object

1. Copy database.pds file onto a machine with Strategy Developer installed.
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

Install driver on each Intelligence Server in the cluster

• Download, install Cosmos DB's ODBC driver files, and create DSN connecting to your Azure Cosmos DB database (Instructions can be found here) on all the Intelligence Server machines in the cluster

Install data type mapping on each Intelligence Server in the cluster

1. Copy the file dtmapping.xslt and AddConnector.jar files to all the Intelligence Server machines that need to be upgraded
2. Find the location of the dtmapping.pds file of the Intelligence Server installation
   
• On Windows the default location is C:\Program Files (x86)\Common Files\Strategy, also referred to in the  $MSTR_CLASSPATH environment variable
3. Run the following command replacing <location> with the folder where dtmapping.pds file is located.
   
• Note: Running the command requires the privileges to copy and modify the existing dtmapping.pds file
• Note: the command will create a backup of the original file in <location>.

java -jar AddConnector.jar --target “<location>\dtmapping.pds” --file dtmapping.xslt

1. Restart Intelligence Server.
2. To validate a correct installation to this point:
   
• Open Developer
• Create a new DBinstance selecting the new connector “Azure Cosmos DB”

1. Open WHCatalog, select the newly created DBinstance from last step
2. Expected behavior: a list of tables from database instance are listed on the left panel
3. Right-click on any table and select “Table Structure”.
4. Expected behavior: a new window displays all columns from the table with correctly mapped data types – no data type shows as “unknown”

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
2. Find the location of the dbproperties.xml file. In the next step replace <location> with the path to DBProperties.xml file which 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
3. Run the following command replacing <location> 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: the command will create a backup of the original file in <location>.

java -jar AddConnector.jar --target “<location>\dbproperties.xml” --file dbproperties.xslt

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

Validating correct installation

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

• Note: Due to a known issue, after saving this data source with a DSN-less connection, the Authentication Key parameter will be lost. The workaround for this issue is to create the data source with DSN connection. Please leave a comment if you wish to obtain more information.

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

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

Remove driver

• Delete all DSN create with Cosmos DB ODBC driver, and uninstall the ODBC driver

Remove data type mapping from all Intelligence Server machines

1. Connect to Intelligence Server machine and find the backup file created as part of the installation of this connector. E.g. DTMAPPING.PDS.bak20170913122250
2. Remove file DTMapping.pds
3. Rename DTMAPPING.PDS.bak20170913122250 to DTMapping.pds
4. Restart the Intelligence Server

Remove connector from Strategy Web

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