Oracle Communities

Oracle Analytics and AI

Child Item

Need to understand how data filter works -1

User_B7ZTH

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

Find more posts tagged with

Accepted answers

All comments

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.

User_B7ZTH

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,

(

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

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.

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

User_B7ZTH

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

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.

User_B7ZTH

Thanks Robert

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.