The other day while working on data level security within OBIEE (BI Server), I found something very interesting I would like to share. While testing my data level security for various combinations of groups, I noticed that for one subject area I was not having my security filters applied.
That should not and can not be, I said to myself. I looked into and traced the presentation column I was using to the related logical tables. It just came up with the logical table which I applied the filter on. So why did it not add the filters? I must have tried different things, looked here and there with no avail. So I called it a night and would come back in the morning and try something different.
Behold the next day, with a fresh mind I found the culprit! It was a logical table (“Fact – Fins – AP Activity Compound”) which has no LTS (As seen below)!
Instead the entire logical table and its logical columns are derived off of other logical table columns and thus inherit their definitions. [To find out I selected all the logical columns and ran display related->logical columns. Below is what came back]
So that is why I was seeing my physical table (alias) which I applied the data level filter on within the physical queries in the NQSQuery.log. But, and here is the kicker, since it was not coming from the logical table that I added the filter too, it did not apply the filter.
Solution: I looked at all the logical tables which were used by that logical table which make up its logical columns to gather the conformed dimensions I want to filter on. Then I added the filter on this logical table. Viola, it is now added the filters to the query as expected.
Lesson Learned: If you apply data level security to a logical table, then it will be for that table and nothing that derives off of it.