8 Replies Latest reply on Jul 27, 2018 3:40 PM by Robert Angel

    Need to understand how data filter works -1

    3521884

      Hello Team,

       

      I have asked in last thread about a data filter how it works and thank you for response.

      (Need to understand about the Data filters )

       

      Current situation:-

      In my rpd there are two fact table F1-Shipment and F2-ClaimsLogistics.

      There are no confirm dimension to join these fact table.

      There is a dimension table D1-ClaimDimension Joined with F2-ClaimLogistics fact table.

      In this D1-ClaimDimension we have two LTS and in one LTS we have have this Limit Filter =0 has been set and other has Limit Fiter = Blank < >.

           (Both LTS are showing same physical table apart some additional custom column mapping and formula) .

       

      In rpd we have a data visibility role and set as mentioned below with Limit Filter =0.

       

      I have two test user:-

      Testuser1 - GlobalLogistcs full author role

                          OBI_Data_Visi_Logistic_role

       

      Testcase1

      When user1 create a report by using column F1-Shipment and F2-ClaimsLogistics and D1-ClaimDimension then user can't see any data from F1-shipment, where as value from F2-ClaimLogistics are coming up.

       

      Testcase2

      When user1 create a report from either of fact table (F1-Shipment OR F2-ClaimsLogistics) including any dimension table (say D1-ClaimDimension) the values comes from both fact (also in case of Union report by taking single fact table with dimesion table).

       

      TestUser2 - GlobalLogistcs full author role

      When user2 create a report by using column F1-Shipment and F2-ClaimsLogistics and D1-ClaimDimension then user can see any data from F1-shipment and F2-ClaimLogistics.

       

      I tried all combination but still struggling what is happening with Testuser1 having Visibility roles can't see the data from both fact table, where user2 having only full author role (without visibility role) can see the data from both fact table.

       

      Apologies expert for my long query.

       

      I really appreciate your suggestion.

      Kindly let me know if you need some more information

       

      Regards,

      Abhishek

        • 1. Re: Need to understand how data filter works -1
          Robert Angel

          Hi,

           

           

          as a starter, set your diagnostics so you can view the physical sql in the logs.

           

          Run the analyses with your test users.

           

          Compare the two sets of physical SQL that are generated.

          1 person found this helpful
          • 2. Re: Need to understand how data filter works -1
            3521884

            Hello Robert,

             

            Thank you for your response.

            I tried to run the test report with physical query with both the test user.

             

            Query with user without Data visibility filter

            There are two query which was sent from OBI server.

             

            select sum(T179775.UNIT_WEIGHT_KG / 1000) as c1,

                 T169099.YEAR_ID as c2

            from

                 COMMON_SL.DWD_TIME T169099,

                 COMMON_SL.DIM_SHIPMENT_LOCATIO T186583,

                 COMMON_SL.FCT_SHIPMENT_UNIT T179775

            where  ( T169099.DAY_ID = T179775.DEPARTURE_DAY and T169099.YEAR_ID = '2018' and T179775.LOCATION_FROM_ID = T186583.LOCATION_ID and substr(T186583.LOCATION_ID , 1, 4) = 'K1' )

            group by T169099.YEAR_ID

            order by c2

            ---------------------------------------------------------------------------------

            WITH

            SAWITH0 AS (select T169099.YEAR as c2,

                 max(T215634.REMOVED_WEIGHT) as c3,

                 T215634.CLAIM_ITE_ID as c4

            from

                 COMMON_SL.DWD_TIME T169099,

                 COMMON_SL.DIM_LOGISTICS_CLAIM_COST T216244,

                 (

                      COMMON_SL.FCT_LOGISTICS_CLAIMS T215634 left outer join COMMON_SL.DIM_EXCHANGE_RATES T169098 On T169098.DAY_ID = T215634.RATE_DATE and T169098.FROM_CURRENCY = T215634.COST_CURRENCY_GID) left outer join COMMON_SL.DIM_EXCHANGE_RATE T233060 /* DIM_EXCHANGE_RATES_CLAIM_ITEM */  On T215634.RATE_DATE = T233060.DAY_ID and T215634.SIZE_OF_LOSS_CURRENCY = T233060.FROM_CURRENCY

            where  ( T169099.DAY_ID = T215634.NOTIFICATION_DATE and

            T169098.TO_CURRENCY = 'E' and

            T169099.YEAR_ID = '2017' and

            T215634.CLAIM_ITEM_COST_SID = T216244.CLAIM_ITEM_COST_SID and

            T216244.REPORTED_BY = 'HUSTN' and

            T233060.TO_CURRENCY = 'E' )

            group by T169099.YEAR_ID, T215634.CLAIM_ITEM_SID),

            SAWITH1 AS (select sum(D1.c3) as c1,

                 D1.c2 as c2

            from

                 SAWITH0 D1

            group by D1.c2)

            select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,

                 D1.c2 as c2,

                 D1.c1 as c3,

                 cast(NULL as  DOUBLE PRECISION  ) as c4

            from

                 SAWITH1 D1

            order by c2 ) D1 where rownum <= 5000001

             

            Query with data visibility filter role added to user.

             

            WITH

            SAWITH0 AS (select T169099.YEAR as c2,

                 max(T215634.REMOVED_WEIGHT) as c3,

                 T215634.CLAIM_ITE_ID as c4

            from

                 COMMON_SL.DWD_TIME T169099,

                 COMMON_SL.DIM_LOGISTICS_CLAIM_COST T216244,

                 (

                      COMMON_SL.FCT_LOGISTICS_CLAIMS T215634 left outer join COMMON_SL.DIM_EXCHANGE_RATES T169098 On T169098.DAY_ID = T215634.RATE_DATE and T169098.FROM_CURRENCY = T215634.COST_CURRENCY_GID) left outer join COMMON_SL.DIM_EXCHANGE_RATE T233060 /* DIM_EXCHANGE_RATES_CLAIM_ITEM */  On T215634.RATE_DATE = T233060.DAY_ID and T215634.SIZE_OF_LOSS_CURRENCY = T233060.FROM_CURRENCY

            where  ( T169099.DAY_ID = T215634.NOTIFICATION_DATE and

            T169098.TO_CURRENCY = 'E' and

            T169099.YEAR_ID = '2017' and

            T215634.CLAIM_ITEM_COST_SID = T216244.CLAIM_ITEM_COST_SID and

            T216244.REPORTED_BY = 'HUSTN' and

            T233060.TO_CURRENCY = 'E' )

            group by T169099.YEAR_ID, T215634.CLAIM_ITEM_SID),

            SAWITH1 AS (select sum(D1.c3) as c1,

                 D1.c2 as c2

            from

                 SAWITH0 D1

            group by D1.c2)

            select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,

                 D1.c2 as c2,

                 D1.c1 as c3,

                 cast(NULL as  DOUBLE PRECISION  ) as c4

            from

                 SAWITH1 D1

            order by c2 ) D1 where rownum <= 5000001

             

            So strange thing is that an additional query is getting fired on FCT_SHIPMENT_UNIT  from the user  who doesn't have visibility role added to his profile.

            This is what the main problem why this query is getting fired only from the user who doen't have visibility role.

             

            Really thank you for help.

            Regards,

            Abhi

             

            • 3. Re: Need to understand how data filter works -1
              Robert Angel

              Hi,

               

               

              I suspect the extra query against FCT_SHIPMENT_UNIT is because the security has been applied to work via that fact table.

               

              Have a look at this to understand how row level security is applied =>

               

              https://www.rittmanmead.com/blog/2012/03/obiee-11g-security-week-row-level-security/

               

               

              See the section on session variable initialization - there must be something on a parallel to this happening in your case, have a read of this and if you don't find it then come back to me and ask further.... I'll do what I can.

              1 person found this helpful
              • 4. Re: Need to understand how data filter works -1
                Robert Angel

                So, in a nutshell.

                 

                1. The user logs in, here an initialization session variable is populated

                2. When the user accesses the dimension / fact table in question the extra query runs to filter the rows of data, basically joining the 'group' the user is in, to the rows of data they are allowed to see, and using this as a filter against the underlying table

                1 person found this helpful
                • 5. Re: Need to understand how data filter works -1
                  3521884

                  Hello Robert,

                   

                  Thank you for your response, I will be going through the link you provided and let you know once I get something more.

                  Kindly give me a day time to respond you back.

                  However, I just tried to create the union report via the test user (having visibility role) with two column from Fact shipment and Fact Claim with Time dimension  and its working,....while the same report when I pull it together in a single table format its not only Time dimension and Fact Claim works.

                   

                  I really appreciate your time and guidance Robert.

                  Thanks you very much.

                  Regards,

                  Abhi

                  • 6. Re: Need to understand how data filter works -1
                    Robert Angel

                    Hi,

                     

                     

                    we are not a formal help desk here, there is no SLA, but I do check fairly regularly in GMT office hours (and out of) so any problems just ask and I will try to clarify.

                    1 person found this helpful
                    • 8. Re: Need to understand how data filter works -1
                      Robert Angel

                      Did you solve your issue?

                       

                      If so can you kindly close the question (assumed answered or if you want to point the answer out to others 'Mark Correct'.

                       

                       

                      thanks,

                       

                      Robert.