Adding dynamic date logic to a datawarehouse

Often with reporting on a data warehouse you see a common theme that many companies want to report on things with the context of my current date. Take for instance a sales report where you want to see this month’s sales activity. There are several ways to handle this but I always try to side with keeping my business logic in the data warehouse so I have one place to maintain this. So the method I prefer is having flags in the Date dimension to indicate current month, current year, current day etc. This can create a good bit of overhead though since you would have to update these flags nightly. This is where dynamic Calculated columns on the table come in handy. I can create a table with columns like this:

CREATE TABLE [dbo].[DimDate](

[Datekey] [date] NULL,

[isCurrentDay]  AS (        case when [Datekey]=CONVERT([date],getdate(),0) then (1)

else (0)        

end),

[isCurrentMonth]  AS (        case when datepart(month,[DateKey])=datepart(month,getdate())

AND datepart(year,[DateKey])=datepart(year,getdate()) then (1)

else (0) end),

[isCurrentYear]  AS (        case when datepart(year,[datekey])=datepart(year,getdate())

then (1)        

else (0) end)

) ON [PRIMARY]

GO

So now the flags are completely dynamic all you need to worry about is that if you are processing you dimension or caching reports you must make sure you do it after midnight or adjust you formula accordingly.