Lucrări practice despre tehnologia olap în excel. Lucrări practice în informatică Dna

03.09.2020 Programe

Problemele de analiză, OLAP, depozite de date sunt de un interes din ce în ce mai mare în rândul specialiștilor IT ruși. Până acum, în presa noastră informatică și pe internet, au fost publicate o mulțime de materiale academice bune pe această temă, inclusiv cele introductive. Vă aducem în atenție un articol în care încercăm în mod deliberat să explicăm OLAP „pe degete”, cu un exemplu concret. Practica arată că o astfel de explicație este necesară pentru unii profesioniști IT și mai ales pentru utilizatorii finali.

Deci, OLAP * 1 în prima aproximare, „pe degete”, poate fi definit ca o modalitate specială de analiză a datelor și obținere de rapoarte. Esența sa este de a oferi utilizatorului un tabel multidimensional care rezumă automat datele în diverse secțiuni și vă permite să controlați în mod interactiv calculele și forma raportului. Acest articol va vorbi despre tehnologia și operațiunile de bază ale OLAP folosind exemplul de analiză a facturilor unui angro de produse alimentare.

*unu. OLAP - Procesare analitică on-line, analiza datelor online.

Ca instrument, vom lua în considerare un sistem OLAP din cea mai simplă și mai ieftină clasă - client OLAP * 1. De exemplu, cel mai simplu produs din numărul de clienți OLAP a fost ales – „Kontur Standard” de către Intersoft Lab. (Pentru claritate, mai târziu în articol, termenii OLAP general acceptați vor fi indicați cu caractere aldine și însoțiți de omologii lor în limba engleză.)

*unu. Mai multe detalii despre clasificarea sistemelor OLAP sunt descrise în articolul „OLAP Made in Russia” din PC Week / RE, №3 / 2001.

Deci, să trecem la lucrul cu sistemul. Mai întâi, trebuie să descrii sursa de date - calea către tabel și câmpurile sale. Depinde de utilizatorul care cunoaște implementarea fizică a bazei de date. Pentru utilizatorii finali, acesta traduce numele tabelului și câmpurile acestuia în termeni de domeniu. În spatele „sursei de date” se află un tabel local, un tabel sau o vedere sau o procedură stocată SQL Server.

Cel mai probabil, într-o anumită bază de date, facturile sunt stocate nu într-unul, ci în mai multe tabele. În plus, unele câmpuri sau înregistrări nu pot fi utilizate pentru analiză. Prin urmare, este creată apoi o Selecție (set de rezultate sau interogare), în care sunt configurate: algoritmul de unire a tabelelor după câmpuri cheie, condiții de filtrare și un set de câmpuri returnate. Să numim selecția noastră „Facturi” și să punem în ea toate câmpurile sursei de date „Facturi”. Astfel, profesionistul IT, creând stratul semantic, ascunde de utilizatorul final implementarea fizică a bazei de date.

Apoi raportul OLAP este configurat. Acest lucru poate fi făcut de un expert în domeniu. În primul rând, câmpurile plate de eșantionare a datelor sunt împărțite în două grupuri - fapte (fapte sau măsuri) și dimensiuni (dimensiuni). Faptele sunt numere, iar dimensiunile sunt „tăieri” care vor rezuma faptele. În exemplul nostru, dimensiunile vor fi: „Regiune”, „Oraș”, „Client”, „Produs”, „Data” și va exista un singur fapt - câmpul „Suma” al facturii. De fapt, trebuie să selectați unul sau mai mulți algoritmi de agregare. OLAP este capabil nu doar să rezuma, ci și să efectueze calcule mai complexe, până la analize statistice. Alegerea mai multor algoritmi de agregare va genera fapte virtuale, calculate. În exemplu, este selectat un algoritm de agregare - „Suma”.

O caracteristică specială a sistemelor OLAP este generarea de dimensiuni și date pentru perioade de timp mai vechi de la dată și calcularea automată a totalurilor pentru aceste perioade. Să selectăm perioadele „An”, „Trimestru” și „Lună”, în timp ce nu vor exista date pentru fiecare zi în raport, dar vor apărea dimensiunile generate „Anul”, „Trimestrul” și „Luna”. Să numim raportul „Analiza vânzărilor” și să-l salvăm. Lucrările de creare a interfeței aplicației analitice s-au încheiat.

Acum, prin lansarea acestei interfețe zilnic sau lunar, utilizatorul va vedea un tabel și un grafic în care facturile sunt rezumate pe mărfuri, clienți și perioade.

Pentru a face manipulările cu date intuitive, instrumentele pentru gestionarea unui tabel dinamic sunt elementele tabelului în sine - coloanele și rândurile sale. Utilizatorul le poate muta, șterge, filtra și efectua alte operațiuni OLAP. În acest caz, tabelul calculează automat noi subtotaluri și totaluri finale.

De exemplu, trăgând (operațiunea „mutare”) coloana „Produs” pe primul loc, vom primi un raport de comparație - „Comparația vânzărilor de produse pentru anul”. Pentru a agrega datele pentru un an, trebuie doar să trageți coloanele „Trimestru” și „Luna” în partea de sus a tabelului - „zona dimensiunilor inactive”. Dimensiunile „Sfert” și „Lună”, transferate în această zonă, vor fi închise (operațiunea „dimensiune închidere”), adică excluse din raport; faptele vor fi rezumate pentru anul. În ciuda faptului că dimensiunile sunt închise, acestea pot fi folosite pentru a specifica anumiți ani, trimestre și luni pentru filtrarea datelor (operațiune „filtru”).

Pentru o mai mare claritate, să schimbăm tipul de grafic care ilustrează tabelul OLAP și locația acestuia pe ecran.

Explorarea datelor (operațiunea „drill down”) ne permite să obținem informații mai detaliate despre vânzările produsului care ne interesează. Făcând clic pe semnul „+” vizavi de produsul „Cafea”, vom vedea volumul vânzărilor acestuia în contextul regiunilor. După ce am deschis regiunea Ural, vom obține volumele de vânzări în contextul orașelor din regiunea Ural, aprofundând în datele despre Ekaterinburg, vom putea vizualiza datele despre cumpărătorii angro ai acestui oraș.

De asemenea, puteți utiliza măsurători deschise pentru a seta filtre. Pentru a compara dinamica vânzărilor de dulciuri în Moscova și Ekaterinburg, să setăm filtre pe dimensiunile „Produs” și „Oraș”.

Închideți măsurătorile inutile și selectați tipul de grafic „Linie”. Pe graficul rezultat, puteți urmări dinamica vânzărilor, puteți evalua fluctuațiile sezoniere și relația dintre scăderi și creșterea vânzărilor de mărfuri în diferite orașe.

Astfel, ne-am asigurat că tehnologia OLAP permite utilizatorului să elibereze zeci de tipuri diferite de rapoarte dintr-o singură interfață, manipulând un tabel OLAP dinamic cu mouse-ul. Sarcina programatorului care deține un astfel de instrument nu este codificarea de rutină a formularelor de raportare, ci configurarea clientului OLAP pentru baze de date. În același timp, metodele de gestionare a raportului sunt intuitive pentru utilizatorul final.

Într-adevăr, OLAP este o continuare naturală și o dezvoltare a ideii de foi de calcul. În esență, interfața vizuală OLAP este, de asemenea, o foaie de calcul, dar echipată cu un motor de calcul puternic și un standard special pentru prezentarea și manipularea datelor. Mai mult, unii clienți OLAP sunt implementați ca suplimente pentru MS Excel. Prin urmare, o armată de gulere albe de un milion de oameni, care stăpânește cu încredere foile de calcul, stăpânește foarte repede instrumentele OLAP. Pentru ei, aceasta este o „revoluție de catifea” care oferă noi oportunități, dar nu este asociată cu nevoia de recalificare.

Dacă cititorul, după ce a citit acest articol, nu și-a pierdut interesul pentru OLAP, se poate referi la materialele menționate la început. Colecțiile de astfel de materiale sunt postate pe o serie de site-uri de pe Internet, inclusiv site-ul de laborator Intersoft - www.iso.ru. Din acesta puteți descărca și o versiune demo a sistemului Kontur Standard cu exemplul descris în articol.

Procesarea analitică analitică (OLAP) este o tehnologie care este folosită pentru a organiza bazele de date de afaceri mari și pentru a sprijini business intelligence. Bazele de date OLAP sunt împărțite în unul sau mai multe cuburi, iar fiecare cub este organizat de administratorul cubului pentru a se potrivi cu modul în care datele sunt preluate și analizate pentru a facilita crearea și utilizarea rapoartelor PivotTable și PivotChart de care aveți nevoie.

În acest articol

Ce este Business Intelligence?

Un analist de afaceri dorește adesea să obțină o imagine de ansamblu a unei afaceri pentru a vedea tendințele mai ample bazate pe date agregate, precum și pentru a vedea tendințele defalcate în orice număr de variabile. Business intelligence este procesul de extragere a datelor dintr-o bază de date OLAP și de analizare a acestor date pentru a oferi informații pe care le puteți utiliza pentru a lua decizii de afaceri informate și pentru a lua măsuri. De exemplu, cu OLAP și Business Intelligence, puteți răspunde la următoarele întrebări despre datele de afaceri.

Cum se compară vânzările totale ale tuturor produselor în 2007 cu vânzările din 2006?

Cum se compară acest lucru cu o dată și o oră pentru o perioadă favorabilă din ultimii cinci ani?

Câți bani au cheltuit clienții pentru 35 în ultimul an și cum s-a schimbat acest comportament în timp?

Câte produse au fost vândute în două țări/regiuni specifice luna aceasta față de aceeași lună a anului trecut?

Pentru fiecare grupă de vârstă a clienților Care este defalcarea rentabilității investiției (atât procentul de marjă, cât și totalul) pe categorii de produse?

Căutați vânzători de top și de jos, distribuitori, furnizori, clienți, parteneri și clienți.

Ce este procesarea analitică online (OLAP)?

Bazele de date de procesare analitică online (OLAP) simplifică interogările de business intelligence. OLAP este o tehnologie de baze de date optimizată pentru interogări și rapoarte, nu pentru procesarea tranzacțiilor. Sursa de date pentru OLAP este bazele de date de procesare a tranzacțiilor online (OLTP), care sunt de obicei stocate în depozite de date. Datele OLAP sunt extrase din aceste date istorice și combinate în structuri care permit analize complexe. Datele OLAP sunt, de asemenea, organizate ierarhic și stocate mai degrabă în cuburi decât în tabele. Este o tehnologie complexă care utilizează structuri multidimensionale pentru a oferi acces rapid la date pentru analiză. În această organizație, pentru un raport PivotTable sau PivotChart, puteți afișa cu ușurință date rezumative la nivel înalt, cum ar fi vânzările totale pentru o întreagă țară sau regiune și puteți afișa informații despre site-urile unde vânzările sunt deosebit de mari sau slabe.

Bazele de date OLAP sunt concepute pentru a accelera încărcarea datelor. Deoarece serverul OLAP, și nu Microsoft Office Excel, calculează valorile rezumate, mai puține date trebuie trimise la Excel atunci când creați sau modificați un raport. Această abordare vă permite să lucrați cu o cantitate mai mare de date brute decât dacă datele ar fi organizate într-o bază de date tradițională, unde Excel preia toate înregistrările individuale și calculează valorile rezumate.

Bazele de date OLAP conțin două tipuri principale de date: măsuri, care sunt date numerice, cantități și medii, care sunt utilizate pentru a lua decizii de afaceri în cunoștință de cauză, și dimensiuni, care sunt categorii utilizate pentru a organiza acele măsuri. Bazele de date OLAP vă ajută să vă organizați datele cu mai multe niveluri de detaliu, folosind aceleași categorii pe care le cunoașteți pentru analiza datelor.

Următoarele secțiuni descriu fiecare componentă în detaliu mai jos.

Cub O structură de date care agregează măsurile după nivel și ierarhie pentru fiecare dimensiune pe care doriți să o analizați. Cuburile combină mai multe dimensiuni, cum ar fi timpul, geografia și liniile de produse, cu totaluri precum vânzările și inventarul. Cuburile nu sunt „Cuburi” în sensul strict matematic, deoarece nu au neapărat aceleași laturi. Cu toate acestea, ele reprezintă metafora lui Apt pentru un concept complex.

Măsurătorile Un set de valori într-un cub bazat pe o coloană din tabelul de fapte cub, care este de obicei o valoare numerică. Măsurile sunt valori centrale într-un cub care sunt preprocesate, procesate și analizate. Cele mai comune exemple sunt vânzările, veniturile, veniturile și costurile.

Membru Un articol dintr-o ierarhie care reprezintă una sau mai multe apariții de date. Un articol poate fi fie unic, fie neunic. De exemplu, 2007 și 2008 reprezintă membri unici la nivel de an al dimensiunii de timp, în timp ce ianuarie reprezintă membri neunici la nivel de lună, deoarece există mai mult de un ianuarie în dimensiunea de timp, deoarece conține date pentru mai mult de un an.

Membru calculat Un membru de dimensiune a cărui valoare este calculată în timpul executării utilizând o expresie. Valorile calculate ale membrilor pot fi derivate din alte valori ale membrilor. De exemplu, un articol calculat „profit” poate fi determinat prin scăderea valorii articolului, precum și a costului din valoarea articolului, vânzări.

dimensiune O colecție de una sau mai multe ierarhii ordonate la nivel de cub pe care un utilizator le înțelege și le folosește ca bază pentru analiza datelor. De exemplu, o dimensiune geografică poate include niveluri de țară/regiune, stat/district și oraș. În plus, o dimensiune de timp poate include o ierarhie cu nivelurile anului, trimestrului, lunii și zilei. Într-un raport PivotTable sau un raport PivotChart, fiecare ierarhie devine un set de câmpuri pe care le puteți extinde și restrânge pentru a afișa niveluri inferioare sau superioare.

Ierarhie O structură arborescentă logică care ordonează membrii unei dimensiuni astfel încât fiecare membru să aibă un părinte și zero sau mai mulți copii. Un copil este membru al unui grup anterior din ierarhie care este direct legat de membrul curent. De exemplu, într-o ierarhie temporală care conține nivelurile trimestrului, lunii și zilei, ianuarie este un copil al lui Qtr1. Un părinte este un membru la un nivel inferior în ierarhie care este direct legat de membrul curent. Valoarea parentală este de obicei consolidarea valorilor tuturor copiilor. De exemplu, într-o ierarhie temporală care conține nivelurile „sfert”, „lună” și „zi”, Qtr1 este părintele lunii ianuarie.

NivelÎntr-o ierarhie, datele pot fi organizate în niveluri mai mici și mai mari de granularitate, cum ar fi ani, trimestre, luni și niveluri zilnice în ierarhia de timp.

Funcții OLAP în Excel

Preluarea datelor OLAP Vă puteți conecta la sursele de date OLAP în același mod în care vă puteți conecta la alte surse de date externe. Puteți lucra cu baze de date create cu Microsoft SQL Server OLAP Services 7.0, Microsoft SQL Server Analysis Services 2000 și Microsoft SQL Server Analysis Services 2005 și Microsoft OLAP Server Products. Excel poate funcționa și cu produse OLAP de la terți, care sunt compatibile cu OLE-DB pentru OLAP.

Datele OLAP pot fi afișate numai ca raport PivotTable sau raport PivotChart sau ca funcție de foaie de lucru convertită dintr-un raport PivotTable, dar nu ca un interval de date extern. Puteți salva rapoarte OLAP PivotTable și PivotChart în șabloane de rapoarte și puteți crea fișiere Office Data Connection (ODC) pentru a vă conecta la bazele de date OLAP pentru interogări OLAP. Când deschideți fișierul .odc în Excel, este afișat un raport PivotTable necompletat, gata pentru a fi găzduit.

Creați fișiere cub pentru utilizare offline Puteți crea un fișier cub autonom (. Cub) cu un subset de date dintr-o bază de date de server OLAP. Fișierele cub offline sunt folosite pentru a lucra cu date OLAP atunci când nu sunteți conectat la o rețea. Folosind un cub, puteți lucra cu mai multe date dintr-un raport PivotTable sau un raport PivotChart decât ați face altfel și să accelerați recuperarea datelor. Puteți crea fișiere Cube numai dacă utilizați un furnizor OLAP, cum ar fi Microsoft SQL Analysis Services 2005, care acceptă această caracteristică.

Acțiuni de server O acțiune de server este o funcție opțională pe care un administrator de cub OLAP o poate defini pe un server care utilizează un element de cub sau o măsură ca parametru într-o interogare pentru a prelua informații din cub sau pentru a lansa o altă aplicație, cum ar fi un browser. Excel acceptă adrese URL, rapoarte, seturi de rânduri, detaliază și detaliază acțiunile detaliate ale serverului, dar nu acceptă propriul operator nativ și setul de date.

KPI Un indicator cheie de performanță (KPI) este o măsură specială calculată, definită pe server, care urmărește „indicatorii cheie de performanță”, inclusiv starea (valoarea curentă corespunde unui anumit număr). și tendință (valori în timp). Când sunt afișate, serverul poate trimite pictograme adecvate, similare cu noua pictogramă Excel, pentru a se alinia deasupra sau sub nivelurile de stare (de exemplu, pentru o pictogramă de oprire) și poate derula valoarea în sus sau în jos (de exemplu, un pictograma săgeată).

Formatarea serverului Administratorii cubului pot crea măsuri și membri calculați folosind formatarea culorilor, formatarea fontului și regulile de formatare condiționată, care pot fi atribuite ca regulă de afaceri standard corporativă. De exemplu, formatul de venit pentru server poate fi un format de monedă numerică, culoarea celulei este verde dacă valoarea este mai mare sau egală cu 30.000 și roșu dacă valoarea este mai mică de 30.000, iar stilul fontului este bold dacă valoarea este mai mică de 30.000, iar dacă valoarea este pozitivă.- uzual. este mai mare sau egal cu 30 000. Pentru mai multe informații, consultați.

Limba biroului Un administrator de cub poate defini traduceri pentru date și erori de pe server pentru ca utilizatorii să vadă informațiile din PivotTable într-o altă limbă. Această funcție este definită ca o proprietate de conexiune la fișier, iar localitatea și țara computerului utilizatorului trebuie să se potrivească cu limba interfeței.

Componentele software necesare pentru a accesa sursele de date OLAP

Furnizorul OLAP Pentru a configura sursele de date OLAP pentru Excel, aveți nevoie de unul dintre următorii furnizori OLAP.

Furnizor Microsoft OLAP Excel include un driver de sursă de date și un software client pentru accesarea bazelor de date create cu Microsoft SQL Server olap versiunea 7.0, Microsoft SQL Server olap versiunea 2000 (8.0) și serviciile Microsoft SQL Server Analysis versiunea 2005 (9, 0).

Furnizori OLAP terți Alte produse OLAP necesită drivere suplimentare și software client pentru a fi instalate. Pentru a utiliza capabilitățile de date OLAP ale Excel, produsul terță parte trebuie să respecte standardul OLE-DB pentru OLAP și să fie compatibil cu Microsoft Office. Pentru informații despre instalarea și utilizarea unui furnizor OLAP terță parte, contactați administratorul de sistem sau furnizorul de produse OLAP.

Baze de date server și fișiere cub Software-ul client Excel OLAP acceptă conexiuni la două tipuri de baze de date OLAP. Dacă baza de date de pe serverul OLAP este online, puteți obține date brute direct de pe aceasta. Dacă aveți un fișier cub offline care conține date OLAP sau un fișier de definire a cubului, vă puteți conecta la acel fișier și puteți obține date brute din acesta.

Surse de date O sursă de date oferă acces la toate datele dintr-o bază de date OLAP sau un fișier cub offline. Odată ce creați o sursă de date OLAP, puteți baza rapoarte pe aceasta și puteți returna datele OLAP în Excel ca raport PivotTable sau raport PivotChart și ca funcție de foaie de lucru convertită dintr-un raport PivotTable.

Microsoft Query Cu Query, puteți prelua date dintr-o bază de date externă, cum ar fi Microsoft SQL sau Microsoft Access. Nu trebuie să utilizați o interogare pentru a prelua date dintr-un tabel pivot OLAP asociat cu un fișier cub. Informații suplimentare .

Diferențele între caracteristicile OLAP și non-OLAP ale datelor sursă

Dacă lucrați cu rapoarte PivotTable și PivotCharts din date sursă OLAP și alte tipuri de date sursă, veți vedea unele diferențe de funcționalitate.

Recuperarea datelor Serverul OLAP returnează date noi în Excel ori de câte ori aspectul raportului se modifică. Cu alte tipuri de surse de date externe, interogați toate datele sursă dintr-o singură mișcare sau puteți seta parametrii pentru interogare numai atunci când afișați diferite elemente ale câmpurilor de filtru ale raportului. În plus, mai aveți câteva opțiuni pentru actualizarea raportului.

În rapoartele bazate pe date brute OLAP, opțiunile câmpului de filtru de raport nu sunt disponibile, interogarea de fundal nu este disponibilă și opțiunea de optimizare a memoriei nu este disponibilă.

Notă: De asemenea, opțiunea de optimizare a memoriei nu este disponibilă pentru sursele de date OLEDB și rapoartele PivotTable bazate pe o serie de celule.

Tipuri de câmpuri Date sursă OLAP. câmpurile de dimensiuni pot fi utilizate numai ca rânduri (rânduri), coloane (categorie) sau câmpuri de pagină. Câmpurile de măsură pot fi folosite doar ca câmpuri de valoare. Pentru alte tipuri de date sursă, toate câmpurile pot fi utilizate oriunde în raport.

Acces la date detaliate Pentru datele sursă OLAP, serverul determină nivelurile disponibile de detaliu și calculează valorile rezumative, astfel încât înregistrările detaliate care alcătuiesc valorile rezumate pot să nu fie disponibile. Cu toate acestea, serverul poate furniza câmpuri de proprietate pe care le puteți afișa. Alte tipuri de date sursă nu au câmpuri de proprietate, dar puteți afișa informații de bază pentru câmpurile de date și valorile membrilor și puteți afișa membri care nu sunt de date.

Este posibil ca câmpurile de filtrare a raportului OLAP să nu aibă toate elemente și comanda Afișați paginile de filtrare a raportului indisponibil.

Ordinea de sortare inițială Pentru datele sursă OLAP, elementele sunt afișate mai întâi în ordinea în care sunt returnate de serverul OLAP. Puteți sorta sau modifica manual ordinea articolelor. Pentru alte tipuri de date sursă, noile elemente ale raportului sunt mai întâi sortate după numele articolului, în ordine crescătoare.

Nimi Serverele OLAP furnizează valori rezumative direct pentru raport, astfel încât nu puteți modifica funcțiile de rezumat pentru câmpurile de valoare. Pentru alte tipuri de date sursă, puteți modifica funcția de agregare pentru câmpul de valoare și puteți utiliza mai multe funcții de rezumat pentru același câmp de valoare. Nu puteți crea câmpuri calculate și membri calculați în rapoarte cu date sursă OLAP.

SubtotaluriÎn rapoartele cu date brute OLAP, nu puteți modifica funcția de rezumat pentru subtotaluri. Cu alte tipuri de date sursă, puteți modifica funcțiile de subtotaluri pentru subtotaluri și puteți afișa sau ascunde subtotalurile pentru toate câmpurile de rând și coloană.

Pentru datele sursă OLAP, puteți include sau exclude elemente ascunse atunci când calculați subtotalurile și totalurile generale. Pentru alte tipuri de date sursă, puteți include elemente ascunse în câmpurile de filtru ale raportului în subtotaluri, dar elementele ascunse din alte câmpuri vor fi excluse în mod implicit.

Uimitor - aproape de...

În decursul activității mele, am avut deseori nevoie să fac rapoarte complexe, am încercat constant să găsesc ceva în comun în ele pentru a le compune mai simplu și mai universal, chiar am scris și publicat un articol pe acest subiect „Arborele lui Osipov”. Totuși, articolul meu a fost criticat și a spus că toate problemele pe care le-am ridicat au fost de mult rezolvate în MOLAP.RU v.2.4 (www.molap.rgtu.ru) și a recomandat să te uiți la tabelele rezumative în EXCEL.
S-a dovedit a fi atât de simplu încât, după ce am aplicat mâinile mele ingenioase, am primit o schemă foarte simplă pentru descărcarea datelor din 1C7 sau orice altă bază de date (în continuare, 1C înseamnă orice bază de date) și analiză în OLAP.
Cred că multe dintre schemele de încărcare OLAP sunt prea complicate, aleg simplitatea.

Specificații :

1. Pentru funcționare este necesar doar EXCEL 2000.
2. Utilizatorul însuși poate proiecta rapoarte fără programare.
3. Descărcarea de la 1C7 într-un format simplu de fișier text.
4. Pentru tranzacțiile din registru, există deja o procesare de încărcare generică care funcționează în orice configurație. Procesarea eșantionului este disponibilă pentru încărcarea altor date.
5. Puteți proiecta formulare de raport în avans și apoi le puteți aplica diferitelor date fără a le reproiecta.
6. Performanță destul de bună. La prima etapă lungă, datele sunt importate mai întâi în EXCEL dintr-un fișier text și este construit un cub OLAP, iar apoi, destul de repede, pe baza acestui cub poate fi construit orice raport. De exemplu, datele despre vânzările de produse de către un magazin timp de 3 luni cu un sortiment de 6000 de produse sunt încărcate în EXCEL în 8 minute pe Cel600-128M, evaluarea pe produse și grupuri (raport OLAP) este recalculată în 1 minut.
7. Datele sunt descărcate din 1C7 complet pentru perioada specificată (toate mișcările, în toate depozitele, firmele, conturile). La importul în EXCEL, este posibil să folosiți filtre care încarcă doar datele necesare pentru analiză (de exemplu, din toate mișcările, doar vânzări).
8. În prezent, s-au dezvoltat metode de analiză a mișcărilor sau a reziduurilor, dar nu a mișcărilor și a reziduurilor împreună, deși acest lucru este posibil în principiu.

Ce este OLAP : (www.molap.rgtu.ru)

Să presupunem că aveți o rețea de vânzări. Lăsați datele despre operațiunile comerciale să fie încărcate într-un fișier text sau un tabel de forma:

Data - data operațiunii
Luna - luna de functionare
Săptămâna - săptămână de intervenție chirurgicală
Tip - cumpărare, vânzare, retur, anulare
Contraparte - o organizație externă implicată într-o operațiune
Autor - persoana care a emis factura

În 1C, de exemplu, un rând din acest tabel va corespunde unui rând al facturii, unele câmpuri (Contraparte, Data) sunt preluate din antetul facturii.

Datele pentru analiză sunt de obicei încărcate în sistemul OLAP pentru o anumită perioadă de timp, din care, în principiu, se poate selecta o altă perioadă prin aplicarea filtrelor de încărcare.

Acest tabel este sursa pentru analiza OLAP.

Raport	Măsurătorile	Date	Filtru
Câte produse se vând și pentru ce sumă pe zi?	Data, articol	Cantitate, Sumă	Kind = „vânzare”
Care contrapărți au livrat ce produs pentru ce sumă pe lună?	Lună, contrapartidă, articol	Sumă	Vizualizare = „cumpărare”
Pentru ce suma au scris operatorii de facturi ce tip de factura pe toata perioada raportului?		Sumă

Utilizatorul însuși determină care dintre câmpurile din tabel vor fi Dimensiuni, ce Date și ce Filtre să aplice. Sistemul în sine construiește raportul într-o formă tabelară vizuală. Dimensiunile pot fi plasate în titlurile de rând sau de coloană ale tabelului de raport.
După cum puteți vedea, dintr-un tabel simplu, puteți obține o mulțime de date sub formă de rapoarte diferite.

Cum se folosește acasă :

Despachetați datele din kitul de distribuție în directorul c:\fixin (pentru un sistem de tranzacționare, este posibil în c:\rapoarte). Citiți fișierul readme.txt și urmați toate instrucțiunile din acesta.

În primul rând, trebuie să scrieți procesarea care descarcă datele din 1C într-un fișier text (tabel). Trebuie să determinați compoziția câmpurilor care vor fi descărcate.
De exemplu, o procesare universală gata făcută care funcționează în orice configurație și descarcă tranzacții pentru o perioadă pentru analiza OLAP, descarcă următoarele câmpuri pentru analiză:

Acolo unde sub prefixele Дт (Кт) sunt subconto-ul Debit (Credit), Grup este grupul acestui subconto (dacă există), Secțiune este grupul grupului, Clasa este grupul secțiunii.

Pentru un sistem de tranzacționare, câmpurile pot fi următoarele:

Pentru a analiza datele, utilizați tabelele „Analiza mișcărilor.xls” („Analiza contabilității.xls”). Nu dezactivați macrocomenzi în timp ce le deschideți, altfel nu veți putea actualiza rapoartele (sunt lansate de macrocomenzi în limbaj VBA). Aceste fișiere sunt preluate din fișierele C:\fixin\motions.txt (C:\fixin\buh.txt), altfel sunt aceleași. Prin urmare, poate fi necesar să vă copiați datele într-unul dintre aceste fișiere.
Pentru a vă încărca datele în EXCEL, selectați sau scrieți filtrul și faceți clic pe butonul „Generează” din foaia „Condiții”.
Foile de raport încep cu prefixul „Raport”. Accesați foaia de raport, faceți clic pe „Actualizați” și datele raportului se vor modifica în funcție de ultimele date încărcate.
Dacă nu sunteți mulțumit de rapoartele standard, există o fișă Rep. Template. Copiați-l într-o foaie nouă și personalizați aspectul raportului lucrând cu tabelul pivot din această foaie (despre lucrul cu tabelele pivot - în orice carte bazată pe EXCEL 2000). Recomand să configurați rapoarte pe un set de date mic și apoi să le rulați pe un set de date mare. nu există nicio modalitate de a dezactiva redesenarea tabelului de fiecare dată când aspectul raportului se modifică.

Note tehnice :

La încărcarea datelor din 1C, utilizatorul alege folderul în care să încarce fișierul. Am făcut asta pentru că este probabil ca mai multe fișiere (rămășițe și mișcări) să fie încărcate în viitorul apropiat. Apoi, făcând clic pe butonul „Trimite” din Explorer -> „Pentru analiza OLAP în EXCEL 2000”, datele sunt copiate din folderul selectat în folderul C:\fixin. (pentru ca această comandă să apară în lista comenzii „Trimite” și trebuie să copiați fișierul „Pentru analiza OLAP în EXCEL 2000.bat” în directorul C:\Windows\SendTo) Prin urmare, descărcați datele imediat dând nume la fișierele motion.txt sau buh.txt.

Format fișier text:
Prima linie a fișierului text este antetele coloanelor separate prin „|”, restul liniilor conțin valorile acestor coloane, separate prin „|”.

Pentru a importa fișiere text în Excel, se folosește Microsoft Query (parte din EXCEL) pentru funcționarea acestuia, este necesar să aveți un fișier shema.ini în directorul de import (C:\fixin) care să conțină următoarele informații:

ColNameHeader = Adevărat
Format = Delimitat (|)
MaxScanRows = 3
CharacterSet = ANSI
ColNameHeader = Adevărat
Format = Delimitat (|)
MaxScanRows = 3
CharacterSet = ANSI

Explicație: motion.txt și buh.txt este numele secțiunii, corespunde numelui fișierului importat, descrie cum se importă un fișier text în Excel. Restul parametrilor înseamnă că prima linie conține numele coloanelor, separatorul de coloane este „|”, setul de caractere este Windows ANSI (pentru DOS - OEM).
Tipul câmpurilor este determinat automat pe baza datelor conținute în coloană (data, număr, rând).
Lista de câmpuri nu trebuie să fie descrisă nicăieri - EXCEL și OLAP vor determina ele însele care câmpuri sunt conținute în fișier de anteturile din prima linie.

Atenție, verificați setările regionale „Panou de control” -> „Setări regionale”. În procesarea mea, numerele sunt descărcate cu un separator prin virgulă, iar datele sunt în formatul „ZZ.LL.AAAA”.

Când butonul „Generare” este apăsat, datele sunt încărcate în tabelul pivot de pe foaia „Base”, iar toate rapoartele din foile „Raport” sunt preluate din acest tabel pivot.

Înțeleg că fanii MS SQL Server și ai bazelor de date puternice vor începe să mormăie că am simplificat totul prea mult, că procesarea mea va fi blocată pe un eșantion de un an, dar în primul rând vreau să ofer avantajele analizei OLAP pt. organizatii mijlocii. Aș poziționa acest produs ca instrument de analiză de un an pentru angrosisti, analiză trimestrială pentru retail și analiză operațională pentru orice organizație.

A trebuit să mă ocup de VBA, astfel încât datele să fie preluate dintr-un fișier cu orice listă de câmpuri și să fie posibil să pregătesc formulare de raport în avans.

Descrierea muncii în EXCEL (pentru utilizatori):

Instrucțiuni pentru utilizarea rapoartelor:
1. Trimiteți datele descărcate pentru analiză (consultați administratorul). Pentru a face acest lucru, faceți clic dreapta pe folderul în care ați descărcat datele din 1C și selectați comanda „Trimite”, apoi „La analiza OLAP în EXCEL 2000”.
2. Deschideți fișierul „Motion Analysis.xls”
3. Selectați valoarea Filtrului, filtrele de care aveți nevoie pot fi adăugate în fila „Valori”.
4. Apăsați butonul „Generare”, iar datele încărcate vor fi încărcate în EXCEL.
5. După încărcarea datelor în EXCEL, puteți vizualiza diferite rapoarte. Pentru a face acest lucru, faceți clic pe butonul „Actualizați” din raportul selectat. Foile cu rapoarte încep cu Rep.
Atenţie! După ce modificați valoarea filtrului, trebuie să faceți clic din nou pe butonul „Generare”, astfel încât datele din EXCEL să fie reîncărcate din fișierul de încărcare în conformitate cu filtrele.

Procesare demonstrativă:

Procesarea motionsbuh2011.ert este cea mai recentă versiune de încărcare a tranzacțiilor din Accounting 7.7 pentru analiză în Excel. Are o casetă de selectare „Atașați la fișier”, care vă permite să încărcați date în părți pe perioade, atașându-le la același fișier, în loc să le încărcați din nou în același fișier:

Procesarea motionwork.ert încarcă datele de vânzări pentru analiză în Excel.

Exemple de rapoarte:

Tablă de șah prin postări:

Volumul de lucru al operatorului în funcție de tipul de factură:

P.S. :

Este clar că printr-o schemă similară, puteți organiza descărcarea datelor din 1C8.
În 2011, m-a contactat un utilizator care trebuia să finalizeze această prelucrare în 1C7, astfel încât să încarce cantități mari de date, am găsit un outsourcer și am făcut această lucrare. Deci dezvoltarea este destul de relevantă.

Procesarea motionsbuh2011.ert a fost îmbunătățită pentru a face față descarcării unei cantități mari de date.

Prima interfață de tabele pivot, numită și rapoarte pivot, a fost inclusă în Excel încă din 1993 (versiunea de Excel 5.0). În ciuda numeroaselor sale caracteristici utile, este cu greu folosit de majoritatea utilizatorilor Excel. Chiar și utilizatorii experimentați înțeleg adesea prin termenul „raport rezumat” ceva construit cu formule complexe. Să încercăm să popularizăm utilizarea tabelelor pivot în munca de zi cu zi a economiștilor. Articolul discută bazele teoretice ale creării de rapoarte rezumative, oferă recomandări practice pentru utilizarea acestora și oferă, de asemenea, un exemplu de acces la date bazat pe mai multe tabele.

Termeni de analiză multivariată a datelor

Majoritatea economiștilor au auzit termenii „date multidimensionale”, „cub virtual”, „tehnologii OLAP” și așa mai departe. Dar, cu o conversație detaliată, de obicei se dovedește că aproape toată lumea nu știe cu adevărat despre ce este vorba în discurs. Adică, oamenii înseamnă ceva complex și de obicei care nu este relevant pentru activitățile lor zilnice. De fapt, acesta nu este cazul.

Date multidimensionale, măsurători

Este sigur să spunem că economiștii se confruntă aproape constant cu date multidimensionale, dar încearcă să le reprezinte într-o formă predefinită folosind foi de calcul. Multidimensionalitatea înseamnă aici capacitatea de a introduce, vizualiza sau analiza aceleași informații cu o schimbare a aspectului, utilizarea diferitelor grupări și sortări de date. De exemplu, un plan de vânzări poate fi analizat după următoarele criterii:

tipuri sau grupuri de bunuri;
mărci sau categorii de produse;
perioade (lună, trimestru, an);
cumpărători sau grupuri de cumpărători;
regiuni de vânzare
etc.

Fiecare dintre criteriile de mai sus în ceea ce privește analiza multivariată a datelor se numește „dimensiune”. Putem spune că o măsurătoare caracterizează informații pentru un set specific de valori. Un tip special de măsurare a informațiilor multidimensionale este „date”. În exemplul nostru, datele planului de vânzări pot fi:

volumul vânzărilor;
Prețul de vânzare;
reducere individuală
etc.

În teorie, datele pot fi, de asemenea, o dimensiune standard a informațiilor multidimensionale (de exemplu, puteți grupa datele după prețul de vânzare), dar, de obicei, datele sunt încă un tip special de valoare.

Astfel, putem spune că în munca practică, economiștii folosesc două tipuri de informații: date multidimensionale ( numere reale și planificate, având multe caracteristici) și cărți de referință (caracteristici sau măsurători ale datelor).

OLAP

Abrevierea OLAP (procesare analitică online) este tradusă literal ca „procesare analitică în timp real”. Definiția nu este foarte specifică, aproape orice raport al oricărui produs software poate fi rezumat sub ea. În sensul OLAP, înseamnă o tehnologie de lucru cu rapoarte speciale, inclusiv software, pentru obținerea și analizarea unor date structurate precis multidimensionale. Unul dintre produsele software populare care implementează tehnologiile OLAP este SQL Server Analysis Server. Unii chiar consideră în mod eronat că este singurul reprezentant al implementării software a acestui concept.

Cub de date virtual

„Cub virtual” (cub multidimensional, cub OLAP) este un termen tehnic inventat de unii furnizori de software specializati. Sistemele OLAP de obicei pregătesc și stochează date în propriile structuri, iar interfețele speciale de analiză (de exemplu, rapoartele pivot Excel) accesează datele din aceste cuburi virtuale. În același timp, utilizarea unei astfel de stocări dedicate nu este deloc necesară pentru procesarea informațiilor multidimensionale. În general, cub virtual- aceasta este o serie de date multidimensionale special optimizate care sunt utilizate pentru a crea rapoarte rezumative. Poate fi obținut atât prin instrumente software specializate, cât și prin acces simplu la tabelele bazei de date sau orice altă sursă, precum un tabel Excel.

Masă rotativă

Un tabel pivot este o interfață de utilizator pentru afișarea datelor multidimensionale. Folosind această interfață, puteți grupa, sorta, filtra și modifica locația datelor pentru a obține diverse mostre analitice. Actualizarea raportului se realizează prin mijloace simple ale interfeței cu utilizatorul, datele sunt agregate automat conform regulilor specificate și nu este necesară introducerea suplimentară sau repetată a vreunei informații. Interfața Excel PivotTable este poate cel mai popular produs software pentru lucrul cu date multidimensionale. Acceptă atât surse de date externe (cuburi OLAP și baze de date relaționale), cât și intervale de foi de calcul interne ca sursă de date. Începând cu versiunea 2000 (9.0), Excel acceptă și o formă grafică de afișare a datelor multidimensionale - o diagramă pivot (Pivot Chart).

Interfața PivotTable a Excel vă permite să poziționați dimensiunile datelor multidimensionale într-o zonă a unei foi de lucru. Pentru simplitate, vă puteți gândi la un tabel pivot ca la un raport situat deasupra unui interval de celule (de fapt, există o anumită legare a formatelor de celule de câmpurile tabelului pivot). Un tabel pivot Excel are patru zone pentru afișarea informațiilor: filtru, coloane, rânduri și date. Dimensiunile datelor sunt denumite câmpuri tabel pivot... Aceste câmpuri au propriile proprietăți și format de afișare.

Încă o dată, aș dori să vă atrag atenția asupra faptului că tabelul pivot Excel este destinat exclusiv analizei datelor fără posibilitatea de a edita informații. Mai apropiată ca înțeles ar fi utilizarea omniprezentă a termenului „Raport pivot”, și așa a fost numită această interfață până în 2000. Dar din anumite motive, în versiunile ulterioare, dezvoltatorii l-au abandonat.

Editarea tabelelor pivot

Prin definiția sa, tehnologia OLAP, în principiu, nu implică posibilitatea de a schimba datele sursă atunci când lucrezi cu rapoarte. Cu toate acestea, pe piață s-a format o întreagă clasă de sisteme software care implementează capabilitățile atât de analiză, cât și de editare directă a datelor în tabele multidimensionale. Practic, astfel de sisteme sunt axate pe rezolvarea problemelor de bugetare.

Folosind instrumentele de automatizare Excel încorporate, puteți rezolva multe sarcini nestandard. Un exemplu de implementare a editării pentru tabele pivot Excel pe baza datelor din foile de lucru poate fi găsit pe site-ul nostru web.

Pregătirea datelor multidimensionale

Să trecem la utilizarea practică a tabelelor pivot. Să încercăm să analizăm datele vânzărilor în direcții diferite. Fişier pivottableexample.xls este format din mai multe foi. Foaie Exemplu conține informații de bază despre vânzări pentru o anumită perioadă. Pentru simplitatea exemplului, vom analiza un singur indicator numeric - volumul vânzărilor în kg. Există următoarele dimensiuni de date cheie: produs, client și transportator (companie de transport). În plus, există mai multe dimensiuni de date suplimentare care sunt caracteristice unui produs: tip, marcă, categorie, furnizor și client: tip. Aceste date sunt colectate pe fișa de referințe. În practică, pot exista mult mai multe astfel de măsurători.

Foaie Exemplu conține un instrument standard de analiză a datelor - AutoFilter. Privind exemplul de completare a tabelului, este evident că datele privind vânzările pe date (sunt aranjate în coloane) se pretează unei analize normale. În plus, folosind un filtru automat, puteți încerca să rezumați datele prin combinații ale unuia sau mai multor criterii cheie. Nu există absolut nicio informație despre mărci, categorii și tipuri. Nu există nicio modalitate de a grupa datele cu rezumare automată după o anumită cheie (de exemplu, de către clienți). În plus, setul de date este fix și nu va fi posibilă vizualizarea informațiilor rezumative pentru o anumită perioadă, de exemplu, 3 zile, prin mijloace automate.

În general, prezența unei locații de date predefinite în acest exemplu este principalul dezavantaj al tabelului. Aranjand datele pe coloane, am cam predefinit dimensiunea acestui tabel, lipsindu-ne astfel de capacitatea de a folosi analiza folosind tabele pivot.

În primul rând, trebuie să scapi de acest dezavantaj - adică. eliminați locația predefinită a uneia dintre dimensiunile datelor originale. Exemplu de tabel valid - foaie Vânzări.

Tabelul are forma unui jurnal de introducere a informațiilor. Aici, data este dimensiunea egală a datelor. De asemenea, trebuie remarcat faptul că pentru analiza ulterioară în tabelele pivot, poziția relativă a rândurilor unul față de celălalt (cu alte cuvinte, sortarea) este complet indiferentă. Înregistrările din bazele de date relaționale au aceste proprietăți. Interfața tabelelor pivot se concentrează în primul rând pe analiza unor volume mari de baze de date. Prin urmare, trebuie să respectați aceste reguli atunci când lucrați cu o sursă de date sub formă de intervale de celule. În același timp, nimeni nu interzice utilizarea instrumentelor de interfață Excel în muncă - tabelele pivot analizează numai datele, iar formatarea, filtrele, gruparea și sortarea celulelor sursă pot fi arbitrare.

De la filtrul automat la raportul de rezumat

Teoretic, pe datele Fișei de vânzări este deja posibilă analiza în trei dimensiuni: mărfuri, clienți și transportatori. Nu există date despre proprietățile produselor și ale cumpărătorilor pe această foaie, ceea ce, în consecință, nu va permite ca acestea să fie afișate în tabelul pivot. În modul normal de creare a unui tabel pivot pentru datele sursă, Excel nu permite ca datele din mai multe tabele să fie legate prin anumite câmpuri. Puteți rezolva această limitare în mod programatic - vedeți un exemplu de supliment la acest articol pe site-ul nostru web. Pentru a nu recurge la metode software de prelucrare a informațiilor (mai ales că nu sunt universale), caracteristici suplimentare trebuie adăugate direct în formularul de intrare în jurnal - vezi fișa SalesAnalysis.

Utilizarea funcțiilor VLOOKUP facilitează completarea datelor originale cu caracteristici lipsă. Acum, folosind AutoFilter, puteți analiza datele în diferite dimensiuni. Dar problema grupărilor rămâne nerezolvată. De exemplu, este destul de problematic să urmăriți suma doar după mărci pentru anumite date. Dacă vă limitați la formule Excel, atunci trebuie să construiți selecții suplimentare folosind funcția SUMIF.

Acum să vedem ce caracteristici oferă interfața tabelului pivot. Pe foaie Analiza codului au fost construite mai multe rapoarte pe baza unei game de celule cu date de foaie Analiza vânzărilor.

Primul tabel de analiză este construit prin interfața Excel 2007 Panglică \ Inserare \ PivotTable(în Excel 2000-2003 meniul Date \ Tabel Pivot).

Al doilea și al treilea tabel au fost create prin copiere și personalizare ulterioară. Sursa de date pentru toate tabelele este aceeași. Puteți verifica acest lucru modificând datele originale, apoi trebuie să actualizați datele rapoartelor rezumative.

Din punctul nostru de vedere, avantajele în claritatea informațiilor sunt evidente. Puteți schimba filtre, coloane și rânduri și puteți ascunde anumite grupuri de valori pentru orice dimensiune și puteți aplica manual drag and drop și sortarea automată.

Proprietăți și formatare

Pe lângă afișarea directă a datelor, există o gamă largă de opțiuni pentru afișarea aspectului tabelelor pivot. Datele suplimentare pot fi ascunse folosind filtre. Pentru un singur element sau câmp, este mai ușor să utilizați elementul din meniul contextual Șterge(în versiunea 2000-2003 Ascunde).

De asemenea, este recomandabil să setați afișarea altor elemente din tabelul pivot nu prin formatarea celulei, ci prin setarea câmpului sau elementului tabelului pivot. Pentru a face acest lucru, mutați cursorul mouse-ului la elementul dorit, așteptați apariția unei forme speciale de cursor (sub forma unei săgeți), apoi selectați elementul selectat printr-un singur clic. După selecție, puteți schimba vizualizarea prin panglică, meniul contextual sau puteți apela dialogul format standard de celulă:

În plus, Excel 2007 introduce multe stiluri de afișare PivotTable predefinite:

Observați că filtrele de control și zonele de glisare și plasare sunt active în diagramă.

Acces la date externe

După cum sa menționat deja, poate cel mai mare efect al utilizării tabelelor pivot poate fi obținut atunci când accesați date din surse externe - cuburi OLAP și interogări de baze de date. Astfel de surse stochează de obicei cantități mari de informații și au, de asemenea, o structură relațională predefinită, care facilitează definirea dimensiunilor datelor multidimensionale (câmpuri de tabel pivot).

Excel acceptă multe tipuri de surse de date externe:

Cel mai mare efect din utilizarea surselor externe de informații se poate obține prin utilizarea instrumentelor de automatizare (programe VBA) atât pentru obținerea datelor, cât și pentru preprocesarea acestora în tabele pivot.

Lucrul cu un cub OLAP în MS Excel
1. Obțineți permisiunea de a accesa cubul OLAP SQL Server Analysis Services (SSAS).
2. MS Excel 2016/2013/2010 trebuie instalat pe computer (MS Excel 2007 este, de asemenea, posibil, dar nu este convenabil să lucrați în el, iar funcționalitatea MS Excel 2003 este destul de slabă)
3. Deschideți MS Excel, lansați vrăjitorul pentru configurarea conexiunii cu serviciul analitic:

3.1 Specificați numele sau adresa IP a serverului OLAP curent (uneori trebuie să specificați numărul portului deschis, de exemplu, 192.25.25.102:80); se utilizează autentificarea domeniului:

3.2 Selectați o bază de date multidimensională și un cub analitic (dacă aveți drepturi de acces la cub):

3.3 Setările pentru conectarea la serviciul analitic vor fi salvate într-un fișier odc de pe computer:

3.4 Selectați tipul de raport (tabel pivot / grafic) și indicați locația pentru plasarea acestuia:

Dacă o conexiune a fost deja creată în registrul de lucru Excel, atunci o puteți reutiliza: meniul principal „Date” -> „Conexiuni existente” -> selectați conexiunea în acest registru de lucru -> inserați tabelul pivot în celula specificată.
4. Conectat cu succes la cub, puteți începe analiza interactivă a datelor:

Înainte de a începe analiza interactivă a datelor, este necesar să se determine care dintre câmpuri va participa la formarea rândurilor, coloanelor și filtrelor (paginilor) din tabelul pivot. În general, tabelul pivot este tridimensional și putem presupune că a treia dimensiune este situată perpendicular pe ecran și observăm secțiuni paralele cu planul ecranului și determinate de ce „pagină” este selectată pentru afișare. Filtrarea se poate face prin glisarea și plasarea atributelor de dimensiune corespunzătoare în zona de filtrare a raportului. Filtrarea limitează spațiul cubului, reducând încărcarea pe serverul OLAP, deci este de preferat să instalați mai întâi filtrele necesare... Apoi plasați atributele dimensiunii în rândul, coloana și zonele de măsură din zona de date a tabelului pivot.

De fiecare dată când PivotTable se modifică, o instrucțiune MDX este trimisă automat către serverul OLAP și returnează datele. Cu cât este mai mare și mai complexă cantitatea de date procesate, indicatorii calculați, cu atât timpul de execuție a interogării este mai lung. Puteți anula executarea cererii apăsând tasta Evadare... Ultimele operații efectuate pot fi anulate (Ctrl + Z) sau reluate (Ctrl + Y).

De obicei, pentru cele mai utilizate combinații de atribute de dimensiune, cubul stochează date agregate precalculate, astfel încât timpul de răspuns pentru astfel de interogări este de câteva secunde. Cu toate acestea, este imposibil să se calculeze toate combinațiile posibile de agregare, deoarece acest lucru poate dura mult timp și spațiu de stocare. Executarea cererilor masive de date la nivel de granularitate poate necesita resurse de calcul de server semnificative, astfel încât timpul de execuție a acestora poate fi lung. După citirea datelor de pe unitățile de disc, serverul le plasează în memoria cache-ului RAM, ceea ce permite ca astfel de solicitări ulterioare să fie executate instantaneu, deoarece datele vor fi preluate din cache.

Dacă considerați că solicitarea dumneavoastră va fi folosită frecvent și timpul de execuție este nesatisfăcător, puteți contacta serviciul de suport pentru dezvoltare analitică pentru a optimiza executarea cererii.

După plasarea ierarhiei în zona rând/coloană, este posibil să ascundeți nivelurile individuale:

Atributele cheie(mai rar - pentru atribute mai înalte în ierarhie) dimensiunile pot avea proprietăți - caracteristici descriptive care pot fi afișate atât în tooltips, cât și sub formă de câmpuri:

Dacă trebuie să afișați mai multe proprietăți de câmp simultan, puteți utiliza lista de dialog corespunzătoare:

Truse definite de utilizator

Excel 2010 introduce capacitatea de a crea interactiv propriile seturi (definite de utilizator) din membrii dimensiunii:

Spre deosebire de seturile create și stocate central pe partea cubului, seturile personalizate sunt stocate local în registrul de lucru Excel și pot fi utilizate ulterior:

Utilizatorii avansați pot crea seturi folosind constructe MDX:

Setarea proprietăților tabelului pivot

Prin elementul „Parametrii tabelului pivot...” din meniul contextual (făcând clic dreapta în tabelul pivot), puteți personaliza tabelul pivot, de exemplu:
- fila „Ieșire”, parametrul „Dispunerea clasică a tabelului pivot” - tabelul pivot devine interactiv, puteți trage câmpurile (Drag & Drop);
- Fila „Ieșire”, parametru „Afișați articolele fără date în rânduri” - în tabelul pivot vor fi afișate rândurile goale care nu conțin valori de indicator pentru elementele de dimensiune corespunzătoare;
- Fila „Aspect și format”, parametrul „Menține formatarea celulelor la actualizare” - în tabelul pivot, puteți suprascrie și păstra formatul celulelor la actualizarea datelor;

Creați diagrame pivot

Pentru un tabel pivot OLAP existent, puteți crea o diagramă pivot - plăcintă, bară, bară, grafică, dispersie și alte tipuri de diagrame:

În acest caz, diagrama pivot va fi sincronizată cu tabelul pivot - atunci când modificați compoziția indicatorilor, filtrelor, dimensiunilor din tabelul pivot, tabelul pivot este și el actualizat.

Crearea de tablouri de bord

Selectați tabelul pivot original, copiați-l în clipboard (Ctrl + C) și lipiți o copie a acestuia (Ctrl + V), în care vom schimba compoziția indicatorilor:

Pentru a gestiona mai multe tabele pivot în același timp, inserați o secțiune (funcționalitate nouă disponibilă începând cu MS Excel 2010). Să conectăm Slicer-ul nostru la tabelele pivot - faceți clic dreapta în slicer, selectați „Pivot table connections...” din meniul contextual. Trebuie remarcat faptul că pot exista mai multe panouri pivot care pot servi tabele pivot pe diferite foi în același timp, ceea ce permite crearea de tablouri de bord coordonate.

Panourile Slicer pot fi personalizate: trebuie să selectați panoul, apoi să vedeți elementele „Dimensiune și proprietăți...”, „Setări Slicer”, „Atribuiți o macrocomandă” în meniul contextual, activat prin clic dreapta al mouse-ului sau Elementul „Opțiuni” din meniul principal. Deci, este posibil să setați numărul de coloane pentru elementele (butoanele) feliei, dimensiunile butoanelor feliei și panoului, să definiți pentru felie schema de culori și stilul de design din setul existent (sau creați-vă propriul stil), pentru a defini propriul titlu de panou, pentru a atribui o macrocomandă de program prin care puteți extinde funcționalitatea panoului.

Executarea unei interogări MDX din Excel

În primul rând, trebuie să efectuați operația DRILLTHROUGH pe o anumită tastă, de exemplu. coborâți la datele detaliate (datele detaliate sunt afișate pe o foaie separată) și deschideți lista de conexiuni;
Deschideți proprietățile conexiunii, accesați fila „Definiție”;
Selectați tipul de comandă implicit și plasați-o pe cea pregătită în câmpul de text al comenzii. MDX Anchetă;
Când dați clic pe butonul după verificarea sintaxei cererii și deținerea drepturilor de acces corespunzătoare, cererea va fi executată pe server, iar rezultatul va fi prezentat în foaia curentă sub forma unui tabel plat obișnuit.
Puteți vizualiza textul interogării MDX generată de Excel instalând un add-on gratuit care oferă și alte funcționalități suplimentare.

Traducerea în alte limbi

Cubul analitic acceptă localizarea în rusă și engleză (dacă este necesar, este posibilă localizarea în alte limbi). Traducerile se aplică numelor de dimensiuni, ierarhii, atribute, foldere, măsuri, precum și elementelor ierarhiilor individuale, dacă există traduceri pentru acestea pe partea sistemelor contabile / stocării datelor. Pentru a schimba limba, trebuie să deschideți proprietățile conexiunii și să adăugați următoarea opțiune la șirul de conexiune:
Proprietăți extinse = „Locale = 1033”
unde 1033 este localizarea în engleză
1049 - localizare în rusă

Extensii suplimentare Excel pentru Microsoft OLAP

Capacitatea de a lucra cu cuburi Microsoft OLAP va crește dacă utilizați extensii suplimentare, de exemplu, extensii OLAP PivotTable, datorită cărora puteți utiliza o căutare rapidă după dimensiune:

site-ul 11-01-2011 16:57:00Z Modificat ultima dată: 15-10-2017 16:33:59Z Public de vârstă: 14-70