This discussion is archived
6 Replies Latest reply: Dec 26, 2012 5:55 PM by 897863 RSS

Data level security for 30000 profir centers

897863 Newbie
Currently Being Moderated
Hello Gurus

I have a requirement to implement data level security for 30000 profit center . Now I can think of creating the groups and applying security filters ( both on Dimesion & Fact) on the top of that.

But I cannot do so as I will have to create some 30,000 groups/roles which is not possible. because there are some users who have access to only one or two profit center and it forms a heirarchy.

As a workaround what I did is created a user-profit center table and joined it with the profit center table which is actually a snowflaked with two more dimensions - gl_account & gl_segment.

In the BMM layer , in the Content section of teh profit center dimension , I applied a where filter like below :

"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment11"."SEGMENT_LOV_ID" in (1000163) and "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment11"."SEGMENT_LOV_NAME"='Profit_Centre' AND ( "Oracle Data Warehouse"."Catalog"."dbo"."PF_USER_MAPPING"."USER" = VALUEOF(NQ_SESSION.USER) OR 'UNMATCHED'=VALUEOF(NQ_SESSION.USER) )

All is well if I create a report having Profit center as one of the dimension/component in the analysis (answers) .

But when I don't take Profit center the roll up is happening with all the Profit center . Reason being I have not applied "security filter " in the fact table and I cannot do so because USER tabel is not directly joined with the fact table.

Is there any workaround for this.

Pls. advise.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points