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.
hi,
interisting explanation on how to handle \’relative time\’ on ssas.
it may be of interest to your reader to have a look at the article below.
it is a detailed explanation of your implementation.
http://www.databasejournal.com/features/mssql/article.php/3518771
Nicolas
hi,my manager tried this work in a project with ssas2000 nearly 3 years ago .
the "relative period" we create also included "average of 3 years in the past","average of nearest 6 month in the past ",and so on .
I\’ve solved this problem by using named sets with a corresponding calculated member. The web tool we use doesn\’t recognize sets. Here\’s what i did:
1) Create the set: \’set13WKCURR\’ that holds the latest 13 weeks:
[Time].[WEEK].Members([Measures].[LastWeekIndex]):[Time].[WEEK].Members([Measures].[LastWeekIndex]).Lag(12)
2) Create the calculated member \’13 WK CURR END FEB 25 06\’ in the time dimension:
Sum({set13WKCURR})
If you only have additive measures, you can stop here. Some of our measures are not additive over time, so we have to be able to get to the members of the set if the user has selected this calculated member. The trick is to use a patterned naming convention with sets and calculated members. Then use the Excel function to remove spaces (not allowed in set names) and other unwanted characters (like the date at the end of the name).
3) Create the measure calculated member "% ACV":
Iif(IsTimeCalcMem = 1, Avg(StrToSet("set" + Substitute(Mid(Time.CurrentMember.Name, 1, (Find("END", Time.CurrentMember.Name) – 1)), " ", "")), [Measures].[ACVPCT NonTimeAgg]), Avg(Descendants(Time, [Time].WEEK), [Measures].[ACVPCT NonTimeAgg]))
\’IsTimeCalcMem\’ is a measure that returns 1 if the current member of time is calculated member. If true, then \’% ACV\’ figures out the set name from the name of the time calculated member and averages another measure \’ACVPCT NonTimeAgg\’ over the weeks in the set. If the current member of time is not a calculated member, then \’% ACV\’ averages over the week-level descendants of time.
Hello world,I have been trying to do a seemingly simple task of adding relative time periods in my MS Cube without using time intelligence within SSAS. What I found over the Internet was people suggesting using MDX to do so. My goal was to get attributes like previous and current month or year or week. Since I was not keen on writing MDX (due to my lack of knowledge) I have managed to do a workaround. What I did was the following:In the data source view of the cube i created a new named calculation called RelativeMonths and in the expression box wrote the following:case when Month = datepart(mm, getdate())and Year=datepart(yyyy, getdate())then \’CurrentMonth\’when Month = datepart(mm, getdate())and Year=datepart(yyyy, getdate())-1then \’LastYearsMonth\’when Month=datepart(mm, getdate())-1and Year=datepart(yyyy, getdate())then \’PreviousMonth\’else \’OtherMonths\’endSo I used case when logic rather than MDX to get the calculated members I so desperately needed.I hope someone has used this approach to relative time creation within MS cubes. I would like to know whether this approach has some downsides to using MDX.CHeers\’