columns with data not filtered based on user login in a report filtered based on user login

Question

Hello,

I am new to oracle BI and working on OBIEE 12.2.1.4.0. I have a requirement to create an analysis where I need to show three columns for a user. 1st column for Average of a measure for that user, 2nd column average of measure for the department that user belongs to and 3rd column average of measure for the entire organization. this is so that the user can see his/her avg vs. dept avg vs. organization avg. The problem I have is that I am filtering the report based on user login. I am able to show avg for the logged-in user. but, since the data is filtered for the logged-in user I am not able to get complete dept level and organization level avgs. I have worked on Cognos and it has something called query which I use to fulfill this kind of requirement. how can I do this in oracle BI??

All help is greatly appreciated,

Thanks and regards,

Syed.

[Deleted User] · Answer

OBI doesn't allow for a similar violation of the integrity of the underlying models.

You need to model things correctly in the metadata repository. The analysis layer should receive data correctly and use it - not change the logic and structure oft he data. The logical table source is in the repository and is a child object of the logical fact table (in you case. logical dimension tables also have logical table sources).

Trying to "solve" your requirement in the front-end layer is a fundamentally wrong approach.

User_428ZJ · Answer

Hello Christian,

Thank you for your response.

By query, I mean Cognos has a function where you can make logical tables and joins in the report developer itself based on the presentation tables from the repository (IBM calls it framework manager). I am still learning Oracle BI and I didn't come across a function like that.

I am implementing row-level security for my report using the session variable and putting data filter in rpd itself (on the identity>group>permission>data filter) and again using that session variable in my analysis filter section to show data for the logged user. I am not able to understand how to bring this unfiltered LTS into my analysis. can you please elaborate on this and help me understand how I can do this.

Thanks,

Syed.

[Deleted User] · Answer

4073688 wrote:I have worked on Cognos and it has something called query which I use to fulfill this kind of requirement. how can I do this in oracle BI??

Good thing that OBI uses "queries" to ;-)

Jokes aside: You need an unfiltered LTS in the logical fact table which is used to source that organization-level measure (or measureS if you use multiple measures).