view_creation.sql

---------------------------------------------------------------------------
-- MULTIDIMENSIONAL MODELING FOR THE ACME-FLYING USE CASE
-- Materialized views
-- 20241029
-- Parcerisa Marc - Vendrix Alexis
---------------------------------------------------------------------------

---------------------------------------------------------------------------
-- Log creation
---------------------------------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON FactFlight;
CREATE MATERIALIZED VIEW LOG ON DimFlight;

CREATE MATERIALIZED VIEW LOG ON FactMaintenance;
CREATE MATERIALIZED VIEW LOG ON DimMaintenance;

CREATE MATERIALIZED VIEW LOG ON FactReportEntries;
CREATE MATERIALIZED VIEW LOG ON DimReporteur;

CREATE MATERIALIZED VIEW LOG ON DimAirport;

CREATE MATERIALIZED VIEW LOG ON DimAircraft;
CREATE MATERIALIZED VIEW LOG ON DimModel;




-- The flight view is created to put together information concerning FactFlight, 
-- DimFlight, DimAircraft, and DimModel tables into a single dataset.
CREATE VIEW Flight_view
    AS
    SELECT 
        f.FlightID,
        f.AircraftID,
        m.ModelID,
        d.ScheduledDepartureTime,
        d.DelayType,
        f.FlightDuration,
        f.DelayDuration
    FROM FactFlight f
    INNER JOIN DimFlight d 
        ON f.FlightID = d.FlightID
    INNER JOIN DimAircraft a
        ON f.AircraftID = a.AircraftID
    INNER JOIN DimModel m
        ON a.ModelID = m.ModelID;


-- ReportEntries_view aggregates information about reports related to flights, 
-- reporteurs, airports, aircraft, models, and manufacturers from multiple tables.
-- This view helps querying insights on LogBook entries.
CREATE VIEW ReportEntries_view
    AS
    SELECT 
        a.FlightID,
        a.ReporteurID,
        b.ReporteurType,
        b.ReporteurID,
        c.AirportID,
        e.AircraftID,
        f.ModelID,
        f.ManufacturerID,
        a.EntryCount
    FROM FactReportEntries a

    INNER JOIN DimReporteur b
        ON a.ReporteurID = b.ReporteurID

    -- Only PIREP have a linked airport
    LEFT JOIN DimAirport c
        ON b.AirportCode = c.AirportCode

    INNER JOIN FactFlight d
        on a.FlightID = d.FlightID

    INNER JOIN DimAircraft e
        ON d.AircraftID = e.AircraftID

    INNER JOIN DimModel f
        ON e.ModelID = f.ModelID

        ;

-- Maintenance_view aggregates information about reports related to Maintenance, 
-- aircraft, models from multiple tables.
-- This view helps querying insights on LogBook entries.
CREATE VIEW Maintenance_view
    AS
    SELECT 
        a.MaintenanceID,
        a.AircraftID,
        d.ModelID,
        b.MaintenanceStart,
        b.IsScheduled,

        -- The maintenance duration is in days
        a.MaintenanceDuration
    FROM FactMaintenance a

    INNER JOIN DimMaintenance b 
        ON a.MaintenanceID = b.MaintenanceID

    INNER JOIN DimAircraft c
        ON a.AircraftID = a.AircraftID

    INNER JOIN DimModel d
        ON c.ModelID = d.ModelID;



-------------------------------------------------------------
------------ Views for KPI
-------------------------------------------------------------

-- a. FlightOut_view
-- We are evaluating FH, TO, DY, DY duration and CN at the Aircraft, ModelID, Day level.
-- Those results can be agregated on the upper level afterward and used to calculate. 
-- This table can be used to directly calculate TDR, CNR, DYR
CREATE MATERIALIZED VIEW Flight_Information_view
    REFRESH FAST ON DEMAND
    AS
    SELECT 
        a.AircraftID,
        a.ModelID,

        --We report the total duration of the flight on the departure date
        a.ScheduledDepartureDate,
        SUM(a.FlightDuration)                                                       AS FH,

        -- The number of take off is the number of time a flight has a positive flight duration
        SUM(case when FlightDuration > 0 then 1 else 0 end )                        AS TO,
        
        -- The number of delay only take into account delays between 15min and 6hours
        SUM(case when DelayDuration> 15 and DelayDuration < 6*60 then 1 else 0 end)  AS DY,

        -- The total duration of delay only take into account delays between 15min and 6hours
        SUM(case when DelayDuration > 15 and DelayDuration < 6*60 then DelayDuration else 0 end)  
                                                                                    AS DY_Duration, 
        -- A flight is counted as cancel if the flight duration is equal to 0                                                                                   
        SUM(case when FlightDuration = 0 then 1 else 0 end)                         AS CN

    FROM Flight_view a
    GROUP BY AircraftID,
                ModelID,
                ScheduledDepartureDate;



-- b. ADOS View
-- We evaluate the duration for ADOS, ADOSS, ADOSUS.
-- The ADIS value is the complement of ADOS on a certain period of time and can be calculated by the user.
-- We report the total duration of maintenance on the month of the start of the maintenance. 
-- An imporovement can be done to manage maintenance occuring on multiple several months because we may have more ADOS than days in a month.
CREATE MATERIALIZED VIEW ADOS_view
    REFRESH FAST ON DEMAND
    AS
    SELECT 
        a.AircraftID,
        a.ModelID,
        -- We aggregate by YEAR MONTH by truncating the date to YYYY-MM
        trunc(a.MaintenanceStart,’month’)                                       AS MaintenanceStart,
        SUM(a.MaintenanceDuration)                                              AS ADOS,
        SUM(case when IsScheduled = TRUE  then MaintenanceDuration else 0 end)  AS ADOSS,
        SUM(case when IsScheduled = FALSE then MaintenanceDuration else 0 end)  AS ADOSU

    FROM Maintenance_view a
    GROUP BY AircraftID,
                ModelID,
                -- We aggregate by YEAR MONTH by truncating the date to YYYY-MM
                trunc(a.MaintenanceStart,’month’);


-- c. Reporting_view
-- We are evaluating the global entry count per flight but also by pilot and maintenance person.
-- A person can have multiple entries for the same flight and are aggregated.
-- The view is aggregated by AircraftID, ModelID, ManufacturerID and month
-- Reporting_view and FlightOut_view can be use together to evaluate RRh, RRc, 
-- PRRh, PRRc, MRRh and MRRc per aircraft (also per model and manufacturer) 
-- per month (also per year). /!\. FlightOut_view is on a Day temporal level.
CREATE MATERIALIZED VIEW Reporting_view
    REFRESH FAST ON DEMAND
    AS
    Select 
            a.AircraftID,
            a.ModelID,
            a.ManufacturerID,
            a.ReporteurID,
            -- We aggregate by YEAR MONTH by truncating the date to YYYY-MM
            trunc(a.ScheduledDepartureDate,’month’)                    AS ScheduledDepartureDate,
            sum(EntryCount) as EntryCount,
            sum(case when ReporteurType = 'PIREP' then EntryCount else 0 end) as Pilot_EntryCount,
            sum(case when ReporteurType = 'MAREP' then EntryCount else 0 end) as Maintenance_EntryCount             
    FROM ReportEntries_view a
    GROUP BY AircraftID,
                ModelID,
                b.ManufacturerID,
                a.ReporteurID,
                -- We aggregate by YEAR MONTH by truncating the date to YYYY-MM
                trunc(a.ScheduledDepartureDate,’month’);