data warehouse, relational database, data source, ETL process, data mining, business intelligence, OLAP, decision support system, executive information system, detailed data, aggregated data, metadata, knowledge discovery in databases, enterprise data warehouse, data mart, virtual data warehouse, galactic data warehouse, dimension, fact table, cub, mapping, level, hierarchy, atribute, fact, measure, metric, star, snowflake, galaxy, constelation, drill down, roll up, rotation, slicing, section, dicing
Un depozit de date este o structură ce conţine mai multe colecţii de date, de provenienţă diferită, având dimensiuni foarte mari, folosite pentru deciziile luate la nivel tactic şi strategic în cadrul unei organizaţii. De regulă, acestea există separat de bazele de date operaţionale (bazate pe modelul relaţional). Înainte de a fi trasferate din sursele de date, informaţiile sunt supuse unor procese ETL (Extract, Transform, Load) prin care sunt filtrate şi prelucrate pentru a respecta anumite convenţii stabilite la proiectarea depozitelor de date. Această tehnologie este folosită în conjuncţie cu data mining, astfel încât volumul de informaţie stocat în depozitele de date este utilizat pentru generarea de cunoştinţe, motiv pentru care domeniul care se ocupă cu astfel de probleme, poartă denumirea de business intelligence.
Frecvent, modelului relaţional folosit în bazele de date (care este reprezentat în 2 dimensiuni: atribute şi înregistrări) i se adaugă şi dimensiunea timp, astfel că informaţiile reţinute au caracter istoric şi sunt orientate pe subiecte.
Merită reţinute şi definiţiile date pentru depozite de date de către cercetătorul american William Harvey Inmon, considerat părintele acestei tehnologii precum şi de către consiliul OLAP. Potrivit lui W. H. Inmon, depozitul de date este o colecţie de date orientate pe subiecte, integrate, istorice şi nevolatile, destinate sprijinirii procesului de luare a deciziilor manageriale. Consiliul OLAP defineşte depozitul de date ca fiind o stocare centralizată a datelor detaliate provenite din toate sursele relevante din cadrul unei organizaţii ce permite interogarea dinamică şi analiza detaliată a tuturor informaţiilor.
Se poate concluziona că depozitul de date reprezintă soluţia optimă pentru organizarea datelor în cazul unor aplicaţii de tip sisteme suport pentru decizii sau sisteme informatice executive. Rolul său constă în a oferi o imagine coerentă asupra datelor relative la activitatea unei organizaţii şi a contextului în care aceasta acţionează.
Costul dezvoltării unui depozit de date este destul de mare, iar investiţia se amortizează într-un orizont de timp mai mare.
Un indicator al utilităţii depozitului de date în contextul organizaţiei este reprezentat de dimensiunea sa, care ar trebui să se dubleze (ca urmare a operaţiilor ETL) într-un interval de aproximativ 12-18 luni.
Principalele caracteristici ale unui depozit de date sunt:
Într-un depozit de date se disting datele detaliate, relativ recente din punct de vedere al încărcării, date agregate (sintetizate) – care presupun un grad de prelucrare în prealabil – precum şi metadatele prin care se precizează structura, provenienţa, regulile de agregare ale informaţiilor reţinute.
Structural, un depozit de date poate fi descris în funcţie de componentele pe care le deţine, în funcţie de nivelurile pe care este organizat precum şi raportat la arhitectura funcţională.
Din punctul de vedere al componentelor care îl formează, distingem sursele de date, depozitul de date şi instrumentele de acces (interfeţe de analiză).
În categoria surslor de date, un aport important îl au bazele de date operaţionale ale organizaţiei, la care se adaugă amprenta de timp corespunzătoare, precum şi diferite informaţii arhivate sau date externe, referitoare la domeniul economic în care îşi desfăşoară activitatea organizaţia sau cu privire la clienţii, respectiv partenerii de afaceri ai acesteia.
Întrucât sursele de date au – de cele mai multe ori – caracter eterogen, acestea trebuie supuse unor procese de transformare înainte de a fi transferate propriu-zis în depozitele de date urmând a fi folosite fie direct, fie sub forma unor informaţii agregate.
Procesul ETL presupune, aşadar, următoarele etape:
Având în vedere aceste aspecte, o problemă importantă este reprezentată de mecanismul de integrare a datelor, astfel încât acestea să asigure o viziune consistentă şi coerentă a organizaţiei pentru orizontul de timp vizat. Prin urmare trebuie avute în vedere următoarele aspecte:
Astfel stocate în depozitele de date (deopotrivă date preluate din diferite surse de date şi informaţii agregate), informaţiile pot fi exploatate în mai multe moduri, în funcţie de scopul în care se doresc a fi folosite. Se pot utiliza data mart-uri care sunt constituite dintr-un anumit segment al depozitelor de date, cunoştinţele din acestea fiind supuse prelucrărilor prin instrumentele de acces (interfeţe de analiză) – produse software ce au în vedere analiza acestora. Frecvent, se recurge la tehnologii precum data mining sau OLAP.
Interfeţele data mining asigură transformarea datelor în cunoştinţe prin tehnici ce ţin de analiza statistică superioară sau inteligenţă artificială.
Instrumentele OLAP au la bază reprezentarea multidimensională a informaţiilor şi permit accesul la date într-un mod interactiv, în funcţie de nivelul la care doresc să le vizualizeze (detaliat sau generic).
Această arhitectură pe componente poate fi analizată şi în funcţie de nivelurile (eng. tier) la care sunt văzute datele sau din punct de vedere funcţional.
Astfel, arhitectura pe niveluri identifică un nivel inferior, format din sursele de date şi depozitul de date propriu-zis precum şi din toate mecanismele pentru transformarea acestora (ETL), un nivel mediu format din instrumentele specializate pentru analiza datelor (data mining sau OLAP) şi un nivel superior care accesează datele care au fost generate.
Din punct de vedere funcţional se disting:
O viziune a depozitului de date din punct de vedere funcţional permite proiectarea acestuia raportat la cerinţele de afaceri.
Clasificarea depozitelor de date se poate face în funcţie de criteriile avute în vedere. Astfel, se pot distinge aria de cuprindere, procesele decizionale pentru care au fost proiectate precum şi modelul de date implementat.
Astfel, în funcţie de aria de cuprindere se pot distinge:
Dacă depozitele de date de la nivelul organizaţiei conţin toate informaţiile referitoare la organizaţia respectivă (atât date extrase ca atare din diferite surse cât şi informaţii agregate), având dimensiuni foarte mari şi implicând costuri destul de importante atât pentru proiectare cât şi pentru implementare, concentrările de date sunt formate dintr-un subset de informaţii care privesc doar un anumit domeniu (subiect) al organizaţiei conţinând, de regulă, doar date obţinute prin agregare. Astfel, concentrările de date sunt considerate ca fiind un subansamblu al unui depozit de date mai ieftin, mai uşor de construit şi de intreţinut. Mai rar utilizate sunt depozitele de date virtuale, în fapt nişte vederi ale informaţiilor reţinute în bazele de date relaţionale, soluţia fiind adoptată doar atunci când numărul de înregistrări este mai mic, întrucât operaţiile (interogările propriu-zise, eventual agregările) sunt realizate de către sistemele de gestiune pentru baze de date, a căror scalabilitate este redusă.
Raportat la procesele decizionale pentru care sunt proiectate, se pot distinge:
Depozitul de date de tip organizațional (eng. Galatic Data Warehouse) cuprinde toate informaţiile de la nivelul companiei, având ca obiectiv integrarea şi prelucrarea tuturor datelor cu relevanţă pentru aceasta.
Depozitele de date departamentale sau orientate pe procese de afaceri reprezintă un subset al depozitului de date organizaţional, orientat pe diferite subiecte (departament sau proces de afaceri), fiind proiectate în scopul asigurării unei concurenţe a utilizatorilor şi scalabilităţi cât mai bune, exprimate sub forma timpului de răspuns.
În aceeaşi categorie se înscriu şi concentrările de date specializate (având acelaşi tip de subiecte), diferenţa faţă de depozitele de date fiind faptul că nu conţin date detaliate, la nivelul acestora fiind folosite numai informaţii agregate pentru generarea unor rapoarte.
Din punctul de vedere al modelului de date adoptat, depozitele de date sunt împărţite în:
Un depozit de date relaţional se foloseşte în cazul când datele provin dintr-o bază de date care îl şi gestionează sau dacă se folosesc depozite de date virtuale.
Un depozit de date multidimensional defineşte dimensiuni şi ierarhii ale datelor (conform modelului multidimensional), aceasta reprezentând varianta optimă de folosire a acestei tehnologii, putându-se procesa un volum mare de date prin intermediul serverului multidimensional care dispune de diferite optimizări pentru procesul de analiză.
Un depozit de date hibrid foloseşte modelul multidimensional pentru stocarea datelor istorice şi modelul relaţional pentru datele curente, oferind o perspectivă completă pentru toate informaţiile existente la nivelul organizaţiei.
Întrucât în depozitele de date operaţiile mai frecvente sunt cele de analiză a informaţiilor reţinute (aceasta reprezintând o caracteristică ce distinge depozitele de date faţă de bazele de date relaţionale unde cele mai frecvente operaţii erau cele de actualizare), precum şi obţinerea unor perspective diferite asupra lor în funcţie de nivelul de agregare dorit, a fost necesară dezvoltarea unui model care să permită un mod de organizare a datelor care să permită astfel de prelucrări. Iniţial s-a pornit de la diferite extensii ale modelului relaţional (Gray, Li-Wang, Gyssens-Lakshmanan) ajungându-se la modelul lui Ralph Limball care defineşte cubul n-dimensional prin intermediul unei scheme de tip stea. De aici, s-a ajung la modele de tip fulg de nea sau galaxie, din care s-au inspirat apoi şi modelele propuse de Gupta şi Sarawagi sau Cabibbo şi Torlone.
Modelul multidimensional implică definirea obiectelor referitoare la tabele de fapte cu atribute de tip măsuri (metrici), a obiectelor de tip dimensiuni pentru care se definesc ierarhii precum şi diferite alte atribute (identificare, descriere). Modelul multidimensional al datelor poate fi descris la nivel conceptual (al înţelegerii utilizatorilor, fără legătură cu modul de implementare propriu-zis), la nivel logic (conceptele pot fi înţelese în continuare de utilizatori, însă sunt adaptate sistemelor de gestiune pentru care acestea sunt implementate) şi la nivel fizic (care descriu modul în care sunt stocate datele fizic).
Dimensiunile sunt structuri formate din atribute grupate pe mai multe niveluri ale unei ierarhii, astfel încât informaţiile pot fi vizualizate mai detaliat sau mai sintetic, în funcţie de cerinţele operaţionale. O definiţie formulată de consiliul OLAP consideră dimensiunea ca fiind un atribut structural al unui cub care constă dintr-o listă de membri, percepuţi ca fiind de acelaşi tip (de exemplu toate lunile, trimestrele, anii formează dimensiunea timp); dimensiunile reprezintă un mod foarte concis, intuitiv de organizare şi selectare a datelor pentru explorare şi analiză.
Ierarhiile reprezintă structurile în care se organizează atributele unei dimensiuni, pe baza relaţiei părinte-copil, semnificaţia acesteia fiind că părintele este obţinut prin agregarea mai multor copii. Acestea sunt utilizate pentru navigarea în cadrul dimensiunilor în funcţie de nivelul de detaliere / sinteză dorit.
Nivelurile definesc poziţiile în cadrul ierarhiilor, relaţiile dintre acestea raportându-se şi la noţiunea de generaţie, conform definiţiei consiliului OLAP: doi membri ai unei ierarhii sunt de aceeaşi generaţie dacă au acelaşi număr de strămoşi. Termenii de generaţie şi nivel sunt necesari pentru a descrie subgrupuri de membri întrucât, de exemplu, deşi doi fraţi membri au acelaşi părinte şi sunt de aceeaşi generaţie, ei ar putea să nu fie la acelaşi nivel, dacă unul dintre fraţi are copil şi celălalt nu.
Atributele reprezintă caracteristicile unei dimensiuni care se pot găsi doar la un singur nivel al unei ierarhii. Se disting atributele de identificare (pentru dimensiune în sine sau pentru un nivel al ierarhiei), respectiv atributele de descriere care realizează o clasificare a datelor în cadrul ierarhiei.
Faptele sunt tabele caracterizate prin legături către dimensiuni, având şi atribute proprii care sunt denumite măsuri (metrici), conţinând de regulă date care pot fi sintetizate (agregate) pentru fiecare nivel din ierarhiile definite pentru dimensiunile referite permiţând o analiză cu un grad de detaliere diferit. Măsurile (metricile) au relevanţă doar în contextul unor anumite dimensiuni şi pot fi clasificate în funcţie de modalitatea de calcul, tipul de funcţii agregate utilizate, modul de calcul raportat la dimensiuni. Astfel, în funcţie de modalitatea de calcul, se definesc măsuri de bază (existente în sursele de date fiind preluate ca atare) şi măsuri derivate, obţinute prin combinarea măsurilor de bază, fiind precizate prin formula utilizată la obţinerea lor. Raportat la tipul de funcţii agregate se disting măsurile distributive, măsurile algebrice şi măsurile holistice. În privinţa modului de calcul raportat la dimensiuni există indicatori aditivi, indicatori semiaditivi şi indicatori neaditivi.
COUNT()
. Funcţiile algebrice sunt obţinute prin aplicarea unei funcţii agregate distributivă (AVG() = SUM() / COUNT()
; la fel, alte exemple din această categorie sunt MIN()
, MAX()
, STDEV()
). Funcţiile holistice nu sunt limitate constant pe spaţiul de stocare cerut de deschiderea subagregării (exemple în acest sens sunt MEDIAN()
, MODE()
, RANK()
).
Metadatele sunt considerate cea mai importantă componentă a unui depozit de date întrucât ele nu doar descriu structura de date respectivă ci reţin şi provenienţa datelor precum şi algoritmii utilizaţi pentru agregarea în funcţie de nivelurile definite la nivelul dimensiunii.
Tot aici sunt reţinute şi transformările pe care le suportă informaţiile de la preluarea lor din sursele de date (relaţionale) până la încărcarea lor în depozitul de date. De asemenea, metadatele reţin şi anumite moduri de vizualizare, în condiţiile în care unele dintre acestea sunt mai utilizate decât altele. Acestea asigură şi calitatea datelor definind valorile valide pentru fiecare atribut din depozit, astfel încât înainte de încărcarea lor pot fi aplicate anumite reguli de corecţie. Prin metadate se asigură şi gestiunea versiunilor, întrucât regulile care se aplică asupra datelor pot fi schimbate odată cu trecerea timpului, având în vedere dependenţa lor şi de acest atribut.
Modelele de reprezentare ale depozitelor de date (sub formă de schemă) fie utilizează extensii ale modelului relaţional, fie structurează obiectele acestuia sub forma unei structuri de tip cub multidimensional.
Ca extensii ale modelului relaţional se folosesc schemele pentru depozite de date propuse de Ralph Kimball:
În schema de tip stea, în centru se află una sau mai multe tabele de fapte care sunt în relaţie cu dimensiunile, suportând două tipuri de interogări: consultare – care se realizează doar pe tabela de fapte – şi joncţiune în care restricţiile cu privire la atribute se fac între tabela de fapte şi dimensiuni. Dimensiunile sunt denormalizate, întrucât nu există alte legături între tabele cu excepţia celor dintre tabela de fapte şi dimensiuni. Astfel, schema depozitului de date este formată dintr-un set de atribute {A1, A2, …, An}
, o tabelă de fapte f
şi m
dimensiuni d1, d2, …, dm
. Atât tabela de fapte f
cât şi dimensiunile d1, d2, …, dm
sunt formate dintr-un subset distinct de atribute {Ak | k = 1..n}
.
{Ak | k = 1..n}
care nu fac parte din dimensiunile d1, d2, …, dm
se numesc măsuri (metrici) întrucât caracterizează tabela de fapte.
Deşi acest tip de schemă are avantajul unei regăsiri foarte rapide a informaţiilor, totuşi implică o inflexibilitate destul de mare.
Sintetizând, caracteristicile schemei de tip stea sunt:
O variantă a schemei de tip stea este schema de tip fulg de nea unde unele dintre dimensiuni sunt normalizate, datele fiind distribuite în alte tabele. Astfel, se menţine redundanţa controlată din cadrul bazelor de date relaţionale, întreţinerea depozitului de date fiind mai facilă (întrucât se evită anomaliile care pot apărea în cazul unor operaţii de actualizare), economisindu-se totodată şi spaţiul de stocare. Totusi, operaţiile de actualizare în cazul unui depozit de date nu sunt foarte frecvente, iar economia spaţiului de stocare este neglijabilă în comparaţie cu volumul de date reţinut. Dezavantajul acestei structuri e legată de timpul de regăsire care creşte în cazul unei interogări întrucât presupune joncţiuni între mai multe tabele.
O schemă de tip galaxie reprezintă o asociere între mai multe scheme de tip stea, conţinând tabele suplimentare pentru datele agregate. Legătura se face prin intermediul unei dimensiuni. În cadrul galaxiei există o stea principală (centrală) care conţine măsurile de bază în timp ce în celelalte stele sunt distribuite măsurile derivate. Această schema de proiectare a depozitelor de date prezintă atât avantaje cât şi dezavantaje: pe de o parte măsurile de bază (atomice) sunt menţinute independent de măsurile derivate, iar pe de altă parte determinarea măsurilor derivate implică asigurarea unor mecanisme pentru realizarea coerenţei cu măsurile de bază.
În schema de tip constelaţie există mai multe legături între dimensiunile depozitului de date, acestea fiind complet denormalizate. Legăturile dintre tabelele de fapte sunt realizate tot prin intermediul dimensiunilor, neexistând o legătură directă între ele. O astfel de abordare este urmată dacă depozitul de date este construit dintr-o bază de date relaţională, aceasta contribuind la întreţinerea acestei structuri.
O altă metodă de reprezentare a datelor care respectă modelul multidimensional este cubul de date (n-dimensional), definit într-un sistem cartezian ce are drept coordonate atributele depozitului de date. Desigur, cubul de date reprezintă un spaţiu de date logic, nicidecum fizic. De regulă întrucât de obicei numărul de dimensiuni depăşeşte cifra 3, se foloseşte noţiunea de hipercub, definită ca un grup de celule de date aranjate după dimensiunile datelor; o matrice tridimensională poate fi vizualizată ca un cub unde fiecare dimensiune formează o faţă a cubului. De obicei, coordonatele cele mai frecvent întâlnite în reprezentarea depozitelor de date sunt timpul, produsele, regiunile geografice, canalele de distribuţie. Prin cuburi se pot realiza diferite secţiuni care poartă denumirea de tablouri.
Un cub cu 3 sau 4 dimensiuni poate fi reprezentat facil în plan, însă pentru mai multe dimensiuni trebuie apelat la anumite modele, ca de exemplu Agrawal-Gupta (care definesc un hypercub ale cărui elemente pot fi 0, 1 respectiv un n-tuplu, propunând şi un set de operatori care acţionează asupra acestuia) sau Golfarelli-Maio-Rizzi care folosesc un graf bazat pe o schemă de fapte.
Se doreşte proiectarea unui depozit de date pentru gestiunea tranzacţiilor dintr-o librărie, prin preluarea informaţiilor din baza de date relaţională, utilizată şi în laboratoarele precedente:
Se observă că s-a obţinut un depozit de date de tip constelaţie în care tabelele de fapte sunt comenzi aprovizionare şi facturi, legăturile intre acestea fiind realizate prin intermediul dimensiunilor timp şi carte. Fiecare dintre ele mai este legată de o dimensiune (comenzi aprovizionare de dimensiunea furnizor, iar facturi de dimensiunea utilizatori). Întrucât nu s-a procedat la denormalizarea bazei de date , dimensiunea carte prezintă în continuare legături către alte patru dimensiuni. Metricile din cadrul tabelelor de fapte sunt cantitate (indicator aditiv) şi stare (indicator neaditiv).
supply_order
conţine şi supply_order_detail
, iar invoice
include şi invoice_detail
).
De asemenea, pentru dimensiuni au fost definite ierarhii, astfel: pentru dimensiunea D_TIME
nivelurile day
, month
, quarter
şi year
; pentru dimensiunea D_BOOK
nivelurile book
(cu atributele din tabela corespunzătoare), series
şi genre
, pentru dimensiunea D_SUPPLIER
nivelurile supplier
, town
, region
, country
iar pentru dimensiunea D_USER
nivelurile user
, type
şi role
.
În construirea ierarhiilor, de remarcat sunt relaţiile de incluziune:
day < month < quarter < year
book < series < genre
supplier < town < region < country
user < type { (regular administrator, super admininstrator), (client, supplier) } < role { administrator, third party }
În acest mod, se pot obţine rapoarte cum ar fi:
În funcţie de nivelul la care se situează în ierarhia specifică unei anumite dimensiuni, rapoartele pot fi mai detaliate sau mai sintetice.
Indiferent de modelul adoptat în proiectarea unui depozit de date (relaţional sau multidimensional), operaţiile care pot fi realizate asupra sa sunt aceleaşi, de vreme ce reprezentarea datelor este similară. Din acest motiv, şi transformarea datelor se face foarte uşor.
unde OLAP = OnLine Analytical Processing.
Operaţiile pot fi folosite de utilizator pentru a avea diferite perspective asupra datelor, fie având un grad de detaliere mai mare sau mai mic (prin agregare), fie pentru a observa anumite dependenţe prin eliminarea unor coordonate (atribute), respectiv extrăgând un anumit subset de date.
Operaţii de navigare pe diferite niveluri ale ierarhiei unei anumite dimensiuni sunt de:
Deşi cele mai multe dintre instrumentele de analiză asociate depozitelor de date pot realiza astfel de operaţii în mod dinamic, se obişnuieşte ca unele valori globale să fie precalculate această tehnică purtând denumirea de consolidare, însumare sau agregare după aspectele care sunt luate în considerare (conceptual, procedural, structural). Aceste valori sunt măsurile tabelelor de fapte, iar consolidarea se face după dimensiunile corespunzătoare ei, nivel cu nivel. Operaţiile propriu-zise urmărite în cadrul consolidării sunt de cele mai multe ori totaluri, însă pot fi folosiţi şi alţi operatori, în cele mai multe cazuri statistici. Nivelul pentru care se realizează consolidarea poartă numele de granularitate. Rezultatele astfel obţinute sunt reţinute în depozitul de date, crescându-i performanţa, fără ca dimensiunea acestuia să crească semnificativ, de vreme ce numărul de valori scade (exponenţial) pe măsură ce se avansează în ierarhie.
Spre exemplu, volumul vânzărilor pentru o carte poate fi calculat doar pentru o localitate, pentru o regiune sau chiar pentru întreaga ţară.
Cele mai frecvente tipuri de operaţii folosite în cazul depozitelor de date sunt rotaţiile, oferind utilizatorului mai multe posibilităţi de vizualizare a informaţiilor reţinute. Pentru un cub n-dimensional există Pn = 1 x 2 x … x n posibilităţi de rotaţie, aducând în prim-plan o anumită faţetă (bidimensională) motiv pentru care o astfel de operaţie mai poartă denumirea de data slicing.
Astfel, poate fi vizualizat volumul de vânzări pentru o carte în funcţie de regiuni, respectiv distribuţia volumului de vânzări a diferitelor volume dintr-o anumită regiune.
De asemenea, pot fi realizate secţiuni prin cubul de date acestea presupunând limitarea unor anumite atribute la anumite valori, astfel obţinându-se un cub de date redus, motiv pentru care o astfel de operaţie mai poartă denumirea de dicing. Din punct de vedere geometric, nu este necesar ca rezultatul obţinut să aibă structura unui cub (chiar de dimensiuni mai mici decât cubul de date iniţial), acesta putând avea orice forme de la dreptunghi (se consideră o singură valoare pentru un atribut) la paralelipiped dreptunghic (în cazul cuburilor de date cu 3 dimensiuni).
De exemplu, poate fi vizualizat volumul vânzărilor doar pentru anumite cărţi, doar pentru un interval de timp (o lună, un trimestru) şi doar pentru anumite regiuni.
Pentru depozitele de date care sunt reţinute sub forma unor baze de date relaţionale, există unele extensii ale unor operatori relaţionali ce permit realizarea unor operaţii de consolidare. Aceștia au fost propuşi în anul 1998 de către cercetătorul american Paul Gray și sunt implementate de către limbajul SQL.
Operatorul CUBE
permite generarea tuturor combinaţiilor posibile pentru operaţiile de consolidare din cadrul unui cub de date. El suportă clauza GROUPING
prin care se face distincţia între valorile NULL
ale tabelei de fapte şi informaţiile obţinute prin agregare.
Operatorul ROLLUP
permite realizarea unor operaţii de consolidare pentru anumite atribute (specificate) prin intermediul operatorilor definiţi de către sistemul de gestiune al bazei de date, dintre care cel mai frecvent utilizaţi sunt SUM()
, COUNT()
, AVG()
, STDEV()
. Întrucât acest operator implică folosirea unor funcţii de grup, el va fi utilizat în conjuncţie cu clauza GROUP BY
.
Având în vedere definiţia acestor operatori, CUBE(ROLLUP)=CUBE
.
Un depozit de date poate fi proiectat pornind de la unele elemente care stau la baza implementării sale ulterioare, existând şi în acest caz mai multe abordări:
Ca mod de lucru se pot folosi:
Etapele dezvoltării unui depozit de date pentru o instituţie sunt:
Implementarea propriu-zisă a depozitului de date implică dezvoltarea unor subsisteme care vor ajuta la exploatarea sa ulterioară şi anume:
Ulterior procesului de implementare şi de încărcare cu date va urma etapa de testare, după care depozitul de date va putea fi adoptat la nivelul organizaţiei.
Procesul de întreţinere vizează încărcarea periodică a depozitului de date, consultarea de statistici cu privire la utilizarea lui, menţinerea calităţii datelor, evaluarea dimensiunii sale şi refacerea datelor în caz de eroare.
Oracle Warehouse Builder 11gR2 reprezintă un produs integrat cu serverul pentru baze de date Oracle, fiind folosit pentru integrarea datelor, aplicaţii cu depozite de date, gestiunea calităţii datelor şi a metadatelor.
Funcţionalităţile pe care le oferă Oracle Warehouse Builder 11gR2 sunt:
Oracle Warehouse Builder 11g este o aplicaţie foarte flexibilă, putând lucra cu orice surse de date, prelucrându-le în formate interfaţabile cu orice aplicaţie.
Arhitectura Oracle Warehouse Builder 11gR2 conţine, la nivel de server depozitul propriu-zis (Oracle Warehouse Builder Repository), spaţiile de lucru (eng. workspaces), serviciul şi agentul centrului de control (control center service/agent) şi schemele ţintă (eng. target schemas), iar la nivel de client modulul de gestiune a centrului de control (Control Center Manager), modulul de proiectare (Design Center), modulul de vizualizare a depozitului (Repository Browser).
Produsul Oracle Warehouse Builder 11gR2 implementează un unic depozit de date centralizat pentru instanţa bazei de date care conţine nişte obiecte preinstalate în cadrul schemelor OWBSYS
şi OWBSYS_AUDIT
care trebuie activate. Toate obiectele depozitelor de date vor fi reţinute în cadrul acestor scheme, chiar dacă vor fi definite alte scheme.
Pentru a putea folosi produsul Oracle Warehouse Builder trebuie specificate nişte spaţii de lucru, care permit vizualizarea obiectelor specifice pentru un anumit utilizator în loc de a consulta întreg depozitul de date. Un spaţiu de lucru corespunde unui anumit proiect.
Se recomandă ca pentru crearea sau pentru gestiunea spaţiilor de lucru să se folosească utilitarul Repository Assistant.
În configurarea unui spaţiu de lucru se vor preciza:
OWBSYS
(nume de utilizator şi parola) – după ce acesta este activat (din cadrul Enterprise Manager);
Fiecare spaţiu de lucru are un centru de control implicit asociat care este pornit şi oprit prin intermediul serviciului corespunzător. Aici sunt reţinute informaţii detaliate cu privire la sarcinile care sunt executate, acestea putând fi accesate fie prin intermediul obiectelor fie prin intermediul acţiunii propriu-zise. Pot fi dezvoltate şi alte centre de control pentru a dezvolta depozite de date pentru alte sisteme şi un singur centru de control poate fi activ la un moment dat.
Toate datele din proiectul Oracle Warehouse Builder sunt reţinute în scheme ţintă sub forma unor obiecte ale bazei de date cum ar fi tabele, viziuni, dimensiuni sau cuburi. De regulă, există o singură astfel de schemă ţintă care este creată prin intermediul unui modul care conţine toate obiectele de date.
Lucrul cu depozite de date folosind Oracle Warehouse Builder 11gR2 este accesibil prin intermediul aplicaţiei Design Center, funcţionalităţile sale devenind disponibile numai după specificarea datelor de autentificare specifice unui spaţiu de lucru.
Pe lângă specificarea numelui de utilizator precum şi a parolei, vor trebui indicate şi detaliile pentru conectarea la baza de date şi anume: maşina pe care rulează serverul de baze de date, portul unde se poate realiza conexiunea precum şi denumirea serviciului asociat bazei de date respective.
În Design Center se va afişa spaţiul de lucru asociat utilizatorului respectiv (în cazul în care unui utilizator îi corespund mai multe spaţii de lucru va fi posibil accesul la toate, însă numai unul dintre ele va fi activ la un moment dat).
Ulterior va trebui creat un proiect, care va fi identificat prin intermediul unei denumiri.
Un proiect Oracle Warehouse Builder 11gR2 conține două module:
Pentru fiecare modul, se va defini o locaţie (fizică), indicând schema care va fi folosită fie pentru extragerea de informaţii, fie pentru încărcarea lor. Locaţia este definită printr-un nume de utilizator şi parolă, prin adresa serverului de baze de date şi portul pe care se realizează conexiunea, precum şi prin denumirea serviciului asociat bazei de date respective. De asemenea, trebuie să se precizeze numele schemei care va fi utilizată precum şi versiunea serverului de baze de date utilizat.
În cazul modulului sursă, se va bifa opţiunea Import after finish, întrucât după definirea propriu-zisă a locaţiei, datele să poată fi pregătite pentru a fi transferate în depozitul de date. Vor trebui precizate tipurile de obiecte disponibile pentru a fi preluate (tabele, vizualizări materializate, cozi, dimensiuni, cuburi, tabele externe, vizualizări, secvenţe, transformări, sinonime, tipuri definite de utilizatori). Vor fi indicate doar tabelele care folosesc drept sursă de informaţie pentru depozitul de date, acestea fiind ulterior create.
Pentru modulul destinaţie se poate indica altă schemă spre a fi creat depozitul de date, sau se poate folosi utilizatorul OWBSYS
şi obiectele asociate.
Un modul se defineşte în cadrul tipului de bază de date asociat, fiind identificat printr-o denumire, un statut şi o locaţie care trebuie să fie accesibilă la momentul în care este creat modulul respectiv. Se pot crea oricât de multe module, însă de regulă, sunt specificate doar modulul sursă şi destinaţie, legătura dintre acestea stabilindu-se prin regulile de transformare ale informaţiilor pornind de la extragerea din sursele de date şi până la încărcarea lor în depozitul de date corespunzător (în dimensiuni sau cuburi - în Oracle Warehouse Builder 11gR2 printr-un cub se înţelege o tabelă de fapte ce are legătură cu una sau mai multe dimensiuni prin intermediul atributelor sale - chei străine).
În cadrul modulului destinaţie se vor defini trei tipuri de obiecte:
Obiect | Tip Obiect | Componente | Atribute Componentă |
---|---|---|---|
D_PUBLISHING_HOUSE | dimensiune | PUBLISHING_HOUSE_LEVEL | id name registered_number description address |
D_SERIES | dimensiune | SERIES_LEVEL | id name description |
D_GENRE | dimensiune | GENRE_LEVEL | id name description |
D_BOOK | dimensiune | BOOK_LEVEL | id title description publishing_house_id printing_year edition stockpile price |
SERIES_LEVEL | series_id |
||
GENRE_LEVEL | genre_id |
||
D_WRITER | dimensiune | WRITER_LEVEL | id first_name last_name biography |
D_AUTHOR | dimensiune | AUTHOR_LEVEL | id book_id writer_id |
D_PROVIDER | dimensiune | PROVIDER_LEVEL | id name registered_number description |
TOWN_LEVEL | town |
||
REGION_LEVEL | region |
||
COUNTRY_LEVEL | country |
||
D_USERS | dimensiune | USERS_LEVEL | personal_identifier first_name last_name address phone_number email username password |
TYPE_LEVEL | type |
||
ROLE_LEVEL | role |
||
C_SUPPLY_ORDER | tabelă de fapte (cub) | dimensiuni | BOOK PROVIDER |
metrici (măsuri) | issue_date state quantity |
||
C_INVOICE | tabelă de fapte (cub) | dimensiuni | BOOK USERS |
metrici (măsuri) | issue_date state quantity |
Pentru a implementa o dimensiune, se va selecta opțiunea New Dimension din meniul contextual al secțiunii Dimensions din cadrul modulului destinație.
Crearea unei dimensiuni implică 8 paşi:
Βusiness
.
Proprietățile cu care va fi creată dimensiunea la accesarea butonului Finish sunt prezentate sintetic.
Pentru fiecare dimensiune definită, Oracle Warehouse Builder 11gR2 generează cod (PL/SQL) pentru a implementa obiectele. Din meniul contextul asociat dimensiunii respective, se va selecta opţiunea Deploy.
Rezultatul operațiilor de validare a definiției dimensiunii, de generare a codului și de implementare propriu-zisă (sub forma unei tabele în cadrul bazei de date Oracle) va fi prezentat în cadrul unei ferestre de jurnalizare.
Pentru a implementa o tabelă de fapte (cub), se va selecta opțiunea New Cube din meniul contextual al secțiunii Cubes din cadrul modulului destinație.
Crearea unei tabele de fapte (cub) implică 4 paşi:
Înainte de a se crea propriu-zis obiectul de tip tabelă de fapte (cub) prin accesarea butonului Finish sunt prezentate atributele acestuia (denumire, dimensiuni referite, măsuri - metrici), metodele de agregare pentru fiecare dintre metrici în parte, per dimensiune, precum și nivelurile din cadrul ierarhiilor dimensiunilor referite pentru care vor fi precalculate informațiile, conform operațiilor de agregare.
SUM
.
De regulă, se poate întâmpla ca unele proprietăți ale obiectului tabelă de fapte (cub) să fie necesar a fi modificate. În acest scop, se va accesa opțiunea Open din cadrul meniului contextual asociat obiectului tabelă de fapte (cub).
În secțiunea Dimensions, pentru fiecare dimensiune în parte, va fi selectat nivelul de la baza ierarhiei (care conține datele cele mai specifice), astfel încât să poată fi realizate toate categoriile de operații cu datele conținute în cadrul acestui obiect.
În secțiunea Aggregation, pentru fiecare măsură (metrică) în parte se va preciza o funcție de agregare precum și nivelurile pentru care se vor realiza totaluri precalculate.
NOAGG
, generându-se o excepție atunci când se încearcă validarea tabelei de fapte (cubului).
FIRST
sau LAST
, întrucât nu implică un efort de calcul suplimentar.
Pentru fiecare tabelă de fapte (cub) definită, Oracle Warehouse Builder 11gR2 generează cod (PL/SQL) pentru a implementa obiectele. Din meniul contextul asociat dimensiunii respective, se va selecta opţiunea Deploy.
Rezultatul operațiilor de validare a definiției tabelei de fapte (cubului), de generare a codului și de implementare propriu-zisă (sub forma unei tabele în cadrul bazei de date Oracle) va fi prezentat în cadrul unei ferestre de jurnalizare.
O mapare indică o legătură între un obiect al modulului sursă și un alt obiect al modulului destinație (dimensiune sau tabelă de fapte - cub) în scopul încărcării depozitului de date cu înregistrări (propriu-zise sau agregate).
Pentru a implementa o mapare, se va selecta opțiunea New Mapping din meniul contextual al secțiunii Mappings din cadrul modulului destinație.
Crearea unei mapări implică doi paşi:
Obiectele între care se vor stabili legături sunt aduse în spaţiul de lucru prin operaţii drag-and-drop din modulele sursă, respectiv destinaţie, iar legăturile sunt de regulă de tipul 1:1 (în cazul dimensiunilor cu un singur nivel), respectiv 1:n (în cazul dimensiunilor cu mai multe niveluri sau în cazul cuburilor).
Pentru fiecare mapare definită, Oracle Warehouse Builder 11gR2 generează cod (PL/SQL) pentru a implementa obiectele. Din meniul contextul asociat dimensiunii respective, se va selecta opţiunea Deploy.
Rezultatul operațiilor de validare a definiției mapării, de generare a codului și de implementare propriu-zisă (sub forma unei tabele în cadrul bazei de date Oracle) va fi prezentat în cadrul unei ferestre de jurnalizare.
Ulterior, se poate realiza transferul propriu-zis al datelor (operația de încărcare a depozitului de date), prin accesarea opțiunii Start din meniul contextual asociat mapării respective.
Rezultatul operației de încărcare este prezentat tot în cadrul ferestrei de jurnalizare, indicându-se numărul de înregistrări selectate, adăugate, modificate, șterse, numărul de erori / avertismente înregistrate, momentul de timp la care a fost începută operația precum și durata operației (exprimată în secunde).
După ce s-a realizat operația de încărcare, conținutul obiectului din cadrul modulului destinație în care au fost transferate datele (dimensiune sau tabelă de fapte - cub) poate fi consultat prin accesarea opțiunii Data… din cadrul meniului contextual asociat obiectului respectiv.
Vizualizarea propriu-zisă a datelor este posibilă prin accesarea butonului Execute. În cadrul ferestrei de vizualizare, poate fi realizată navigarea de-a lungul nivelurilor ierarhiei (pentru o dimensiune), respectiv datele vor fi afișate în format tabular (pentru o tabelă de fapte - cub).
Generarea obiectelor prin intermediul cărora se va implementa propriu-zis depozitul de date se face prin intermediul modulului de gestiune a controlului (eng. Control Center Manager), disponibil în meniul Tools. Acesta va grupa obiectele definite (tabele, dimensiuni, cuburi, mapări) în funcţie de locaţia lor fizică. Utilitarul generează obiectele, executând codul pentru cele la care operaţia este posibilă (spre exemplu, mapările).
Obiectele care nu au fost generate anterior (spre exemplu, tabelele) sunt selectate după care – din meniul contextual – se va alege opţiunea Deploy.
Fiecare obiect are asociată o anumită acţiune. Acţiunea asociată unui obiect poate avea valorile: CREATE
, UPGRADE
, DROP
sau REPLACE
. Dacă obiectele nu au mai fost create, implicit va fi selectată opţiunea CREATE
.
Se va selecta acţiunea implicită (butonul Default Action - 1) care pentru obiectele care nu au fost generate va avea valoarea CREATE
.
Prin intermediul butonului de generare (2), va fi creat codul corespunzător pentru toate obiectele ce au asociată o acţiune. Totodată, vor fi validate definiţiile (anterior), întrucât în caz contrar există posibilitatea de a nu se putea genera codul corespunzător.
Popularea propriu-zisă a depozitului de date se face prin intermediul butonului Execute (3), în urma acestuia realizându-se transferul informaţiilor din sursele de date către depozitul de date (dimensiuni şi cuburi). Este important ca ordinea execuţiei mapărilor să fie: mapări corespunzătoare dimensiunilor, mapări corespunzătoare cuburilor. În caz contrar, valorile informaţiilor din câmpurile de legătură ale cuburilor nu vor fi completate (de vreme ce lipsesc din dimensiunile corespunzătoare – ale căror mapări nu au fost executate încă).
Pentru obiectele care nu au fost deja generate (dimensiuni, tabele de fapte - cuburi, mapări) sunt afișate denumirea, starea (din punct de vedere al procesului de proiectare), acțiunea solicitată de utilizator pentru dezvoltarea sa, momentul de timp la care a fost realizat procesul de dezvoltare, starea procesului de dezvoltare (succes sau eroare), locația la care se găsește și modulul din care face parte.
Pentru astfel de obiecte nu mai este necesar să se realizeze procesele de validare, generare cod și execuție, de vreme ce aceste operații au stat la baza creării propriu-zise a obiectelor în depozitul de date, iar definiția lor a rămas nemodificată.
Vizualizarea datelor pentru un cub se face selectând din meniul contextual opţiunea Data.
În fereastra Data, vizualizarea propriu-zisă a informațiilor cu care a fost populat depozitul de date este realizată prin accesarea butonului Execute.
Raportările specifice OLAP se fac prin opţiunea Query Builder, specificându-se ca parametri măsurile şi criteriile de analiză (dimensiunile), stabilindu-se şi formatul dorit (perspectivele de analiză) în funcţie de cerinţele specificate de utilizator.
De asemenea, diferite tipuri de calcule pot fi realizate prin intermediul opțiunii Calculation Builder.
1. [0 puncte] Accesați baza de date Oracle 11gR2 pentru a implementa schema conceptuală BookStore
, populând-o corespunzător.
a) Să se pornească consola Enterprise Manager a bazei de date Oracle 11gR2, care oferă posibilitatea administrării acesteia:
[aipi2014@oraclelinux ~]$ . oraenv ORACLE_SID = [aipi2014] ? orcl The Oracle base for ORACLE_HOME=/home/aipi2014/app/aipi2014/product/11.2.0/dbhome_1 is /home/aipi2014/app/aipi2014 [aipi2014@oraclelinux ~]$ emctl start dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved. https://localhost:1158/em/console/aboutApplication Starting Oracle Enterprise Manager 11g Database Control ................. started. ------------------------------------------------------------------ Logs are generated in directory /home/aipi2014/app/aipi2014/product/11.2.0/dbhome_1/localhost_orcl/sysman/log
. oraenv
are rolul de a stabili valoarea variabilei de mediu ORACLE_HOME
, raportată la denumirea instanței bazei de date Oracle 11gR2 instalată (în cazul de față, orcl
).
Comanda emctl start dbconsole
pornește consola Enterprise Manager a bazei de date Oracle 11gR2 prin intermediul căreia pot fi realizate diferite operații de administrare.
b) Să se acceseze consola Enterprise Manager a bazei de date Oracle 11gR2, de la adresa https://localhost:1158/em.
SYSTEM
StudentAipi2014
c) În tab-ul Server, categoria Security, se se acceseze opțiunea Users.
Să se creeze un nou utilizator, având denumirea BookStore
și parola BookStore
, acordându-i-se drepturile de acces necesare pentru gestiunea mai multor tabele.
GLOBAL_AQ_USER_ROLE
(drepturi de acces globale nu pot avea decât utilizatorii SYS
, SYSTEM
, SYSDBA
). Se selectează butonul OK.
The object has been created successfully
.
d) Să se deschidă aplicația Oracle SQL Developer din Applications → Programming → SQL Developer.
Să se creeze o nouă conexiune la baza de date (Connections → New Connection…).
În cadrul ferestrei New / Select Database Connection, să se completeze următorii parametrii:
BookStore
BookStore
BookStore
localhost
1521
orcl
Status: Success
.
Se accesează butonul Connect.
Se deschide scriptul Laborator11.sql
folosind opțiunea Open (Ctrl+O) și se rulează folosind opțiunea Run Script (F5).
La solicitarea indicării unei conexiuni la baza de date, se va indica BookStore
.
Rezultatele execuției scriptului Laborator11.sql
(inclusiv timpul de execuție) vor fi disponibile în cadrul ferestrei Script Output.
2. [0 puncte] Să se deblocheze conturile corespunzătoare utilizatorilor OWBSYS
și ΟWBSYS_AUDIT
.
În lista tuturor utilizatorilor bazei de date Oracle 11gR2 (accesibili din consola Enterprise Manager, tab-ul Server, categoria Security, se se acceseze opțiunea Users), se selectează utilizatorii OWBSYS
și ΟWBSYS_AUDIT
și apoi butonul Edit.
Se modifică parola (proprietățile Enter Password / Confirm Password), care va avea valoarea BookStore
. La proprietatea Status se selectează Unlocked
. Se accesează butonul Apply.
În situația în care modificările au fost realizate cu succes, se vor afișa mesajele:
User OWBSYS has been modified successfully
;User OWBSYS_AUDIT has been modified successfully
.
3. [10 puncte] Folosind utilitarul Oracle Warehouse Builder Repository Assistant, să se creeze un spațiu de lucru pentru utilizatorul BookStore
.
[aipi2014@oraclelinux ~]$ cd /home/aipi2014/app/aipi2014/product/11.2.0/dbhome_1/owb/bin/unix [aipi2014@oraclelinux unix]$ ./reposinst.sh
4. [0 puncte] Folosind utilitarul Oracle Warehouse Builder Design Center, să se creeze un proiect denumit BookStore
.
[aipi2014@oraclelinux ~]$ cd /home/aipi2014/app/aipi2014/product/11.2.0/dbhome_1/owb/bin [aipi2014@oraclelinux bin]$ ./owb
5. [20 puncte] a) Să se definească un modul sursă a cărui locaţie corespunde schemei utilizatorului BookStore
. În modulul sursă se vor importa tabelele din schema BookStore
ce vor fi utilizate pentru extragerea informaţiilor în depozitul de date: AUTHOR
, BOOK
, GENRE
, INVOICE
, PROVIDER
, PUBLISHING_HOUSE
, SERIES
, SUPPLY_ORDER
, USERS
, WRITER
.
b) Să se definească un modul destinație care poate folosi aceeaşi schemă, schema corespunzătoare utilizatorului OWBSYS
sau schema unui alt utilizator.
6. [20 puncte] Să se creeze în modulul destinaţie dimensiunile D_PUBLISHING_HOUSE
, D_SERIES
, D_GENRE
, D_BOOK
, D_WRITER
, D_AUTHOR
, D_PROVIDER
, D_USERS
, având ierarhiile definite prin nivelurile indicate.
7. [20 puncte] Să se creeze în modulul destinaţie tabelele de fapte (cuburile) D_SUPPLY_ORDER
și D_INVOICE
.
8. [20 puncte] Să se stabilească mapările între tabelele din sursele de date (din cadrul modulului sursă) şi obiectele depozitului de date (dimensiuni şi tabele de fapte - cuburi) definite pentru modulul destinaţie. Astfel, trebuie definite mapările M_PUBLISHING_HOUSE
, M_SERIES
, M_GENRE
, M_BOOK
, M_WRITER
, M_AUTHOR
, M_PROVIDER
, M_USERS
, M_SUPPLY_ORDER
și M_INVOICE
.
9. [10 puncte] Folosind utilitarul Control Center Manager, să se genereze codul pentru obiectele depozitului de date care nu au fost încă dezvoltate (tabelele din modulul sursă).
10. [20 puncte] Să se vizualizeze informaţiile stocate în cuburi şi să se creeze rapoarte folosind utilitarul Query Builder.
Manole VELICANU, Ion LUNGU, Iuliana BOTHA, Adela BÂRA, Anda VELICANU, Emanuil REDNIC, Sisteme de Baze de Date Evoluate, Colecţia Informatică, Editura Academiei de Studii Economice, Bucureşti, 2009
Ion LUNGU (coordonator), Adela BÂRA, Constanţa BODEA, Iuliana BOTHA, Vlad DIACONIŢA, Alexandra FLOREA, Anda VELICANU, Tratat de Baze de Date (volumul I – Baze de Date: Organizare, Proiectare, Implementare), Editura Academiei de Studii Economice, Bucureşti, 2011
Silviu TEODORU, Oracle Business Intelligence, December 2011
Ion LUNGU, Adela BÂRA, Sisteme Informatice Executive, Colecţia Informatica, Editura Academiei de Studii Economice, Bucureşti, 2007
Introduction to Oracle Warehouse Builder
Designing Source and Target Schemas
6. Dimensiunea D_SERIES
Dimensiunea D_GENRE
Dimensiunea D_BOOK
Dimensiunea D_WRITER
Dimensiunea D_AUTHOR
Dimensiunea D_PROVIDER
Dimensiunea D_USERS
7. Tabela de fapte (cubul) C_INVOICE
8. Maparea M_SERIES
Maparea M_GENRE
Maparea M_BOOK
Maparea M_WRITER
Maparea M_AUTHOR
Maparea M_PROVIDER
Maparea M_USERS
Maparea M_SUPPLY_ORDER
Maparea M_INVOICE