Case Study Report
on
Sage Human Capital
Customer :
Sage Human Capital, HR Company
Geographical Location :
San Bruno, California
Tools Used :
DB used : MySQL, Postgres
ETL tool : Talend Open Studio
BI Tool : Jaspersoft Professional
Cloud Service : Amazon Web Hosting (AWS)
Other softwares : Maxhire, Ringcentral
Company Overview :
Sage Human Capital is US leading human resource company. They have revolutionized HR recruiting industry by bringing introducing some innovative concepts like “talent as a service” and “SmartSource” and boast of clientele like Accenture, Jaspersoft, McAfee, Salesgenie, Adidas, Text100 to name a few.
Problem Statement :
Working in HR domain, Sage gets resource requirement for specific job profiles with specific skillsets. Based on skillset and other factors like location experience etc, Sage starts searching for suitable candidates. Different teams come into picture at different stage, sourcing team is responsible for sourcing of CVs from all the platforms, KAM are responsible for approaching candidates via making and other mediums calls and taking interviews, followups, shortlisting and then the result is shared with client for the final process.
The problems faced by Sage was:
- Lack of transparency of information to client regarding the job progress
- Lack of trust on the performance data
- Tracking the candidate progress by clients
- Checking the performance of the sourcing team and key account managers in terms of actual hiring.
Sage was in need of a BI platform which could be used by different stakeholders like client, Sage internal team etc with different access and privilege. These teams once login, they can see their specific KPIs related to jobs, hires, interviews etc.
Solution Developed :
Helical did handholding of the client and understood the requirement thoroughly. Once the same was over, we helped in deciding which BI suite to go for. We chose Jaspersoft BI tool. We used the Jaspersoft professional version which is hosted on AWS to develop the solution.
Also, we suggested to create separate reporting database using Postgres which will help the clients in fetching the reports and dashboards with minimal lag and much better performance.
Different kind of reports were developed with very interactive visualizations. Some of the reports which were developed are:
- Weekly calling Report showing number of calls made by different KAM for different profiles
- Candidate status Report showing profile wise status like how many candidates approached, how many interviews done, how many declined etc
- Client engagement Report
- Weekly Summary Report, etc.
Also many dashboards were developed, some of which includes :
- Sourcing Dashboard showing work done by sourcing team in getting CVs
- Recruitment Manager Dashboard
- Company Dashboard
Information related to calls made is coming from Ringcentral, information related to candidates, their skillset location company experience etc is coming from Maxhire.
All of these reports and dashboard were very interactive with input parameters. Based on those input parameters like (company name, date range, relationship manager etc) the entire report/dashboard data changes. Also various kind of visualizations were used like bar chart, candle stick, gauges, histogram etc to improve the readability.
Also these reports and dashboard were having the ability to drill down, which means as soon as we click on any of the panel, another panel will appear with detailed information, thus enabling the customer to do a deep down analysis. Also via email bursting, many of the reports were sent to different managers inbox in pdf/doc format.
Multi-tenancy was also implemented and proper user and role management was also implemented. Hence they were able to provide all of their customers separate user id and password, thus customers were able to access this hosted HR BI solution but they were restricted to see their own specific data only. Entire HR BI solution was hosted on Amazon Web Services.
Many functionality which were not directly available from Jaspersoft were also implemented by custom coding (like saving dashboard, rejection charts, saving input controls etc). This entire developed BI solution was integrated inside their website. Also the entire solution was also white labeled according to the companies color themes, text font, color etc.
Helical was also responsible for hosting this solution on AWS, and also doing an end to end performance tuning (SQL query optimization, cache memory increasing, hardware increasing etc.
ETL and DW Work :
Sage haS primarily two data source – One is Maxhire : MySQL and the other one is RingCentral : Postgres). Maxhire was storing information related to CV, job profile, skillset, company details, candidate information, job posting, etc. Whereas Ringcentral database is holding the information related to calls made by KAM, response, duration, etc.
Helical was responsible for writing ETL and creating a datawarehouse. The ETL tool used was an open source ETL tool namely Talend Open Studio. Different types of ETL were written like Initial Load ETL, Change Data Capture (CDC) ETL, Maintenance ETL etc. These ETL were executing at different frequency some were on daily and some on weekly basis.
ETL Automation Activity Details :
To transfer the data from the Max Hire database to Postgres we ran the ETL process using the Talend Open Studio tool. Here we performed various types of ETL to achieve the requirement which are:
- Initial Load ETL : Here we had defined the properties like source and destination database, required table , format of data, etc. Then a transformation of the data in the required format is done. At last, loading of data in the Postgres database is performed.
- Change Data Capture (CDC) : It is performed on a daily basis to check the manipulation of the data in the source database. If happened then the updation in the destination database is also performed.
- Maintenance ETL : It is performed on a weekly basis to check the manipulation of the data. But it checks the data from start till date and then updation of the data in the destination is performed.
- RingCentral ETL : This ETL process helps in uploading the calling data records in the Postgres database. Whereas the input records are in excel format. It is performed on the weekly basis.
Activity includes :
- Job scheduling : For updating the database source to destination database it is performed on a weekly basis.
The built HR BI solution by Helical is vigorously used by Sage internal team as well as its client and it is using this data analysis as one of its USP to sell more of its services. Not only this platform helped them to better serve their client but also they are more efficiently able to monitor the performance of different members of their team as well. Sage client retention has also improved drastically and they have become a heavy user of the data analysis capabilities of the build BI platform.
Snapshots for BI solution :
Login Page
We have used here features like multi-tenancy, white-labelling
Some Dashboards are :
-
Recruiting Dashboard
-
Client Dashboard
-
Sourcing Dashboard
-
Home Dashboard
-
List of exporting/saving the reports in various formats
Testimonial :
“Team Helical really did a excellent job building a world class BI solution for us. They helped us choose the right platform, work through our business cases and implement efficiently and effectively. They’re offshore so we were a bit apprehensive; however, they work during US hours and worked quickly. I would highly recommend Nitin and his team to anyone doing BI work”
– Paul Grewal, CEO, Sage Human Capital
.
Best Open Source Business Intelligence Software Helical Insight is Here
