Offset Rules

Center of Excellence

1. Abstract

Offset rules are a type of rule which allow you to reference multiple blocks in the same layout as part of your calculation, they can be very powerful but should be used with caution.

2. Context

Offset rules can be very powerful in certain circumstances but should be used with caution due to the complexity they add to a solution and the potential for incorrect results to be returned if the wrong layout is used with them, this risk can be mitigated with the right precautions.

3. Content

3.1 Use of Offset Rules

3.1.1 How to Define an Offset Rule

An offset rule is essentially a normal rule with one or more lines that use the offset notation, this works by adding 1 or more full stops at the start of the tuple reference, e.g. if I was referencing a tuple with a code of PL1234 but I wanted to offset it by one block I’d use the notation [.PL1234], if I was offset by 2 blocks I’d use [..PL1234] and so on. This then means that when used the rule will look for the value of tuple PL1234, not in the block on which the rule has been applied, but either the preceding block if a single offset has been used, the block prior to that if a double offset has been used and so on.

3.1.2 When to use offset rules

Offset rules are typically used in complex calculations where you need to reference information not contained in the cube to which the rule is applied, but in another cube also dimensioned by the entity on which the rule is based – for example for a balance sheet calculation you might need to reference a cube which contains opening balances, or to which a year-to-date aggregation has been applied.

Using an offset rule in this way can facilitate complex calculations in a single rule, rather than possibly needing to use a series of data flows and temporary cubes to achieve the same result. However offset rules are not without their downsides as we’ll see, and they should only be used where it makes sense to do so, not as a first resort.

3.1.3 What to avoid with offset rules

The biggest mistake to avoid with an offset rule is combining multiple different uses within the same rule which requires different layouts to work, for example, if you are calculating a balance sheet you might have some rows which rely on different cubes for their calculation than other rows, meaning that the cubes you want to reference with your offset are different. In this circumstance, you should avoid having a single rule which uses one offset layout for one set of lines, and another for other lines – this makes the rule very difficult for somebody not familiar with it to understand, and greatly increases the chances of it being used incorrectly, it would normally be better in this situation to have more than one rule, and to clearly document how each rule should be used.

3.1.4 Downsides of using offset rules

The biggest downside to using an offset rule is that whereas with a normal rule, somebody not familiar with it can simply look at the rule and understand what calculations are taking place – the rule itself contains all the information they need to see how the calculation works. With an offset rule to understand the calculations taking place, it’s necessary to look not only at the rule itself but also the context in which it’s used – i.e., the layout it’s designed to work with. This means that offset rules need to be clearly documented in case the original designer is unavailable and somebody else needs to understand what the rule is doing.

The second downside to an offset rule is that any time the rule is used, the expected offset columns must be present for the rule to work – this is the case even if the rule is being used to calculate only the non-offset lines. For example, if your rule contains a single line that uses a double offset – any time you want to use that rule you need to ensure that the block using the rule has at least 2 preceding blocks, even if you aren’t using the offset line, this is because the rule as a whole is validated before being executed, and if the expected offset blocks are not present the validation will fail and the rule will not be executed.

3.1.5 Summary

What we’ve seen here is that offset rules can be tremendously useful in the right scenario and can facilitate complex calculations that might otherwise require multiple data flows and temporary cubes, however, we’ve also seen that they require careful use and clear documentation to avoid mistakes being made – in short they’re a great technique to have at our disposal but should only be used if the situation absolutely calls for it.

Center of Excellence

Thanks @Robert Barkaway for all the insights on this topic!