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:
@VAR("FY2014", "FY2013");
Or this (which is I guess slightly better but still not quite great):
@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:
@PRIOR("Actual", 1, @CHILDREN("Years"))
As well as the actual versus 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.
Jason,
I agree with not hard coding because of maintenance, but in this case it might be worth it abet with a slight change from your code. @Prior can be extremely slow. We retrieved a very large spreadsheet that was using @prior variances like yours and the retrieval took minutes (many thousand cells) by putting an if statement into the formula like (disregard any typos or syntax errors):
IF(@ISMBR("FY14")) "FY14"- "FY13"; ELSEIF (@ISMBR("FY13") FY13" - "FY12"; ELSEIF ...... ELSE #MISSING; (for the first year) ENDThis took seconds to retrieve the same worksheet
So sometimes while it is less maintenance to use functions, for performance it is better to hardcode. The nice thing about my code is you only have to change it once a year and it works on multiple years. If you want to use this code in multiple cubes, you could try making it a substitution variable and then update it once and use it multiple places
Very interesting — thanks for the insight. The nice thing too about your version is that it keeps the “inter-dimensional promise” of the cube. I.e., the variance scenario is based on the year in the POV. This was the big drawback of the cube I saw, in that it was hard-coded to ignore the current year, as it were.
There are two bright spots for anyone considering homemade alternative energy systems.
Some with the drawbacks of those systems is that it might not be dependable.
Around $117 billion was committed to projects concentrating on alternative energy sources
including wind, biofuel, solar and geothermal.