Advanced SQL Modeling in RDBMS - SQL Spreadsheet part1 1.12.2009 2005-12-31
Your DanielName Vojtek Your Valčík Title Jakub Your Organization (Line #1) Query Languages Your Organization (Line #2) I
Agenda SQL Spreadsheet OLAP, ROLAP Spreadsheet klauzule Reference Spreadsheets Pořadí vyhodnocení formulí UPDATES v SQL Spreadsheet Motivační příklad
2
SQL Spreadsheet Tabulkový procesor - MS Excel, OO Calc + Snadné vkládání dat, formulí, pěkné UI + Tvorba grafů - Omezení (velikost dat, 2 dimenze) - Paralelní zpracování - Žádná standardizace
RDBMS Složité a neefektivní dotazování ve více rozměrných polích Mnohonásobná spojení (join)
SQL Spreadsheet Spreadsheet-like rozšíření k SQL 3
SQL Spreadsheet
Relace => n-dimenzionální pole formule definované přes buňky pole Adresace buněk je symbolická (dimenze) Rekurzivní reference a konvergenční podmínky Zahuštění Vyplnění mezer v řídkých datech Formule jsou definovány v nové SQL klauzuli Výsledek je relace a může být dále použit Paralelní zpracování 4
OLAP x OLTP
Online Transaction Processing Snadné a bezpečné ukládání Konkurenční prostředí Online Analytical Processing Jednorázově nahrávaná data Optimalizace pro (složité) dotazování Velké objemy dat, analýza obchodních trendů Tabulky nejsou normalizované (často redundantní uložení)
5
OLAP, ROLAP, MOLAP, HOLAP Multidimenzionální datový model MOLAP (Multidimensional OLAP) Vyžaduje před počítání a ukládání dat v kostce optimalizované multidimenzionální pole
Rychlé výpočty, menší objem dat (x ROLAP) Pomalé nahrávání dat Problémy s kostkami o více jak 10 dimenzích
ROLAP (Relational OLAP) Data jsou uložena relační databázi Pro výpočet je vygenerován SQL dotaz Škálovatelné, velké objemy dat, rychlé nahrávání dat(ETL) Tvorba agregovaných tabulek, výpočty musí SQL zvládat Není hierarchické indexování
HOLAP (Hybrid OLAP) 6
ROLAP Faktové tabulky, dimenzionální tabulky Dimenze určují „úhel“ pohledu – čas, produkt, zákazník Dimenze udržují hierarchie (vztah 1:N) Faktové tabulky (sledované ukazatele) – prodej, náklady Region 3 dimenze PRODEJ Rok Kvartál
Kategorie produktu
Gr an ula rita
Typ produktu Produkt
7
Schéma tabulek Schéma napojení – hvězda, sněhová vločka D_PRODUCT
D_TIME ID_CAS Datum Rok Kvartal Mesic ...
F_PRODEJ ID_CAS ID_REGION ID_PRODUKT SALES COST ...
ID_PRODUKT Nazev Kategori Subkategorie ...
D_REGION ID_REGION Nazev Uzemni jedn. ...
8
Spreadsheet klauzule Faktová tabulka f(t,r,p,s,c) Dimenze t – čas, r – region, p – produkt Metriky s – prodej, c – ceny Relační atributy – dimenze x metriky PARTITION(PBY) Rozdělení do disjunktních podmnožin
DIMENSION(DBY) Identifikují jedinečný řádek v každé části (partition) Buňka
MEASURES(MEA) - Počítané výrazy <existing parts of a query block> S PRE ADSHE ET PBY (cols) DBY (cols) M EA (cols) <processing options> (
, ,.., ) 9
Příklad SQL Spreadsheet Vyhodnocení po spojeních, agregacích, finální projekcí, před ORDER BY Array notation - s[‘vcr’, 2002]
SELECT r, p, t, s FROM f SPREADSHEET PBY(r) DBY (p, t) MEA (s) ( s[p='dvd',t=2002] =s[p='dvd',t=2001]*1.6, s[p='vcr',t=2002] =s[p='vcr',t=2000]+s[p='vcr',t=2001], s[p='tv', t=2002] =avg(s)[p='tv',1992
10
UPDATE, UPSERT SPREADSHEET PBY(r) DBY (p, t) MEA (s) ( UPSERT s[‘tv’, 2000] = s[‘black-tv’,2000] + s[‘white-tv’,2000] )
11
Reference spreadsheet Objekty s různou dimenzionalitou Prodej(region, product, time, sales) Budget (r – region, p – prediction factor) SELECT r, t, s FROM f GROUP by r, t SPREADSHEET REFERENCE budget ON (SELECT r, p FROM budget) DBY(r) MEA(p) DBY (r, t) MEA (sum(s) s) ( s[‘west’,2002]= p[‘west’]*s[‘west’ ,2001], s[‘east’,2002]= s[‘east’,2001]+s[‘east’,2000] ) 12
Pořadí vyhodnocení formulí Implicitně se formule vyhodnocují podle závislostí AUTOMATIC ORDER SPREADSHEET PBY(r) DBY (p, t) MEA (s) ( s[‘dvd’,2002] = s[‘dvd’,2000] + s[‘dvd’,2001] s[‘dvd’,2001] = 1000 )
SEQUENTIAL ORDER SPREADSHEET DBY(r,p,t) MEA(s) SEQUENTIAL ORDER (........)
13
UPDATES v SQL Spreadsheet
SQL Spreadsheet je součástí dotazu Nezpůsobuje žádné změny v uložených datech
Použití UPDATE a MERGE Obahuje explicitní spojení dotazu se spreadsheetem do cílové relace
WHERE u ANSI SQL UPDATE se vztahuje na data, která chceme aktualizovat Čistší, ale slabší
14
UPDATE, MERGE MERGE INTO f USING ( SELECT r, p, t, s FROM f SPREADSHEET PBY(r) DBY (p, t) MEA (s) ( UPSERT s[‘tv’, *] = s[‘black-tv’,cv()] + s[‘white-tv’,cv()] ) ) v ON f.r = v.r AND f.p = v.p AND f.t = v.t WHEN MATCHED THEN UPDATE SET f.s = v.s WHEN NOT MATCHED THEN INSERT VALUES (v.r, v.p, v.t, v.s) 15
Motivační příklad Analytik předpovídá prodej pro rok 2002 Prodej 'tv' v roce 2002 je spočítán podle obchodního trendu jako průměrný přírůstek mezi lety 1992 a 2001 Prodej 'vcr' v roce 2002 je součet prodeje 'vcr' za 2000 a 2001 Prodej 'dvd' v 2002 je průměr předchozích tří let Nakonec chce analytik do každého regionu přidat nového člena dimenze 'video' pro rok 2002 definovaného jako prodej 'tv' + prodej 'vcr' Předpokládáme že řádky pro 'tv', 'dvd' a 'vcr' existují
16
Motivační příklad SELECT r, p, t, s FROM f SPREADSHEET PBY(r) DBY (p, t) MEA (s) ( F1: UPDATE s[‘tv’,2002] = s[‘tv’,2001] + Slope(s,t)[‘tv’,1992<=t<=2001]*s[‘tv’,2001], F2: UPDATE s[‘vcr’, 2002] = s[‘vcr’,2000]+s[‘vcr’,2001], F3: UPDATE s[‘dvd’,2002] = (s[‘dvd’,1999]+s[‘dvd’,2000]+s[‘dvd’,2001])/3, F4: UPSERT s[‘video’, 2002] = s[‘tv’,2002]+s[‘vcr’,2002] ) 17
ANSI SQL
F1: agregační poddotaz, spojení do faktové tabulky f F2: dvojité spojení faktové tabulky sama se sebou F3: trojité spojení faktové tabulky sama se sebou F4: operaci UNION Složité generovat a neefektivní pro vykonání
18
SQL Spreadsheet Pouze jeden průchod dat pro vytvoření adresovatelné struktury hašovací tabulka nebo indexace všech formulí
SLOPE funkce – prochází data pro nalezení řádků vyhovujícím predikátu 1992 <= t <= 2001 Vyvodíme z omezení db že se jedná o Integer F1 se transformuje na F1: UPDATE s[‘tv’,2002] = s[‘tv’,2001]+ SLOPE(s,t)[‘tv’,t in (1992,...,2001)]* s[‘tv’,2001]
To umožnuje použít náhodný přístup a nemusejí se data procházet a zjišťovat zda vyhovují predikátu F2, F3, F4 využívají strukturu přímo 19
Motivační příklad 2 Určení maximální splátky hypotéky Příjem je tvořen 2 složkami – platem a výnosy z kapitálu Plat minus úroky hypotéky je daněn 38% Výnosy kapitálu jsou daněny 28% Čistý příjem = plat plus výnosy z kapitálu minus úroky minus daně Maximální odepsatelné výdaje v podobě úroků hypotéky jsou 30% čistého příjmu 20
Motivační příklad 2 SELECT account, b FROM ledger SPREADSHEET DBY (account) MEA (balance b) RULES IGNORE NAV ITERATE (100) UNTIL (ABS(b[‘net’] - PREVIOUS(b[‘net’])) < 0.01) ( F1: b[‘interest’] = b[‘net’] * 0.30, F2: b[‘net’] = b[‘salary’] +b[‘capital gains’] - b[‘interest’] - b[‘tax’], F3: b[‘tax’]=(b[‘salary’]-b[‘interest’])* 0.38 + b[‘capital gains’] * 0.28 ) 21
Závislosti formulí
F1
F2
F3
22
Výpočet pomocí iterací
Account Input Balance Result Balance Salary 100,000.00 100,000.00 Capital gains 15,000.00 15,000.00 Net 0 61,382.80 Tax 0 35,202.36 Interest 0 18,414.83
23
Part - 2
PART - 2
24
