DWH Script

DROP TABLE fakten;
DROP TABLE dwh_locations;
DROP TABLE dwh_departments;
DROP TABLE dwh_hire_dates;
DROP TABLE dwh_jobs;
DROP SEQUENCE S_DWH_HIRE_DATES_ID;
DROP SEQUENCE S_DWH_JOBS_ID;


CREATE TABLE Fakten
  (
    avg_salary                    NUMBER ,
    anz_mitarbeiter               NUMBER ,
    dwh_locations_location_id     NUMBER NOT NULL,
    dwh_departments_department_id NUMBER NOT NULL,
    dwh_jobs_job_pk               NUMBER NOT NULL,
    dwh_hire_dates_hd_id          NUMBER NOT NULL
  ) ;

CREATE TABLE dwh_departments
  (
    department_id   NUMBER NOT NULL ,
    department_name VARCHAR2(50)
  ) ;
ALTER TABLE dwh_departments ADD CONSTRAINT dwh_departments_PK PRIMARY KEY ( department_id );

CREATE TABLE dwh_hire_dates(hd_id NUMBER NOT NULL,hd_year NUMBER);
ALTER TABLE dwh_hire_dates ADD CONSTRAINT dwh_hire_dates_PK PRIMARY KEY ( hd_id ) ;

CREATE TABLE dwh_jobs
  (
    job_pk    NUMBER NOT NULL ,
    job_id    VARCHAR2 (10) ,
    job_title VARCHAR2 (50)
  ) ;
ALTER TABLE dwh_jobs ADD CONSTRAINT dwh_jobs_PK PRIMARY KEY ( job_pk ) ;

CREATE TABLE dwh_locations
  (
    location_id     NUMBER NOT NULL ,
    city        VARCHAR2 (50) ,
    county_name     VARCHAR2 (50) ,
        region_name     VARCHAR2 (50)
  ) ;
ALTER TABLE dwh_locations ADD CONSTRAINT dwh_locations_PK PRIMARY KEY ( location_id ) ;

ALTER TABLE Fakten ADD CONSTRAINT Fakten_dwh_departments_FK FOREIGN KEY ( dwh_departments_department_id ) REFERENCES dwh_departments ( department_id ) ;

ALTER TABLE Fakten ADD CONSTRAINT Fakten_dwh_hire_dates_FK FOREIGN KEY ( dwh_hire_dates_hd_id ) REFERENCES dwh_hire_dates ( hd_id ) ;

ALTER TABLE Fakten ADD CONSTRAINT Fakten_dwh_jobs_FK FOREIGN KEY ( dwh_jobs_job_pk ) REFERENCES dwh_jobs ( job_pk ) ;

ALTER TABLE Fakten ADD CONSTRAINT Fakten_dwh_locations_FK FOREIGN KEY ( dwh_locations_location_id ) REFERENCES dwh_locations ( location_id ) ;



--Hier geht es richtig los


INSERT INTO dwh_departments (SELECT department_id,department_name FROM departments);

INSERT INTO dwh_locations (SELECT l.location_id,l.city,c.country_name,r.region_name FROM locations l JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id));

CREATE SEQUENCE S_DWH_HIRE_DATES_ID START WITH 1 INCREMENT BY 1 NOCYCLE;

INSERT INTO dwh_hire_dates (SELECT S_DWH_HIRE_DATES_ID.nextval,e.year FROM (SELECT DISTINCT TO_NUMBER(TO_CHAR(hire_date,'yyyy'),'0000') AS year FROM employees) e);

CREATE SEQUENCE S_DWH_JOBS_ID START WITH 1 INCREMENT BY 1 NOCYCLE;

INSERT INTO dwh_jobs (SELECT S_DWH_JOBS_ID.nextval, job_id, job_title FROM jobs);

INSERT INTO fakten (select AVG(e.salary), COUNT(e.employee_id), l.location_id, d.department_id, j.job_pk, h.hd_id
from employees e JOIN departments el ON (e.department_id = el.department_id), dwh_hire_dates h, dwh_jobs j, dwh_locations l, dwh_departments d
WHERE TO_NUMBER(TO_CHAR(e.hire_date,'yyyy'),'0000') = h.hd_year
AND e.job_id = j.job_id
AND el.location_id = l.location_id
AND e.department_id = d.department_id
GROUP BY (l.location_id, d.department_id, j.job_pk, h.hd_id));