The article is authored by Sanjay Rai, Service Delivery Manager

One of the pre-requisite of areporting solution is that it should be simple & easy to use. Also itshould hide the technical complexity of operational systems. It would be goodpractice if the terms used for reporting in HR Data Model have businessfriendly name like business title, status category, local category etc.Thisenables users to design their own ad-hoc query and reports using from HR datamodels. Also this reduced unnecessary support for IT team.

Some of users prefer reportingdirectly from source system. Usually creating report on transaction system isnteasily. There are number of limitations. Running reports on transaction systemwould unnecessary burden the system. Also reporting from transaction system isntuser friendly. Usually user faced difficulty in designing ad-hoc query or reporton transaction system as the table/column names are too technical. In such acase Reporting from BI tool is an advantage. There are number of BI toolsavailable in market as well as freewares.IT team can use these tools to createdata model for HR. The one disadvantage of reporting through BI tool is as mostof these system access data from data warehouse database and there is time lag.Unlike transaction system, reporting through datawarehouse involves some timelag.

Start designing HR data mart

One of key challenge is toidentify relevant HR reporting tables from Source system.

Normally complex ERP solutionshave thousands of database tables. Out of them tables relevant for reportingpurpose would be around 4-5% of total number of tables. When you start datamodeling for HR data marts, I would suggest focus on de-normalization. Bundled as much as possible the relatedinformation’s inside single table. For Instance for employee information likejob, personal, employment, nationality, educational qualification would be inseparate tables in operation system. In data warehouse, you can create singletable EMPLOYEE and stored all information inside it. Such approach wouldminimize the number of fact tables in data model and as a result data modelwould be simple and comprehensive. Such design of data models help in tuningcomplex reports in future.

As in other business domain, thereis need in Human Resource to do reporting for current as well as historical details. Historical detailsreporting in HR is normally done for compensation, performance, job grade, jobcode etc. I would suggest create separate fact tables for storing completeevolution/history of particular action. For Instance, an employee has 25 differentactions like grade change, pay rate change, department change etc.It is goodidea to store all related actions in separate fact tables. Create different fact tables for compensationevolution, grade evolution, review history, training details etc.This wouldreduce complexity in Data Model design as well as in extraction program.

Another challenge is to identify conform dimensions acrossdifferent  data models of HR. Normallyattributes which are common are cost center,location,jobcode,time etc.Acomplete hierarchy should be build for each of these conformed dimension. ForInstance for cost center confirmed dimension, other common attributes likeorganization, group, cost center manager manager, effective status etc can becombined in single hierarchy called Organization or Cost Center. Hierarchyenables users to easily drill-up and drill down the data in report. Also it iseasy to do reporting by different axis of conformed dimension. Similarlyhierarchy should be build for other conformed dimensions.

Data Structures

While building data structure forHR Data Models follow some standardnaming convention. For Instance prefix all data structure name with HR.Further segregation could be done for tables, procedures, packages, functions,triggers etc.

It is always good to follow somewhatsimilar database objects name as available in source system. For Instance if inSource system one of database object name is PS_PERSONAL_DATA, in datawarehouse it could be TR_PERSONAL_DATA_TBL. This helps in data debugging whenIT teams needs to verify data in data warehouse with data in source systems.

HR Key functional areas:

Tracking Census & Movement of Employees

One of key function of HR isreport & track on period ally   basisthe census in company as well as in different divisions of company. Censusreporting is important for tracking hiring, resignation, and turnover or attrition rate in the company.Normally census reporting is done historically i.e. census data are storedhistorically either by week or month. Census reporting enables seniormanagement to keep control on hiring in today recessionary situation. Also ithelps to identify current attrition rate for different division of the company.Depending on the needs of the organization, census reporting could be done indetails like headcount in activity (considering only Active employees),registered headcount (apart from Active it also considers employees on Leave),contract headcount etc.In some organization there is a need to have complexcensus reporting by attribute like running average, year to date etc.In bigcorporate having operations across multiple countries international mobility ofemployees is a common features. Every week hundreds of employee moves acrossdifferent division & locations of an organization. In HR, there is need totrack such movement of employees for different division of an organization.

Staffing

Another equally important HRfunction which needs to track is recruitment or staffing. To have businesscontinuity organization needs to hire or fill important openings in todayrecessionary business scenario .In big corporate house there is standardprocedures followed for any new hiring. A job requisition is raised and itpassed through cycle of validations/approved. Once an employee is hired againstjob requisition, the life cycle of job requisition comes to an end. Dependingupon the size of organizations, job requisition (job openings) would be multiples.Also there are internal or external job openings. Sometimes it takes months tohire an employees against job requisition, some job requisition didnt completethe life cycle, may be declined by senior manager. It is useful to trackimportant HR metrics like average timeto fill job requisition in an organization, average time taken by jobrequisition to open (a stage in life cycle of job requisition  at which employee would be hired).Also datamodel would gives pictures of total active job requisition ,inactive or closedjob requisition.

Training and Development

Training & Development is oneof core HR functions. In totally competitive world, it is necessary thatorganization regularly upgrade their employees skills. There could be multiplemethods used by an organization to train it employees like instructor led classtraining or computer based training. Considering the cost and feasibility toreuse again and again most of organizations are focusing more on e-learningtraining programs. There are different packages available in market today fore-learning package. In some specific manufacturing industries are specificcertification course for operator, who operator complex machines. An operatorneeds to regularly get certified on complex machines. Also Training data modelsenable IT team to consolidate different IT training systems data into singleplace. Use of Conformed dimension in Training data models enable users tocombine Training data with other HR statistics like census. For instance aTraining department users wants to find out which all division at specificlocation of an organization have total training hours less than mandatory training hours. Criticalindicators like training cost per divisions,number of certified internal trainers etc would be easily extracted from Trainingdata model.

Compensation and Benefits

Compensation and Benefits analysisis an important functions of HR. Normally at the beginning of each financialyear, Corporate allocated budget to each country HR to cater to pay ratechange, promotion, recognition and for other benefits. Corporate Compensationteams needs to track consumption ofbudget allocated to different countries. Usually in such tracking there arenumber of exclusion rules like employee impacted by international mobility arenot considered, new hire and rehire are excluded from such analysis. Usuallycommon currency mainly USD are used for such analysis.

Usage of Application

It is challenging to design &build technically & functionally rich HR data model, but its more important that end users use the applications. It had been seen in most of BIsolutions the usage of application isaround 50-60%.One of the reason for low usage of BI solution is that, inbig corporate access to BI applications are by default are given to everySenior Management employees. Normally these employees never had time or in somecase inclination to log-on to BI applications. To increase the usage of BIapplications, I would suggest the following actions for IT teams:

Publish frequently used standard reports as corporate documents to be access by all users.

Automatic schedule standard reports for some key users. For some users as per their need schedule some of report. Outputs of these reports are automatically refreshed at given date/time and send directly to user mail box.

Refresh output of some of standard reports and published it on common portal to be access by users based on their profile.

Challenges: Data Quality, Changes in Source Systems, Performance &Security

There are some challenges inmaintaining HR Data Models. Some of keys challenges are data quality,performance & data security. Data in HR Reporting solution should be alwaysconsistent with data in Source System. While doing data extraction &transformation we have to ensure that source data isnt modified in any otherway than what business needs. Data modifications done in source systems have tobe done in HR data warehouse also. This data modification are sometimes have to be done retroactive also so asto have consistent of data in source system as historical data indatawarehouse.I would suggest build some keydata integrity checks while doing ETL,this would ensure that wrong dataaren’t reported in HR reports. For Instance check to ensure that all EmployeeID existing in datawarehouse are also exists in source system. Similar checksfor some of important HR indicators like census of company, top managers in company,total count of employees etc. In fact most of times these checks indatawarehouse helps to clean the data in source systems. In case during dataquality checks if bad data is detected, it is good to remove the access of thisspecific functionality from reporting application till the time it is fixed.This would unnecessary avoid reporting bad data in HR reporting.

Change is data structure in sourcesystems is another challenge for data warehouse team. It is good practice to keepdata structures of data warehouse insynch with data structures of source systems. Normally source systemsundergo version up- gradation and these results in sometime major changes indatabase structures. Also these up-grades results in additional functionalitiesin the source systems. It is uphill tasks in data warehouses to keepconsistency of data as well as database structure vis-à-vis source systems.

One of key issues in any datawarehouse is the performance. As data warehouse store lots of historical data,it is challenging task to keep performance of data warehouse at optimal level.It is advisable to regularly index and analyze the database objects .The historicaldata which arent used much for reporting can be archived. This would reduce the size of database as well asincrease the performance of database. For Instance Corporate having 50,000 plusemployees would generate around 200 Gbytes of data in around 20 years, whichinclude history also.

HR data is always sensitive and itis one of the reasons why HR data marts are kept out of Enterprisedatawarehouse.We need to ensure that users can access HR data as per his/her profile.It is good to apply same security profiles for users what have in sourcetransaction applications. Apply same security rules as available in SourceSystems avoid unnecessary security management in data warehouse. One of bestpractice for HR reports is always publish and share them as blank reports. User based on his/herprofile will see the data in reports. Take special care of hide sensitive datalike compensation, grade and review rating etc.At Data Model level securityshould be applied at record level as well as object level. Record level security ensures that user see only relevant records,whereas object level security restrict it further. For Instance, if the userhas access to employees data of division A and he/she cant access compensationinformation. In this case record level security ensures that the user see allemployees data of division A, whereas objectlevel security further restrict him from access compensation details ofthese employees.

Summary

My suggestion is always follow bottom to top approach when designingHR Data mart. First and foremost identify different sub functions of HR whichare relevant for your organization. Secondly identify conformed dimensionswhich could be used across other HR Data Models. Put one Data Model in LIVE andhave users  feedback. Incorporate thevaluable users feedbacks in other HR Data Models.