AdvancedTopics: olap_fact_service_usage.sql

-- FactServiceUsage

CREATE TABLE FactServiceUsage (
                                  service_usage_key   BIGSERIAL,

                                  date_key            INTEGER       NOT NULL,
                                  hotel_key            INTEGER       NOT NULL,
                                  pet_key               INTEGER       NOT NULL,
                                  employee_key          INTEGER       NOT NULL,
                                  service_key            INTEGER       NOT NULL,

                                  price                 NUMERIC(10,2) NOT NULL,
                                  duration_minutes       INTEGER       NOT NULL,
                                  status                 VARCHAR(20)   NOT NULL
)
    PARTITION BY RANGE (date_key);

CREATE TABLE FactServiceUsage_2015
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20150101) TO (20160101);

CREATE TABLE FactServiceUsage_2016
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20160101) TO (20170101);

CREATE TABLE FactServiceUsage_2017
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20170101) TO (20180101);

CREATE TABLE FactServiceUsage_2018
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20180101) TO (20190101);

CREATE TABLE FactServiceUsage_2019
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20190101) TO (20200101);

CREATE TABLE FactServiceUsage_2020
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20200101) TO (20210101);

CREATE TABLE FactServiceUsage_2021
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20210101) TO (20220101);

CREATE TABLE FactServiceUsage_2022
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20220101) TO (20230101);

CREATE TABLE FactServiceUsage_2023
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20230101) TO (20240101);

CREATE TABLE FactServiceUsage_2024
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20240101) TO (20250101);

CREATE TABLE FactServiceUsage_2025
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20250101) TO (20260101);

CREATE TABLE FactServiceUsage_2026
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20260101) TO (20270101);

CREATE TABLE FactServiceUsage_2027
    PARTITION OF FactServiceUsage
        FOR VALUES FROM (20270101) TO (20280101);

CREATE INDEX idx_factsvc_service
    ON FactServiceUsage(service_key);

CREATE INDEX idx_factsvc_pet
    ON FactServiceUsage(pet_key);

CREATE INDEX idx_factsvc_hotel
    ON FactServiceUsage(hotel_key);



-- ETL

INSERT INTO FactServiceUsage (
    date_key,
    hotel_key,
    pet_key,
    employee_key,
    service_key,
    price,
    duration_minutes,
    status
)
SELECT
    TO_CHAR(sr.scheduled_date, 'YYYYMMDD')::INTEGER,
    dh.hotel_key,
    dp.pet_key,
    de.employee_key,
    ds_svc.service_key,
    s.price,
    s.duration_minutes,
    srs.name
FROM ServiceReservation sr

         JOIN ServiceReservationStatus srs
              ON srs.service_reservation_status_id = sr.status_id

         JOIN Reservation   r      ON r.reservation_id   = sr.reservation_id
         JOIN DimService    ds_svc ON ds_svc.service_id  = sr.service_id
         JOIN DimPet        dp     ON dp.pet_id          = r.pet_id
    AND dp.is_current      = TRUE
         JOIN DimEmployee   de     ON de.employee_id      = r.employee_id
    AND de.is_current       = TRUE
         JOIN Service       s      ON s.service_id        = sr.service_id

         JOIN RoomReservation rr   ON rr.reservation_id   = r.reservation_id
         JOIN DimRoom         dr   ON dr.room_id          = rr.room_id
    AND dr.is_current       = TRUE
         JOIN DimHotel        dh   ON dh.hotel_key        = dr.hotel_key;