Monday, September 17, 2012

Limitation - Descendants MDX Function against Sets/Hierarchy

Though it is documented on the IBM Support Site that Descendants against Set functions may result in incorrect outcome, wish there was a way we could work around this limitation.

Some reasons why we may need Descendants function against a Hierarchy name rather than a member is:
  • When we use TM1 as the source and have security set up such that a user has access to the lower level members of a dimension hierarchy but not to the root member of the hierarchy (which is perfectly valid in our business scenario).
  • And we do not have named levels set up since we deal with unbalanced hierarchies, again valid when dealing with Accounts dimension.
  • And we have multiple alternate hierarchies set up against a dimension.

So in this case we are limited to using the descendants function against the hierarchy name rather than the root member which does not result in the required outcome.

So one of the work-around in this case is to use other MDX expressions like Descendants with Except to build the set required.

No comments: