Handling Relative Time Periods

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],


([Date].[Calendar].[Calendar Quarter].members,


([Relative Time].[Relative Quarter].currentmember, [Relative Time].[Relative Quarter].[Relative Quarter].members)




([Relative Time].[Relative Year].[Relative Year].Members, [Measures].[Internet Sales Amount]) =

([Measures].[Internet Sales Amount],

tail([Date].[Calendar].[Calendar Year].members,


([Relative Time].[Relative Year].currentmember, [Relative Time].[Relative Year].[Relative Year].members)




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.

Solve Order in AS2005

You may already be aware – possibly the hard way – that solve order has changed in AS2005. Notably, calculated members defined with query or session scope can’t have an effective solve order lower than any calculated member defined on the server, the only exceptions being calculated members which use the AGGREGATE() function and (I believe) ones which use visualtotals functionality.
Here’s an example of this happening, using the [Gross Profit Margin] calculated measure in AdventureWorks:

with member [Product].[Category].AggTest as Aggregate([Product].[Category].[Category].members)


[Product].[Category].SumTest as Sum([Product].[Category].[Category].members)


[Measures].[Gross Profit Margin] on 0,


allmembers on 1


[Adventure Works]

In this case you can see Aggtest is evaluated at a lower solve order than [Gross Profit Margin], whereas Sumtest is evaluated at a higher solve order. MDX Solutions has a great writeup of this behaviour (courtesy of George) so I suggest you buy a copy if you’d like to know more about the details; it gets even more fun when you want to create calculated members with query or session scope which have solve orders higher than some server calcs and lower than others – one way of doing this is to use SUM() for the former and AGGREGATE for the latter depending on what the currentmember on Measures is, eg IIF(Measures.CurrentMember is Measures.ABC, SUM([MySet]), AGGREGATE([MySet])).
What isn’t in the book, though, is what has been happening since RTM. One post on the MSDN forums that I’ve pointed to before refers to a property called CalculationPassMode which will be in SP1:
However, as Mosha comments in his reply, there are some unpleasant side effects. There is apparently another property coming called SCOPE_ISOLATION which won’t be in SP1 which will give us back the AS2000 behaviour of query and server solve orders working properly together. I’ll let you know if and when it does appear.

Local Cubes and .NET 2.0

Here’s a little nugget of information I thought I’d post here for future reference: if you want to create local cubes direct from relational sources in AS2005 you need to have .NET 2.0 installed on your machine, although you don’t need to if you’re creating your local cubes from server cubes. I’m sure someone will find this useful… thanks to Siva for letting me know about this.

I’m an MVP!!!

I found out earlier this week but I thought I’d mention it here. Obviously I am very chuffed indeed. Time for a tearful Oscars-style acceptance speech: I’d like to thank everyone out there who has helped me over the years, whether at work, on the newsgroups or on this blog… (sniff)… I love you all!

Microsoft buys Proclarity

This is all over the blogs today, and with good reason: it’s the biggest news in the Microsoft BI world since, well, the release of OLAP Services. Here’s the press release:
Now, why has this happened, what does it mean and is it a good thing? Well, the first question is fairly easy to answer. Microsoft has for a long time suffered from not having a complete set of tools when it comes to BI: customers had to buy their server from one place (Microsoft, a big, fairly well trusted company) but unless they were willing to use Excel pivot tables (and few were) then got told to go and look at all the other client tools on the market which meant another round of evaluations, another swarm of salesmen to talk to and significant extra expense. It didn’t help that all these other client tool companies were relatively small and unknown and their products were, let’s face it, sometimes lacking in terms of quality and finesse. Certainly some companies appreciated having a choice, but in my experience the majority, especially the enterprise customers, didn’t. So as a result companies like Cognos and Microstrategy benefitted from being seen as the ‘one stop shop’ for BI solutions.
This situation was the result of a professed strategy by Microsoft to deliberately stay out of the client tools market. The thinking was that Excel and the other MS client tools would take the low-hanging fruit and leave the rest of the business to third parties like Proclarity; competition between these third parties would stimulate innovation and provide the customer with choice. In my opinion though this innovation didn’t really appear and the only choice the customer got was between similar tools, so that and the point I made in the previous paragraph about customers having to buy from two vendors is I think why this strategy got dumped. Interestingly, I heard from a Proclarity guy ages ago that MS tried to buy them in 2001 but Proclarity turned them down (MS going on to buy what became Data Analyzer instead); I would imagine that Proclarity changed their mind about being bought when they realised that the new functionality in Office 12 would mean that Microsoft would not only be taking the low-hanging fruit but just about everything edible on the lower-half of the tree, so providing much stiffer competition.
Is this a good thing then? For Microsoft and for its customers, yes. Proclarity is a good tool – I was impressed with what I saw of its latest release when I saw it a few months ago, and it seemed to be the tool on the market that took greatest advantage of the new features of AS2005. It also provides much needed infrastructure which now, presumably, will be much more closely integrated with the rest of Microsoft’s tool suite; I would guess that Microsoft will be investing even more in development to make the tool even better. It will be interesting to see what happens with pricing too – I would guess that licence costs will go down, with the obvious effect of making the Microsoft BI suite even more attractive on that front.
Of course there are going to be some losers in this situation, and in this case its all the other third party tool vendors. Panorama especially seem to be screwed – why buy their stuff when you can buy the equivalent tools directy from Microsoft? I can see that if the integration of Proclarity into Microsoft takes a long time, or if the integration of Proclarity tools with other Microsoft tools is too tight (eg if customers don’t use Sharepoint, or are unwilling to upgrade to Office 12, then they can’t use the other MS BI tools) then there might be some short-term opportunity for them, but in the long run I can’t imagine they’ll prosper. Then there are all the much smaller tool vendors, who carved out niches based on price or specialist functionality or who were tied to consultancies: times will be much harder for them now they’re seen as being in direct competition with Microsoft and their cost advantage is eroded, and many of them will disappear too. Listen out for the sound of wailing and gnashing of teeth in the partner community over the next few months… the comments on Mosha’s blog entry about this are only a start:
It seems a shame that this is happening but at the end of the day Microsoft’s first and only priority is its bottom line and its customers, and as I’ve said from that point of view this is the right move.
%d bloggers like this: