Showing posts with label HIERARCHY. Show all posts
Showing posts with label HIERARCHY. Show all posts

Thursday, September 15, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 7

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF, in part 6 ISROOT. This will be last in the series for the time being

ISBROTHER

From the documentation:

….. There is no documentation….Why? It’s my own solution Knipogende emoticon. This is a trick to show data of people who have the same parent, starting from the child.

Let’s start by determining who is our daddy:

SELECT
     "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1
FROM "Y - Hierarchy levels"
WHERE
(ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF( NQ_SESSION.HierarchyUser)))

image

We don’t need the report, just the SQL from the advanced tab.

Next determine who our brothers and sisters are:

SELECT       

"Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

WHERE ("Sales Person"."E8  Manager Number"

IN (SELECT saw_0 FROM (SELECT "Sales Person"."E0  Sales Rep Number" saw_0 FROM "Y - Hierarchy levels" WHERE ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))

 

image

We can use this list as the base for an in statement:

case WHEN "Sales Person"."E0  Sales Rep Number" in

(SELECT        "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

   WHERE ("Sales Person"."E8  Manager Number" IN

      (SELECT saw_0 FROM

         (SELECT "Sales Person"."E0  Sales Rep Number" saw_0

          FROM "Y - Hierarchy levels"

          WHERE ISPARENT("SALES PERSON"."H5 Sales Rep",

           VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))) THEN "Base Facts"."1- Revenue" else 0.0 end

image

image

Till Next Time

Wednesday, September 14, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 6

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF.

ISROOT

From the documentation:
A presentation hierarchy member is defined as a root member if it has no ancestors above it in a parent-child presentation hierarchy.

Presentation Layer Syntax

ISROOT(pc_presentation_hierarchy)


Example “Hardcoded”:


Case When ISROOT("Sales Person"."H5 Sales Rep" ) Then 'YES' else 'NO' END


Business Model and Mapping Layer Syntax


ISROOT(logical_dimension)


Example “Hardcoded”:


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


image

Till Next Time

Tuesday, September 13, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 5

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT.

ISLEAF

From the documentation:

The ISLEAF function applies to both level-based and parent-child hierarchies. For both types of hierarchy, a leaf member is defined as a member that has no child members.

Presentation Layer Syntax

ISLEAF(presentation_hierarchy)

Example “Hardcoded”:

Case When ISLEAF("Sales Person"."H5 Sales Rep" ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax

ISLEAF(logical_dimension) Example “Hardcoded”:

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

image

Till Next Time

Monday, September 12, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 4

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD. Now it’s time for:

ISDESCENDANT

From the documentation:

The ISDESCENDANT function enables you to find the descendants of a member of a parent-child hierarchy, either all the descendants of a member, or the descendants at a specified hierarchical distance from the member.

Presentation Layer Syntax

ISDESCENDANT(pc_presentation_hierarchy, member_identifier [, distance])


Example “Hardcoded” distance = 1:

Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END


Example “Hardcoded” distance = 2:


Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",'24',2 ) Then 'YES' else 'NO' END


Example “SessionVariable” distance = 2:


Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser) ) Then 'YES' else 'NO' END

Note: If you leave the distance option out you will get all the children.

Business Model and Mapping Layer Syntax


ISDESCENDANT(logical_dimension, member_identifier [, distance])


Example “Hardcoded” distance = 1:


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


Example “Hardcoded” distance = 2:


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


Example “SessionVariable”:


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


image

image

Till Next Time

Sunday, September 11, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 3

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR.

ISCHILD

From the documentation:

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

Presentation Layer Syntax:

ISCHILD(pc_presentation_hierarchy, member_identifier)

Example “hardcoded”:

Case When ISCHILD("Sales Person"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END


Example “Session Variable”:

Case When ISCHILD("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser) ) Then 'YES' else 'NO' END


Business Model and Mapping Layer Syntax:


ISCHILD(logical_dimension, member_identifier)


Example “hardcoded” :

Case When ISCHILD("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  ISCHILD("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser")))


image

Till Next Time

Saturday, September 10, 2011

OBIEE Hierarchy Navigation Functions {HNF} Part 2

In Part 1 we did ISPARENT. ISPARENT is basically the ISANCHESTOR function with a distance of 1.

ISANCHESTOR

From the documentation:

The ISANCESTOR function enables you to find the ancestors of a member of a parent-child hierarchy, either all the ancestors of a member, or the ancestors at a specified hierarchical distance from the member.

Presentation Layer Syntax

ISANCESTOR(pc_presentation_hierarchy, member_identifier [, distance] )

Example “hardcoded” , no distance:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21' ) Then 'YES' else 'NO' END


This will show all anchestors!

Example “hardcoded” , distance = 2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21',2 ) Then 'YES' else 'NO' END


Example “Session Variable” , distance =2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser),2 ) Then 'YES' else 'NO' END


Business Model and Mapping Layer Syntax


ISANCESTOR(logical_dimension, member_identifier [, distance])


Example “hardcoded”, distance =1:


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


Example “hardcoded”, distance =2:


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


Example “SessionVariable”:


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


image

image

Till Next Time

Friday, September 9, 2011

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. image

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")))

image image

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

Till Next Time

Friday, July 8, 2011

OBIEE Developing a Parent-Child hierarchy

Just some strange behaviour I noticed OBIEE doesn’t like me developing a Parent-Child hierarchy in online modus in 11.1.1.5. It seems to miss some settings during a consistency check. When I did it in off-line modus the consistency check showed the errors.

Till Next Time

Monday, July 27, 2009

OBIEE Moving period to date aggregations

When using the OBIEE todate functionality the BI-server starts at the first entry next higher dimensional level. => TODATE(FACT, TIME.MONTH) means that it will start aggregating from the first month it will find on the TIME.YEAR level.

Recently on of out customers wanted a moving todate column, Starting 21 days ago and going on for the next 7 days after CURRENT_DATE. We couldn’t use a MSUM since we didn’t have an entry for each date and the total reporting period was greater then the aggregation period.

We fixed by creating a extra column on the calendar view on the database indentifying the period.

CASE WHEN DAY_DATE <SYSDATE –21 then ‘00-PREVIOUS’ ELSE CASE WHEN DAY_DATE > SYSDATE + 7 THEN ‘02-NEXT’ ELSE ‘01-CURRENT’ END END AS P21MINT7PLUSPERIOD

This will give ‘00-PREVIOUS’, ‘01-CURRENT’ or ‘02-NEXT’ for P21MINT7PLUSPERIOD. It’s important that you created this in the physical layer. The TODATE function doesn’t like dynamical column in the Business Model Layer.

Next when created an alternative hierarchy: TIME.TOTAL => TIME.P21MINT7PLUSPERIOD => TIME.DAY. Now we could use the TODATE function like TODATE(FACT, P21MINT7PLUSPERIOD) Which gives us a daily moving period.

Till Next Time

Friday, March 6, 2009

OBIEE identifying the hierarchy level

On the OTN forum I was asked how you can identify the hierarchy level which the user is currently on (Did he select Year, Quarter,Month etc?).

One way of doing this is "using" the aggregate awareness availability of a column. First you create a "dummy" aggregate view for each hierarchy:

  • year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
  • quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date
  • etc.

These tables you physically join to your dimension:

image

Update 2010/06/28:

Add Dummy joins to your other dimensions:

image 

Set the expression to 1=1image

In your fact table you add an extra column named dim_date_level:

image

Next add the sources to your fact table:

image

Add an inner join on your core fact table to your original dimension:

image

On each level source map to the column:

image

and set the aggregation content level:

image

set the aggregation level for the level column to MIN:

image

Test your Report:

image

image

image

image

Be sure to check all your dimension levels!

Till Next Time