| Version 6 (modified by , 4 days ago) ( diff ) |
|---|
DATA CUBE - BankPaymentService
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,
quarter_num INT NOT NULL,
quarter_label VARCHAR(10) NOT NULL,
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,
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);
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 INТ,
is_paid BOOLEAN NOT NULL DEFAULT FALSE,
is_late BOOLEAN NOT NULL DEFAULT FALSE,
is_pending BOOLEAN NOT NULL DEFAULT FALSE
);
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 создава хиерархиски нивоа на агрегација (Година → Квартал → Месец). Овој пристап е особено корисен кога податоците природно следат временска или организациска хиерархија.
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), -- Po tip i kvartal
(dt.quarter_label, db.branch_name, db.region), -- Po filijala
(dt.quarter_label), -- Vkupno po kvartal
(db.branch_name, db.region) -- Vkupno po filijala
)
5. Window Functions — Тренд анализа
Window Functions се користат за извршување на пресметки врз група редови (window) без да се изгубат поединечните записи, за разлика од GROUP BY кој ги агрегира редовите. Тие овозможуваат анализа на трендови преку пресметување на:
- кумулативни суми (YTD)
- споредба со претходни периоди (LAG за MoM анализа)
- подвижни просеци (Moving Average)
- рангирање (RANK)
На овој начин може да се следи развојот на податоците низ времето додека деталните информации за секој период остануваат достапни.
5.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,
SUM(total_eur) OVER(
PARTITION BY year_num ORDER BY month_num
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_cumulative_eur,
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,
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;
6. 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 |
6.1 Refresh Procedure
Процедурата refresh_data_cube_summaries() се извршува периодично во временски интервали кога системот е помалку оптоварен (пр. во ноќните часови), со цел да се минимизира влијанието врз оперативните активности. Освежувањето на секоја summary табела се реализира преку пристапот TRUNCATE + INSERT, при што постојните агрегирани податоци се заменуваат со новопресметани вредности. Овој пристап обезбедува конзистентност, точност и усогласеност на податоците со моменталната состојба во системот.
CREATE OR REPLACE PROCEDURE refresh_data_cube_summaries()
LANGUAGE plpgsql AS $$
BEGIN
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;
TRUNCATE summary_quarterly_loans;
INSERT INTO summary_quarterly_loans (...) SELECT ...;
TRUNCATE summary_quarterly_debt;
INSERT INTO summary_quarterly_debt (...) SELECT ...;
RAISE NOTICE 'Refreshed at %', CURRENT_TIMESTAMP;
END;
$$;
-- CALL refresh_data_cube_summaries();
7. Вредност и придобивки од решението
7.1 Поддршка при донесување одлуки
Системот обезбедува брз и едноставен пристап до клучни деловни информации поврзани со трансакциската активност, кредитното портфолио, ризичните индикатори и доцнењата во отплатата на кредити. Наместо податоците да се собираат и обработуваат рачно, корисниците можат веднаш да добијат аналитички извештаи и показатели потребни за носење информирани одлуки.
7.2 Управување со ризик
Преку анализата на NPL (Non-Performing Loans), LTV соодносот и детекцијата на сомнителни трансакции, системот овозможува рано идентификување на потенцијални проблеми. Анализите можат да се извршуваат на различни нивоа, вклучувајќи региони и филијали.
7.3 Оперативна ефикасност
- Автоматизирано освежување — без рачна интервенција
- Partial индекси — помала меморија, побрзи пребарувања на сомнителни трансакции
- Summary табели — извештаи за секунди наместо минути
7.4 Заклучок
Во рамките на овој проект е дизајнирано и имплементирано Data Warehouse решение базирано на Star Schema архитектура и OLAP концепти. Системот користи димензионални и факт-табели за структурирање на податоците, додека аналитичките барања се реализирани преку CUBE, ROLLUP, GROUPING SETS и Window Functions.
Преку summary табели, partial индекси и автоматска refresh процедура се обезбедуваат високи перформанси и точност на извештаите. Решението демонстрира практична примена на концептите од Data Warehousing и Business Intelligence во реален банкарски контекст, со цел изградба на скалабилна и аналитички ефикасна платформа.
