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.
Anyway, the first thing I did was create a new table in Adventure Works DW from which I built my Relative Time dimension. For the purposes of this exercise, I only created two columns/attributes: Relative Year and Relative Quarter. Relative Year had two members, Current Year and Current Year-1, and Relative Quarter had eight members, Current Quarter to Current Quarter-7. I then added this to the Adventure Works cube, but did not make any changes on the Dimension Usage tab, so it had no relationship with any measure group. I then added the following code onto the end of the MDX Script:
//Relative Time
([Relative Time].[Relative Quarter].[Relative Quarter].
Members, [Measures].[Internet Sales Amount]) =
([Measures].[Internet Sales Amount],
tail
([Date].[Calendar].[Calendar Quarter].members,
rank
([Relative Time].[Relative Quarter].currentmember, [Relative Time].[Relative Quarter].[Relative Quarter].members)
).
item(0)
);
([Relative Time].[Relative Year].[Relative Year].
Members, [Measures].[Internet Sales Amount]) =
([Measures].[Internet Sales Amount],
tail
([Date].[Calendar].[Calendar Year].members,
rank
([Relative Time].[Relative Year].currentmember, [Relative Time].[Relative Year].[Relative Year].members)
).
item(0)
);
What this does is overwrite the values displayed in the cube for the measure [Internet Sales Amount] and everything below the All Members of the two attributes on Relative Time – Current Year shows the value of the last member on the [Date].[Calendar].[Calendar Year] level, and so on. You now have a Relative Time dimension in your cube that users can use in their reports, but which has no impact on aggregations or processing. You can change the scope of the assignments to cover all the measures in your cube fairly easily.
The advantages of physically building the dimension, rather than using calculated members to do the same thing, are that a) you get drill down from Relative Year to Relative Quarter and b) your MDX Script doesn’t get cluttered with (potentially) hundreds of calculated members.
As I said, this is really just a proof of concept. I’m not 100% satisfied with the way this works (for example I’d like to see it return something nicer than repeated values when you crossjoin Relative Year and Calendar Year, and the Root on Relative Time doesn’t show sensible values unless you have as many relative periods as actual periods), but I thought it was worth posting in case anyone was interested.