Dimensionally Modelling A Recursive Hierarchy


Creative Commons License photo credit: gadl

I was recently asked how to include a recursive hierarchy into a dimensional model.

What do I mean by a recursive hierarchy? This is when an entity (a table) relates to it’s self. Take the following example from an Oracle database:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 ‘SMITH’ ‘CLERK’ 7902 ’17-DEC-1980′ 800 NULL 20
7499 ‘ALLEN’ ‘SALESMAN’ 7698 ’20-FEB-1981′ 1600 300 30
7521 ‘WARD’ ‘SALESMAN’ 7698 ’22-FEB-1981′ 1250 500 30
7566 ‘JONES’ ‘MANAGER’ 7839 ‘2-APR-1981’ 2975 NULL 20
7654 ‘MARTIN’ ‘SALESMAN’ 7698 ’28-SEP-1981′ 1250 1400 30
7698 ‘BLAKE’ ‘MANAGER’ 7839 ‘1-MAY-1981’ 2850 NULL 30
7782 ‘CLARK’ ‘MANAGER’ 7839 ‘9-JUN-1981’ 2450 NULL 10
7788 ‘SCOTT’ ‘ANALYST’ 7566 ’09-DEC-1982′ 3000 NULL 20
7839 ‘KING’ ‘PRESIDENT’ NULL ’17-NOV-1981′ 5000 NULL 10
7844 ‘TURNER’ ‘SALESMAN’ 7698 ‘8-SEP-1981’ 1500 0 30
7876 ‘ADAMS’ ‘CLERK’ 7788 ’12-JAN-1983′ 1100 NULL 20
7900 ‘JAMES’ ‘CLERK’ 7698 ‘3-DEC-1981’ 950 NULL 30
7902 ‘FORD’ ‘ANALYST’ 7566 ‘3-DEC-1981’ 3000 NULL 20
7934 ‘MILLER’ ‘CLERK’ 7782 ’23-JAN-1982′ 1300 NULL 10

From this we can see that there is a recursive hierarchy within this table between the empno column and the mgr column, the mgr column shows the employee that is the manager for each row.
Continue reading Dimensionally Modelling A Recursive Hierarchy