Shared Member Problems in BSO Calcs

Shared members are an amazing feature of Essbase but they seem to always present challenges and many of those are not easy to spot.

A question arose on the Planning Cloud Customer Connect recently where the issue the poster was having was related to a shared member/alternate hierarchy. For some reason, it's never immediately apparent that this is the problem. Several people suggested a number of reasons before it dawned on me that a shared member was the culprit.

In a nutshell, they were requesting the parent of a shared member, expecting to get back the shared member's parent. However, they were getting back the protype's parent.

Suppose the following hierarchy:

If you had a calc script that was iterating through the level 0 members under Diet, and you requested the parent of each, you wouldn't get back the member name Diet. For 100-20 you'd get back 100, for 200-20 you'd get back 200 and for 300-30 you'd get back 300.

This problem shows up when you have a formula with something like the following:

@PARENT("Products")

Note: The @PARENT calculation function for Essbase returns the parent of the current member being calculated in the specified dimension.

The workaround the poster settled on was to add a new alias table and look up based upon that using the @ALIAS() function. It would be nice if there was a function, maybe @SPARENT(), that would return the parent of a shared member.

Update: I'm told that @SPARENT() does exist as an undocumented function. I tested the function and it returns the parent of the first instance of a shared member. If you need the second or greater instance, it seems, you're out of luck. Use at your own risk.