Not able to open pivot table view

Question

Hello expert I have developed an analysis in analytics portal. When i am trying to open Pivot table view, it is giving me following error. What could be reason for this? I am using OBIEE 11g in virtual machine for learning purpose. I checked, al the services are running. There was a problem retrieving the response data: Internal Server Error (500). Error 500--Internal Server Error

Error 500--Internal Server Error

From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:

10.5.1 500 Internal Server Error

The server encountered an unexpected condition which prevented it from fulfilling the request.

Thanks Sas

Joel Acha · Answer

I'm not sure if this is of relevance but check this: Oracle Support Document 2115367.1 (OBIEE 11g: Using The AGO Function When Fragmentation In The Fact Source Fails With Error 'nQSError 12002') can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2115367.1

Also, you are using very old software on a very old server so there are bound to be issues just based on this point alone.

SasDutta · Answer

Hi I checked couple of more logs. One is nqserver.log. There i see following error message. [2018-03-07T09:36:20.000+05:30] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1534] [nQSError: 12002] Socket communication error at call=recv: (Number=10054) An existing connection was forcibly closed by the remote host. [2018-03-07T09:36:20.000+05:30] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 152c] [nQSError: 12002] Socket communication error at call=recv: (Number=10054) An existing connection was forcibly closed by the remote host. I believe this is the reason of the failure. I checked nqquery.log. There is see the query is successfully fetching data. Below is the log output [2018-03-07T09:36:19.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-0] [] [ecid: e0198d9168e1ee5b:4597e79d:161f38f3c48:-8000-0000000000001a57] [tid: 1534] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] ############################################## [[ -------------------- SQL Request, logical request hash: db6c9e2e SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/RRITEC_REPORT/Pivot table testing';SELECT 0 s_0, "Supplier Sales Rep Saswata"."Dim - Customer"."District" s_1, "Supplier Sales Rep Saswata"."Dim - Customer"."Region" s_2, "Supplier Sales Rep Saswata"."Dim - Period"."Monthcode" s_3, "Supplier Sales Rep Saswata"."Dim - Period"."Year" s_4, "Supplier Sales Rep Saswata"."Dim - Product"."Itemtype" s_5, "Supplier Sales Rep Saswata"."Fact - Sales"."Dollars" s_6 FROM "Supplier Sales Rep Saswata" ORDER BY 1, 5 ASC NULLS LAST, 4 ASC NULLS LAST, 3 ASC NULLS LAST, 2 ASC NULLS LAST, 6 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY ]] [2018-03-07T09:36:19.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-23] [] [ecid: e0198d9168e1ee5b:4597e79d:161f38f3c48:-8000-0000000000001a57] [tid: 1534] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- General Query Info: [[ Repository: Star, Subject Area: core, Presentation: Supplier Sales Rep Saswata ]] [2018-03-07T09:36:19.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-2] [] [ecid: e0198d9168e1ee5b:4597e79d:161f38f3c48:-8000-0000000000001a57] [tid: 1534] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- Logical Request (before navigation): [[ RqList 0 as c1 GB, Dim - Customer.District as c2 GB, Dim - Customer.Region as c3 GB, Dim - Period.Monthcode as c4 GB, Dim - Period.Year as c5 GB, Dim - Product.Itemtype as c6 GB, Dollars:[DAggr(Fact - Sales.Dollars by [ Dim - Period.Year, Dim - Period.Monthcode, Dim - Customer.District, Dim - Customer.Region, Dim - Product.Typecode, Dim - Product.Itemtype] )] as c7 GB, Dim - Product.Typecode as c8 GB OrderBy: c1 asc, c5 asc NULLS LAST, c4 asc NULLS LAST, c3 asc NULLS LAST, c2 asc NULLS LAST, c6 asc NULLS LAST ]] [2018-03-07T09:36:19.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-50] [] [ecid: e0198d9168e1ee5b:4597e79d:161f38f3c48:-8000-0000000000001a57] [tid: 1534] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- The logical query hits the plan cache [[ plan RqList <<10780>> [for database 0:0,0] /* FETCH FIRST 65001 ROWS ONLY */ 0 as c1 [for database 0:0,1], D1.c2 as c2 [for database 0:0,0], D1.c3 as c3 [for database 0:0,0], D1.c4 as c4 [for database 0:0,0], D1.c5 as c5 [for database 0:0,0], D1.c6 as c6 [for database 0:0,0], D1.c7 as c7 [for database 0:0,0] Child Nodes (RqJoinSpec): <<10796>> [for database 0:0,0] RqJoinNode <<10797>> [] ( RqList <<10800>> [for database 0:0,9] 2 as c2 [for database 0:0,9], 3 as c3 [for database 0:0,9], 4 as c4 [for database 0:0,9], 5 as c5 [for database 0:0,9], 6 as c6 [for database 0:0,9], 7 as c7 [for database 0:0,9] Child Nodes (RqJoinSpec): <<10814>> [for database 0:0,9] RqJoinNode <<10815>> [] CacheTable T1520210166:837:3278:0012EF28 ) as D1 ]] [2018-03-07T09:38:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-34] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 43070005] [sessionid: 43070000] [username: weblogic] -------------------- Query Status: Successful Completion [[ ]] [2018-03-07T09:38:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-29] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 43070005] [sessionid: 43070000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[ ]] [2018-03-07T09:38:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-24] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 43070005] [sessionid: 43070000] [username: weblogic] -------------------- Rows returned to Client 2087 [[ ]] [2018-03-07T09:38:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-33] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 43070005] [sessionid: 43070000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 569, Response time 0, Compilation time 0 (seconds) [[ ]] [2018-03-07T09:44:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-34] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- Query Status: Successful Completion [[ ]] [2018-03-07T09:44:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-29] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[ ]] [2018-03-07T09:44:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-24] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- Rows returned to Client 2087 [[ ]] [2018-03-07T09:44:24.000+05:30] [OracleBIServerComponent] [TRACE:3] [USER-33] [] [ecid: 00ilJNBKCJfEwGWzLwYROA0000tg000000] [tid: 1568] [requestid: 430a0005] [sessionid: 430a0000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 485, Response time 0, Compilation time 0 (seconds) [[ ]]

SasDutta · Answer

Hi

I checked sawlog0.dat under

C:\OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1.

Though i am not sure whether this file will give exact presentation service error.

I see following messages.

[2018-03-07T09:36:20.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.sawserver] [ecid: ] [tid: ] Oracle BI Presentation Services (OBIPS) 11.1.1.7.0 (Build 130303.2025 32-bit) are starting up.[[

File:sawserver.cpp

Line:455

Location:

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:21.000+05:30] [OBIPS] [WARNING:16] [] [saw.mas.csf] [ecid: ] [tid: ] Database security store is not available, do not re-associate to this provider type.[[

File:csfwrapperimpl.cpp

Line:152

Location:

saw.mas.csf

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:21.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.sawserver] [ecid: ] [tid: ] Security query timeout sec is: 120[[

File:odbcuserpoploaderimpl.cpp

Line:1068

Location:

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:21.000+05:30] [OBIPS] [WARNING:16] [] [saw.threadpool.indexusersaccount.performance] [ecid: ] [tid: ] ThreadPool<IndexUsersAccount:1>.Thread count: running  0, maximum 1 (1), idle 0. Jobs pending: 1.  No available threads, creating new thread for job. Approaching configured maximum for total number of threads.[[

File:threadpool.cpp

Line:1260

Location:

saw.threadpool.indexusersaccount.performance

saw.threadpool.indexusersaccount

saw.catalog.autosearch.startindexingaccounts

saw.security.securityimpl.initialize

saw.catalog.local.loadCatalog

saw.subsystems.catalogbootstrapper.loadcatalog

saw.webextensionbase.init

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

threadpool: IndexUsersAccount

]]

[2018-03-07T09:36:22.000+05:30] [OBIPS] [WARNING:1] [] [saw.webextensionbase.init] [ecid: ] [tid: ] Initializing the auditing subsystem...[[

File:auditwrapperimpl.cpp

Line:457

Location:

saw.webextensionbase.init

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:22.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.webextensionbase.init] [ecid: ] [tid: ] Auditing initialization succeeded - auditing is enabled.[[

File:auditwrapperimpl.cpp

Line:572

Location:

saw.webextensionbase.init

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:22.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.sawserver] [ecid: ] [tid: ] Oracle BI Presentation Services have started successfully.[[

File:sawserver.cpp

Line:553

Location:

saw.sawserver

saw.sawserver.initializesawserver

saw.sawserver

ecid:

]]

[2018-03-07T09:36:26.000+05:30] [OBIPS] [WARNING:16] [] [saw.security.userpopulationcache.populateapproleshierarchy] [ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1] [tid: 1300] Populating app role hierarchy. this could take a while[[

File:userpopulationcache.cpp

Line:289

Location:

saw.security.userpopulationcache.populateapproleshierarchy

saw.RoleHierarchyCache.cache.cleanup

saw.taskScheduler.processJob

saw.threadpool.taskscheduler

saw.threads

ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1

ThreadID: 1300

task: Cache/RoleHierarchyCache

]]

[2018-03-07T09:36:26.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.security.userpopulationcache.populateapproleshierarchy] [ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1] [tid: 1300] Populating the application role hierarchy from backend.[[

File:userpopulationcache.cpp

Line:304

Location:

saw.security.userpopulationcache.populateapproleshierarchy

saw.RoleHierarchyCache.cache.cleanup

saw.taskScheduler.processJob

saw.threadpool.taskscheduler

saw.threads

ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1

ThreadID: 1300

task: Cache/RoleHierarchyCache

]]

[2018-03-07T09:36:26.000+05:30] [OBIPS] [NOTIFICATION:1] [] [saw.security.userpopulationcache.populateapproleshierarchy] [ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1] [tid: 1300] Finding all app roles took 110 ms.[[

File:userpopulationcache.cpp

Line:321

Location:

saw.security.userpopulationcache.populateapproleshierarchy

saw.RoleHierarchyCache.cache.cleanup

saw.taskScheduler.processJob

saw.threadpool.taskscheduler

saw.threads

ecid: 00ilM3r42pOFc5WFLzVK8A0000kG000000,0:1

ThreadID: 1300

task: Cache/RoleHierarchyCache

]]