Build a OTBI analysis using 2 subject areas but results is giving blank table.

Sourav Bose

I am trying to build a OTBI analysis in oracle fusion application using two subject areas ' Risk Management Cloud - Advanced Financial Controls Real Time ' and ' User System Usage ' but after selecting the required columns from the both the subject area the table showing in the results tab is coming blank i.e. without any data, just the columns headers are present no data is showing in the tables.

my objective here is to get the mail id of the investigators (person who has been assigned some incidents) in the analysis, from the 'Risk Management Cloud - Advanced Financial Controls Real Time' subject area i am getting the display name in the investigator column but my requirement is to get the mail id of the investigators, I have taken the mail id column from the 'User System Usage' subject area.

RanaAshutosh-Oracle

Thank you for visiting OBI Community to seek support. 

Unfortunately, this Community does not provide support for Fusion Applications OTBI. 

However, you can visit our sister community, https://community.oracle.com/products/oracleanalytics/categories/otbi , to receive support from our Fusion OTBI experts within the OTBI Forums.

Gianni Ceresa

@RanaAshutosh-Oracle , the post is already in the category you mention...

Nathan CCC

Hi Gianni Ceresa,

Yes you are on the correct forum for OTBI in Oracle Fusion Cloud Applications.

Assume you are also getting no data found when you use the out of the box pre-defined dashboards that use the subject areas in Risk Management. Assume you are getting no data found no matter what columns you select from the risk management subject area in your analysis. Even if you select only one column. I suspect your issue is that you need to setup data access for your user.

So to use a subject you need to be assigned a role with the duty role as per the subject area user guide that allows you to query that subject area without getting a error thrown from the database because you do not have permission to query this subject area in the metadata repository database (RPD) in OTBI. But that does not mean you will see any data. If you look in your session log for your query from page manage sessions /analytics/saw.dll?sessions you may see that the system has appended where clauses at runtime to your query in your session to restrict to only the rows for which you are setup for data access for your user.

With risk management you can not just go to setup and maintenance page "Manage Data Access for Users" to grant access to for example business units based on data security policies in your job roles like you can for other subject areas in ERP, of just setup a data role like you can for other subject areas in HCM BUT for Risk Management subject areas you need to go into the work areas in Risk Management itself to set up data access on the various objects in Risk Management.

Nathan CCC

Hi, Having just re-read your question it maybe that you do in fact get data when you select only columns from the risk management subject area. But it returns no data only after you add some columns from the second subject area from system usage? Queries where you just add columns from multiple subject areas will only work if the 2 subject areas are compatible. Look in your session log to see how oracle decided to join the data. You may need to rewrite your logical query to explicit add the join yourself between the 2 logical queries from each subject area so you can control which attribute from each subject area to use to join on.

For example

select all 0 as s_0
, userrole.user_username as user_username 
, worker.worker_username as worker_username 
from (
	select all "User"."User Name" as user_username
	from "Security - Roles and Privileges Real Time" 
) userrole 
left outer join 
(
	select all "Worker"."User Name" as worker_username
	from "Workforce Management - Worker Assignment Real Time"
) worker 
on 
(
worker.worker_username = userrole.user_username 
)