6 Replies Latest reply: Dec 26, 2012 7:55 PM by 897863 RSS

    Data level security for 30000 profir centers

      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.
        • 1. Re: Data level security for 30000 profir centers
          ANy pointer/help psl.
          • 2. Re: Data level security for 30000 profir centers

            Just create the session variable which hold the "profit center name" for a particular user.
            Create a role in obiee say "Profit Center Group" and apply data filter for this role with the condition using the session variable(profit center name).
            Assign all the users to that particular role(Profit Center Group).

            • 3. Re: Data level security for 30000 profir centers
              Thanks MVSST , appreciate your response.

              But say a user of CFO level having access to all the Profit Center , would have 30,000 profit center in the IN CLAUSE as the session variable holds teh profit center name separated by semicolon/comma.

              As I know oracle supports only 1000 values in the IN clause. Pls. help.
              • 4. Re: Data level security for 30000 profir centers

                Yes, any dimension filters are applied only when you include that dimension in your analysis.

                As a workaround, you could create a filter as "Profit Centre" is not equal to 'Dummy Profit Centre' with "Protect Filter as ON" and add this filter to all of your analysis.

                So what it does is, even though you do not refer to profit centre dimension in your analysis, the filter in each analysis makes sure that the profit centre dimension is always mapped and the data restriction is applied.

                Hope this helps.

                Thank you,
                • 5. Re: Data level security for 30000 profir centers
                  For the CEO level we should not apply the filters.create a role in obiee with no data filters and assign all the users who need all the pofit centers, to that role.
                  • 6. Re: Data level security for 30000 profir centers
                    Hi Dhar

                    Thanks.Can we do this in the RPD itself as the requirement is to build te RPD and give to business user . Only they can create the analysis. And the data should be visible to them as per the Profit Center security access.

                    Can I just create a single Role OR use eixting Role -BI Consumer and assign this security filter in required dimensions - Profit Center & The Fact like. Profit center is not equal to "Dummy Profit Center".