Archive for the Dimensional Modelling category.

Money

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.

Read the rest of this entry »

Dimensional Modelling - Facts

posted by Ross (Admin)


Creative Commons License 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

Read the rest of this entry »


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.
Read the rest of this entry »

subscribe to our news feeds
Already a member, login below or join us here
USERNAME
PASSWORD