Beginner’s Guide to E.T.L (Extract, Transform and Load) – A Basic Process

Loading Data from Source to Target

Before we proceed it’s important to identify the tool you might need to accomplish the process of ETL, in my case i would be using the Pentaho Data Integration Application (keep in mind irrespective of the tool you use the steps or procedures are similar but the approach might be differ).

The following Steps can be followed:

1. Identify your Source, it could be the following:
a. C.S.V file
b. Text file
c. A database
d. and so on

in my scenario a C.S.V (comma separated file)file.

2. Open Up your Spoon.bat
Select a new transformation and select a “Input” then select what type of input you require
we have Text File Input, C.S.V file input, SAS Input, Table Input and so on. In My case since i’ll be using C.S.V file as a Source i’ll select C.S.V file Input Component.

3. Set Up your Connection Based on your preferred connection type in my case i’ll be using the postgreSQL.

[Read my next Blog on setting Up a Connection using Connection type: PostgreSQL]

4. Once Connection has been Established you can Right-Click on the Connection and Select Share if that’s a common Connection to all your transformations will be using this will share the Transformation Connection to other transformation.

5. So we will be Sending data from Source to a Target to we need to to have a “Input” as the source
and an “Output” as the Target.

6. Input

download a C.S.V file from the internet

or even Create a TXT/C.S.V input file
as shown below.

Create a source if required
Text_Source (comma delimited)

Employee_Number,First_Name, Last_Name, Middle_Initial, Age, Gender, Title
101,Ehizogie,Izebhijie,Sohail,24,Male,Developer
102,Fahad,Anjum,WithClause,23,Male,Developer
103,Gayatri,Sharma,A,24,Female,Accountant

Save as txt or csv and this can be your input.

Here since our input is from a csv file
we open Up or C.S.V File Input component
Step Name: Anyname
File Name: Browse the selected path
Delimiter: , (comma)
Enclosure: ”

and then Select Get Fields and Select OK
Preview your Data

7. Output
Open Up Table output component
Select Target Schema
Select The Target Table

[Keeping in mind a Table exist in the DB]

Select OK!

Right-Click on the table output to MAP the columns from Source to Target

Now this is important as the to get the right data from the source to the column in the target
and Then Run

As a Beginner keep in mind that
Errors are a bound to occur
Such as Type of data from
Source Does not Match your Target Table format.
and so on.

Here a we can Have some little transformation step to convert and take care of such format errors
[In my next blog we can look into handling that]

Now Go to your Target Database and Run the SELECT * FROM table_name

there you go!

Thanks
Sohail Izebhijie

Business Intelligence in Human Resource

Business Intelligence in Human Resource

 

Business Intelligence 

Business Intelligence refers to the ability to use information to gain a competitive edge over competitors and to collect business data to find information primarily through asking questions, reporting, and online analytical processes. It is highly capable of handling large amounts of unstructured data to help identify, develop and otherwise create new strategic business opportunities.

Business Intelligence includes data management methods for planning, collecting, storing, and structuring data into data warehouses and data marts for clustering, classification, segmentation, and prediction.

Business intelligence plays a crucial role to achieve competitive edge over competitors in the challenging economy. The data collected contains a copy of analytical data that facilitates decision support which serve for business intelligence.

Human Resource Management

Human resource management is a function in organizations designed to maximize employee performance in service of their employer’s strategic objectives. It is primarily concerned with how people are managed within organizations, focusing on policies and systems. HR departments and units in organizations are typically responsible for a number of activities including employee recruitment, training and development, and rewarding.

Importance of Business Intelligence in Human Resource

Today, analyzing the demographics of a workforce has become an increasingly important part of HR function. Companies in the traditional markets face the problem of an aging workforce and there is often an intense competition for the best new talent.

Human Resource system globally contains masses of data. HR system manages information such as employee profiles, appraisals, compensation, benefits, etc. It must analyze the key skill sets and demographics of their existing workforce, assess whether it is helping them to meet their business targets and then identify whether things are going correct or not to help the company progress.

The Business Intelligence is potentially the missing link to turn those swarms of data into valuable information that can be used to inform decisions at all levels within a company. One of the most important features of modern BI is being able to tailor access to sensitive data.

Moreover the BI solution contains hundreds of pre-defined metrics (Skills by location, function, grade, etc , Employee performance by function, demographics, location, etc , Costing of HR functions(recruit, train), Benefits Costing, etc), assembled in meaningful charts, dashboards, scoreboards and various reports. The solution should be in such a way that HR manager can access the information they need with little effort. The user should be able to slice and dice the information through the charts and dashboards to easily drill down to the underlying data. The developed BI solution contains better use of Key Performance Indicators (KPIs). The solution should help the users easily answer important questions, identify emerging trends and predict risks and results.

 

Benefits of using Business Intelligence in HR system
  • Workforce Optimization : Using analysis throughout the company, top and bottom performers can be identified and then moved up or down. This could be used to inform future spending on training and to identify learning needs of employees.
  • Optimize Compensation : BI will help in analyzing the salary trends, group wise salary comparison, salary distribution and skews by grade, performance, etc. It will also help in evaluating benefits plan for maximum value.
  • Manage Recruitment : BI will help in analyzing the time and cost by recruitment method. Also they can analyze the recruitment success rates, applicant statistics, dropout reasons etc.
  • Analyze Workforce Composition : It will help in understanding workforce trends by job, geography, business areas, user-defined categories etc.
  • Staff Requirements Forecasting : This is based on organization goals, turnover prediction, Staff demographics, attrition etc.

Following are the key KPIs for the HR functions              

HR functions KPIs
  • % external hire rate
  • Net hire rate
  • % new position recruitment rate
  • new position recruitment ratio
  • Applicant Ratio
  • % newly recruited employees screened
  • $ Average interviewing Cost
  • % Actual cost of hire
  • Average feedback time on candidates
  • % Employee probation reports outstanding
  • % Internal appointments above level
  • % Internal hire rate
  • % Internal placement rate
  • % Cross functional mobility
  • % Employee transfer rate
  • Average interviews per hire
  • % Referral rate
  • Interviewee ratio
  • $ cost per hire
  • $ Average signing bonus expense
  • Average open time of job positions

Conclusion

HR departments struggle to make sense of disparate and overlapping data sources such as ERP systems, spreadsheets, payroll and benefits data, employee surveys, industry benchmarks. These can be brought down by using the business intelligence, which helps the HR’s to design effective compensation benefits, analyze overtime hours and costs by the departments. It also helps to measure the employees productivity and performance and correlate the information with the employee’s skill set to identify the skills that contribute to good performance by the employees.