Cuburi de date. Ierarhiile în dimensiuni. Ierarhii și niveluri

04/07/2011 Derek Comingor

Dacă ați avut de-a face cu orice domeniu legat de tehnologie, probabil ați auzit termenul „cub”; cu toate acestea, majoritatea DBA-urilor obișnuite și dezvoltatorilor de baze de date nu au lucrat cu aceste obiecte. Cuburile oferă o arhitectură de date puternică pentru agregarea rapidă a informațiilor multidimensionale. Dacă organizația dvs. trebuie să analizeze cantități mari de date, atunci un cub este soluția ideală.

Ce este un cub?

Bazele de date relaționale au fost proiectate pentru a gestiona mii de tranzacții simultane, menținând în același timp performanța și integritatea datelor. Prin proiectare, bazele de date relaționale nu sunt eficiente la agregarea și căutarea unor cantități mari de date. Pentru a agrega și a returna cantități mari de date, o bază de date relațională trebuie să primească o interogare bazată pe set, pentru care informațiile vor fi colectate și agregate din mers. Aceste interogări relaționale sunt foarte scumpe, deoarece se bazează pe mai multe îmbinări și funcții agregate; interogările relaționale agregate sunt deosebit de ineficiente atunci când se lucrează cu seturi mari de date.

Cuburile sunt entități multidimensionale concepute pentru a soluționa acest defect în bazele de date relaționale. Prin utilizarea unui cub, puteți oferi utilizatorilor o structură de date care răspunde rapid la interogări cu cantități mari de agregare. Cuburile realizează această magie de agregare prin pre-agregarea datelor (dimensiuni) pe mai multe dimensiuni. Pre-agregarea unui cub se face de obicei în timp ce este procesat. Când procesați un cub, generați agregate de date precomputate care sunt stocate în formă binară pe disc.

Cubul este construcția centrală de date în sistemul de analiză de date online (SSAS) OLAP SQL Server Analytical Services. Cuburile sunt de obicei construite dintr-o bază de date relațională de bază numită model dimensional, dar sunt entități tehnice separate. În mod logic, un cub este un depozit de date care este alcătuit din dimensiuni și măsuri. Dimensiunile conțin atribute descriptive și ierarhii, în timp ce dimensiunile sunt fapte pe care le descrieți în dimensiuni. Dimensiunile sunt combinate în combinații logice numite grupuri de dimensiuni. Legați dimensiunile de grupurile de dimensiuni pe baza atributului - granularitatea.

V Sistemul de fișiere un cub este implementat ca o secvență de binare conexe. Arhitectura binară a cubului facilitează extragerea rapidă a unor cantități mari de date multidimensionale.

Am menționat că cuburile sunt construite dintr-o bază de date relațională de bază numită modelul dimensiunii. Modelul de dimensiuni conține tabele relaționale (fapt și dimensiune), care îl leagă de entitățile cubului. Tabelele de date conțin dimensiuni precum cantitatea unui produs vândut. Tabelele de dimensiuni stochează atribute descriptive, cum ar fi numele produselor, datele și numele angajaților. De obicei, tabelele de date și tabelele de dimensiuni sunt legate prin constrângeri de chei străine primare, în timp ce cheile străine se află în tabelul de date real (această relație relațională este legată de trăsătura de granularitate a cubului discutată mai sus). Când tabelele de dimensiuni sunt legate direct de un tabel de fapte, se formează o schemă stea. Atunci când tabelele de dimensiuni nu sunt legate direct de tabelul de date, se obține o schemă de fulgi de zăpadă.

Rețineți că modelele de dimensiuni sunt clasificate în funcție de aplicația lor. Un martie de date este un model dimensional care abordează un singur proces de afaceri, cum ar fi vânzările sau gestionarea stocurilor. Un depozit de date este un model dimensional conceput pentru a cuprinde procese de afaceri complexe, astfel încât să faciliteze analiza între afaceri a proceselor de afaceri.

Cerințe software

Acum, că aveți o înțelegere de bază despre ce sunt cuburile și de ce sunt importante, voi activa instrumentația și vă voi duce la o soluție: construirea primului cub folosind SSAS. Există câteva componente software de bază de care veți avea nevoie, deci asigurați-vă că sistemul dvs. îndeplinește cerințele înainte de a începe construirea primului cub.

Exemplul meu Cub de vânzări pe Internet va fi construit din baza de date de test AdventureWorksDW 2005. Voi construi un cub de testare dintr-un subset de tabele găsite în baza de date de testare, care va fi util pentru analiza datelor de vânzări pe Internet. Figura 1 prezintă schema de bază a tabelelor bazei de date. Deoarece folosesc versiunea 2005, puteți urma instrucțiunile mele folosind fie SQL Server 2005, fie SQL Server 2008.

Figura 1. Un subset al martorului de date Adventure Works pe Internet

Exemplul de bază de date Adventure WorksDW 2005 poate fi găsit la CodePlex: msftdbprodsamples.codeplex.com. Găsiți linkul „Eșantionul de baze de date de produse SQL Server 2005 sunt încă disponibile” (http://codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004). Baza de date eșantion este conținută în fișierul AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId=11755).

După cum sa menționat, trebuie să aveți acces la o instanță a SQL Server 2008 sau 2005, inclusiv SSAS și componentele Business Intelligence Development Studio (BIDS). Voi folosi SQL Server 2008, astfel încât să puteți vedea câteva diferențe subtile dacă utilizați SQL Server 2005.

Crearea unui proiect SSAS

Primul lucru pe care ar trebui să-l faci este să creezi un proiect SSAS folosind BIDS. Găsiți BIDS în meniul Start, apoi în meniul Microsoft SQL Server 2008/2005 sub-elementul SQL Server Business Intelligence Development Studio. Apăsând acest buton se va lansa BIDS cu ecranul inițial implicit. Creați un nou proiect SSAS alegând Fișier, Nou, Proiect. Veți vedea caseta de dialog New Project, pe care o prezintă Figura 1. Selectați folderul Project Analysis Services și dați descrierea proiectului „SQLMAG_MyFirstCube”. Faceți clic pe OK.

Când proiectul este creat, faceți clic dreapta pe acesta în Solution Explorer și selectați Proprietăți din meniul contextual. Acum selectați secțiunea Implementare din partea stângă a casetei de dialog SQLMAG_MyFirstCube: Pagini de proprietate și verificați setările Serverului țintă și a bazei de date, așa cum arată Figura 2. în care urmează să implementați. Faceți clic pe OK când sunteți mulțumit de valorile parametrilor de implementare configurate pentru acest proiect SSAS.

Definirea unei surse de date

Primul obiect de creat este sursa de date. Obiectul sursă de date oferă schema și datele care sunt utilizate pentru a construi obiectele asociate cu și la baza cubului. Pentru a crea un obiect sursă de date în BIDS, utilizați expertul sursă Date de date Expertul sursă.

Porniți Expertul sursă de date făcând clic dreapta pe folderul Sursă de date din panoul Solution Explorer, alegând Sursă de date nouă. Veți descoperi că crearea obiectelor SSAS în BIDS are un caracter de dezvoltare. Vrăjitorul vă ghidează mai întâi prin procesul de creare a obiectelor și setările generale. Și apoi deschideți obiectul SSAS rezultat în proiectant și îl reglați fin dacă este necesar. Odată ce ați trecut ecranul de solicitare, definiți o nouă conexiune de date făcând clic pe butonul Nou. Selectați și creați o nouă conexiune Native OLEDB \ SQL Server Native Client 10 care indică serverul SQL dorit care deține instanța de bază de date dorită. Puteți utiliza fie autentificarea Windows, fie SQL Server, în funcție de setările dvs. mediul SQL Server. Faceți clic pe butonul Testare conexiune pentru a vă asigura că ați identificat corect conexiunea la baza de date și apoi pe butonul OK.

Aceasta este urmată de informații despre uzurparea identității, care, la fel ca legarea datelor, depinde de modul în care este structurat mediul SQL Server. Uzurparea identității este contextul de securitate pe care se bazează SSAS atunci când își procesează obiectele. Dacă gestionați implementarea pe un server primar, unic (sau laptop), așa cum presupun că sunt majoritatea cititorilor, puteți alege pur și simplu să utilizați contul Utilizați serviciul. Faceți clic pe Următorul pentru a finaliza Expertul sursă de date și a specifica AWDW2005 ca nume sursă de date. Este minunat că puteți utiliza această metodă în scopuri de testare, dar într-un mediu real de producție nu este cea mai bună practică să utilizați un cont de serviciu. Mai bine să specificați conturi de domeniu pentru a identifica conexiunile SSAS la sursa de date.

Vizualizarea sursei de date

Pentru sursa de date pe care ați definit-o, următorul pas în procesul de construire a cubului SSAS este crearea unei vizualizări a sursei de date (DSV). DSV oferă posibilitatea de a separa schema la care se așteaptă cubul de cea a bazei de date de bază. Ca rezultat, DSV-urile pot fi utilizate pentru a extinde schema relațională de bază atunci când se construiește un cub. Unele dintre capabilitățile DSV cheie pentru extinderea schemelor sursei de date includ interogări denumite, relații logice între tabele și coloane calculate numite.

Înainte, faceți clic dreapta pe folderul DSV și selectați Vizualizare sursă de date nouă pentru a lansa Expertul vizualizare DSV nou. În caseta de dialog, la pasul Selectați o sursă de date, selectați conexiunea la baza de date relațională și faceți clic pe Următorul. Selectați tabelele FactInternetSales, DimProduct, DimTime, DimCustomer și faceți clic pe butonul săgeată dreapta pentru a muta acele tabele în coloana Inclus. În cele din urmă, faceți clic pe Următorul și completați expertul acceptând numele implicit și făcând clic pe Finalizare.

În acest moment, ar trebui să aveți o vizualizare DSV situată sub folderul Vizualizări sursă de date din Solution Explorer. Faceți dublu clic pe noul DSV pentru a lansa designerul DSV. Ar trebui să vedeți toate cele patru tabele pentru acest DSV, așa cum se arată în Figura 2.

Crearea dimensiunilor bazei de date

După cum am explicat mai sus, dimensiunile oferă caracteristici descriptive ale dimensiunilor și ierarhiilor care sunt utilizate pentru a permite agregarea peste nivelul de detaliu. Trebuie să înțelegeți diferența dintre o dimensiune a bazei de date și o dimensiune a cubului: dimensiunile din baza de date oferă obiecte de dimensiune de bază pentru dimensiunile multiple ale cubului pe care să îl construiți.

Dimensiunile bazei de date și ale cubului oferă o soluție elegantă unui concept cunoscut sub numele de dimensiuni de rol. Dimensiunile rolului sunt utile atunci când trebuie să refolosiți o singură dimensiune într-un cub de mai multe ori. Data este un exemplu perfect în această instanță cub: veți construi o singură dimensiune de dată și o veți referi o dată pentru fiecare dată pentru care doriți să analizați vânzările pe Internet. Data calendaristică va fi prima dimensiune pe care o creați. Faceți clic dreapta pe folderul Dimensiuni din Solution Explorer și selectați Dimensiune nouă pentru a lansa Expertul dimensiune. Selectați Utilizați un tabel existent și faceți clic pe Următorul în pasul Selectare metodă de creație. La pasul Specificați informațiile sursei, specificați tabelul DimTime din lista derulantă Tabel principal și faceți clic pe Următorul. Acum, la pasul selectării atributelor Select Dimension, trebuie să selectați atributele dimensiunii de timp. Selectați fiecare atribut așa cum se arată în Figura 3.

Faceți clic pe Următorul. În pasul final, introduceți Dim Dim în câmpul Name și faceți clic pe Finalizare pentru a finaliza expertul de dimensiuni. Acum ar trebui să vedeți noua Dim Dim în folderul Dimensions din Solution Explorer.

Apoi utilizați Expertul pentru dimensiuni pentru a crea dimensiunile produsului și ale clientului. Urmați aceiași pași pentru a crea dimensiunea de bază ca înainte. Pe măsură ce lucrați cu Expertul pentru dimensiuni, asigurați-vă că selectați toate atributele potențiale în pasul Selectați atributele dimensiunii. Valorile implicite pentru ceilalți parametri de reglare sunt bine pentru instanța de testare a cubului.

Creați un cub de vânzări pe internet

Acum că ați pregătit dimensiunile bazei de date, puteți începe să construiți cubul. În Solution Explorer, faceți clic dreapta pe folderul Cubes și selectați New Cube pentru a lansa Expertul Cube. În fereastra Selectare metodă de creație, selectați un caz de utilizare tabele existente Folosiți tabelele existente. Selectați tabelul FactInternetSales pentru grupul de măsurare în pasul Selectați tabelele grupului de măsurare. Debifați caseta de lângă Cheia promoțională, Cheia valutară, Cheia teritoriului de vânzări și Numărul revizuirii din pasul Selectați măsuri și faceți clic pe Următorul.

În ecranul Selectare dimensiuni existente, asigurați-vă că toate dimensiunile existente ale bazei de date sunt selectate pentru a fi utilizate în continuare ca dimensiuni cub. Deoarece aș dori să păstrez acest cub cât mai simplu posibil, deselectați dimensiunea FactInternetSales în pasul Selectați dimensiuni noi. Lăsând selectată dimensiunea FactInternetSales, veți crea ceea ce se numește dimensiunea de fapt sau dimensiunea degenerată. Dimensiunile de fapt sunt dimensiuni care au fost create folosind tabelul principal de date spre deosebire de tabelul de dimensiuni tradițional.

Faceți clic pe Următorul pentru a trece la pasul Finalizarea expertului și introduceți „Primul meu cub” în câmpul cu numele cubului. Faceți clic pe Finalizare pentru a finaliza expertul cubului.

Implementarea și procesarea unui cub

Acum sunteți gata să implementați și să procesați primul dvs. cub. Faceți clic dreapta pe noua pictogramă cub din Solution Explorer și selectați Procesare. Veți vedea o casetă de mesaje care vă informează că conținutul pare a fi depășit. Faceți clic pe Da pentru a implementa noul cub pe serverul SSAS țintă. Când implementați un cub, trimiteți un fișier XML pentru Analisis (XMLA) către SSAS-ul țintă, care creează cubul pe serverul însuși. Așa cum am menționat, procesarea unui cub populează binarele sale pe disc cu date de la sursa primară, precum și metadate suplimentare pe care le-ați adăugat (dimensiuni, dimensiuni și setări cub).

După finalizarea procesului de implementare, apare o nouă casetă de dialog Process Cube. Faceți clic pe butonul Executare pentru a porni procesul de procesare a cubului, care se deschide odată cu fereastra Progres proces. Când procesarea este finalizată, faceți clic pe Închidere (de două ori pentru a închide ambele casete de dialog) pentru a finaliza implementarea și procesarea cubului.

Acum ați construit, implementat și procesat primul cub. Puteți răsfoi acest nou cub făcând clic dreapta pe acesta în fereastra Solution Explorer și alegând Răsfoire. Trageți dimensiunile în centrul tabelului pivot și atributele dimensiunii pe rânduri și coloane pentru a explora noul cub. Observați cât de repede cubul procesează diverse interogări agregate. Acum puteți aprecia puterea nelimitată și, astfel, valoarea de afaceri a unui cub OLAP.

Derek Comingore ( [e-mail protejat]) este arhitect senior la B. I. Voyage, partener Microsoft în Business Intelligence. Are titlul de SQL Server MVP și mai multe certificări Microsoft

OLAP nu este un produs software unic, nu este un limbaj de programare sau chiar o tehnologie specifică. Dacă încercați să acoperiți OLAP în toate manifestările sale, atunci este un set de concepte, principii și cerințe care stau la baza produselor software care facilitează accesul analiștilor la date. Să aflăm De ce analiștii au nevoie de ceva special facilita acces la date.

Ideea este că analiștii sunt consumatori speciali de informații corporative. Sarcina analistului este de a găsi modele în seturi mari de date... Prin urmare, analistul nu va acorda atenție faptului luat separat că joi, a patra zi, un lot de cerneală neagră a fost vândut contrapartidei Chernov - are nevoie de informații aproximativ sute și mii evenimente similare. Faptele unice din baza de date pot fi de interes, de exemplu, pentru un contabil sau șeful departamentului de vânzări care se ocupă de tranzacție. Un analist cu o singură înregistrare nu este suficient - el, de exemplu, poate avea nevoie de toate tranzacțiile unei sucursale sau birouri reprezentative date pentru o lună sau un an. În același timp, un analist aruncă detalii de care nu are nevoie, cum ar fi TIN-ul cumpărătorului, adresa și numărul său de telefon exacte, indexul contractului și altele asemenea. În același timp, datele de care are nevoie analistul pentru muncă conțin în mod necesar valori numerice - acest lucru se datorează însăși esenței activității sale.

Deci, analistul are nevoie de multe date, aceste date sunt selective și au și un caracter " set de atribute - număr Acesta din urmă înseamnă că analistul lucrează cu tabele de următorul tip:

Aici " Țară", "Produs", "An"sunt atribute sau măsurători, A " Volumul vânzărilor"- prin aceasta valoare numerică sau măsura. Sarcina analistului, repetăm, este de a identifica relații persistente între atribute și parametrii numerici.... Privind tabelul, puteți vedea că acesta poate fi tradus cu ușurință în trei dimensiuni: de-a lungul uneia dintre axe vom amâna țările, de-a lungul celeilalte - bunuri, și în al treilea - anii. Iar valorile din această matrice tridimensională vor fi volumele de vânzări corespunzătoare.

Vizualizare 3D a mesei. Segmentul gri arată că nu există date pentru Argentina în 1988

Tocmai un astfel de tablou tridimensional în termeni OLAP este numit cub. De fapt, din punctul de vedere al matematicii stricte, o astfel de matrice nu va fi întotdeauna un cub: un cub real ar trebui să aibă același număr de elemente în toate dimensiunile, în timp ce cuburile OLAP nu au o astfel de limitare. Cu toate acestea, în ciuda acestor detalii, termenul „cuburi OLAP” a devenit în general acceptat datorită conciziei și imaginilor sale. Un cub OLAP nu trebuie să fie deloc 3D. Poate fi atât bidimensional, cât și multidimensional - în funcție de problema rezolvată. Analiștii mai experimentați pot avea nevoie de aproximativ 20 de măsurători - iar produsele OLAP serioase sunt concepute doar pentru un astfel de număr. Aplicațiile desktop mai simple acceptă aproximativ 6 dimensiuni.

Măsurători Cuburile OLAP sunt compuse din așa-numitele Etichete sau membri. De exemplu, dimensiunea Țară constă din etichetele Argentina, Brazilia, Venezuela și așa mai departe.

Nu trebuie completate toate elementele cubului: dacă nu există informații despre vânzările de produse din cauciuc în Argentina în 1988, pur și simplu valoarea din celula corespunzătoare nu va fi determinată. De asemenea, este complet opțional ca aplicația OLAP să stocheze date într-o structură multidimensională - principalul lucru este că aceste date arată astfel pentru utilizator. Apropo, prin metode speciale de stocare compactă a datelor multidimensionale, „vidul” (elementele goale) în cuburi nu duce la risipa de memorie.

Cu toate acestea, cubul în sine nu este potrivit pentru analiză. Dacă este încă posibil să se reprezinte sau să se descrie în mod adecvat un cub tridimensional, atunci cu un cub cu șase sau nouăsprezece dimensiuni, situația este mult mai gravă. De aceea inainte de folosire dintr-un cub multidimensional, obișnuit tabele bidimensionale... Această operație se numește „tăierea” cubului. Acest termen, din nou, este figurativ. Analistul, ca și cum ar fi, ia și „taie” dimensiunile cubului în funcție de etichetele de interes. În acest fel, analistul ia o felie bidimensională a cubului și lucrează cu acesta. Aproximativ în același mod, lemnarii consideră inele anuale pe o tăietură cu ferăstrăul.

În consecință, de regulă, doar două dimensiuni rămân „netăiate” - în funcție de numărul de dimensiuni din tabel. Uneori, doar o dimensiune rămâne „netăiată” - dacă un cub conține mai multe tipuri de valori numerice, acestea pot fi depuse de-a lungul uneia dintre dimensiunile tabelului.

Dacă aruncați o privire mai atentă la tabelul pe care l-am desenat mai întâi, veți observa că datele din acesta sunt cel mai probabil nu primare, ci obținute ca rezultat însumare pe elemente mai mici. De exemplu, un an este împărțit în sferturi, sferturi în luni, luni în săptămâni, săptămâni în zile. O țară este formată din regiuni, iar regiunile sunt formate din așezări. În cele din urmă, în orașele în sine, se pot distinge districte și puncte de vânzare cu amănuntul specifice. Produsele pot fi grupate în grupuri de produse și așa mai departe. În termeni OLAP, astfel de îmbinări pe mai multe niveluri sunt numite destul de logic ierarhii... Instrumentele OLAP fac posibilă trecerea la nivelul dorit al ierarhiei în orice moment. Mai mult, de regulă, mai multe tipuri de ierarhii sunt acceptate pentru aceleași elemente: de exemplu, zi-săptămână-lună sau zi-deceniu-trimestru. Datele originale sunt preluate de la nivelurile inferioare ale ierarhiilor și apoi sintetizate pentru a obține valori la nivelurile superioare. Pentru a accelera procesul de tranziție, valorile însumate pentru diferite niveluri sunt stocate într-un cub. Astfel, ceea ce arată ca un cub din perspectiva utilizatorului, aproximativ vorbind, constă din mai multe cuburi primitive.

Exemplu de ierarhie

Acesta este unul dintre punctele esențiale care au dus la apariția OLAP - performanță și eficiență. Imaginați-vă ce se întâmplă atunci când un analist are nevoie de informații și nu există OLAP în întreprindere. Analistul independent (ceea ce este puțin probabil) sau cu ajutorul unui programator face interogarea SQL adecvată și primește datele de interes sub forma unui raport sau le exportă într-o foaie de calcul. În acest caz, apar foarte multe probleme. În primul rând, analistul este obligat să facă altceva decât propria sa muncă (programare SQL) sau să aștepte programatorii să-și finalizeze sarcina - toate acestea afectează negativ productivitatea muncii, crește rata de asalt, infarct și accident vascular cerebral etc. pe. În al doilea rând, un singur raport sau tabel, de regulă, nu salvează giganții gândirii și părinții analizei rusești - și întreaga procedură va trebui repetată din nou și din nou. În al treilea rând, așa cum am aflat deja, analiștii nu întreabă despre fleacuri - au nevoie de toate deodată. Aceasta înseamnă (deși tehnica avansează cu pași mari) că serverul SGBD relațional corporativ, la care apelează analistul, poate gândi profund și mult timp, blocând restul tranzacțiilor.

Conceptul OLAP s-a născut tocmai pentru a rezolva astfel de probleme. Cuburile OLAP sunt în esență meta-rapoarte. Tăind meta-rapoartele (cuburi, adică) după dimensiuni, analistul primește, de fapt, rapoartele bidimensionale „obișnuite” care îl interesează (acestea nu sunt neapărat rapoarte în sensul obișnuit al termenului - vorbim despre date structuri cu aceleași funcții). Avantajele cuburilor sunt evidente - datele trebuie solicitate de la SGBD relațional o singură dată - la construirea cubului. Deoarece analiștii, de regulă, nu lucrează cu informații care sunt completate și modificate din mers, cubul generat este relevant pentru o perioadă destul de lungă de timp. Datorită acestui fapt, nu numai întreruperile în funcționarea serverului DBMS relațional sunt excluse (nu există interogări cu mii și milioane de linii de răspuns), ci și viteza de acces la date pentru analistul însuși crește brusc. În plus, după cum sa menționat, performanța este, de asemenea, îmbunătățită prin numărarea subtotalelor ierarhiilor și a altor valori agregate în momentul construirii cubului. Adică, dacă inițial datele noastre conțineau informații despre veniturile zilnice pentru un anumit produs dintr-un anumit magazin, atunci la formarea cubului, aplicația OLAP calculează sumele totale pentru diferite niveluri de ierarhii (săptămâni și luni, orașe și țări).

Desigur, trebuie să plătiți pentru a crește productivitatea în acest fel. Uneori se spune că structura datelor pur și simplu „explodează” - un cub OLAP poate ocupa zeci sau chiar sute de ori mai mult spațiu decât datele originale.

Raspunde la intrebari:

Ce cub OLAP?

Ce Etichete măsurare specifică? Dă exemple.

Pot ei măsuri într-un cub OLAP, conține valori nenumerice.

Cuburile de date de procesare analitică online (OLAP) vă permit să extrageți și să analizați eficient date multidimensionale. Spre deosebire de alte tipuri de baze de date, bazele de date OLAP sunt concepute special pentru prelucrarea analitică și extragerea rapidă a tuturor tipurilor de seturi de date din acestea. De fapt, există mai multe diferențe cheie între bazele de date relaționale standard, cum ar fi Access sau SQL Server și bazele de date OLAP.

Orez. 1. Pentru a conecta un cub OLAP la un registru de lucru Excel, utilizați comanda De la servicii de analiză

Descărcați o notă în format sau

În bazele de date relaționale, informațiile sunt prezentate sub formă de înregistrări care sunt adăugate, eliminate și actualizate secvențial. Bazele de date OLAP stochează doar un instantaneu al datelor. Într-o bază de date OLAP, informațiile sunt arhivate ca un singur bloc de date și sunt destinate numai ieșirilor la cerere. Deși este posibil să adăugați la o bază de date OLAP informație nouă, datele existente sunt rareori editate și cu atât mai mult șterse.

Bazele de date relaționale și bazele de date OLAP diferă structural. Bazele de date relaționale constau de obicei dintr-un set de tabele care sunt legate între ele. În unele cazuri, o bază de date relațională conține atât de multe tabele încât este foarte dificil să se determine modul în care acestea sunt legate. În bazele de date OLAP, relația dintre blocurile de date individuale este predefinită și stocată într-o structură cunoscută sub numele de cuburi OLAP. Cuburile de date stochează informații complete despre structura ierarhică și relațiile bazei de date, ceea ce simplifică foarte mult navigarea prin ea. În plus, este mult mai ușor să creați rapoarte dacă știți din timp unde se află datele extrase și ce alte date sunt asociate cu acestea.

Principala diferență între bazele de date relaționale și bazele de date OLAP este modul în care sunt stocate informațiile. Datele dintr-un cub OLAP sunt rareori reprezentate în vedere generala... Cuburile de date OLAP conțin de obicei informații prezentate într-un format prestabilit. Astfel, operațiile de grupare, filtrare, sortare și combinare a datelor în cuburi sunt efectuate înainte de a le completa cu informații. Acest lucru facilitează extragerea și afișarea datelor solicitate cât mai ușor posibil. Spre deosebire de bazele de date relaționale, nu este nevoie să organizați corect informațiile înainte de a le afișa pe ecran.

Bazele de date OLAP sunt de obicei create și întreținute de administratorii IT. Dacă organizația dvs. nu are o structură care este responsabilă pentru gestionarea bazelor de date OLAP, atunci puteți contacta administratorul bazei de date relaționale cu o solicitare de a implementa cel puțin unele soluții OLAP în rețeaua corporativă.

Conectarea la un cub de date OLAP

Pentru a accesa baza de date OLAP, trebuie mai întâi să stabiliți o conexiune la cubul OLAP. Începeți accesând fila panglică Date... Faceți clic pe buton Din alte surseși selectați comanda din meniul derulant De la servicii de analiză(fig. 1).

Când selectați comanda specificată din Expertul conexiune de date (Fig. 2). Sarcina sa principală este de a vă ajuta să stabiliți o conexiune la server, care va fi utilizată de Excel pentru gestionarea datelor.

1. Mai întâi trebuie să furnizați Excel cu informații de înregistrare. Introduceți numele serverului, numele de conectare și parola de acces la date în câmpurile casetei de dialog, așa cum se arată în Fig. 2. Faceți clic pe buton Mai departe... Dacă vă conectați utilizând un cont Intrări Windows apoi setați comutatorul Utilizați autentificarea Windows.

2. Selectați baza de date cu care veți lucra din lista derulantă (Fig. 3). Exemplul actual utilizează baza de date Tutorial Analysis Services. După selectarea acestei baze de date din lista de mai jos, vi se solicită să importați toate cuburile OLAP disponibile. Selectați cubul de date necesar și faceți clic pe buton Mai departe.

Orez. 3. Selectați baza de date de producție și cubul OLAP pe care intenționați să le utilizați pentru analiza datelor

3. În următoarea casetă de dialog a expertului, prezentată în fig. 4, vi se cere să introduceți informații descriptive despre conexiunea pe care o creați. Toate câmpurile casetei de dialog prezentate în Fig. 4 sunt opționale. Puteți ignora oricând dialogul curent fără a-l completa, iar acest lucru nu va afecta conexiunea în niciun fel.

Orez. 4. Modificați informațiile descriptive despre conexiune

4. Faceți clic pe buton Gata pentru a finaliza conexiunea. O fereastră de dialog va apărea pe ecran. Importul datelor(fig. 5). Setați comutatorul Raport din tabelul pivotși faceți clic pe butonul OK pentru a începe crearea tabelului pivot.

Structura cubului OLAP

Pe măsură ce creați un tabel pivot pe baza unei baze de date OLAP, veți observa că fereastra panoului de activitate Câmpuri din tabelul pivot va fi diferită de cea a unui tabel pivot obișnuit. Motivul constă în ordonarea PivotTable pentru a afișa cât mai aproape posibil structura cubului OLAP atașat la acesta. Pentru a naviga în cubul OLAP cât mai repede posibil, trebuie să vă familiarizați cu componentele sale și cu modul în care acestea interacționează. În fig. 6 prezintă structura de bază a unui cub tipic OLAP.

După cum puteți vedea, principalele componente ale unui cub OLAP sunt dimensiunile, ierarhiile, nivelurile, membrii și măsurile:

• Dimensiune... Principala caracteristică a elementelor de date analizate. Cele mai frecvente exemple de dimensiuni includ Produse, Client și Angajat. În fig. 6 arată structura dimensiunii Produse.
• Ierarhii... O agregare predefinită a nivelurilor într-o dimensiune specificată. Ierarhia vă permite să creați date de sinteză și să le analizați la diferite niveluri ale structurii, fără a intra în relațiile care există între aceste niveluri. În exemplul prezentat în Fig. 6, dimensiunea Produse are trei niveluri, care sunt agregate într-o singură ierarhie a categoriilor de produse.
• Nivele... Nivelurile sunt categorii care sunt agregate într-o ierarhie comună. Gândiți-vă la niveluri ca la câmpuri de date care pot fi interogate și analizate separat unul de celălalt. În fig. 6, există doar trei niveluri: categorie, subcategorie și nume produs.
• Membri... Un singur element de date dintr-o dimensiune. Membrii sunt de obicei accesați printr-o structură OLAP de dimensiuni, ierarhii și niveluri. În exemplul din Fig. Se definesc 6 membri pentru nivelul Nume produs. Alte niveluri au proprii membri, care nu sunt afișați în structură.
• Măsuri sunt date reale în cuburi OLAP. Măsurile sunt stocate în propriile dimensiuni, care se numesc dimensiuni de măsură. Măsurile pot fi interogate folosind orice combinație de dimensiuni, ierarhii, niveluri și membri. Aceasta se numește măsuri de „tăiere”.

Acum, că sunteți familiarizați cu structura cuburilor OLAP, să aruncăm o privire nouă la lista câmpurilor din tabelul pivot. Organizarea câmpurilor disponibile este clară și simplă. În fig. Figura 7 arată cum sunt reprezentate elementele din tabelul pivot OLAP în Lista de câmpuri.

Într-o listă de câmpuri pentru tabelul pivot OLAP, măsurile sunt listate mai întâi și sunt indicate printr-o pictogramă de însumare (sigma). Acestea sunt singurele elemente de date care pot fi în zona VALORI. După ele în listă, sunt indicate dimensiunile, indicate de pictograma cu imaginea tabelului. În exemplul nostru, se utilizează dimensiunea Client. Un număr de ierarhii sunt imbricate în această dimensiune. După extinderea ierarhiei, vă puteți familiariza cu nivelurile individuale de date. Pentru a vizualiza structura de date a unui cub OLAP, pur și simplu navigați prin lista câmpurilor din tabelul pivot.

Limitări privind tabelele pivot OLAP

Când lucrați cu tabelele pivot OLAP, nu uitați că interacționați cu sursa de date a tabelului pivot într-un mediu OLAP Analysis Services. Aceasta înseamnă că fiecare aspect comportamental al cubului de date, de la dimensiuni până la măsurile incluse în cub, este, de asemenea, controlat de serviciile analitice OLAP. La rândul său, acest lucru duce la restricții privind operațiunile care pot fi efectuate pe tabelele pivot OLAP:

• nu puteți pune alte câmpuri decât măsuri în zona VALORI a tabelului pivot;
• nu este posibil să se schimbe funcția utilizată pentru rezumare;
• nu puteți crea un câmp calculat sau un membru calculat;
• orice modificare a numelor câmpurilor este eliminată de îndată ce câmpul este eliminat din tabelul pivot;
• nu este permisă modificarea parametrilor marginii paginii;
• comanda indisponibilă Spectacolpagini;
• parametru dezactivat Spectacolsemnăturielementeîn absența câmpurilor din gama de valori;
• parametru dezactivat Subtotaluri de elementele de pagină selectate de filtru;
• parametru indisponibil fundalAnchetă;
• după dublu clic în câmpul VALORI, sunt returnate doar primele 1000 de înregistrări din memoria cache a tabelului pivot;
• casetă de selectare indisponibilă Optimizațimemorie.

Crearea de cuburi de date offline

Într-un tabel pivot standard, datele sursă sunt stocate pe hard diskul local. Astfel, le puteți gestiona oricând, precum și schimba structura, chiar și fără a avea acces la rețea. Dar acest lucru nu se aplică în niciun fel tabelelor pivot OLAP. În tabelele pivot OLAP, memoria cache nu se află pe hard diskul local. Prin urmare, imediat după deconectare de la rețeaua locală, tabelul dvs. pivot OLAP va deveni inutilizabil. Nu puteți muta niciun câmp dintr-un astfel de tabel.

Dacă totuși trebuie să analizați datele OLAP fără o conexiune la rețea, creați un cub de date autonom. Este un fișier separat care reprezintă memoria cache a tabelului pivot. Acest fișier stochează datele OLAP care sunt vizualizate după deconectarea de la rețeaua locală. Pentru a crea un cub de date offline, creați mai întâi un tabel pivot OLAP. Plasați cursorul în tabelul pivot și faceți clic pe buton Instrumente OLAP filă contextuală Analiză inclusă în setul filelor contextuale Lucrul cu tabelele pivot... Selectați o echipă Modul offline OLAP(fig. 8).

O fereastră de dialog va apărea pe ecran. Configurarea funcționării offline OLAP(fig. 9). Faceți clic pe buton Creați un fișier de date offline... Prima fereastră a Data Cube File Wizard va apărea pe ecran. Faceți clic pe buton Mai departe pentru a continua procedura.

În al doilea pas (Figura 10), specificați dimensiunile și nivelurile care vor fi incluse în cubul de date. În caseta de dialog, trebuie să selectați datele de importat din baza de date OLAP. Este necesar să selectați numai acele dimensiuni care vor fi necesare după deconectarea computerului de la rețeaua locală. Cu cât specificați mai multe dimensiuni, cu atât va fi mai mare cubul de date offline.

Faceți clic pe buton Mai departe pentru a trece la al treilea pas (fig. 11). În această fereastră, selectați membrii sau elementele de date care nu vor fi incluse în cub. Dacă nu este bifată caseta de selectare, elementul specificat nu va fi importat și va ocupa spațiu suplimentar pe hard diskul local.

Specificați locația și numele cubului de date (Figura 12). Fișierele cub de date au o extensie .cub.

După ceva timp, Excel va salva cubul de date offline în folderul specificat. Pentru a-l testa, faceți dublu clic pe fișier, care va genera automat un registru de lucru Excel care conține un tabel pivot asociat cu cubul de date selectat. Odată creat, puteți distribui cubul de date offline tuturor utilizatorilor interesați care lucrează în modul LAN offline.

Odată conectat la rețeaua dvs. locală, puteți deschide fișierul cub de date offline și îl puteți actualiza, precum și tabelul de date corespunzător. Rețineți că, deși cubul de date offline este utilizat atunci când nu există acces la rețea, acesta trebuie actualizat după restaurarea conexiunii la rețea. Încercarea de a actualiza un cub de date offline după deconectarea de la rețea va duce la o eroare.

Aplicarea funcțiilor cubului de date la tabelele pivot

Funcțiile cubului de date care sunt utilizate în bazele de date OLAP pot fi, de asemenea, lansate dintr-un tabel pivot. În versiunile vechi de Excel, ai acces la funcționalitatea cuburilor de date numai după ce ai instalat programul de completare Analysis Pack. În Excel 2013, aceste funcții sunt integrate în program și, prin urmare, sunt disponibile pentru utilizare. Pentru a vă familiariza pe deplin cu capacitățile lor, luați în considerare un exemplu specific.

Una dintre cele mai simple modalități de a afla despre funcțiile cubului de date este de a converti un tabel pivot OLAP în formule de cub de date. Această procedură este foarte simplă și vă permite să obțineți rapid formule de cuburi de date fără a fi nevoie să le creați de la zero. Principiul cheie este de a înlocui toate celulele din tabelul pivot cu formule care sunt asociate cu o bază de date OLAP. În fig. 13 prezintă un tabel pivot asociat cu o bază de date OLAP.

Plasați cursorul oriunde în tabelul pivot, faceți clic pe buton Instrumente OLAP fila contextuală a panglicii Analizăși selectați comanda Convertiți în formule(fig. 14).

Dacă tabelul pivot conține un câmp de filtrare a raportului, atunci caseta de dialog prezentată în Fig. 15. În această fereastră, specificați dacă doriți ca listele derulante ale filtrelor de date să fie convertite în formule. Dacă da, listele derulante vor fi eliminate și în schimb vor fi afișate formulele statice. Dacă intenționați să utilizați liste derulante în viitor pentru a modifica conținutul tabelului pivot, apoi debifați singura casetă de selectare din caseta de dialog. Dacă lucrați la un tabel pivot în modul quirks, filtrele de date se vor converti automat în formule, fără avertisment prealabil.

După câteva secunde, în loc de tabelul pivot, sunt afișate formule care sunt executate în cuburile de date și oferă ieșire în fereastra Excel informatie necesara... Rețineți că acest lucru elimină stilurile aplicate anterior (Figura 16).

Orez. 16. Uitați-vă la bara de formule: celulele conțin formule de cuburi de date

Având în vedere că valorile pe care le vizualizați nu mai fac parte din obiectul PivotTable, puteți adăuga coloane, rânduri și membri calculați, le puteți combina cu alte surse externe și puteți modifica raportul într-o varietate de moduri, inclusiv drag and drop formule.

Adăugarea de calcule la tabelele pivot OLAP

În versiunile anterioare de Excel, calculele personalizate nu erau permise în tabelele pivot OLAP. Acest lucru înseamnă că nu a fost posibil să adăugați un nivel suplimentar de analiză la tabelele pivot OLAP, la fel cum faceți în tabelele pivot obișnuite care permit adăugarea câmpurilor și a membrilor calculați (consultați detalii; asigurați-vă că sunteți familiarizați cu acest material înainte de a continua citirea .).

Excel 2013 introduce noi instrumente OLAP - măsuri calculate și membri calculați în MDX. Acum nu vă limitați la utilizarea măsurilor și a membrilor în cubul OLAP furnizat de administratorul bazei de date. Veți obține capacități de analiză suplimentare prin crearea de calcule personalizate.

Introducere în MDX. Când utilizați un tabel pivot cu un cub OLAP, trimiteți interogări MDX (expresii multidimensionale) către baza de date. MDX este un limbaj de interogare folosit pentru a extrage date din surse multidimensionale, cum ar fi cuburile OLAP. Când un tabel pivot OLAP este modificat sau actualizat, interogările MDX corespunzătoare sunt transmise la baza de date OLAP. Rezultatele interogării sunt returnate înapoi la Excel și afișate în zona PivotTable. Astfel, este posibil să lucrați cu date OLAP fără o copie locală a memoriei cache a tabelului pivot.

Măsurile calculate și membrii MDX sunt creați utilizând sintaxa MDX. Utilizând această sintaxă, tabelul pivot permite calculului să interacționeze cu backend-ul bazei de date OLAP. Exemplele din această carte se bazează pe construcții MDX de bază care demonstrează noile caracteristici în Excel 2013. Dacă trebuie să creați măsuri complexe calculate și membri MDX, va trebui să vă alocați timp pentru a aprofunda capacitățile MDX.

Creați măsuri calculate. O măsură calculată este o versiune OLAP a unui câmp calculat. Ideea este de a crea un nou câmp de date pe baza unor operații matematice efectuate pe câmpurile OLAP existente. În exemplul prezentat în Fig. 17, se utilizează un tabel pivot OLAP, care include lista și cantitatea de produse, precum și veniturile din vânzarea fiecăruia dintre ele. Trebuie să adăugăm o nouă măsură care va calcula prețul mediu pe unitate.

Analiză Lucrul cu tabelele pivot... În meniul derulant Instrumente OLAP selectați elementul (fig. 18).

Orez. 18. Selectați un element de meniu Măsură calculată MDX

O fereastră de dialog va apărea pe ecran. Creați o măsură calculată(fig. 19).

Urmați acești pași:

2. Selectați un grup de măsuri care va conține noua măsură calculată. Dacă nu, Excel va plasa automat noua măsură în primul grup de măsură disponibil.

3. În teren Expresie multidimensională(MDX) introduceți codul pentru noua măsură. Pentru a accelera procesul de tastare, utilizați lista din stânga pentru a selecta măsurile existente de utilizat în calculele dvs. Faceți dublu clic pe măsura dorită pentru ao adăuga la câmpul MDX. Următorul MDX este utilizat pentru a calcula prețul mediu de vânzare al unei unități:

4. Faceți clic pe OK.

Fii atent la buton Verificați MDX, care se află în partea din dreapta jos a ferestrei. Faceți clic pe acest buton pentru a valida sintaxa MDX. Dacă sintaxa conține erori, se afișează un mesaj.

După ce ați terminat de creat noua măsură calculată, accesați lista Câmpuri din tabelul pivotși selectați-l (fig. 20).

O măsură calculată este cuprinsă numai în registrul de lucru curent. Cu alte cuvinte, măsurile calculate nu sunt create direct în cubul OLAP al serverului. Aceasta înseamnă că nimeni nu poate accesa măsura calculată decât dacă deschideți acces generalîn registrul de lucru sau nu îl publicați pe Internet.

Creați membri calculați în MDX. Un membru calculat MDX este o versiune OLAP a unui membru calculat obișnuit. Ideea este de a crea un nou element de date pe baza unor operații matematice efectuate pe elemente OLAP existente. În exemplul prezentat în Fig. 22, se utilizează un tabel pivot OLAP care include date privind vânzările pentru 2005-2008 (cu o defalcare trimestrială). Să presupunem că doriți să agregați datele pentru primul și al doilea trimestru prin crearea unui nou membru, prima jumătate a anului. De asemenea, vom combina datele referitoare la trimestrele trei și patru, formând un element nou A doua jumătate a anului (a doua jumătate a anului).

Orez. 22. Vom adăuga noi membri calculați MDX, prima jumătate a anului și a doua jumătate a anului

Plasați cursorul oriunde în Tabelul pivot și selectați fila contextuală Analiză dintr-un set de file contextuale Lucrul cu tabelele pivot... În meniul derulant Instrumente OLAP selectați elementul Membru calculat MDX(fig. 23).

O fereastră de dialog va apărea pe ecran. (fig. 24).

Orez. 24. Fereastra Crearea unui membru calculat

Urmați acești pași:

1. Dați un nume măsurii calculate.

2. Selectați ierarhia părinte pentru care sunt creați noi membri calculați. La un șantier de construcții Element părinte atribuiți valoarea Tot... Această setare permite Excel să acceseze toți membrii ierarhiei părinte atunci când evaluează o expresie.

3. În fereastră Expresie multidimensională introduceți sintaxa MDX. Pentru a economisi puțin timp, utilizați lista din stânga pentru a selecta membrii existenți pe care să îi utilizați în MDX. Faceți dublu clic pe elementul selectat și Excel îl va adăuga în fereastră Expresie multidimensională... În exemplul prezentat în Fig. 24, suma primului și celui de-al doilea trimestru se calculează:

..&& +

.. && +

.. && + …

4. Faceți clic pe OK. Excel afișează membrul calculat MDX pe care tocmai l-ați creat în tabelul pivot. Așa cum se arată în fig. 25, noul element calculat este afișat împreună cu celelalte elemente calculate din tabelul pivot.

În fig. 26 ilustrează un proces similar utilizat pentru a crea membrul calculat în a doua jumătate a anului.

Rețineți că Excel nici măcar nu încearcă să șteargă membrii MDX originali (Figura 27). Tabelul pivot afișează încă înregistrări pentru 2005-2008 cu o defalcare trimestrială. În acest caz, aceasta nu este o mare problemă, dar în majoritatea scenariilor, ar trebui să ascundeți elemente „suplimentare” pentru a evita conflictele.

Orez. 27. Excel afișează membrul calculat MDX generat împreună cu membrii originali. Dar este totuși mai bine să ștergeți elementele originale pentru a evita conflictele.

Nu uitați, membrii calculați se găsesc numai în registrul de lucru curent. Cu alte cuvinte, măsurile calculate nu sunt create direct în cubul OLAP al serverului. Aceasta înseamnă că nimeni nu poate accesa o măsură calculată sau un membru calculat decât dacă partajați registrul de lucru sau îl publicați online.

Rețineți că, dacă schimbați ierarhia părinte sau părintele într-un cub OLAP, membrul calculat MDX încetează să funcționeze. Va trebui să recreați acest element.

Managementul calculului OLAP. Excel oferă o interfață care vă permite să gestionați măsurile calculate și membrii MDX în tabelele pivot OLAP. Plasați cursorul oriunde în Tabelul pivot și selectați fila contextuală Analiză dintr-un set de file contextuale Lucrul cu tabelele pivot... În meniul derulant Instrumente OLAP selectați elementul Managementul calculelor... La fereastră Managementul calculelor sunt disponibile trei butoane (fig. 28):

• Crea. Creați o nouă măsură calculată sau un membru calculat MDX.
• Schimbare. Modificați calculul selectat.
• Șterge.Șterge calculul evidențiat.

Orez. 28. Casetă de dialog Managementul calculelor

Efectuează analize ce se întâmplă pe datele OLAP.În Excel 2013, puteți efectua analize ce se întâmplă dacă datele care se află în tabelele pivot OLAP. Mulțumesc acestui lucru o nouă oportunitate puteți modifica valorile din tabelul pivot și puteți recalcula măsurile și membrii pe baza modificărilor dvs. De asemenea, puteți răspândi modificările înapoi în cubul OLAP. Pentru a profita de posibilitățile de analiză, creați un tabel pivot OLAP și selectați fila contextuală Analiză Lucrul cu tabelele pivot... În meniul derulant Instrumente OLAP alege echipa Dacă analiză –> Activați analiza ce se întâmplă(fig. 29).

De acum înainte, puteți modifica valorile tabelului pivot. Pentru a modifica valoarea selectată în tabelul pivot, faceți clic dreapta pe ea și selectați (fig. 30). Excel va rula din nou toate calculele din tabelul pivot după cum a fost revizuit, inclusiv măsurile calculate și membrii MDX calculați.

Orez. 30. Selectați un element Luați în considerare modificarea atunci când calculați tabelul pivot pentru a face modificări în tabelul pivot

În mod implicit, modificările făcute într-un tabel pivot în modul de analiză ce se întâmplă dacă sunt locale. Alternativ, dacă doriți să propagați modificările pe serverul OLAP, selectați comanda pentru a publica modificările. Selectați fila contextuală Analiză situat în setul de file contextuale Lucrul cu tabelele pivot... În meniul derulant Instrumente OLAP selectați elemente Dacă analiză – > Postează modificările(fig. 31). Această comandă va activa „writeback” pe serverul OLAP, ceea ce înseamnă că modificările pot fi propagate către cubul OLAP original. (Pentru a propaga modificările către serverul OLAP, trebuie să aveți permisiunile de server corespunzătoare. Contactați administratorul bazei de date care vă poate ajuta să obțineți permisiuni de acces la scriere în baza de date OLAP.)

Notă bazată pe cartea lui Jelen, Alexander. ... Capitolul 9.

În cadrul acestei lucrări, vor fi luate în considerare următoarele aspecte:

• Ce sunt cuburile OLAP?
• Ce sunt măsurile, dimensiunile, ierarhiile?
• Ce tipuri de operații puteți efectua pe cuburile OLAP?

Conceptul de cub OLAP

Principiul principal al OLAP este multidimensionalitatea în prezentarea datelor. În terminologia OLAP, conceptul de cub sau hipercub este utilizat pentru a descrie un spațiu de date discret multidimensional.

cub este o structură de date multidimensională din care un utilizator analist poate interoga informații. Cuburile sunt create din fapte și dimensiuni.

Fapte- acestea sunt date despre obiecte și evenimente din companie care vor fi supuse analizei. Faptele unui singur tip măsoară. O măsură este un tip de valoare într-o celulă cub.

Măsurători sunt elementele de date pe care sunt analizate faptele. O colecție de astfel de elemente formează un atribut de dimensiune (de exemplu, zilele săptămânii pot forma un atribut de dimensiune de timp). În sarcinile de analiză a întreprinderilor comerciale, categorii precum „timp”, „vânzări”, „bunuri”, „clienți”, „angajați”, „locație geografică” sunt adesea folosite ca dimensiuni. Dimensiunile sunt cel mai adesea structuri ierarhice care reprezintă categorii logice prin care utilizatorul poate analiza datele reale. Fiecare ierarhie poate avea unul sau mai multe niveluri. Deci ierarhia dimensiunii „locație geografică” poate include niveluri: „țară - regiune - oraș”. Într-o ierarhie a timpului, se poate distinge, de exemplu, următoarea secvență de niveluri: O dimensiune poate avea mai multe ierarhii (în acest caz, fiecare ierarhie a unei dimensiuni trebuie să aibă același atribut cheie al tabelului de dimensiuni).

Un cub poate conține date reale de la unul sau mai multe tabele de informații și cel mai adesea conține mai multe dimensiuni. Orice cub special are de obicei un subiect direcțional specific de analiză.

Figura 1 prezintă un exemplu de cub conceput pentru a analiza vânzările de produse rafinate de către o anumită companie pe regiuni. Acest cub are trei dimensiuni (timp, produs și regiune) și o singură măsură (vânzări în termeni monetari). Valorile măsurate sunt stocate în celulele (celulele) corespunzătoare ale cubului. Fiecare celulă este identificată în mod unic de un set de membri ai fiecărei dimensiuni, numit tuplu. De exemplu, celula din colțul din stânga jos al cubului (conține valoarea 98399 USD) este specificată de tuplul [iulie 2005, Extremul Orient, Diesel]. Aici, valoarea de 98.399 dolari arată volumul vânzărilor (în termeni monetari) al motorinei din Extremul Orient pentru iulie 2005.

De asemenea, este demn de remarcat faptul că unele celule nu conțin nicio valoare: aceste celule sunt goale deoarece tabelul de fapt nu conține date pentru ele.

Orez. 1. Cub cu informații despre vânzările de produse petroliere în diferite regiuni

Scopul final al creării unor astfel de cuburi este de a minimiza timpul de procesare a interogărilor care extrag informațiile solicitate din datele reale. Pentru a realiza acest lucru, cuburile conțin de obicei totaluri precomputate numite agregări(agregări). Acestea. cubul acoperă mai mult spațiu de date decât spațiul real - există puncte logice, calculate în el. Funcțiile de agregare vă permit să calculați valorile punctelor din spațiul logic din valorile reale. Cele mai simple funcții de agregare sunt SUM, MAX, MIN, COUNT. De exemplu, folosind funcția MAX, pentru cubul prezentat în exemplu, puteți afla când a existat un vârf al vânzărilor de motorină în Extremul Orient etc.

O altă caracteristică specifică a cuburilor multidimensionale este dificultatea în localizarea originii. De exemplu, cum definiți punctul 0 pentru dimensiunea Produs sau Regiuni? Soluția la această problemă este introducerea unui atribut special care unește toți membrii dimensiunii. Acest atribut (generat automat) conține un singur element, Toate. Pentru funcții simple de agregare, cum ar fi suma, Toate sunt echivalente cu suma valorilor tuturor membrilor în spațiul real al unei dimensiuni date.

Un concept important într-un model de date multidimensional este subcubul. Un subcub este o parte din spațiul total al unui cub sub forma unei figuri multidimensionale în interiorul unui cub. Deoarece spațiul multidimensional al unui cub este discret și delimitat, subcubul este, de asemenea, discret și delimitat.

Operații pe cuburi OLAP

Următoarele operații pot fi efectuate pe un cub OLAP:

• felie;
• rotație;
• consolidare;
• detaliind.

Felie(Figura 2) este un caz special al unui subcub. Aceasta este o procedură pentru formarea unui subset al unei matrice de date multidimensionale care corespunde unei singure valori a unuia sau mai multor elemente de dimensiuni care nu sunt incluse în acest subset. De exemplu, pentru a afla cum evoluează vânzările produselor petroliere în timp doar într-o anumită regiune, și anume în Ural, este necesar să se fixeze dimensiunea „Bunuri” pe elementul „Ural” și să se extragă subsetul corespunzător (subcub ) din cub.

Orez. 2. Felie de cub OLAP

Rotație(Figura 3) - operația de modificare a locației măsurătorilor prezentate în raport sau pe pagina afișată. De exemplu, o operație de rotație poate implica rearanjarea rândurilor și coloanelor unui tabel. În plus, rotația cubului de date este de a muta dimensiunile off-table la locația dimensiunilor prezentate pe pagina afișată și invers.

În general, fiecare specialist știe ce este OLAP astăzi. Cel puțin conceptele „OLAP” și „date multidimensionale” sunt strâns legate în mintea noastră. Cu toate acestea, faptul că acest subiect este ridicat din nou, sper, va fi aprobat de majoritatea cititorilor, deoarece pentru ca ideea a ceva să nu devină depășită în timp, trebuie să comunicați periodic cu oameni inteligenți sau citiți articole într-o publicație bună ...

Depozite de date (locul OLAP în structura informaționalăîntreprinderi)

Termenul „OLAP” este indisolubil legat de termenul „depozit de date”.

Iată definiția, formulată de „tatăl fondator” al depozitelor de date, Bill Inmon: „Un depozit de date este o colecție de date specifică domeniului, legată de timp și imuabilă pentru a sprijini procesul de luare a deciziilor de gestionare”.

Datele din stocare provin sisteme operaționale(Sisteme OLTP), care sunt concepute pentru a automatiza procesele de afaceri. În plus, depozitul poate fi completat cu surse externe, cum ar fi rapoarte statistice.

De ce să construim depozite de date - la urma urmei, acestea conțin informații evident redundante care deja „trăiesc” în baze de date sau fișiere ale sistemelor de operare? Răspunsul poate fi scurt: este imposibil sau foarte dificil să analizăm direct datele sistemelor operaționale. Acest lucru se datorează diverselor motive, inclusiv fragmentarea datelor, stocarea acestora în diferite formate DBMS și în diferite „colțuri” ale rețelei corporative. Dar chiar dacă într-o întreprindere toate datele sunt stocate pe un server central de baze de date (ceea ce este extrem de rar), un analist aproape sigur nu va înțelege structurile lor complexe, uneori confuze. Autorul are o experiență destul de tristă de a încerca să „alimenteze” analiștii flămânzi cu date „brute” din sistemele operaționale - s-a dovedit a fi „prea dur” pentru ei.

Astfel, sarcina depozitului este de a furniza „materii prime” pentru analiză într-un singur loc și într-o structură simplă, ușor de înțeles. Ralph Kimball scrie în introducerea cărții sale „The Data Warehouse Toolkit” că dacă, după citirea întregii cărți, cititorul înțelege doar un singur lucru și anume: structura depozitului trebuie să fie simplă, autorul va considera sarcina sa finalizată.

Există un alt motiv care justifică apariția unui spațiu de stocare separat - interogările analitice complexe privind informațiile operaționale încetinesc activitatea curentă a companiei, pentru o lungă perioadă de timp blocând tabelele și confiscând resursele serverului.

În opinia mea, stocarea nu este neapărat o acumulare gigantică de date - principalul lucru este că este convenabil pentru analiză. În general vorbind, un termen separat este destinat stocărilor mici - Data Marts (date marts), dar în practica noastră rusă nu îl auzi des.

OLAP este un instrument de analiză convenabil

Centralizarea și structurarea convenabilă nu sunt tot ceea ce are nevoie un analist. El are încă nevoie de un instrument pentru vizualizarea și vizualizarea informațiilor. Rapoartele tradiționale, chiar construite pe baza unui singur depozit, nu au un singur lucru - flexibilitatea. Nu pot fi rotite, extinse sau restrânse pentru a obține vizualizarea dorită a datelor. Desigur, puteți apela programatorul (dacă vrea să vină), iar el (dacă nu este ocupat) va face un nou raport suficient de repede - să zicem, în decurs de o oră (scriu și nu cred singur - nu se întâmplă niciodată) atât de repede în viață; hai să-i acordăm trei ore) ... Se pare că un analist nu poate verifica mai mult de două idei într-o zi. Și el (dacă este un bun analist) poate veni cu câteva astfel de idei pe oră. Și cu cât un analist vede mai multe „felii” și „tăieturi” de date, cu atât mai multe idei are, care, la rândul lor, necesită tot mai multe „felii” pentru verificare. Mi-aș dori să aibă un astfel de instrument care să permită extinderea și colapsarea datelor simplu și convenabil! OLAP acționează ca un astfel de instrument.

Deși OLAP nu este un atribut necesar al unui depozit de date, este utilizat din ce în ce mai mult pentru a analiza informațiile acumulate în acest depozit.

Componentele care alcătuiesc un depozit tipic sunt prezentate în Fig. 1.

Orez. 1. Structura depozitului de date

Datele operaționale sunt colectate din diverse surse, curățate, integrate și stocate într-un depozit relațional. Mai mult, acestea sunt deja disponibile pentru analiză folosind diverse instrumente de raportare. Apoi datele (în totalitate sau parțial) sunt pregătite pentru analiza OLAP. Acestea pot fi încărcate într-o bază de date OLAP specială sau lăsate într-un stocaj relațional. Cel mai important element al său sunt metadatele, adică informații despre structura, plasarea și transformarea datelor. Datorită acestora, este asigurată interacțiunea eficientă a diferitelor componente de stocare.

În rezumat, OLAP poate fi definit ca o colecție de instrumente analiza multivariată datele acumulate în stocare. În teorie, instrumentele OLAP pot fi aplicate direct la datele operaționale sau la copiile lor exacte (pentru a nu interfera cu utilizatorii operaționali). Însă riscăm să pășim rake-ul deja descris mai sus, adică să începem să analizăm datele operaționale care nu sunt potrivite direct pentru analiză.

Definiție și concepte de bază ale OLAP

Pentru început, să descifrăm: OLAP este Procesarea Analitică Online, adică analiza operațională a datelor. Cele 12 principii definitorii ale OLAP au fost formulate în 1993 de EF Codd, „inventatorul” bazelor de date relaționale. Mai târziu, definiția sa a fost reprelucrată în așa-numitul test FASMI, care necesită o aplicație OLAP pentru a oferi posibilitatea de a analiza rapid informațiile partajate multidimensionale ().

Test FASMI

Rapid(Rapid) - analiza trebuie efectuată la fel de repede pentru toate aspectele informațiilor. Timpul de răspuns acceptabil este de 5 secunde sau mai puțin.

Analiză(Analiză) - ar trebui să fie posibilă efectuarea tipurilor de bază de analiză numerică și statistică, predefinite de către dezvoltatorul aplicației sau definite în mod arbitrar de către utilizator.

Impartit(Partajat) - mai mulți utilizatori trebuie să aibă acces la date, iar accesul la informații confidențiale trebuie controlat.

Multidimensional(Multidimensional) este principala, cea mai esențială caracteristică a OLAP.

informație(Informații) - aplicația trebuie să poată accesa orice informații necesare, indiferent de volumul și locația de stocare.

OLAP = MDF = Cub

OLAP oferă instrumente convenabile și de mare viteză pentru accesarea, vizualizarea și analiza informațiilor despre afaceri. Utilizatorul primește un model de date natural, intuitiv, organizându-le sub formă de cuburi multidimensionale (Cuburi). Axele sistemului de coordonate multidimensionale sunt principalele atribute ale procesului de afaceri analizat. De exemplu, pentru vânzări, acesta poate fi un produs, regiune, tip de client. Timpul este folosit ca una dintre măsurători. La intersecțiile axelor - dimensiuni - există date care caracterizează cantitativ procesul - măsuri (Măsuri). Acestea pot fi volume de vânzări în bucăți sau în termeni monetari, solduri de stocuri, costuri etc. Utilizatorul care analizează informațiile poate „tăia” cubul în direcții diferite, poate primi un rezumat (de exemplu, pe an) sau, invers, detaliat (de săptămână) informații și alte manipulări care îmi vin în minte în timpul analizei.

Ca măsuri în cubul tridimensional prezentat în Fig. 2, se utilizează sumele de vânzare, iar timpul, produsul și magazinul sunt utilizate ca măsurători. Dimensiunile sunt prezentate la niveluri specifice de grupare: produsele sunt grupate pe categorii, magazinele sunt grupate pe țări, iar timpul tranzacțiilor este grupat pe lună. Vom analiza mai detaliat nivelurile de grupare (ierarhia).

Orez. 2. Exemplu de cub

„Tăierea” unui cub

Chiar și un cub tridimensional este dificil de afișat pe ecranul computerului, astfel încât valorile măsurilor de interes să fie vizibile. Ce putem spune despre cuburile cu mai mult de trei dimensiuni? Pentru a vizualiza datele stocate într-un cub, de regulă, sunt utilizate vederi bidimensionale familiare, adică tabulare, cu anteturi ierarhice complexe de rânduri și coloane.

O reprezentare bidimensională a unui cub poate fi obținută prin „tăierea” acestuia pe una sau mai multe axe (dimensiuni): fixăm valorile tuturor dimensiunilor, cu excepția a două - și obținem un tabel bidimensional regulat. Axa orizontală a unui tabel (anteturile coloanei) reprezintă o dimensiune, axa verticală (antetele rândurilor) reprezintă alta, iar celulele tabelului reprezintă valorile măsurate. În acest caz, un set de măsuri este considerat de fapt ca una dintre dimensiuni - fie selectăm o măsură pentru afișare (și apoi putem plasa două dimensiuni în titlurile rândurilor și coloanelor), fie afișăm mai multe măsuri (și apoi una dintre axele tabelului vor fi ocupate de numele măsurilor, iar cealaltă - valorile unei singure dimensiuni „netăiate”).

Uită-te la fig. 3 - Aceasta este o felie 2D a unui cub pentru o singură măsură, Vânzări unitare și două dimensiuni „netăiate”, Magazin și Timp.

Orez. 3. Felie de cub bidimensională pentru o singură măsură

În fig. 4 prezintă o singură dimensiune „netăiată” - Magazin, dar afișează valorile mai multor măsuri - Vânzări unitare, Vânzări magazin și Costul magazinului.

Orez. 4. Felie de cub bidimensională pentru mai multe măsuri

O reprezentare bidimensională a unui cub este posibilă și atunci când mai mult de două dimensiuni rămân „netăiate”. În acest caz, două sau mai multe dimensiuni ale cubului „tăiat” vor fi plasate pe axele feliei (rânduri și coloane) - vezi fig. 5.

Orez. 5. Felie bidimensională a unui cub cu mai multe dimensiuni pe o axă

Etichete

Valorile care sunt „relaxate” de-a lungul dimensiunilor se numesc membri. Etichetele sunt folosite atât pentru „tăierea” cubului, cât și pentru restricționarea (filtrarea) datelor selectate - atunci când, într-o dimensiune care rămâne „netăiată”, nu ne interesează toate valorile, ci subsetul lor, de exemplu, trei orașe de câteva zeci. Valorile etichetei sunt afișate în cubul 2D ca titluri de rând și coloană.

Ierarhii și niveluri

Etichetele pot fi combinate în ierarhii de unul sau mai multe niveluri. De exemplu, etichetele dimensiunii Magazin sunt grupate în mod natural într-o ierarhie cu niveluri:

Țară

Stat

Oraș

Magazin.

Valorile agregate sunt calculate în funcție de nivelurile ierarhiei, cum ar fi vânzările pentru SUA (nivel de țară) sau California (nivel de stat). Mai multe ierarhii pot fi implementate într-o singură dimensiune - de exemplu, pentru timp: (An, Trimestru, Lună, Zi) și (An, Săptămână, Zi).

Arhitectura aplicației OLAP

Tot ceea ce s-a spus mai sus despre OLAP, de fapt, a fost legat de prezentarea multidimensională a datelor. Modul în care sunt stocate datele, aproximativ vorbind, nu privește nici utilizatorul final, nici dezvoltatorii instrumentului pe care îl folosește clientul.

Multidimensionalitatea în aplicațiile OLAP poate fi împărțită în trei niveluri:

• Prezentarea datelor multidimensionale - instrumente pentru utilizatorul final care oferă vizualizare multidimensională și manipulare a datelor; stratul MDI abstractizează din structura fizică a datelor și tratează datele ca fiind multidimensionale.
• Procesarea multidimensională este un instrument (limbaj) pentru formularea interogărilor multidimensionale (limbajul SQL relațional tradițional se dovedește a fi inadecvat aici) și un procesor care poate procesa și executa o astfel de interogare.
• Depozitare multidimensională - facilități organizarea fizică date pentru executarea eficientă a interogărilor multidimensionale.

Primele două niveluri sunt obligatorii în toate instrumentele OLAP. Al treilea nivel, deși răspândit, nu este necesar, deoarece datele pentru reprezentarea multidimensională pot fi recuperate din structuri relaționale regulate; procesorul de interogare multidimensională în acest caz traduce interogările multidimensionale în interogări SQL care sunt executate de SGBD relațional.

Produsele OLAP specifice sunt de obicei fie un vizualizator de date multidimensional, un client OLAP (de exemplu, Pivot Tables în Excel 2000 de la Microsoft sau ProClarity de la Knosys), fie o bază de date server multidimensională, server OLAP (de exemplu, Oracle Express Server sau Microsoft OLAP Services ).

Stratul de procesare multidimensional este de obicei încorporat în clientul OLAP și / sau în serverul OLAP, dar poate fi izolat în forma sa pură, cum ar fi componenta Microsoft Pivot Table Service.

Aspecte tehnice ale stocării datelor multidimensionale

După cum sa menționat mai sus, instrumentele de analiză OLAP pot extrage date direct din sistemele relaționale. Această abordare era mai atractivă în zilele în care serverele OLAP lipseau din listele de prețuri ale furnizorilor principali de baze de date. Dar astăzi, Oracle, Informix și Microsoft oferă servere OLAP complete și chiar acei manageri IT cărora nu le place să planteze o „grădină zoologică” de software de la diferiți producători în rețelele lor pot cumpăra (mai precis, contactați conducerea companiei) Serverul OLAP este același brand ca serverul principal de baze de date.

Serverele OLAP sau serverele de baze de date multidimensionale își pot stoca datele multidimensionale în diferite moduri. Înainte de a lua în considerare aceste metode, trebuie să vorbim despre un aspect atât de important ca stocarea agregatelor. Faptul este că în orice depozit de date - atât obișnuit, cât și multidimensional - împreună cu date detaliate preluate din sistemele operaționale, indicatori de sinteză (agregate, agregate), cum ar fi suma volumelor de vânzări pe luni, pe categorii de bunuri etc. Agregatele sunt stocate în mod explicit cu singurul scop de a accelera interogările. Într-adevăr, pe de o parte, de regulă, o cantitate foarte mare de date se acumulează în depozit și, pe de altă parte, analiștii, în majoritatea cazurilor, sunt interesați nu de indicatori detaliați, ci de indicatori generalizați. Și dacă milioane de vânzări individuale ar trebui să fie însumate de fiecare dată pentru a calcula vânzările anuale, viteza ar fi cel mai probabil inacceptabilă. Prin urmare, atunci când încărcați date într-o bază de date multidimensională, toți indicatorii rezumat sau o parte din aceștia sunt calculați și salvați.

Dar, după cum știți, trebuie să plătiți pentru tot. Și trebuie să plătiți pentru viteza de procesare a cererilor către datele agregate, mărind cantitatea de date și timpul necesar încărcării acestora. Mai mult, creșterea volumului poate deveni literalmente catastrofală - într-unul dintre testele standard publicate, un număr complet de agregate pentru 10 MB de date inițiale a necesitat 2,4 GB, adică datele au crescut de 240 de ori! Gradul de „umflare” a datelor la calcularea agregatelor depinde de numărul de dimensiuni din cub și de structura acestor dimensiuni, adică de raportul numărului de „tați” și „copii” la diferite niveluri ale dimensiunii. Pentru a rezolva problema stocării agregatelor, acestea sunt uneori folosite circuite complexe care permit, atunci când se calculează departe de toate agregatele posibile, să se obțină o creștere semnificativă a performanței interogării.

Acum despre diferitele opțiuni pentru stocarea informațiilor. Atât datele detaliate, cât și agregatele pot fi stocate în structuri relaționale sau multidimensionale. Stocarea multidimensională vă permite să tratați datele ca o matrice multidimensională, oferind astfel sumări la fel de rapide și diverse transformări multidimensionale pentru orice dimensiune. Cu ceva timp în urmă, produsele OLAP suportau stocarea relațională sau multidimensională. Astăzi, de regulă, același produs oferă atât aceste tipuri de stocare, cât și al treilea tip - mixt. Se aplică următorii termeni:

• MOLAP(OLAP multidimensional) - atât datele detaliate, cât și agregatele sunt stocate într-o bază de date multidimensională. În acest caz, se obține cea mai mare redundanță, deoarece datele multidimensionale conțin complet date relaționale.
• ROLAP(OLAP relațional) - datele detaliate rămân acolo unde au „trăit” inițial - într-o bază de date relațională; agregatele sunt stocate în aceeași bază de date în tabele de servicii special create.
• HOLAP(OLAP hibrid) - datele detaliate rămân la locul lor (într-o bază de date relațională), iar agregatele sunt stocate într-o bază de date multidimensională.

Fiecare dintre aceste metode are propriile avantaje și dezavantaje și ar trebui aplicate în funcție de condiții - cantitatea de date, puterea SGBD relațional etc.

La stocarea datelor în structuri multidimensionale, există o problemă potențială de umflare datorită stocării valorilor goale. La urma urmei, dacă spațiul este rezervat într-o matrice multidimensională pentru toate combinațiile posibile de etichete de măsurare și doar o mică parte este de fapt umplută (de exemplu, un număr de produse sunt vândute doar într-un număr mic de regiuni), atunci majoritatea cubul va fi gol, deși spațiul va fi ocupat. Produsele OLAP moderne sunt capabile să facă față acestei probleme.

Va urma. În viitor, vom vorbi despre produse OLAP specifice produse de producători de renume.