Due to Security - Report Run Time is increased

[Kiran Kandavalli]

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.

Code Select Expand


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