Here’s just a quickie I saw the other day. Imagine a normal cube with a Years dimension, a Scenario dimension, and any other normal dimensions. Years contains FY2012, FY2013, FY2014 or similar and so on. Scenario contains Actual, Budget, and all the other normal stuff you’d expect to see.
Naturally, the Scenario dimension will contain all sorts of handy dynamic calcs, starting with our trusty Actual to Budget variance:;
Actual vs. Budget: @VAR("Actual", "Budget");
So far so good.
How about a scenario that gives us the current year versus the prior year? Don’t do this:
Curr vs. Prior: @VAR("FY2014", "FY2013");
Or this (which is I guess slightly better but still not quite great):
Curr vs. Prior: @VAR(&CurrentYear, &PriorYear);
Why shouldn’t you do this? One, it requires maintenance – the kind of maintenance that is easily forgotten about util a user calls up and says that something doesn’t look quite right.
Second and more importantly, it’s semantically wrong. Hard-coding the year effectively breaks the inter-dimensional promise that our cube is ostensibly making – which is that the Scenario value we’re looking at should be based on the current Year member – not some arbitrary member irrespective of the POV.
(This all being said, yes, there could be a legitimate design reason to code a dynamic calc in Scenario that is always the current year irrespective of the POV, but I digress).
A simple formula can get us the prior value, as well as the actual versus prior:
Prior: @PRIOR("Actual", 1, @CHILDREN("Years")
Actual vs. Prior: @VAR("Actual", @PRIOR("Actual", 1, @CHILDREN("Years")));
Note that this assumes there is nothing else in the Years dimension and that it’s got a typical “ascending” sort (2010, 2011, 2012, in that order). If you have a years dimension going in descending order you could put -1 in for the @PRIOR command or just switch to @NEXT.
There you have it – a simple cleanup that saves maintenance, doesn’t rely on outline variables being updated, is intuitive, and more importantly, is doesn’t break the semantics of the cube.