Combining accounts of different dimensionality in an expression

One of the moments of greatest frustration in writing HFM rules is when we tend to ignore the Dimension Intersection Considerations applying to references of dimension members of the source and destination Points of View (POV) in the expression string (HS.Exp). I will not get into every detail of writing correct expressions, since it is well documented in the Oracle Hyperion Financial Management Administrator's Guide. Instead, I will focus on a common category of expressions, where accounts of different analysis are involved.

Let's consider for example that we have three accounts, "IDest", "ISourceA" and "SourceB". The first two accounts are specified as inter-company (IC) in their metadata definition, while the third is not.

Now, lets suppose that we need to get the sum of ISourceA plus SourceB into IDest so that all IC members of the first source pass to the corresponding IC of the destination and only the [ICP None] member of the second source account passes to the corresponding member of the destination account. An experienced rules author recognizes that this expression will not work:

HS.Exp "A#IDest = A#ISourceA + A#SourceB"

This is because when the destination account's dimension members are not explicitly specified in the expression, HFM implicitly uses all valid combinations of members of unspecified dimensions. This is not a problem as regards the first account on the right side of the equation ISourceA, which will pass the value of each IC member, to the corresponding IC member of the IDest account.

However, this is not the case of the second account SourceB. Since this account does not support IC analysis, the only valid IC member having some data is [ICP None] and HFM will pass that value to every IC member of the IDest account, ending up in redundant values in the destination.

There are several ways to handle this, like building member lists or using data units. Both member lists and data units involve For loops and make code unnecessarily complex and hard to read. My preferable solution to this issue is summarized in the following two lines of code:

HS.Exp "A#IDest = A#ISourceA"
HS.Exp "A#IDest.I#[ICP None] = A#ISourceA.I#[ICP None] + A#SourceB.I#[ICP None]"


The first line executes between the two IC accounts and passes every IC value of the source to the corresponding IC value of the destination, including the [ICP None] member. The second line, passes the sum of the two source accounts' intersections with [ICP None], to the [ICP None] member of the destination account. You might observe that [ICP None] gets a value in both lines, the first line setting it incorrectly to the [ICP None] value of ISourceA, thought we won't bother because the second line overwrites the destination account's [ICP None] member with the correct value.

Of course, according to the Dimension Intersection Considerations section of admin manual mentioned above, we could omit the I#[ICP None] from both accounts on the right side of the equation in the second line. However, we have chosen to preserve the explicit references to IC members for better understanding of the issue at hand and for greater code readability.

This technique is not limited to the ICP dimension. It can be applied to any other custom dimension as well. In cases that the destination account along with some of the source accounts share a common top member of the custom dimension in their metadata definition, while the remaining source accounts support only the [None] member of the same custom dimension, the technique shown here is suitable.

As a final word, the selected example used for exhibiting the presented concepts, was about using rules for adding up two accounts. The same effect could also be achieved by building an account hierarchy in metadata, having IDest as the parent account and ISourceA along with SourceB as the children. No rules would be involved in that case. Though, the later approach can not be generalized as a substitute for rules in every case at hand.