Lekérdezés - ELTE



Query by Excel - Lekérdezés Excel használatával

Andrew Witkowski, Srikanth Bellamkonda, Tolga Bozkaya, Aman Naimat, Lei Sheng,

Sankar Subramanian, Allison Waingold.

Oracle, 500 Oracle Parkway, Redwood Shores CA 94065

Firstname.Lastname@

Fordította: Juhász Miklós (juhasz@inf.elte.hu)

Kivonat

A táblázatkezelők, és különösen a MS Excel, jól megalapozott elemző eszközök. Vonzó felhasználói felületet nyújtanak, egyszerűen használható számítási modellt adnak és nagyfokú interaktivitást kínálnak a „Mi történik, ha...?” típusú elemzésekhez. Azonban, ellentétben a relációs adatbázisokat kezelő rendszerekkel (továbbiakban RDBMS - Relational Data Base Management System), nem rendelkeznek központi tárolóval, ennélfogva nem biztosítják az Excelben felépített modellek megosztását, ez pedig ugyanazon táblázat több példányban való elburjánzásához vezet. Ezenkívül a táblázatkezelők nem támogatják a skálázható számításokat, a párhuzamosság is hiányzik belőlük. Hogy elejét vegyük a megosztásbeli és skálázhatóság problémájának, javasoljuk az Excel számítások SQL-be való lefordítását. Az elemző be tudja importálni az adatokat a relációs rendszerből, a számításokat ezeken keresztül tudja megadni az ismert Excel formulák használatával, majd mindez fordításra kerülne és a relációs tárolóban SQL nézetként (SQL View) tárolódna az importált adatokon keresztül. Az Excel számítás így a relációs rendszer által kerül végrehajtásra. A modell szerkesztéséhez az elemző az adatokat visszatöltheti Excelbe, módosíthatja az Excelben és újra letárolhatja őket SQL nézetként. Erre a rendszerre Query by Excel-ként (Lekérdezés Excelből) fogunk (röviden QBX) hivatkozni.

1 Bevezetés

A táblázatkezelők, és különösen a MS Excel [5], [6], jól megalapozott üzleti és személyes felhasználású elemző eszközök. Vonzó felhasználói felületet kínálnak grafikákkal és testre szabható menükkel, egyszerűen használható számítási modellt adnak és nagyfokú interaktivitást kínálnak a „Mi történik, ha...?” típusú elemzésekhez. A táblázatkezelők sok gazdasági, statisztikai, mérnöki és matematikai függvényt kínálnak, csakúgy mint adat-átalakító szolgáltatásokat, mint például pivot, aggregáció, keresések, stb.

Mégis, a táblázatkezelőknek mint számításokat végző szerkezeteknek, komoly hiányosságaik vannak. Hiányozik a jól definiált algebra és a számítási mód titkos. Nagy adathalmaz esetén skálázhatósági probléma lép fel. A táblázatkezelők elkülönített adatforrásokkal, például RDBMS, töredezett, nem összegzett képet nyújtanak egy cégről.

Másfelől léteznek számítást végző motorok skálázhatósági vagy töredezettségi problémák nélkül, jól definiált számítási algebrával, pl. az OLAP [7],[8] Statisztikai [12] és Relációs motorok. Ezek azonban hátrányban vannak az interaktivitás, a grafikus szemléltető képesség és a számítási nyelv népszerűségét illetően. Ez a tanulmány egy olyan rendszert tárgyal, mely kombinálja az Excel prezentációs és interaktív modellezési képességét, valamint egy megalapozott Elemzői Kiterjesztést tartalmazó Relációs Motor számítási teljesítményét és skálázhatóságát. A rendszer neve Query by Excel (röviden: QBX), és a következő tulajdonságokkal bír:

• Az elemzők az ismert Excel formulákat használva megépíthetik és szerkeszthetik a modellt. Ez a modell ezután automatikusan lefordul SQL-be és publikusan rendelkezésre álló relációs nézetként kerül letárolásra.

• Az elemzők a táblázatkezelőben relációs forrásokat jelölnek ki, melyeket RTábláknak hívnak. Ez a terület egy relációs tábla képét (részletét) tartalmazza. Az RTábla átalakítható egy másik RTáblává a Külső Join, Kiválasztás, Projekció és Aggregáció műveleteknek megfelelő Excel műveletekkel. Ennél fogva a felhasználók relációs műveleteket hajthatnak végre Excelből, SQL használata nélkül.

• Az elemzők az Excel táblába illeszkedő tábla-mintákon írják az Excel formulákat. Ám mikor a műveletek lefordulnak SQL-be, a teljes táblán lépnek működésbe, így az RDBMS hatékonyságát (méretben és párhuzamosságban) alkalmazza Excel modellekre.

• Ahhoz, hogy relációs adatokat importáljanak az Excelbe, jelenleg a felhasználók Relációs Lekérdező Építőket [10],[11] használnak, mely némi SQL ismeretet is feltételez. A QBX ezt elrendezi, így a lekérdezés felépítése az Excel formulák használatával történik meg.

• Az üzleti jelentéskészítő eszközök [9] az Excel táblázatokból lefordított relációs nézetekhez férnek hozzá. Az Excel-összevonás olyan egyszerűvé válik, mint a nézetek kombinálása SQL műveletekkel, pl. join, union, stb.

A tanulmány elkészítését az SQL Modell [1],[2] és SQL Pivot Operátor [3] elemzőeszközök motiválta, melyek az Excel formulák hatékony, SQL-ben történő kifejezésére szolgáltatnak nyelvet.

A tanulmány szerkezete a következő. A 2. fejezet összefoglalva leírja az Excel fordításához használt SQL kiterjesztést. A 3. fejezet a magas szintű felépítést és a relációs sémákat ismerteti. A 4. fejezet az Excel képletek SQL-re fordítását tartalmazza, az 5. fejezet az optimalizálást mutatja be. A 6. fejezett a lefordított modellek teljesítményét tárgyalja. A 7. fejezet levonja a következtetéseket és javaslatokat tesz a további kutatások témáira.

2. SQL kiterjesztések

Ez a rész összefoglalva leírja az SQL Modell [1],[2] kiterjesztést (az Oracle 10g Release-ben áll rendelkezésre), melyet az Excelben használunk az SQL fordításhoz. A példáink csillag sémára alapulnak háromdimenziós táblákkal:time_dim, prod_dim, region_dim és a képlet: f(t, r, p, s, c). Az f a következők szerint kerül dimenzionálásra: idő (t), régió (r) termék (p) oszlopok, valamint eladások (s) és költségek (c), mint értékek.

[1] bemutatott egy SQL kiterjesztést analitikai feldolgozásra „SQL Spreadsheet Clause” néven, melyet később SQL Modell névre kereszteltek át. Ez a kiterjesztés lehetővé teszi a felhasználóknak, hogy egy relációt többdimenziós tömbként tekintsenek meg és többszörös képleteket definiáljanak rajtuk. Az SQL Modell felismeri a partíciókat, dimenziókat és az érték oszlopokat a lekérdezés eredményén belül. A partíció (PBY) oszlopok a relációt független részhalmazokra osztja. A dimenzió (DBY) oszlopok egyedileg azonosítják a sorokat minden partícióban, melyeket celláknak hívunk. Az érték (MEA) oszlopok a modell által kiszámított kifejezések. A képletek sorrendje a cellák kiszámítását írja le, a PBY, DBY és MEA definíciókat követi. Az SQL Modell felépítése a következő:

MODEL PBY (oszlopok) DBY (oszlopok) MEA (oszlopok)

(

, ,..,

)

A cellákra tömböknél használatos jelölési rendszerrel hivatkozunk a dimenzió oszlopokkal, melyeket az állítások osztanak fel, például s[p=’dvd’, t=2002] vagy s[’dvd’, 2002] rövidítve. Egy képlet kifejezések célcellákba történő, értékeken értelmezett besorolását jelenti. Például:

SELECT r, p, t, s FROM f

MODEL PBY(r) DBY (p, t) MEA (s)

( s[’vcr’,2002]=s[‘vcr’,2000]+s[’vcr’,2001],

s[’tv’, 2002] = avg(s)[’tv’,1992 SQL Translation” fordítja le a táblázatot SQL nézetre. A rendszerben két felhasználói típust különböztetünk meg: az Excel elemző az Excelt használja a modell megalkotására az alapokat jelentő tranzakciós adatokon; az RDBMS felhasználók és alkalmazások pedig felhasználják az Excel elemzők által létrehozott modellt.

1. ábra - A QBX felépítése

[pic]

A „Database Schema Component” az Excel kétdimenziós tábláját és a relációs táblákban megvalósított relációs objektumokat:

Az Excels(eid, name, owner, ExcelBinary, SQLView) sorai a tárolt Excel táblázatokról tartalmaznak információkat, így a nevüket (name), belső Excel azonosítójukat (eid), tulajdonosukat (owner) és az Excel xls fájlt egy „lob” oszlopban (ExcelBinary). Szintén tartalmazza annak az SQL nézetnek a nevét, mely az Excel SQL fordítását tartalmazza (SQLView oszlop). Ez a nézet a külvilág számára is rendelkezésre áll.

Cells(eid, sheet, row, col, x, f) egy Excel táblázat celláit tárolja. Tartalmazza a cella koordinátáit (sheet (lap), row (sor), col (oszlop)), valamint az értékét (x) és a képletét (f). Az üres cellák nem tárolódnak. Az adatokat sztringként tároljuk és az SQL-re bízzuk az implicit konverziót.

RTables(eid, RTable, sheet, row, col, sample, RTableView,...) tárolja az Excelbe importált RTáblák nevét (RTable), az Excelben elfoglalt helyét (sheet, row, column), paramétereket (pl. sample (minta) mérete), az RTáblát megvalósító SQL nézet nevét (RTableView) és más információkat, mint például oszlop típusok. Az RTáblát megvalósító SQL szöveg a publikus katalógusban kerül letárolásra.

Illusztrációként nézzük meg a következő SQL táblázatot.

2. ábra - 1. példa

[pic]

Ezen Excel táblázat esetén egy bejegyzés jön létre az „Excels” táblában, majd letárolunk öt cellát: C1, C2, C3, D1 és D3 a „Cells” táblába. A C1, C2, C3, D1 cellák konstansokat tartalmaznak (a Cells.x oszlopban), a D3-as cella pedig egy képletet tartalmaz (Cells.f oszlop). Ez az Excel táblázat nem tartalmaz RTáblákat, így az „RTables” tábla üres. Az „Excels” szintén tartalmazza annak az SQL nézetnek a nevét, amely megvalósítja ezen táblázat számításait.

A QBX által generált objektumok egy publikus mappában tárolják az Excel számításait megvalósító nézeteket. Példaként a fentiekről a következő nézetet tároljuk:

Q1

CREATE VIEW Example1 AS

SELECT sheet, row, col, x FROM cells

MODEL DBY (sheet,row,col) MEA (x) AUTOMATIC ORDER

(

x[1,3,4] = x[1,3,3] - x[1,2,3] -- D3 = C3-C2

)

Az „RDBMS Interaction and Modeling” komponens egy VBA-ban írt Excel bővítmény. Ez a komponens egy menüvezérelt felületet jelenít meg; a QBX nevű főmenü tővé teszi az RTáblák kezelését és Excelek letárolását, fordítását és betöltését az adatbázisból. Két főbb almenüje van:

1. QBX -> RTables menü az RTáblák kezelésére. Lehetővé teszi relációs táblák betöltését (LoadTable menüpont), oszlop hozzáadása és törlése (Add Column/Drop Column), átalakított RTábla mentése relációs nézetként (SaveTable), és Excel területek mentése relációs nézetként (SaveRegions).

2. QBX -> Spreadsheet menü. A „Store” lefordítja az Excel táblázatot SQL-be és letárolja az adatbázisba az eredeti Excel táblázattal együtt. A „Load” betölt egy korábban letárolt Excel táblázatot.

Ez a komponens aktiválja a „Translation and Persistence” (Fordítás és Beállítás) komponenst a munka zömének elvégzésére.

A „Persistence” (Beállítás) komponens beállítja az információkat az RTáblákról, az Excelbeli helyükről, az őket megvalósító SQL nézetekről, a használt cellák értékeiről és az RDBMS-beli képletekről. A komponens VBA-ban írt Excel bővítmény, mely OLE objektumokat használ. A következő lépések történnek az Excel munkalap feldolgozásakor:

1.) Egy új Excel azonosító készül (eid) és az Excel xls fájl letárolásra kerül az „Excels” táblában a nevével, stb.

2.) A használt, de RTáblához nem tartozó cellák értékei és képletei letárolódnak a „Cells” táblában.

3.) Az Excel, SQL fordító (lásd 4. fejezet) indítása és a keletkező relációs nézet, mely megvalósítja az Excel számításokat, eltárolódik az RDBMS nyilvános katalógusban. A neve az „Excels” táblába kerül.

A „Traslation” (Fordítás) komponens két típusú fordítást valósít meg: Excel képleteket a „Cells” táblán keresztül SQL nézetbe; valamint a nézeteken keresztül valósítja meg az RTáblákat. A komponens az RDBMS-ben fut, tárolt Java eljárásként. A fordítást részletesen a 4. fejezet tárgyalja.

4. Excel fordítása SQL-be

A QBX-ben megpróbáltuk minimalizálni az Excel kiterjesztéseket. Fejlett Excel képességeket használtunk ki a relációs műveletek szimulálására (aggregáció, kiválasztás és halmazműveletek), mint a pivot függvények, fejlett szűrők és nevesített tartományokon végrehajtott műveletek.

Felismertük, hogy az Excel olyan függvényeket is támogat, melyek nem lettek még megvalósítva SQL-ben, mint pl. a gazdasági függvények, és úgy határoztunk azokat a táblázatokat, melyek ilyet tartalmaznak, nem fordítjuk le. A célunk az volt, hogy a függvények egy olyan részhalmazát fordítsuk le, melynek van az SQL-ben megfelelője. A legtöbb Excel függvény egyszerű SQL sorfüggvénnyel megvalósítható PL/SQL használatával. Mások, mint a SUMPRODUCT, ami változó számú tartományt átfoghat, aggregátumok és kollekciók használatával fordíthatók le SQL-re és nyitva maradtak további bővítésekre.

Három Excel – SQL fordítást különböztetünk meg. Az abszolút fordítás (4.1-es fejezet) az önmagukat tartalmazó Excel számításokat kezelik, pl. a képletek a beágyazott (azaz nem importált) Excel adatokon hajtanak végre műveletet. A táblafordítás (4.2-es fejezet) Excel műveleteket használ az importált relációs táblákon, hogy szimulálja a relációs algebrát. Az egységes fordítás lehetővé teszi az Excel számítást az RTáblákon (ld. 4.3-as és 4.4-es fejezet).

4.1 Abszolút Fordítás

Az abszolút fordítás azon megfigyelésen alapul, hogy az Excel képletek egy lap, sor és oszlop által meghatározott rögzített dimenziós hálón hajtanak végre műveleteket. Ezeken a hálókon belül Excel kifejezésekkel és függvényekkel, esetleg más cellák vagy tartományok értékeinek függvényében határozzák meg a képletek a cellák értékeit. Az abszolút fordítás igen jól fordít át SQL Modellbe, mivel jól tudja definiálni az SQL képleteket az Excel háló által megadott tömbön: (lap, sor, oszlop). Néhány kivétel következik:

Minden Excelbeli cella a „Cells” táblában tárolódik, mely tartalmazza a helyét, értékét és a képletét minden használt (nem üres) cellának. Az Excel munkalapot egy SQL nézet reprezentálja a „Cells” táblán keresztül, mely nézet kiszámítja a cellák értékét. Ez a nézet tartalmaz egy SQL Modellt, melyet az Excel koordinátái határoznak meg: (lap, sor, oszlop), és az x érték valósítja meg a cella értékét. Az Excel koordinátái tömb-indexként szerepelnek, pl. a Munkalap1!A1 (1,1,1) formában jelenik meg. A képleteket tartalmazó cellák értékeit az SQL Modell frissítő szabálya számítja ki. Például vegyük a következő Excel táblát:

[pic]

A táblázat esetén az A1, B1, A2, B2 kerülnek letárolásra a „Cells” táblában. Az A1 és A2 konstansok nem hoznak létre frissítő szabályokat; a B1 és a B2 a következőképp valósul meg:

SELECT sheet, row, col FROM cells

MODEL DBY (sheet,row,col) MEA (x) AUTOMATIC ORDER

( x[1,1,2] = x[1,1,1] + x[1,2,1],-- B1=A1+A2

x[1,2,2] = x[1,3,1] + 1 -- B2=A3+1

)

Vegyük észre, hogy az SQL Modell az AUTOMATIKUS SORREND opciót használja, mely garantálja hogy a képletek függőségi sorrendben kerülnek végrehajtásra, csak úgy, mint Excelben. A további példákban ezt a beállítást külön nem jelöljük.

Az Alapszintű Abszolút Fordítás. A 3. ábra bemutatja az újraírási szabályait az iménti fordításunknak. Az R rekurzív újraíró függvény Excel képletet konvertál át SQL Modell képletté. A következő újraíró szabályokat használjuk:

- cref – a cellahivatkozást fordítja le, pl. R(Munkalap1!A1)=x[1,1,1].

- Crange és crange_in_list – egy Excel függvény cellatartomány argumentumát fordítja le. Excel aggregátum esetén az Excel tartományt relációs tartományba fordítjuk. Például: R(sum(A1:A10))=sum(x) x)[1, 1RTable->SaveTable”. A nézetet a közvetlen RTáblákon elvégzett SQL transzformációkkal fejezzük ki. Megjegyezzük, hogy egy közvetlen RTábla létrehozásakor a felhasználó csak az alapul szolgáló relációs tábla adataiból egy mintát kér le. Viszont amikor beviszünk egy származtatott RTáblát, a nézetet a teljes relációs táblán hajtjuk végre, nem csak a mintán. Például a 6. ábrán megadott aggregáció a teljes relációs táblából készült nézetként tárolódik (lásd Q3-at), pedig a 4. ábrán mutatott RTábla csak egy mintát tartalmaz az adatokból. A Excelbe importált minták arra szolgálnak, hogy mintaadatokat szolgáltassanak egy korlátozott területen.

Továbbá az RTábla RDBMS objektumként (nézet) történő letárolásakor rögzítjük az RTábla Excelben elfoglalt helyét így később ugyanoda visszaállítható.

Származtatott RTábla frissítése az RDBMS-ből. Amint az RTáblát reprezentáló relációs nézet elkészült, az RTábla visszaállítható az Excelbe. Ez biztosítja azt az elvárást a felhasználóknak, hogy kidolgozzák a kívánt relációs műveleteket az Excelbe töltött relációs táblákból származó adatmintákon, melyek a korlátozott méretet kezelni képes Excelben is elférnek, majd ezeket relációs nézetként eltárolják, végrehajtsák a számításokat az RDBMS skálázható és párhuzamos számítás-végrehajtásával, majd az eredményt visszatöltsék az Excelbe. Elég valószínű, hogy az eredmény, az aggregációnak köszönhetően, elég kicsi lesz, hogy Excelben is elférjen, még ha az eredeti relációs bevitt adatok nem is férnének el. A frissítést a betöltést végző menüben tudjuk elvégezni: „QBX->RTable->LoadTable”.

4.3 Az RTáblákon végzett abszolút műveletek fordtása

Az Excel gyakran tölt be előre feldolgozott adatokat külső forrásokból, például relációs táblákból, folyamatos Excel tartományokban elhelyezi és a továbbiakban Excel képletekkel elemzi őket. Az RTáblákkal egy olyan olyan eszközt nyújtunk az elemzőknek, mellyel közvetlen az Excelben tudják elvégezni a relációs adatok elő-feldolgozását egy származtatott RTáblaként. Elméletileg be szerettünk volna mutatni az RTáblákon végzett utólagos számítást, mint további SQL átalakításokat, mely igen nehezen kifejezhető a klasszikus ANSI SQL használatával, mivel a felhasználók az RTáblákban tetszőleges képleteket hozhatnak létre tetszőleges cellákra hivatkozva.

Hogy támogassuk a tetszőleges Excel számításokat az RTáblákon, kétdimenziós tömbként fogtuk fel őket az Excel koordináták (sor, oszlop) felhasználásával. Vegyük az M soros R(c1, c2, ..., cn) RTáblát, melynek bal felső sarka az Excel pontjában van, jobb alsó sarka pedig a pontban. Adott egy T0 relációs nézet mely megfelel az R-nek azonos oszlopokkal (c1, c2, ..., cn), valamint egy rn oszloppal, mely a T0-beli sorok egyedi sorrendjét tartalmazza az Excelben. Megjegyezzük, hogy az R a T összes sorát reprezentálja (ugyanis az R a T összes adatán alapul), egyébként a linearizáció a minta méretétől függene.

Két linearizációs technikát kísérleteztünk ki. Az elsőt Besorolásos Linearizációnak hívjuk, mely a T0-t reprezentálja az Excel koordináták (sor, oszlop) által méretezett „SQL Reference Model-ként”. Az átalakítás a ci oszlopok őket megelőző, ci-1 oszlopokra történő egyesítésével történik az UNION ALL operátor segítségéve. Például a T0 első, c1 oszlopa a következőképp néz ki:

SELECT rn+rs AS row, cs+1 AS col, c1 AS x FROM To,

A második, c2 oszlop:

UNION ALL

SELECT rn+rs AS row, cs+2 AS col, c2 AS x FROM To

stb. A fő SQL Modellben besorolja a hivatkozott értékeket a megfelelő Excel cellákba a (, ) tartományban. Az SQL képlet a következő:

Q5

SELECT row,col, x FROM cells

MODEL

REFERENCE ref_t ON

(SELECT rn+rs rn, cs+1 c, c1 x FROM to UNION ALL

SELECT rn+rs rn, cs+2 c, c2 x FROM to UNION ALL

..

SELECT rn+rs rn, cs+M c, cn x FROM to)

DBY (rn, c) MEA (x)

MAIN DBY (row, col) MEA (x)

( x[rs ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download