Ross Goodman

Category: Dimensional Modelling

  • Blogging Whilst Employed – Beware!

    october05.0103
    Creative Commons License photo credit: eshm
    I was having a flick through my employment contract and I found the following clause:

    13.2
    You agree that during the course of this contract and after the termination of the contract for whatever reason without limit in point in time you will keep in confidence and shall not disclose to any person unless authorised to do so with the prior written consent of the Company the following:-
    13.2.1 Any consultancy know-how, methods, tools, techniques or intellectual capital.

    I realised that some of my blog entries or tweets could fall foul of this clause so I asked for written permission to continue blogging generic “work related” content such as Business Intelligence, Data Warehousing, Data Modelling and Business Objects.
    Rather than granting this and leveraging the fairly good google rankings that my posts seem to get I was asked to remove all work related posts.
    Business Objects Best Practice (higher ranking than BusinessObjects.com)
    Business Objects New Features (Page One)
    Dimensional Modelling (Ralph Kimball ?)
    As such I have password protected all of my work related posts.
    If you are struggling to find content there are a variety of searches that could turn up interesting content.
    Alternatively you can go to my company site, contact details can be found here or here.

  • A Year In Retrospect

    Just a quick summary of my on-line presence this year.

    Twitter

    I have invested a lot of content to Twitter this year.
    This is primarily due to the convenience of the platform racking up almost 1200 updates in the year here.

    Flickr / Photography

    I can’t find a way to get stats for the year so here are my top five photos:
    tennis
    Car Crash Rescue 1
    Lightning 5
    Kelvingrove Heads 1
    Car Crash Rescue 2

    Web Site / Blog

    By far the most popular post on the site was the photos page here.
    Second most popular page is the “About Me” page here.
    As for the blog entries themselves they rank at:
    1 – Business Objects XI 3.0 New Features – Conditional Prompts
    2 – Crystal Xcelsius – Filtered Rows From A Filter Component
    3 – Modelling Change In Your Dimensions
    4 – Business Objects XI 3.0 New Features – Change Data Tracking
    5 – Dimensional Modelling – Facts
    As you can see here the site only really started getting traffic last January so that gives me 17300 page views.
    sitestats

    Personal Stuff

    Obviously the biggest news of the year was the passing of my daughter Emma.
    Celebrating the Life of Emma Berry Goodman
    And the year ends on another low point with the passing of another member of my extended family.
    George Miller – Footballer | 1939 – 2008

  • Oracle Design Tool – SQL Developer Data Modelling

    On my most recent project I am again designing another data warehouse solution.

    My tool of choice for data modelling has always been CA’s ERwin DataModeler http://www.ca.com/us/products/product.aspx?ID=260

    This is great, but we run into problems when trying to reverse engineer an existing database as this means that either my laptop has to go onto the customers network; that’s never going to happen. or the customer has to buy a copy of ERWin to use on their desktop.
    My most recent customer is a dedicated Oracle customer using Database, Data Guard, RAC, Forms, OBIEE, Answers and on and on.

    The obvious choice would be Oracle Designer. I have not used this in a few years but the last time it took me two days to get it installed and configured.

    A quick search pointed me to an early adopters release of SQL Developer Data Modelling, you can find it here : http://www.oracle.com/technology/products/database/sql_developer/files/releasenotes525.htm
    Here are a few of my thoughts:

    (more…)

  • Modelling Change In Your Dimensions

    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.

    (more…)

  • Dimensional Modelling – Facts


    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

    (more…)

  • 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.
    (more…)