Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
ODV not retrieving all records from OBIEE analysis
Answers
-
Hi Michael,
On OBIEE side the ResultRowLimit has the following value:
<ODBC>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ResultRowLimit>10000000</ResultRowLimit>
</ODBC>
thanks
0 -
Are you able to share the SQL generated within DVD?
0 -
HI,
I see the below query being generated in the DV logs for BI:
[2018-04-05T12:14:25.618+02:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 2b20] [messageid: USER-16] [requestid: fffe0005] [sessionid: fffe0000] [username: weblogic] -------------------- Execution plan: [[
RqPopulate <<1085>> [for database 0:0,0] append file
RqList <<1110>> [for database 3023:43812:'weblogic'.'mybi_uat',87]
T1000001.COLUMN1 as c1 [for database 3023:43812:'weblogic'.'uat',87],
T1000001.COLUMN2 as c2 [for database 3023:43812:'weblogic'.'uat',87],
T1000001.COLUMN3 as c3 [for database 3023:43812:'weblogic'.'uat',87],
T1000001.COLUMN4 as c4 [for database 3023:43812:'weblogic'.'uat',87],
T1000001.COLUMN4 as c5 [for database 3023:43812:'weblogic'.'uat',87]
Child Nodes (RqJoinSpec): <<1126>> [for database 3023:43812:'weblogic'.'uat',87]
RqJoinNode <<1125>> []
[/shared/ODV Reporting/<analysis>] as T1000001
]]
[2018-04-05T12:14:25.619+02:00] [OBIS] [TRACE:2] [] [] [ecid: ] [sik: ssi] [tid: 2b20] [messageid: USER-18] [requestid: fffe0005] [sessionid: fffe0000] [username: weblogic] -------------------- Sending query to database named 'weblogic'.'uat' (id: <<1110>>), connection pool named 'weblogic'.'uat', logical request hash a15a920a, physical request hash 9ee09528: [[
<?xml version="1.0" encoding="UTF-8" ?>
<Query xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" queryid="17460-23157" locale="en">
<Select>
<Projection>
<Column columnAlias="c1">
<Expr tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN1>]]></Name></Expr>
</Column>
<Column columnAlias="c2">
<Expr tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN2>]]></Name></Expr>
</Column>
<Column columnAlias="c3">
<Expr tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN3>]]></Name></Expr>
</Column>
<Column columnAlias="c4">
<Expr tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN3>]]></Name></Expr>
</Column>
<Column columnAlias="c5">
<Expr tableNo="1000001" xsi:type="TargetColumn"><Name><![CDATA[<COLUMN4>]]></Name></Expr>
</Column>
</Projection>
<From>
<TableRef joinType="Inner"><LeftTable idQuoteChar="" tableNo="1000001"><Catalog><![CDATA[]]></Catalog><Schema><![CDATA[]]></Schema><Name><![CDATA[/shared/ODV Reporting/<analysis_name>]]></Name></LeftTable>
</TableRef>
</From>
</Select>
</Query>
*I have just masked the column names shown above.
0 -
Below is the query that is generate on BI server side:
[2018-04-05T12:00:28.146+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:3] [tid: 11f8] [messageid: USER-0] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] ############################################## [[
-------------------- SQL Request, logical request hash:
3af145af
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/ODV Reporting/ANALYSIS';SELECT
0 s_0,
"LD"."F07"."COLUMN1" s_1,
"LD"."F07"."COLUMN2" s_2,
"LD"."F07"."COLUMN3" s_3,
"LD"."F07"."COLUMN4" s_4,
"LD"."F07"."COLUMN5" s_5
FROM "LD"
WHERE
("F07"."COLUMN1" IN (400, 401, 402))
ORDER BY 1, 2 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 3 ASC NULLS LAST, 6 ASC NULLS LAST
FETCH FIRST 100000001 ROWS ONLY
]]
[2018-04-05T12:00:28.177+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:3] [tid: 11f8] [messageid: USER-23] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] -------------------- General Query Info: [[
Repository: Star, Subject Area: LD, Presentation: LD
]]
[2018-04-05T12:00:28.177+02:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: e80f8094b3c2d8a3:-18fc1428:1626207411d:-8000-000000000003601a,0:1:17:5] [tid: 11f8] [messageid: USER-18] [requestid: 4a3b0006] [sessionid: 4a3b0000] [username: biadmin] -------------------- Sending query to database named DW (id: <<966312>>), connection pool named Conn_DW, logical request hash 3af145af, physical request hash c907cfb9: [[
select 0 as c1,
D1.c5 as c2,
D1.c4 as c3,
D1.c3 as c4,
D1.c2 as c5,
D1.c1 as c6
from
(select T113269.COLUMN1 as c1,
T113269.COLUMN2 as c2,
T113269.COLUMN3 as c3,
T113269.COLUMN4 as c4,
T113269.COLUMN5 as c5
from
LD.TABLE1 T113269 /* B02*/
where ( T113269.COLUMN2 in (400, 401, 402) )
) D1
]]
0 -
Yes, even when I was testing in the beginning I was getting less rows (like 65,000 rows). I increased some of the other OBIEE instanceconfig parameters to higher values and now its getting around 1M rows, but still not all of them.
As mentioned in one of the above posts I found also an instanceconfig.xml file that ODV has on its folders, (C:\Users\<username>\AppData\Local\Temp\DVDesktop\logs\OBIS), which has similar parameters to the OBIEE config file. I updated the row numbers also on this file but this one looks like is getting replaced everytime ODV is restared.
thanks
0 -
I have tested on my local system and seem to only get 55.000 rows returned. Need to investigate why this is happening.
0 -
Hi all,
Does anyone have any update on this issue? Has anyone else been facing the same thing with ODV?
thank you.
0
