Fiddlesticks! I have confirmed that the join elimination does in fact work with a view, using the AdventureWorksDW2008 database. Unfortunately, the foreign key constraints look exactly the same in both databases. Now I need to find out what settings are effecting this behavior and check them in both databases. Or find some other reason it works in one but not the other. This behavior is definitely not working in my database. :angry:

-- Join to DimDate directly... this works. The join to DimDate is eliminated.

select dc.LastName, sum(f.SalesAmount)

from FactInternetSales f

join DimCustomer dc

on dc.CustomerKey = f.CustomerKey

join DimDate do

on do.DateKey = f.OrderDateKey

group by dc.LastName

-- Create a view that uses DimDate, renaming DateKey to OrderDateKey (to match name in fact table)

create view TestDimOrderDate AS

SELECT DateKey AS OrderDateKey,

FullDateAlternateKey,

DayNumberOfWeek

FROM DimDate

-- Now join to the new view, and DimDate is still eliminated

select dc.LastName, sum(f.SalesAmount)

from FactInternetSales f

join DimCustomer dc

on dc.CustomerKey = f.CustomerKey

join TestDimOrderDate do

on do.OrderDateKey = f.OrderDateKey

group by dc.LastName