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.