none
Design question for fact table RRS feed

  • Question

  • I have 4 dimensions:
    dimCustomer, dimEmployee, dimTask, dimDate

    and one fact table to hold time logged by employees:
    factLoggedTime
    --------------------
    employee_key
    customer_key
    task_key
    date_key
    code_type
    hours_logged

    The dimTask dimension has descriptions of the various types of tasks that can be logged.  For example, general support.  Each task has a default code type, for example, the general support task could have a default code type of 'help'.  When an employee logs time, they can change the code type from the default if it makes sense; it could be related to a bug in software, so the employee by change the code from 'help' to 'bug'.

    My question is how to utilize the *logged* code type?

    The ways I have thought of attacking this problem:

    1. create a dimension table of the fact table with these types of attributes in it.  It could just be created by using a named query to select the proper columns.
    2. Hard code a column for each code type into the fact table (as a named query), so for example: case code_type when 'help' then hours_logged else 0 end as help_hours.  This is not something I would like to pursue... but the idea did pop into my head.
    3. Modify the task dimension to include every possible combination and have the fact table link to it via a surrogate key.  I think this option is the best option, but I have a feeling that it would be a pain to maintain it.
    Does anyone have any ideas on how to approach this?
    Tuesday, March 3, 2009 7:02 PM

Answers

  • Hi  Justin

    I think I would dimension by the code_type. You could then analyse your data by that - for example look at trends in time logged to general support month on month.

    One suggestion though: do your users select this jon type from a UI? If so I would create a dimension from that and link it to the fact table rather than doing it in the DSV.

    HTH

    Paul
    • Marked as answer by JustinRush Tuesday, March 3, 2009 9:06 PM
    Tuesday, March 3, 2009 7:48 PM

All replies

  • Hi  Justin

    I think I would dimension by the code_type. You could then analyse your data by that - for example look at trends in time logged to general support month on month.

    One suggestion though: do your users select this jon type from a UI? If so I would create a dimension from that and link it to the fact table rather than doing it in the DSV.

    HTH

    Paul
    • Marked as answer by JustinRush Tuesday, March 3, 2009 9:06 PM
    Tuesday, March 3, 2009 7:48 PM
  • I am not sure why I didn't think of doing that... thanks for pointing it out.
    Tuesday, March 3, 2009 9:06 PM