Excel olap cuburi versiune de laborator. OLAP-CUBE (raportare de management dinamic)

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

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

Termenul „OLAP” este indisolubil legat de termenul „depozit de date” (Data Warehouse).

Iată definiția formulată de „părintele fondator” al depozitării de date, Bill Inmon: „Un depozit de date este o colecție de date specifică domeniului, limitată în timp și imuabilă pentru a sprijini luarea deciziilor de management.”

Datele intră în stocare de la sisteme de operare(sisteme OLTP), care sunt concepute pentru a automatiza procesele de afaceri. În plus, depozitul poate fi completat din surse externe, cum ar fi rapoartele statistice.

De ce să construim depozite de date - până la urmă, acestea conțin informații redundante, evident, care deja „trăiește” în baze de date sau fișiere ale sistemului de operare? Răspunsul poate fi scurt: este imposibil sau foarte dificil să analizezi direct datele din sistemele de operare. Acest lucru se datorează diferitelor motive, inclusiv fragmentarea datelor, stocarea acestora în diferite formate DBMS și în diferite „colțuri” rețeaua corporativă. Dar chiar dacă o întreprindere își stochează toate datele pe un server central de baze de date (ceea ce este extrem de rar), un analist aproape sigur nu le va înțelege structurile complexe, uneori confuze. Autorul are o experiență destul de tristă de a încerca să „hrănească” analiștii înfometați cu date „brute” din sistemele operaționale - sa dovedit a fi „prea mult pentru ei”.

Astfel, scopul depozitului este de a furniza „materiile prime” pentru analiză într-un singur loc și într-o structură simplă, de înțeles. Ralph Kimball, în prefața cărții sale „The Data Warehouse Toolkit”, scrie că, dacă, după ce a citit întreaga carte, cititorul înțelege un singur lucru - și anume că structura depozitului ar trebui să fie simplă - autorul își va lua în considerare sarcina finalizată.

Mai există un motiv care justifică apariția unei stocări separate - interogările analitice complexe pentru informații operaționale încetinesc activitatea curentă a companiei, blocând tabelele pentru o lungă perioadă de timp și confiscând resursele serverului.

În opinia mea, un depozit nu înseamnă neapărat o acumulare gigantică de date - principalul lucru este că este convenabil pentru analiză. În general, există un termen separat pentru facilitățile de stocare mici - Data Marts (chioșcuri de date), dar în practica noastră rusă nu îl auzi des.

OLAP - un instrument de analiză convenabil

Centralizarea și structurarea convenabilă nu sunt tot ceea ce are nevoie un analist. Mai are nevoie de un instrument pentru vizualizarea și vizualizarea informațiilor. Rapoartelor tradiționale, chiar și cele construite pe un singur depozit, le lipsește un singur lucru - flexibilitatea. Ele nu pot fi „răsucite”, „extinse” sau „restrânse” pentru a obține vizualizarea dorită a datelor. Desigur, puteți suna un programator (dacă vrea să vină), iar el (dacă nu este ocupat) va face un nou raport suficient de repede - să zicem, într-o oră (scriu asta și nu cred eu însumi - nu se întâmplă atât de repede în viață; să-i dăm trei ore) . Se pare că un analist nu poate testa mai mult de două idei pe zi. Și lui (dacă el bun analist) pot veni în minte mai multe astfel de idei pe oră. Și cu cât analistul vede 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. Dacă ar avea un instrument care să-i permită să extindă și să restrângă datele simplu și convenabil! OLAP acționează ca un astfel de instrument.

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

Componentele incluse într-un depozit tipic sunt prezentate în Fig. 1.

Orez. 1. Structura depozitului de date

Datele operaționale sunt colectate din diverse surse, curățate, integrate și stocate într-un magazin relațional. În plus, acestea sunt deja disponibile pentru analiză diverse mijloace construirea rapoartelor. 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 stocate în stocare relațională. Cel mai important element al său sunt metadatele, adică informații despre structura, plasarea și transformarea datelor. Datorită acestora, este asigurată interacțiunea eficientă a diferitelor componente de stocare.

Pentru a rezuma, putem defini OLAP ca un set de instrumente pentru analiza multidimensională a datelor acumulate într-un depozit. Teoretic, instrumentele OLAP pot fi aplicate direct datelor operaționale sau acestora copii exacte(pentru a nu deranja utilizatorii operativi). Dar riscăm astfel să călcăm pe grebla deja descrisă mai sus, adică să începem să analizăm date operaționale care nu sunt direct potrivite pentru analiză.

Definiția și conceptele de bază ale OLAP

În primul rând, să descifrăm: OLAP este procesarea analitică online, adică analiza datelor operaționale. Cele 12 principii definitorii ale OLAP au fost formulate în 1993 de E. F. Codd, „inventatorul” bazelor de date relaționale. Mai târziu, definiția sa a fost reluată în așa-numitul test FASMI, care necesită ca aplicația OLAP să ofere capacitatea de a analiza rapid informațiile multidimensionale partajate ().

Testul FASMI

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

Analiză(Analiză) - trebuie să se poată efectua tipuri de bază de analiză numerică și statistică, predefinite de dezvoltatorul aplicației sau definite liber de utilizator.

Impartit(Partajat) - mulți utilizatori trebuie să aibă acces la date, în timp ce este necesar să se controleze accesul la informații confidențiale.

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

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

OLAP = Vedere multidimensională = Cub

OLAP oferă mijloace convenabile și rapide de accesare, vizualizare și analiză a informațiilor de afaceri. Utilizatorul primește un model de date natural, intuitiv, organizându-le sub formă de cuburi multidimensionale (Cuburi). Axele sistemului de coordonate multidimensionale sunt principalele atribute ale procesului de afaceri analizat. De exemplu, pentru vânzări ar putea fi produs, regiune, tip de cumpărător. Timpul este folosit ca una dintre dimensiuni. La intersecțiile axelor - dimensiuni (Dimensiuni) - există date care caracterizează cantitativ procesul - măsuri (Măsuri). Acestea pot fi volume de vânzări pe 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. 2, sunt utilizate 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. 2. Exemplu de cub

„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 vizualizări bidimensionale familiare, adică tabelare, cu titluri ierarhice complexe de rânduri și coloane.

O reprezentare bidimensională a unui cub poate fi obținută prin „tăierea” acestuia pe una sau mai multe axe (dimensiuni): fixăm valorile tuturor dimensiunilor, cu excepția a două, și obținem un tabel bidimensional 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 ca 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 celelalte - valori ale singurei dimensiuni „netăiate”).

Aruncă o privire la fig. 3 - aici este o felie bidimensională a cubului pentru o măsură - Vânzări unitare (bucăți vândute) și două dimensiuni „netăiate” - Magazin (Magazin) și Timp (Timp).

Orez. 3. 2D cub felie pentru o măsură

În fig. Figura 4 prezintă o singură dimensiune „netăiată” - Magazin, dar afișează valorile mai multor măsuri - Vânzări unitare (unități vândute), Vânzări în magazin (suma vânzării) și Cost magazin (cheltuieli magazin).

Orez. 4. 2D cub felie pentru mai multe măsuri

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

Orez. 5. Secțiune de cub 2D cu dimensiuni multiple pe o axă

Etichete

Valorile „așezate” de-a lungul dimensiunilor se numesc membri sau etichete. Etichetele sunt folosite atât pentru a „tăia” cubul, cât și pentru a limita (filtra) datele selectate - când într-o dimensiune care rămâne „netăiată” nu ne interesează toate valorile, ci un subset al acestora, de exemplu, trei orașe din câteva zeci. Valorile etichetelor apar în vizualizarea cubului 2D ca titluri de rând și coloană.

Ierarhii și niveluri

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

Țară

Stat

Oraș

Magazin.

Valorile agregate sunt calculate în funcție de nivelurile ierarhice, de exemplu volumul vânzărilor pentru SUA (nivelul „Țară”) sau pentru California (nivelul „stat”). Este posibil să implementați mai mult de o ierarhie într-o singură dimensiune - să zicem, pentru timp: (An, Trimestru, Lună, Zi) și (An, Săptămână, Zi).

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 reprezentare a datelor multidimensionale, un client OLAP (de exemplu, Pivot Tables în Excel 2000 de la Microsoft sau ProClarity de la Knosys), fie un server multidimensional DBMS, un server OLAP (de exemplu, Oracle Express Server sau Microsoft OLAP Services).

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

Aspecte tehnice ale stocării datelor multidimensionale

După cum sa menționat mai sus, instrumentele de analiză OLAP pot extrage date direct din sistemele relaționale. Această abordare era mai atractivă în acele vremuri când serverele OLAP nu erau incluse în listele de prețuri ale producătorilor de top DBMS. Dar astăzi, Oracle, Informix și Microsoft oferă servere OLAP cu drepturi depline și chiar și acei manageri IT cărora nu le place să creeze o „zoo” de software în rețelele lor. diferiți producători, poate cumpăra (mai exact, face o cerere corespunzătoare conducerii companiei) un server OLAP de aceeași marcă cu serverul principal de baze de date.

Serverele OLAP sau serverele de baze de date multidimensionale își pot stoca datele multidimensionale în moduri diferite. Înainte de a lua în considerare aceste metode, trebuie să vorbim despre un aspect atât de important ca depozitarea unităților. Cert este că în orice depozit de date - atât obișnuit, cât și multidimensional - alături de datele detaliate extrase din sistemele operaționale, sunt stocați și indicatori de sinteză (indicatori agregați, agregari), precum suma volumelor vânzărilor pe lună, pe categorii de mărfuri etc. . Agregatele sunt stocate în mod explicit cu scopul exclusiv de a accelera execuția interogărilor. La urma urmei, pe de o parte, de regulă, în depozit se acumulează o cantitate foarte mare de date, iar pe de altă parte, analiștii, în majoritatea cazurilor, nu sunt interesați de indicatori detaliați, ci generalizați. Și dacă ar trebui să se adună milioane de vânzări individuale de fiecare dată pentru a calcula vânzările totale pentru anul, viteza ar fi cel mai probabil inacceptabilă. Prin urmare, la încărcarea datelor într-o bază de date multidimensională, toți indicatorii totali sau o parte a acestora sunt calculați și stocați.

Dar, după cum știți, trebuie să plătiți pentru tot. Iar pentru viteza de procesare a cererilor de date rezumative, trebuie să plătiți pentru o creștere a volumelor de date și a timpului de încărcare a acestora. Mai mult, o creștere a volumului poate fi literalmente catastrofală - într-unul dintre testele standard publicate, un calcul complet al agregatelor pentru 10 MB de date sursă necesita 2,4 GB, adică datele au crescut de 240 de ori! Gradul de „umflare” a datelor la calcularea agregatelor depinde de numărul de dimensiuni ale cubului și de structura acestor dimensiuni, adică de raportul dintre numărul de „părți” și „copii” pe diferite niveluri măsurători. Pentru a rezolva problema depozitării unităților, acestea sunt uneori folosite circuite complexe, care fac posibilă obținerea unei creșteri semnificative a performanței interogărilor atunci când se calculează nu toate agregatele posibile.

Acum despre diferitele opțiuni pentru stocarea informațiilor. Atât datele granulare, cât și agregatele pot fi stocate în structuri relaționale sau multidimensionale. Stocarea multidimensională vă permite să tratați datele ca o matrice multidimensională, ceea ce asigură calcule la fel de rapide ale indicatorilor totali și diverse transformări multidimensionale de-a lungul oricărei dimensiuni. Cu ceva timp în urmă, produsele OLAP acceptau stocarea fie relațională, fie multidimensională. Astăzi, de regulă, același produs oferă ambele tipuri de depozitare, precum și un al treilea tip - mixt. Se aplică următorii termeni:

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

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

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

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

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 informație corporativă. 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 copiilor exacte ale acestora. 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 instrumentele de prelucrare a datelor statistice (de la produse din această clasă la piata ruseasca produsele de la Stat Soft și SPSS sunt utilizate pe scară largă) și în unele foi de calcul. În special, are instrumente bune de analiză multidimensională Microsoft Excel 2000. 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 sunt informații despre cazuri specifice de boli, sunt date confidențiale institutii 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ă de cunoștință publică.

Ideea stocării unui cache de date agregate într-un fișier a fost dezvoltată în continuare în instrumentele OLAP de server, în care salvarea și modificarea datelor agregate, precum și menținerea stocării care le conține, se realizează printr-o aplicație sau proces separat 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 instrumentele OLAP 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 o matrice multidimensională, datorită căreia 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 efectua orice analiză logică și statistică caracteristică 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ă căi diferite, începând cu cele mai simple instrumente de analiză a datelor din aplicațiile de birou și terminând cu sisteme analitice distribuite 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 pe 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

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.
Procesarea multidimensională este un mijloc (limbaj) pentru formularea de interogări multidimensionale (limbajul relațional tradițional SQL este nepotrivit aici) și un procesor care poate procesa și executa o astfel de interogare.
Stocarea multidimensională este un mijloc de organizare fizică a datelor care asigură executarea eficientă a interogărilor multidimensionale.

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.

Într-un tabel pivot standard, datele sursă sunt stocate pe hard disk-ul local. În acest fel, le puteți gestiona și reorganiza oricând, 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 niciodată stocată pe hard disk-ul local. Prin urmare, imediat după deconectare de la retea locala tabelul pivot nu va mai funcționa. Nu veți putea muta un singur câmp în el.

Dacă mai trebuie să analizați datele OLAP după ce ați fost offline, creați un cub de date offline. Un cub de date offline este un fișier separat care este un cache de tabel pivot și stochează date OLAP care sunt vizualizate după deconectarea de la rețeaua locală. Datele OLAP copiate într-un tabel pivot pot fi tipărite; acest lucru este descris în detaliu pe site-ul web http://everest.ua.

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 din fila contextuală Instrumente, care face parte din grupul de file contextuale Instrumente PivotTable. Selectați o echipă Modul offline OLAP (OLAP offline) (Fig. 9.8).

Pe ecran apare caseta de dialog Offline OLAP Data Cube Settings. Faceți clic pe butonul Creare fișier de date offline. Ați lansat Expertul Creare fișier cub de date. Faceți clic pe butonul Următorul pentru a continua procedura.

Mai întâi trebuie să specificați dimensiunile și nivelurile care vor fi incluse în cubul de date. În caseta de dialog, trebuie să selectați datele care vor fi importate din baza de date OLAP. Ideea este de a specifica doar acele dimensiuni care vor fi necesare după ce computerul este deconectat 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 Următorul pentru a trece la următorul căsuță de dialog masterat Acest lucru vă oferă posibilitatea de a specifica membri sau elemente de date care nu vor fi incluse în cub. În special, nu veți avea nevoie de măsura Vânzărilor pe Internet-Sumă extinsă, așa că caseta de selectare a acesteia va fi debifată din listă. O casetă de selectare debifată indică faptul că elementul specificat nu va fi importat și va ocupa spațiu inutil pe hard disk-ul local.

În ultimul pas, specificați locația și numele cubului de date. În cazul nostru, fișierul cub va fi numit MyOfflineCube.cub și va fi localizat în folderul Work.

Fișierele cub de date au extensia .pui

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, ceea ce va provoca generare automată Un registru de lucru Excel care conține un tabel pivot asociat cu cubul de date selectat. Odată creat, puteți distribui cubul de date offline tuturor utilizatorilor interesați care lucrează în modul LAN offline.

Odată conectat la rețeaua locală, puteți deschide fișierul cub de date offline și îl puteți actualiza și tabelul de date corespunzător. Principiul principal prevede că cubul de date offline este folosit doar pentru a funcționa atunci când rețeaua locală este deconectată, dar este necesar să fie actualizat după restabilirea conexiunii. Încercarea de a actualiza un cub de date offline după o eroare a conexiunii va avea ca rezultat un eșec.

Al treilea articol, despre procesarea unor cantități mari de date cu 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. De asemenea, în principiu, este posibil să se genereze un set de valori unice folosind doar formule (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 anterior, în selectarea unei liste unice (în exemplu, numele țărilor).

Analiza 2D

Exemplele descrise mai devreme demonstrează analiza datelor bazată pe un criteriu. Foi 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 mai multor criterii este posibilă și prin standard Funcții Excel SUMIFS, SUMPRODUCT și funcții 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ă. Există o zonă specială de celule situată deasupra tabelului pivot în acest scop.

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 a munca interactivă: Trebuie să configurați și să formatați formularul de raportare o dată, apoi atunci când editați datele 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 de tabel pivot vă permite să implementați diferite grupări atunci când lucrați cu tipul „dată” (pe lună, pe trimestru), dar dacă există valori goale, aceste opțiuni devin indisponibile, deoarece Excel definește o astfel de coloană ca 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)

Cea mai fiabilă, dar cea mai lentă modalitate este de a verifica secvențial rândurile foii sursă și apoi de a completa proprietatea 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 folosită cu mare atenție - interfața raportului rezumat implică modificarea poziției datelor afișate în raport cu coordonatele dreptunghiulare ale foii de lucru fără niciun efect 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:

Copierea și lipirea valorilor tabelului pivot într-o altă foaie (folosind funcția „Lipire specială”) cu o căutare ulterioară a datelor î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.
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, există funcții speciale acces la date și măsurători: 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.
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 versiunile anterioare ale interfeței (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 memoria cache sunt actualizate atunci când faceți clic pe butonul Reîmprospătare al interfeței PivotTable (butonul de pe panglică sau din meniul contextual), sau la un interval de timp specificat, dacă o astfel de setare este specificată în parametri. Modul de calcul 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, 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:

Intervalele de celule
Interogări baze de date
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 bazei 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 la baza de date este performanta ridicata, pe măsură ce 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 SQL Server Serviciu de analiză pentru crearea și configurarea cuburilor OLAP. Recomandarea este utilă, dar, în primul rând, acest serviciu este inclus doar în versiunile plătite ale 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ă nicio modalitate de a schimba elementele părinte și secundare.
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.

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 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țiere 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 dimensiuni, să omiteți și detaliile de nivel inferior și caracteristicile de nivel superior pe care nu doriți să le afișați. 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 a serverului și fișierul offline (de exemplu, când lucrați pe un laptop acasă sau pe drum și apoi reconectați computerul 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 consuma mult timp și nu puteți lucra în Microsoft Excel în timp ce fișierul este salvat. 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 fișierul 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