You use dimension tables to hold the descriptive attributes that you want to use to analyse your measures.
This is fine but what do you do when this information changes? There are three common ways that you can handle changes in your dimensional model.
photo credit: Felipe Morin
I am often asked by my colleagues to explain dimensional modelling concepts to them. I will try to capture come on the concepts here. I am going to focus on Facts.
Specifically, types of facts: Accumulating, Factless, Transactions, Snapshots, Additive, SemiAdditive and Non Additive
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.
Read the rest of this entry »
News