WordPress.com



R?GAS TEHNISK? UNIVERSIT?TEDatorzinātnes un informācijas tehnolo?ijas fakultāteInformācijas tehnolo?ijas institūts3. praktiskais darbs priek?metā?Progresīvās datu bāzes”“Datu noliktavas izveido?ana un izmanto?ana”Izstrādāja: Maksims Kor?evsII RDMI-2, 081RDB076Pārbaudīja: asoc. prof. J. Eiduks2012./13. māc. gadsAnotācijaDarba mēr?is tiek definēts kā izpētīt datu noliktavas tehnolo?ija. Mēr?a sasnieg?anai tika izdomāta uzskaitī?anas sistēma biroja pre?u veikalam SIA VEIKALS, kas satur informāciju par precēm, klientiem, darbiniekiem un to mijiedarbības vēsturi jeb transakcijām. Tika izdomātas datu bāzes tabulas un objekti, kā arī lo?iskas saites starp tām. Kā rezultāts, darbā ir aprakstīta darbība ar datu noliktavas tehnolo?ijām, kas ietver sevī vairākus posmus tādus kā faktu tabulas, dimensiju tabulu, materializētu skatu utt izmanto?ana. Ir definēti un izpildīti vairāki vaicājumi.Darbā gaitā tika izmantota SQL valoda. Darbs tika veikts ar Oracle SQL Developer 3.0.04 programmas palīdzību. Darba apjoms ir 40 lappuses, kuras satur 25 attēlus un 6 tabulas.Saturs TOC \o "1-3" \h \z \u Anotācija PAGEREF _Toc344822787 \h 2Saturs PAGEREF _Toc344822788 \h 3Uzdevums PAGEREF _Toc344822789 \h 41. Izmantotā datu bāze PAGEREF _Toc344822790 \h 52. Oracle SQL Developer programmas izmanto?ana PAGEREF _Toc344822791 \h 63. Dimensiju modelē?ana PAGEREF _Toc344822792 \h 84. Datu glabā?anas struktūras PAGEREF _Toc344822793 \h 114.1. Dimensiju tabulas PAGEREF _Toc344822794 \h 114.2. Faktu tabula PAGEREF _Toc344822795 \h 154.3. Datu ievade PAGEREF _Toc344822796 \h 155. Grupē?anas vaicājumi PAGEREF _Toc344822797 \h 185.1. GROUP BY vaicājumi PAGEREF _Toc344822798 \h 195.2. ROLLUP vaicājumi PAGEREF _Toc344822799 \h 205.3. CUBE vaicājumi PAGEREF _Toc344822800 \h 215.4. GROUPING vaicājumi PAGEREF _Toc344822801 \h 225.5. GROUPING_ID vaicājumi PAGEREF _Toc344822802 \h 256. SQL Model vaicājumi PAGEREF _Toc344822803 \h 267. PIVOT vaicājumi PAGEREF _Toc344822804 \h 298. Materializētie skati PAGEREF _Toc344822805 \h 339. Secinājumi PAGEREF _Toc344822806 \h 3810. Izmantotās literatūras saraksts PAGEREF _Toc344822807 \h 40Uzdevums1. Dimensiju modelē?ana: tiek izveidotas vismaz trīs dimensijas ar hierarhijām (hierarhijā vismaz trīs līme?i) un definēti vismaz divi faktu veidi.2. Datu bāzē tiek izveidotas atbilsto?ās datu glabā?anas struktūras: dimensiju tabulas un faktu tabula. Tiek veikta datu ievade.3. Izmantojot GROUP by CUBE() konstrukciju un funkciju GROUPING(), tiek veikti vaicājumi, lai demonstrētu to iespējas.4. Izmantojot SQL Model konstrukciju, tiek veikti vaicājumi, lai demonstrētu tās iespējas.5. Izmantojot Pivot konstrukciju, tiek veikti vaicājumi, lai demonstrētu tās iespējas.5. Papildus dimensiju un faktu tabulām tiek izveidoti materializētie skati, kuros iek?auti vaicājumos pieprasītie datu agregāti.. Tiek veikti vaicājumi un tiek pārbaudīts vai notiek vaicājumu pārrakstī?ana (rewriting) sakarā ar materializēto skatu izmanto?anu.6. Kopējie secinājumi.1. Izmantotā datu bāzeDatu bāze ir paredzēta biroja pre?u veikalam SIA VEIKALS. Tā ir uzskaitī?anas sistēma, kas satur informāciju par precēm, klientiem, darbiniekiem un to mijiedarbības vēsturi jeb transakcijām. Saska?ā ar aprakstu vislabākais risinājums ir datu bāze, kas ir redzama 1. attēlā.Datu bāze2. Oracle SQL Developer programmas izmanto?anaDarba izpildei tika izmantots ORACLE programma – ORACLE SQL Developer 3.0.04. ORACLE SQL Developer ir SQL*Plus grafiskā versija, kas dot iespēju datu bāzes administratoriem ērtā veidā izpildīt pamatuzdevumus: apskatīt, veidot, redi?ēt un iznicināt datu bāzes objektus; pildīt SQL komandas un skriptus, redi?ēt un atk?ūdot PL/SQL kodus; ievadīt un eksportēt datus; apskatīt un veidot atskaites.Programmas palai?anas fails ir sqldeveloper.exe (sk. att. 2)ORACLE SQL Developer palai?anas logsPirmais solis, kas ir nepiecie?ams izveidot ir savienojuma ar datu bāzi izveido?ana. To var izdarīt sekojo?i:no izvēlnes VIEW ir nepiecie?ams izvelēties rīkjoslu Connections;rīkjoslā Conections izvēlēties New Connection (sk. attēls 3);attiecīgos laukos ir jāievada savienojuma parametri un lietotāja konta dati;uzklik??inot uz Test Connection, jāparādās zi?ojumam Status: Success;jāsaglabā savienojums.New Coonection parametru logsKad savienojums ir izveidots ir jāpieslēdzas datu bāzei, uzklik??inot ar labu tausti?u uz jaunizveidotā savienojuma nosaukuma un izvēloties Connect. Vaicājumi jāraksta jaunatvērtajā logā. Palaist izpildei uzrakstītu vaicājumu var ar pogu F5. Rezultāti tiek izvadīti logā Script Output kā ir redzams attēlā 4.ORACLE SQL Developer darba virsma3. Dimensiju modelē?anaDimensiju modelē?ana ir metode, kas tiek lietota projektējot datu noliktavas datu struktūras. ?ī tehnolo?ija nodro?ina datu struktūras vieglu uztveramību un izprotamību. Veidojot operatīvās datu bāzes, datu struktūras projektē?anai izmanto ER diagrammas. Dimensiju modelisDimensiju modelē?ana ir lo?ikas projektē?anas tehnika, kas mē?ina prezentēt datus intuitīvi labi izprotamā veidā un nodro?ina ātru piek?ū?anu vajadzīgajiem datiem. Tiek izmantotas faktu un dimensiju tabulas. Faktu tabulai ir vairākas datu meklē?anas atslēgas. Dimensiju tabulas primārā atslēga atbilst vienai faktu tabulas atslēgai. Dimensiju tabulas ir ieejas punkti datu noliktavā. Dimensiju mode?a priek?rocības:Dimensiju modelis ir labi izprotams un no tā vienkār?i var iegūt nepiecie?amos datus.Visas dimensijas ir lo?iski ekvivalentas. Līdz ar to vaicājumi ir simetriski, vienveidīgi.Dimensiju modelis ir viegli papla?ināms, pievienojot :jaunus faktus;jaunas dimensijas;jaunus dimensiju atribūtus;jaunu apak?dimensiju ievie?ana.Ir speciālas pieejas īpa?u situāciju modelē?anā:lēnu izmai?u dimensiju modelē?ana;heterogēnu biznesa objektu izvērtē?ana (vienlaicīgi jāievēro ar??irīgas biznesa lietas);“pay-in advance” datubāzes, kurās var apskatīt transakcijas un kopējo gala rezultātu ;“event-handling” datubāzes - darbība faktu deficītā.Agregātu lieto?anas iespējas (būtiskas vaicājuma izpildes ātrdarbības palielinā?anas iespējas).Dimensiju modelī svarīgi ir at??irt faktus un atribūtus. Fakts ir kaut kādi dati, kas iepriek? nav zināmi. Tie parasti ir skait?u formā (reāli skait?i), bet var būt arī teksta veidā (retāk). Atribūts parasti ir teksta formā un norāda reālu lietu īpa?ību. Tekstuālie atribūti, kas raksturo lietas tiek organizēti dimensijās. Dimensijas atribūti ir cie?i saistīti viens ar otru. Dimensiju vērtībām kombinējoties kopā, rodas norāde uz faktiem. Ja dimensijas ir samērā vāji korelētas, tad fakta iespējamo vērtību skaits ir neliels. Ja korelācijas pakāpe ir augsta, fakta vērtību skaits var būt ?oti liels.Atribūtiem dimensiju modelī ir iz??iro?a loma. Tie veido lietojuma vaicājuma noteikumu sistēmu (ierobe?ojumus) un igūstamā pārskata rindu virsrakstus.Dimensiju tabulā atribūti parasti veido vairākas hierarhiju sistēmas. Labi projektētā datu noliktavā pieprasot vienādu skaitu rakstu da?ādās hierarhijas pakāpēs, izpildes laiki nedrīkst būtiski at??irties.Strādājot ar dimensiju tabulām jānodro?ina labas to atribūtu vērtību pārskata iespējas. Lietotājam bie?i ir vēlams noskaidrot kādas un cik ir unikālas atribūtu vērtības un kā tās ir saistītas ar citu atribūtu vērtībām. Tā kā katrs lietotājs datus analizē izejot no savām prasībām, lietderīgi izmantot ierobe?ojumu definē?anu un piekārto?anu katram lietotāja mehānismam. Lietotājs definē savus ierobe?ojumus, tie fiksējas un nāko?ā sesijā atkal tiek ievēroti.Da?reiz zvaigznes shēma tiek papla?ināta uz sniegpārsli?as shēmu izslēdzot no sākotnējās dimensiju tabulas zemas kardinalitātes atribūtus un izveidojot pakārtotas dimensijas tabulu. Datu noliktavas kvalitāti nosaka dimensiju atribūtu kvalitāte. Dimensiju atribūtiem un to vērtībām jābūt:saprotamiem;apraksto?iem;pilnīgiem, bez k?udainām (pretrunīgām) vērtībām;indeksētiem;dokumentētiem (metadatos).Darbā ir izmantota sniegpārsla mode?a shēma (sk. 6. att.) biroja pre?u veikalam, kur ir trīs dimensijas: Darbinieku_dim (satur informāciju par veikala darbiniekiem), Klientu_dim (satur informāciju par klientiem) un Precu_dim (satur informāciju par precēm). Katrā no ?īm dimensijām ir hierarhiskie līmeni. Visas dimensijas apvieno faktu tabula Faktu_taula, kur ir ierakstīta informācija par transakcijas datiem: klients, prece un to daudzums, atbildīgais darbinieks, datums un transakcijas statuss.Darba izmantota sniegpārsla mode?a shēma4. Datu glabā?anas struktūras Lai datu bāze varētu pareizi funkcionēt ir nepiecie?ams izveidot atbilsto?ās datu glabā?anas struktūras: dimensiju tabulas un faktu tabula.Tabulas ir pamatstruktūra, kurā dati tiek glabāti datu bāzē. Tabulas ir sadalītas rindās un kolonnās. Katra rinda ir viena datu vienība, un katrs lauks var būt uzskatams par ?ādu datu kopas noteiktu da?u. Tabulas izveido?anas laikā, nosaka lauku apzīmējumus un datu tipus, kas tur glabājas.Tabulas izveido?ana tika realizēta SQL valodā ar komandas CREATE TABLE palīdzību, un tās pamata sintakse ir:CREATE TABLE tabulas_nosaukums(pirmā_lauka_nosaukumspirmā_lauka_tips,otrā_lauka_nosaukumsotrā_lauka_tips,...pēdējā_lauka_nosaukumspēdējā_lauka_tips);Eksistē vairākas lauka tipi, kas ir domāti datu glabā?anai kā skaitli binārā sistēmā (binary, varbinary, image) un decimālā sistēmā (bigint, numeric, bit, smallint, decimal, smallmoney, int, tinyint, float, real), datu glabā?anai kā laiks vai datums (date, datetimeoffset, datetime2, smalldatetime, datetime, time), kā teksta virkne (char, varchar, text) vai citā veidā (cursor, timestamp, hierarchyid, uniqueidentifier, sql_variant, xml utt.)4.1. Dimensiju tabulasDatu bāzē ir piecas dimensiju tabulas: Cilveku_dim, Darbinieku_dim, Adresu_dim, Klientu_dim un Precu_dim. Katrai tabulai ir savi atribūti un līdz ar to, katra tabula ir definēta atsevi??i sekojo?ā veidā:CREATE TABLE Cilveku_dim (Cilveka_Id Number NOT NULL,Vards VarChar2(15),Uzvards VarChar2(15),Per_kods VarChar2(12),CONSTRAINT Cilveks_PK PRIMARY KEY (Cilveka_Id));Cilvēku dimensijas izveido?anaCREATE TABLE Darbinieku_dim (Darbinieka_Id Number NOT NULL,Amats VarChar2(10),Alga Number(10,2),Nodala VarChar2(12),Cilveks_FK Number,CONSTRAINT Darbinieks_PK PRIMARY KEY (Darbinieka_Id),CONSTRAINT Cilveks_FKey FOREIGN KEY (Cilveks_FK) REFERENCES Cilveku_dim(Cilveka_Id));Darbinieku dimensijas izveido?anaCREATE TABLE Adresu_dim (Adreses_Id Number NOT NULL,Valsts VarChar2(10),Pilseta VarChar2(10),Iela VarChar2(10),Maja Number,Birojs Number,Pasta_indekss VarChar2(7),CONSTRAINT Adrese_PK PRIMARY KEY (Adreses_Id));Adre?u dimensijas izveido?anaCREATE TABLE Klientu_dim (Klienta_Id Number NOT NULL,Konts VarChar2(30),Atlaide Number,Talrunis VarChar2(12),Adrese_FK Number,CONSTRAINT Klients_PK PRIMARY KEY (Klienta_Id),CONSTRAINT Adrese_FKey FOREIGN KEY (Adrese_FK) REFERENCES Adresu_dim(Adreses_Id));Klientu dimensijas izveido?anaCREATE TABLE Precu_dim (Preces_Id Number NOT NULL,Nosaukums VarChar2(15),Cena VarChar2(15),Mervieniba VarChar2(12),Piegadatajs VarChar2(12),CONSTRAINT Prece_PK PRIMARY KEY (Preces_Id));Pre?u dimensijas izveido?ana?ādā veidā visas dimensijas ir izveidotas.4.2. Faktu tabulaPēc dimensiju tabulu izveido?anas faktu tabula ir izveidota. Tas ir izpildīts ar sekojo?o vaicājumu:CREATE TABLE Faktu_Tabula (Klients Number,Prece Number,Daudzums Number,Darbinieks Number,Datums Date,Statuss Number,CONSTRAINT Klients_FKey FOREIGN KEY (Klients) REFERENCES Klientu_dim(Klienta_Id),CONSTRAINT Prece_FKey FOREIGN KEY (Prece) REFERENCES Precu_dim(Preces_Id),CONSTRAINT Darbinieks_FKey FOREIGN KEY (Darbinieks) REFERENCES Darbinieku_dim(Darbinieka_Id));Faktu tabulas izveido?ana4.3. Datu ievadeKad visas tabulas un saites starp tām ir izveidotas, ir nepiecie?ams ievadīt datus. Datu ievadī?ana var notiek ar komandu INSERT INTO vai ar SQL*LOADER programmas palīdzību. Tā kā ir paredzēts liels datu apjoms, SQL*LOADER ir izmatots.SQL*LOADER ir ievades programma, kas domāta ārējas datu ievadei iek? Oracle datu bāzes. Programma dot iespēju ievadīt da?ādu failu formātus, veikt da?ēju ievadi un arī ievadīt datus vairākām tabulām. Programmas palai?ana sakas ar operācijas sistēmas komandu:SQLLDR lietotājs/parole@// 85.254.218.238:1521/ rtuditf CONTROL=Loader.ctl,kur lietotājs un parole ir attiecīgi dati serverī. Loader.ctl ir kontroles fails, kas satur nepiecie?amu informāciju:LOAD DATAINFILE 'C:\SCHOOL\DB3\Cilveki.dat'INTO TABLE Cilveku_dimFIELDS TERMINATED BY ","(Cilveka_ID, Vards, Uzvards, Per_kods)Fails ar datiem Cilveki.dat izskatās sekojo?i:1, Anete, Blauva, 040768-148562, Sta?islavs, Du?kins, 131071-173213, Savelijs, Gu?evs, 010760-136024, Maksims, Ivanovs, 220873-174125, O?egs, Mihailovs, 110866-120996, Ernests, Pakers, 291192-104437, Aleksandrs, Petri?ins, 010481-189978, U?is, Pūpoli??, 030164-188129, Aleksandrs, Sila, 091095-1783110, Aleksandrs, Suvorovs, 221063-13057…108, Viktors, Pehota, 190374-18211Līdzīgā veidā tika aizpildītas visas tabulas. Pārliecināties, kā ievadīti dati ir ievadīti korekti cara ar SELECT vaicājumu:Dati no Faktu tabulas5. Grupē?anas vaicājumiGrupē?ana ?auj analizēt datus, lai izdotu nepiecie?amo rezultātu. Ir vairāki grupē?anas funkcijas:Group byRollup (GROUP BY ROLLUP)Cube (GROUP BY CUBE)GroupingGrouping_ID un citiDarba tiek grupēti dati par ierakstītām transakcijām pēc darbinieka un pēc klienta pilsētas.Dati vaicājumam var būt iegūti ar sekojo?o vaicājumu:SELECT C.Uzvards, F.Klients, A.Pilseta, (F.Daudzums*P.Cena*(100-K.Atlaide)/100) AS SummaFROM Faktu_Tabula F, Precu_Dim P, Klientu_Dim K, Darbinieku_Dim D, Cilveku_Dim C, Adresu_Dim AWHERE F.Prece=P.Preces_Id AND F.Klients=K.Klienta_Id AND F.Darbinieks=D.Darbinieka_Id AND D.Cilveks_Fk = C.Cilveka_Id AND K.Adrese_FK=A.Adreses_ID;Lai nerakstītu kodu katru reizi, ir izveidots skats ?Skats”, kas izvada nepiecie?amu informāciju.Skats grupē?anas vaicājumiem5.1. GROUP BY vaicājumiGROUP BY darbojas ar agregātām funkcijām, piemērām funkcija SUM, COUNT, MAX, MIN un citām. Agregātas funkcijas izanalizē vairākas rindas un izdot vienu rezultātu, piemēram:Visu transakciju kopīga summaIek?aujot GROUP BY vaicājumā, tas ierobe?o apstrādāto datu kopu. Tādā veidā ir sa?emtas agregātas vērtības katrai atsevi??ai kombinācijai, kas ir definētas GROUP BY da?ā. Sagaidāmo ierakstu skaitu var aprē?ināt, reizinot at??irīgas vērtības katrā kolonnā, kas ir uzskaitītas pēc GROUP BY. Darba gadījumā kolonai ?Uzvards” ir 26 vērtības, kolonai ?Klients” arī 26 vērtības, un kolonai ?Pilseta” – tikai divas vērtības. Līdz ar, izmantojot pēdējo kolonu rezultātā ir sa?emtas divas rezultātu rindas:Transakciju skaits un to summa katrai klienta pilsētaiIzmantojot kolonu kombināciju GROUP BY da?ā rezultātu ierakstu būs vairāk, piemērām, darbinieka un klienta pilsētas kombinācija izdos 2*26=52 ierakstus, un visu kolonu kombinācija izdos 2*26*26=1352 ierakstus.5.2. ROLLUP vaicājumiPapildus parastajiem agregātiem rezultātiem, ko var sagaidīt no GROUP BY vaicājuma, ROLLUP papla?inājums veido grupas starpsummas no labās uz kreiso, un kopējo summu. Ja vaicājuma uzskaitīto kolonnu skaits ir n, tiks ievadīti n+1 starpsummas.Ir izmantots sekojo?s vaicājums, lai aprē?inātu cik katrā pilsētā katrs darbinieks izpildīja transakcijas:Select Uzvards, Pilseta, Count(*) As "Ierakstu skaits", Sum(Summa) As SummaFrom SkatsGroup By Rollup (Uzvards, Pilseta)ORDER BY Uzvards,Summa;Vaicājumam ir sekojo?s rezultāts:UZVARDSPILSETAIerakstu skaitsSUMMAAnsonsLiepāja2101,8AnsonsRīga71163,7545Ansons91265,5545AntropovsLiepāja4564,543AntropovsRīga101252,6335Antropovs141817,1765A?isimovsLiepāja2320,976A?isimovsRīga9804,702A?isimovs111125,678A?iskovecsLiepāja5380,572A?iskovecsRīga71432,785A?iskovecs121813,357ArsentjevaRīga5877,47ArsentjevaLiepāja5945,75Arsentjeva101823,22…VērzemnieksLiepāja2112,588VērzemnieksRīga122151,6825Vērzemnieks142264,270528943411,913576 rows selectedLai samazinātu starprezultātu daudzumu ir iespējams veikt da?ējo PULLUP, piemēram sekojo?ā veidā: Group By Uzvards, Rollup (Pilseta). ?ajā gadījumā vaicājuma rezultāts būs līdzīgs iepriek?ējam tikai nebūs starpsummas pēc abām kolonām.5.3. CUBE vaicājumiCUBE ?enerē starpsummas līdzīgi ROLLUP, bet klāt CUBE izveido starprezultātus visām iespējam kombinācijām, ko var izveidot no norādītām kolonām. Ja vaicājuma uzskaitīto kolonnu skaits ir n, tiks ievadīti n2 starpsummas.Ir izmantots vaicājums kā iepriek?ējā apak?noda?ā izmainot tikai ROLLUP uz CUBE. Rezultātā ir izdotas tie pa?i ieraksti un 2 jauni ieraksti (atzīmēti ar sarkano):UZVARDSPILSETAIerakstu skaitsSUMMAAnsonsLiepāja2101,8AnsonsRīga71163,7545Ansons91265,5545AntropovsLiepāja4564,543AntropovsRīga101252,6335Antropovs141817,1765A?isimovsLiepāja2320,976A?isimovsRīga9804,702A?isimovs111125,678A?iskovecsLiepāja5380,572A?iskovecsRīga71432,785A?iskovecs121813,357ArsentjevaRīga5877,47ArsentjevaLiepāja5945,75Arsentjeva101823,22…VērzemnieksLiepāja2112,588VērzemnieksRīga122151,6825Vērzemnieks142264,2705Liepāja9011448,549Rīga19031963,364528943411,913576 rows selectedAr kolonu skaitu palielinā?u CUBE da?ā, palielinās arī starpsummu skaits. Piemērām ?ādam vaicājumam ir 590 rezultāti un 360 no tiem ir starpsummas.Select Uzvards, Pilseta, Klients, Count(*) As "Ierakstu skaits", Sum(Summa) As SummaFrom SkatsGroup By CUBE (Uzvards, Pilseta, Klients)ORDER BY Uzvards,Summa;Līdzīgi kā ar PULLUP, lai samazinātu starprezultātu daudzumu ir iespējams veikt da?ējo CUBE vaicājumu.5.4. GROUPING vaicājumiVar būt diezgan viegli vizuāli identificēt starpsummas no ROLLUP vai CUBE vaicājumiem, bet lai automatizētu rezultātu apkopojumu ir nepiecie?ams kaut precīzāk Null vērtības grupējuma kolonnās. Tas ir iespējams atrisināt ar GROUPING funkciju. Tā izveido vienu kolonnu kā parametru un atgrie? "1", ja ROLLUP vai CUBE rezultātā dē? kolonna satur Null vērtības vai "0" par jebkuru citu vērtību, tostarp ja tā vērtība citu iemeslu dē? ir Null.Sekojo?s vaicājums ir atkārtojums no CUBE vaicājuma, bet GROUPINF funkcija ir pievienota katrai kolonai.SELECT Uzvards, Pilseta, Count(*) As "Ierakstu skaits", Sum(Summa) As Summa, GROUPING(Uzvards) AS "Grupē?ana pēc uzvārda", GROUPING(Pilseta) AS "Grupē?ana pēc pilsētas"FROM SkatsGROUP BY CUBE (Uzvards, Pilseta)ORDER BY Uzvards,Summa;UZVARDSPILSETAIerakstu skaitsSUMMAGrupē?ana pēc uzvārdaGrupē?ana pēc pilsētasAnsonsLiepāja2101,800AnsonsRīga71163,754500Ansons91265,554501AntropovsLiepāja4564,54300AntropovsRīga101252,633500Antropovs141817,176501A?isimovsLiepāja2320,97600A?isimovsRīga9804,70200A?isimovs111125,67801A?iskovecsLiepāja5380,57200A?iskovecsRīga71432,78500A?iskovecs121813,35701ArsentjevaRīga5877,4700ArsentjevaLiepāja5945,7500Arsentjeva101823,2201…VērzemnieksLiepāja2112,58800VērzemnieksRīga122151,682500Vērzemnieks142264,270501Liepāja9011448,54910Rīga19931963,36451028943411,91351178 rows selectedNo tā var redzēt:Grupē?ana pēc uzvārdaGrupē?ana pēc pilsētasNozīme00Parastā starpsumma, kas varētu būt iegūta no GROUP?BY operatora01Starpsumma pēc uzvārda, ko var iegūt ar ROLLUP vai CUBE operatoriem10Starpsumma pēc pilsētas, ko var iegūt tikai ar CUBE operatoru11Vaicājuma kopsumma, ko var iegūt ar ROLLUP vai CUBE operatoriemAr ?īm vērtībām tagad ir vieglāk izveidot programmu datu apkopo?anai. Arī GROUPING kolonas var būt izmantotas rezultātu kārto?anai vai filtrācijai:SELECT Uzvards, Pilseta, Count(*) As "Ierakstu skaits", Sum(Summa) As Summa, GROUPING(Uzvards) AS "Grupē?ana pēc uzvārda", GROUPING(Pilseta) AS "Grupē?ana pēc pilsētas"FROM SkatsGROUP BY CUBE (Uzvards, Pilseta)HAVING GROUPING(Uzvards) = 1 OR GROUPING(Pilseta) = 1ORDER BY Uzvards,Summa;UZVARDSPILSETAIerakstu skaitsSUMMAGrupē?ana pēc uzvārdaGrupē?ana pēc pilsētasAkantjevs81079,32601Ansons91265,554501Antropovs141817,176501A?isimovs111125,67801A?iskovecs121813,35701Arsentjeva101823,2201?boli??151411,888501Balcers91374,44701Bern?teine141510,76801Bessonova101919,647501Blauva183160,69401Bod?s131897,89201Boginskis4658,91801Borovskis320501Brēdi?is192822,95101Du?kins122378,57801Gu?evs101379,58101Ivanovs4458,913501Mihailovs81074,306501Pakers122287,3401Petri?ins122364,568501Pūpoli??101366,61901Sila101887,62801Slobodnik121917,23901Suvorovs162146,351501Vērzemnieks142264,270501Liepāja9011448,54910Rīga19931963,36451028943411,9135115.5. GROUPING_ID vaicājumiFunkcija GROUPING_ID piedāvā kompaktāko alternatīvu starprezultātu identifikācijai. Tā izvada dimensijas kolonas kā argumentus pēc GROUP BY līme?a. Kā piemēru var apskatīt vaicājumu, kas bija apskatīts ar GROUPING funkciju: SELECT Uzvards, Pilseta, Count(*) As "Ierakstu skaits", Sum(Summa) As Summa, GROUPING_ID(Uzvards, Pilseta) AS Grupē?ana FROM SkatsGROUP BY CUBE (Uzvards, Pilseta)ORDER BY Uzvards,Summa;UZVARDSPILSETAIerakstu skaitsSUMMAGrupē?ana AnsonsLiepāja2101,80AnsonsRīga71163,75450Ansons91265,55451AntropovsLiepāja4564,5430AntropovsRīga101252,63350Antropovs141817,17651A?isimovsLiepāja2320,9760A?isimovsRīga9804,7020A?isimovs111125,6781A?iskovecsLiepāja5380,5720A?iskovecsRīga71432,7850A?iskovecs121813,3571…VērzemnieksLiepāja2112,5880VērzemnieksRīga122151,68250Vērzemnieks142264,27051Liepāja9011448,5492Rīga19931963,3645228943411,9135378 rows selected6. SQL Model vaicājumiBie?i izstrādājot vaicājumus rodas problēmas milzīgo vaicājuma dē?, īpa?i gadījumos ar aprē?inu iteratīviem metodēm, starprezultātu aprē?inā?anām, risinājumiem, kas pieprasa blakus vērtības utt. Tas vada pie ātrdarbības samazinā?anas un koda caurskatāmības bojā?anu. Lai atrisinātu ?īs problēmas, ir piedāvāts izmantot SQL MODEL operatoru.SQL MODEL operators ir SELECT konstrukcijas papla?inājums, kas ir pieejams sakot ar Oracle 10 versiju. MODEL ?auj izmantot SELECT rezultātus kā daudzdimensiju masīvu, mainīt un pievienot elementus, veikt sare??ītas agregātas funkcijas, un arī risināt problēmas, ko pirms tam tika risināts tikai ar PL/SQL. Tajā pa?ā laikā valodas konstrukcijas ir labi caurskatāmi un saprotami.MODEL operatora sintakse ir sekojo?ā: MODEL [IGNORE NAV] [RETURN UPDATED ROWS] [PARTITION BY (partition_column_1, ...)] DIMENSION BY (dimension_column_1, ...) MEASURES (measured_column_1, ...) RULES [AUTOMATIC ORDER | ITERATE (value) [UNTIL (expression)]] ( rule_1, ... );MODEL operators tiek izpildīts viens no pēdējam. Pēc tā ir izpildīts tikai DISTINCT un ORDER BY operatori. Pielieto?anas rezultātā aprē?ini ir izvadīti masīvā measured_column_n ar dimensijām dimension_column_n. Parametrs PARTITION BY ir neobligāts un nosāka nodalījumus. Likumi tiek izpildīti definē?anas kartībā.MODEL operatoru darbā var apskatīt ar dimensiju tabulu ?Darbinieku_Dim” palīdzību. Pa?laik darbiniekam ar ID=1 ir alga 345 lati (sk. 17. att.). Ar likumu MODEL operatora ir iespējams izmainīt to uz citu vērtību, piemērām 500 Ls, kā ir redzams 18. attēlā. Darbinieka ar ID=1 algaIzmainīta darbinieka algaLikumus arī var definēt visai grupai vai grupas da?ai ar filtra palīdzību. To var darīt sekojo?ā veidā:Alga[Darbinieka_ID<20, Amats LIKE ‘Selle%’, any]Ar ?āda veida likumiem nevar veidot jaunus elementus, bet tikai atjaunot vērtības jau eksistējo?os elementos. Kā piemērs ir apskatīts uzdevums, kad ir nepiecie?ams palielināt algu elektronoda?as darbiniekiem par 50% un arī apre?ināt vidējo algu katrā noda?ā. To var izdarīt ar sekojo?o vaicājumu:SELECT *FROM Darbinieku_DimMODEL DIMENSION BY (Darbinieka_Id,Amats, Nodala) MEASURES (Alga) RULES ( Alga[Any, Any, 'Electro'] = Alga[CV(Darbinieka_Id), CV(Amats),Cv(Nodala)] * 1.5, Alga[Null, Null, 'Electro'] = AVG(Alga)[Any,Any,'Electro'], Alga[Null, Null, 'Office'] = AVG(Alga)[Any,Any,'Office'], Alga[Null, Null, 'Paper'] = AVG(Alga)[Any,Any,'Paper'], Alga[Null, Null, 'PR'] = AVG(Alga)[Any,Any,'PR'], Alga[Null, Null, 'Valdība'] = AVG(Alga)[Any,Any,'Valdība'] )ORDER BY Amats,Nodala;MODEL vaicājuma rezultāts7. PIVOT vaicājumiPIVOT vaicājumi iek?auj sevī rindu transformāciju kolonnās (pivot) vai kolonnu transformāciju rindās (unpivot), lai radītu rezultātus pagriezta formātā. Pagrieziens ir izplatīta metode, īpa?i atskai?u ?enerē?anai, kas bija pieejams SQL valodā un Oracle versijās ilgu laiku. Ta?u tikai Oracle 11. Versijā parādījās operatori PIVOT un UNPIVOT. ?ie operatori ir SELECT operatora papla?inājumi ar savu sintaksi un struktūtu. PIVOT operatora darbības ir pārsvarā zināmi: vairākas rindas tiek apkopotas jeb agregātas un pagriezti, lai tiktu atspogu?otiem kā kolonnas, kur katra kolonna ir atsevi??o agregāto datu kopa. Kopumā, to sintakse ir ?āda:SELECT ...FROM ...PIVOT ( pivot_clause pivot_for_clause pivot_in_clause )WHERE ...kurpivot_clause-apraksta kolonas, kas tiks agregātas;pivot_for_clause-apraksta kolonas, kas tiks grupēti un pagriezti;pivot_in_clause-apraksta ierobe?ojumus pivot_for_clause da?ai.PIVOT funkcija nav pieejama ORACLE SQL Developer 3.0.04 versija, līdz ar to uzdevums ir izpildīts izmantojot Oracle Database 11g Express Edition. Pirmais vaicājums var būt sekojo?s:SELECT *FROM Darbinieku_DimPivot ( Avg(Alga) For Nodala In ('Electro', 'Office', 'Paper', 'PR'));Tomēr, kā ir ir redzms 20. attēlā rezultāts nav lietderīgs, jo tas izvada visu informāciju un rezultātu apkopo?ana nav redzama.PIVOT vaicājumsLai izlabotu situāciju, jādefinē apak?kopu iepriek?:WITH pivot_data AS (SELECT Nodala, Amats, AlgaFROM Darbinieku_Dim)SELECT * FROM pivot_dataPivot ( Avg(Alga) For Nodala In ('Electro', 'Office', 'Paper', 'PR'));Vidējā alga pēc amata katra noda?āJa pievienot pivot_clause da?ā nosaukumu, tad tā paradīsies arī rezultātos. Tas ir svarīgi, lai īpa?i paradīt kā dati ir agregēti vai kad ir vairāk nekā viens rezultāts:WITH pivot_data AS (SELECT Nodala, Amats, AlgaFROM Darbinieku_Dim)SELECT *FROM pivot_dataPivot ( Sum(Alga) as ALGA, COUNT(Alga) as Daudzums For Nodala In ('Electro', 'Office', 'Paper', 'PR'));Vidējā alga un darbinieku skaits pēc amata katra noda?āPirms Oracle 11. versijai līdzīgu rezultātu bija iespējams sasniegt izmantojot DECODE funkciju kopā ar agregācijas funkciju. Piemēram sekojo?s vaicājums izdot tādu pa?u rezultātu kā iepriek?ējs vaicājums.SELECT Amats, avg(DECODE(Nodala, 'Electro', ALGA, 0)) AS "'Electro'_ALGA", count(DECODE(Nodala, 'Electro', ALGA, 0)) AS "'Electro'_DAUDZUMS", avg(Decode(Nodala, 'Office', Alga, 0)) AS "'Office'_ALGA", count(DECODE(Nodala, 'Office', ALGA, 0)) AS "'Office'_DAUDZUMS", avg(DECODE(Nodala, 'Paper', ALGA, 0)) AS "'Paper'_ALGA", count(DECODE(Nodala, 'Paper', ALGA, 0)) AS "'Paper'_DAUDZUMS", avg(DECODE(Nodala, 'PR', ALGA, 0)) AS "'PR'_ALGA", count(DECODE(Nodala, 'PR', ALGA, 0)) AS "'PR'_DAUDZUMS"From Darbinieku_DimGROUP BY Amats;8. Materializētie skatiMaterializētie skati pirmo riezi parādījās Oracle 8. versijā kā da?a no komponentes Pārskatu vadība. Daudzi cilvēki ikdienā lieto materializētos skatus, vienīgā at??irība ir, ka vi?i tos lieto ar citu vārdu, piemēram, summārās vai agregātu tabulas. Materializētie skati ?auj būtiski samazināt saliktu vaicājumu ar pla?u atribūtu agregāciju izpildes laiku. Tiek izmantota arī kopsavilkumu konsultanta komponente, kas iesaka administratoram, kādus materializētos skatus radīt, dzēst un saglabāt.Lielākos materializēto skatu ieguvumus gūs lietotājs, kas lietos datu noliktavu. Tiks izmantots vaicājumu pārrakstī?anas mehānisms Oracle serverī automātiski pārrakstīs vaicājumu tā, lai tiktu izmantots materializētais skats. Materializētie skati pilda da?ādas funkcijas:vaicājumu izpildes ātruma palielinā?ana, datu kopē?ana, datu apstrāde un apkopo?ana. Informāciju no materializētajiem skatiem var izgūt ar vienkār?u SELECT vaicājumu palīdzību.Materializētais skats ir saglabāta vaicājuma atbilde. Lai izveidotu materializētu skatu, ir nepiecie?ams definēt:apraksts, kā fiziski tiek glabāti dati ;kad vajag aizpildīt materializēto skatu uzreiz pēc izveido?anas vai vēlāk;kā atjaunot datus, kad dati atbilsto?ās tabulās mainās; kad veikt datu atjauno?anu: katras transakcijas beigās, vai pēc pieprasījuma;izmantot skatu, lai pārrakstītu vaicājumu, vai nē;SELECT operators, kur? apraksta materializēta skata saturu.Pirms materializēta skata veido?anas datu bāzē ir iespējams sakumā pārbaudīt to ar utilītu EXPLAIN_MVIEW. Utilīta palīdz noskaidrot, vai materializētā skata vaicājumu būs iespējams ātri atjaunot, vai nē. Utilīta paskaidros, kādas rindi?as nepietiek materializēta skatā definējumā.Darbā izveidotais materializētais skats ir izmantots darbam ar transakcijām pēc darbinieka uzvārda, klienta ID, piegādes pilsētas un transakcijas summas. Materializētā skata izveido?ana notiek ar sekojo?o vaicājumu:CREATE MATERIALIZED VIEW Materializetais_Skats BUILD IMMEDIATE ENABLE QUERY REWRITE AS SELECT C.Uzvards, F.Klients, A.Pilseta, (F.Daudzums*P.Cena*(100-K.Atlaide)/100) AS Summa FROM Faktu_Tabula F, Precu_Dim P, Klientu_Dim K, Darbinieku_Dim D, Cilveku_Dim C, Adresu_Dim A WHERE F.Prece = P.Preces_Id AND F.Klients = K.Klienta_Id AND F.Darbinieks = D.Darbinieka_Id AND D.Cilveks_Fk = C.Cilveka_Id AND K.Adrese_FK = A.Adreses_ID;Materializētā skata izveido?anaSQL komandu izpildes plāna ierakstī?anai izmanto tabulu PLAN_TABLE. Vaicājuma izpildes plāna iegū?ana un ierakstī?ana tabulā PLAN_TABLE:EXPLAIN PLANSET STATEMENT_ID = 'Transakciju_skats' FOR SELECT C.Uzvards, F.Klients, A.Pilseta, (F.Daudzums*P.Cena*(100-K.Atlaide)/100) AS Summa FROM Faktu_Tabula F, Precu_Dim P, Klientu_Dim K, Darbinieku_Dim D, Cilveku_Dim C, Adresu_Dim A WHERE F.Prece = P.Preces_Id AND F.Klients = K.Klienta_Id AND F.Darbinieks = D.Darbinieka_Id AND D.Cilveks_Fk = C.Cilveka_Id AND K.Adrese_FK = A.Adreses_ID;Plāna ierakstī?anaIzpildes plāna izvade ir ierakstīts tabulā PLAN_TABLE un to var apskatīt ar SELECT vaicājumu:SELECT Statement_Id, Operation, OptionsFROM Plan_TableWHERE Statement_Id='Transakciju_skats';SELECT vaicājuma izpildeIr iespējams pārbaudīt materializētā skata neizmanto?anu ar jauno izpildes plāna ierakstī?anu un tālāko plāno apskati:EXPLAIN PLANSET STATEMENT_ID = 'Skata_neizmantosana' FOR SELECT C.Uzvards, F.Klients, A.Pilseta, (F.Daudzums*P.Cena*(100-K.Atlaide)/100) AS Summa FROM Faktu_Tabula F, Precu_Dim P, Klientu_Dim K, Darbinieku_Dim D, Cilveku_Dim C, Adresu_Dim A WHERE F.Prece = P.Preces_Id AND F.Klients = K.Klienta_Id AND F.Darbinieks = D.Darbinieka_Id AND D.Cilveks_Fk = C.Cilveka_Id AND K.Adrese_FK = A.Adreses_ID;SELECT Statement_Id, Operation, OptionsFROM Plan_TableWHERE Statement_Id='Skata_neizmantosana';9. SecinājumiDarba mēr?is tiek definēts kā izpētīt datu noliktavas tehnolo?ija. Mēr?a sasnieg?anai tika izdomāta uzskaitī?anas sistēma biroja pre?u veikalam SIA VEIKALS, kas satur informāciju par precēm, klientiem, darbiniekiem un to mijiedarbības vēsturi jeb transakcijām. Tika izdomātas datu bāzes tabulas un objekti, kā arī lo?iskas saites starp tām. Kā rezultāts, darbā ir aprakstīta darbība ar datu noliktavas tehnolo?ijām, kas ietver sevī vairākus posmus tādus kā faktu tabulas, dimensiju tabulu, materializētu skatu utt izmanto?ana. Ir definēti un izpildīti vairāki vaicājumi.Datu noliktavas ir pielietotas vairākās jomās, jo tās ?auj saglabāt un apstrādāt datus. Datu noliktavas var būt izmantotas uzskaitī?anas sistēmās, finan?u sistēmās, krājumu vadības sistēmās un vairākās citās sistēmās.Viena no stiprām pusēm ?ajā datu bāzē ir dimensiju modelē?ana. Tā ir metode, kas tiek lietota projektējot datu noliktavas datu struktūras. ?ī tehnolo?ija nodro?ina datu struktūras vieglu uztveramību un izprotamību. Dimensiju mode?a priek?rocības ir sekojo?as:Dimensiju modelis ir labi izprotams un no tā vienkār?i var iegūt nepiecie?amos datus.Visas dimensijas ir lo?iski ekvivalentas. Līdz ar to vaicājumi ir simetriski, vienveidīgi.Dimensiju modelis ir viegli papla?ināms, pievienojot jaunus faktus, jaunas dimensijas, jaunus dimensiju atribūtus. Jaunu apak?dimensiju ievie?ana.Praktiskais darbs tika izpildīts aptuveni 40 stundu laikā. Darba ietvaros ir izveidotas 5 dimensiju tabulas un viena faktu tabula, kas ir savienotas sniegpārsla veidā. Klāt izveidotām tabulām, darbā ir izpildīti un aprakstīti vairāki vaicājumi. Darbā iek?auti vaicājumi neierobe?o visus vaicājumus, kas tika izpildīti darba procesa, bet darbā ir iek?auti tikai labākos piemērus pēc autora domām. Pirmais apskatītais vaicājumu klāsts ir grupē?anas vaicājumi, kur autors papildi izpētīja vairākus grupē?anas operatorus kā ROLLUP, GROUPING_ID un citus.Otrā vaicājumu grupa izmantoja MODEL operatoru. Principā MODEL netiek izmantots bie?i, jo gandrīz visu to darbu var realizēt ar analītiskām funkcijām, bet ko nevar – parast netiek risināts datu bāzes līmenī. Bet tomēr ir izdevīgi izmantot MODEL, kad:Ir jā?enerē tie?am sare??īts vaicājums, kas ir labi caurskatāms;Atlase nav iespējama par ar analītiskām funkcijām;Vaicājums tiek ?enerēts ar augsto valodas palīdzību, jo vieglāk definēt MODEL likumus nekā analītiskie likumi;Ir veikta resursuietilpīga skait?o?ana.Pēdējā vaicājumu grupa izmantoja materializētus skatus. Darba gaita bija da?ādas grūtības. Da?a no tām tika izraisīta nepareizas sintakses dē? vai nepareizas datu bāzes vienību definē?anas secību. Vēl viena problēma bija saistīta ar to, ka PIVOT funkcija nav pieejama Oracle Developer rīkā, un bija jāmeklē cits rīks, kas atbalstu ?o funkciju. Rezultātā tika izvelēts Oracle Database 11g Express Edition, jo tas bija līdzīgāk Oracle Developer pēc darbības principa nekā citi rīki.Kaut gan darba izstrāde aiz?ēma daudz laika, tomēr darbs ir bijis interesants un dot daudz prasmju un zinā?anas, kas noteikti būs nepiecie?amas turpmākajā mācību laikā un profesionālā darbībā.Apkopojot visu izdarīto, autors ir ?oti apmierināts ar paveikto darbu, jo visa izdomāta struktūra funkcionē korekti un visi informācijas objekti mijiedarbojoties savā starpā pilnīgi saska?ā ar uzstādītu lo?iku, kas ?auj secināt, ka ?o datu bāzes paraugu var realizēt arī reālajā dzīvē.10. Izmantotās literatūras saraksts Jā?a Eiduka lekciju konspektsJā?a Eiduka disks ar piemēriemOracle? Database Data Warehousing Guide 11g Release 2 (11.2). Chapter 22 SQL for ModelingOracle? Database Performance Tuning Guide 11g Release 1 (11.1). Chapter 12 Using EXPLAIN PLANOracle? Database Data Warehousing Guide 11g Release 1 (11.1). Chapter 8 Basic Materialized ViewsOracle? Database Data Warehousing Guide 11g Release 1 (11.1). Chapter 9 Advanced Materialized ViewsThe SQL Model Clause of Oracle Database 10g. ................
................

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

Google Online Preview   Download