In Oracle E-Business Suite (EBS), the Human Resource (HR) module uses various tables to store transactional and dimensional data for HR activities like employee records, payroll, benefits, and job positions. For reporting and analysis, these tables can be structured into fact and dimension tables in a data warehouse or BI environment.
Key Fact Tables for HR Module
Fact tables in the HR module primarily store measurable and transactional data.
Fact Table Description
HR_PAYROLL_FACT Stores payroll-related data, such as salaries, bonuses, and deductions.
HR_EMPLOYEE_ATTENDANCE Tracks attendance records like working hours, leaves, and overtime.
HR_TRAINING_FACT Captures training sessions attended by employees, completion rates, and scores.
HR_RECRUITMENT_FACT Contains recruitment metrics such as applications, interviews, and hires.
HR_BENEFITS_FACT Records benefits enrollment and usage details like health plans and retirement.
HR_PERFORMANCE_FACT Stores employee performance evaluation scores and appraisal metrics.
Key Dimension Tables for HR Module
Dimension tables provide descriptive data for analysis and join with fact tables via foreign keys.
Dimension Table Description
EMPLOYEE_DIM Stores employee details like name, department, job title, and hire date.
JOB_DIM Stores job roles, job categories, and pay grades.
DEPARTMENT_DIM Stores department names, IDs, and hierarchy.
TIME_DIM Stores time attributes (day, month, quarter, year) for analyzing trends.
LOCATION_DIM Stores office locations, regions, and countries.
PAYROLL_DIM Stores payroll period details like start date, end date, and frequency.
BENEFITS_PLAN_DIM Stores details about benefits plans like health, dental, and retirement plans.
TRAINING_DIM Stores training program details like course name, trainer, and duration.
RECRUITMENT_STAGE_DIM Tracks stages of recruitment like screening, interview, and onboarding.
Example Schema Design
Fact Table: HR_PAYROLL_FACT
Column Description
EMPLOYEE_ID Foreign key to EMPLOYEE_DIM.
PAYROLL_ID Foreign key to PAYROLL_DIM.
PAY_PERIOD Foreign key to TIME_DIM.
GROSS_PAY Total pay before deductions.
NET_PAY Pay after deductions.
DEDUCTIONS Amount deducted (taxes, benefits).
Dimension Table: EMPLOYEE_DIM
Column Description
EMPLOYEE_ID Unique identifier for employees.
EMPLOYEE_NAME Full name of the employee.
DEPARTMENT_ID Foreign key to DEPARTMENT_DIM.
JOB_ID Foreign key to JOB_DIM.
HIRE_DATE Employee’s hire date.
Dimension Table: TIME_DIM
Column Description
TIME_ID Unique identifier for time periods.
DATE Specific date.
MONTH Month name.
QUARTER Fiscal quarter.
YEAR Year.
Example Query
Question: What is the total payroll cost by department in Q4 2023?
SQL:
SELECT
d.DEPARTMENT_NAME,
t.QUARTER,
SUM(f.GROSS_PAY) AS TOTAL_PAYROLL
FROM
HR_PAYROLL_FACT f
JOIN
EMPLOYEE_DIM e ON f.EMPLOYEE_ID = e.EMPLOYEE_ID
JOIN
DEPARTMENT_DIM d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN
TIME_DIM t ON f.PAY_PERIOD = t.TIME_ID
WHERE
t.YEAR = 2023 AND t.QUARTER = 'Q4'
GROUP BY
d.DEPARTMENT_NAME, t.QUARTER;
ETL Process for HR Data
1. Extract: Pull data from Oracle EBS HR tables (PER_ALL_PEOPLE_F, PAY_PAYROLL_ACTIONS, PER_ALL_ASSIGNMENTS_F).
2. Transform:
• Map source columns to fact and dimension tables.
• Cleanse data (e.g., deduplicate employees, standardize job titles).
• Generate surrogate keys for dimensions.
3. Load: Populate fact and dimension tables in the data warehouse.
Benefits of Dimensional Modeling in HR
1. Performance: Simplifies complex queries for HR analytics.
2. Flexibility: Supports slicing and dicing data by time, department, or job role.
3. Scalability: Easy to add new metrics or dimensions, such as new benefits plans.
This approach transforms Oracle EBS HR data into actionable insights for strategic decision-making.