Notes

Create table Fact_Emp
(
J_ID Number,
P_ID Number,
D_ID Number,
Salary Number(10,2),
Commission_PCT Number(4,2)
);

Create table Dim_Job
(
J_ID Number,
Job_ID Varchar2(10),
Job_Titloe Varchar2(35)
);

Create table Dim_Period
(
P_ID Number,
P_Year Number
);

Create table Dim_Department
(
D_ID Number,
Department_ID Number,
Department_Name varchar2(24)
);

Create sequence Dim_ID;

Insert into Dim_Job
Select Dim_ID.NEXTVAL, Job_ID,Job_Title
From hr.jobs;

Insert into Dim_Department
Select Dim_ID.NEXTVAL, Department_ID,Department_Name
From hr.departments;

Insert into Dim_Period
Select Dim_ID.NEXTVAL, Yr
From (Select DISTINCT EXTRACT(YEAR From Hire_Date) as Yr
From hr.Employees);

Commit;

Declare
Type Arr_Emp IS TABLE OF Fact_Emp%ROWTYPE;
Emp Arr_Emp;

CURSOR cr IS SELECT J_ID, P_ID, D_ID, SUM(Salary), SUM(Commission_PCT)
From hr.employees e INNER JOIN Dim_Job j
ON e.Job_ID=j.Job_Id
INNER JOIN Dim_Department d
on e.department_ID = d.department_ID
INNER JOIN Dim_Period p
ON p.P_Year=EXTRACT(YEAR From e.Hire_Date)
GROUP BY J_ID, P_ID, D_ID;
BEGIN
OPEN cr;
Fetch cr BULK COLLECT INTO emp;
CLOSE cr;

For i IN 1..Emp.Last Loop
IF EMP(i).Commission_PCT IS NULL THEN
EMP(i).Commission_PCT :=0;
END IF;
END LOOP;

FORALL i in 1..Emp.Last
Insert into Fact_Emp Values(
Emp(i).J_ID,
Emp(i).P_ID,
Emp(i).D_ID,
Emp(i).Salary,
Emp(i).Commission_PCT
);
END;

Select * from fact_emp;