OBIEE Displays Partial Result, When Querying SQL Server (Doc ID 2090336.1)

Last updated on FEBRUARY 18, 2016

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.9.0 and later
Information in this document applies to any platform.

Symptoms

You configure ODBC connection for SQL Server data source and then create connection in your RPD, you are able to view data in the RPD successfully.

but when you create analysis report, you only view 4 rows populated, and when you execute physical query in the database directly, there are 32 rows.

Below is the query sent to backend DB and log return in the saw.log

 

[2015-12-11T12:21:57.935-05:00] [OracleBIServerComponent] [TRACE:5] [] [] [ecid: ab3a4698558da86b:4d92fd39:14f5f7c7b7a:-8000-00000000008aa29c,0:1:17:5] [tid: d091700] [messageid: USER-18] [requestid: 7a60000c] [sessionid: 7a600000] [username: 5374226419] -------------------- Sending query to database named Help Desk Survey (id: <<482251>>), connection pool named Connection Pool, logical request hash dcebc564, physical request hash 5af27eb0: [[
select 0 as c1,
D1.c4 as c2,
D1.c3 as c3,
D1.c2 as c4,
D1.c1 as c5
from
(select count(T91741.[idsurvey]) as c1,
T91741.[UI_Type] as c2,
T91741.[Type] as c3,
T91741.[Satisfaction Level] as c4
from
CustomerSurveySelfService.dbo.vw_CSSS_HDCrystalRpts T91741 /* Help_Desk */
group by T91741.[UI_Type], T91741.[Type], T91741.[Satisfaction Level]
) D1
order by c3, c4

]]

--- Execution Node for logical request hash dcebc564 : <<482251>>, Close Row Count = 32, Row Width = 131572 bytes
--- Query Result Cache: [59124] The query for user '5374226419' was inserted into the query result cache. The filename is '/apps/opt/fmw/webcenter/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache/NQS__735945_44527_69.TBL'.
--- Execution Node for logical request hash dcebc564 : <<482284>> GroupByNoSort, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482285>> Projection, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482285>> Sort, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482305>> DbGateway Exchange: Help Desk Survey.Connection Pool, Close Row Count = 4, Row Width = 131572 bytes
--- Query Status: Successful Completion
--- Rows 32, bytes 4210304 retrieved from database query id: <<482251>>
--- Physical query response time 50.137 (seconds), id <<482251>>
--- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 50.137, DB-connect time 0.000 (seconds)
--- Rows returned to Client 4
--- Logical Query Summary Stats: Elapsed time 50.374, Total time in BI Server 50.372, Response time 50.373, Compilation time 0.018 (seconds), Logical hash dcebc564
--- Execution Node for logical request hash dcebc564 : <<482284>> GroupByNoSort, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482285>> Projection, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482285>> Sort, Close Row Count = 4, Row Width = 131572 bytes
--- Execution Node for logical request hash dcebc564 : <<482305>> DbGateway Exchange: Help Desk Survey.Connection Pool, Close Row Count = 4, Row Width = 131572 bytes

Changes

 Configure SQL Server connection in the RPD using ODBC.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms