KB485555: Old DB instances connecting to Exasol showing invalid characters after upgrading to MicroStrategy 2021 Update 6

Symptom
Previously we have set the default unixEncoding for Exasol connection to UTF-16 due to an Exasol driver encoding issue.
Exasol have fixed the bug in EXASOL-2989.
After Exasol driver upgraded from 7.0.4 to 7.1.7 along with MSTR 11.3.6, only UTF-8 could work for Linux OBDC driver.
For existing Exasol db role created in older IServer, it cannot fetch data correctly from Database.

Cause
We have confirmed with Exasol engineer, this is an expected behavior after a bug fixing.
"The old driver was not setting the encoding for W(unicode) data to utf8 as requested by the driver manager. This was a bug. Our driver was using the fallback from the driver manager which is utf16. Now only set the encoding for Unix drivers for database instance to UTF-8, the Exasol ODBC driver on Linux platform could work properly.  The new driver correctly sets the encoding as requested by the driver mamager "SQLSetConnectAttr(ConnectionHandle=2, Attribute=1061,Value=SQL_ATTR_APP_WCHAR_TYPE = SQL_DD_CP_UTF8, StringLength=-5)" and uses this for the W(unicode) strings of data."
Action

1. In the Strategy database instance and database connection change the character set encoding to UTF-8

Once this change is made, restart the Intelligence Server and Strategy will now use the right type of unicode when processing the data out of the Exasol database.
 

1. For db role creaed from MSTR Web, simply edit the db role and save it without any change, it will change the encoding in the background.

Note: In case you are using an older version of web, before editing the db role,  please modify DBProperties.xml and restart the web, DBProperties.xml could be found under Strategy Web folder, such as  C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\MicroStrategy\WEB-INF\xml
AS-IS:
<dbVersion n="Exasol7x" v="343" pq="1" unixEncoding="0"/> <!-- R: EXASolution -->
TO-BE:
<dbVersion n="Exasol7x" v="343" pq="1"/> <!-- R: EXASolution -->
 

1. For db connection created in data source management or developer, we can also use Strategy REST API to update encoding

Workflow, you can visit http://<iserver-web >:8080/MicroStrategyLibrary/api-docs/index.html:

1. Call http://<iserver-web>:8080/MicroStrategyLibrary/api/auth/login to authenticate a user and get x-mstr-authtoken
2. Call http://<iserver-web>:8080/MicroStrategyLibrary/api/datasources/connections using x-mstr-authtoken to get all connections, get connection id of connections whose database.type equals to “exa_solution”
3. Call http://<iserver-web>:8080/MicroStrategyLibrary/api/datasources/connections/<connectionId> with Payload

{
  "operationList": [
    {
      "op": "replace",
      "path": "/charEncodingUnix",
      "value": "utf8"
    }
  ]
}

1. Restart Iserver

Appendix
https://www.progress.com/tutorials/odbc/unicode
https://www.exasol.com/support/browse/EXASOL-2989
MicroStrategy REST API
Version:
11.3.6