Modeling for HR data

[petperson]

We are implementing HR data into the DW - currently in the conceptual modeling stage. How do you handle employee history data? Would like to hear some thoughts/advice on modeling (star schema). Employee history appears to be a challenge. Any input appreciated!

 

[johnherman]

The dimensions for employee should be Type-2 slowly changing dimensions. If this does not make sense, let me know and I will write a FAQ about slowly changing dimensions. Those dimensions should be:

Position - the employee's job and perhaps phone number, e-mail address, effective date, office location, supervisor, etc, perhaps with performance appraisals attached as further details.

Skills - degrees, courses, and other skills possessed by the employee and the date "awarded".

Personal - the employee's home address and phone, perhaps SSN, date of birth, marital status, spouse's name, home e-mail, etc.

Payroll - the employee's salary, effective date, deductions, etc.

There may be more. I have never built a HR system. But see also thread669-890569


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[Elsebet]

Chapter 8 of Kimball's "The Data Warehouse Toolkit" covers modeling HR Management data. Basically he suggests a Type 2 SCD (as johnherman said above) for the Employee Dimension (Attributes), an Employee Transaction Fact Table, and Date/Time/Transaction Type Dimension tables.

 

[888aaa]

Hi Every one,

I am trying to design a marketing data mart for mortgage industry. Does any body done this kind of work earlier, if so please tell me what are the dimensions and facts and their attributes.

any help is really appreciated,

Thanks in advance,
Ram

 

[johnherman]

You will probably have better results if you start a new thread. This thread's subject is HR, not Mktg.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[blom0344]

About a year ago we were asked to create better HR mangement reports, based on both relational data and Notes applications. The Notes applications were storing data on absence like illness, holidays and on periodic evaluations.

We created an ODS (operational data storage) for the non relational data and use Notes scripts to write to the ODS database (DB2). The ODS tables are then used to create facttables on holidays,illness and evaluations.

To be able to correctly calculate illness percentages on company, division and departemental level we decided to create monthly snapshots on the employee-data. This leads to an uncommon dimensiontable, cause all monthly snapshots beside the active period are just there for calculation reasons. (Size of departents,companies etc)

The database model is unsuited for casual users, but we have managed to create some very smart reports by using multiple dataproviders within Business Objects.

A typical illness analysis stores:

1. illness-percentage on all levels, absence frequency, estimated loss in terms of salaries, percentages for all individual months.
2. Distribution of absence-type (short,medium,long)
3. Information on long-termed absentees

Though the reports look very smart, the database-model is heavily compromised, which is further proof of how tricky this HR field really is.....

Ties Blom
Information analyst
tbl@shimano-eu.com