Changes between Version 3 and Version 4 of Напредна тема
- Timestamp:
- 06/14/26 00:44:15 (4 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Напредна тема
v3 v4 57 57 ==== dim_branch — Филијали со регион и категорија ==== 58 58 59 Секоја филијала се класифицира по регион (Sever / Centar / Jug) и по големина (LARGE /MEDIUM/SMALL) врз основа на бројот на активни вработени.59 Секоја филијала се класифицира по регион (Sever / Centar / Jug) и по големина (LARGE / MEDIUM / SMALL) врз основа на бројот на активни вработени. 60 60 61 61 {{{ … … 74 74 }}} 75 75 76 ==== Останати димензионални табели ==== 77 76 78 Покрај овие димензионални табели, искористена е и '''dim_loan_type''' (четири типа кредити, секој со своја ризична категорија и типичен рок). 77 79 … … 105 107 Конвертираниот износ се пресметува со формулата: `amount_eur = amount / rate` 106 108 107 '''Детектирање на сомнителни трансакции''' 109 ==== Детектирање на сомнителни трансакции ==== 108 110 109 111 Овој пристап овозможува рана идентификација на потенцијално ризични трансакции и обезбедува основа за понатамошна анализа од страна на тимовите за ризик и усогласеност. … … 116 118 ELSE NULL 117 119 END AS amount_eur, 118 -- AML: >9000 EUR + статус PENDING/FAILED119 120 CASE WHEN er.rate IS NOT NULL 120 121 AND (t.amount / er.rate) > 9000 … … 139 140 CREATE INDEX idx_ft_branch ON fact_transaction(branch_dim_id); 140 141 CREATE INDEX idx_ft_currency ON fact_transaction(currency_dim_id); 141 -- Partial index: само сомнителни трансакции (AML)142 142 }}} 143 143 … … 149 149 CREATE TABLE IF NOT EXISTS fact_installment ( 150 150 fact_inst_id BIGSERIAL PRIMARY KEY, 151 time_id_due INT NOT NULL REFERENCES dim_time(time_id), -- кога ТРЕБА да се плати152 time_id_paid INT REFERENCES dim_time(time_id), -- кога НАВИСТИНА е платена151 time_id_due INT NOT NULL REFERENCES dim_time(time_id), 152 time_id_paid INT REFERENCES dim_time(time_id), 153 153 loan_type_id INT REFERENCES dim_loan_type(loan_type_id), 154 154 branch_dim_id INT REFERENCES dim_branch(branch_dim_id), … … 159 159 status VARCHAR(20), 160 160 amount DECIMAL(15,2) NOT NULL, 161 days_late IN T, -- NULL ако не е платена; позитивен ако paid > due161 days_late INТ, 162 162 is_paid BOOLEAN NOT NULL DEFAULT FALSE, 163 163 is_late BOOLEAN NOT NULL DEFAULT FALSE, … … 165 165 ); 166 166 167 -- days_late логика:168 167 CASE 169 168 WHEN li.paid_date IS NOT NULL AND li.paid_date > li.due_date 170 169 THEN (li.paid_date - li.due_date) 171 ELSE NULL -- не измислуваме вредност ако не е платена уште170 ELSE NULL 172 171 END AS days_late 173 172 }}} … … 225 224 226 225 {{{ 227 -- rollup_level: 3=Grand Total, 2=по Година, 1=по Квартал, 0=по Месец228 226 SELECT dt.year_num, dt.quarter_label, dt.month_num, 229 227 COUNT(*) AS total_transactions, … … 248 246 {{{ 249 247 GROUP BY GROUPING SETS ( 250 (dt.quarter_label, dtt.type_name), -- P ertip i kvartal251 (dt.quarter_label, db.branch_name, db.region), -- P erfilijala248 (dt.quarter_label, dtt.type_name), -- Po tip i kvartal 249 (dt.quarter_label, db.branch_name, db.region), -- Po filijala 252 250 (dt.quarter_label), -- Vkupno po kvartal 253 251 (db.branch_name, db.region) -- Vkupno po filijala … … 255 253 }}} 256 254 257 == 5. AML — Детектирање на сомнителни трансакции == 258 259 Системот поддржува процеси за спречување перење пари (Anti-Money Laundering). Трансакција се означува како сомнителна (`is_suspicious = TRUE`) доколку нејзиниот EUR еквивалент надминува 9.000 EUR (стандарден ЕУ AML праг) и статусот е PENDING или FAILED. 260 261 {{{ 262 -- AML CUBE: По ГОДИНА × КВАРТАЛ × ТИП × ФИЛИЈАЛА 263 SELECT dt.year_num, dt.quarter_label, 264 dtt.type_name, db.branch_name, db.region, 265 COUNT(*) AS total_txns, 266 SUM(ft.is_suspicious::INT) AS suspicious_count, 267 ROUND(SUM(CASE WHEN ft.is_suspicious 268 THEN ft.amount_eur ELSE 0 END),2) AS suspicious_eur, 269 ROUND(SUM(ft.is_suspicious::INT)::NUMERIC 270 / NULLIF(COUNT(*),0)*100,2) AS suspicious_rate_pct, 271 GROUPING(dt.year_num) AS grp_year, 272 GROUPING(db.branch_name) AS grp_branch 273 FROM fact_transaction ft 274 JOIN dim_time dt ... 275 GROUP BY CUBE(dt.year_num, dt.quarter_label, 276 (dtt.type_name, dtt.category), (db.branch_name, db.region)) 277 HAVING SUM(ft.is_suspicious::INT) > 0 -- само ќелии со AML активност 278 ORDER BY suspicious_count DESC NULLS LAST; 279 }}} 280 281 Partial индексот `idx_ft_suspicious` ги опфаќа само записите каде `is_suspicious = TRUE`, со што се намалува големината на индексот и се зголемува брзината на AML барањата. 282 283 == 6. Window Functions — Тренд анализа == 255 == 5. Window Functions — Тренд анализа == 284 256 285 257 Window Functions се користат за извршување на пресметки врз група редови (window) без да се изгубат поединечните записи, за разлика од GROUP BY кој ги агрегира редовите. Тие овозможуваат анализа на трендови преку пресметување на: … … 292 264 На овој начин може да се следи развојот на податоците низ времето додека деталните информации за секој период остануваат достапни. 293 265 294 === 6.1 Месечен тренд: YTD + MoM + Moving Average ===266 === 5.1 Месечен тренд: YTD + MoM + Moving Average === 295 267 296 268 {{{ … … 302 274 ) 303 275 SELECT year_num, month_num, txn_count, 304 -- Year-to-Date кумулативна EUR сума305 276 SUM(total_eur) OVER( 306 277 PARTITION BY year_num ORDER BY month_num 307 278 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 308 279 ) AS ytd_cumulative_eur, 309 -- Month-over-Month промена (%)310 280 ROUND((total_eur - LAG(total_eur) OVER(ORDER BY year_num, month_num)) 311 281 / NULLIF(LAG(total_eur) OVER(ORDER BY year_num, month_num),0)*100 312 282 ,2) AS mom_eur_change_pct, 313 -- 3-месечен подвижен просек314 283 ROUND(AVG(total_eur) OVER( 315 284 ORDER BY year_num, month_num 316 285 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 317 286 ),2) AS moving_avg_3m_eur, 318 -- Ранг на месецот во годината319 287 RANK() OVER(PARTITION BY year_num ORDER BY total_eur DESC) AS rank_in_year 320 288 FROM monthly_agg ORDER BY year_num, month_num; 321 289 }}} 322 290 323 == 7. Summary Tables и Refresh Procedure ==291 == 6. Summary Tables и Refresh Procedure == 324 292 325 293 За системи со голем обем на податоци, повторливото пресметување на сложени агрегати може да биде временски интензивно. Summary табелите ги складираат однапред пресметаните резултати, а refresh процедурата ги освежува периодично. … … 330 298 || summary_quarterly_debt || Квартална статистика на доцнења || late_count, avg_days_late, amount_overdue || 331 299 332 === 7.1 Refresh Procedure ===300 === 6.1 Refresh Procedure === 333 301 334 302 Процедурата `refresh_data_cube_summaries()` се извршува периодично во временски интервали кога системот е помалку оптоварен (пр. во ноќните часови), со цел да се минимизира влијанието врз оперативните активности. Освежувањето на секоја summary табела се реализира преку пристапот TRUNCATE + INSERT, при што постојните агрегирани податоци се заменуваат со новопресметани вредности. Овој пристап обезбедува конзистентност, точност и усогласеност на податоците со моменталната состојба во системот. … … 338 306 LANGUAGE plpgsql AS $$ 339 307 BEGIN 340 -- 1. Месечни трансакции341 308 TRUNCATE summary_monthly_transactions; 342 309 INSERT INTO summary_monthly_transactions (...) … … 350 317 db.branch_name, db.region, dtt.type_name; 351 318 352 -- 2. Квартални кредити353 319 TRUNCATE summary_quarterly_loans; 354 320 INSERT INTO summary_quarterly_loans (...) SELECT ...; 355 321 356 -- 3. Квартални доцнења357 322 TRUNCATE summary_quarterly_debt; 358 323 INSERT INTO summary_quarterly_debt (...) SELECT ...; … … 362 327 $$; 363 328 364 -- Повик:CALL refresh_data_cube_summaries();365 }}} 366 367 == 8. Вредност и придобивки од решението ==368 369 === 8.1 Поддршка при донесување одлуки ===329 -- CALL refresh_data_cube_summaries(); 330 }}} 331 332 == 7. Вредност и придобивки од решението == 333 334 === 7.1 Поддршка при донесување одлуки === 370 335 371 336 Системот обезбедува брз и едноставен пристап до клучни деловни информации поврзани со трансакциската активност, кредитното портфолио, ризичните индикатори и доцнењата во отплатата на кредити. Наместо податоците да се собираат и обработуваат рачно, корисниците можат веднаш да добијат аналитички извештаи и показатели потребни за носење информирани одлуки. 372 337 373 === 8.2 Управување со ризик ===338 === 7.2 Управување со ризик === 374 339 375 340 Преку анализата на NPL (Non-Performing Loans), LTV соодносот и детекцијата на сомнителни трансакции, системот овозможува рано идентификување на потенцијални проблеми. Анализите можат да се извршуваат на различни нивоа, вклучувајќи региони и филијали. 376 341 377 === 8.3 Оперативна ефикасност ===342 === 7.3 Оперативна ефикасност === 378 343 379 344 * Автоматизирано освежување — без рачна интервенција … … 381 346 * Summary табели — извештаи за секунди наместо минути 382 347 383 === 8.4 Заклучок ===348 === 7.4 Заклучок === 384 349 385 350 Во рамките на овој проект е дизајнирано и имплементирано Data Warehouse решение базирано на Star Schema архитектура и OLAP концепти. Системот користи димензионални и факт-табели за структурирање на податоците, додека аналитичките барања се реализирани преку CUBE, ROLLUP, GROUPING SETS и Window Functions. 386 351 387 352 Преку summary табели, partial индекси и автоматска refresh процедура се обезбедуваат високи перформанси и точност на извештаите. Решението демонстрира практична примена на концептите од Data Warehousing и Business Intelligence во реален банкарски контекст, со цел изградба на скалабилна и аналитички ефикасна платформа. 388 389 ----390 ''Data Cube — BankPaymentService''
