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

obiee report is not fetching data

3274064

I am new to OBIEE. I am creating my first report. When I am dragging column from subject area and going to Results then I am not able to see any data.

My Subject Area is hr1, table name is countries and I am able to see 25 rows in physical layer as well as in database. Please Advice.

Below is the SQL issued.

SELECT

0 s_0,

"hr1"."COUNTRIES"."COUNTRY_NAME" s_1

FROM "hr1"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

Find more posts tagged with

Accepted answers

All comments

3182650

Does your Oracle Database have any table policies (VPD) applied on them? Something that can restrict data as per users.

That might be one reason why no data is being shown in OBIEE as the user which send the SQL is different than you own user which sends the SQL from SQLPLUS.

3274064

I am not aware how to check VPD's. But I am able to view table data present in physical layer of Oracle BI Administration Tool.

Gianni Ceresa

If you look at your logs how does OBIEE translate that simple logical SQL into a physical SQL?

Because if it does you clearly see the physical query and you can find out what the server is asking to your DB, if it doesn't you generally have some messages around telling you what stopped the query.

You can also have filters in the logical table in the business layers, or row-level security in place not allowing you to see the data etc.

Is it your own RPD you built yourself or somebody else did?

3274064

I have built the repository.

I again built new rpd (Training) and gave SYSTEM access while creating connection pool, Just to check that it is not a access issue. I am connecting to Oracle database.

Again when I am trying to run the report, I am not able to see data in oracle BI.

Below is the log from session Monitor:

[2016-07-14T06:51:03.977+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:3] [tid: 1710] [messageid: USER-0] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] ############################################## [[

-------------------- SQL Request, logical request hash:

8a040e16

SET VARIABLE QUERY_SRC_CD='Report';SELECT

0 s_0,

"Training"."COUNTRIES"."COUNTRY_NAME" s_1

FROM "Training"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2016-07-14T06:51:04.06+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:3] [tid: 1710] [messageid: USER-23] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- General Query Info: [[

Repository: Star, Subject Area: Training, Presentation: Training

]]

[2016-07-14T06:51:04.30+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:3] [tid: 1710] [messageid: USER-2] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Logical Request (before navigation): [[

RqList distinct

0 as c1 GB,

COUNTRIES.COUNTRY_NAME as c2 GB

OrderBy: c1 asc, c2 asc NULLS LAST

]]

[2016-07-14T06:51:04.57+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:5] [tid: 1710] [messageid: USER-21] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Cache Hit on query: [[

Matching Query: SET VARIABLE QUERY_SRC_CD='Report';SELECT

0 s_0,

"Training"."COUNTRIES"."COUNTRY_NAME" s_1

FROM "Training"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

Created by: weblogic

]]

[2016-07-14T06:51:04.89+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:5] [tid: 1710] [messageid: USER-50] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- The logical query hits the plan cache [[

plan

RqList <<10519>> [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]

Child Nodes (RqJoinSpec): <<10525>> [for database 0:0,0]

RqJoinNode <<10526>> []

(

RqList <<10529>> [for database 0:0,9]

2 as c2 [for database 0:0,9]

Child Nodes (RqJoinSpec): <<10533>> [for database 0:0,9]

RqJoinNode <<10534>> []

CacheTable T1468457872:893:16880:000000000012EB50

) as D1

]]

[2016-07-14T06:51:04.119+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:1:5] [tid: 1710] [messageid: USER-16] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Execution plan: [[

RqList <<10519>> [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]

Child Nodes (RqJoinSpec): <<10525>> [for database 0:0,0]

RqJoinNode <<10526>> []

(

RqList <<10529>> [for database 0:0,9]

2 as c2 [for database 0:0,9]

Child Nodes (RqJoinSpec): <<10533>> [for database 0:0,9]

RqJoinNode <<10534>> []

CacheTable T1468457872:893:16880:000000000012EB50

) as D1

]]

[2016-07-14T06:51:04.140+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:2] [tid: 1710] [messageid: USER-34] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Query Status: Successful Completion

[2016-07-14T06:51:04.163+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:2] [tid: 1710] [messageid: USER-29] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0.000, DB-connect time 0.000 (seconds)

[2016-07-14T06:51:04.186+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:2] [tid: 1710] [messageid: USER-24] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Rows returned to Client 25

[2016-07-14T06:51:04.221+05:30] [OracleBIServerComponent] [TRACE:4] [] [] [ecid: b9459288d417b965:-352e0a90:155e6e400ae:-8000-00000000000015fd,0:1:2] [tid: 1710] [messageid: USER-33] [requestid: 178001b] [sessionid: 1780000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 0.164, Total time in BI Server 0.161, Response time 0.164, Compilation time 0.159 (seconds), Logical hash 8a040e16

Robert Angel

Check that the logical table source content is set to give the correct granularity between fact and dimensions, on both the fact and dimension side.

Check all your joins are valid.

Check that you have an aggregation rule on your fact measures.

The SQL you post seems to indicate that it is only pulling from one table for your analysis, is that really the case?

Robert Angel

One more thought is check the properties on the column to make sure that the type and size are sufficient for the column

Gianni Ceresa

Your query is not being sent to the DB, it actually stops in the cache as there is a match.

But if for any reason your cache contains bad or wrong data (maybe because of when you started building things etc.) you don't get any data out.

And because of the cache you also don't see the physical SQL sent to the DB.

So as you are new and probably trying to practice I would really suggest you turn cache OFF, at least you are a bit closer to your real data and avoid issues...