I've got a strange situation with a report that I have created. Under my login which does not have any security filters, the sql for the report is correct:
select a11.SBU SBU,
a11.Company Company,
a11.ReservationCarrier ReservationCarrier,
a11.ReservationOrigin ReservationOrigin,
a11.ReservationDestination ReservationDestination,
a11.CardType CardType,
a12.Month Month,
a12.MonthName MonthName,
a12.CalendarYear CalendarYear,
sum(a11.DetailDeposit) WJXBFS1
from Res_Summary_Accounting_Detail a11
join Dim_Date_Reservation_Depart a12
on (a11.ReservationDepartDate = a12.ReservationDepartDate)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
and a11.CancelDate is null)
group by a11.SBU,
a11.Company,
a11.ReservationCarrier,
a11.ReservationOrigin,
a11.ReservationDestination,
a11.CardType,
a12.Month,
a12.MonthName,
a12.CalendarYear
Under a user's login that does have a security filter on the SBU attribute, the sql for the report is incorrect. Notice how the sql performs a distinct select, leaves out the sum on the metric and needlessly joins to all of the dimension tables:
select distinct a11.SBU SBU,
a11.Company Company,
a11.ReservationCarrier ReservationCarrier,
a11.ReservationOrigin ReservationOrigin,
a11.ReservationDestination ReservationDestination,
a11.CardType CardType,
a14.Month Month,
a14.MonthName MonthName,
a14.CalendarYear CalendarYear
from Res_Summary_Accounting_Detail a11
join Dim_SBU a12
on (a11.Company = a12.Company)
join Dim_Carrier_Reservation a13
on (a11.ReservationCarrier = a13.ReservationCarrier)
join Dim_Date_Reservation_Depart a14
on (a11.ReservationDepartDate = a14.ReservationDepartDate)
join Dim_City_Reservation_Destination a15
on (a11.ReservationDestination = a15.ReservationDestination)
join Dim_City_Reservation_Origin a16
on (a11.ReservationOrigin = a16.ReservationOrigin)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
and a11.ActivityType = 'DEP'
and a11.SBU in ('ADV', 'AMV'))
Has anyone ever seen this situation? I've tried changing the dimensionality on the metric to no avail. If I run the report under my login and answer the SBU prompt to essentially replicate what the user's security filter is doing automatically, the sql still looks good. I just don't understand what the security filter is doing. The SBU attribute used by the security filter looks fine.
Thanks.
select a11.SBU SBU,
a11.Company Company,
a11.ReservationCarrier ReservationCarrier,
a11.ReservationOrigin ReservationOrigin,
a11.ReservationDestination ReservationDestination,
a11.CardType CardType,
a12.Month Month,
a12.MonthName MonthName,
a12.CalendarYear CalendarYear,
sum(a11.DetailDeposit) WJXBFS1
from Res_Summary_Accounting_Detail a11
join Dim_Date_Reservation_Depart a12
on (a11.ReservationDepartDate = a12.ReservationDepartDate)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
and a11.CancelDate is null)
group by a11.SBU,
a11.Company,
a11.ReservationCarrier,
a11.ReservationOrigin,
a11.ReservationDestination,
a11.CardType,
a12.Month,
a12.MonthName,
a12.CalendarYear
Under a user's login that does have a security filter on the SBU attribute, the sql for the report is incorrect. Notice how the sql performs a distinct select, leaves out the sum on the metric and needlessly joins to all of the dimension tables:
select distinct a11.SBU SBU,
a11.Company Company,
a11.ReservationCarrier ReservationCarrier,
a11.ReservationOrigin ReservationOrigin,
a11.ReservationDestination ReservationDestination,
a11.CardType CardType,
a14.Month Month,
a14.MonthName MonthName,
a14.CalendarYear CalendarYear
from Res_Summary_Accounting_Detail a11
join Dim_SBU a12
on (a11.Company = a12.Company)
join Dim_Carrier_Reservation a13
on (a11.ReservationCarrier = a13.ReservationCarrier)
join Dim_Date_Reservation_Depart a14
on (a11.ReservationDepartDate = a14.ReservationDepartDate)
join Dim_City_Reservation_Destination a15
on (a11.ReservationDestination = a15.ReservationDestination)
join Dim_City_Reservation_Origin a16
on (a11.ReservationOrigin = a16.ReservationOrigin)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
and a11.ActivityType = 'DEP'
and a11.SBU in ('ADV', 'AMV'))
Has anyone ever seen this situation? I've tried changing the dimensionality on the metric to no avail. If I run the report under my login and answer the SBU prompt to essentially replicate what the user's security filter is doing automatically, the sql still looks good. I just don't understand what the security filter is doing. The SBU attribute used by the security filter looks fine.
Thanks.
