OBIEE - How to define the BI server security to add automatically a filter when a column is added to an answer (row security level)

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - Introduction

“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?”

The idea is when you add the column Channel desc from the SH sample schema to an answer the query must be automatically filtered with a predicate on a other column.

Advertising

3 - Steps

  1. Go to Manage > Security Manager
  2. Open a group or a user
  3. Add this filter

You can read it as when you add the column “Channel Desc”, add the business model filter

SH.Channels."Channel Class" = 'Direct'

4 - The result

4.1 - With the column "Channel class" and "Amount Sold"

Sql Request

SELECT Channels."Channel Class" saw_0, "Sales Facts"."Amount Sold" saw_1 FROM SH ORDER BY saw_0

As you can see no filter is added to the database query.

Database Query

SELECT T161.CHANNEL_CLASS AS c1,
     SUM(T245.AMOUNT_SOLD) AS c2
FROM 
     SH.CHANNELS T161,
     SH.SALES T245
WHERE  ( T161.CHANNEL_ID = T245.CHANNEL_ID ) 
GROUP BY T161.CHANNEL_CLASS
ORDER BY c1
Advertising

4.2 - With the column "Channel desc" and "Amount Sold"

Sql Request

SELECT Channels."Channel Class" saw_0, Channels."Channel Desc" saw_1, "Sales Facts"."Amount Sold" saw_2 
FROM SH ORDER BY saw_0, saw_1

By adding the column Channels.“Channel Desc”, OBIEE add automatically the security filter.

Database Query

SELECT T161.CHANNEL_CLASS AS c1,
     T161.CHANNEL_DESC AS c2,
     SUM(T245.AMOUNT_SOLD) AS c3
FROM 
     SH.CHANNELS T161,
     SH.SALES T245
WHERE  ( T161.CHANNEL_ID = T245.CHANNEL_ID AND T161.CHANNEL_CLASS = 'Direct' ) 
GROUP BY T161.CHANNEL_DESC, T161.CHANNEL_CLASS
ORDER BY c1, c2

4.3 - With the column "Channel desc" and "Channel class"

Sql Request

SELECT Channels."Channel Class" saw_0, Channels."Channel Desc" saw_1 FROM SH ORDER BY saw_0, saw_1

Database Query

SELECT DISTINCT T161.CHANNEL_CLASS AS c1,
     T161.CHANNEL_DESC AS c2
FROM 
     SH.CHANNELS T161
WHERE  ( T161.CHANNEL_CLASS = 'Direct' ) 
ORDER BY c1, c2

5 - Others

This documentation was created in answer to an original idea of Venkat with this blog entry: Puzzlers – Puzzle 1

Advertising