Po wygenerowaniu wszystkich danych do naszej tabeli przeszliśmy do następnego kroku który polegał na napisaniu plików sterujących ctl do każdych z osobna tabel.

Poniżej jest pokazana budowa z dowolnie wybranej tabeli:

Zawartość pliku sterującego: miasto.ctl

load data

infile miasto.csv

into table miasto

replace

fields terminated by ';'

(id_miasto,

nazwa_m,

ulica

)

Gdy zostały napisane 10 plików sterujących dla każdej tabeli, mogliśmy przejść do załadowania danych do naszej hurtowni danych.

Żeby można było załadować nasz pliki csv musieliśmy wywołać narzędzie sqlldr. Dokładniej przedstawiam poniżej skrypt który ładuje mi wszystkie pliki csv:

@echo

cd c:\generujemy

sqlldr system/aaa control=miasto.ctl

sqlldr system/aaa control=pracownik.ctl

sqlldr system/aaa control=towar.ctl

sqlldr system/aaa control=kwartal.ctl

sqlldr system/aaa control=dzien.ctl

sqlldr system/aaa control=wojewodztwo.ctl

sqlldr system/aaa control=stacjab.ctl

sqlldr system/aaa control=czas.ctl

sqlldr system/aaa control=dostawca.ctl

sqlldr system/aaa control=stacja_benzynowa.ctl

@ echo

Gdzie (system) oznacza – nazwa użytkownika, a (aaa)oznacza – hasło

Cały ten skrypt jest zapisany w pliku skrypt.bat który można później uruchomić w celu załadowania wszystkich plików csv naraz.

Poniżej przedstawiam cześć obrazka pokazująca uruchomienie tego skryptu

Po zakończonym ładowaniu wszystkich plików w katalogu zostały nam utworzone do każdej z tabel osobno pliki z rozszerzeniem *.log. Z nich możemy wyczytać np. czy wszystkie rekordy zostały wypełnione i czy nie nastąpiły jakieś bledy.

Poniżej przedstawiam przykładowy plik: (miasto.log)

Control File: miasto.ctl

Data File: miasto.csv

Bad File: miasto.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table MIASTO, loaded from every logical record.

Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

ID_MIASTO FIRST * ; CHARACTER

NAZWA_M NEXT * ; CHARACTER

ULICA NEXT * ; CHARACTER

Table MIASTO:

2000 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 49536 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 2000

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Wt Cze 01 01:28:58 2010

Run ended on Wt Cze 01 01:28:59 2010

Elapsed time was: 00:00:01.03

CPU time was: 00:00:00.04

Kolejnym naszym krokiem było połączenie mechanizmu PSP z naszą hurtownią danych.

Żeby użyć ten mechanizm musieliśmy go umieścić w ogranicznikach <% ....... %> a ograniczniki w kodzie HTML. Następnie zapisać go z rozszerzeniem *.psp i na końcu wywołać procedurę ładującą psp do bazy.

Poniżej przedstawiam procedurę ładowania plików wszystkich naszych plików psp do bazy.

@echo

cd c:\psp

loadpsp -replace -user system/aaa bazy_index.psp

loadpsp -replace -user system/aaa bazy_stacje.psp

loadpsp -replace -user system/aaa bazy_pracownicy.psp

loadpsp -replace -user system/aaa bazy_sprzedaz.psp

loadpsp -replace -user system/aaa bazy_sprzedaz_R.psp

loadpsp -replace -user system/aaa bazy_sprzedaz_M.psp

loadpsp -replace -user system/aaa bazy_inne.psp

loadpsp -replace -user system/aaa bazy_autorzy.psp

loadpsp -replace -user system/aaa bazy_towar.psp

loadpsp -replace -user system/aaa bazy_inne1.psp

loadpsp -replace -user system/aaa bazy_inne2.psp

loadpsp -replace -user system/aaa bazy_inne3.psp

loadpsp -replace -user system/aaa bazy_inne4.psp

loadpsp -replace -user system/aaa bazy_inne5.psp

@echo

Oraz przedstawiam obrazek przedstawiający ładowanie powyższego skryptu:

Żeby nam te pliki zadziałały to musimy jeszcze przed ich wywołaniem odpowiednio skonfigurować środowiska aby móc używać naszej strony w przeglądarce WWW. Do tego celu będzie nam potrzebny deskryptor dostępu do bazy, którego budowę przedstawiam poniżej.

BEGIN

DBMS_EPG.create_dad (

dad_name => 'mydad',

path => '/plsql/*' );

END;

/

Ale również żeby nam ten deskryptor zadziałał musimy również załadować polecenie GRANT XDBADMIN To user; który znajduje się w skrypcie zdbadmin.sql

Poniżej znajduje się obrazek przedstawiający załadowanie plików: xdbadmin.sql oraz deskryptor.sql

Gdy wykonamy wszystkie kroki które omawiane były powyżej, to będziemy mogli wejść w przeglądarkę WWW i tak jak w naszym przypadku wpisać adres url: http://localhost:8080/plsql/system.bazy_index który pokaże nam pierwszą strone startową która została wyświetlona z pliku bazy_index.psp.

Rysunek 1 . Plik znajduje się w procedurze bazy_index.psp

Rysunek 1. Przedstawia naszą stronę główną naszej Hurtowni Danych, gdzie znajduje się informacja ile posiadamy w bazie pracowników o raz ile mamy stacji

Rysunek 2 Procedura znajduje się w pliku bazy_stacje.psp

Rysunek 2. Przedstawia naszą bazę stacji benzynowych. Przedstawione są na niej wszystkie stacje oraz w jakim województwie się znajdują. Dodatkowo możemy wybrać z listy rozwijanej jedno województwo.

Rysunek 3 Procedura znajduje się w pliku bazy_pracownicy.psp

Rysunek 3. W tej tabeli zostały wyświetlona lista wszystkich pracowników których mamy zatrudnionych. Z listy rozwijanej możemy wybrać jedną z naszych stacji żeby zobaczyć ile pracowników pracuje w danej stacji.

Rysunek 4 Procedura znajduje się w pliku bazy_towar.psp.

Rysunek 4. Przedstawia tabele zakupionych towarów od danego dostawcy, jaki to jest towar oraz w jakiej ilości był kupiony. Dodatkowo możemy wybrać z listy rozwijanej rodzaj towaru i nazwę dostawcy.

Rysunek 5 Procedura znajduje się w pliku bazy_sprzedaz.psp

Rysunek 5. Przedstawia nam 2 linki ROK I MIESIĄC.

Rysunek 6 . Procedura znajduje się w pliku bazy_sprzedaz_R.psp

Rysunek 6. Przedstawia nam sprzedaż we wszystkich latach podzielona na kwartały. W tej tabeli wykorzystaliśmy operator CUBE, który stanowi rozszerzenie klauzuli GROUP BY zapytania SELECT. Zapytanie wykorzystujące operator CUBE jest semantycznie równoważne złożeniu wielu zapytań, stosujących grupowanie według wszystkich kombinacji podanych kolumn.

Przykładem tego działania operatora przedstawia rysunek powyżej, gdzie można zauważyć że w pierwszej części podsumowało nam z każdego kwartału ilość sprzedaży a pod spodem podsumowało nam wszystko. Następnie z każdego roku zostały podsumowane kwartały i podsumowany cały rok.

Dodatkowo możemy wybrać dany rok z listy rozwijanej

Rysunek 7 . Procedura znajduje się w pliku bazy_sprzedaz_M.psp

Rysunek 7. Przedstawia tabelkę sprzedaży w poszczególnych miesiącach. Do tej tabelki został użyty operator CUBE którego działanie omówiłem powyżej w punkcie 6

Rysunek 8 Procedura znajduje się w pliku bazy_inne.psp

Rysunek 8. Przedstawia nam linki do kolejnych tabel.

Rysunek 9 Procedura znajduje się w pliku bazy_inne.psp

Rysunek 9. Przedstawia nam kupno towaru w różnych okresach czasowych. Do wyświetlenia takiej tabelki użyliśmy operator GROUPING SETS, który umożliwia realizację wielu zadanych schematów grupowania w jednym przebiegu zapytania SELECT. Jego argumentem jest lista wielu zestawów kolumn grupujących. Odwołując się do obrazka powyżej to można zauważyć jak operator GROUPING SETS w pierwszej kolejności przedstawia sumę ilości kupionego towaru dla danej stacji. Później mamy sumę według poszczególnych miesięcy, nastepnie według nr dnia a na końcu według roku.

Poniżej przedstawiam zapytanie wyświetlającą taką tabelkę użytym operatorem GROUPING SETS.

<% FOR bazy_inne1 IN (

SELECT stacja.nazwa_s,czas.nazwa_miesiaca,dzien.nr_dnia,kwartal.rok,Sum(towar.ilosc)ilosc

FROM stacja_benzynowa, stacja, czas, kwartal,dzien,towar,dostawca

WHERE stacja.id_stacja=stacja_benzynowa.id_stacji

AND kwartal.id_kwartal=czas.id_kwartal

AND dzien.id_dzien=czas.id_dzien

AND czas.id_czas=stacja_benzynowa.id_czas

AND towar.id_towar=dostawca.id_towaru

AND dostawca.id_dostawca=stacja_benzynowa.id_dostawca

GROUP BY GROUPING SETS (stacja.nazwa_s,czas.nazwa_miesiaca,dzien.nr_dnia,kwartal.rok)

) LOOP %>

Rysunek 10 Procedura znajduje się w pliku bazy_inne2.psp

Rysunek 10. Przedstawia koszty utrzymania pracowników. Został tu urzyty operator ROLLUP który stanowi rozszerzenie klauzuli GROUP BY zapytania SELECT, pozwalające na wyznaczaniu wartości funkcji grupowych na rosnących poziomach agregacji. Dzięki zastosowaniu tego mechanizmu bez konieczności stosowania dodatkowych zapytań możemy w jednym zapytaniu uzyskać podsumowanie roczne pensji ze wszystkich miesięcy. Mechanizm ten zwiększa wydajność aplikacji.

Poniżej przedstawiam zapytanie wyświetlającą taką tabelkę:

<% FOR bazy_inne2 IN (

SELECT czas.nazwa_miesiaca,kwartal.rok,Sum(pracownik.wynagrodzenie)kwota

FROM pracownik,stacja,stacja_benzynowa,czas,kwartal

WHERE kwartal.id_kwartal=czas.id_kwartal

AND czas.id_czas=stacja_benzynowa.id_czas

AND pracownik.id_pracownik=stacja.id_pracownik

AND stacja.id_stacja=stacja_benzynowa.id_stacji

GROUP BY ROLLUP (czas.nazwa_miesiaca), kwartal.rok ORDER BY kwartal.rok DESC

) LOOP %>

Rysunek 11 Procedura znajduje się w pliku bazy_inne3.psp

Rysunek 11. Przedstawia Ilość dochodów uzyskanych od początku naszej działalności. Do tego celu użyliśmy Funkcji analitycznej SQL PARTITION, wyrażenie umożliwia prosty zapis wyrażeń analitycznych, które w przeszłości koniecznie wymagały stosowania podzapytań. Poniżej przedstawiam nasz przykład z napisaną funkcją analityczną PARTITION w celu zwrócenia wartości funkcji grupowej SUM() która sumuje dochód z danego towaru.

<% FOR bazy_inne3 IN (

SELECT DISTINCT nazwa_t,Sum(cena) over (PARTITION BY nazwa_t)kwota

FROM towar

) LOOP %>

Rysunek 12 Procedura znajduje się w pliku bazy_inne4.psp

Rysunek 12. Przedstawia tabelke porównujacą ilość sprzedaży i kupna poszczególnych stacji. Użyty ostał operator ROLLUP którego działanie opisywałem pod rysunkiem nr.10. który podsumowuje nam dla stacji ilość sprzedaży oraz ilość kupionego towaru.

Poniżej przedstawiam zapytanie wyświetlającą taką tabelkę:

<% FOR bazy_inne3 IN (

SELECT stacja.nazwa_s,Sum(stacja_benzynowa.ilosc_sprzedazy)ilosc_sp,towar.nazwa_t,Sum(towar.ilosc)ilosc_ku

FROM stacja,stacja_benzynowa,dostawca,towar

WHERE stacja.id_stacja=stacja_benzynowa.id_stacji

AND towar.id_towar=dostawca.id_towaru

AND dostawca.id_dostawca=stacja_benzynowa.id_dostawca

Group by ROLLUP(stacja.nazwa_s, towar.nazwa_t) ORDER BY stacja.nazwa_s, towar.nazwa_t

) LOOP %>

Rysunek 13 Procedura znajduje się w pliku bazy_inne5.psp

Rysunek 13. Przedstawia średnia ilość wydatków na towar za trzy ostatnie kwartały danego roku (wraz z bieżącym kwartałem). Została tu wykorzystana funkcja analityczna PARTITION tak zwane RUCHOME OKNO OBLICZENIOWE Grupy takie nazywane są oknami i mogą być definiowane przy pomocy wyrażeń ROWS lub RANGE. W naszym przypadku użyliśmy ROWS które definiuje tzw. Okno fizyczne, którego rozmiar określony jest liczbą rekordów.

Poniżej przedstawiam zapytanie wyświetlającą taką tabelkę:

<% FOR bazy_inne5 IN (

SELECT distinct kwartal.rok, kwartal.nr_kwartalu ,Round(avg(towar.cena) over (PARTITION BY kwartal.rok ORDER BY kwartal.nr_kwartalu RANGE BETWEEN 3 preceding AND CURRENT ROW))AS srednia

FROM towar, dostawca, stacja_benzynowa, czas, kwartal

WHERE towar.id_towar = dostawca.id_towaru AND dostawca.id_dostawca = stacja_benzynowa.id_dostawca AND stacja_benzynowa.id_czas = czas.id_czas AND

czas.id_kwartal = kwartal.id_kwartal ORDER BY kwartal.rok DESC

) LOOP %>