HR Dimensional Model

View previous topic View next topic Go down

HR Dimensional Model

Post  xXNetRavenXx on Fri Jun 07, 2013 6:40 pm

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?


xXNetRavenXx

Posts : 4
Join date : 2013-06-07
Age : 33
Location : Bend, Oregon

View user profile

Back to top Go down

Figuring out the details

Post  xXNetRavenXx on Mon Jun 10, 2013 7:23 pm

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.

xXNetRavenXx

Posts : 4
Join date : 2013-06-07
Age : 33
Location : Bend, Oregon

View user profile

Back to top Go down

Re: HR Dimensional Model

Post  LAndrews on Mon Jun 10, 2013 8:03 pm

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Re: HR Dimensional Model

Post  xXNetRavenXx on Tue Jun 11, 2013 12:16 pm

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.

xXNetRavenXx

Posts : 4
Join date : 2013-06-07
Age : 33
Location : Bend, Oregon

View user profile

Back to top Go down

Re: HR Dimensional Model

Post  layinka on Fri Jun 21, 2013 8:49 am

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

layinka

Posts : 2
Join date : 2013-06-21

View user profile

Back to top Go down

Re: HR Dimensional Model

Post  xXNetRavenXx on Fri Jun 21, 2013 5:57 pm

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

xXNetRavenXx

Posts : 4
Join date : 2013-06-07
Age : 33
Location : Bend, Oregon

View user profile

Back to top Go down

Re: HR Dimensional Model

Post  layinka on Sat Jun 22, 2013 3:12 am

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

layinka

Posts : 2
Join date : 2013-06-21

View user profile

Back to top Go down

Re: HR Dimensional Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum