Note on the IsToday-column
The IsToday-column is a flag indicating which date is "today". The column contains only 0's
when generated by this service and it is up to you (i.e. your ETL) to set the flag.
The flag makes the very common "current day", "current week",
"current month", "year-to-date", etc. calculations very easy.
By using the flag set by the ETL-process instead of using the system date,
the "current date" is always the date of the last successful ETL-job, i.e. the "today" of
your data warehouse. In other words, if you update the time table at the very end
of your ETL job (i.e. issues an UPDATE setting a '1' on the row with today's date and
'0' everywhere else) - the global definition of "today" is readily available for
your data marts to use. And furthermore: It efficient.
Example: Common sets in Analysis Services 2005
As mentioned before, "CurrentDate"-calculations are very common on cubes. Here
are a few examples from Analysis Services 2005 (MDX calculated sets).
[CurrentDate] (returns "today"-member)
[CurrentWeek] (week of "today")
[CurrentMonth] (month of "today")
Other time calculations are easy as well:
[PreviousDate] (returns "yesterday"-member)
[PreviousWeek] ("last week"-member)
[PreviousMonth] ("last month"-member)
This query will return data from last week:
SELECT [rows and columns...]