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@xxxxxxxx]
Sent: Wednesday, January 12, 2005 10:05 AM
To: Aggarwal, Meenakshi; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Aggarwal, Meenakshi
Sent: Wednesday, January 12, 2005 8:46 AM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: