KB38427: Error occurs when attempting to perform element browse against Hive/Hadoop database in MicroStrategy Developer 10.x

SYMPTOM:

In Strategy Developer 10.x, the following error is obtained while attempting to perform element browse against a Hive/Hadoop 0.7 database:
 
"QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect."
 
Refer to technical note KB13189 to see the exact SQL performed when browsing the element:
KB13189: How to trace the SQL generated by microStrategy Engine 9.x
 
Following entries are caught in the SQL trace:
Sample Code/Error
(QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Connection String: DSN=Hive;DATABASE=default;HOST=XX.XX.XXX.XXX;PORT=10000;FRAMED=;. SQL Statement:
select     distinct a11.XYZ as XYZ
from    dim_ABC        a11
order by                1 asc.. Error in Process method of Component: QueryEngineServer, Project Hive Project, Job 78, Error Code= -2147212544.)
 

CAUSE:

Hive database does not support ORDER BY <ordinal>. Because of this limitation, element browsing can’t be performed against lookup tables in Hive.
 
 

WORKAROUND:

Workaround 1:
 
           Refer technical note below to re-load the revised 'DATABASE_Hive0.7x.PDS' object in Strategy 9.2.1
KB38428: How to re-load the 'DATABASE_Hive0.7x.PDS' object in MicroStrategy 9.2.1
 
NOTE: The updated 'Hive 0.7x' object is available out-of-the-box in 9.2.1m.
 
Workaround 2:
 
            Use Multi-Source to store lookup tables in some other database (for example MySQL). This improve performance of element browsing queries to some extent.