Changes between Initial Version and Version 1 of Напредна тема

Timestamp:

06/14/26 00:24:32 (5 days ago)

Author:

231124

Comment:

--

Напредна тема

@@ +1 @@
+= DATA CUBE =
+= BankPaymentService =
+
+Напредни Бази на Податоци — Проектна Документација
+
+||= '''Предмет''' =|| Напредни Бази на Податоци ||
+||= '''Тема''' =|| Data Cube за банкарска аналитика ||
+||= '''Технологија''' =|| PostgreSQL · Star Schema · OLAP ||
+||= '''Опфат''' =|| Трансакции · Кредити · AML · Ризик · Перформанси ||
+
+== 1. Вовед — Цел и мотивација ==
+
+Data Cube претставува аналитички модел кој овозможува повеќедимензионална анализа на податоци. Наместо за секој извештај да се пишуваат посебни SQL барања, Data Cube овозможува со едно барање да се добијат агрегирани резултати по различни димензии, како што се време, филијала, валута или нивни комбинации.
+
+Во банкарски систем со голем обем на трансакции, ваквиот пристап значително ја забрзува анализата и овозможува поефикасно донесување одлуки базирани на податоци.
+
+Со Data Cube може лесно да се одговорат прашања како:
+
+ * Колкав бил прометот во EUR во одреден регион и период?
+ * Кој тип на кредит има највисок ризик од неплаќање?
+ * Кои филијали имаат најголем придонес во вкупниот ризик?
+ * Какви трендови се забележуваат на месечно и квартално ниво?
+
+Data Cube не го менува начинот на складирање на податоците, туку начинот на нивна анализа.
+
+== 2. Архитектура — Star Schema ==
+
+Решението е имплементирано со Star Schema архитектура. Во центарот се наоѓаат факт-табелите кои ги содржат главните деловни настани, додека околу нив се поставени димензионалните табели кои даваат контекст за анализата.
+
+Овој модел е широко користен во аналитички системи бидејќи нуди едноставна структура, добри перформанси и лесна интерпретација на податоците.
+
+||= '''Компонента''' =||= '''Улога''' =||= '''Примери''' =||
+|| Dimension Tables || Описни / контекстуални податоци || dim_time, dim_branch, dim_currency, dim_loan_type ||
+|| Fact Tables || Мерливи деловни настани || fact_transaction, fact_loan, fact_installment ||
+|| Summary Tables || Претходно агрегирани резултати || summary_monthly_transactions, summary_quarterly_loans ||
+
+=== 2.1 Димензионални табели ===
+
+==== dim_time — Временска димензија ====
+
+Содржи по еден запис за секој ден (5 години наназад, 2 години однапред). Вклучува атрибути за ден, недела, месец, квартал и година, генерирани преку generate_series. ON CONFLICT DO NOTHING обезбедува безбедно повторно извршување.
+
+{{{
+CREATE TABLE IF NOT EXISTS dim_time (
+    time_id       SERIAL PRIMARY KEY,
+    full_date     DATE        NOT NULL UNIQUE,
+    day_of_week   INT         NOT NULL,  -- 1=Пон … 7=Нед (ISO)
+    quarter_num   INT         NOT NULL,  -- 1–4
+    quarter_label VARCHAR(10) NOT NULL,  -- 'Q1 2026'
+    year_num      INT         NOT NULL,
+    is_weekend    BOOLEAN     NOT NULL,
+    is_holiday    BOOLEAN     NOT NULL DEFAULT FALSE
+);
+
+INSERT INTO dim_time (...)
+SELECT d, EXTRACT(ISODOW FROM d)::INT, ...,
+       'Q' || EXTRACT(QUARTER FROM d) || ' ' || EXTRACT(YEAR FROM d)
+FROM generate_series(
+    CURRENT_DATE - INTERVAL '5 years',
+    CURRENT_DATE + INTERVAL '2 years',
+    INTERVAL '1 day') AS t(d)
+ON CONFLICT (full_date) DO NOTHING;
+}}}
+
+==== dim_branch — Филијали со регион и категорија ====
+
+Секоја филијала се класифицира по регион (Sever/Centar/Jug) и по големина (LARGE/MEDIUM/SMALL) врз основа на бројот на активни вработени.
+
+{{{
+INSERT INTO dim_branch (branch_id, branch_name, city, region, branch_size)
+SELECT b.branch_id, b.branch_name, b.city,
+       CASE b.city
+           WHEN 'Skopje' THEN 'Centar'
+           WHEN 'Tetovo' THEN 'Sever'
+           WHEN 'Bitola' THEN 'Jug'  ...
+       END AS region,
+       CASE WHEN emp_count > 20 THEN 'LARGE'
+            WHEN emp_count > 10 THEN 'MEDIUM'
+            ELSE 'SMALL'
+       END AS branch_size
+FROM Branch b LEFT JOIN (...) e ON b.branch_id = e.branch_id;
+}}}
+
+Покрај овие димензионални табели, искористена е и '''dim_loan_type''' (четири типа кредити, секој со своја ризична категорија и типичен рок).
+
+||= '''Код''' =||= '''Назив''' =||= '''Ризик''' =||= '''Типичен рок''' =||
+|| HOME || Станбен кредит || LOW — низок || 240 месеци (20 год.) ||
+|| CAR || Автомобилски кредит || MEDIUM — среден || 60 месеци (5 год.) ||
+|| PERSONAL || Личен кредит || HIGH — висок || 36 месеци (3 год.) ||
+|| BUSINESS || Деловен кредит || MEDIUM — среден || 120 месеци (10 год.) ||
+
+Димензионалната табела '''dim_transaction_type''' ги дефинира различните типови на банкарски трансакции (на пример депозит, повлекување, плаќање, трансфер). Содржи информации за категоријата на трансакцијата и дали таа влијае врз состојбата на сметката, што овозможува анализа на приливи, одливи и други активности.
+
+Димензионалната табела '''dim_currency''' ги содржи валутите што се поддржани во системот и ги групира според географски регион. На тој начин може да се анализираат трансакциите на регионално ниво, без потреба секоја валута да се разгледува одделно.
+
+||= '''Регион''' =||= '''Примери''' =||
+|| EUROPE || EUR, MKD, GBP, CHF, TRY, RSD, PLN, SEK... ||
+|| AMERICA || USD, CAD, BRL, MXN, ARS, COP... ||
+|| ASIA || JPY, CNY, INR, SGD, AED, SAR, KRW, HKD... ||
+|| AFRICA || ZAR, EGP, KES, NGN, GHS, MAD... ||
+|| OTHER || Океанија и останати ||
+
+== 3. Факт-табели (Fact Tables) ==
+
+Факт-табелите ги содржат настаните кои се предмет на анализа и ги поврзуваат мерливите показатели со димензиите. Секој запис претставува конкретен настан: трансакција, кредит или рата.
+
+=== 3.1 fact_transaction — EUR конверзија и детектирање на сомнителни трансакции ===
+
+==== Конверзија во EUR преку LATERAL ====
+
+За да се обезбеди конзистентна анализа, секоја трансакција се конвертира во EUR во моментот на вчитување во Data Cube. Конверзијата се врши со користење на последниот достапен девизен курс кој е валиден на датумот на трансакцијата.
+
+Конвертираниот износ се пресметува со формулата: `amount_eur = amount / rate`
+
+'''Детектирање на сомнителни трансакции'''
+
+Овој пристап овозможува рана идентификација на потенцијално ризични трансакции и обезбедува основа за понатамошна анализа од страна на тимовите за ризик и усогласеност.
+
+{{{
+INSERT INTO fact_transaction (..., amount_eur, is_suspicious, ...)
+SELECT ...,
+    CASE WHEN er.rate IS NOT NULL
+         THEN ROUND(t.amount / er.rate, 2)
+         ELSE NULL
+    END AS amount_eur,
+    -- AML: >9000 EUR + статус PENDING/FAILED
+    CASE WHEN er.rate IS NOT NULL
+              AND (t.amount / er.rate) > 9000
+              AND t.status IN ('PENDING','FAILED')
+         THEN TRUE ELSE FALSE
+    END AS is_suspicious
+FROM Transaction t
+LEFT JOIN LATERAL (
+    SELECT rate FROM Exchange_rate
+    WHERE currency_id = t.currency_id
+      AND rate_date <= t.transaction_date
+    ORDER BY rate_date DESC LIMIT 1
+) er ON TRUE;
+}}}
+
+==== Индекси за оптимизација ====
+
+Поради големиот обем на податоци, врз табелата се дефинирани повеќе индекси со цел подобрување на перформансите на аналитичките барања.
+
+{{{
+CREATE INDEX idx_ft_time       ON fact_transaction(time_id);
+CREATE INDEX idx_ft_branch     ON fact_transaction(branch_dim_id);
+CREATE INDEX idx_ft_currency   ON fact_transaction(currency_dim_id);
+-- Partial index: само сомнителни трансакции (AML)
+}}}
+
+=== 3.3 fact_installment ===
+
+Секој ред во табелата претставува една рата од кредит. Табелата содржи информации за статусот на ратите и овозможува следење на навременоста на плаќањата, вклучувајќи ги и ратите со задоцнување.
+
+{{{
+CREATE TABLE IF NOT EXISTS fact_installment (
+    fact_inst_id    BIGSERIAL PRIMARY KEY,
+    time_id_due     INT   NOT NULL REFERENCES dim_time(time_id), -- кога ТРЕБА да се плати
+    time_id_paid    INT   REFERENCES dim_time(time_id),          -- кога НАВИСТИНА е платена
+    loan_type_id    INT   REFERENCES dim_loan_type(loan_type_id),
+    branch_dim_id   INT   REFERENCES dim_branch(branch_dim_id),
+    installment_id  BIGINT NOT NULL,
+    loan_id         BIGINT NOT NULL,
+    client_id       INT,
+    installment_num INT,
+    status          VARCHAR(20),
+    amount          DECIMAL(15,2) NOT NULL,
+    days_late       INT,     -- NULL ако не е платена; позитивен ако paid > due
+    is_paid         BOOLEAN NOT NULL DEFAULT FALSE,
+    is_late         BOOLEAN NOT NULL DEFAULT FALSE,
+    is_pending      BOOLEAN NOT NULL DEFAULT FALSE
+);
+
+-- days_late логика:
+CASE
+    WHEN li.paid_date IS NOT NULL AND li.paid_date > li.due_date
+        THEN (li.paid_date - li.due_date)
+    ELSE NULL   -- не измислуваме вредност ако не е платена уште
+END AS days_late
+}}}
+
+ * `is_defaulted` — TRUE ако постои барем една LATE рата во loan_installment.
+
+== 4. Аналитички барања — CUBE, ROLLUP, GROUPING SETS ==
+
+Една од клучните цели е овозможување брза и флексибилна анализа на податоците со OLAP техники. Наместо креирање посебни извештаи, Data Cube овозможува повеќедимензионални агрегати со едно SQL барање. Со користење на CUBE, ROLLUP и GROUPING SETS се добиваат извештаи на различни нивоа на деталност без дополнителна обработка, што ја забрзува анализата и ја зголемува флексибилноста при донесување одлуки.
+
+=== 4.1 CUBE — Трансакции по 4 димензии (16 комбинации) ===
+
+Ова барање овозможува анализа на вкупниот промет според:
+
+ * година
+ * квартал
+ * филијала
+ * валута
+
+Со користење на CUBE, се генерираат сите можни комбинации на агрегација помеѓу наведените димензии.
+
+Како резултат се добиваат:
+
+ * детални записи за секоја комбинација
+ * меѓузбирови по година
+ * меѓузбирови по филијала
+ * меѓузбирови по валута
+ * вкупен агрегат за целиот систем
+
+Овој извештај овозможува истовремено следење на трендовите од различни временски и организациски перспективи.
+
+{{{
+SELECT
+    dt.year_num, dt.quarter_label,
+    db.branch_name, dtt.type_name,
+    COUNT(*)                  AS transaction_count,
+    SUM(ft.amount_eur)        AS total_amount_eur,
+    ROUND(AVG(ft.amount_eur),2) AS avg_amount_eur,
+    SUM(ft.is_suspicious::INT)  AS suspicious_count,
+    ROUND(SUM(ft.is_completed::INT)::NUMERIC
+          / NULLIF(COUNT(*),0)*100,2) AS completion_rate_pct,
+    GROUPING(dt.year_num)      AS grp_year,
+    GROUPING(db.branch_name)   AS grp_branch
+FROM fact_transaction ft
+JOIN dim_time dt  ON ft.time_id = dt.time_id
+JOIN dim_branch db ON ft.branch_dim_id = db.branch_dim_id
+JOIN dim_transaction_type dtt ON ft.trx_type_dim_id = dtt.trx_type_dim_id
+GROUP BY CUBE(dt.year_num, dt.quarter_label, db.branch_name, dtt.type_name)
+ORDER BY dt.year_num NULLS LAST, dt.quarter_label NULLS LAST;
+}}}
+
+=== 4.2 ROLLUP — Хиерархиска временска анализа ===
+
+За разлика од CUBE, кој генерира сите можни комбинации, ROLLUP создава хиерархиски нивоа на агрегација (Година → Квартал → Месец). Овој пристап е особено корисен кога податоците природно следат временска или организациска хиерархија.
+
+{{{
+-- rollup_level: 3=Grand Total, 2=по Година, 1=по Квартал, 0=по Месец
+SELECT dt.year_num, dt.quarter_label, dt.month_num,
+    COUNT(*)           AS total_transactions,
+    SUM(ft.amount_eur) AS total_amount_eur,
+    GROUPING(dt.year_num) + GROUPING(dt.quarter_num)
+    + GROUPING(dt.month_num) AS rollup_level
+FROM fact_transaction ft
+JOIN dim_time dt ON ft.time_id = dt.time_id
+GROUP BY ROLLUP(dt.year_num, (dt.quarter_num, dt.quarter_label), dt.month_num);
+}}}
+
+=== 4.3 GROUPING SETS — Мулти-перспективна анализа ===
+
+Во практични сценарија не е секогаш потребно да се пресметаат сите комбинации што ги генерира CUBE. Наместо тоа се користи GROUPING SETS, кој овозможува експлицитно дефинирање само на потребните агрегации. Овој пристап обезбедува:
+
+ * подобри перформанси
+ * помала потрошувачка на ресурси
+ * пофокусирани и појасни резултати
+
+Во системот се дефинираат клучни аналитички перспективи како анализа по година и филијала, година и валута, регион, тип на кредит, како и глобални агрегати. Со GROUPING SETS се пресметуваат само овие релевантни комбинации, без непотребни пресметки.
+
+{{{
+GROUP BY GROUPING SETS (
+    (dt.quarter_label, dtt.type_name),           -- Per tip i kvartal
+    (dt.quarter_label, db.branch_name, db.region), -- Per filijala
+    (dt.quarter_label),                           -- Vkupno po kvartal
+    (db.branch_name, db.region)                   -- Vkupno po filijala
+)
+}}}
+
+== 5. AML — Детектирање на сомнителни трансакции ==
+
+Системот поддржува процеси за спречување перење пари (Anti-Money Laundering). Трансакција се означува како сомнителна (`is_suspicious = TRUE`) доколку нејзиниот EUR еквивалент надминува 9.000 EUR (стандарден ЕУ AML праг) и статусот е PENDING или FAILED.
+
+{{{
+-- AML CUBE: По ГОДИНА × КВАРТАЛ × ТИП × ФИЛИЈАЛА
+SELECT dt.year_num, dt.quarter_label,
+    dtt.type_name, db.branch_name, db.region,
+    COUNT(*)                             AS total_txns,
+    SUM(ft.is_suspicious::INT)           AS suspicious_count,
+    ROUND(SUM(CASE WHEN ft.is_suspicious
+        THEN ft.amount_eur ELSE 0 END),2) AS suspicious_eur,
+    ROUND(SUM(ft.is_suspicious::INT)::NUMERIC
+        / NULLIF(COUNT(*),0)*100,2)       AS suspicious_rate_pct,
+    GROUPING(dt.year_num)   AS grp_year,
+    GROUPING(db.branch_name) AS grp_branch
+FROM fact_transaction ft
+JOIN dim_time dt ...
+GROUP BY CUBE(dt.year_num, dt.quarter_label,
+              (dtt.type_name, dtt.category), (db.branch_name, db.region))
+HAVING SUM(ft.is_suspicious::INT) > 0  -- само ќелии со AML активност
+ORDER BY suspicious_count DESC NULLS LAST;
+}}}
+
+Partial индексот `idx_ft_suspicious` ги опфаќа само записите каде `is_suspicious = TRUE`, со што се намалува големината на индексот и се зголемува брзината на AML барањата.
+
+== 6. Window Functions — Тренд анализа ==
+
+Window Functions се користат за извршување на пресметки врз група редови (window) без да се изгубат поединечните записи, за разлика од GROUP BY кој ги агрегира редовите. Тие овозможуваат анализа на трендови преку пресметување на:
+
+ * кумулативни суми (YTD)
+ * споредба со претходни периоди (LAG за MoM анализа)
+ * подвижни просеци (Moving Average)
+ * рангирање (RANK)
+
+На овој начин може да се следи развојот на податоците низ времето додека деталните информации за секој период остануваат достапни.
+
+=== 6.1 Месечен тренд: YTD + MoM + Moving Average ===
+
+{{{
+WITH monthly_agg AS (
+    SELECT dt.year_num, dt.month_num,
+        COUNT(*) AS txn_count, SUM(ft.amount_eur) AS total_eur
+    FROM fact_transaction ft JOIN dim_time dt ON ft.time_id = dt.time_id
+    GROUP BY dt.year_num, dt.month_num
+)
+SELECT year_num, month_num, txn_count,
+    -- Year-to-Date кумулативна EUR сума
+    SUM(total_eur) OVER(
+        PARTITION BY year_num ORDER BY month_num
+        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+    ) AS ytd_cumulative_eur,
+    -- Month-over-Month промена (%)
+    ROUND((total_eur - LAG(total_eur) OVER(ORDER BY year_num, month_num))
+        / NULLIF(LAG(total_eur) OVER(ORDER BY year_num, month_num),0)*100
+    ,2) AS mom_eur_change_pct,
+    -- 3-месечен подвижен просек
+    ROUND(AVG(total_eur) OVER(
+        ORDER BY year_num, month_num
+        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
+    ),2) AS moving_avg_3m_eur,
+    -- Ранг на месецот во годината
+    RANK() OVER(PARTITION BY year_num ORDER BY total_eur DESC) AS rank_in_year
+FROM monthly_agg ORDER BY year_num, month_num;
+}}}
+
+== 7. Summary Tables и Refresh Procedure ==
+
+За системи со голем обем на податоци, повторливото пресметување на сложени агрегати може да биде временски интензивно. Summary табелите ги складираат однапред пресметаните резултати, а refresh процедурата ги освежува периодично.
+
+||= '''Компонента''' =||= '''Улога''' =||= '''Примери''' =||
+|| summary_monthly_transactions || Месечни агрегати по филијала/тип || txn_count, total_eur, suspicious_count ||
+|| summary_quarterly_loans || Квартални кредитни агрегати || loan_count, default_rate_pct, avg_ltv ||
+|| summary_quarterly_debt || Квартална статистика на доцнења || late_count, avg_days_late, amount_overdue ||
+
+=== 7.1 Refresh Procedure ===
+
+Процедурата `refresh_data_cube_summaries()` се извршува периодично во временски интервали кога системот е помалку оптоварен (пр. во ноќните часови), со цел да се минимизира влијанието врз оперативните активности. Освежувањето на секоја summary табела се реализира преку пристапот TRUNCATE + INSERT, при што постојните агрегирани податоци се заменуваат со новопресметани вредности. Овој пристап обезбедува конзистентност, точност и усогласеност на податоците со моменталната состојба во системот.
+
+{{{
+CREATE OR REPLACE PROCEDURE refresh_data_cube_summaries()
+LANGUAGE plpgsql AS $$
+BEGIN
+    -- 1. Месечни трансакции
+    TRUNCATE summary_monthly_transactions;
+    INSERT INTO summary_monthly_transactions (...)
+    SELECT dt.year_num, dt.month_num, dt.quarter_label,
+        db.branch_name, db.region, dtt.type_name,
+        COUNT(*), ROUND(SUM(ft.amount_eur),2),
+        SUM(ft.is_suspicious::INT), CURRENT_TIMESTAMP
+    FROM fact_transaction ft
+    JOIN dim_time dt ... JOIN dim_branch db ... JOIN dim_transaction_type dtt ...
+    GROUP BY dt.year_num, dt.month_num, dt.quarter_label,
+             db.branch_name, db.region, dtt.type_name;
+
+    -- 2. Квартални кредити
+    TRUNCATE summary_quarterly_loans;
+    INSERT INTO summary_quarterly_loans (...) SELECT ...;
+
+    -- 3. Квартални доцнења
+    TRUNCATE summary_quarterly_debt;
+    INSERT INTO summary_quarterly_debt (...) SELECT ...;
+
+    RAISE NOTICE 'Refreshed at %', CURRENT_TIMESTAMP;
+END;
+$$;
+
+-- Повик: CALL refresh_data_cube_summaries();
+}}}
+
+== 8. Вредност и придобивки од решението ==
+
+=== 8.1 Поддршка при донесување одлуки ===
+
+Системот обезбедува брз и едноставен пристап до клучни деловни информации поврзани со трансакциската активност, кредитното портфолио, ризичните индикатори и доцнењата во отплатата на кредити. Наместо податоците да се собираат и обработуваат рачно, корисниците можат веднаш да добијат аналитички извештаи и показатели потребни за носење информирани одлуки.
+
+=== 8.2 Управување со ризик ===
+
+Преку анализата на NPL (Non-Performing Loans), LTV соодносот и детекцијата на сомнителни трансакции, системот овозможува рано идентификување на потенцијални проблеми. Анализите можат да се извршуваат на различни нивоа, вклучувајќи региони и филијали.
+
+=== 8.3 Оперативна ефикасност ===
+
+ * Автоматизирано освежување — без рачна интервенција
+ * Partial индекси — помала меморија, побрзи пребарувања на сомнителни трансакции
+ * Summary табели — извештаи за секунди наместо минути
+
+=== 8.4 Заклучок ===
+
+Во рамките на овој проект е дизајнирано и имплементирано Data Warehouse решение базирано на Star Schema архитектура и OLAP концепти. Системот користи димензионални и факт-табели за структурирање на податоците, додека аналитичките барања се реализирани преку CUBE, ROLLUP, GROUPING SETS и Window Functions.
+
+Преку summary табели, partial индекси и автоматска refresh процедура се обезбедуваат високи перформанси и точност на извештаите. Решението демонстрира практична примена на концептите од Data Warehousing и Business Intelligence во реален банкарски контекст, со цел изградба на скалабилна и аналитички ефикасна платформа.
+
+----
+''Data Cube — BankPaymentService''