Dynamic Data security in OBIEE

Here is an interesting scenario that I came across recently. The client has 2 hierarchies based on cost centers, Management and P&L. The business push behind this was that lot of users had dual roles in terms of how they look at expenses. Both the hierarchies had cost center at the lowest level and the same number of levels in the hierarchy. The differences were 1) users would have access different set of cost centers in both hierarchies (not necessarily mutually exclusive) 2) roll ups for the cost centers would be different in both hierarchies. The business had a couple of requirements around this.

1) The users should be able to pick the hierarchy that they would like to view the report by.

2) When the user flips between the hierarchies the security applied should change accordingly, ie if they are viewing the report by the P&L hierarchy then the P&L security should be applied and if they pick management then Management hierarchy security should be applied.

Here is a very simplified version of how we implemented the solution.

The security profile table

Hierarchy User_id Cost_center
P&L USER1 1
P&L USER1 2
P&L USER1 3
P&L USER1 4
MGMT USER1 3
MGMT USER1 4
MGMT USER1 5
MGMT USER1 6
MGMT USER1 7

User1 has access to cost centers 1-4 in the P&L hierarchy and 3-7 in the Management Hierarchy.

Here are the 2 hierarchy tables for this example.

Hierarchy Cost_center Level2 Level1
P&L 1 P&L LVL2 1 P&L LVL1 1
P&L 2 P&L LVL2 1 P&L LVL1 1
P&L 3 P&L LVL2 1 P&L LVL1 1
P&L 4 P&L LVL2 1 P&L LVL1 1
P&L 5 P&L LVL2 2 P&L LVL1 1
P&L 6 P&L LVL2 2 P&L LVL1 1
P&L 7 P&L LVL2 2 P&L LVL1 1
Hierarchy Cost_center Level2 Level1
MGMT 1 MGMT LVL2 1 MGMT LVL1 1
MGMT 2 MGMT LVL2 1 MGMT LVL1 1
MGMT 3 MGMT LVL2 1 MGMT LVL1 1
MGMT 4 MGMT LVL2 2 MGMT LVL1 1
MGMT 5 MGMT LVL2 2 MGMT LVL1 1
MGMT 6 MGMT LVL2 2 MGMT LVL1 1
MGMT 7 MGMT LVL2 2 MGMT LVL1 1

I have an expenses fact table where the cost center is a key.

clip_image002

Now lets see how this is modeled in the RPD.

Physical Layer Joins

I have aliased the security profile table as Sec Profile PL and Sec Profile Mgmt

clip_image004

There is a similar join between the Sec Profile Mgmt table with the Management Hierarchy table.

Here is how the BMM layer is done.

The cost center table will have 2 logical sources and each of these logical sources would have an inner join to the appropriate sec profile alias table.

clip_image006clip_image008

All the columns are mapped to both the logical sources.

Now we need to define appropriate fragmentation content for these 2 logical sources. Here is the Mgmt source

clip_image010

Add the user_id column to the logical table and map it to the appropriate logical sources.

Now all that’s left is to go to the relevant group in the RPD and add the security filter.

clip_image012

Create reports with the hierarchy as one of the columns and set filter to a presentation variable

clip_image014

Also create a prompt and set a presentation variable in the prompt.

clip_image016

Throw the 2 in a dashboard and lets see how the reports work for USER1

clip_image018clip_image020

Its very important to have the filter on the hierarchy column in every query otherwise you would end up overstating the numbers.

Advertisements

2 Responses

  1. Is there any way to default the hierarchy to MGMT via the RPD?
    As in, when a report is built like the one above in Answers without the Hierarchy filtered by a presentation variable, it would only show MGMT hierarchy. And then when filtering on the Hierarchy field for P&L, it would override the MGMT default in the RPD.
    Basically, we have many Answers users that want to build a report and 90% of the time it is the MGMT hierarchy (so we wouldn’t want to have to filter) but that remaining 10% is P&L which they understand they would have to filter at that point.

    Hope that makes sense.
    Thanks.

    • I guess if you take of the fragmentation content (not the content filter) for the management hierarchy table ( keep the fragmentation for the P&L) it would always default to the management hierarchy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: