Team,
I have Two Dimensions and a Fact Tables which are driving my Reports. I need to apply security based on AD Groups created.
We have a Lookup Security Table which is 'Security Table' in the below code
In the Framework Model --> Database Layer --> Using Native SQL using a query we are applying the Filter Conditions on the Fact Table as below. The Report use to Run for 10 Secs and after implementing the Security in FM the Report is Running for 1 minute.
Please let me know if I need to change the below SQL, to improve the performance.
select f.* from FactTable f
join DIMTable1 p on p.PCCode = f.PCCode
where exists (
select distinct 1 from SecurityTable s
join DIMTable2 c on c.CCode = s.N_KEY
where s.line_of_biz = 'MF' and s.active_ind = 'Y'
and (
case
when s.ldap_DSC = 'GROUP_NAME' and s.ldap_common_name in ( #CSVIdentityNameList()# ) then f.CCode = s.N_KEY
when s.ldap_DSC = 'USER_NAME' and s.ldap_common_name = #sq($account.personalInfo.userName)# then f.CCode = s.N_KEY
end
)
)
union
select f.* from FactTable f
where exists (
select distinct 1 from SecurityTable s
join DIMTable1 p on p.PCCode = s.N_KEY
where s.line_of_biz = 'MF' and s.active_ind = 'Y'
and (
case
when s.ldap_DSC = 'GROUP_NAME' and s.ldap_common_name in ( #CSVIdentityNameList()# ) then f.PCCode = s.N_KEY
when s.ldap_DSC = 'USER_NAME' and s.ldap_common_name = #sq($account.personalInfo.userName)# then f.CCode = s.N_KEY
end
)
)
Thanks!
Kiran