time relatives - YTD, MTD, QTD the best approach

[djoshi]

What are the various design options for calculating fact by YTD,MTD, QTD for multiple different calendars?

[kap]

I use Microsoft SQL Server 2005 and calculate them in the cube definition by using the Add business inteligence wizard in the Cube designer. A table level and don´t have anything like this since all my queries are using the cubes.

[ngalemmo]

Can you elaborate on 'multiple different calendars'? How many are there and does it affect the interpretation of month, quarter, and year?

[Guest]

We have defined a number of flags against our calendar table e.g. week to date , month to date. We have a scheduled script that runs at 1 minute past minute to update these flags every day.

To then sum up a fact becomes a simple condition - where week_to_date_flag = 'Y'.

If you have different calendars you could add and maintain flags on the calendar table - for example, we maintain calendar week,month, year to date flags and financial week, month, year to date flags on ours.

[ngalemmo]

I have done something similar using sequence and offset values. The date dimension has sequence numbers for day, week, month, quarter and year. These are set when the table is first built (or reset when new dates are appended) by counting of each period in chronological order. There are then a series of offset values for day, week, month, quarter and yeat that are calculated daily by subtracting the current day's sequence from the date in question's sequence (all 5).

The offset values will then be zero for the current day, week, month, quarter and year, -1 for the previous period, +1 for the next future period and so on. To do quarter-to-date, for example, you would select the quarter offset = 0.

This is more flexible than flags because you can use the offsets for things like last quarter, same month last year and so on simply by selecting the correct offset. If you need to support a different fiscal calendar, you can have another set of sequences and offsets to support that.

[Guest]

I don't think there is much between the flags and sequences as a method. We also have flags for this week last year, this month last year, and from a user perspective I think it is more common to think in terms of yes/no that an offset value. I can see the benefit if you want to look at say two quarters ago but within our business the reporting is very much week/month/quarter/year to date and this year/last year comparisons, so flags work fine for us.

[Sponsored content]