Time series in BI revisited

But before that:

Why are recruitment companies so unnecessarily secretive ?

I have just seen an ad for a senior role at a leading Global BI vendor that goes out of its way to hide the name of the company recruiting - it's a bit sad though that they mentioned part of the role is to "help [product X - real name given!] customers use more of the company's [real name given again] product stack"

Looking at cumulative values for a measure over a period of time (month to date, last 52 weeks or whatever) and reporting now against a previous time are common query types for most BI users. Forgetting the problems of how actually to calculate such measures in either a relational or OLAP source (often OLAP cubes have very helpful functions for moving totals)and the benefits of pre-calculating versus loss of sparsity, we arrive a fundamental problem: many orgainsations simply do not use ISO calendars. Fiscal years can start at almost anytime (or so it seems), some organisations use calendar months, some have 13 four-week periods in a year. Year-on-year reporting may be same-date-last-year or modified to be same day of the week. I've even seen "reporting months" starting on the first Monday in the month.

One practical way to deal with this is to add extra attribute columns to the denormailsed date table. Typically I would also include attributes such as "day of the week" and alternative presentational forms of the date, but the key thing for me is to record offsets into weeks, months, periods etc and values for same-day-last-[week/period/month/year]. And, of course I would probably include alternative date hierarchies as some people need to report on civil calendars and others on fiscal.

The good thing about calendar tables is that they don't often change - they are probably well worth the effort of the initial setup.