Handling Relative Time Periods (by Chris Webb)
I recently had an interesting exchange with Michael Barrett Jensen and Thomas Pagel on the MSDN Forum about the best way to handle the problem of relative time periods, ie letting the user select a member called something like 'current month', which always returns the most recent month's data (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=346119&SiteID=1 and please ignore the posts in the middle of the thread where I talk rubbish about when sets are evaluated). Now for some scenarios it's a good idea to use named sets to hold the 'current month' or the last five months or whatever you want - if you have a client tool which handles sets well, then the advantage of this is that it works as a kind of dynamic selection and you get set of members you want each time. However not all front ends (eg Excel) support sets, they can be a difficult concept for some users to understand, and they are a bit inflexible. So as an alternative here's the outline of a method for building a relative time dimension to add to your cube - it's more of a sketch than a robust solution, so I'd be interested in hearing feedback from anyone who tries to implement it.
Tags: time dimension