Un exemplu de creare a unui cub în VBA. OLAP-CUBE (raportare de management dinamic)

Un fișier cub autonom (.cub) stochează date într-un formular într-un cub de procesare analitică online (OLAP). Aceste date pot reprezenta o parte a bazei de date date OLAP de pe un server OLAP sau poate fi creat independent de orice bază de date OLAP. Pentru a continua să lucrați cu rapoartele PivotTable și PivotChart atunci când serverul este indisponibil sau când este offline, utilizați un fișier cub offline.

Aflați mai multe despre cuburile offline

Când lucrați cu un raport PivotTable sau PivotChart care se bazează pe o sursă de date de la un server OLAP, utilizați Offline Cube Wizard pentru a copia datele sursă într-un fișier cub offline separat de pe computer. Pentru a crea aceste fișiere offline, trebuie să aveți un furnizor de date OLAP care acceptă aceste capabilități, cum ar fi MSOLAP de la Microsoft SQL Server Analysis Services, instalat pe computer.

Notă: Crearea și utilizarea fișierelor cub offline de la Microsoft SQL Server Analysis Services, sub rezerva condițiilor și a licenței Instalări Microsoft SQL Server. Examinați informațiile de licență adecvate pentru versiunea dvs. de SQL Server.

Utilizarea Offline Cube Wizard

Pentru a crea un fișier cub offline, utilizați Offline Cube Wizard pentru a selecta un subset de date din baza de date OLAP, apoi salvați acel set. Raportul nu trebuie să includă toate câmpurile incluse în fișier și puteți alege dintre dimensiunile și câmpurile de date ale acestuia disponibile în baza de date OLAP. Pentru a minimiza dimensiunea fișierului, puteți include numai datele pe care doriți să le puteți afișa în raport. Puteți sări peste toate dimensiunile și, pentru majoritatea tipurilor de măsurători, puteți omite și mai mult de nivel scăzut detalii si elemente nivel superior, care nu trebuie afișate. Pentru un fișier offline, sunt salvate și toate elementele care pot fi incluse în câmpurile de proprietate care sunt disponibile în baza de date pentru acele elemente.

Preluarea datelor offline și apoi readucerea datelor online

Pentru a face acest lucru, mai întâi trebuie să creați un raport PivotTable sau un raport PivotChart care se bazează pe baza de date a serverului, apoi creați un fișier cub autonom din raport. Ulterior, atunci când lucrați cu un raport, puteți comuta oricând între baza de date server și un fișier offline (de exemplu, când lucrați la laptop acasă sau pe drum și apoi restabilirea conexiunii computerului la rețea).

Următoarele descriu pașii de bază pentru a scoate datele offline și a le readuce online.

Notă:

Faceți clic pe raportul PivotTable. Dacă acesta este un raport PivotChart, selectați raportul PivotTable asociat.

Pe „fila” Analiză" in grup calculele faceți clic pe butonul Serviciul OLAPși apăsați butonul OLAP offline.

Selectați un articol OLAP cu conectivitateși apoi faceți clic pe butonul Bine.

Dacă vi se solicită să găsiți o sursă de date, faceți clic Găsiți sursași găsiți un server OLAP în rețea.

Faceți clic pe raportul PivotTable care se bazează pe fișierul cub offline.

În Excel 2016: în fila „ date" in grup solicitări și conexiuni Actualizați toateși apăsați butonul Actualizați.

În Excel 2013: în fila „ date" in grup conexiuni faceți clic pe săgeata de lângă buton Actualizați toateși apăsați butonul Actualizați.

Pe „fila” Analiză" in grup calculele faceți clic pe butonul Serviciul OLAPși apăsați butonul OLAP offline.

Faceți clic pe butonul Modul offline OLAP, și apoi - .

Notă: Stopîn caseta de dialog.

Avertizare:

Crearea unui fișier cub offline dintr-o bază de date de server OLAP

Notă: Dacă baza de date OLAP este mare și fișierul cub este necesar pentru a oferi acces la un subset mare de date, o mulțime de spatiu liber pe disc, iar salvarea fișierului poate dura mult timp. Pentru a îmbunătăți performanța, se recomandă să creați fișiere cub autonome folosind un script MDX.

Problemă: Computerul meu nu are suficient spațiu pe disc atunci când salvez un cub.

Bazele de date OLAP sunt concepute pentru a gestiona cantități mari de date detaliate, astfel încât o bază de date găzduită pe un server poate ocupa mult mai mult spațiu decât este disponibil pe hard disk-ul local. Dacă selectați o cantitate mare de date pentru un cub de date offline, este posibil să nu aveți suficient spațiu liber pe disc. Următoarea abordare va ajuta la reducerea dimensiunii fișierului cub offline.

Eliberați spațiu pe disc sau selectați un alt discÎnainte de a salva fișierul cub, scoateți-l de pe disc. fișiere inutile sau salvați fișierul pe o unitate de rețea.

Includerea mai puține date într-un fișier cub offline Luați în considerare cum puteți minimiza cantitatea de date incluse în fișier, astfel încât fișierul să conțină toate datele necesare pentru un raport PivotTable sau PivotChart. Încercați pașii de mai jos.

Conectarea unui fișier cub offline la o bază de date de server OLAP

Actualizarea și recrearea unui fișier cub offline

Actualizarea unui fișier cub offline care este creat din cele mai recente date obținute dintr-un cub server sau dintr-un fișier cub offline nou poate dura o perioadă semnificativă de timp și necesită o cantitate mare de spațiu temporar pe disc. Rulați acest proces atunci când nu aveți nevoie de acces imediat la alte fișiere, după ce vă asigurați că aveți suficient spațiu pe hard disk.

Problemă: Datele noi nu apar în raport când sunt reîmprospătate.

Verificarea disponibilității bazei de date sursă Este posibil ca fișierul cub offline să nu se poată conecta la baza de date a serverului sursă pentru a obține date noi. Asigurați-vă că baza de date originală de pe serverul care este sursa de date pentru cub nu a fost redenumită sau mutată în altă locație. Asigurați-vă că serverul este accesibil și la care poate fi conectat.

Se verifică date noi Verificați cu administratorul bazei de date pentru a vedea dacă datele care ar trebui incluse în raport au fost actualizate.

Verificarea imuabilității organizării bazei de date Dacă cubul serverului OLAP a fost modificat, poate fi necesar să reorganizați raportul, să creați un fișier cub offline sau să rulați Expertul Creare cub OLAP pentru a accesa datele modificate. Pentru a afla despre modificările bazei de date, contactați administratorul bazei de date.

Includerea altor date în fișierul cub offline

Salvarea unui fișier cub offline modificat poate fi consumatoare de timp și necesită lucru Microsoft Excel nu este posibil în timpul salvării fișierului. Rulați acest proces atunci când nu aveți nevoie de acces imediat la alte fișiere, după ce vă asigurați că aveți suficient spațiu pe hard disk.

Verificați dacă există o conexiune la rețea și că baza de date a serverului OLAP sursă de la care fișierul cub offline obținut este accesibilă.

Faceți clic pe un raport PivotTable creat dintr-un fișier cub autonom sau pe un raport PivotTable asociat pentru un raport PivotChart.

Pe fila Opțiuni in grup Serviciu faceți clic pe butonul Serviciul OLAPși apăsați butonul Modul OLAP offline.

Faceți clic pe butonul Modul OLAP offline, și apoi - Editați fișierul de date offline.

Urmați Offline Cube Wizard pentru a selecta alte date de inclus în acest fișier. În ultimul pas, specificați numele și calea către fișierul de modificat.

Notă: Pentru a anula salvarea fișierului, faceți clic pe butonul Stopîn caseta de dialog Crearea unui fișier cub - progres.

Ștergerea unui fișier cub offline

Avertizare: Dacă ștergeți un fișier cub offline pentru un raport, nu mai puteți utiliza acel raport offline și nu mai puteți crea un fișier cub offline pentru raportul respectiv.

Închideți toate registrele de lucru care conțin rapoarte care utilizează fișierul cub offline sau asigurați-vă că toate aceste rapoarte sunt șterse.

ÎN Microsoft Windows Localizați și ștergeți fișierul cub offline (fișier CUB).

Informații suplimentare

Puteți oricând să puneți o întrebare unui specialist Excel Tech Community, să cereți ajutor în comunitatea Answers și, de asemenea, să sugerați optiune noua sau îmbunătățirea site-ului

Cuburile de date OLAP (Online Analytical Processing) vă permit să extrageți și să analizați eficient datele multidimensionale. Spre deosebire de alte tipuri de baze de date, bazele de date OLAP sunt concepute special pentru procesarea analitică și extragerea rapidă a tuturor tipurilor de seturi de date din acestea. Există de fapt 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 Din serviciile de analiză

Descărcați nota în sau

În bazele de date relaționale, informațiile sunt reprezentate ca înregistrări care sunt adăugate, șterse ș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șirii la cerere. Deși puteți adăuga la o bază de date OLAP informație nouă, datele existente sunt rareori editate, cu atât mai puțin șterse.

Bazele de date relaționale și bazele de date OLAP sunt diferite din punct de vedere 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 cum sunt conectate. În bazele de date OLAP, relațiile dintre blocurile individuale de date sunt definite în prealabil și stocate î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 aceasta. În plus, este mult mai ușor să creezi rapoarte dacă știi dinainte unde se află datele pe care le extragi și ce alte date sunt asociate cu acestea.

Principala diferență dintre 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 prezentate într-un mod general. Cuburile de date OLAP conțin de obicei informații prezentate într-un format prestabilit. Astfel, operațiunile de grupare, filtrare, sortare și îmbinare a datelor în cuburi sunt efectuate înainte de completarea acestora cu informații. Acest lucru face ca recuperarea și afișarea datelor solicitate să fie cât mai simplificată 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 dumneavoastră nu are o structură care să fie responsabilă cu 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 o bază de date OLAP, mai întâi trebuie să stabiliți o conexiune la cubul OLAP. Începeți prin a accesa fila Panglică Date. Faceți clic pe butonul Din alte surseși selectați comanda din meniul derulant Din serviciile de analiză(Fig. 1).

Când selectați comanda specificată a expertului pentru conexiune de date (Fig. 2). Sarcina sa principală este să vă ajute să stabiliți o conexiune la serverul care va fi utilizat programul Excel la gestionarea datelor.

1. Mai întâi trebuie să furnizați Excel informații de înregistrare. Introduceți numele serverului în câmpurile casetei de dialog, numele de înregistrareși parola de acces la date, așa cum se arată în Fig. 2. Faceți clic pe butonul Mai departe. Dacă vă conectați folosind cont 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 folosește baza de date Tutorial Analysis Services. Odată ce selectați această bază de date, lista de mai jos vă solicită să importați toate cuburile OLAP disponibile în ea. Selectați cubul de date dorit și faceți clic pe butonul Mai departe.

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

3. În următoarea casetă de dialog expert, 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 nu trebuie completate. Puteți ignora oricând caseta de dialog curentă fără a o completa, iar acest lucru nu vă va afecta în niciun fel conexiunea.

Orez. 4. Modificați informațiile descriptive ale conexiunii

4. Faceți clic pe butonul Gata pentru a finaliza crearea conexiunii. Pe ecran va apărea o casetă de dialog Importă date(Fig. 5). Setați comutatorul Raport PivotTableși faceți clic pe OK pentru a începe crearea tabelului pivot.

Structura cubului OLAP

Pe măsură ce creați un tabel pivot dintr-o bază de date OLAP, veți observa că fereastra panoului de activități Câmpuri din tabel pivot va fi diferită de cea pentru un tabel pivot obișnuit. Motivul constă în aranjarea tabelului pivot, astfel încât să reflecte îndeaproape structura cubului OLAP atașat acestuia. Pentru a naviga printr-un cub OLAP cât mai repede posibil, trebuie să vă familiarizați complet cu componentele acestuia și cu modul în care acestea interacționează. În fig. Figura 6 prezintă structura de bază a unui cub OLAP tipic.

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

Dimensiuni. Principalele caracteristici ale elementelor de date analizate. Exemplele comune de dimensiuni includ Produse, Client și Angajat. În fig. Figura 6 prezintă structura dimensiunii Produse.
Ierarhii. O agregare predefinită de niveluri într-o dimensiune specificată. Ierarhia vă permite să creați date rezumative și să le analizați la diferite niveluri ale structurii, fără a explora 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 de Categorii de produse.
Niveluri. Nivelurile sunt categorii care sunt agregate într-o ierarhie comună. Gândiți-vă la straturi ca la câmpuri de date care pot fi interogate și analizate separat unele de altele. În fig. 6 există doar trei niveluri: Categorie, SubCategorie și Nume produs.
Membrii. Un singur element date din cadrul dimensiunii. Membrii sunt de obicei accesați printr-o structură OLAP de dimensiuni, ierarhii și niveluri. În exemplul din fig. 6 membri sunt definiți pentru nivelul Nume produs. Alte niveluri au proprii lor membri, care nu sunt afișați în structură.
Măsuri- acestea sunt date reale în cuburi OLAP. Măsurile sunt stocate în propriile dimensiuni, care se numesc dimensiuni de măsură. Puteți interoga măsuri folosind orice combinație de dimensiuni, ierarhii, niveluri și membri. Această procedură se numește măsuri de „traniere”.

Acum că sunteți familiarizat cu structura cuburilor OLAP, să aruncăm o privire nouă asupra listei de câmpuri PivotTable. Organizarea câmpurilor disponibile devine clară și nu provoacă reclamații. În fig. Figura 7 arată cum lista de câmpuri reprezintă elementele unui tabel pivot OLAP.

În lista de câmpuri dintr-un tabel pivot OLAP, măsurile apar mai întâi și sunt indicate de o pictogramă de sumă (sigma). Acestea sunt singurele elemente de date care pot fi în regiunea VALUE. După ele în listă sunt indicate dimensiunile, indicate printr-o pictogramă cu o imagine de tabel. Exemplul nostru folosește dimensiunea Client. Această dimensiune conține o serie de ierarhii. Odată ce ierarhia este extinsă, puteți vizualiza nivelurile individuale de date. Pentru a vizualiza structura de date a unui cub OLAP, pur și simplu navigați prin lista de câmpuri din tabelul pivot.

Restricții asupra tabele pivot OLAP

Când lucrați cu OLAP PivotTables, rețineți că interacționați cu sursa de date PivotTable în mediul Analysis Services OLAP. Aceasta înseamnă că fiecare aspect comportamental al cubului de date, de la dimensiuni până la măsurile care sunt incluse în cub, este, de asemenea, controlat de serviciile de analiză OLAP. La rândul său, acest lucru duce la restricții asupra operațiunilor care pot fi efectuate pe tabelele pivot OLAP:

Nu puteți plasa alte câmpuri decât măsuri în zona VALORI a unui tabel pivot;
este imposibil să se schimbe funcția utilizată pentru însumare;
Nu puteți crea un câmp calculat sau un articol calculat;
orice modificare a numelor câmpurilor este anulată imediat după ce câmpul este eliminat din PivotTable;
Modificarea parametrilor câmpului paginii nu este permisă;
comanda nu este disponibilă Spectacolpagini;
opțiunea dezactivată Spectacolsemnăturielemente dacă nu există câmpuri în zona valorii;
opțiunea dezactivată Subtotaluri după elementele de pagină selectate de filtru;
parametrul nu este disponibil fundalcerere;
după dublu click Doar primele 1000 de înregistrări din memoria cache a tabelului pivot sunt returnate în câmpul VALUES;
caseta de selectare dezactivată Optimizațimemorie.

Crearea de cuburi de date autonome

Într-un tabel pivot standard, datele sursă sunt stocate pe hard disk-ul local. Astfel, le puteți gestiona oricând, precum și modifica structura, chiar și fără acces la rețea. Dar acest lucru nu se aplică în niciun caz tabelelor pivot OLAP. În tabelele pivot OLAP, memoria cache nu este localizată pe hard disk-ul local. Prin urmare, imediat după deconectare de la retea locala tabelul pivot OLAP nu va mai funcționa. Nu veți putea muta un singur câmp într-un astfel de tabel.

Dacă mai trebuie să analizați datele OLAP când nu sunteți conectat la o rețea, creați un cub de date offline. Acesta este un fișier separat care reprezintă memoria cache a tabelului pivot. Acest fișier stochează date OLAP care sunt vizualizate după deconectarea de la rețeaua locală. Pentru a crea un cub de date autonom, creați mai întâi un tabel pivot OLAP. Plasați cursorul în tabelul pivot și faceți clic pe butonul instrumente OLAP filă contextuală Analiză, inclusă în setul de file contextuale Lucrul cu tabelele pivot. Selectați o echipă Modul OLAP offline(Fig. 8).

Pe ecran va apărea o casetă de dialog Setări durata de viata a bateriei OLAP(Fig. 9). Faceți clic pe butonul Creați fișier de date offline. Pe ecran va apărea prima fereastră a Expertului de creare a fișierelor cubului de date. Faceți clic pe butonul Mai departe pentru a continua procedura.

În al doilea pas (Fig. 10), indicaț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 doar 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 autonom.

Faceți clic pe butonul Mai departe pentru a trece la a treia etapă (Fig. 11). În această fereastră trebuie să selectați membri sau elemente de date care nu vor fi incluse în cub. Dacă caseta de selectare nu este bifată, elementul specificat nu va fi importat și va ocupa spațiu inutil pe hard disk-ul local.

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

Dupa ceva timp Ora 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 lucru Caiete 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 locală, puteți deschide fișierul cub de date offline și îl puteți actualiza și tabelul de date asociat. Vă rugăm să rețineți că, deși cubul de date offline este utilizat atunci când nu există acces la rețea, este necesar să fie actualizat atunci când conexiunea la rețea este restabilită. Încercarea de a actualiza un cub de date offline după ce conexiunea la rețea a fost pierdută va avea ca rezultat o eșec.

Utilizarea funcțiilor cuburilor de date în tabelele pivot

Funcțiile cuburilor de date care sunt utilizate în bazele de date OLAP pot fi, de asemenea, rulate dintr-un tabel pivot. ÎN versiuni învechiteÎn Excel, ați avut acces la funcționalitatea cubului de date numai după instalarea suplimentului Analysis Pack. În Excel 2013, aceste funcții sunt încorporate în program și, prin urmare, sunt disponibile pentru utilizare. Pentru a le înțelege pe deplin capacitățile, să ne uităm la un exemplu specific.

Una dintre cele mai moduri simpleÎnvățarea funcțiilor unui cub de date implică conversia unui tabel pivot OLAP în formule de cub de date. Această procedură este foarte simplă și vă permite să obțineți rapid formule cub de date fără a le crea de la zero. Principiul cheie este înlocuirea tuturor celulelor din tabelul pivot cu formule care sunt legate la baza de date OLAP. În fig. Figura 13 prezintă un tabel pivot asociat cu o bază de date OLAP.

Plasați cursorul oriunde în tabelul pivot, faceți clic pe butonul instrumente OLAP fila panglică contextuală Analizăși selectați o echipă Convertiți în formule(Fig. 14).

Dacă tabelul pivot conține un câmp de filtru de raport, caseta de dialog prezentată în Figura 1 va apărea pe ecran. 15. În această fereastră, puteți specifica dacă doriți să convertiți listele derulante ale filtrului de date în formule. Dacă răspunsul este da, listele derulante vor fi eliminate și vor fi afișate formulele statice. Dacă intenționați să utilizați listele derulante în viitor pentru a modifica conținutul tabelului pivot, debifați singura casetă de selectare din caseta de dialog. Dacă lucrați la un tabel pivot în modul de compatibilitate, filtrele de date vor fi convertite în formule automat, fără avertisment prealabil.

După câteva secunde, în loc de PivotTable, veți vedea formule care rulează pe cuburile de date și oferă rezultate în fereastra Excel informatie necesara. Vă rugăm să rețineți că acest lucru elimină stilurile aplicate anterior (Fig. 16).

Orez. 16. Aruncă o privire la bara de formule: celulele conțin formulele cuburilor de date

Deoarece 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 personaliza raportul. căi diferite, inclusiv tragerea și plasarea formulelor.

Adăugarea de calcule la tabelele pivot OLAP

ÎN Versiuni anterioare Tabelele pivot OLAP Excel nu permiteau calcule personalizate. Aceasta înseamnă că nu a fost posibil să adăugați un nivel suplimentar de analiză la Tabelele Pivote OLAP în același mod în care este posibil să adăugați câmpuri calculate și membri la Tabelele Pivote obișnuite (pentru mai multe informații, vă rugăm să vă asigurați că sunteți familiarizat cu acest material înainte de a continua lectură).

Excel 2013 introduce noi instrumente OLAP - măsuri calculate și membri calculati ai expresiilor MDX. Nu mai sunteți limitat la utilizarea măsurilor și a membrilor din cubul OLAP furnizați de DBA. Primești caracteristici suplimentare analiză prin crearea de calcule personalizate.

Introducere în MDX. Când utilizați un tabel pivot cu un cub OLAP, emiteți interogări MDX (Expresii multidimensionale) în baza de date. MDX este un limbaj de interogare folosit pentru a prelua 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 trimise la baza de date OLAP. Rezultatele interogării sunt returnate înapoi în Excel și afișate în zona PivotTable. Acest lucru face posibilă lucrul cu date OLAP fără copie locală cache-ul tabelului pivot.

Când creați măsuri calculate și membri MDX, utilizați sintaxa limbajului MDX. Folosind această sintaxă, un tabel pivot permite interacțiunea cu calculele partea de server baze de date OLAP. Exemplele discutate în carte se bazează pe modele de bază MDX care demonstrează noi Funcții Excel 2013. Dacă trebuie să creați măsuri complexe calculate și membri MDX, va trebui să petreceți timp pentru a afla mai multe despre capabilitățile MDX.

Creați măsuri calculate. O măsură calculată este versiunea 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 folosește un tabel rezumativ OLAP, care cuprinde lista și cantitatea de bunuri, precum și veniturile din vânzarea fiecăreia dintre acestea. Trebuie să adăugăm o nouă măsură care va calcula prețul mediu pe unitate a unui articol.

Analiză Lucrul cu tabelele pivot. În meniul drop-down instrumente OLAP selectați elementul (Fig. 18).

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

Pe ecran va apărea o casetă de dialog Creați o măsură calculată(Fig. 19).

Urmați acești pași:

2. Selectați grupul de măsuri în care va fi localizată noua măsură calculată. Dacă nu faceți acest lucru, Excel va plasa automat noua măsură în primul grup de măsuri disponibil.

3. În câmp Expresia MDX(MDX) Introduceți codul care specifică noua măsură. Pentru a accelera procesul de introducere, utilizați lista din stânga pentru a selecta măsurile existente pe care să le utilizați în calcule. Faceți dublu clic pe măsura dorită pentru a o adăuga la câmpul MDX. Următorul MDX este utilizat pentru a calcula prețul unitar mediu de vânzare:

4. Faceți clic pe OK.

Acordați atenție butonului Verificați MDX, care se află în partea dreaptă jos a ferestrei. Faceți clic pe acest buton pentru a verifica dacă sintaxa MDX este corectă. Dacă sintaxa conține erori, apare un mesaj.

După ce ați terminat de creat noua măsură calculată, mergeți la listă Câmpuri din tabel pivotși selectați-l (Fig. 20).

Sfera de aplicare a unei măsuri calculate se aplică numai registrului de lucru curent. Cu alte cuvinte, măsurile calculate nu sunt create direct în cubul serverului OLAP. Aceasta înseamnă că nimeni nu va putea accesa măsura calculată decât dacă deschideți acces general La registrul de lucru sau nu îl vei publica pe internet.

Creați membri MDX calculati. Un membru calculat MDX este versiunea 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, este utilizat un tabel pivot OLAP care include informații despre vânzări pentru 2005–2008 (cu o defalcare trimestrială). Să presupunem că doriți să agregați datele pentru primul și al doilea trimestru prin creare element nou Prima jumătate a anului. De asemenea, vom combina datele referitoare la trimestrul al treilea și al patrulea, formând un nou element al doilea al anului.

Orez. 22. Vom adăuga noi membri calculati MDX, Prima jumătate a anului și a doua jumătate a anului

Plasați cursorul oriunde în PivotTable și selectați fila contextuală Analiză dintr-un set de file contextuale Lucrul cu tabelele pivot. În meniul drop-down instrumente OLAP selectați elementul Membru MDX Computed(Fig. 23).

Pe ecran va apărea o casetă de dialog (Fig. 24).

Orez. 24. Fereastra Crearea unui articol calculat

Urmați acești pași:

1. Dați un nume măsurării calculate.

2. Selectați ierarhia părinte pentru care creați noi membri calculati. La un șantier Element părinte atribuie o valoare Toate. Această setare permite Excel să acceseze toți membrii ierarhiei părinte atunci când evaluează o expresie.

3. În fereastră Expresia MDX Introduceți sintaxa MDX. Pentru a economisi ceva timp, utilizați lista din stânga pentru a selecta membrii existenți pe care să îi folosiți în MDX. Faceți dublu clic pe elementul selectat și Excel îl va adăuga în fereastră Expresia MDX. În exemplul prezentat în fig. 24, se calculează suma primului și al doilea trimestru:

..&& +

.. && +

.. && + …

4. Faceți clic pe OK. Excel afișează membrul calculat MDX nou creat în PivotTable. După cum se arată în Fig. 25, noul articol calculat este afișat împreună cu celelalte elemente calculate în PivotTable.

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

Observați că Excel nici măcar nu încearcă să elimine membrii MDX originali (Figura 27). Tabelul Pivot continuă să arate înregistrări corespunzătoare anilor 2005–2008, defalcate pe trimestru. În acest caz, aceasta nu este mare lucru, dar în majoritatea scenariilor ar trebui să ascundeți elemente „în plus” pentru a evita conflictele.

Orez. 27. Excel afișează membrul calculat MDX creat ca membri originali. Dar este mai bine să ștergeți elementele originale pentru a evita conflictele

Rețineț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 serverului OLAP. Aceasta înseamnă că nimeni nu va putea accesa măsura calculată sau membrul calculat decât dacă distribuiți registrul de lucru sau îl publicați online.

Rețineți că dacă ierarhia părinte sau elementul părinte dintr-un cub OLAP se modifică, elementul calculat MDX nu mai funcționează. Va trebui să recreați acest element.

Gestionarea calculelor 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 PivotTable și selectați fila contextuală Analiză dintr-un set de file contextuale Lucrul cu tabelele pivot. În meniul drop-down instrumente OLAP selectați elementul Managementul calculatoarelor. La fereastră Managementul calculatoarelor Sunt disponibile trei butoane (Fig. 28):

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

Orez. 28. Caseta de dialog Managementul calculatoarelor

Efectuați o analiză ce se întâmplă în cazul datelor OLAP.În Excel 2013, puteți efectua o analiză ce se întâmplă dacă datele din tabelele pivot OLAP. Datorită acestui lucru noua oportunitate Puteți modifica valorile într-un tabel Pivot și puteți recalcula măsurile și membrii pe baza modificărilor dvs. De asemenea, puteți propaga modificările înapoi în cubul OLAP. Pentru a profita de capabilitățile de analiză ce se întâmplă, creați un tabel pivot OLAP și selectați fila contextuală Analiză Lucrul cu tabelele pivot. În meniul drop-down instrumente OLAP alege echipa Analiza ce ar fi –> Activați analiza ce se întâmplă dacă(Fig. 29).

Din acest moment, puteți modifica valorile tabelului pivot. Pentru a modifica valoarea selectată în PivotTable, faceți clic dreapta pe ea și selectați elementul din meniul contextual (Fig. 30). Excel va rula din nou toate calculele din PivotTable cu modificările pe care le-ați făcut, inclusiv măsurile calculate și membrii MDX calculați.

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

În mod implicit, editările aduse unui Tabel Pivot în modul de analiză dacă sunt locale. Dacă doriți să propagați modificările pe serverul OLAP, selectați comanda pentru a publica modificările. Selectați o filă contextuală Analiză, situat într-un set de file contextuale Lucrul cu tabelele pivot. În meniul drop-down instrumente OLAP selectați articole Analiza ce ar fi – > Publicați modificările(Fig. 31). Rularea acestei comenzi va permite rescrierea pe serverul OLAP, ceea ce înseamnă că modificările pot fi propagate la cubul OLAP sursă. (Pentru a propaga modificările la serverul OLAP, trebuie să aveți permisiunile corespunzătoare pentru a accesa serverul. Contactați administratorul bazei de date pentru a vă ajuta să obțineți permisiuni de acces la scriere la baza de date OLAP.)

Nota a fost scrisă pe baza cărții lui Jelen, Alexander. . Capitolul 9

În articolul precedent din această serie (vezi Nr. 2’2005), am vorbit despre principalele inovații ale serviciilor analitice din SQL Server 2005. Astăzi vom arunca o privire mai atentă asupra instrumentelor de creare a soluțiilor OLAP incluse în acest produs.

Pe scurt despre elementele de bază ale OLAP

Înainte de a începe să vorbim despre instrumente pentru crearea soluțiilor OLAP, să reamintim că OLAP (On-Line Analytical Processing) este o tehnologie de analiză complexă a datelor multidimensionale, al cărei concept a fost descris în 1993 de către E.F. Codd, celebrul autor. model relațional date. În prezent, suportul OLAP este implementat în multe SGBD-uri și alte instrumente.

cuburi OLAP

Ce sunt datele OLAP? Pentru a răspunde la această întrebare, luați în considerare un exemplu simplu. Să presupunem că în baza de date corporativă a unei anumite întreprinderi există un set de tabele care conțin informații despre vânzările de bunuri sau servicii, iar pe baza acestora a fost creată o vizualizare Facturi cu câmpurile Țara (țara), Orașul (orașul), Nume Client (numele companiei client), agent de vânzări (manager de vânzări), Data comandă (data plasării comenzii), CategoryName (categoria de produs), ProductName (numele produsului), ShipperName (compania de transport), ExtendedPrice (plată pentru mărfuri), în timp ce ultimul dintre aceste domenii este, de fapt, obiectul analizei.

Selectarea datelor dintr-o astfel de vizualizare se poate face folosind următoarea interogare:

SELECTARE Țară, Oraș, Nume Client, Agent de vânzări,

OrderDate, CategoryName, ProductName, ShipperName, ExtendedPrice

DIN Facturi

Să presupunem că suntem interesați de valoarea totală a comenzilor efectuate de clienți din diferite țări. Pentru a obține un răspuns la această întrebare, trebuie să faceți următoarea solicitare:

SELECTARE Țara, SUMA (Preț extins) FROM Facturi

GRUP PE Țară

Rezultatul acestei interogări va fi un set unidimensional de date agregate (în acest caz, sume):

Țară	SUM (Preț extins)
Argentina	7327.3
Austria	110788.4
Belgia	28491.65
Brazilia	97407.74
Canada	46190.1
Danemarca	28392.32
Finlanda	15296.35
Franţa	69185.48
	209373.6
	...

Dacă dorim să cunoaștem costul total al comenzilor plasate de clienți din diferite țări și livrate de diferite servicii de livrare, trebuie să rulăm o interogare care să conțină doi parametri în clauza GROUP BY:

SELECTAȚI Țara, Numele Expeditorului, SUMA (Preț extins) FROM Facturi

GRUP DUPĂ ȚĂRĂ, ShipperName

Pe baza rezultatelor acestei interogări, puteți crea un tabel care arată astfel:

Acest set de date se numește tabel pivot.

SELECTAȚI Țara, Numele Expeditorului, SUMA (ExtendedPrice) FROM Facturi

GRUP DUPĂ ȚĂRĂ, Nume expeditor, An

Pe baza rezultatelor acestei interogări, se poate construi un cub tridimensional (Fig. 1).

Adăugând Opțiuni suplimentare pentru analiză, puteți crea un cub cu orice număr de dimensiuni teoretic, iar împreună cu sumele, celulele cubului OLAP pot conține rezultatele calculării altor funcții agregate (de exemplu, valori medii, maxime, minime, numărul de înregistrări ale vederii originale, corespunzătoare acest set parametrii). Câmpurile din care se calculează rezultatele se numesc măsuri cub.

Ierarhii în dimensiuni

Să presupunem că ne interesează nu numai valoarea totală a comenzilor plasate de clienți în tari diferite, dar și costul total al comenzilor efectuate de clienți din diferite orașe ale aceleiași țări. În acest caz, puteți profita de faptul că valorile reprezentate pe axe au niveluri diferite de detaliu - acest lucru este descris în conceptul de ierarhie a modificărilor. Să spunem că țările sunt situate la primul nivel al ierarhiei, orașele sunt la al doilea. Rețineți că, începând cu SQL Server 2000, serviciile de analiză acceptă așa-numitele ierarhii dezechilibrate, care conțin, de exemplu, membri ai căror „copii” nu sunt incluși la nivelurile adiacente ale ierarhiei sau lipsesc pentru unii membri ai modificării. Un exemplu tipic de astfel de ierarhie este luarea în considerare a faptului că în diferite țări pot exista sau nu unități administrativ-teritoriale precum un stat sau o regiune, situate în ierarhia geografică dintre țări și orașe (Fig. 2).

Rețineți că recent a fost obișnuit să se distingă ierarhiile tipice, de exemplu cele care conțin date geografice sau temporale și, de asemenea, să se susțină existența mai multor ierarhii într-o singură dimensiune (în special, pentru anul calendaristic și fiscal).

Crearea de cuburi OLAP în SQL Server 2005

Cuburile SQL Server 2005 sunt create cu folosind SQL Server Business Intelligence Development Studio. Acest instrument este o versiune specială Studio vizual 2005, conceput pentru a rezolva din această clasă sarcini (și dacă aveți deja un mediu de dezvoltare instalat, lista de șabloane de proiect este completată cu proiecte concepute pentru a crea soluții bazate pe SQL Server și serviciile sale analitice). În special, șablonul Proiect de servicii de analiză este conceput pentru a crea soluții bazate pe servicii analitice (Fig. 3).

Pentru a crea un cub OLAP, mai întâi trebuie să decideți ce date să îl formați. Cel mai adesea, cuburile OLAP sunt construite pe baza unor depozite de date relaționale cu scheme stea sau fulgi de nea (am vorbit despre ele în partea anterioară a articolului). Pachetul SQL include un exemplu de astfel de stocare: baza de date AdventureWorksDW, pentru a o folosi ca sursă, ar trebui să găsiți folderul Surse de date în Solution Explorer, selectați meniul contextual Nouă sursă de date și răspunde în mod constant la întrebările vrăjitorului corespunzător (Fig. 4).

Apoi se recomandă crearea unei Vizualizări sursei de date pe care va fi creat cubul. Pentru a face acest lucru, trebuie să selectați elementul corespunzător din meniul contextual din folderul Vizualizări sursei de date și să răspundeți în mod constant la întrebările expertului. Rezultatul acestor acțiuni va fi o schemă de date, cu ajutorul căreia se va construi o reprezentare a surselor de date, iar în schema rezultată, în locul celor originale, puteți specifica nume de tabele „prietenoase” (Fig. 5) .

Cubul descris în acest fel poate fi transferat pe serverul de servicii analitice selectând opțiunea Deploy din meniul contextual al proiectului și vizualizând datele acestuia (Fig. 7).

În prezent, multe caracteristici sunt utilizate la crearea cuburilor versiune noua SQL Server, cum ar fi vizualizarea sursei de date. Descrierea datelor sursă pentru construirea unui cub, precum și descrierea structurii cubului, se face acum folosind instrumentul Visual Studio familiar multor dezvoltatori, ceea ce reprezintă un avantaj semnificativ al noii versiuni a acestui produs - studiul noilor instrumente de către dezvoltatorii de soluții analitice în acest caz este minimizat.

Rețineți că în cubul creat puteți modifica compoziția măsurilor, puteți șterge și adăuga atribute de dimensiune și puteți adăuga atribute calculate ale membrilor dimensiunii pe baza atributelor existente (Fig. 8).

Orez. 8. Adăugați un atribut calculat

În plus, cuburile SQL Server 2005 pot grupa sau sorta automat membrii dimensiunii după valoarea atributelor, pot defini relații între atribute, pot implementa relații multi-la-mulți, pot determina valori cheie pentru afaceri și multe altele (aflați cum toți acești pași pot fi găsiți în Tutorialul SQL Server Analysis Services sistem de ajutor a acestui produs).

În părțile ulterioare ale acestei publicații, vom continua să explorăm serviciile analitice ale SQL Server 2005 și vom afla ce este nou în domeniul suportului pentru data mining.

Poate pentru unii, utilizarea tehnologiei OLAP (On-line Analytic Processing) la crearea rapoartelor va părea oarecum exotică, așa că utilizarea OLAP-CUBE pentru acestea nu este deloc una dintre cele mai importante cerințe în automatizarea bugetării și contabilității de gestiune.

De fapt, este foarte convenabil să folosiți un CUBE multidimensional atunci când lucrați cu raportarea managementului. Când dezvoltați formate de buget, puteți întâmpina problema formelor multivariate (puteți citi mai multe despre aceasta în Cartea 8, „Tehnologie pentru stabilirea bugetului într-o companie” și în cartea, „Configurarea și automatizarea contabilității de gestiune”).

Acest lucru se datorează faptului că managementul eficient al unei companii necesită din ce în ce mai detaliat raportarea managementului. Adică, sistemul folosește din ce în ce mai multe secțiuni analitice diferite (în sisteme de informare analiticele sunt definite de un set de cărți de referință).

Desigur, acest lucru duce la faptul că managerii doresc să primească raportări în toate secțiunile analitice care îi interesează. Aceasta înseamnă că rapoartele trebuie făcute pentru a „respira” cumva. Cu alte cuvinte, putem spune că în acest caz vorbim despre faptul că sensul aceluiași raport ar trebui să ofere informații în diferite aspecte analitice. Prin urmare, rapoartele statice nu se mai potrivesc multor manageri moderni. Au nevoie de dinamica pe care o poate oferi un CUBU multidimensional.

Astfel, tehnologia OLAP a devenit deja element obligatoriuîn sistemele informaţionale moderne şi avansate. Prin urmare, atunci când alegeți un produs software, trebuie să acordați atenție dacă acesta utilizează tehnologia OLAP.

Mai mult, trebuie să poți distinge CUBU-urile reale de cele imitate. O astfel de simulare este tabelele pivot în MS Excel. Da, acest instrument arată ca un CUBU, dar de fapt nu este unul, deoarece acestea sunt tabele statice, nu dinamice. În plus, au o implementare mult mai proastă a capacității de a construi rapoarte folosind elemente din directoare ierarhice.

Pentru a confirma relevanța utilizării CUBE la construirea rapoartelor de management, putem da un exemplu simplu cu un buget de vânzări. În exemplul luat în considerare, următoarele secțiuni analitice sunt relevante pentru companie: produse, sucursale și canale de vânzare. Dacă aceste trei analize sunt importante pentru companie, atunci bugetul de vânzări (sau raportul) poate fi afișat în mai multe versiuni.

Trebuie remarcat faptul că, dacă creați linii bugetare pe baza a trei secțiuni analitice (ca în exemplul luat în considerare), acest lucru vă permite să creați destul de complexe modele bugetareși creați rapoarte detaliate folosind CUBE.

De exemplu, un buget de vânzări poate fi compilat utilizând doar o singură analiză (director). Un exemplu de buget de vânzări construit pe baza unei analize „Produse” este prezentat la figura 1.

Orez. 1. Un exemplu de buget de vânzări construit pe baza unei „Produse” analitice în OLAP-CUBE

Același buget de vânzări poate fi compilat folosind două analitice (directoare). Un exemplu de buget de vânzări construit pe baza a două analize „Produse” și „Sucursale” este prezentat la Figura 2.

Orez. 2. Un exemplu de buget de vânzări construit pe baza a două analitice „Produse” și „Sucursale” în OLAP-CUBE al pachetului software INTEGRAL

Dacă este nevoie să construim mai mult rapoarte detaliate, atunci puteți întocmi același buget de vânzări folosind trei analitice (directoare). Un exemplu de buget de vânzări construit pe baza a trei analize „Produse”, „Sucursale” și „Canale de vânzări” este prezentat la Figura 3.

Orez. 3. Un exemplu de buget de vânzări construit pe baza a trei analize „Produse”, „Sucursale” și „Canale de vânzări” în OLAP-CUBE al pachetului software INTEGRAL

Trebuie amintit că CUBU-ul folosit pentru a genera rapoarte vă permite să afișați datele în secvențe diferite. Pe Figura 3 Bugetul de vânzări este mai întâi „extins” pe produs, apoi pe ramură și apoi pe canal de vânzări.

Aceleași date pot fi prezentate într-o secvență diferită. Pe Figura 4 același buget de vânzări este „extins” mai întâi pe produs, apoi pe canal de vânzări și apoi pe ramură.

Orez. 4. Un exemplu de buget de vânzări construit pe baza a trei analitice „Produse”, „Canale de distribuție” și „Sucursale” în OLAP-CUBE al pachetului software INTEGRAL

Pe Figura 5 același buget de vânzări este „desfășurat” mai întâi pe sucursale, apoi pe produse și apoi pe canale de vânzare.

Orez. 5. Un exemplu de buget de vânzări construit pe baza a trei analize „Sucursale”, „Produse” și „Canale de vânzări” în pachetul software OLAP-CUBE „INTEGRAL”

De fapt, acestea nu sunt toate opțiunile posibile pentru retragerea bugetului de vânzări.

În plus, trebuie să acordați atenție faptului că CUBE vă permite să lucrați cu structura ierarhica carti de referinta. În exemplele prezentate, directoarele ierarhice sunt „Produse” și „Canale de distribuție”.

Din punctul de vedere al utilizatorului, el este în acest exemplu primește mai multe rapoarte de management (vezi Orez. 1-5), iar din punctul de vedere al setărilor din produsul software, acesta este un raport. Pur și simplu folosind CUBE, îl puteți vizualiza în mai multe moduri.

Bineînțeles, în practică, un număr foarte mare de opțiuni pentru emiterea diferitelor rapoarte de management este posibil dacă articolele acestora se bazează pe unul sau mai mulți analiști. Și setul de analize în sine depinde de nevoile utilizatorilor de detalii. Adevărat, nu trebuie să uităm că, pe de o parte, cu cât analistul este mai mare, cu atât se pot construi rapoarte mai detaliate. Dar, pe de altă parte, asta înseamnă că modelul de bugetare financiară va fi mai complex. În orice caz, dacă există un KUB, compania va avea posibilitatea de a vizualiza raportările necesare în diferite versiuni, în conformitate cu secțiunile analitice de interes.

Este necesar să menționăm mai multe caracteristici ale OLAP-CUBE.

Într-un OLAP-CUBE ierarhic multidimensional există mai multe dimensiuni: tipul rândului, data, rândurile, directorul 1, directorul 2 și directorul 3 (vezi. Orez. 6). Desigur, raportul afișează atâtea butoane cu cărți de referință câte sunt în linia bugetară care conține suma maxima carti de referinta. Dacă nu există o singură carte de referință în nicio linie bugetară, atunci raportul nu va avea un singur buton cu cărți de referință.

Inițial, OLAP-CUBE este construit pe toate dimensiunile. În mod implicit, atunci când raportul este construit inițial, dimensiunile sunt situate exact în zonele afișate în Figura 6. Adică, o dimensiune precum „Data” este situată în zona dimensiunilor verticale (dimensiunile din zona coloanei), dimensiunile „Rânduri”, „Directorul 1”, „Directorul 2” și „Directorul 3” - în zona de dimensiuni orizontale (dimensiuni în rândurile zonei), iar dimensiunea „Tip de rând” este în zona dimensiunilor „neexpandate” (dimensiuni în zona paginii). Dacă o dimensiune se află în ultima zonă, atunci datele din raport nu se vor „extinde” pe acea dimensiune.

Fiecare dintre aceste dimensiuni poate fi plasată în oricare dintre cele trei zone. După transferul măsurătorilor, raportul este reconstruit instantaneu conform configurație nouă măsurători. De exemplu, puteți schimba data și rândurile cu cărți de referință. Sau puteți muta una dintre cărțile de referință în zona verticală de măsurare (vezi. Orez. 7). Cu alte cuvinte, puteți „răsuci” raportul în OLAP-CUBE și puteți selecta opțiunea de ieșire a raportului care este cea mai convenabilă pentru utilizator.

Orez. 7. Un exemplu de reconstruire a unui raport după modificarea configurației de măsurare a pachetului software INTEGRAL

Configurația măsurătorilor poate fi modificată fie în formularul principal CUBE, fie în editorul de modificare a hărții (vezi. Orez. 8). În acest editor, puteți, de asemenea, să glisați și să plasați măsurătorile dintr-o zonă în alta cu mouse-ul. În plus, puteți schimba măsurătorile într-o zonă.

În plus, în aceeași formă puteți configura niște parametri de măsurare. Pentru fiecare dimensiune, puteți personaliza locația totalurilor, ordinea de sortare a elementelor și numele elementelor (vezi. Orez. 8). De asemenea, puteți specifica ce nume de element să fie afișat în raport: prescurtat (Nume) sau complet (Nume complet).

Orez. 8. Editor de hartă de măsurători a pachetului software INTEGRAL

Puteți edita parametrii de măsurare direct în fiecare dintre aceștia (vezi. Orez. 9). Pentru a face acest lucru, faceți clic pe pictograma situată pe butonul de lângă numele măsurătorii.

Orez. 9. Exemplu de director de editare 1 Produse și servicii în

Folosind acest editor, puteți selecta elementele pe care doriți să le afișați în raport. În mod implicit, toate elementele sunt afișate în raport, dar dacă este necesar, unele elemente sau foldere pot fi omise. De exemplu, dacă trebuie să afișați un singur grup de produse în raport, atunci trebuie să debifați toate celelalte din editorul de măsurători. După aceea, raportul va conține un singur grup de produse (vezi. Orez. 10).

De asemenea, puteți sorta elemente în acest editor. În plus, elementele pot fi rearanjate căi diferite. După o astfel de regrupare, raportul este reconstruit instantaneu.

Orez. 10. Exemplu de ieșire într-un raport pentru un singur grup de produse (dosar) în pachetul software INTEGRAL

În editorul de dimensiuni, puteți să vă creați rapid propriile grupuri, să trageți și să plasați elemente din directoare de acolo etc. În mod implicit, numai grupul Alte este creat automat, dar pot fi create alte grupuri. Astfel, folosind editorul de dimensiuni, puteți configura ce elemente ale cărților de referință și în ce ordine trebuie afișate în raport.

Trebuie remarcat faptul că toate astfel de rearanjamente nu sunt înregistrate. Adică după închiderea raportului sau după recalcularea acestuia, toate directoarele vor fi afișate în raport în conformitate cu metodologia configurată.

De fapt, toate astfel de modificări ar fi putut fi făcute inițial la configurarea liniilor.

De exemplu, folosind restricții, puteți specifica și ce elemente sau grupuri de directoare ar trebui să fie afișate în raport și care nu.

Notă: subiectul acestui articol este discutat mai detaliat la ateliere „Gestionarea bugetului unei întreprinderi”Și „Organizarea și automatizarea contabilității de gestiune” condus de autorul acestui articol, Alexander Karpov.

Dacă utilizatorul aproape în mod regulat trebuie să afișeze doar anumite elemente sau foldere de director în raport, atunci este mai bine să facă astfel de setări în avans atunci când creează linii de raport. Dacă diferite combinații de elemente de director din rapoarte sunt importante pentru utilizator, atunci nu este nevoie să setați nicio restricție la configurarea metodologiei. Toate astfel de restricții pot fi configurate rapid folosind editorul de măsurători.

Adnotare: Această prelegere acoperă elementele de bază ale proiectării cuburilor de date pentru depozitele de date OLAP. Exemplul arată metoda de construire a unui cub de date folosind instrumentul CASE.

Scopul prelegerii

După ce ați studiat materialul din această prelegere, veți ști:

în ce este un cub de date Depozitul de date OLAP ;
cum să proiectați un cub de date pentru Depozitele de date OLAP ;
ce este o dimensiune cub de date;
modul în care un fapt este legat de un cub de date;
care sunt atributele dimensiunii;
ce este ierarhia;
ce este o metrică cub de date;

si invata:

construi diagrame multidimensionale ;
design simplu diagrame multidimensionale.

Introducere

Tehnologia OLAP nu este una singură software, Nu limbaj de programare. Dacă încercăm să acoperim OLAP în toate manifestările sale, atunci este un set de concepte, principii și cerințe care stau la baza produse software, facilitând accesul analiștilor la date.

Analiștii sunt principalii consumatori de informații corporative. Sarcina analistului este să găsească tipare în cantități mari de date. Prin urmare, analistul nu va acorda atenție faptului individual că într-o anumită zi un lot de pixuri a fost vândut cumpărătorului Ivanov - are nevoie de informații despre sute și mii de evenimente similare. Faptele singulare din depozitul de date pot fi de interes, de exemplu, pentru un contabil sau șeful departamentului de vânzări, a cărui competență este susținerea unui anumit contract. Pentru un analist, o înregistrare nu este suficientă - el, de exemplu, poate avea nevoie de informații despre toate contractele unui punct de vânzare pe o lună, un trimestru sau un an. Este posibil ca analistul să nu fie interesat de TIN-ul cumpărătorului sau de numărul său de telefon - lucrează cu date numerice specifice, care reprezintă esența activității sale profesionale.

Centralizarea și structurarea convenabilă nu sunt tot ceea ce are nevoie un analist. Are nevoie de un instrument pentru a vizualiza și vizualiza informațiile. Rapoartelor tradiționale, chiar și cele construite pe baza unui singur depozit de date, le lipsește, însă, o anumită flexibilitate. Ele nu pot fi „răsucite”, „extinse” sau „restrânse” pentru a obține vizualizarea dorită a datelor. Cu cât un analist poate explora mai multe „slices” și „secțiuni” de date, cu atât mai multe idei are, care, la rândul lor, necesită din ce în ce mai multe „slices” pentru verificare. OLAP servește ca un astfel de instrument pentru analiza datelor de către un analist.

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

Datele operaționale sunt colectate din diverse surse, curățate, integrate și stocate într-un depozit de date. În plus, acestea sunt deja disponibile pentru analiză folosind diverse instrumente de raportare. Apoi datele (în întregime 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-o bază de date relațională. Cel mai important element al utilizării OLAP sunt metadatele, adică informații despre structură, plasare și transformarea datelor. Datorită acestora, este asigurată interacțiunea eficientă a diferitelor componente de stocare.

Prin urmare, OLAP poate fi definit ca un set de instrumente pentru analiza multidimensională a datelor acumulate într-un depozit de date. Teoretic, instrumentele OLAP pot fi aplicate direct datelor operaționale sau acestora copii exacte. Cu toate acestea, există riscul de a supune datele unei analize care nu sunt potrivite pentru această analiză.

OLAP pe client și server

OLAP se bazează pe analiza multidimensională a datelor. Poate fi produs folosind diverse instrumente, care pot fi împărțite în instrumente OLAP pentru client și server.

Instrumentele client OLAP sunt aplicații care calculează date agregate (sume, medii, valori maxime sau minime) și le afișează, în timp ce datele agregate în sine sunt conținute într-un cache în spațiul de adrese al unui astfel de instrument OLAP.

Dacă datele sursă sunt conținute într-un SGBD desktop, calculul datelor agregate este efectuat de instrumentul OLAP însuși. Dacă sursa datelor inițiale este un SGBD de server, multe dintre instrumentele OLAP client trimit interogări SQL care conțin operatorul GROUP BY către server și, ca rezultat, primesc date agregate calculate pe server.

De regulă, funcționalitatea OLAP este implementată în instrumente prelucrare statistică date (de la produsele din această clasă în continuare piata ruseasca produsele de la Stat Soft și SPSS sunt utilizate pe scară largă) și în unele foi de calcul. În special, Microsoft Excel 2000 are instrumente bune de analiză multidimensională. Folosind acest produs, puteți crea și salva ca fișier un mic cub OLAP multidimensional local și puteți afișa secțiunile sale bidimensionale sau tridimensionale.

Mulți instrumente de dezvoltare conțin biblioteci de clase sau componente care vă permit să creați aplicații care implementează funcționalitate OLAP simplă (cum ar fi, de exemplu, componentele Decision Cube în Borland Delphi și Borland C++Builder). În plus, multe companii oferă controale ActiveX și alte biblioteci care implementează funcționalități similare.

Rețineți că instrumentele OLAP client sunt utilizate, de regulă, cu un număr mic de dimensiuni (de obicei nu sunt recomandate mai mult de șase) și o varietate mică de valori pentru acești parametri - la urma urmei, datele agregate rezultate trebuie să se încadreze în spațiul de adrese al unui astfel de instrument, iar numărul acestora crește exponențial pe măsură ce numărul crește măsurătorile Prin urmare, chiar și cele mai primitive instrumente OLAP client, de regulă, vă permit să efectuați un calcul preliminar al volumului necesar memorie cu acces aleator pentru a crea un cub multidimensional în el.

Multe (dar nu toate) instrumente client OLAP vă permit să salvați conținutul cache-ului cu date agregate ca fișier, ceea ce, la rândul său, vă permite să evitați recalcularea acestora. Rețineți că această oportunitate este adesea folosită pentru a înstrăina datele agregate în scopul transferării acestora către alte organizații sau pentru publicare. Un exemplu tipic de astfel de date agregate alienabile este statistica morbidității în diferite regiuni și în diferite grupuri de vârstă, ceea ce este informații deschise, publicat de ministerele sănătăţii din diverse ţări şi de Organizaţia Mondială a Sănătăţii. În același timp, datele originale în sine, care reprezintă informații despre cazuri specifice de boli, sunt date confidențiale de la instituțiile medicale și în niciun caz nu ar trebui să cadă în mâinile companiilor de asigurări, cu atât mai puțin să devină publice.

Ideea salvării unui cache cu date agregate într-un fișier a fost dezvoltată în continuare în instrumentele OLAP de server, în care datele agregate sunt salvate și modificate, precum și suport pentru stocarea care le conține. cerere separată sau un proces numit server OLAP. Aplicațiile client pot solicita o astfel de stocare multidimensională și pot primi anumite date ca răspuns. De asemenea, unele aplicații client pot crea astfel de magazine sau le pot actualiza pe baza datelor sursă modificate.

Avantajele utilizării instrumentelor OLAP server în comparație cu instrumentele OLAP client sunt similare cu avantajele utilizării SGBD-urilor server față de cele desktop: în cazul utilizării instrumentelor server, calculul și stocarea datelor agregate are loc pe server, iar aplicația client. primește doar rezultatele interogărilor împotriva acestora, ceea ce permite, în general, reducerea traficului de rețea, perioada de graţie cererile și cerințele de resurse consumate de aplicația client. Rețineți că instrumentele de analiză și procesare a datelor la nivel de întreprindere, de regulă, se bazează pe instrumente OLAP de server, de exemplu, Oracle Express Server, Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, produse de la Crystal Decisions, Business Objects, Cognos, SAS Institut. Deoarece toți producătorii de top de SGBD-uri pentru servere produc (sau au licențiat de la alte companii) unul sau altul instrumente OLAP de server, alegerea este destul de largă și, în aproape toate cazurile, puteți achiziționa un server OLAP de la același producător ca și serverul de baze de date în sine. .

Rețineți că multe instrumente client OLAP (în special, Microsoft Excel 2003, Seagate Analysis, etc.) vă permit să accesați depozitele OLAP de server, acționând în acest caz ca aplicații client care efectuează astfel de interogări. În plus, există multe produse care sunt aplicații client pentru instrumente OLAP de la diverși producători.

Aspecte tehnice ale stocării datelor multidimensionale

Depozitele de date multidimensionale conțin date agregate cu diferite grade de detaliu, de exemplu, volumele vânzărilor pe zi, lună, an, pe categorie de produse etc. Scopul stocării datelor agregate este reducerea perioada de graţie solicită, întrucât în cele mai multe cazuri, pentru analize și prognoze, nu sunt date detaliate, ci sintetizate care prezintă interes. Prin urmare, atunci când se creează o bază de date multidimensională, unele date agregate sunt întotdeauna calculate și stocate.

Rețineți că salvarea tuturor datelor agregate nu este întotdeauna justificată. Cert este că atunci când se adaugă noi dimensiuni, volumul de date care alcătuiește cubul crește exponențial (uneori se vorbește despre „creștere explozivă” a volumului de date). Mai precis, gradul de creștere a volumului de date agregate depinde de numărul de dimensiuni ale cubului și de membrii dimensiunilor la diferite niveluri ale ierarhiilor acestor dimensiuni. Pentru a rezolva problema „creșterii explozive”, sunt utilizate diverse scheme, care fac posibilă atingerea unei viteze acceptabile de execuție a interogărilor atunci când se calculează nu toate datele agregate posibile.

Atât datele brute, cât și cele agregate pot fi stocate în structuri relaționale sau multidimensionale. Prin urmare, în prezent sunt utilizate trei metode de stocare a datelor.

MOLAP(OLAP multidimensional) - datele sursă și agregate sunt stocate într-o bază de date multidimensională. Stocarea datelor în structuri multidimensionale vă permite să manipulați datele ca matrice multidimensională, datorită căruia viteza de calcul a valorilor agregate este aceeași pentru oricare dintre dimensiuni. Cu toate acestea, în acest caz, baza de date multidimensională este redundantă, deoarece datele multidimensionale conțin în întregime datele relaționale originale.
ROLAP(OLAP relațional) - datele originale rămân în aceeași bază de date relațională în care au fost localizate inițial. Datele agregate sunt plasate în tabele de servicii special create pentru stocarea lor în aceeași bază de date.
HOLAP(OLAP hibrid) - datele originale rămân în aceeași bază de date relațională în care au fost localizate inițial, iar datele agregate sunt stocate într-o bază de date multidimensională.

Unele instrumente OLAP acceptă stocarea datelor doar în structuri relaționale, altele doar în cele multidimensionale. Cu toate acestea, majoritatea instrumentelor moderne OLAP de server acceptă toate cele trei metode de stocare a datelor. Alegerea metodei de stocare depinde de volumul și structura datelor sursă, de cerințele pentru viteza de execuție a interogărilor și de frecvența actualizării cuburilor OLAP.

Rețineți, de asemenea, că marea majoritate a instrumentelor OLAP moderne nu stochează valori „vide” (un exemplu de valoare „vide” ar fi absența vânzărilor unui produs sezonier în afara sezonului).

Concepte de bază OLAP

Testul FAMSI

Tehnologia pentru analiza complexă a datelor multidimensionale se numește OLAP (On-Line Analytical Processing). OLAP este o componentă cheie a unei organizații de depozit de date. Conceptul de OLAP a fost descris în 1993 de Edgar Codd, un faimos cercetător de baze de date și autor al modelului de date relaționale. În 1995, pe baza cerințelor stabilite de Codd, așa-numitul Testul FASMI(Fast Analysis of Shared Multidimensional Information) - analiza rapidă a informațiilor multidimensionale partajate, inclusiv următoarele cerințe pentru aplicațiile de analiză multidimensională:

Rapid(Rapid) - furnizarea utilizatorului de rezultate de analiză într-un timp acceptabil (de obicei nu mai mult de 5 s), chiar și cu prețul unei analize mai puțin detaliate;
Analiză(Analiză) - capacitatea de a implementa orice logic și analize statistice, caracteristic de aceasta aplicație, și salvarea acestuia într-o formă accesibilă utilizatorului final;
Impartit(Partajat) - acces multi-utilizator la date cu suport pentru mecanisme de blocare adecvate si mijloace de acces autorizate;
Multidimensional(Multidimensional) - reprezentare conceptuală multidimensională a datelor, inclusiv suport complet pentru ierarhii și ierarhii multiple (aceasta este o cerință cheie a OLAP);
informație(Informații) - aplicația trebuie să poată accesa orice informație necesară, indiferent de volumul și locația de stocare.

Trebuie remarcat faptul că funcționalitatea OLAP poate fi implementată în diverse moduri, de la cele mai simple instrumente de analiză a datelor din aplicații de birou până la distribuite. sisteme analitice bazate pe produse server.

Reprezentarea multidimensională a informațiilor

cuburi

OLAP oferă mijloace convenabile și rapide de accesare, vizualizare și analiză a informațiilor de afaceri. Utilizatorul primește un aspect natural, intuitiv model de date, 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 ar putea fi produs, regiune, tip de cumpărător. Timpul este folosit ca una dintre dimensiuni. La intersecțiile axelor de măsurare (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 stocuri, costuri etc. Un utilizator care analizează informații poate „taia” cubul în diferite direcții, poate obține rezumat (de exemplu, pe an) sau, dimpotrivă, detaliat (pe săptămână). ) informează și efectuează alte manipulări care îi vin în minte în timpul procesului de analiză.

Ca măsurători în cubul tridimensional prezentat în Fig. 26.1, se folosesc sumele vânzărilor, iar timpul, produsul și magazinul sunt folosite ca dimensiuni. Măsurătorile sunt prezentate la niveluri specifice de grupare: produsele sunt grupate pe categorii, magazinele în funcție de țară și datele privind momentul tranzacțiilor pe lună. Puțin mai târziu ne vom uita la nivelurile de grupare (ierarhie) mai detaliat.

Orez. 26.1.

„Tăierea” unui cub

Chiar și un cub tridimensional este dificil de afișat pe ecranul unui computer, 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 bidimensionale familiare, adică vederi de tabel cu titluri ierarhice complexe de rânduri și coloane.

O reprezentare bidimensională a unui cub poate fi obținută prin „tăierea” în cruce de-a lungul uneia sau mai multor axe (dimensiuni): fixăm valorile tuturor dimensiunilor, cu excepția a două, și obținem un tabel bidimensional obișnuit. Axa orizontală a tabelului (anteturile coloanelor) reprezintă o dimensiune, axa verticală (antetele rândurilor) reprezintă o alta, iar celulele tabelului reprezintă valorile măsurilor. În acest caz, un set de măsuri este de fapt considerat unul dintre dimensiuni: fie selectăm o măsură de afișat (și apoi putem plasa două dimensiuni în titlurile rândurilor și coloanelor), fie arătăm mai multe măsuri (și apoi una dintre axele tabelului vor fi ocupate de numele măsurilor, iar celălalt de valori ale singurei dimensiuni „netăiate”).

(niveluri). De exemplu, etichetele prezentate în nu sunt acceptate de toate instrumentele OLAP. De exemplu, Microsoft Analysis Services 2000 acceptă ambele tipuri de ierarhie, dar Microsoft OLAP Services 7.0 acceptă numai pe cele echilibrate. Numărul de niveluri ierarhice, numărul maxim permis de membri ai unui nivel și numărul maxim posibil de dimensiuni în sine pot fi diferite în diferite instrumente OLAP.

Arhitectura aplicațiilor OLAP

Tot ce s-a spus mai sus despre OLAP ține în esență de prezentarea multidimensională a datelor. Modul în care sunt stocate datele, în linii mari, 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.

Reprezentarea datelor multidimensionale - instrumente pentru utilizatorul final care oferă vizualizare și manipulare multidimensională a datelor; Stratul de reprezentare multidimensională face abstracție din structura fizică a datelor și tratează datele ca fiind multidimensionale.
Procesare multidimensională - un instrument (limbaj) pentru formularea de interogări multidimensionale (relaționale tradiționale Limbajul SQL se dovedește a fi nepotrivit aici) și un procesor capabil să prelucreze și să execute o astfel de cerere.
Stocarea multidimensională este un mijloc de organizare fizică a datelor care asigură executarea eficientă a interogărilor multidimensionale.

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

Produsele OLAP specifice, de regulă, sunt fie un instrument de prezentare a datelor multidimensionale (client OLAP - de exemplu, Pivot Tables în Excel 2000 de la Microsoft sau ProClarity de la Knosys), fie un server DBMS multidimensional (server OLAP - de exemplu, Oracle Express Server sau Servicii Microsoft OLAP).

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