Recursive – Ross Goodman

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.