Parent Child Hierarchy in OBIEE returns no results for any filters applied which is not at top level

Question

Hello Experts,

We have impemented parent child hierarchy for the organization reporting dimension in our subject area. The hierarchy looks great, data and everything is perfect. The hierarchy however returns no result as soon as we apply a filter on any column. For example,

The hierarchy data looks like this, Andrew(male) has four reportees , of which 2 are male and 2 are female

Andrew

>>Peter

>>Kathy

>>Lynda

>>John

Now, If I apply a filter on Gender ='Female', No Results are returned . This is because the top level Andrew is of Male Gender. The SQL clearly shows that the query generated by OBIEE always takes ANCESTOR_KEY =NULL and it fails to give data for the lower levels.

I hope I put my question in an understandable manner. Please let me know, if any clarifications required.

Jerry Casey · Answer

You're talking about row-level security. Search on that and you'll find a lot of answers that are better than I can provide.
Here's one from Gianni:

https://community.oracle.com/message/12991195#12991195

User_DTFOB · Answer

Hello Jerry,

Thank you for your reply. Selection steps allows us to see fitered view of the data. Which is good.

The requirement we have is to apply certain filters on this dimension to apply data level security.

I would like to make it work like as below.

When person A with 4 reportees logs in, he should see only his data and for his reportees.

When person B with 3 reportees logs in, he should see his data and not person A data.

This does not look possible with selection steps. Kindly guide.

Jerry Casey · Answer

Hi,

You can filter on hierarchical columns using selection steps.