Inzicht in uw bedrijfsgegevens en processen
Contact   |   Vacatures   |   Inloggen
#1 in inzichten en expertise

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)


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.


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.

Datum: 04-10-2011 Auteur: John Minkjan


 BI Technologie
 Interactive Dashboards
 Office integratie
 BI Applicaties
 Financial Analytics
 Project Analytics
 Human Resource Analytics
 Supply Chain Analytics
 Customer Sales Analytics
 Procurement and Spend Analytics
 Manufacturing Analytics
 BI Projecten
 Project management
 Advisering / BICC
 Over ons
 Laatste nieuws
 Inzetbare consultants
 Blog facturatie software
 Gratis urenregistratie
 Een factuur maken
 Een creditnota maken
 Een offerte maken
 Een pakbon maken
 Een betalingsherinnering maken
 Een factuur voorbeeld

Volg Insight Experts via jouw Facebook page

Deel deze blog met jouw sociale netwerken

Sitemap voor deze pagina