Thursday, May 1, 2008

Two Helpful New HR Tables Available

Two new tables are available to HR data warehouse users: PS_ASU_HR158_VW and PS_ASU_CUR_JOB_VW. These "views" will make query development easier since they eliminate the need to do effective date related subqueries. The new tables only contain the most current record. Past and future effective dated records have been filtered out.

The PS_ASU_HR158_VW was inspired by the legacy data warehouse's tblHR158, hence the name. The goal was to provide current job information including position funding sources. Based on the PS_JOB table, the view pulls in data from ten other tables including the current position distribution, job code salary ranges, tenure information, and vacation plan information.

Some of the column names require some explanation:

  • DEPTID2 is the home department which should be the department of an employee's primary job.
  • DEPTID_LD is the description of the home department.
  • DESCR is the title associated with the job code.
  • DEPTID is the department associated with the job.
  • DESCR2 is the job department's description
  • Amount is Annual Rate * position distribution percentage

We plan to create a view on top of this view that will have more descriptive column names, but do not want to do this until you all have had a chance to use the existing view and point out all its shortcomings. So please, let us know if you see data that looks incorrect.

PS_ASU_CUR_JOB_VW was created along the way as a convenience while working on the PS_ASU_HR158_VW table. It is simply the PS_JOB table filtered to only the current record.

Kudos to Mark Monninger for developing these helpful new tables and to Robin Gonzalez for defining the requirements.

No comments: