Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 29

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 32

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::load() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 161

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 138

Strict Standards: Non-static method JRequest::clean() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 33

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 463

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 464

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 465

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 466

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 467

Strict Standards: Non-static method JRequest::_cleanArray() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/environment/request.php on line 468

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 35

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 38

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 39

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::load() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 161

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 138

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 46

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 47

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 50

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 53

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::import() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 54

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/loader.php on line 71

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 57

Strict Standards: Non-static method JLoader::register() should not be called statically in /home/content/19/13966119/html/adivanew/libraries/joomla/import.php on line 58

Warning: session_start(): Cannot send session cookie - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/libraries/joomla/session/session.php on line 423

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/libraries/joomla/session/session.php on line 423

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/libraries/joomla/session/session.php on line 426

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/templates/ja_kyanite_ii/libs/ja.template.helper.php on line 119

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/templates/ja_kyanite_ii/libs/mobile_device_detect.php on line 113

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/templates/ja_kyanite_ii/libs/mobile_device_detect.php on line 114

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/templates/ja_kyanite_ii/libs/mobile_device_detect.php on line 113

Warning: Cannot modify header information - headers already sent by (output started at /home/content/19/13966119/html/adivanew/libraries/joomla/import.php:29) in /home/content/19/13966119/html/adivanew/templates/ja_kyanite_ii/libs/mobile_device_detect.php on line 114
OBIEE 11g New features : Working with Hierarchical Table
You are here: Blog OBIEE OBIEE 11g New features : Working with Hierarchical Table

Warning: Creating default object from empty value in /home/content/19/13966119/html/adivanew/components/com_k2/models/item.php on line 226

Warning: Creating default object from empty value in /home/content/19/13966119/html/adivanew/components/com_k2/models/item.php on line 614

OBIEE 11g New features : Working with Hierarchical Table

Written by  Sourabh Kumar
Rate this item
(15 votes)

Hierarchical table feature is a new addition to OBIEE 11g.
A hierarchy can be created when a parent child table exists. Consider an Employee and manager table with the following hierarchy.
 

To create an hierarchy out of this parent child table we follow the steps as detailed below.

Step-1

 

We create a table in the warehouse schema with the following script:

CREATE TABLE XW_EMPL_D(EMPL_ID NUMBER,EMPL_NAME VARCHAR2(100),MANAGER_ID NUMBER,SALARY NUMBER);

INSERT INTO XW_EMPL_D VALUES(1,'Andy',NULL,25000);

INSERT INTO XW_EMPL_D VALUES(2,'Raj',1,18550);

INSERT INTO XW_EMPL_D VALUES(3,'Kumar',1,19000);

INSERT INTO XW_EMPL_D VALUES(4,'Gomez',3,16000);

INSERT INTO XW_EMPL_D VALUES(5,'Puyol',3,15550);

INSERT INTO XW_EMPL_D VALUES(6,'Ronald',5,15875);

INSERT INTO XW_EMPL_D VALUES(8,'Borges',6,8000);

INSERT INTO XW_EMPL_D VALUES(9,'Mark',6,9500);

Step-2

 Import the table XW_EMPL_D and create an alias in physical layer of OBIEE  with the name Hier_Employee as shown in the figure above.

Step-3

 
Create a new Business model called ValueHierarchy. 

Step-4

 Drag the physical table Hier_Employees table twice and rename one as Hier_Employees_Salary

Step-5

Define 1:N relationship between Hier_Employees and Hier_Employees_Salary as shown above in BMM only.
 

Step-6

Now create a Logical Parent-Child Dimension from Hier_Employees by right clicking on it.

Step-7

Verify that Member Key and Parent Key are EMPL_ID and Manager_ID respectively.

Step-8

Step-9

Step-10

Step-11

Provide name of the hierarchy table and data source/schema details and click next.
 

Step-12

The DML and DDL script for the hierarchy table can be viewed by clicking the ViewScript Button. Encircled.  Say Finish.

 These scripts need to be executed back in the Datawarehouse schema so that the hierarchical stable is created and populated.

DDL:
CREATE TABLE HIERARCHY_TABLE ( MEMBER_KEY DOUBLE PRECISION, ANCESTOR_KEY DOUBLE PRECISION, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) )
 

declare

v_max_depth integer;

 v_stmt varchar2(32000);

 i integer;

 begin

 select max(level) into v_max_depth

 from XW_EMPL_D

 connect by prior EMPL_ID=MANAGER_ID

 start with MANAGER_ID is null;

 v_stmt := 'insert into HIERARCHY_TABLE (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10)

 || 'select EMPL_ID as member_key, null, null, 0 from XW_EMPL_D where MANAGER_ID is null' || chr(10)

 || 'union all' || chr(10)

 || 'select' || chr(10)

 || '  member_key,' || chr(10)

 || '  replace(replace(ancestor_key, ''\p'', ''|''), ''\'', ''\'') as ancestor_key,' || chr(10)

 || '  case when depth is null then 0' || chr(10)

 || '  else max(depth) over (partition by member_key) - depth + 1' || chr(10)

 || '  end as distance,' || chr(10)

 || '  is_leaf' || chr(10)

 || 'from' || chr(10)

 || '(' || chr(10)

 || '  select' || chr(10)

 || '    member_key,' || chr(10)

 || '    depth,' || chr(10)

 || '    case' || chr(10)

 || '      when depth is null then '''' || member_key' || chr(10)

 || '      when instr(hier_path, ''|'', 1, depth + 1) = 0 then null' || chr(10)

 || '      else substr(hier_path, instr(hier_path, ''|'', 1, depth) + 1, instr(hier_path, ''|'', 1, depth + 1) - instr(hier_path, ''|'', 1, depth) - 1)' || chr(10)

 || '    end ancestor_key,' || chr(10)

 || '    is_leaf' || chr(10)

 || '  from' || chr(10)

 || '    (' || chr(10)

 || '      select EMPL_ID as member_key, MANAGER_ID as ancestor_key, sys_connect_by_path(replace(replace(EMPL_ID, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10)

 || '        case when EMPL_ID in (select MANAGER_ID from XW_EMPL_D ) then 0 else 1 end as IS_LEAF' || chr(10)

 || '      from XW_EMPL_D ' || chr(10)

 || '      connect by prior EMPL_ID = MANAGER_ID ' || chr(10)

 || '      start with MANAGER_ID is null' || chr(10)

 || '    ),' || chr(10)

 || '    (' || chr(10)

 || '      select null as depth from dual' || chr(10);

 for i in 1..v_max_depth - 1 loop

 v_stmt := v_stmt || '      union all select ' || i || ' from dual' || chr(10);

 end loop;

 v_stmt := v_stmt || '    )' || chr(10)

 || ')' || chr(10)

 || 'where ancestor_key is not null' || chr(10);

 execute immediate v_stmt;

 end;

 /

Step-13

This is the final review window where the Member Key, Parent Key, Relationship Distance and Leaf Node Identifier are shown and it is pre-populated. Say OK.
 

Step-14

Verify that the dimension is created in the BMM layer

Step-15

Verify that a Hierarchy table is also created in the physical layer. Note the icon is different for this hierarchical table.
 

Step-16


Drag the 2 tables to the presentation Layer.

Step-17

Create a report using thehierarchy column along with Salary and Employee name. The report looks like the one above. One can browse the hierarchy tree using this feature.
If you enjoyed reading this, pls do rate the article.
 

Read 20597 times

1 comment

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.