RE: Database hierarchies

Hi Vincent,

Thanks for providing me the links. I could access the first link but was = not able to open second link. I would appreciate if you can send second = link again.

Thanks
Meenakshi

-----Original Message-----
From: Chazhoor, Vincent [mailto:Vincent.Chazhoor_at_CIBC.com] Sent: Wednesday, January 12, 2005 10:05 AM To: Aggarwal, Meenakshi; oracle-l_at_freelists.org Subject: RE: Database hierarchies

There are two other approaches=20
=20

1. Kimballs helper table apprach. Please see the details at
=20

http://www.google.ca/search?q=3Dcache:s8k19JsELzIJ:www.dbmsmag.com/9809d0= 5.htm
l+kimball+helper+tables+hierarchies+fact+table&hl=3Den
=20

     This will work even if you have multiple hierarchies. If one = employee
has one manager the Oracle parent_id apprach will work. But if an = employee
is reporting to one manager for administrative purposes and another = managers
(project manager) for work related tasks then the apprach of using = CONNECT
BY MAY not work. But kimball's helper table apprach will work. This is mainly used in reporting/data warehouse environments. But can be used = for
OLTP also.=20
The sample code forOracle databaseis available at kimball's website.
=20

2. Joe celko's left number right number appraoch.

    This was introduced by Joe in his book 'SQL for smarties'. You can = get
the details at=20
=20

http://www.google.ca/search?q=3Dcache:tl_CTRYtCQYJ:www.intelligententerpr= ise.c
om/001020/celko.shtml+Joe+left+number+right+number+hierarchies&hl=3Den
=20

This is a very flexible apprach and it will give good performance. The = only
issue is generating the left and right numbers are very complex. There = are
some sample code on the web for Sql serve. May be you need to modify it = for
oracle if this is the apprach that you want to go.

Thanks,

Vincent

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Aggarwal, Meenakshi Sent: Wednesday, January 12, 2005 8:46 AM To: oracle-l_at_freelists.org
Subject: Database hierarchies

Hi,

I have parent-child-child-child-.... (multiple hierarchies). What is the best approach to create tables in data warehouse, when user have no = clarity
on levels of hierarchies and I need to consider future increase in = levels of
hierarchies, so I need to plan a structure that can absorb future hierarchies.

I saw one example on asktom.oracle.com. Does anybody have better way...

select rpad('*',2*level,'*')||ename EmpName, dname,   2 sys_connect_by_path( ename, '/' ) cbp   3 from emp
  4 start with mgr is null
  5 connect by prior empno =3D mgr
  6 order SIBLINGS by ename
  7 /

EMPNAME                        DNAME          CBP
------------------------------ -------------- =
------------------------------
**KING                         ACCOUNTING     /KING
****BLAKE                      SALES          /KING/BLAKE
******ALLEN                    SALES          /KING/BLAKE/ALLEN
******JAMES                    SALES          /KING/BLAKE/JAMES
******MARTIN                   SALES          /KING/BLAKE/MARTIN
******TURNER                   SALES          /KING/BLAKE/TURNER
******WARD                     SALES          /KING/BLAKE/WARD
****CLARK                      ACCOUNTING     /KING/CLARK
******MILLER                   ACCOUNTING     /KING/CLARK/MILLER
****JONES                      RESEARCH       /KING/JONES
******FORD                     RESEARCH       /KING/JONES/FORD
********SMITH                  RESEARCH       /KING/JONES/FORD/SMITH
******SCOTT                    RESEARCH       /KING/JONES/SCOTT
********ADAMS                  RESEARCH       /KING/JONES/SCOTT/ADAMS

Thanks

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l