Creating Materialized Views

To optimize data loading into the dashboard, let’s create materialized views.

Orders Materialized View

Create a materialized view for all metrics at the order level.

%%sql
CREATE MATERIALIZED VIEW analytics.mv_orders AS
WITH invoices_agg AS (
    SELECT
        fi.order_id
        , COUNT(fi.invoice_id) AS invoices_count
        , SUM(fi.invoice_amount) AS amount
        , SUM(fi.paid_amount) AS paid_amount
        , SUM(fi.invoice_profit) AS profit
        , STRING_AGG(DISTINCT ddm.delivery_method_name, ' | ' ORDER BY ddm.delivery_method_name) AS delivery_methods
        , MIN(fi.invoice_date) AS first_invoice_date
        , MAX(fi.invoice_date) AS last_invoice_date
        , MIN(fi.confirmed_delivery_time) AS first_delivery_time
        , MAX(fi.confirmed_delivery_time) AS last_delivery_time
        , BOOL_OR(fi.is_delivered) AS is_any_delivered
        , BOOL_AND(fi.is_delivered) AS is_all_delivered
		, SUM(CASE WHEN fi.is_delivered THEN fi.invoice_amount ELSE 0 END) AS delivered_amount
		, SUM(CASE WHEN fi.is_delivered THEN fi.paid_amount ELSE 0 END) AS paid_delivered_amount
    FROM
        analytics.fact_invoices fi
        LEFT JOIN analytics.dim_delivery_methods ddm ON fi.delivery_method_id = ddm.delivery_method_id
    GROUP BY
        fi.order_id
),
order_lines_agg AS (
    SELECT 
        fol.order_id
        , COUNT(fol.order_line_id) AS order_lines_count
        , SUM(fol.quantity) AS total_quantity
        , COUNT(DISTINCT fol.stock_item_id) AS unique_products_count
        , STRING_AGG(DISTINCT dp.stock_item_name, ' | ' ORDER BY dp.stock_item_name) AS products_list
    FROM
        analytics.fact_order_lines fol
        LEFT JOIN analytics.dim_products dp 
        	ON fol.stock_item_id = dp.stock_item_id
        	AND dp.current_record = TRUE
    GROUP BY
        fol.order_id
),
last_delivery_attempts_per_invoice AS (
    SELECT DISTINCT ON (fi.invoice_id)
        fi.invoice_id,
        fi.order_id,
        fi.invoice_date,
        (event->>'Latitude')::numeric AS delivery_latitude,
        (event->>'Longitude')::numeric AS delivery_longitude
    FROM
        analytics.fact_invoices fi
        CROSS JOIN LATERAL jsonb_array_elements(fi.returned_delivery_data::jsonb->'Events') AS event
    WHERE 
        event->>'Event' = 'DeliveryAttempt'
        AND event->>'Latitude' IS NOT NULL
        AND event->>'Longitude' IS NOT NULL
    ORDER BY 
        fi.invoice_id, 
        (event->>'EventTime')::timestamp DESC
),
last_delivery_coordinates_per_order AS (
    SELECT DISTINCT ON (ldai.order_id)
        ldai.order_id,
        ldai.delivery_latitude AS last_delivery_latitude,
        ldai.delivery_longitude AS last_delivery_longitude
    FROM
        last_delivery_attempts_per_invoice ldai
    ORDER BY
        ldai.order_id,
        ldai.invoice_date DESC
)
SELECT
    fo.order_id
    , fo.order_date
    , fo.expected_delivery_date
    , fo.picking_completed_when
    , fo.customer_id
    , dc.customer_name
    , dc.customer_category_name
    , dc.state_province_name AS customer_state
    , dc.city_name AS customer_city
    -- Invoice
    , ia.invoices_count
    , ia.amount AS total_amount
    , ia.paid_amount
    , ia.profit
    , ia.delivery_methods
    , ia.first_invoice_date
    , ia.last_invoice_date
    , ia.first_delivery_time
    , ia.last_delivery_time
    , ia.is_any_delivered
    , ia.is_all_delivered
    , ia.delivered_amount
    , ia.paid_delivered_amount
    , ldco.last_delivery_latitude
    , ldco.last_delivery_longitude    
    -- Order_line
    , ola.order_lines_count
    , ola.total_quantity
    , ola.unique_products_count
    , ola.products_list
    -- Calculated metrics
    , ROUND((ia.paid_amount / NULLIF(ia.amount, 0)), 2) AS paid_share
    , (ia.last_delivery_time::DATE - fo.order_date::DATE) AS days_to_delivery
    , (ia.last_delivery_time::DATE - fo.expected_delivery_date::DATE) AS delivery_delay_days
    , (fo.picking_completed_when::DATE - fo.order_date::DATE) AS days_to_picking
    , (ia.last_delivery_time::DATE - fo.picking_completed_when::DATE) AS days_to_deliver_after_picking
    , CASE
        WHEN ia.last_delivery_time IS NULL OR fo.expected_delivery_date IS NULL THEN NULL  
        WHEN ia.last_delivery_time::DATE > fo.expected_delivery_date::DATE THEN TRUE 
        ELSE FALSE 
    END AS is_late_delivery
FROM
    analytics.fact_orders fo
    LEFT JOIN analytics.dim_customers dc 
    	ON fo.customer_id = dc.customer_id
    	AND dc.current_record = TRUE
    LEFT JOIN invoices_agg ia ON fo.order_id = ia.order_id
    LEFT JOIN order_lines_agg ola ON fo.order_id = ola.order_id
    LEFT JOIN last_delivery_coordinates_per_order ldco ON fo.order_id = ldco.order_id;

Create indexes to speed up dashboard performance.

%%sql
CREATE INDEX idx_mv_orders_order_id ON analytics.mv_orders(order_id);
CREATE INDEX idx_mv_orders_customer_id ON analytics.mv_orders(customer_id);
CREATE INDEX idx_mv_orders_order_date ON analytics.mv_orders(order_date);
CREATE INDEX idx_mv_orders_customer_state ON analytics.mv_orders(customer_state);
CREATE INDEX idx_mv_orders_is_late_delivery ON analytics.mv_orders(is_late_delivery);
CREATE INDEX idx_mv_orders_is_all_delivered ON analytics.mv_orders(is_all_delivered);

Products Materialized View

Create a materialized view for all metrics at the product level.

%%sql
CREATE MATERIALIZED VIEW analytics.mv_products AS
WITH invoices_agg AS (
    SELECT
        fi.order_id
        , BOOL_OR(fi.is_delivered) AS is_any_delivered
    FROM
        analytics.fact_invoices fi
    GROUP BY
        fi.order_id
)
SELECT
	fo.order_id
	, ia.is_any_delivered
	, fol.order_line_id
	, fol.stock_item_id
	, dp.stock_item_name
	, dp.stock_group_names
	, dp.color_name
	, dp.unit_package_type_name
	, dp.outer_package_type_name
	, dp.brand
	, dp.size
	, fo.order_date
	, fol.quantity
	, fol.unit_price
	, fol.il_quantity
	, fol.il_extended_price
	, fol.il_line_profit
	, dp.brand IS NOT NULL AS has_brand
	, dp.size IS NOT NULL AS has_size
FROM
	analytics.fact_orders fo 
	LEFT JOIN invoices_agg ia ON fo.order_id = ia.order_id
	LEFT JOIN analytics.fact_order_lines fol ON fo.order_id = fol.order_id
	LEFT JOIN analytics.dim_products dp ON fol.stock_item_id =dp.stock_item_id
		AND dp.current_record = TRUE;

Create indexes to speed up dashboard performance.

%%sql
CREATE INDEX idx_mv_products_id ON analytics.mv_products(stock_item_id);
CREATE INDEX idx_mv_products_name ON analytics.mv_products(stock_item_name);
CREATE INDEX idx_mv_products_brand ON analytics.mv_products(brand);
CREATE INDEX idx_mv_products_group ON analytics.mv_products(stock_group_names);