Share via

OLAP Cubes: Relative Dates - Today, Yesterday, LastWeek etc.in your date dimension

  • Article

A useful little piece of functionality we often add to OLAP cubes is what we call Relative Dates.  

In the Date dimension you can add attributes CurrentDay, CurrentWeek, CurrentPeriod, CurrentYear etc.  These attributes are simply functions within the Dates dimension view, which gets processed early every morning. For example, CurrentDay could be something like:

Case

when d.MyDate=convert(date,getdate()) then 'Current Day'

when d.MyDate=convert(date,dateadd(dd,-364,getdate())) then 'Current Day LY'

when d.MyDate=convert(date,dateadd(dd,-1,getdate())) then 'Yesterday'

when d.MyDate=convert(date,dateadd(dd,-365,getdate())) then 'Yesterday LY'

when d.MyDate=convert(date,dateadd(dd,-2,getdate())) then 'Day before yesterday'

when d.MyDate=convert(date,dateadd(dd,-366,getdate())) then 'Day before yesterday LY'

else 'Other days'

end CurrentDay,

Note, Current Day Last Year is -364 days, which will ensure that it will be the same day of week. It's the same as saying -52 weeks.

Note, you only need to process the Date dimension. The large fact tables do not need to be reprocessed, as their Date dimension keys are not changing. It's just the flexible attributes of the Date dimension that are changing daily.

Note, it is not practical to create a hierarchy of these attributes as they don't fall within one another. For example, Today and Yesterday might be in the same fiscal week, or they might fall in consecutive weeks.

The logic for CurrentWeek, etc. is a bit more complicated as your case statement will be checking if the FiscalWeek of getdate() is the same as the FiscalWeek of MyDate. Eg.

case

when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,getdate())) then 'Current Week'

when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-52,getdate()))) then 'Current Week LY'

when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-1,getdate()))) then 'Last Week'

when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-53,getdate()))) then 'Last Week LY'

else 'Other weeks'

end CurrentWeek,

You can do the same sort of logic for Periods, Quarters, and Years.

Note, this functionality does not replace Time Intelligence. If anything it adds to it as Time Intelligence will go across the Relative Dates attributes, or, more likely, you can use the Relative Dates attributes to filter dates and use a regular date hierarchy on rows/columns with Time Intelligence.

The advantage of the CurrentDay, CurrentWeek, CurrentPeriod, and CurrentYear attributes is that reports can be relative in time. For example, the filter can be a multi-select of this period and last period, showing weeks on columns, which will have 5-9 weeks. So the report will always display the last 5-9 weeks, without the user having to select the current period etc. These filters will work in Reporting Services, Excel, PerformancePoint, or any other cube browser/reporting tool. To achieve the same without these "relative" attributes, you could create sets, but they would not perform as well, and there can be issues when combining with other Date dimension attributes.