Processing .....please wait.. 
The article has been forwarded.... 
Human Resources Analytics
By siliconindia
|
1 Comments
One of the pre-requisite of porting solution is that it should be simple & easy to use. Also it should hide the technical complexity of operational systems. It would be good practice if the terms used for reporting in HR Data Model have business friendly name like business title, status category, local category etc. This enables users to design their own ad-hoc query and reports using from HR data models. Also this reduced unnecessary support for IT team.
Some of users prefer reporting directly from source system. Usually creating report on transaction system isn't easily. There are number of limitations. Running reports on transaction system would unnecessary burden the system. Also reporting from transaction system isn't user friendly. Usually user faced difficulty in designing ad-hoc query or report on transaction system as the table/column names are too technical. In such a case Reporting from BI tool is an advantage. There are number of BI tools available in market as well as free wares. IT team can use these tools to create data model for HR. The one disadvantage of reporting through BI tool is as most of these system access data from data warehouse database and there is time lag. Unlike transaction system, reporting through data warehouse involves some time lag.
Start designing HR data mart One of the key challenges is to identify relevant HR reporting tables from Source system. Normally complex ERP solutions have thousands of database tables. Out of them tables relevant for reporting purpose would be around 4-5 percent of total number of tables. When you start data modeling for HR data marts, I would suggest focus on de-normalization. Bundled as much as possible the related information's inside single table. For instance, for employee information like job, personal, employment, nationality, educational qualification would be in separate tables in operation system. In data warehouse, you can create single table EMPLOYEE and stored all information inside it. Such approach would minimize the number of fact tables in data model and as a result data model would be simple and comprehensive. Such design of data models help in tuning complex reports in future.
As in other business domain, there is need in Human Resource to do reporting for current as well as historical details. Historical details reporting in HR is normally done for compensation, performance, job grade, job code and more. I would suggest create separate fact tables for storing complete evolution/history of particular action. For instance, an employee has 25 different actions like grade change, pay rate change, department change and more. It is good idea to store all related actions in separate fact tables. Create different fact tables for compensation evolution, grade evolution, review history, training details and more. This would reduce complexity in Data Model design as well as in extraction program.
Another challenge is to identify conform dimensions across different data models of HR. Normally attributes which are common are cost center, location, job code, time and more. A complete hierarchy should be build for each of these conformed dimensions. For instance for cost center confirmed dimension, other common attributes like organization, group, cost center manager, effective status and such can be combined in single hierarchy called Organization or Cost Center. Hierarchy enables users to easily drill-up and drill down the data in report. Also, it is easy to do reporting by different axis of conformed dimension. Similarly hierarchy should be build for other conformed dimensions.
Data Structures
While building data structure for HR Data Models follow some standard naming convention. For Instance prefix all data structure name with HR. Further segregation could be done for tables, procedures, packages, functions, triggers and more. It is always good to follow somewhat similar database objects name as available in source system. For instance, if in Source system one of database object name is PS_PERSONAL_DATA, in data warehouse it could be TR_PERSONAL_DATA_TBL. This helps in data debugging when IT teams needs to verify data in data warehouse with data in source systems.
HR Key functional areas
Tracking Census & Movement of Employees
One of the key functions of HR is report & track on periodically basis the census in company as well as in different divisions of company. Census reporting 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 stored historically either by week or month. Census reporting enables senior management to keep control on hiring in today recessionary situation. Also it helps to identify current attrition rate for different division of the company. Depending on the needs of the organization, census reporting could be done in details 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 complex census reporting by attribute like running average, year to date etc. In big corporate having operations across multiple countries international mobility of employees is a common features. Every week hundreds of employee moves across different division & locations of an organization. In HR, there is need to track such movement of employees for different division of an organization.
Staffing
Another equally important HR function which needs to track is recruitment or staffing. To have business run, organization needs to hire or fill important openings in today's recessionary business scenario .In big corporate house there is standard procedures followed for any new hiring. A job requisition is raised and it passed through cycle of validations/approved. Once an employee is hired against job requisition, the life cycle of job requisition comes to an end. Depending upon the size of organizations, job requisition (job openings) would be multiples. Also there are internal or external job openings. Sometimes it takes months to hire an employee against job requisition, some job requisition don't complete the life cycle, may be declined by senior manager. It is useful to track important HR metrics like average time to fill job requisition in an organization, average time taken by job requisition to open (a stage in life cycle of job requisition at which employee would be hired). Also data model would gives pictures of total active job requisition, inactive or closed job requisition.
Training and Development
Training & Development is one of the core HR functions. In totally competitive world, it is necessary that organization regularly upgrade their employees' skills. There could be multiple methods used by an organization to train it's employees like instructor led class training or computer based training. Considering the cost and feasibility to reuse again and again, most of the organizations are focusing more on e-learning training programs. There are different packages available in market today fore-learning package. In some specific manufacturing industries are specific certification course for operator, who operator complex machines. An operator needs to regularly get certified on complex machines. Also, Training data models enable IT team to consolidate different IT training systems data into single place. Use of Conformed dimension in Training data models enable users to combine Training data with other HR statistics like census. For instance a Training department users wants to find out which all division at specific location of an organization have total training hours less than mandatory training hours. Critical indicators like training cost per divisions, number of certified internal trainers etc would be easily extracted from Training data model.
Compensation and Benefits
Compensation and Benefits analysis is an important functions of HR. Normally at the beginning of each financial year, Corporate allocated budget to each country HR to cater to pay rate change, promotion, recognition and for other benefits. Corporate Compensation teams needs to track consumption of budget allocated to different countries. Usually in such tracking there are number of exclusion rules like employee impacted by international mobility are not considered, new hire and rehire are excluded from such analysis. Usually, common 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 BI solutions the usage of application is around 50-60 percent. One of the reasons for low usage of BI solution is that, in big corporate access to BI applications are by default given to every Senior Management employees. Normally these employees never had time or in some case inclination to log-on to BI applications. To increase the usage of BI applications, 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 in maintaining HR Data Models. Some of keys challenges are data quality, performance & data security. Data in HR Reporting solution should be always consistent with data in Source System. While doing data extraction &transformation we have to ensure that source data isn't modified in any other way than what business needs. Data modifications done in source systems have to be done in HR data warehouse also. This data modification are sometimes have to be done retroactive also so as to have consistent of data in source system as historical data in data warehouse. I would suggest build some key data integrity checks while doing ETL, this would ensure that wrong data aren't reported in HR reports. For instance check to ensure that all Employee ID existing in data warehouse are also exists in source system. Similar checks for 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 in data warehouse helps to clean the data in source systems. In case during data quality checks if bad data is detected, it is good to remove the access of this specific 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 source systems is another challenge for data warehouse team. It is good practice to keep data structures of data warehouse in sync with data structures of source systems. Normally, source systems undergo version up- gradation and these results in sometime major changes in database structures. Also these up-grades results in additional functionalities in the source systems. It is uphill tasks in data warehouses to keep consistency of data as well as database structure vis-a-vis source systems.
One of key issues in any data warehouse 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 historical data which aren't used much for reporting can be archived. This would reduce the size of database as well as increase the performance of database. For Instance Corporate having 50,000 plus employees would generate around 200 GB of data in around 20 years, which include history also.
HR data is always sensitive and it is one of the reasons why HR data marts are kept out of Enterprise data warehouse. 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 source transaction applications. Apply same security rules as available in SourceSystems avoid unnecessary security management in data warehouse. One of best practice for HR reports is always publish and share them as blank reports. User based on his/her profile will see the data in reports. Take special care of hide sensitive data like compensation, grade and review rating and more. At Data Model level security should 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 user has access to employees data of division A and he/she can't access compensation information. In this case record level security ensures that the user see all employees data of division A, whereas object level security further restrict him from access compensation details of these employees.
Summary My suggestion is always follow bottom to top approach when designing HR Data mart. First and foremost identify different sub functions of HR which are relevant for your organization. Secondly identify conformed dimensions which could be used across other HR Data Models. Put one Data Model in LIVE and have users feedback. Incorporate the valuable users feedbacks in other HR Data Models.
The article is authored by Sanjay Rai, Service Delivery Manager, ST Microelectronics.