HR Dimensional Model

[Guest]

I am working on a model to satisfy several human resources business processes and I had a few questions and wanted someone to take a look at my model and tear it apart/give some pointers since my dimensional modeling experience is pretty low and everything I have learned I picked up from lecture videos and Ralph Kimballs books.

The purpose of this dimensional model is to provide analytical capabilities around employee workload and turnover to support company scorecard measures and HR dashboard needs. The business processes that are being measured by this model as stated by HR and derived by the departmental scorecards are:
• Turnover by department, title/specialty, years of service.
• PTO usage by employee, department.

I have drawn from the Human Resources Management in The Data Warehouse Toolkit and am using a type 2 slowly changing employee dimension to satisfy these needs and any perceived ad-hoc queries HR might have pertaining to employee information.

One question is the does the level of granularity in the fact table directly affect the transaction level grain in the employee dimension? What is a reasonable level of granularity for this fact table, during meetings and discussions at my company I am being pushed to a finer grain detail but I am constantly suggesting a level that is at least weekly because of their actual reporting needs.

My second question is how does the Employee Transaction Expiration Date/Employee Transaction Expiration Time work within the employee dimension?

[Guest]

After reading Ralph Kimball's article Human Resources Data Mart I found some good detailed information and made some additions to my Employee transaction dimension. I have figured out how the transaction expiration date works

This date/time is exactly equal to the date/time of the next transaction to occur on this employee record, whenever that may be. In this way, these two date/times in each record define a span of time during which the employee description is exactly correct.

That way you can query a specific period in time and see exactly what that employees stats were.

I still am working on explaining how the granularity of the fact table in this model should be monthly, the transaction level of granularity of the employee transaction dimension is going to make slicing and dicing of the fact table more than sufficient. I have a hard time believing that anyone would attempt to have a report specifying how much vacation was taken on a specific date as opposed to between two date ranges.

[LAndrews]

Some things to think about.

The "HR Fact" is really a snapshot fact, so picking a standard time grain is normal. I think the Kimball example was monthly, but I've also used weekly due to dashboard requirements.

Some of the metrics in HR fact would come from counts in the Employee Dimension (e.g. HIRE COUNT), others will come from other granular fact tables. (PTO_transactions, OT_Transactions etc).

Once you get it figured out, you'll find its an extremely powerfull model. The majority of your HR reporting will be against the employee details.

[Guest]

Thank you for your reply, that really sheds some light on what the fact table will look like in the end. Basically every week, or month when the snapshot fact is populated it pulls the entire employee population and each count (I view them as flags) and measure is set for summarization and aggregation.

I don't entirely understand what you mean by deriving the PTO and OT from other granular fact tables. I suppose in my head I assumed that would be handled in the ETL process, but I can see the error in that. I could see making PTO_transactions and OT_Transactions as mini dimensions with a relationship to the customer dimension and HR snapshot fact. If we were to make those as additional fact tables I guess I can't see how the relationship will flow back in this model.

[layinka]

Hi
please i also am working on something similar, please can you explain to me what the employee count and the newhirecount,transfercount columns are on the fact table. do they respresent the number of employees under the current person(the one referenced by the employee traxn key) employed etc or what?
Thanks a  lot

[Guest]

The fact table is a snapshot of the every employee profile at that specific moment in time. The counts are all flags associated with each employee data (if new hire then 1 else 0), then you derive counts and aggregates by summarizing that data. 


Example:

Employee TransKey	Department Key	Date Key	Salary Paid	PTO Accrued	PTO Taken	PTO Balance	Overtime Paid	Overtime Hours	Employee Count	New Hire Count	Transfer Count	Promotion Count
256	10	20130526	4000	5.58	0	35.58	0	0	1	0	0	1
257	10	20130526	5000	6.88	0	36.58	0	0	1	0	0	0
258	10	20130526	4050	5	0	37.58	0	0	1	0	0	0
259	10	20130526	3500	5.6	0	38.58	0	0	1	0	0	0
260	10	20130526	3000	6.58	0	39.58	0	0	1	0	0	0
261	10	20130526	4000	5.25	0	40.58	0	0	1	0	0	0
262	10	20130526	4050	5.5	4	41.58	0	0	1	0	0	0
263	10	20130526	3050	6.8	0	42.58	0	0	1	0	0	0

Further reading: Chapter 8 of Ralph Kimball's Data Warehouse Toolkit: Complete Guide to Dimensional Modeling. Human Resources Data Marts 


All the facts in the fact table are additive across all the dimensions except for the facts labeled as balances. These balances, like all balances, are semiadditive and must be averaged across the time dimension after adding across the other dimensions. The fact table is also needed to present additive totals like salary earned and vacation days taken. -Kimball

[layinka]

Thanks a lot, i guess it was the count in the name that was confusing, if they had been named flag, i will have understood

Thanks

[Sponsored content]