[MicroStrategy][Oracle] MicroStrategy ODBC Driver for Oracle Wire Protocol: Trailing Spaces Issue with CHAR Semantics and UTF8 Encoding

Issue:

Users experience trailing spaces in query results when utilizing CHAR semantics and UTF8 encoding with the MicroStrategy ODBC Driver for Oracle Wire Protocol. The unexpected behavior arises when inserting values into CHAR datatype columns, leading to discrepancies between MicroStrategy and native Oracle drivers.

Root Cause:

The issue stems from a known bug in the MicroStrategy ODBC Driver for Oracle Wire Protocol by DataDirect Technologies. The driver mishandles CHAR semantics, causing additional spaces in query results.

Fix:

1. Add the connection option `ColumnSizeAsCharacter=1` to the specific DSN in the registry editor on Windows or the odbc.ini file on UNIX.

2. For Windows: Navigate to `HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI`, find the DSN, add a new string `ColumnSizeAsCharacter`, and set the value to 1.

3. For UNIX: Add `ColumnSizeAsCharacter=1` to the specific DSN section in the odbc.ini file.

   

Note: The workaround is not effective for specific versions like Oracle Wire Protocol driver version 5.00.00.50 in MicroStrategy 8.0.1 but works with version 5.10.00.53 in MicroStrategy 8.0.2.

Summarization:

The MicroStrategy ODBC Driver for Oracle Wire Protocol exhibits trailing spaces in query results with CHAR semantics and UTF8 encoding. To resolve, users should add the connection option `ColumnSizeAsCharacter=1` to the DSN in the registry editor (Windows) or odbc.ini file (UNIX). This workaround addresses the known bug in the driver, ensuring accurate query output.

LINK: https://community.microstrategy.com/s/article/KB13618-MicroStrategy-ODBC-Driver-for-Oracle-Wire-Protocol?language=en_US

[MicroStrategy][Oracle] 'Optional feature not implemented' error message appears when attempting to run a query with 'CURRENT_TIMESTAMP' function using the MicroStrategy ODBC Driver for Oracle Wire Protocol

 Issue:

Users encounter an 'Optional feature not implemented' error when executing a query with the 'CURRENT_TIMESTAMP' function using the MicroStrategy ODBC Driver for Oracle Wire Protocol. This function, crucial for generating current date or timestamp columns in reports, triggers the error due to a specific setting.

Message:

Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Optional feature not implemented.

Root Cause:

The error results from the absence of the 'Enable Timestamp With Timezone' setting in the DSN for the MicroStrategy ODBC Driver for Oracle Wire Protocol. This setting is necessary to support the 'CURRENT_TIMESTAMP' function.

Fix:

Enable 'Enable Timestamp With Timezone' in the ODBC DSN's 'Advanced' tab.

Use the MicroStrategy Connectivity Configuration Wizard to create the DSN, ensuring the correct setting.

If created on Unix, set EnableTimestampWithTimeZone=1 in the odbc.ini file.

Note: For existing DSNs created with the MicroStrategy Connectivity Configuration Wizard, use the Microsoft ODBC Data Source Administrator to modify the 'Enable Timestamp With Timezone' setting.

Summarization: The 'Optional feature not implemented' error arises when using the 'CURRENT_TIMESTAMP' function with the MicroStrategy ODBC Driver for Oracle Wire Protocol. To resolve, enable 'Enable Timestamp With Timezone' in the DSN settings using the MicroStrategy Connectivity Configuration Wizard or manual adjustments on Unix.

[MicroStrategy][Oracle] Connect failed. Error type: Odbc error. Odbc operation attempted: SQLDriverConnect. Optional feature not implemented. Driver's SQLSetConnectAttr failed. Socket closed. Connection Dead.

Issue:

The 'Connection Dead' error sporadically occurs when MicroStrategy Intelligence Server uses the MicroStrategy ODBC driver for Oracle Wire Protocol to connect to metadata.

This error, indicative of a disconnection from the database, specifically arises on UNIX machines hosting an Oracle database. The low value of the maxuproc setting (maximum processes per user) on UNIX is identified as a potential cause.

Root Cause:

The 'Connection Dead' error signals disconnection from the database, often linked to a low maxuproc setting on the Oracle UNIX machine. This setting restricts the maximum number of processes per user. Competition for resources, especially when other processes (e.g., a Web Server) are contending, can quickly exhaust this limit.

Fix:

Check the maxuproc setting value using lsattr -El sys0. The default is usually 128.

Increase the maxuproc value to a higher number (e.g., 4096) using

chdev -l sys0 -a maxuproc=4096.

Note: The 'Connection Dead' error is generic, and this technical note addresses one potential cause. Another possibility is covered in KB17194.

Published: Apr 2, 2017 Last Updated: Jul 20, 2023 Related Versions: 10.7, 10.6, 10.5, 10.4, 10.3, 10.2, 10.1, 10.0, 9.5.1, 9.4.1

Keys: Connection Dead
LINK: https://community.microstrategy.com/s/article/KB20403-Connection-dead-error-message-occurs-with-the?language=en_US

[MicroStrategy][Oracle][ODBC driver for Oracle][Oracle] Error while trying to retrieve text for error ORA-01019" error message occurs

 Issue:

Users encounter the "[ODBC driver for Oracle][Oracle] Error while trying to retrieve text for error ORA-01019" message when opening the Warehouse Catalog in MicroStrategy Developer 9.4.1 on a new machine. The error indicates a database connection failure.

Root Cause: The error arises from the presence of multiple 32-bit client libraries (SQAORA32.dll) on the same machine. The application is unable to determine which library to use when communicating with the driver, leading to the ORA-01019 error.

Fix: Remove the additional 32-bit client library from the client machine. In the case of a 3-tier connection, ensure the extra library is also removed from the Intelligence Server machine. This resolves the conflict and allows for proper communication between MicroStrategy and the Oracle driver.

Note: The issue is independent of MicroStrategy and relates to the configuration of third-party client libraries.

Published: Mar 31, 2017 Last Updated: Apr 12, 2019 Related Versions: 9.5.1, 9.4.1

LINK: https://community.microstrategy.com/s/article/KB249428-quot-ODBC-driver-for-Oracle-Oracle-Error-while-trying?language=en_US

[MicroStrategy][Oracle] best practice on integration of Oracle in MicroStrategy BI platform

Best Practices to integrate Oracle in MicroStrategy (Version 19c with DDA Mode)

Construction Considerations for Project Database Tuning: When initiating a project using MicroStrategy with Oracle as the warehouse (Version 19c in DDA mode), careful construction steps are crucial for optimal performance.

1. 1. VLDB True Temporary Table vs. Derived Table:

   • The default Intermediate Table Type is True temporary table, generating multi-pass queries.
   • Derived table and Common table expression produce single-pass SQL queries.
   • Experiment with different Intermediate Table Type settings, especially for Derived table and Common table expression, to find the optimal configuration for your specific business scenarios.

2. 2. Review and Set Database Parameters:

   • Collaborate with the project DBA to review and set database parameters according to instance resource quotas.
   • Adjust parameters like SGA_MAX_SIZE and SGA_TARGET based on available memory for improved performance.
   • MicroStrategy's in-house model testing showcased a 30% performance improvement by modifying these parameters.

3. 3. Create Index and Optimize SQL Execution Plan:

   • For traditional databases, a well-optimized query execution plan is crucial for performance.
   • Collect database statistics to inform the query execution plan.
   • Use Oracle AWR reports or database monitoring to identify low-performance queries.
   • Analyze low-performance queries and intervene by adding indexes or SQL hints to enhance the execution plan.
   • Modify the SQL execution plan to leverage a better SQL profile.

4. Example of SQL Tuning Adviser:

   • Presented an SQL statement with a 54s execution time before tuning.
   • Executed the SQL Tuning Adviser, resulting in an improved execution time of 2.37s.
   • Demonstrated the acceptance of a recommended new SQL profile to enhance the execution plan.

Additional Recommendations for Performance Enhancement:

1. 1. Table Compression:

   • For large data sizes (>80M) and less transactional tables (e.g., history tables), consider compressing them to free up space on data blocks and reduce table scan size during queries.

2. 2. Table Partitioning:

   • For data models involving querying on parts of large tables, consider using table partitioning based on specific columns (e.g., fiscal year/month or product categories) to decrease table scan costs.

3. 3. Enable Result Cache:

   • Oracle result cache stores query results in memory for reuse.
   • Enable result cache through VLDB settings or the driver connection to improve performance for queries with fixed result returns.
4. 

This comprehensive guide provides practical steps and considerations for tuning performance in MicroStrategy using Oracle 19c in DDA mode.

Regularly refer to MicroStrategy's documentation for the latest updates and version-specific recommendations.

LINK: https://community.microstrategy.com/s/article/KB484932-Best-Practice-for-Performance-Tuning-using-Oracle?language=en_US

[MicroStrategy][Oracle] MicroStrategy Oracle ODBC Driver Error Message with “Driver does not support this function

 Issue:

When executing dossiers or reports in MicroStrategy using Oracle as a data warehouse with parameterized queries enabled, users encounter the error message:

"Error: QueryEngine encountered error: OdbcPreparedStatement::GetParameterDataTypes() failed. [IM001:0: on SQLHANDLE] [Microsoft][ODBC Driver Manager] Driver does not support this function."

This issue arises when attempting parameterized queries, and the feature is confirmed to be enabled.

Root Cause: The error is caused by the Oracle ODBC Driver encountering a limitation – it does not support the SQLDescribeParam function. This limitation becomes apparent when executing parameterized queries in MicroStrategy.

Fix: To address the issue, users need to check and add a specific parameter for Windows-created Oracle ODBC DSN (Data Source Name).

For Oracle Linux ODBC DSN and Oracle ODBC DSN-less connection strings, the parameter "EnableDescribeParam = 1" should be added.

This action ensures compatibility and resolves the "driver does not support this function" error.

Note: MicroStrategy's Technology team is currently reviewing the situation for potential enhancement in an upcoming MicroStrategy release. Always refer to MicroStrategy's documentation for the latest information and updates related to specific versions.

LINK: https://community.microstrategy.com/s/article/KB484759-MicroStrategy-Oracle-ODBC-Driver-Error-Message-with-Driver-does-not-support-this-function?language=en_US

[MicroStrategy][Oracle] Kerberos Authentication is not supported by this Oracle Server.

 Issue:

When attempting to connect Oracle in MicroStrategy using the provided JDBC driver and the user's password consists of only spaces, an error arises. Users creating an Oracle DB role on MicroStrategy web with a JDBC connection string, including spaces as a password, encounter the following error message: "[MicroStrategy][Oracle JDBC Driver]Kerberos Authentication is not supported by this Oracle Server." This occurs when Kerberos authentication is attempted due to the "auto" AuthenticationMethod connect option with no specified username/password.

Root Cause: The issue is rooted in the attempt to perform Kerberos authentication on the Oracle Server, which does not support this method when the password contains only spaces. The error is triggered by the default authentication behavior when no explicit username/password is provided.

Fix: A workaround is available to enclose the space password with curly braces {} in the JDBC connection string. This modification allows for successful connection to Oracle in MicroStrategy even when the password consists solely of spaces.

Note: This Knowledge Base article is relevant to MicroStrategy versions 2019 and later. Always refer to MicroStrategy's documentation for the latest information and updates related to specific versions.

Kerberos Authentication is not supported by this Oracle Server. The driver attempted Kerberos authentication because the AuthenticationMethod connect option was "auto" and no username/password was specified

LINK: https://community.microstrategy.com/s/article/KB484361-Connect-Using-Oracle-JDBC-Driver-When-the-Password-Consists-of-Only-Spaces?language=en_US