OBIEE Hierarchy Navigation Functions {HNF} Part 1

Since 11g OBIEE has some nice hierarchy navigation function for Parent-Child hierarchies. If you have a 10g background like me you will probably have a natural tendency to work around hierarchy stuff, since it wasn’t available. In this article series I want to show you the functions and how to implement them. There are functions available:

• ISPARENT (Who is my Mother or Father?)
• ISANCESTOR (Who are the {great}(grant)-parent(s)?)
• ISCHILD (Who is my child?)
• ISDESCENDANT (who are the {great}(grant)-children?
• ISLEAF (are there children?)
• ISROOT (Who is the overall boss?)
• ISBROTHER (or sister) (You have to wait until the final part to see the solution)

member_identifier

The functions ISPARENT, ISANCESTER, ISCHILD and ISDENSCENDANT all depend on the member_identifier. This is the column Member Key you identify in your logical table source.

ISPARENT

From the documentation:

The ISPARENT function enables you to find the parents of a member of a parent-child hierarchy, that is, all the members that are one hierarchical level above the specified member.

Presentation Layer Syntax:

ISPARENT(pc_presentation_hierarchy, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("Sales Person"."H5 Sales Rep",'21') Then 'YES' else 'NO' END Example “Session Variable”: Case When ISPARENT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser)) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax:

ISPARENT(logical_dimension, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END

Example “SessionVariable”

FILTER("13 - Hierarchy levels"."F0 Sales Base Measures"."1- Revenue" USING   ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser")))

These functions are also available in the LTS and can be used as data access restriction.

Till Next Time