1

I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like job_start, job_end, salary etc.

Firstly, I tried to draw a dimension for each data, but I consider whether it should be done like a connection between these dimensions each other?

Improve this question
6
  • 1
    If staff are the dimension, what is the fact? Are you interested in analyzing data about the staff themselves, or about other facts (projects, records of working hours) where the staff really are a filter condition?
    – Pondlife
    Commented Mar 20, 2013 at 16:50
  • The facts are just records of working hours, max values of salary.
    – daroPL
    Commented Mar 20, 2013 at 17:01
  • 2
    I still don't understand what your fact is, or what data you're trying to analyze. The best suggestion I can make is that you read Kimball's data warehousing book; chapter 8 is actually about modelling employee data.
    – Pondlife
    Commented Mar 20, 2013 at 18:47
  • 1
    @daroPL Typically the max(value) of a salary is not going to be stored as a granular fact. The salary snapshots when they change might be stored in a salary fact table or more likely in an employee position/role fact table.
    – Cade Roux
    Commented Mar 20, 2013 at 19:44
  • How do you travel along a staff dimension. I could see salary as a dimension. But I wouldnt expect such discrete uses to be very useful in terms of dimensions.
    – Chris Travers
    Commented Mar 21, 2013 at 6:17

1 Answer 1

Reset to default
6

Typically dimensional modelling starts by identifying facts and the dimensions about those facts. Most of those things you mention are dimension attributes.

Staff/employees alone and their place in an organization is probably going to be modeled with a factless fact table.

You may well have a supervisor/employee relationship, and this would be an instance of a factless fact table where the facts in that table are likely to be dates of supervision (these are really a lot like degenerate dimensions).

However, I expect you will have fact tables like hours worked etc which will use these dimensions as foreign keys.

It is very unusual to be modeling dimensions in isolation, as dimensional modeling is a very pragmatic type of approach which is very dependent upon the type of data you have as well as the types of analysis. The choice of how many dimension tables and which attributes go in which dimensions is very dependent upon data behavior and can sometimes be quite arbitrary.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.