[nQSError: 59044] Unable to create table TT* error on OBIEE 11g

[nQSError: 59044] Unable to create table TT* error on OBIEE 11g 

Some time when you configure a new connection pool and through that connection poo, if trying to run OBIEE queries, especially when using one report on the filter of the other report  , you may get the error similar to below.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 59044] Unable to create table TT2OCI7GIAP2N676894873010H3 [nQSError: 17001] Oracle Error code: 1031, message: ORA-01031: insufficient privileges at OCI call OCIStmtExecute: CREATE TABLE TT2OCI7GIAP2N676894873010H3 ( column1 VARCHAR2(80 CHAR) ). [nQSError: 17011] SQL statement execution failed. (HY000)

Depending on the Database, insufficient privilege error will change. In the above error I used Oracle Database.

What is happening over here is, when using one report on the filter of another report, OBIEE creates subqueries and instead of storing the results of the subqueries on BI Server side, it creates TEMP tables on Database. This happens when you set Persist connection pool while creating your connection pool on the physical layer of the RPD. See below.

The User ID on the connection pool used here does not have CREATE TABLE privilege on the Database to create TEMP tables. Hence, you are getting the error.

You can simply clear persist Connection Pool settings on the connection pool and save the RPD and reload metadata, which will solve the issue. Or if you still want to keep Persist Connection pool for improving performance of your Reports, you can give CREATE TABLE privilege to the USER ID given on the connection pool.