Analytics and Reporting - Human Resources Data Warehouse Release 38

Here are the upcoming changes to HR reporting going into production on May 19, 2014 for Release 38. 

New views enable schools/departments to see departmental historical compensation whether or not the employee is currently in your DPL (department permission list)

Four new "_DEPT_SV" views have been created and made available to users with the "HDW^HR^TUB^Reports User role: OHR_JOB_ACTN_ALL_DEPT_SV, OHR_JOB_ACTN_DEPT_SV, OHR_JOB_HIST_ALL_DEPT_SV, OHR_JOB_HIST_DEPT_SV. These views have alternate department-based row-level security.  They are identical in what columns they make available as their counterparts without the “DEPT_SV” (i.e., OHR_JOB_ACTN_SV and OHR_JOB_ACTN_DEPT_SV  have the same columns available).

The new OHR_JOB_HIST_DEPT_SV view enables you to get a snapshot of your current permission list of departments as of a point in time with compensation information. To get a historical snapshot of all jobs that were active in your current permission list of department ID’s as of a past point in time, in addition to doing max effective dating as of the snapshot date, the end_eff_date attribute (the last day this job row was in effect) should also be used to determine whether this particular row was still in effect as of the snapshot date. ( For job rows that are not superceded by another job row, the value of end_eff_date is NULL which is taken care of by the NVL function below.) For example, the following query will give a count of jobs in your current permission list of department ID’s as of December 31, 2013.

select count(*) from sysadm.ohr_job_hist_dept_sv jhd
where 1=1
and jhd.eff_date = (select max(jhd2.eff_date) from sysadm.ohr_job_hist_dept_sv jhd2
where jhd2.empl_id = jhd.empl_id
and jhd2.empl_rcd = jhd.empl_rcd
and jhd2.eff_date <= '31-DEC-2013')
and nvl(jhd.end_eff_date, sysdate) >= '31-DEC-2013'
and active_flag = 'Y'

NOTE: To get “person” information, join to OHR_PERS_CURR_NOCOMP_V

The new _DEPT_SV views restrict rows available for querying to job records when those jobs were in a department the querier has permission to see. The standard _SV versions restrict rows instead to all job records where today's version of the job is right now in a department the querier has permission to see. So, if a job today is currently in one of your departments, in the original _SV views, you can see that job across all its history, even when it wasn't in your department. Currently, you can't see any records for jobs no longer in your department, even if they were once in your department. In the _SV views.  Once a job has gone over to another department, only the query writers for that department can see its whole history. These new _DEPT_SV views enable a query writer to see the history of jobs and job actions that were once in that querier's available department list, but only the records back in time when the job was in one of their departments. These views slice across history to find jobs once or currently in your department, only showing the history while it was/is in your department.

Changed public views DEPT_R_V, SHR_PS_DEPT_TBL_V: new column added

The two basic reference views for HR department descriptions have a single new column, HU_OFFICIAL_NAME, which contains the official name of the department in a clean version without special characters (such as carats '^'). This is the name sent to IdM that appears in the Directory Listing.

Changed HR^TUB view IDMS_FLAGS_SV: new columns added

The existing IDMS_FLAGS_SV view of employee attributes, currently visible to query users with the "HDW^HR^TUB^Reports User" PeopleSoft role, has two new columns added to it. These are RET_ACTIVE_PRIOR_010114_FLAG and RET_PRH_ELIG_DATE, attributes related to the recent 2014 Post-Retirement Health changes.

Changed HR Analytics analyst-only DHR views: new columns added, minor column calculation changes, new dimension view

A few column additions have been made across all but one of the DHR star schema fact tables, and the Person and Job dimension views. There is a new dimension, Salary Plan Grade Ranges, for use with a new dimension key column added to the Demographics fact. See table below for full details:

Two columns visible in the DHR_TURNOVER_ALL_F_V view Turnover fact, FTE_SALARY_CHANGE_PCT and ANNUAL_SALARY_CHANGE_PCT, have had a minor correction in their calculation which will adjust the values in those columns for a small percentage of Turnover fact rows.

Please note: Be careful with use of the two reference columns added to the Demographics Costing fact: EMPL_ID and EMPL_RCD. Before using these to join back to an SHR or OHR view, take a look first to see if the employee or job attributes you wish to see or restrict your query on are present in one of the DHR dimensions associated with the fact by KEY. For example, as of this release, the Person dimension has EMPL_ID available. Performance and accuracy for dimension attributes is tested for and ensured; queries with joins from large fact tables to large OHR tables may perform poorly. Also, always join between a dimension and a fact on the KEY column they have in common, never on an attribute column. For example, never join the Person dimension to a fact table like Demographics Costing on EMPL_ID to EMPL_ID.

Specifically for Demographics Costing, be careful joining back to a particular Job History or Job Action record in the OHR tables using those two columns with the EFF_DATE column in the fact. The EFF_DATE given in the Costing fact is the effective date of the costing setup as of the given DW_AS_OF_DATE_KEY, not necessarily matching to all possible job effective dates.

Similarly note: Be careful with use of the four reference columns added to the Mobility and Turnover facts: EMPL_ID, EMPL_RCD, EFF_DATE, EFF_SEQ. Before using these to join back to an SHR or OHR view, take a look first to see if the employee or job attributes you wish to see or restrict your query on are present in one of the DHR dimensions associated with the fact by KEY.

New view versions of HR Analytics DHR Earnings views with alternate Department-based row-level security

With this release, we are able to make the HR Analytics Earnings star schema available to more analysts than just those with permissions to see all data university-wide regardless of department. Now some analysts, pending authorized requestor approval, can be granted the PeopleSoft role "HDW^HRA^DEPT^Restricted^Earns", giving them access to the Earnings and Earnings Distribution fact data via two new views; DHR_EARNINGS_F_DEPT_SV and DHR_EARNINGS_DIST_F_DEPT_SV. These views are identical in what columns they make available with the view version without "DEPT_SV"; for example, DHR_EARNINGS_F_V and DHR_EARNINGS_F_DEPT_SV both make the same columns available.

The difference with the new _DEPT_SV versions is that these new views restrict rows available for querying to records with Organization dimension keys that match the limited list of departments the querier has permission to see. A query writer limited to departments of a single school will only see earnings or earnings distribution rows with Organization dimension keys for that school. These views slice across history to show only earnings and earnings distributions associated with the analyst's department list.

Note: Data in the dimension views themselves is not restricted. If an analysts with a limited department list queries only against the Organization dimension, they will see records for all Organizations, as dimension reference data is not sensitive across departments.

New view OHR_CONTACT_V in new role "HDW^HR^CTR^Contact No Comp"

A new view OHR_CONTACT_V of employee contact information has been created, for use by a very limited audience to be identified by the new PeopleSoft role "HDW^HR^CTR^Contact No Comp".

If you have questions, please contact us via the HUIT Helpdesk at 495-7777 or ithelp@harvard.edu.

Thank you,
The Human Resources Analytics and Reporting and HUIT Enterprise Data Warehouse teams