Sunday, June 26, 2011

OBIEE 11g: table does not exist- nQSError: 43119

OBIEE 11g: ORA-00942: table or view does not exist at OCI call OCIStmtExecute Error

I am working on OBIEE 11g - Database is Oracle 10g-

Set up: I have a presentation hierarchy Region>Country>State>City build from a single Geography Dimension. I have three fact columns coming from three materialized views. 

Now, when this Presentation Hierarchy and three fact columns are used to create a request, and I try to drill down, BI query is throwing a "table does not exist" error. I get the following error.

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P 
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

Instead of the presentation hierarchy if I use high level column (Region), I am able to drill down and get correct numbers. I am able to create a report with Region, Country, State, City, Fact1, Fact2, and Fact3 without getting an error. 

With the error, I also saw the query log with too long query and many SAWITHn, SACOMMONnnnn clauses. 

I enabled PERF_PREFER_INTERNAL_STITCH_JOIN in DB Features in repository and now the hierarchy and reports work fine. You will get a warning that DB Features do not match default values during consistency check, you can ignore that.

Hope it helps someone.