OTBI Analysis is not showing any data because of predefined Session Variable FLAG_Y_LOOKUP_MEANING

Question

OTBI Analysis is not showing any data because of predefined Session Variable FLAG_Y_LOOKUP_MEANING is defined as a filter in the analysis the reports are seeded repots.

How can I solve this issue?

I have removed the session variables then when I run the analysis the data is showing. I searched through the Oracle documents but didn't find anything specific to this Session Variable "FLAG_Y_LOOKUP_MEANING".

Nathan CCC · Answer

Hi , Try using the same method for both queries. Obviously you can build up your custom analysis using the user interface to select columns and add filters. I actually did these 2 queries as new analysis after selecting the subject area then looked at the sql in the advanced tab. Query 2 error is a syntax cut paste error "all". This is a know bug in the generate an analysis algorithm. Make is select not select all and it can handle it.

Here is an alternative better safer method than creating a data model in OTBI or "create an analysis from simple logical SQL" in OTBI. If your bi administrator has granted using page manage privileges /analytics/saw.dll?PrivilegeAdmin to your user or a role your uer has or inherit privileges Issue SQL Directly and Manage Sessions then you can go to page issue sql /analytics/saw.dll?issuerawsql. The benefit of this method is you can use view log to quickly debug data access issues etc. and you can comment in and out lines with a hash "#" to quickly find issues in your queries.

Protyush Ghoshal · Answer

Hi Nathan,

The problem is that I am running some seeded Procurement OTBI analysis, we have created an inquiry only role to access the OTBI reports/analysis in procurement, but because of the session variable defined the user cannot see the data. The required data access set is given to the user along with that we included the OBI privileges and Transaction Analysis Duty to the role.

Note: I have created analysis with the same subject areas with this user and the data is visible there.

I used your query.

When I run the 1st query in BIP the result is matching with your output.

When I run the 2nd query in OTBI the results are as follows:

Query:

Result:

Nathan CCC · Answer

Hi Protyush,

What is the problem to be solved.

When you run the analysis using Open does it return no data ?

In your session does this variable have no value?

select all 0 s_0

, VALUEOF(NQ_SESSION.FLAG_Y_LOOKUP_MEANING) as s

from "Procurement - Pending Change Orders Real Time"

order by 1 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 7 ROWS ONLY

If you do this simple test case does it work?

select all 0 s_0

, "- Purchase Order Pending Change Order Schedule Detail"."Schedule Qty changed Flag" as s

from "Procurement - Pending Change Orders Real Time"

where

("- Purchase Order Pending Change Order Schedule Detail"."Schedule Qty changed Flag"

=  valueof(NQ_SESSION."FLAG_Y_LOOKUP_MEANING"))

order by 1 asc nulls last, 2 asc nulls last

fetch first 7 rows only