Eroare la crearea unui fișier cub din Excel. Crearea unui cub OLAP folosind Microsoft Query

Orez. 2. OLAP cub felie

Rotație(Figura 3) - operația de schimbare a locației măsurătorilor prezentate într-un 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, rotirea unui cub de date mută dimensiunile din afara tabelului la locul lor cu dimensiunile prezente pe pagina afișată și invers.

cuburi date OLAP(Procesare analitică online - analiza datelor operaționale) 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 baze 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 o bază de date funcțională și cub OLAP, pe care intenționați să îl 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 masă rotativă ș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.

Limitări ale tabelelor 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 offline OLAP(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.

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 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 versiunile anterioare de Excel, tabelele pivot OLAP 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 crearea unui nou element, 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. Datorită acestui lucru setări Excel accesează toate elementele 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 meniul contextual selectați elementul (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

Al treilea articol este dedicat procesării unor cantități mari de date cu folosind Excel, descrie beneficiile utilizării tabelelor pivot. În general, acest articol ar fi trebuit să fie primul din serie, dacă vorbim despre beneficiile acestei sau acelei metode de lucru. Într-adevăr, interfața Pivot Table a fost creată special pentru a analiza cantități mari de date care pot fi stocate nu numai în intervale de foi de calcul, ci și în surse de date externe. Înțelegerea principiilor de funcționare și utilizarea practică a tabelelor pivot poate optimiza semnificativ munca de zi cu zi a economiștilor. Creșterea nivelului de analiză a datelor duce, la rândul său, la îmbunătățirea managementului companiei și la adoptarea unor decizii corecte de management de către managerii de la diferite niveluri.

Problemele teoretice generale privind lucrul cu tabele pivot și analiza datelor multidimensionale sunt descrise într-un alt articol de pe site-ul nostru.

Aici ne vom opri mai detaliat asupra metodelor specifice de prelucrare a datelor folosind interfața tabelului pivot. Folosiți fișierul ca exemplu nwdata_pivot.xls.

Utilizarea tabelelor pivot

Eșantionarea valorilor unice

Una dintre cele mai populare sarcini rezolvate folosind un tabel pivot este selectarea valorilor unice dintr-o listă sau o matrice de date. Utilizarea interfeței PivotTable vă permite să rezolvați această problemă în cel mai „elegant” mod - fără a utiliza formule.

În exemplul de pe foaie Probă afișează o listă de țări și numărul de mențiuni din matricea de date.

Câmpul de date trebuie să conțină tipul operațiunii – „cantitate”. Această opțiune vă permite să procesați câmpuri de date sursă non-numerice în zona de date PivotTable. O alternativă la operația de numărare este funcția standard COUNTIF. Creați un set valori unice numai utilizarea formulelor este posibilă în principiu (a se vedea partea 1), dar acest lucru va necesita formule foarte complexe cu adresare calculată. Adică, utilizarea unui tabel pivot în această sarcină este cea mai optimă modalitate de a o rezolva.

Suma valorilor

O altă sarcină populară pentru utilizarea interfeței Pivot Table este obținerea totalurilor pentru înregistrările unice dintr-un set de date.

În exemplul de pe foaie Sumă au fost generate date rezumative privind comenzile pentru fiecare țară:

Tipul de operație „Suma” în câmpul de date permite doar câmpuri numerice. Alte tipuri de agregare a datelor sursă nu sunt aproape niciodată utilizate în practică.

Pentru a rezolva o problemă folosind formule standard, puteți utiliza funcția SUMIF. Evident, dificultatea nu apare în consolidarea valorilor, ci, ca în exemplul precedent, în selectarea unei liste unice (în exemplu, numele țărilor).

Analiza 2D

Exemplele descrise mai devreme demonstrează analiza datelor bazată pe un criteriu. Foile de calcul vă permit să vizualizați datele în două dimensiuni: coloane și rânduri. Tabelele pivot au și aceste zone de afișare a datelor.

În exemplul de pe foaie Masa a fost generat un raport pe țară și dată, care arată modificările numărului de comenzi de-a lungul timpului. Vă rugăm să rețineți că pentru câmpul tip dată se aplică o grupare suplimentară: pe lună și pe an.

Însumarea după mai multe criterii este posibilă și prin funcțiile standard Excel SUMIFS, SUMPRODUCT, precum și prin funcțiile de procesare a matricei (vezi partea 1). Cu toate acestea, această opțiune necesită valori cunoscute anterior ale parametrilor - tastele de selecție. În plus, calculele care utilizează formule necesită mult mai mult timp, ceea ce poate duce la pierderi mari de productivitate pentru volume mari de date.

Analiza multivariată

Pe lângă analiza vizuală a zonei pe rânduri și coloane, în tabelele pivot puteți utiliza un filtru global după unul sau mai multe câmpuri ale datelor sursă. În acest scop este destinat zonă specială celulele situate deasupra tabelului pivot.

Exemplu pe o foaie Filtru demonstrează capacitatea de a vizualiza date pentru companii dintr-o țară folosind zona de filtrare a unui tabel pivot:

Câmpul de filtrare poate fi mutat în zona de rânduri sau coloane, permițându-vă să vizualizați o gamă mai mare de informații. Pe lângă zona de filtrare descrisă, filtrarea suplimentară a datelor poate fi realizată prin configurarea listelor de câmpuri cheie în zonele de rând sau coloane.

Analogul utilizării filtrelor din tabel pivot folosind formule de foi de lucru este, în cele mai multe cazuri, formule de procesare a matricei.

Exemple pe foi pivot1Și pivot2 afișați opțiunile pentru afișarea acelorași informații folosind diferite setări de dimensiuni pentru tabelul pivot.

Lucrul cu date

Actualizare de date

Un tabel pivot se poate baza atât pe date situate într-o zonă arbitrară a celulelor, cât și pe surse de date externe. Să ne uităm mai întâi la prima opțiune. Acestea. datele pentru analiză sunt stocate într-o serie de celule de lucru Foaie Excel.

Un raport sub forma unui tabel pivot poate fi pregătit atât pentru utilizare unică, cât și pentru utilizare continuă cu un set variabil de date sursă. Ultima opțiune oferă utilizatorului oportunități mai mari de lucru interactiv: este necesar să configurați și să formatați formularul de raportare o dată, apoi la editarea datelor sursă, modificările în formularul final vor fi făcute automat. În acest caz, raportul nu numai că modifică datele, ci poate adăuga și șterge și rânduri și coloane, ceea ce este practic imposibil de implementat cu formulele foii de lucru.

Expertul PivotTable vă permite să specificați o serie de celule pe care să le utilizați ca sursă de date. Dacă s-au adăugat rânduri noi la actualizarea informațiilor, este posibil ca acestea să nu fie incluse în sursa de date din tabelul pivot și, în consecință, să nu fie analizate corect. Această caracteristică este destul de dificil de urmărit atunci când procesează cantități mari de date.

Puteți modifica intervalul sursei de date pentru un tabel pivot existent printr-un dialog special din Excel 2007-2010. În versiunile anterioare de Excel, această caracteristică de interfață a fost implementată în Expertul tabel pivot când a fost lansată dintr-un tabel pivot activ. După deschiderea expertului, trebuie să mergeți înapoi cu un pas:

De asemenea, pot fi făcute corecții ale surselor de date în mod programatic. De exemplu, prin fereastra de calcul a editorului VBA (imediat):

Pentru a nu vă gândi la corectitudinea dimensiunii intervalului sursă a datelor din tabelul pivot, puteți specifica inițial un interval de rânduri cu o marjă mare în timpul construcției. De exemplu, știind că volumul așteptat de rânduri nu depășește 10.000, puteți seta imediat această valoare ca dimensiune a intervalului. În practică, o astfel de redundanță nu va duce la încetiniri vizibile în funcționarea interfeței PivotTable. Puteți ascunde valorile goale în parametrii raportului. Dezavantajul acestei metode se manifestă, în primul rând, atunci când se lucrează cu câmpuri de tip „date”. Interfața standard tabel pivot vă permite să implementați diferite grupări atunci când lucrați cu tipul „date” (pe lună, pe trimestru), dar dacă este disponibil valori goale Aceste opțiuni nu mai sunt disponibile deoarece Excel definește coloana ca coloană text.

Pe lângă metodele luate în considerare de control al sursei de date, vă propunem să configurați intervalul de rânduri din tabelul pivot al foii de lucru active folosind metode programatice. Dacă sursa de date ocupă întreaga zonă de lucru a foii, atunci puteți utiliza următoarea comandă:

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData, "!")) & _ Range(Application.ConvertFormula(_ ActiveSheet.PivotTables(_) (1).SourceData, xlR1C1, xlA1) _).Worksheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

Cel mai de încredere, dar într-un mod lent, este o verificare secvențială a rândurilor foii sursă urmată de completarea proprietății Date sursă tabel pivot activ. Rețineți că această proprietate este stocată doar în adresarea R1C1.

Macro-urile pot fi apelate după eveniment Foaia de lucru_Activare, sau configurați o tastă rapidă.

Lucrul cu rezultatele analizei

Un tabel pivot este situat într-o serie de celule dintr-o foaie de lucru Excel. Scrierea de formule de foi de lucru în limitele unui tabel pivot nu este permisă, nici manual, nici folosind metode programatice. Teoretic, este posibil să lucrați cu celule situate în limitele unui tabel pivot folosind link-uri pentru formule externe. Adesea, în practică, funcția VLOOKUP este folosită și pentru a căuta o coloană de tabel pivot. Această metodă trebuie utilizată cu mare prudență - interfața raportului rezumat necesită schimbarea poziției datelor afișate în raport cu coordonate dreptunghiulare foaie de lucru fără niciun impact asupra sursei respectivelor date. Adică, nu există nicio garanție că legătura specificată în formula din interiorul tabelului pivot va afișa valoarea corectă când munca in continuare cu dosarul. Cu toate acestea, este posibil ca sursa de date să nu se modifice.

Disponibil moduri alternative procesarea rezultatelor tabelului pivot:

1. Copierea și inserarea valorilor Tabelului Pivot într-o altă foaie (folosind „ Inserție specială") cu căutare ulterioară pentru date deja în acest interval format de celule. Este mult mai dificil să încălcați integritatea datelor într-un tabel simplu decât într-un tabel rezumat. Evident, principalul dezavantaj al acestui mod de lucru este utilizarea operațiilor manuale după fiecare actualizare a sursei de date.
2. Utilizați funcția GETPIVOTDATA (Excel 2002 și versiuni ulterioare). Această funcție implică accesarea datelor nu prin coordonatele foii de lucru, ci după dimensiunile tabelului pivot. Pentru sursele de date de tip cub OLAP sunt prevăzute funcții speciale de accesare a datelor și dimensiunilor: CUBEVALUE, CUBEMEMBER și altele (Excel 2007-2010). Aceasta metoda este incomod și, de asemenea, încetinește semnificativ activitatea dacă trebuie să obțineți multe valori diferite într-un tabel pivot.
3. Renunțați la un tabel pivot pentru a obține rezultate. Utilizați în schimb formulele foii de lucru (vezi Partea 1). Această metodă, deși dificil de implementat, poate fi cea mai convenabilă dacă alte calcule se bazează pe rezultate și sursa de date este actualizată frecvent.

Versiuni de interfață tabel pivot

În noul format de fișier xlsx (Excel 2007-2010), capacitățile interfeței tabelului pivot au fost modificate semnificativ. ÎN Versiuni anterioare interfață (97-2003) au fost făcute doar modificări „cosmetice”:

• Excel 2000 (9.0) – versiunea de bază Interfață tabel pivot.
• Excel XP (10.0) - nouă funcție GETPIVOTDATE
• Excel 2003 (11.0) - Se pare că nu au fost făcute modificări
• Excel 2007 (12.0) – o nouă versiune Interfață tabel pivot cu suport pentru intervale extinse. Performanță îmbunătățită, schimbată aspect interfata. Compatibilitatea cu vechiul format este menținută.
• Excel 2010 (14.0) – Suport pentru suplimentul PowerPivot. Lucrul cu cuburi OLAP actualizate.

Modificări majore în noul format de fișier (2007-2010):

• O coloană poate conține mai multe câmpuri ale unui tabel pivot, evidențiate prin indentări (formă comprimată).
• felii Tabelele pivot vă permit să afișați vizual setul curent de valori de filtrare.
• Dimensiunile din zona de filtrare acceptă selecția multiplă.
• Elementele de dimensiune pot fi ascunse/afișate prin intermediul butoanelor situate în aceeași celulă cu antetul însuși.
• Există câțiva parametri noi în proprietățile câmpului și tabelului.
• Stilurile de tabel pivot sunt disponibile pentru a vă permite să schimbați în orice moment aspectul rapoartelor dvs.

Pentru a înțelege mai bine diferențele, descărcați și deschideți fișierele exemplu nwdata_pivot1.xlsxȘi nwdata_pivot2.xlsx(în arhivă nwdata_pivot.zip). Primul fișier prezintă raportul în vechiul format, al doilea - în cel nou, datele sursă sunt aceleași.

Organizarea internă a interfeței tabelului pivot

Pentru a înțelege mai bine principiile de funcționare a unui tabel pivot, să ne uităm la organizarea internă a interfeței.

Cache-ul tabelului pivot

Când creați sau actualizați un tabel pivot, indiferent de tipul de sursă selectat, Excel transferă datele într-un magazin intermediar numit cache-ul tabelului pivot. Structura de organizare a datelor în cache vă permite să optimizați în mod semnificativ agregarea datelor și calculele din tabelul pivot. Stocarea datelor în propriul cache vă permite să utilizați diferite surse de date, păstrând în același timp o funcționalitate similară.

Datele din cache sunt actualizate atunci când faceți clic pe butonul „Reîmprospătare” al interfeței tabelului pivot (un buton de pe panglică sau în meniul contextual), sau la un interval de timp specificat, dacă o astfel de setare este specificată în parametri. Modul calcule Excel(automat sau manual) nu afectează în niciun fel tabelul pivot.

Mai multe tabele pivot (sau diagrame) pot afișa date din aceeași memorie cache. Această opțiune de lucru este utilizată pentru a afișa mai multe forme de raportare ale acelorași date fără a utiliza interfața de configurare a măsurătorilor. În acest caz, atunci când unul dintre tabele este actualizat, cel bazat pe același cache este reconstruit automat.

Obiecte VBA

Accesul la date folosind metode programatice este posibil la nivelul obiectelor din tabel pivot - obiect Masă rotativă. Alte obiecte PivotTable sunt responsabile pentru aranjarea și afișarea vizuală a elementelor și datelor. Acestea includ colecții de câmpuri: PivotFields, ColumnFields, RowFields, PageFields, DataFields. Opțiunile pentru valoarea câmpului sunt disponibile prin colecțiile de obiecte PivotItems.

Capacitate universală de a accesa datele direct în cache (obiect PivotCache) din anumite motive nu este furnizat de dezvoltatorii Excel. Logica nu este complet clară. După cum sa menționat deja, datele din cache sunt stocate separat și pot fi chiar văzute într-un fișier xlsx dacă deschideți acest fișier ca arhivă zip. În funcție de tipul sursei de date, ați putea încerca să utilizați proprietatea Date sursă(pentru tabele pivot bazate pe intervale) sau Set de înregistrări(pentru surse de tip „interogare baza de date”).

Câmpuri calculate și obiecte PivotTable ( Câmpuri calculate, CalculatedItems) au propriul mecanism de calcul și arbore de dependență de formule, care nu este legat de formulele din foile de lucru Excel. În practică, recomandăm, dacă este posibil, evitarea unui număr mare de câmpuri calculate în tabelele pivot, deoarece aceasta duce la o încetinire semnificativă a calculelor. Pentru sursele de date sub formă de intervale de celule, de multe ori puteți adăuga pur și simplu o coloană cu o formulă obișnuită la datele sursă, iar pentru interogările bazei de date, puteți adăuga calcule direct în corpul interogării SQL.

Tipuri de surse de date

La nivel global, sursele de date pot fi împărțite în 3 tipuri:

1. Intervalele de celule
2. Interogări baze de date
3. Cuburile OLAP și PowerPivot2010 ca una dintre opțiunile de implementare a mecanismului OLAP.

Intervalele

Prima opțiune este cea mai comună în practică; Descrierile anterioare ale exemplelor se aplică în mod specific datelor stocate într-o serie de celule.

Interfața standard Excel nu vă permite să construiți un raport rezumat bazat pe mai multe intervale de celule. Motivul acestei limitări nu este foarte clar. Există o suspiciune că dezvoltatorii pur și simplu nu pot oferi o interfață de utilizator intuitivă pentru a rezolva această problemă. Implementarea tehnică a sarcinii nu pare prea complicată - trebuie doar să completați memoria cache a datelor. În capitolul Suplimente Site-ul nostru web prezintă propria noastră soluție pentru construirea de rapoarte rezumative complexe.

Interogări baze de date

Interogările bazelor de date pot fi implementate folosind diverse mecanisme tehnice: Microsoft Query, ADO, ODBC. Indiferent de interfața de acces la date, factorul unificator al acestei soluții este popularea cache-ului tabelului pivot direct dintr-o sursă externă. Când lucrați în continuare cu tabelul pivot, interogarea poate fi executată din nou, după care datele vor fi transferate din nou în cache. Această metodă vă permite să analizați datele din surse externe (sisteme contabile) în timp real. Dacă se pierde conexiunea cu sursa de date, analiza poate fi efectuată pe cele mai recente date din cache.

cuburi OLAP

Cubul OLAP oferă un nivel intermediar de pregătire a informațiilor pentru analiza multidimensională în tabele pivot. Cubul stochează informații despre tipurile de câmpuri disponibile (dimensiune sau date), dependențele de câmpuri ierarhice, valorile agregate (subtotaluri) și alte elemente calculate. Principalul avantaj al utilizării cuburilor față de interogările directe ale bazei de date este performanța ridicată, deoarece datele sunt mutate și agregate în stocarea intermediară. Dezavantajul este, de asemenea, evident aceasta metoda– Datele cubului OLAP pot conține informații învechite, care depind de setările de stocare.

Înainte de Office 2007, un simplu cub OLAP putea fi pregătit folosind Ajutor Microsoft Interogare, dar în ultimele versiuni Această funcție a fost dezactivată din motive necunoscute. Dezvoltatorii recomandă insistent utilizarea Serviciului de analiză SQL Server pentru a crea și configura cuburi OLAP. Recomandarea este utilă, dar, în primul rând, acest serviciu este inclus doar în versiunile plătite de SQL Server și, în al doilea rând, necesită un studiu serios atât al interfeței, cât și al limbajului pentru procesarea interogărilor MDX.

Exemplul pentru acest articol arată o arhivă nwdata_cube.zip cu două dosare nwdata_cube.cub, nwdata_cube.xls. Vă rugăm să rețineți modificările din interfața PivotTable atunci când utilizați un cub OLAP ca sursă de date:

• Prezența dimensiunilor ierarhice, nu există posibilitatea de a schimba părintele și element copil in unele locuri.
• Nu este permisă mutarea dimensiunilor în zona de date și invers.
• Subtotalurile sunt afișate pentru toate articolele, nu după filtrul de grup curent.

PowerPivot

Un program de completare PowerPivot special este disponibil pentru Excel 2010, care este, în general, un mecanism alternativ pentru implementarea cuburilor OLAP. Cu PowerPivot, puteți procesa milioane de înregistrări din diferite fișiere de informații și baze de date cu performanțe enorme. În același timp, interfața cu utilizatorul pentru analiza finală a datelor este implementată în Excel 2010.

Este foarte probabil ca acest program de completare să fie inclus în următoarea versiune de Excel ca o funcționalitate de bază. Sperăm cu adevărat să dedicăm un articol separat sau chiar o serie de articole pentru a descrie modul în care funcționează PowerPivot. Astăzi PowerPivot + Excel sunt probabil cele mai multe Unealtă puternică pentru analiza unor volume mari de date.

Site-ul web oficial PowerPivot.

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 în construirea raportării manageriale, putem cita cel mai simplu exemplu cu 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 punct de vedere al setărilor în produs 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.

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.

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 unei baze de date OLAP de la un server OLAP sau pot fi create 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 de sine stătătoare din Microsoft SQL Server Analysis Services se supune termenilor de instalare și licenței 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 OLAP offline, ș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