Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Need to understand how data filter works -1
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
Answers
-
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.
0 -
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
0 -
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.
0 -
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
0 -
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
0 -
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.
0 -
Thanks Robert
0 -
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.
0