Kohustusliku kirjanduse märkmed vol 8

Kursusel oli kokku 9 moodulit, kus iga juurde anti ka ports kohustuslikku kirjandust lugeda. Loomulikult sel ajal ma seda ei jõudnud lugeda, kuid võtan nüüd eesmärgiks kuu aega järjest lugeda. Vaatame, kuhu jõuame. Lugemine suvalises järjekorras.

Tim Lu: OLTP vs OLAP: A Beginner’s Guide to Data Systems, moodul 2: andmemudelid ja baasid, lisainfot OLAP ja OLTP süsteemide kohta.

OLTP:

• traditsiooniline andmebaasiskeem, kus andmed efektiivselt hallatud ja struktureeritud. Kiire tehingute töötlus suurte andmemahtude juures, kus oluline kliendi pöördumise täitmine ja tema andmete sisestamine.
• samaaegselt toimuvad samad protsessid erinevate andmetega, nt pangatehingud
• reaalajas
• oluline tagada andmete terviklikkus, järjepidevus nende muutmisel ja uuendamisel
• kiired operatsioonid: lisamine, muutmine, kustutamine
• andmete olemasolu kiirus prioriteediks
• päringud lühikesed

SELECT customer_name FROM database WHERE customer_ID = 23;

• normaliseeritud. Klient ja tema ostud eraldi tabelites.
• mitmetuumalised protsessorid paralleeltöötluseks, suure mälumahuga SSD-kettad.

OLAP:

• keskendub analüütikale, eesmärgiks avastada trende, mustreid ja anda ülevaateid strateegilisteks äri otsusteks
• andmed organiseeritud OLAP-kuupi, mis lihtsustab andmete tükeldamist. Tänaseks on sellelt edasi liigutud MPP- peale(massive parallel processing)
• OLAP-kuubis võimaldab kiirelt ja paindlikult leida andmeid dimensioonide kaupa
• kombineerib hetkeseisu ja ajaloolist vaadet
• võimaldab töölaudade, raportite loomist ning ennustava analüütikat
• denormaliseeritud, kus klient ja tema ostud võivad olla ühes tabelis
• keerukad päringud, kus liidetakse, ühendatakse andmeid erinevates tabelitest, dimensioonidest

-- Faktid
fact_sales(sale_id, sale_date_key, product_key, store_key, customer_key, qty, net_amount)
fact_inventory(snapshot_date_key, product_key, store_key, qty_on_hand, inventory_value) -- Dimensioonid
dim_date(date_key, full_date, year, quarter, month, month_name, is_weekend)
dim_product(product_key, product_name, category, subcategory, brand)
dim_store(store_key, store_name, city, region)
dim_customer(customer_key, customer_segment) Küsimus: regiooni, kategooria ja kuu lõikes näita müüki, müügi kasvu eelmise kuuga võrreldes, kumulatiivset müüki aastas, kategooria osakaalu regioonis ning viimase laoseisu (poolliidetav mõõdik). WITH monthly_sales AS (
    SELECT
        st.region,
        p.category,
        d.year,
        d.month,
        d.month_name,
        MIN(d.full_date) AS month_start,
        SUM(fs.qty) AS units_sold,
        SUM(fs.net_amount) AS net_sales
    FROM fact_sales fs
    JOIN dim_date d      ON d.date_key = fs.sale_date_key
    JOIN dim_product p   ON p.product_key = fs.product_key
    JOIN dim_store st    ON st.store_key = fs.store_key
    WHERE d.year = 2025
    GROUP BY
        st.region,
        p.category,
        d.year,
        d.month,
        d.month_name
), sales_with_olap AS (
    SELECT
        region,
        category,
        year,
        month,
        month_name,
        month_start,
        units_sold,
        net_sales,         -- eelmine kuu samas regioonis + kategoorias
        LAG(net_sales) OVER (
            PARTITION BY region, category
            ORDER BY month
        ) AS prev_month_sales,         -- kumulatiiv müük aasta sees
        SUM(net_sales) OVER (
            PARTITION BY region, category, year
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ytd_sales,         -- kategooria osakaal regiooni kuu müügist
        net_sales * 100.0 / SUM(net_sales) OVER (
            PARTITION BY region, year, month
        ) AS category_share_in_region_pct,         -- regiooni järjekoht kuu müügi järgi
        RANK() OVER (
            PARTITION BY year, month
            ORDER BY SUM(net_sales) OVER (PARTITION BY region, year, month) DESC
        ) AS region_rank_in_month
    FROM monthly_sales
), latest_inventory AS (
    -- poolliidetav: laoseisu EI tohi liita kuude lõikes, võtame viimase hetke
    SELECT DISTINCT ON (st.region, p.category)
        st.region,
        p.category,
        fi.qty_on_hand,
        fi.inventory_value,
        d.full_date AS inventory_snapshot_date
    FROM fact_inventory fi
    JOIN dim_date d    ON d.date_key = fi.snapshot_date_key
    JOIN dim_product p ON p.product_key = fi.product_key
    JOIN dim_store st  ON st.store_key = fi.store_key
    WHERE d.year = 2025
    ORDER BY st.region, p.category, d.full_date DESC
) SELECT
    s.region,
    s.category,
    s.year,
    s.month,
    s.month_name,
    s.units_sold,
    ROUND(s.net_sales, 2) AS net_sales,
    ROUND(s.prev_month_sales, 2) AS prev_month_sales,
    ROUND(
        100.0 * (s.net_sales - s.prev_month_sales)
        / NULLIF(s.prev_month_sales, 0),
        1
    ) AS mom_growth_pct,
    ROUND(s.ytd_sales, 2) AS ytd_sales,
    ROUND(s.category_share_in_region_pct, 1) AS category_share_in_region_pct,
    s.region_rank_in_month,
    i.qty_on_hand,
    ROUND(i.inventory_value, 2) AS inventory_value,
    i.inventory_snapshot_date
FROM sales_with_olap s
LEFT JOIN latest_inventory i
    ON i.region = s.region
   AND i.category = s.category
WHERE s.month IN (10, 11, 12)   -- slice: Q4
ORDER BY
    s.region,
    s.month,
    s.net_sales DESC; 

• suure jõudlusega protsessorid, suure mahuga RAM andmete töötlemiseks.

Meeldetuletuseks

Tavaliselt alguses OLTP ja siis liigutakse edasi sümbioosile neist mõlemast. OLTP igapäevaste tehingute töötlemine, andmed asjakohased ja täpsed. OLAP koondavad ja analüüsivad neid andmeid. OLTP OLAPile liikumine toimub, siis kui tunned, et andmehulk on muutunud suureks (ei, see ei ole paarkümmendtuhat rida), ja reaalselt tegeled ka oma andmete analüüsimisega. Või OTLP päringud võtavad liialt aega, mis selle põhjuseks on? Artikli näide mõlema põhimõtte sümbioosist: e-kaubandusettevõte kasutab OLTP-süsteemi laoseisu ja tellimustega seotud tehingute reaalajas jälgimiseks ning OLAP-süsteemi ostutrendide, klientide eelistuste ja hooajalise nõudluse analüüsimiseks. 

Allikas: https://www.datacamp.com/blog/oltp-vs-olap