nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

ODV not retrieving all records from OBIEE analysis

Engrid Braholli

Summary

ODV not retrieving all records from OBIEE analysis

Content

Dear all,

I am trying to achieve an integration of ODV Desktop with Oracle BI EE. We have latest version of ODV and OBIEE version 11.1.1.9.0.

I am able to create a dataset from a pre-build BI Analysis and I am able to retrieve records in ODV. The issue is that for some reason not all records of the analysis are retrieved into ODV.

When I access the analysis from OBIEE I am able to downloan all the records of the report into a CSV file. But ODV is not retrieving all the records and I dont know why.

I have increase all the parameters inside the instanceconfig file of OBIEE into big numbers.

Can you please suggest for any reasons why is this think happening when connecting ODV with OBIEE?

I tested the same query from ODS with direct database access and in that case it is retrieving all the records.

Will appreciate your support.

regards.

Find more posts tagged with

Packaging and Licensing

Accepted answers

All comments

Gianni Ceresa

Hi,

If you look in the log where you find the query sent to the source (OBIEE in your case), do you see a "LIMIT" clause inside the query?

Engrid Braholli

Hi,

I guess the log file to be checked is ...\OBIS\obis1-query.log.

I have checked the log file and the query that is being generated but there is no "LIMIT" clause on the query.

thank you.

Engrid Braholli

Hi all,

Can you please provide any feedback on the above, because I am still facing the same situation.

thank you.

Joel Acha11111

and when you run the query that is in the obis1-query.log file directly on the database (TOAD,SQL Developer), do you get the correct results?

Engrid Braholli

Hi,

Yes I am able to execute the query direclty on the database (with TOAD) and it is showing all the records.

Also the report that I have build in BI is displaying all the records and I am able to download all the records in CSV file from OBIEE web interface.

Mark_Daynes

Hi,

Great pointers from the guys above - What I was wondering is about volume - is this a huge dataset? - can you tell us how many records are returned in BIEE and how many in DV.

Also, have you tried taking the data straight from the subject area - effectively creating a variant of the analysis in DV.

Mark

1.0.0.17

Engrid Braholli

Hi,

In response to Mark_Daynes questions.

I tried various ways to build the report and retrieve the data in DV ie. by creating a version of the report directly from subject area in DV, by creating the dataset based on an analysis already build in obiee. the nr of records retrieved is always the same.

In terms of number of records, the simple report I am currently trying to work with has:

-- in obiee it has 1,186,000 (all records are exported in CSV file from obiee)

-- while in DV it looks like it downloads only 1,100,001

I tried to download the file in csv from DV by building a project but now I am getting the following error:

Exceeded configured maximum number of allowed input records. ResultRowLimit(table) OR CubeMaxRecords(Pivot/Chart) needs to be reset in the config file. Please check with your administrator.

I found some config files on the path "C:\Users\<username>\AppData\Local\Temp\DVDesktop\config\fmwconfig" and incresed the limits there but it looks like they are getting replaced everytime I open DV desktop, so the situation is the same when downloading the file in csv. Is there any other path where I can file DV Desktop config file?

Engrid Braholli

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

]]

Michael Verzijl

Do you also have the corresponding query executed from the BI server?

Engrid Braholli

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" ?>

</Column>

</Column>

</Column>

</Column>

</Column>

</Projection>

<From>

</TableRef>

</From>

</Select>

</Query>

*I have just masked the column names shown above.