KB483056: Huawei Fusioninsight Analytic Platform-GaussDB

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Summary
Huawei GaussDB (https://support.huawei.com/enterprise/zh/cloud-computing/fusioninsight-libra-pid-21407429) is a massive MPP analytical database in the Huawei FusionInsight family which provides PB level data warehousing capability for analytics/BI scenarios. Huawei GaussDB provides compatibility with SQL92/SQL2003 standard and exposes JDBC and ODBC connectivity.
GaussDB enables sub-second SQL query latency on a petabyte-scale dataset, providing high concurrency at internet-scale, and empowering analysts to architect BI on Hadoop with an industry-standard data warehouse, and with business intelligence methodology.
The GaussDB Compatible with PostgreSQL which enables connectivity to Strategy, enhancing the Strategy product with support for Huawei Big Data Platform.
 
For more information please refer to : https://www.huaweicloud.com/en-us/product/dws.html
For Chinese Version, please refer: KB483267 华为Fusioninsight 分析平台-GaussDB连接MicroStratgy实践教程
Table of Contents

1. Prerequisites
2. Installation

2.1. Update DATABASE.PDS File
2.2. Update DTMAPPING.PDS File
2.3. Update ConnstrMapping.json File
2.4. Install connector in all Strategy Web machines in the cluster
2.5. Install connector in Strategy Workstation/Desktop

1. Validating Correct Installation
2. Uninstallation
3. Remove Database Object
4. Remove connector from Strategy Web
5. Remove connector from Strategy WorkStation/Desktop

                 
1. Prerequisites
1.1. Strategy Intelligent Enterprise should be installed and configured, including an instance of Strategy Developer, Strategy Web, and the Strategy Intelligence Server. Here is the link for download:  https://community.strategy.com/products. In the download site, please choose Intelligent Enterprise and set the version into Strategy 2019 Update 1. Click download button, your download will be start immediately. All the attachment file was modified based on the Strategy 2019 Update 1 version.
1.2. Using ODBC to connect (Connector Download Link:Huawei ODBC Connector)
The GaussDB ODBC driver should be installed on the machine containing your Strategy Intelligence Server. 

• In Windows IServer:

1. Download Gauss200-VxxxRxxxCxx-Windows-Odbc.tar.gz, Depress the tar file and click psqlodbc.msi（x86）or psqlodbc_x64.msi（x64）according to your operating system.
2. Click the application for installation.

• In Linux IServer:

• UnixODBC should be installed first (Download Link: UnixODBC 2.3.0 ).
• Copy the dowloaded file to the Linux machine and run below commands in terminal.

• After running the command, the unixODBC should be installed on your machine. The default install path in Linux is:  /usr/local.
• Download Gauss200-VxxxRxxxCxx-Linux-Odbc.tar.gz or PostgreSQL native ODBC Driver, depress the tar file.  Copy all files in lib directory to /usr/local/lib, replace all same name files.
• Then move psqlodbcw.la and psqlodbcw.so from obcd/lib into /usr/local/lib. Update odbc.ini and odbcinst.ini accordingly. Detail steps please refer to the GaussDB manual Book.

1.3. Using JDBC to connect (Connector Download Link: Huawei JDBC Connector.)
Attention!  Huawei Shipped JDBC driver have some compatible issue with MSTR data import functions. Recommend using native postgresql JDBC driver instead. 
 
1.4. Please be aware that, GaussDB also support the native postgresql connector. Click the link for download. 

•  ODBC: https://odbc.postgresql.org/   
•  JDBC: https://jdbc.postgresql.org/

 
2. Installation
The installation of this connector comprises of 5 steps.

2.1. Update DATABASE.PDS File
2.2. Update DTMAPPING.PDS File
2.3. Update ConnstrMapping.json File
2.4. Install connector in all Strategy Web machines in the cluster
2.5. Install connector in Strategy Workstation/Desktop
 
Notes: Here is the common path which store the 2 files describing in the below steps
Files:

• DATABASE.PDS
• DTMAPPING.PDS
• ConnstrMapping.json

Path:
Windows

• $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\DATABASE.PDS
• $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\DTMAPPING.PDS
• $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\ConnstrMapping.json

Linux

• $MSTR_INSTALL_PATH$\MicroStrategy\DATABASE.PDS
• $MSTR_INSTALL_PATH$\MicroStrategy\DTMAPPING.PDS
• $MSTR_INSTALL_PATH$\MicroStrategy\ConnstrMapping.json

 2.1. Update DATABASE.PDS File

1. You can find DATABASE.PDS file in this KB attachment, This file contain the GaussDB part which will enable the connection to GaussDB
2. Back up the original DATABASE.PDS file, then copy file in this KB attachment and replace the file located in $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\DATABASE.PDS. 
3. Follow the steps in KB43537  to reload the DBMS object in the DATABASE.PDS file into Strategy metadata. KB43537: How to install DBMS objects provided by Strategy Technical Support.
4. You will see a new DB type called GaussDB display in the database interface.

 2.2. Update DTMAPPING.PDS File

1. You can find DTMAPPING.PDS file in this KB attachment, This file contain the GaussDB part which will enable the data type mapping relationship from MSTR to GaussDB
2. Back up the original DTMAPPING.PDS file, then copy the file DTMAPPING.PDS in this KB attachment and replace the original file located in $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\DTMAPPING.PDS

2.3. Update ConnstrMapping.json File

1. You can find ConnstrMapping.json file in this KB attachment, This file contain the GaussDB part which will enable dossier import/export functions
2. Back up the original ConnstrMapping.json file, then copy the file ConnstrMapping.json in this KB attachment and replace the original file located in $MSTR_INSTALL_PATH$\Common Files\MicroStrategy\ConnstrMapping.json
3. Restart Intelligence Server.

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

1. You can find DBProperties.xml  in this KB attachment, This file contain the GaussDB part which will enable the web to show GaussDB gateway and enable the dossier import/export functions
2. Back up the original DBProperties.xml file, then copy the file DBProperties.xml  in this KB attachment and replace the original file. Below is the common path for this file:

• Microsoft IIS: C:\Program Files (x86)\MicroStrategy\Web ASPx\WEB-INF\xml\DBproperties.xml
• Tomcat (on Windows): C:\Program Files (x86)\Common Files\MicroStrategy\Tomcat\apache-tomcat-8.0.30\webapps\MicroStrategy\WEB-INF\xml\DBProperties.xml
• Tomcat (on Linux): /opt/apache/tomcat/apache-tomcat-8.0.43/webapps/MicroStrategy/WEB-INF/xml/DBproperties.xml

For configuring the connector icon:

• By default, the icon in Data Import home page is general icon for Database like below

• To customize your own icon, create a 75*75 png file with the exactly same name in dbProperties.xml  <dbID n="GaussDB" v="172"/>. Put this image to  <MSTR_WEB_SERVER>\javascript\mojo\css\images\DI\connectors, then restart web server. 

2.5. Install connector in Strategy Workstation/Desktop

1. Update DBProperties.json
   
1. Decompress the utils.zip found in this KB attachement.
2. Put the utils folder in Web server install location: <MSTR_WEB_SERVER>
3. Run the run.bat script as administrator from the command line on windows, you can also run run.sh script in Mac. Note that running the script requires that you install JRE 8 or above
4. After running the script, the tool will automatically convert original DBProperties.xml into two new files -- DBProperties.json32 and DBProperties.json64, rename DBProperties.json64 to DBProperties.json and replace the old file in workstation/Desktop with this new file
   
• File location: <MSTR_WINDOWS_WORKSTATION/Desktop>\code\config for Windows Platform.
• File location: <MSTR_MAC_WORKSTATION/Desktop>/Contents/Frameworks/OneTierDashboardWindow.framework/Versions/A/Resources/code/config for MAC Workstation
2. To customize your own icon, put the icon image named "GaussDB.png" in the location 

• File location: <MSTR_MAC_WORKSTATION/Desktop>/Contents/Frameworks/OneTierDashboardWindow.framework/Versions/A/Resources/code/javascript/mojo/css/images/DI/connectors for MAC Workstation
• File location: <MSTR_WINDOWS_WORKSTATION/Desktop>\code\javascript\mojo\css\images\DI\connectors\ for Windows Workstation

1. Replace the DATABASE.PDS and DTMAPPING.PDS in workstation with the two files you configured in previous steps.
   
• DATABASE.PDS and DTMAPPING.PDS location: C:\Program Files (x86)\Common Files\Strategy
  
2. Restart Workstation/Desktop.
3. Notice that GaussDB can be used in Mac Workstation/Desktop, but you must use JDBC to connect.

3. Validating correct installation

1. Connect to Strategy web, click “Add external data”, type “GaussDB” in filtering bar at the upper-right corner, and you will find GaussDB lists in the main panel.
2. Choosing a query type (Select Tables, Type a Query, Build a Query)
3. Click the "+" button on the right of the DATA SOUCES, you can using DSN or DSN-less for connect to GaussDB. Below is the common connection string to connect to GaussDB:

• ODBC:

DRIVER={PostgreSQL Unicode(x64)}; Servername=xxx.xxx.xxx.xxx; Port=xxxx; Database=xxxx; DriverUnicodeType=1; UseDeclareFetch=1; Fetch=1000;

• JDBC:

JDBC;DRIVER={org.postgresql.Driver};URL={jdbc:postgresql://xxx.xxx.xxx.xxx:xxxx/databasename};
4. Uninstallation
To remove the installation of this connector it is necessary to reverse the installation steps.

1. Remove database object
2. Remove connector from Strategy Web
3. Remove connector from Strategy WorkStation/Desktop

5. Remove Database Object

1. Open Strategy Developer
2. Remove any dependency on the connector. This might require modifying or deleting any Database Instance that uses this connector.
3. Open any DB instance 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

6. Remove connector from Strategy Web

1. Replace all 4 files with original file, then GaussDB should be removed.
2. Restart Intelligence Server.

7. Remove connector from Strategy WorkStation/Desktop

1. Replace all 3 files with original file, then GaussDB should be removed.
2. Restart Application.