Drilling with security filter

[MSTRJD]

I have a drill hierarchy which is as followsirector->Manager->Supervisor->Service Rep

Is it possible using security filter or any other options where a user can only view report details pertaining to him/her only?

for example,if there are 2 managers, manager1 and manager2 and if manager1 executes the report he should only see the his record set and manager2 details should be hidden.Manager1 can only drill down to supervisor,service rep below him/her and not manager2.

Do any one have an idea how this can be implemented?

Thanks.

 

[datamart]

There should be a normalised dimension table (NODE) with all the 4 keys (D, M, S, SR). Suppose the primary key for this table is node_id.
The fact table should have the node_id as a part of its primary key.

Create 4 attributes, one each for D, M, S, SR with the source table as the individual dimension tables. Create one attr for the node, with the NODE and fact table as Source tables.

Create a se filter with attribute value pair, say M->M1. Assign this to a user.
When the user logs in, the following sql will be generated

SELECT M.M_NAME, F.FACT_COL
FROM FACT F,
NODE N,
MANAGER M
WHERE F.NODE_ID = N.NODE_ID AND
N.MGR_ID = M.MGR_ID AND
M.MGR_ID = "M1"

Now, if the same user wants to see data at a Dept level, but restricted to filter M1, the sql will be

SELECT D.D_NAME, F.FACT_COL
FROM FACT F,
NODE N,
MANAGER M,
DEPT D
WHERE F.NODE_ID = N.NODE_ID AND
N.MGR_ID = M.MGR_ID AND
N.DEPT_ID = D.DEPT_ID AND
M.MGR_ID = "M1"

Whatever way, the sql now generates according to user security.

 

[MSTRJD]

Thanks for your response.

I have done almost similar to what you have explained.In my case the MGR_ID will be same as the manager login ID.So i have inserted the MGR_ID as a secutity filter for a given manager user profile.

But in this case security filter is applied to all the reports a manager executes.If there is time when this condition "M.MGR_ID = M1" should not be applied for some reports,then I think i have to create one more login for that manager with out that security filter!

Is there any around to over come this?

Once again Thanks.

 

[mizh]

You might want to read technotes TN5200-7X0-0086 and TN5700-7XX-0997 to see if either one addresses what you're trying to do. A combination of these two usually solves most of the special case issues I've encountered.

Otherwise, you will need to do some hacking with the Web SDK, which opens up its own can of worms.

 

[MSTRJD]

Thanks for the technotes.Will try to work on that.