Pogreška prilikom kreiranja datoteke kocke iz Excela. Stvaranje OLAP kocke pomoću Microsoft Queryja

Riža. 2. OLAP kockasti isječak

Rotacija(Slika 3) - operacija promjene mjesta mjerenja prikazanih u izvješću ili na prikazanoj stranici. Na primjer, operacija rotacije može uključivati ​​preuređivanje redaka i stupaca tablice. Osim toga, rotiranje podatkovne kocke premješta dimenzije izvan tablice na mjesto dimenzija prisutnih na prikazanoj stranici i obrnuto.

kocke OLAP podaci(Online Analytical Processing - analiza operativnih podataka) omogućuju učinkovito izdvajanje i analizu višedimenzionalnih podataka. Za razliku od drugih vrsta baza podataka, OLAP baze podataka dizajnirane su posebno za analitičku obradu i brzo izdvajanje svih vrsta skupova podataka iz njih. Zapravo postoji nekoliko ključnih razlika između standardnih relacijskih baza podataka kao što su Access ili SQL poslužitelj, i OLAP baze podataka.

Riža. 1. Da biste povezali OLAP kocku s Excel radnom knjigom, koristite naredbu Iz Analytics Services

Preuzmite bilješku u or

U relacijskim bazama podataka informacije su predstavljene kao zapisi koji se uzastopno dodaju, brišu i ažuriraju. OLAP baze podataka pohranjuju samo snimku podataka. U OLAP bazi podataka informacije se arhiviraju kao jedan blok podataka i namijenjene su samo za izlaz na zahtjev. Iako možete dodati u OLAP bazu podataka nove informacije, postojeći podaci rijetko se uređuju, a još manje brišu.

Relacijske baze podataka i OLAP baze podataka strukturno se razlikuju. Relacijske baze podataka obično se sastoje od skupa tablica koje su međusobno povezane. U nekim slučajevima relacijska baza podataka sadrži toliko tablica da je vrlo teško odrediti kako su povezane. U OLAP bazama podataka, odnosi između pojedinačnih blokova podataka definirani su unaprijed i pohranjeni u strukturi poznatoj kao OLAP kocke. Podatkovne kocke pohranjuju potpune informacije o hijerarhijskoj strukturi i odnosima baze podataka, što uvelike olakšava navigaciju kroz nju. Osim toga, puno je lakše izraditi izvješća ako unaprijed znate gdje se nalaze podaci koje izdvajate i koji su drugi podaci s njima povezani.

Glavna razlika između relacijskih baza podataka i OLAP baza podataka je način na koji se informacije pohranjuju. Podaci u OLAP kocki rijetko su predstavljeni na općenit način. OLAP podatkovne kocke obično sadrže informacije predstavljene u unaprijed dizajniranom formatu. Dakle, operacije grupiranja, filtriranja, sortiranja i spajanja podataka u kockama se izvode prije njihovog punjenja informacijama. To čini dohvaćanje i prikaz traženih podataka što je moguće jednostavnijim. Za razliku od relacijskih baza podataka, nema potrebe pravilno organizirati informacije prije nego što se prikažu na zaslonu.

OLAP baze podataka obično stvaraju i održavaju IT administratori. Ako vaša organizacija nema strukturu koja je odgovorna za upravljanje OLAP bazama podataka, tada se možete obratiti administratoru relacijske baze podataka sa zahtjevom za implementaciju barem nekih OLAP rješenja na korporativnoj mreži.

Povezivanje s OLAP podatkovnom kockom

Za pristup OLAP bazi podataka prvo morate uspostaviti vezu s OLAP kockom. Započnite odlaskom na karticu Ribbon Podaci. Pritisnite gumb Iz drugih izvora i odaberite naredbu iz padajućeg izbornika Iz Analytics Services(Sl. 1).

Kada odaberete navedenu naredbu čarobnjaka za povezivanje podataka (slika 2). Njegova glavna zadaća je pomoći vam uspostaviti vezu s poslužiteljem koji će se koristiti Excel program prilikom upravljanja podacima.

1. Najprije trebate dati Excelu podatke o registraciji. Unesite naziv poslužitelja u polja dijaloškog okvira, registracijsko ime i lozinku za pristup podacima, kao što je prikazano na sl. 2. Pritisnite gumb Unaprijediti. Ako se povežete pomoću račun Windows, zatim postavite prekidač Koristite Windows autentifikaciju.

2. S padajućeg popisa odaberite bazu podataka s kojom ćete raditi (slika 3). Trenutačni primjer koristi bazu podataka Vodič za usluge Analysis Services. Nakon što odaberete ovu bazu podataka, donji popis od vas traži da uvezete sve OLAP kocke dostupne u njoj. Odaberite traženu podatkovnu kocku i kliknite na gumb Unaprijediti.

Riža. 3. Odaberite radnu bazu podataka i OLAP kocka, koji planirate koristiti za analizu podataka

3. U sljedećem dijaloškom okviru čarobnjaka, prikazanom na sl. 4, morate unijeti opisne informacije o vezi koju stvarate. Sva polja dijaloškog okvira prikazanog na sl. 4 nije potrebno ispuniti. Uvijek možete zanemariti trenutni dijaloški okvir bez da ga ispunite, a to ni na koji način neće utjecati na vašu vezu.

Riža. 4. Promijenite opisne informacije veze

4. Pritisnite gumb Spreman da dovršite stvaranje veze. Na ekranu će se pojaviti dijaloški okvir Uvoz podataka(slika 5). Postavite prekidač izvješće stožerna tablica i kliknite OK za početak stvaranja zaokretne tablice.

Struktura OLAP kocke

Dok stvarate zaokretnu tablicu iz OLAP baze podataka, primijetit ćete da prozor okna zadataka Polja zaokretne tablice razlikovat će se od one za običnu zaokretnu tablicu. Razlog leži u rasporedu zaokretne tablice tako da što bolje odražava strukturu OLAP kocke koja joj je pripojena. Za navigaciju kroz OLAP kocku što je brže moguće, morate se temeljito upoznati s njezinim komponentama i načinom na koji one međusobno djeluju. Na sl. Slika 6 prikazuje osnovnu strukturu tipične OLAP kocke.

Kao što vidite, glavne komponente OLAP kocke su dimenzije, hijerarhije, razine, članovi i mjere:

• Dimenzije. Glavne karakteristike analiziranih elemenata podataka. Uobičajeni primjeri dimenzija uključuju proizvode, kupce i zaposlenike. Na sl. Slika 6 prikazuje strukturu dimenzije Proizvodi.
• Hijerarhije. Unaprijed definirana agregacija razina u određenoj dimenziji. Hijerarhija vam omogućuje stvaranje sažetih podataka i njihovu analizu na različitim razinama strukture, bez zalaženja u odnose koji postoje između tih razina. U primjeru prikazanom na sl. 6, dimenzija proizvoda ima tri razine, koje su agregirane u jednu hijerarhiju kategorija proizvoda.
• Razine. Razine su kategorije koje su agregirane u zajedničku hijerarhiju. Zamislite slojeve kao podatkovna polja koja se mogu ispitivati ​​i analizirati odvojeno jedno od drugog. Na sl. 6 postoje samo tri razine: kategorija, potkategorija i naziv proizvoda.
• članovi. Jedan element podatke unutar dimenzije. Članovima se obično pristupa putem OLAP strukture dimenzija, hijerarhija i razina. U primjeru na Sl. Za razinu naziva proizvoda definirano je 6 članova. Ostale razine imaju svoje članove, koji nisu prikazani u strukturi.
• Mjere- ovo su pravi podaci u OLAP kockama. Mjere su pohranjene u vlastitim dimenzijama koje se nazivaju mjerne dimenzije. Mjerama možete postavljati upite koristeći bilo koju kombinaciju dimenzija, hijerarhija, razina i članova. Taj se postupak naziva mjerama "rezanja".

Sada kada ste upoznati sa strukturom OLAP kocki, bacimo novi pogled na popis polja zaokretne tablice. Organizacija dostupnih polja postaje jasna i ne izaziva nikakve pritužbe. Na sl. Slika 7 prikazuje kako popis polja predstavlja elemente OLAP zaokretne tablice.

Na popisu polja u OLAP zaokretnoj tablici mjere se pojavljuju prve i označene su ikonom zbroja (sigma). Ovo su jedini podatkovni elementi koji mogu biti u regiji VALUE. Nakon njih na popisu su naznačene dimenzije, označene ikonom sa slikom tablice. Naš primjer koristi dimenziju kupca. Ova dimenzija sadrži brojne hijerarhije. Nakon što se hijerarhija proširi, možete vidjeti pojedinačne razine podataka. Da biste vidjeli strukturu podataka OLAP kocke, jednostavno se krećite kroz popis polja u zaokretnoj tablici.

Ograničenja OLAP zaokretnih tablica

Kada radite s OLAP zaokretnim tablicama, zapamtite da komunicirate s izvorom podataka zaokretne tablice u OLAP okruženju Analysis Services. To znači da svaki aspekt ponašanja podatkovne kocke, od dimenzija do mjera koje su uključene u kocku, također kontroliraju OLAP analitičke usluge. Zauzvrat, to dovodi do ograničenja operacija koje se mogu izvesti na OLAP zaokretnim tablicama:

• U područje VRIJEDNOSTI zaokretne tablice ne možete postaviti druga polja osim mjera;
• nemoguće je promijeniti funkciju koja se koristi za zbrajanje;
• Ne možete stvoriti izračunato polje ili izračunatu stavku;
• sve promjene naziva polja poništavaju se odmah nakon uklanjanja polja iz zaokretne tablice;
• Promjena parametara polja stranice nije dopuštena;
• naredba nije dostupna Pokazatistranice;
• opcija onemogućena Pokazatipotpisielementi ako nema polja u području vrijednosti;
• opcija onemogućena Međuzbrojevi prema elementima stranice odabranim filtrom;
• parametar nije dostupan Pozadinazahtjev;
• nakon dvostruki klik Samo prvih 1000 zapisa iz predmemorije zaokretne tablice vraća se u polje VRIJEDNOSTI;
• potvrdni okvir onemogućen Optimizirajtememorija.

Stvaranje autonomnih podatkovnih kocki

U standardnoj zaokretnoj tablici izvorni podaci pohranjeni su na vašem lokalnom tvrdom disku. Stoga njima uvijek možete upravljati, kao i mijenjati strukturu, čak i bez pristupa mreži. Ali to se ni na koji način ne odnosi na OLAP pivot tablice. U OLAP zaokretnim tablicama predmemorija se ne nalazi na lokalnom tvrdom disku. Stoga, odmah nakon prekida veze s lokalna mreža vaša OLAP zaokretna tablica više neće raditi. U takvoj tablici nećete moći pomaknuti niti jedno polje.

Ako i dalje trebate analizirati OLAP podatke kada niste spojeni na mrežu, izradite izvanmrežnu podatkovnu kocku. Ovo je zasebna datoteka koja predstavlja predmemoriju zaokretne tablice. Ova datoteka pohranjuje OLAP podatke koji se pregledavaju nakon prekida veze s lokalnom mrežom. Da biste izradili samostalnu podatkovnu kocku, prvo izradite OLAP zaokretnu tablicu. Postavite kursor u zaokretnu tablicu i kliknite gumb OLAP alati kontekstualna kartica Analiza, uključena u skup kontekstualnih kartica Rad sa zaokretnim tablicama. Odaberite tim Izvanmrežni način rada OLAP(slika 8).

Na ekranu će se pojaviti dijaloški okvir postavke život baterije OLAP(slika 9). Pritisnite gumb Stvorite izvanmrežnu podatkovnu datoteku. Na ekranu će se pojaviti prvi prozor Data Cube File Creation Wizard. Pritisnite gumb Unaprijediti za nastavak postupka.

U drugom koraku (Sl. 10) označite dimenzije i razine koje će biti uključene u podatkovnu kocku. U dijaloškom okviru morate odabrati podatke za uvoz iz OLAP baze podataka. Potrebno je odabrati samo one dimenzije koje će biti potrebne nakon odspajanja računala s lokalne mreže. Što više dimenzija navedete, veća će biti autonomna podatkovna kocka.

Pritisnite gumb Unaprijediti za prelazak na treći korak (slika 11). U ovom prozoru trebate odabrati članove ili podatkovne elemente koji neće biti uključeni u kocku. Ako potvrdni okvir nije odabran, navedena stavka neće biti uvezena i zauzet će nepotreban prostor na vašem lokalnom tvrdom disku.

Odredite mjesto i naziv podatkovne kocke (slika 12). Datoteke podatkovne kocke imaju nastavak .cub.

Nakon nekog vremena Excel će izvanmrežnu podatkovnu kocku spremiti u navedenu mapu. Da biste ga testirali, dvaput kliknite na datoteku, koja će automatski generirati radnu datoteku Excel radne knjige, koji sadrži zaokretnu tablicu povezanu s odabranom podatkovnom kockom. Nakon što je stvorena, možete distribuirati izvanmrežnu podatkovnu kocku svim zainteresiranim korisnicima koji rade u izvanmrežnom LAN načinu rada.

Nakon što se povežete s lokalnom mrežom, možete otvoriti datoteku izvanmrežne podatkovne kocke i ažurirati je i pridruženu podatkovnu tablicu. Imajte na umu da iako se izvanmrežna podatkovna kocka koristi kada nema pristupa mreži, potrebno ju je ažurirati kada se uspostavi mrežna povezanost. Pokušaj ažuriranja izvanmrežne podatkovne kocke nakon što je mrežna veza izgubljena rezultirat će neuspjehom.

Korištenje funkcija podatkovne kocke u zaokretnim tablicama

Funkcije podatkovne kocke koje se koriste u OLAP bazama podataka također se mogu pokrenuti iz zaokretne tablice. U zastarjele verzije U Excelu ste imali pristup funkciji podatkovne kocke tek nakon instaliranja dodatka Analysis Pack. U programu Excel 2013 te su funkcije ugrađene u program i stoga su dostupne za korištenje. Kako bismo u potpunosti razumjeli njihove mogućnosti, pogledajmo konkretan primjer.

Jedan od naj jednostavnih načina Učenje funkcija podatkovne kocke uključuje pretvaranje OLAP zaokretne tablice u formule podatkovne kocke. Ovaj postupak je vrlo jednostavan i omogućuje vam brzo dobivanje formula podatkovne kocke bez njihovog stvaranja ispočetka. Ključni princip je zamijeniti sve ćelije u zaokretnoj tablici formulama koje su povezane s OLAP bazom podataka. Na sl. Slika 13 prikazuje zaokretnu tablicu povezanu s OLAP bazom podataka.

Postavite kursor bilo gdje u zaokretnu tablicu, kliknite gumb OLAP alati kontekstualna kartica vrpce Analiza i odaberite tim Pretvori u formule(slika 14).

Ako vaša zaokretna tablica sadrži polje filtra izvješća, dijaloški okvir prikazan na slici 1 pojavit će se na vašem zaslonu. 15. U ovom prozoru možete odrediti želite li pretvoriti padajuće popise filtera podataka u formule. Ako je odgovor potvrdan, padajući popisi bit će uklonjeni i umjesto njih će se prikazati statičke formule. Ako planirate koristiti padajuće popise u budućnosti za promjenu sadržaja zaokretne tablice, poništite jedini potvrdni okvir u dijaloškom okviru. Ako radite na zaokretnoj tablici u načinu kompatibilnosti, filtri podataka automatski će se pretvoriti u formule, bez prethodnog upozorenja.

Nakon nekoliko sekundi, umjesto zaokretne tablice, vidjet ćete formule koje se pokreću na podatkovnim kockama i daju izlaz u Excel prozoru potrebne informacije. Imajte na umu da ovo uklanja prethodno primijenjene stilove (Sl. 16).

Riža. 16. Pogledajte traku s formulama: ćelije sadrže formule podatkovne kocke

Budući da vrijednosti koje gledate više nisu dio objekta zaokretne tablice, možete dodati stupce, retke i izračunate članove, kombinirati ih s drugim vanjskim izvorima i prilagoditi svoje izvješće. različiti putevi, uključujući formule za povlačenje i ispuštanje.

Dodavanje izračuna u OLAP pivot tablice

U prethodnim verzijama Excela, OLAP zaokretne tablice nisu dopuštale prilagođene izračune. To znači da nije bilo moguće dodati dodatnu razinu analize OLAP zaokretnim tablicama na isti način na koji je moguće dodati izračunata polja i članove uobičajenim zaokretnim tablicama (za više informacija, provjerite jeste li upoznati s ovim materijalom prije nastavka čitanje).

Excel 2013 uvodi nove OLAP alate - izračunate mjere i izračunate članove MDX izraza. Niste više ograničeni na korištenje mjera i članova u vašoj OLAP kocki koju vam daje vaš DBA. Dobivate dodatne mogućnosti analiza stvaranjem prilagođenih izračuna.

Uvod u MDX. Kada koristite zaokretnu tablicu s OLAP kockom, bazi podataka postavljate MDX (višedimenzionalne izraze) upite. MDX je jezik upita koji se koristi za dohvaćanje podataka iz višedimenzionalnih izvora (kao što su OLAP kocke). Kada se OLAP zaokretna tablica promijeni ili ažurira, odgovarajući MDX upiti šalju se u OLAP bazu podataka. Rezultati upita vraćaju se natrag u Excel i prikazuju u području zaokretne tablice. To omogućuje rad s OLAP podacima bez lokalna kopija predmemorija zaokretne tablice.

Kada stvarate izračunate mjere i MDX članove, koristite sintaksu MDX jezika. Koristeći ovu sintaksu, zaokretna tablica omogućuje interakciju s izračunima strana poslužitelja OLAP baze podataka. Primjeri o kojima se govori u knjizi temelje se na osnovnim MDX dizajnima koji pokazuju novo Excel funkcije 2013. Ako trebate stvoriti složene izračunate mjere i članove MDX-a, morat ćete potrošiti vrijeme na učenje više o mogućnostima MDX-a.

Napravite izračunate mjere. Izračunata mjera je OLAP verzija izračunatog polja. Ideja je stvoriti novo podatkovno polje na temelju nekih matematičkih operacija izvedenih na postojećim OLAP poljima. U primjeru prikazanom na sl. 17, koristi se OLAP sumarna tablica koja uključuje popis i količinu robe, kao i prihod od prodaje svake od njih. Moramo dodati novu mjeru koja će izračunati prosječnu cijenu po jedinici artikla.

Analiza Rad sa zaokretnim tablicama. U padajućem izborniku OLAP alati odaberite stavku (Slika 18).

Riža. 18. Odaberite stavku izbornika MDX izračunata mjera

Na ekranu će se pojaviti dijaloški okvir Napravite izračunatu mjeru(Slika 19).

Prati ove korake:

2. Odaberite grupu mjera u kojoj će se nalaziti nova izračunata mjera. Ako to ne učinite, Excel će novu mjeru automatski smjestiti u prvu dostupnu grupu mjera.

3. Na terenu MDX izraz(MDX) Unesite kod koji specificira novu mjeru. Kako biste ubrzali postupak unosa, upotrijebite popis s lijeve strane za odabir postojećih mjera za korištenje u izračunima. Dvaput kliknite željenu mjeru da biste je dodali u MDX polje. Sljedeći MDX koristi se za izračun prosječne jedinične prodajne cijene:

4. Pritisnite U redu.

Obratite pozornost na gumb Provjerite MDX, koji se nalazi u donjem desnom dijelu prozora. Pritisnite ovaj gumb da provjerite je li MDX sintaksa točna. Ako sintaksa sadrži pogreške, pojavljuje se poruka.

Nakon što završite s izradom nove izračunate mjere, idite na popis Polja zaokretne tablice i odaberite ga (slika 20).

Opseg izračunate mjere odnosi se samo na trenutnu radnu knjižicu. Drugim riječima, izračunate mjere ne kreiraju se izravno u kocki OLAP poslužitelja. To znači da nitko neće moći pristupiti izračunatoj mjeri ako vi ne otvorite opći pristup u radnu bilježnicu ili je nećete objaviti na internetu.

Stvorite izračunate MDX članove. MDX izračunati član je OLAP verzija običnog izračunatog člana. Ideja je stvoriti novi podatkovni element na temelju nekih matematičkih operacija izvedenih na postojećim OLAP elementima. U primjeru prikazanom na sl. 22, koristi se OLAP stožerna tablica koja uključuje informacije o prodaji za 2005. – 2008. (s kvartalnom analizom). Recimo da želite agregirati podatke za prvo i drugo tromjesečje stvaranjem novog elementa, Prva polovica godine. Također ćemo kombinirati podatke koji se odnose na treći i četvrti kvartal, tvoreći novi element Druga godina.

Riža. 22. Dodat ćemo nove MDX izračunate članove, prva polovica godine i druga polovica godine

Postavite kursor bilo gdje u zaokretnu tablicu i odaberite kontekstualnu karticu Analiza iz skupa kontekstualnih kartica Rad sa zaokretnim tablicama. U padajućem izborniku OLAP alati odaberite stavku MDX izračunati član(slika 23).

Na ekranu će se pojaviti dijaloški okvir (slika 24).

Riža. 24. Prozor Stvaranje izračunate stavke

Prati ove korake:

1. Imenujte izračunatu mjeru.

2. Odaberite nadređenu hijerarhiju za koju kreirate nove izračunate članove. Na gradilištu Roditeljski element dodijeliti vrijednost svi. Zahvaljujući ovome Excel postavke pristupa svim elementima nadređene hijerarhije kada procjenjuje izraz.

3. U prozoru MDX izraz Unesite MDX sintaksu. Kako biste uštedjeli vrijeme, koristite popis s lijeve strane za odabir postojećih članova za korištenje u MDX-u. Dvaput pritisnite odabranu stavku i Excel će je dodati u prozor MDX izraz. U primjeru prikazanom na sl. 24 izračunava se zbroj prvog i drugog kvartala:

..&& +

.. && +

.. && + …

4. Pritisnite U redu. Excel prikazuje novostvoreni MDX izračunati član u zaokretnoj tablici. Kao što je prikazano na sl. 25, nova izračunata stavka prikazuje se zajedno s ostalim izračunatim stavkama u zaokretnoj tablici.

Na sl. Slika 26 ilustrira sličan proces koji se koristi za stvaranje izračunate stavke za drugu polovicu godine.

Primijetite da Excel niti ne pokušava ukloniti izvorne MDX članove (slika 27). Zaokretna tablica nastavlja prikazivati ​​zapise koji odgovaraju godinama 2005. – 2008., raščlanjene po kvartalima. U ovom slučaju to nije velika stvar, ali u većini scenarija trebali biste sakriti "dodatne" elemente kako biste izbjegli sukobe.

Riža. 27. Excel prikazuje stvoreni MDX izračunati član kao izvorne članove. No ipak je bolje izbrisati izvorne elemente kako biste izbjegli sukobe

Zapamtite: Izračunati članovi nalaze se samo u trenutnoj radnoj knjizi. Drugim riječima, izračunate mjere ne kreiraju se izravno u kocki OLAP poslužitelja. To znači da nitko neće moći pristupiti izračunatoj mjeri ili izračunatom članu osim ako ne podijelite radnu knjigu ili je objavite na mreži.

Imajte na umu da ako se nadređena hijerarhija ili nadređeni element u OLAP kocki promijeni, MDX izračunati element više ne funkcionira. Morat ćete ponovno stvoriti ovaj element.

Upravljanje OLAP izračunima. Excel pruža sučelje koje vam omogućuje upravljanje izračunatim mjerama i MDX članovima u OLAP zaokretnim tablicama. Postavite kursor bilo gdje u zaokretnu tablicu i odaberite kontekstualnu karticu Analiza iz skupa kontekstualnih kartica Rad sa zaokretnim tablicama. U padajućem izborniku OLAP alati odaberite stavku Upravljanje računalom. U prozoru Upravljanje računalom Dostupna su tri gumba (Sl. 28):

• Stvoriti. Stvorite novu izračunatu mjeru ili izračunati MDX član.
• Promijeniti. Promijenite odabrani izračun.
• Izbrisati. Izbrišite odabrani izračun.

Riža. 28. Dijaloški okvir Upravljanje računalom

Izvršite analizu što ako na OLAP podacima. U programu Excel 2013 možete izvršiti analizu što ako na podacima u OLAP zaokretnim tablicama. Zahvaljujući ovome nova prilika Možete promijeniti vrijednosti u zaokretnoj tablici i ponovno izračunati mjere i članove na temelju vaših promjena. Također možete propagirati promjene natrag u OLAP kocku. Kako biste iskoristili mogućnosti analize što-ako, izradite OLAP zaokretnu tablicu i odaberite kontekstualnu karticu Analiza Rad sa zaokretnim tablicama. U padajućem izborniku OLAP alati odaberite tim Što-ako analiza –> Omogućite analizu što ako(Slika 29).

Od ove točke nadalje, možete promijeniti vrijednosti zaokretne tablice. Da biste promijenili odabranu vrijednost u zaokretnoj tablici, desnom tipkom miša kliknite na nju i kontekstni izbornik odaberite stavku (Slika 30). Excel će ponovno pokrenuti sve izračune u zaokretnoj tablici s promjenama koje ste napravili, uključujući izračunate mjere i izračunate MDX članove.

Riža. 30. Odaberite stavku Uzmite promjenu u obzir pri izračunu zaokretne tablice kako biste izvršili izmjene u zaokretnoj tablici

Prema zadanim postavkama, izmjene napravljene u zaokretnoj tablici u načinu analize što ako su lokalne. Ako želite prenijeti promjene na OLAP poslužitelj, odaberite naredbu za objavu promjena. Odaberite kontekstualnu karticu Analiza, koji se nalazi u nizu kontekstualnih kartica Rad sa zaokretnim tablicama. U padajućem izborniku OLAP alati odaberite stavke Što-ako analiza – > Objavi promjene(Slika 31). Pokretanje ove naredbe omogućit će povratno pisanje na OLAP poslužitelju, što znači da se promjene mogu prenijeti na izvornu OLAP kocku. (Da biste prenijeli promjene na OLAP poslužitelj, morate imati odgovarajuća dopuštenja za pristup poslužitelju. Obratite se svom administratoru baze podataka da vam pomogne pri dobivanju dopuštenja za pristup pisanju u OLAP bazu podataka.)

Bilješka je napisana prema knjizi Jelen, Alexander. . Poglavlje 9

Treći članak posvećen je obradi velike količine podataka s koristeći Excel, opisuje prednosti korištenja zaokretnih tablica. Općenito, ovaj je članak trebao biti prvi u nizu, ako govorimo o prednostima ove ili one metode rada. Doista, sučelje zaokretne tablice posebno je stvoreno za analizu velikih količina podataka koji se mogu pohraniti ne samo u rasponima proračunskih tablica, već iu vanjskim izvorima podataka. Razumijevanje principa rada i praktična uporaba pivot tablica može znatno optimizirati svakodnevni rad ekonomista. Povećanje razine analize podataka, pak, dovodi do boljeg upravljanja poduzećem i donošenja ispravnih upravljačkih odluka od strane menadžera na različitim razinama.

Opća teorijska pitanja o radu sa zaokretnim tablicama i višedimenzionalnoj analizi podataka opisana su u drugom članku na našoj web stranici.

Ovdje ćemo se detaljnije osvrnuti na specifične metode obrade podataka pomoću sučelja zaokretne tablice. Koristite datoteku kao primjer nwdata_pivot.xls.

Korištenje zaokretnih tablica

Uzorkovanje jedinstvenih vrijednosti

Jedan od najpopularnijih zadataka koji se rješava pomoću zaokretne tablice je odabir jedinstvenih vrijednosti s popisa ili niza podataka. Korištenje sučelja zaokretne tablice omogućuje vam da riješite ovaj problem na "najelegantniji" način - bez upotrebe formula.

U primjeru na listu Uzorak prikazuje popis zemalja i broj spominjanja u nizu podataka.

Podatkovno polje mora sadržavati tip operacije – “količina”. Ova vam opcija omogućuje obradu nenumeričkih polja izvornih podataka u području podataka zaokretne tablice. Alternativa operaciji brojanja je standardna funkcija COUNTIF. Stvorite skup jedinstvene vrijednosti samo korištenje formula također je moguće u načelu (vidi dio 1), ali to će zahtijevati vrlo složene formule s izračunatim adresiranjem. Odnosno, korištenje stožerne tablice u ovom zadatku najoptimalniji je način za njegovo rješavanje.

Vrijednosti zbroja

Još jedan popularan zadatak za korištenje sučelja zaokretne tablice je dobivanje ukupnih iznosa za jedinstvene zapise u skupu podataka.

U primjeru na listu Iznos generirani su sumarni podaci o narudžbama za svaku zemlju:

Vrsta operacije "Zbroj" u podatkovnom polju dopušta samo numerička polja. Druge vrste agregacije izvornih podataka gotovo se i ne koriste u praksi.

Da biste riješili problem pomoću standardnih formula, možete koristiti funkciju SUMIF. Očito, poteškoća ne nastaje u konsolidaciji vrijednosti, već, kao u prethodnom primjeru, u odabiru jedinstvenog popisa (u primjeru imena zemalja).

2D analiza

Prethodno opisani primjeri pokazuju analizu podataka temeljenu na jednom kriteriju. Proračunske tablice omogućuju vizualizaciju podataka u dvije dimenzije: stupce i retke. Zaokretne tablice također imaju ova područja prikaza podataka.

U primjeru na listu Stol generirano je izvješće prema zemlji i datumu, koje prikazuje promjene u broju narudžbi tijekom vremena. Imajte na umu da se za polje vrste datuma primjenjuje dodatno grupiranje: prema mjesecu i prema godini.

Zbrajanje po nekoliko kriterija također je moguće putem standardnih Excel funkcija SUMIFS, SUMPROIZVOD, kao i funkcijama za obradu polja (vidi 1. dio). Međutim, ova opcija zahtijeva prethodno poznate vrijednosti parametara - tipke za odabir. Osim toga, izračuni pomoću formula zahtijevaju znatno više vremena, što može dovesti do velikih gubitaka u produktivnosti za velike količine podataka.

Multivarijantna analiza

Osim vizualne analize u području po redovima i stupcima, u zaokretnim tablicama možete koristiti globalni filter po jednom ili više polja izvornih podataka. U tu svrhu namijenjen je posebno područjećelije koje se nalaze iznad zaokretne tablice.

Primjer na listu filtar demonstrira mogućnost pregledavanja podataka za tvrtke u jednoj zemlji pomoću područja filtra zaokretne tablice:

Polje filtra može se premjestiti u područje redaka ili stupaca, što vam omogućuje pregled većeg niza informacija. Uz opisano područje filtriranja, dodatno filtriranje podataka moguće je izvršiti postavljanjem popisa ključnih polja u područjima redaka ili stupaca.

Analog korištenja filtara zaokretne tablice koji koriste formule radnog lista su u većini slučajeva formule za obradu polja.

Primjeri na listovima stožer1 I stožer2 prikazati opcije za prikaz istih informacija pomoću različitih postavki dimenzija zaokretne tablice.

Rad s podacima

Ažuriranje podataka

Zaokretna tablica može se temeljiti i na podacima koji se nalaze u proizvoljnom području ćelija i na vanjskim izvorima podataka. Pogledajmo prvo prvu opciju. Oni. podaci za analizu pohranjuju se u niz radnih ćelija Excel list.

Izvješće u obliku zaokretne tablice može se pripremiti i za jednokratnu i za trajnu upotrebu s varijabilnim skupom izvornih podataka. Potonja opcija pruža korisniku veće mogućnosti za interaktivni rad: potrebno je jednom konfigurirati i formatirati obrazac za izvješćivanje, a zatim će se prilikom uređivanja izvornih podataka automatski izvršiti promjene u konačnom obrascu. U ovom slučaju izvješće ne samo da mijenja podatke, već može dodavati i brisati retke i stupce, što je praktički nemoguće implementirati s formulama na radnom listu.

Čarobnjak zaokretne tablice omogućuje vam da odredite raspon ćelija koje ćete koristiti kao izvor podataka. Ako su novi retci dodani prilikom ažuriranja informacija, oni možda neće biti uključeni u izvor podataka zaokretne tablice i, sukladno tome, neće biti ispravno analizirani. Ovu je značajku prilično teško pratiti kada se obrađuju velike količine podataka.

Možete promijeniti raspon izvora podataka za postojeću zaokretnu tablicu kroz poseban dijaloški okvir u programu Excel 2007-2010. U prethodnim verzijama Excela ova značajka sučelja bila je implementirana u čarobnjaku zaokretne tablice kada se pokretao iz aktivne zaokretne tablice. Nakon otvaranja čarobnjaka morate se vratiti jedan korak unatrag:

Moguće je izvršiti i ispravke izvora podataka programski. Na primjer, kroz prozor izračuna VBA uređivača (Odmah):

Kako ne biste razmišljali o ispravnosti veličine izvornog raspona podataka zaokretne tablice, u početku možete odrediti raspon redaka s velikom marginom tijekom konstrukcije. Na primjer, znajući da očekivani volumen redaka ne prelazi 10 000, možete odmah postaviti ovu vrijednost kao veličinu raspona. U praksi takva redundancija neće dovesti do vidljivih usporavanja rada sučelja PivotTable. Možete sakriti prazne vrijednosti u dimenzijama izvješća. Nedostatak ove metode očituje se, prije svega, kada radite s poljima tipa "datum". Standardno sučelje zaokretne tablice omogućuje implementaciju različitih grupiranja kada radite s tipom "datum" (po mjesecu, po kvartalu), ali ako je dostupno prazne vrijednosti Ove opcije više nisu dostupne jer Excel definira stupac kao tekstualni stupac.

Uz razmatrane metode kontrole izvora podataka, predlažemo konfiguraciju raspona redaka zaokretne tablice aktivnog radnog lista pomoću programskih metoda. Ako izvor podataka zauzima cijelo radno područje lista, tada možete koristiti sljedeću naredbu:

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)

Najpouzdaniji, ali na polagani način, je sekvencijalna provjera redaka izvornog lista nakon koje slijedi popunjavanje svojstva Izvor podataka aktivna stožerna tablica. Imajte na umu da je ovo svojstvo pohranjeno samo u R1C1 adresiranju.

Makronaredbe se mogu pozvati po događaju Radni list_Aktiviraj ili konfigurirajte prečac.

Rad s rezultatima analize

Zaokretna tablica nalazi se u nizu ćelija na Excel radnom listu. Pisanje formula na radnom listu unutar granica zaokretne tablice nije dopušteno, bilo ručno ili korištenjem programskih metoda. Teoretski, moguće je raditi sa ćelijama koje se nalaze unutar granica zaokretne tablice koristeći veze za vanjske formule. Često se u praksi funkcija VLOOKUP koristi i za pretraživanje stupca zaokretne tablice. Ova se metoda mora koristiti s velikim oprezom - sučelje sažetog izvješća zahtijeva promjenu položaja prikazanih podataka u odnosu na pravokutne koordinate radni list bez ikakvog utjecaja na izvor tih podataka. Odnosno, nema jamstva da će veza navedena u formuli unutar zaokretne tablice prikazati ispravnu vrijednost kada daljnji rad s datotekom. Međutim, izvor podataka se možda neće promijeniti.

Dostupno alternativni načini obrada rezultata zaokretne tablice:

1. Kopiranje i lijepljenje vrijednosti zaokretne tablice na drugi list (pomoću " Poseban umetak") uz daljnju pretragu podataka već u ovom formiranom rasponu ćelija. Mnogo je teže narušiti integritet podataka unutar jednostavne tablice nego u tablici sažetka. Očito je glavni nedostatak ovakvog načina rada korištenje ručnih operacija nakon svakog ažuriranja izvora podataka.
2. Koristite funkciju GETPIVOTDATA (Excel 2002 i novije). Ova funkcija uključuje pristup podacima ne prema koordinatama radnog lista, već prema dimenzijama zaokretne tablice. Za izvore podataka tipa OLAP kocka predviđene su posebne funkcije za pristup podacima i dimenzijama: CUBEVALUE, CUBEMEMBER i druge (Excel 2007-2010). Ova metoda je nezgodno i također značajno usporava rad ako trebate dobiti mnogo različitih vrijednosti u zaokretnoj tablici.
3. Isključite se iz zaokretne tablice da biste dobili rezultate. Umjesto toga upotrijebite formule s radnog lista (pogledajte 1. dio). Ova metoda, iako je teška za implementaciju, može biti najprikladnija ako se drugi izračuni temelje na rezultatima i ako se izvor podataka često ažurira.

Verzije sučelja zaokretne tablice

U novom formatu datoteke xlsx (Excel 2007-2010) značajno su promijenjene mogućnosti sučelja zaokretne tablice. U prethodne verzije sučelje (97-2003) napravljene su samo "kozmetičke" promjene:

• Excel 2000 (9.0) – osnovna verzija Sučelje zaokretne tablice.
• Excel XP (10.0) - nova funkcija GETPIVOTDATE
• Excel 2003 (11.0) - Čini se da uopće nisu napravljene promjene
• Excel 2007 (12.0) – nova verzija Sučelje zaokretne tablice s podrškom za proširene raspone. Poboljšana izvedba, promijenjeno izgled sučelje. Zadržana je kompatibilnost sa starim formatom.
• Excel 2010 (14.0) – podrška za PowerPivot dodatak. Rad s ažuriranim OLAP kockama.

Glavne promjene u novom formatu datoteke (2007.-2010.):

• Jedan stupac može sadržavati nekoliko polja pivot tablice, istaknutih uvlakama (komprimirani oblik).
• Kriške Zaokretne tablice omogućuju vam vizualni prikaz trenutnog skupa vrijednosti za filtriranje.
• Dimenzije u području filtra podržavaju višestruki odabir.
• Stavke dimenzija mogu se sakriti/prikazati putem gumba koji se nalaze u istoj ćeliji kao i samo zaglavlje.
• Postoji nekoliko novih parametara u svojstvima polja i tablice.
• Dostupni su stilovi zaokretne tablice kako biste mogli promijeniti izgled svojih izvješća u bilo kojem trenutku.

Da biste bolje razumjeli razlike, preuzmite i otvorite datoteke primjera nwdata_pivot1.xlsx I nwdata_pivot2.xlsx(u arhivi nwdata_pivot.zip). Prva datoteka predstavlja izvješće u starom formatu, druga - u novom, izvorni podaci su isti.

Unutarnja organizacija sučelja zaokretne tablice

Kako bismo bolje razumjeli principe rada zaokretne tablice, pogledajmo unutarnju organizaciju sučelja.

Predmemorija zaokretne tablice

Kada izradite ili ažurirate zaokretnu tablicu, bez obzira na odabranu vrstu izvora, Excel prenosi podatke u međuspremište tzv. predmemorija zaokretne tablice. Struktura organiziranja podataka u predmemoriji omogućuje vam da značajno optimizirate prikupljanje podataka i izračune u zaokretnoj tablici. Pohranjivanje podataka u vlastitu predmemoriju omogućuje vam korištenje različitih izvora podataka uz zadržavanje slične funkcionalnosti.

Podaci u predmemoriji se ažuriraju kada kliknete gumb "Osvježi" sučelja zaokretne tablice (gumb na vrpci ili u kontekstnom izborniku) ili u određenom vremenskom intervalu, ako je takva postavka navedena u parametrima. Način rada Excel izračuni(automatski ili ručno) ni na koji način ne utječe na stožernu tablicu.

Više zaokretnih tablica (ili grafikona) može prikazati podatke iz iste predmemorije. Ova opcija rada koristi se za prikaz više obrazaca za izvješćivanje istih podataka bez korištenja sučelja za postavljanje mjerenja. U ovom slučaju, kada se jedna od tablica ažurira, ona koja se temelji na istoj predmemorije automatski se ponovno gradi.

VBA objekti

Pristup podacima programskim metodama moguć je na razini objekata stožerne tablice – objekt Zaokretna tablica. Ostali objekti PivotTablea odgovorni su za raspored i vizualni prikaz elemenata i podataka. To uključuje zbirke polja: Zaokretna polja, Polja stupaca, Polja redaka, PageFields, DataFields. Opcije vrijednosti polja dostupne su kroz zbirke objekata Zaokretne stavke.

Univerzalna mogućnost pristupa podacima izravno u predmemoriji (objekt PivotCache) iz nekog razloga ne pružaju programeri programa Excel. Logika nije sasvim jasna. Kao što je već navedeno, podaci iz predmemorije pohranjuju se zasebno i čak se mogu vidjeti u xlsx datoteci ako ovu datoteku otvorite kao zip arhivu. Ovisno o vrsti izvora podataka, možete pokušati upotrijebiti svojstvo Izvor podataka(za zaokretne tablice temeljene na rasponu) ili Skup zapisa(za izvore tipa “upit baze podataka”).

Izračunata polja i objekti zaokretne tablice ( Izračunata polja, Izračunate stavke) imaju vlastiti mehanizam izračuna i stablo ovisnosti o formulama, koje nije povezano s formulama radnog lista programa Excel. U praksi preporučujemo, ako je moguće, izbjegavanje velikog broja izračunatih polja u zaokretnim tablicama, jer to dovodi do značajnog usporavanja izračuna. Za izvore podataka u obliku raspona ćelija često možete jednostavno dodati stupac s uobičajenom formulom izvornim podacima, a za upite baze podataka možete dodati izračune izravno u tijelo SQL upita.

Vrste izvora podataka

Globalno, izvori podataka mogu se podijeliti u 3 vrste:

1. Rasponi ćelija
2. Upiti u bazi podataka
3. OLAP kocke i PowerPivot2010 kao jedna od mogućnosti implementacije OLAP mehanizma.

Rasponi

Prva opcija je najčešća u praksi; Prethodni opisi primjera odnose se posebno na podatke pohranjene u nizu ćelija.

Standardno Excel sučelje ne dopušta vam izradu sažetog izvješća na temelju višestrukih raspona ćelija. Razlog za ovo ograničenje nije baš jasan. Postoji sumnja da programeri jednostavno ne mogu ponuditi intuitivno korisničko sučelje za rješavanje ovog problema. Tehnička provedba zadatka ne izgleda previše komplicirano - samo trebate popuniti predmemoriju podataka. U poglavlju Dodaci Naša web stranica predstavlja vlastito rješenje za izradu složenih sažetih izvješća.

Upiti u bazi podataka

Upiti bazi podataka mogu se implementirati pomoću različitih tehničkih mehanizama: Microsoft Query, ADO, ODBC. Bez obzira na sučelje za pristup podacima, objedinjujući faktor ovog rješenja je popunjavanje predmemorije zaokretne tablice izravno iz vanjskog izvora. Prilikom daljnjeg rada sa zaokretnom tablicom, upit se može ponovno izvršiti, nakon čega će se podaci ponovno prenijeti u predmemoriju. Ova metoda omogućuje analizu podataka iz vanjskih izvora (računovodstvenih sustava) u stvarnom vremenu. Ako se veza s izvorom podataka izgubi, analiza se može izvršiti na najnovijim podacima u predmemoriji.

OLAP kocke

OLAP kocka pruža srednju razinu pripreme informacija za višedimenzionalnu analizu u zaokretnim tablicama. Kocka pohranjuje informacije o dostupnim vrstama polja (dimenzija ili podaci), hijerarhijskim ovisnostima polja, agregiranim vrijednostima (međuzbrojevima) i drugim izračunatim elementima. Glavna prednost korištenja kocki u odnosu na izravne upite baze podataka je visoka izvedba jer se podaci premještaju i agregiraju u srednjoj pohrani. Nedostatak je također očit ovu metodu– Podaci OLAP kocke mogu sadržavati zastarjele informacije, što ovisi o postavkama pohrane.

Prije Officea 2007, jednostavna OLAP kocka se mogla pripremiti korištenjem Microsoft pomoć Upit, ali u najnovije verzije Ova značajka je onemogućena iz nepoznatih razloga. Programeri snažno preporučuju korištenje SQL Server Analysis Service za izradu i konfiguraciju OLAP kocki. Preporuka je korisna, ali, prvo, ova je usluga uključena samo u plaćene verzije SQL Servera, i, drugo, zahtijeva ozbiljno proučavanje i sučelja i jezika za obradu MDX upita.

Primjer za ovaj članak prikazuje arhivu nwdata_cube.zip s dvije datoteke nwdata_cube.cub, nwdata_cube.xls. Imajte na umu promjene u sučelju zaokretne tablice kada koristite OLAP kocku kao izvor podataka:

• Prisutnost hijerarhijskih dimenzija, ne postoji mogućnost promjene roditelja i element djeteta na nekim mjestima.
• Nije dopušteno pomicanje dimenzija u područje podataka i obrnuto.
• Međuzbrojevi se prikazuju za sve stavke, a ne prema trenutnom filteru grupe.

PowerPivot

Poseban PowerPivot dodatak dostupan je za Excel 2010, koji je uglavnom alternativni mehanizam za implementaciju OLAP kocki. S PowerPivotom možete obraditi milijune zapisa iz raznih informacijskih datoteka i baza podataka s enormnom izvedbom. Istovremeno, korisničko sučelje za konačnu analizu podataka implementirano je u Excel 2010.

Vrlo je vjerojatno da će ovaj dodatak biti uključen u sljedeću verziju programa Excel kao temeljna funkcija. Zaista se nadamo da ćemo posvetiti poseban članak ili čak niz članaka kako bismo opisali kako PowerPivot radi. Danas su PowerPivot + Excel možda i najviše moćan alat za analizu velikih količina podataka.

Službeno PowerPivot web mjesto.

Zapravo, vrlo je zgodno koristiti višedimenzionalni CUBE kada radite s izvješćima za upravljanje. Prilikom izrade proračunskih formata možete se susresti s problemom multivarijatnih obrazaca (više o tome možete pročitati u Knjizi 8 „Tehnologija postavljanja proračuna u poduzeću“ i u knjizi „Postavljanje i automatizacija upravljačkog računovodstva“).

To je zbog činjenice da učinkovito upravljanje poduzećem zahtijeva sve detaljnije izvješćivanje uprave. Odnosno, sustav koristi sve više različitih analitičkih odjeljaka (in informacijski sustavi analitika je definirana skupom referentnih knjiga).

Naravno, to dovodi do činjenice da menadžeri žele primati izvješća u svim analitičkim dijelovima koji ih zanimaju. To znači da izvještaji moraju nekako “disati”. Drugim riječima, možemo reći da je u ovom slučaju riječ o tome da značenje istog izvješća treba pružiti informacije u različitim analitičkim aspektima. Stoga statična izvješća mnogim modernim menadžerima više ne odgovaraju. Oni trebaju dinamiku koju višedimenzionalni CUBE može pružiti.

Tako je OLAP tehnologija već postala obavezan element u modernim i naprednim informacijskim sustavima. Stoga pri odabiru softverskog proizvoda morate obratiti pozornost na to koristi li OLAP tehnologiju.

Štoviše, morate znati razlikovati prave KOCKE od imitacije. Jedna od takvih simulacija su pivot tablice u MS Excelu. Da, ovaj alat izgleda kao KOCKA, ali to zapravo nije jer se radi o statičnim, a ne dinamičkim tablicama. Osim toga, imaju mnogo lošiju implementaciju mogućnosti izrade izvješća pomoću elemenata iz hijerarhijskih direktorija.

Kako bismo potvrdili relevantnost korištenja CUBE-a u konstrukciji upravljačkog izvješćivanja, možemo citirati najjednostavniji primjer s proračunom prodaje. U primjeru koji se razmatra, za tvrtku su relevantni sljedeći analitički dijelovi: proizvodi, podružnice i kanali prodaje. Ako su ove tri analitike važne za tvrtku, tada se proračun prodaje (ili izvještaj) može prikazati u više verzija.

Treba napomenuti da ako izradite proračunske linije na temelju tri analitička odjeljka (kao u primjeru koji razmatramo), to vam omogućuje stvaranje prilično složenih proračunski modeli i izraditi detaljna izvješća koristeći CUBE.

Na primjer, proračun prodaje može se sastaviti koristeći samo jednu analitiku (imenik). Primjer proračuna prodaje izgrađenog na temelju jedne analitike "Proizvodi" prikazan je na Slika 1.

Riža. 1. Primjer proračuna prodaje izgrađenog na temelju jedne analitike “Proizvodi” u OLAP-CUBE

Isti proračun prodaje može se sastaviti pomoću dvije analitike (direktorija). Na Slika 2.

Riža. 2. Primjer proračuna prodaje izgrađenog na temelju dvije analitike “Proizvodi” i “Grane” u OLAP-CUBE-u programskog paketa INTEGRALA

.

Ako postoji potreba za izgradnjom više detaljna izvješća, tada možete sastaviti isti proračun prodaje pomoću tri analitike (direktorija). Na Slika 3.

Riža. 3. Primjer proračuna prodaje izgrađenog na temelju tri analitike “Proizvodi”, “Poslovnice” i “Prodajni kanali” u OLAP-CUBE-u programskog paketa INTEGRALA

Treba podsjetiti da CUBE koji se koristi za generiranje izvješća omogućuje prikaz podataka u različitim sekvencama. Na Slika 3 Prodajni budžet prvo se “proširuje” po proizvodu, zatim po granama, a zatim po kanalima prodaje.

Isti podaci mogu se prikazati različitim redoslijedom. Na Slika 4 isti prodajni proračun se “proširuje” prvo po proizvodu, zatim po prodajnom kanalu, a zatim po granama.

Riža. 4. Primjer prodajnog proračuna izgrađenog na temelju tri analitike “Proizvodi”, “Distribucijski kanali” i “Poslovnice” u OLAP-CUBE-u programskog paketa INTEGRAL

Na Slika 5 isti prodajni proračun se “razmotava” prvo po granama, zatim po proizvodima, a zatim po kanalima prodaje.

Riža. 5. Primjer proračuna prodaje izgrađenog na temelju tri analitike “Branches”, “Products” i “Sales Channels” u OLAP-CUBE programskom paketu “INTEGRAL”

Zapravo, ovo nisu sve moguće opcije za povlačenje prodajnog proračuna.

Osim toga, morate obratiti pozornost na činjenicu da CUBE omogućuje rad s hijerarhijska struktura referentne knjige. U prikazanim primjerima hijerarhijski direktoriji su “Proizvodi” i “Distribucijski kanali”.

Sa stajališta korisnika, on to i jest u ovom primjeru prima nekoliko izvješća o upravljanju (vidi Riža. 1-5), a sa stajališta postavki u softverski proizvod- ovo je jedno izvješće. Jednostavno korištenjem CUBE-a možete ga vidjeti na nekoliko načina.

Naravno, u praksi je moguć vrlo velik broj opcija za ispis različitih izvješća o upravljanju ako se njihovi članci temelje na jednom ili više analitičara. A sam skup analitike ovisi o potrebama korisnika za detaljima. Istina, ne bismo trebali zaboraviti da, s jedne strane, što je veći analitičar, to se detaljnija izvješća mogu izraditi. No, s druge strane, to znači da će model financijskog proračuna biti složeniji. U svakom slučaju, ako postoji KUB, tvrtka će imati priliku pregledati potrebna izvješća u različitim verzijama, u skladu s analitičkim dijelovima od interesa.

Potrebno je spomenuti još nekoliko karakteristika OLAP-CUBE-a.

U višedimenzionalnom hijerarhijskom OLAP-CUBE-u postoji nekoliko dimenzija: tip retka, datum, redovi, direktorij 1, direktorij 2 i direktorij 3 (vidi. Riža. 6). Naravno, izvješće prikazuje onoliko gumba s referentnim knjigama koliko ih ima u proračunskoj liniji koja sadrži maksimalan iznos referentne knjige. Ako ni u jednoj proračunskoj liniji nema niti jedne referentne knjige, izvješće neće imati niti jedan gumb s referentnim knjigama.

U početku se OLAP-CUBE gradi duž svih dimenzija. Prema zadanim postavkama, kada se izvješće inicijalno sastavlja, dimenzije se nalaze točno u područjima prikazanim u Slika 6. Odnosno, dimenzija kao što je "Datum" nalazi se u području okomitih dimenzija (dimenzije u području stupaca), dimenzije "Redovi", "Imenik 1", "Imenik 2" i "Imenik 3" - u područje vodoravnih dimenzija (dimenzije u redovima područja), a dimenzija “Row Type” je u području “neproširenih” dimenzija (dimenzije u području stranice). Ako je dimenzija u zadnjem području, tada se podaci u izvješću neće "proširiti" na tu dimenziju.

Svaka od ovih dimenzija može se smjestiti u bilo koje od tri područja. Nakon prijenosa mjerenja, izvješće se odmah obnavlja prema nova konfiguracija mjerenja. Na primjer, možete zamijeniti datum i retke s referentnim knjigama. Ili možete premjestiti jednu od referentnih knjiga u okomito područje mjerenja (pogledajte. Riža. 7). Drugim riječima, možete "izvrnuti" izvješće u OLAP-CUBE i odabrati opciju izlaza izvješća koja je najprikladnija za korisnika.

Riža. 7. Primjer rekonstrukcije izvješća nakon promjene konfiguracije mjerenja programskog paketa INTEGRALA

Konfiguracija mjerenja može se promijeniti ili u glavnom obrascu CUBE ili u uređivaču mape promjena (pogledajte. Riža. 8). U ovom uređivaču također možete povući i ispustiti mjerenja s jednog područja na drugo pomoću miša. Osim toga, možete zamijeniti mjerenja u jednom području.

Osim toga, u istom obrascu možete konfigurirati neke mjerne parametre. Za svaku dimenziju možete prilagoditi mjesto ukupnih zbrojeva, redoslijed sortiranja elemenata i nazive elemenata (pogledajte. Riža. 8). Također možete odrediti koji će se naziv elementa prikazati u izvješću: skraćeni (Name) ili puni (FullName).

Riža. 8. Editor mjernih karata programskog paketa INTEGRAL

Parametre mjerenja možete uređivati ​​izravno u svakom od njih (pogledajte. Riža. 9). Da biste to učinili, kliknite na ikonu koja se nalazi na gumbu pored naziva mjerenja.

Riža. 9. Primjer uređivanja imenika 1 Proizvodi i usluge u

Pomoću ovog editora možete odabrati elemente koje želite prikazati u izvješću. Standardno se u izvješću prikazuju svi elementi, no po potrebi se neki elementi ili mape mogu izostaviti. Na primjer, ako trebate prikazati samo jednu grupu proizvoda u izvješću, tada morate poništiti odabir svih ostalih u uređivaču mjerenja. Nakon toga, izvješće će sadržavati samo jednu grupu proizvoda (vidi. Riža. 10).

Također možete sortirati elemente u ovom editoru. Osim toga, elementi se mogu preuređivati različiti putevi. Nakon takvog pregrupiranja, izvješće se trenutačno ponovno gradi.

Riža. 10. Primjer ispisa u izvješću samo jedne grupe proizvoda (mape) u programskom paketu INTEGRALA

U uređivaču dimenzija možete brzo kreirati vlastite grupe, povući i ispustiti elemente iz direktorija, itd. Prema zadanim postavkama automatski se stvara samo grupa Ostalo, ali se mogu kreirati i druge grupe. Stoga, pomoću uređivača dimenzija, možete konfigurirati koji će elementi referentnih knjiga i kojim redoslijedom biti prikazani u izvješću.

Zapravo, sve takve promjene mogle su se napraviti na početku prilikom postavljanja linija.

Na primjer, korištenjem ograničenja također možete odrediti koji elementi ili grupe direktorija trebaju biti prikazani u izvješću, a koji ne.

Bilješka: tema ovog članka detaljnije se obrađuje na radionicama "Upravljanje proračunom poduzeća" I "Organizacija i automatizacija upravljačkog računovodstva" koju je proveo autor ovog članka Alexander Karpov.

Ako korisnik gotovo redovito treba prikazati samo određene elemente ili mape direktorija u izvješću, tada je bolje napraviti takve postavke unaprijed prilikom kreiranja redaka izvješća. Ukoliko su korisniku važne različite kombinacije elemenata imenika u izvješćima, tada nema potrebe postavljati nikakva ograničenja prilikom postavljanja metodologije. Sva takva ograničenja mogu se brzo konfigurirati pomoću uređivača mjerenja.

Samostalna datoteka kocke (.cub) pohranjuje podatke u obliku u kocki online analitičke obrade (OLAP). Ovi podaci mogu predstavljati dio OLAP baze podataka s OLAP poslužitelja ili mogu biti stvoreni neovisno o bilo kojoj OLAP bazi podataka. Da biste nastavili raditi s izvješćima zaokretne tablice i zaokretnog grafikona kada je poslužitelj nedostupan ili kada je izvan mreže, upotrijebite datoteku izvanmrežne kocke.

Saznajte više o offline kockama

Kada radite s izvješćem zaokretne tablice ili zaokretnog grafikona koje se temelji na izvoru podataka s OLAP poslužitelja, upotrijebite čarobnjaka za izvanmrežnu kocku za kopiranje izvornih podataka u zasebnu izvanmrežnu datoteku kocke na vašem računalu. Da biste stvorili ove izvanmrežne datoteke, morate imati davatelja OLAP podataka koji podržava ove mogućnosti, kao što je MSOLAP iz Microsoft SQL Server Analysis Services, instaliran na vašem računalu.

Bilješka: Stvaranje i korištenje samostalnih datoteka kocke iz Microsoft SQL Server Analysis Services podliježe uvjetima instalacije i licenciranju Microsoft SQL Servera. Pregledajte odgovarajuće informacije o licenciranju za svoju verziju SQL Servera.

Korištenje čarobnjaka za izvanmrežnu kocku

Da biste stvorili izvanmrežnu datoteku kocke, upotrijebite čarobnjak za izvanmrežnu kocku da odaberete podskup podataka u OLAP bazi podataka, a zatim spremite taj skup. Izvješće ne mora uključivati ​​sva polja uključena u datoteku, a možete odabrati bilo koju od njegovih dimenzija i podatkovnih polja dostupnih u OLAP bazi podataka. Da biste smanjili veličinu datoteke, možete uključiti samo podatke koje želite prikazati u izvješću. Možete preskočiti sve dimenzije i, za većinu vrsta mjerenja, također izostaviti više od niska razina detalja i elemenata vrhunska razina, koje nije potrebno prikazivati. Za izvanmrežnu datoteku, svi elementi koji se mogu uključiti u polja svojstava koja su dostupna u bazi podataka za te elemente također se spremaju.

Prebacivanje podataka izvan mreže i njihovo ponovno vraćanje na mrežu

Da biste to učinili, prvo morate stvoriti izvješće zaokretne tablice ili izvješće zaokretnog grafikona koje se temelji na bazi podataka poslužitelja, a zatim iz izvješća izraditi samostalnu datoteku kocke. Naknadno, kada radite s izvješćem, možete se prebacivati ​​između baze podataka poslužitelja i izvanmrežne datoteke u bilo kojem trenutku (na primjer, kada radite na prijenosno računalo kod kuće ili na putu i zatim ponovno uspostavljanje veze računala s mrežom).

Sljedeće opisuje osnovne korake za prebacivanje podataka izvan mreže i njihovo vraćanje na mrežu.

Bilješka:

Kliknite izvješće zaokretne tablice. Ako je ovo izvješće zaokretne tablice, odaberite povezano izvješće zaokretne tablice.

Na "kartici" Analiza" u grupi kalkulacije kliknite gumb OLAP usluga i pritisnite tipku Izvanmrežni OLAP.

Odaberite stavku OLAP s mogućnošću povezivanja a zatim kliknite gumb u redu.

Ako se od vas zatraži da pronađete izvor podataka, kliknite Pronađite izvor i pronađite OLAP poslužitelj na mreži.

Kliknite izvješće zaokretne tablice koje se temelji na datoteci izvanmrežne kocke.

U programu Excel 2016: na kartici " podaci" u grupi zahtjeva i veza Ažuriraj sve i pritisnite tipku Ažuriraj.

U programu Excel 2013: na kartici " podaci" u grupi veze kliknite na strelicu pokraj gumba Ažuriraj sve i pritisnite tipku Ažuriraj.

Na "kartici" Analiza" u grupi kalkulacije kliknite gumb OLAP usluga i pritisnite tipku Izvanmrežni OLAP.

Pritisnite gumb Izvanmrežni OLAP način rada, i onda - .

Bilješka: Stop u dijaloškom okviru.

Upozorenje:

Stvaranje izvanmrežne datoteke kocke iz baze podataka OLAP poslužitelja

Bilješka: Ako je OLAP baza podataka velika i potrebna je kockasta datoteka za pristup velikom podskupu podataka, puno slobodan prostor na disk, a spremanje datoteke može potrajati dugo. Kako biste poboljšali izvedbu, preporučuje se da izradite samostalne datoteke kocke pomoću MDX skripte.

Problem: Moje računalo nema dovoljno prostora na disku prilikom spremanja kocke.

OLAP baze podataka dizajnirane su za upravljanje velikim količinama detaljnih podataka, tako da baza podataka smještena na poslužitelju može zauzeti znatno više prostora nego što je dostupno na vašem lokalnom tvrdom disku. Ako odaberete veliku količinu podataka za izvanmrežnu podatkovnu kocku, možda nećete imati dovoljno slobodnog prostora na disku. Sljedeći pristup pomoći će smanjiti veličinu izvanmrežne datoteke kocke.

Oslobodite prostor na disku ili odaberite drugi disk Prije spremanja datoteke kocke uklonite je s diska. nepotrebne datoteke ili spremite datoteku na mrežni pogon.

Uključivanje manje podataka u izvanmrežnu datoteku kocke Razmislite o tome kako možete smanjiti količinu podataka uključenih u datoteku tako da datoteka sadrži sve podatke potrebne za izvješće zaokretne tablice ili zaokretni grafikon. Pokušajte sljedeće.

Povezivanje izvanmrežne datoteke kocke s bazom podataka OLAP poslužitelja

Ažuriranje i ponovno stvaranje izvanmrežne datoteke kocke

Ažuriranje datoteke izvanmrežne kocke koja je stvorena iz najnovijih podataka dobivenih iz kocke poslužitelja ili iz nove datoteke izvanmrežne kocke može potrajati značajno vrijeme i zahtijevati veliku količinu privremenog prostora na disku. Pokrenite ovaj postupak kada vam nije potreban trenutni pristup drugim datotekama, nakon što provjerite imate li dovoljno prostora na tvrdom disku.

Problem: Novi podaci se ne pojavljuju u izvješću kada se osvježi.

Provjera dostupnosti izvorne baze podataka Izvanmrežna datoteka kocke možda se neće moći povezati s bazom podataka izvornog poslužitelja za dobivanje novih podataka. Provjerite da izvorna baza podataka na poslužitelju koji je izvor podataka za kocku nije preimenovana ili premještena na drugu lokaciju. Provjerite je li poslužitelj dostupan i može li se s njim povezati.

Provjera novih podataka Provjerite sa svojim administratorom baze podataka jesu li podaci koji bi trebali biti uključeni u izvješće ažurirani.

Provjera nepromjenjivosti organizacije baze podataka Ako je kocka OLAP poslužitelja izmijenjena, možda ćete trebati reorganizirati izvješće, stvoriti izvanmrežnu datoteku kocke ili pokrenuti čarobnjak za stvaranje OLAP kocke da biste pristupili promijenjenim podacima. Da biste saznali više o promjenama baze podataka, obratite se svom administratoru baze podataka.

Uključivanje drugih podataka u izvanmrežnu datoteku kocke

Spremanje modificirane izvanmrežne datoteke kocke može biti dugotrajno i zahtijeva rad Microsoft Excel nije moguće tijekom spremanja datoteke. Pokrenite ovaj postupak kada vam nije potreban trenutni pristup drugim datotekama, nakon što provjerite imate li dovoljno prostora na tvrdom disku.

Provjerite postoji li mrežna veza i je li dostupna izvorna baza podataka OLAP poslužitelja iz koje je izvanmrežna datoteka kocke dobila podatke.

Pritisnite izvješće zaokretne tablice stvoreno iz samostalne datoteke kocke ili povezano izvješće zaokretne tablice za izvješće zaokretnog grafikona.

Na kartici Mogućnosti u grupi Servis kliknite gumb OLAP usluga i pritisnite tipku Izvanmrežni OLAP način rada.

Pritisnite gumb Izvanmrežni OLAP način rada, i onda - Uredite izvanmrežnu podatkovnu datoteku.

Slijedite čarobnjaka za izvanmrežnu kocku kako biste odabrali druge podatke koje želite uključiti u ovu datoteku. U zadnjem koraku navedite naziv i put do datoteke koju želite promijeniti.

Bilješka: Za odustajanje od spremanja datoteke kliknite gumb Stop u dijaloškom okviru Izrada kockaste datoteke - napredak.

Brisanje offline datoteke kocke

Upozorenje: Ako izbrišete datoteku izvanmrežne kocke za izvješće, više ne možete koristiti to izvješće izvan mreže i više ne možete stvoriti datoteku izvanmrežne kocke za to izvješće.

Zatvorite sve radne knjige koje sadrže izvješća koja koriste izvanmrežnu datoteku kocke ili osigurajte da su sva takva izvješća izbrisana.

U Microsoft Windows Pronađite i izbrišite izvanmrežnu datoteku kocke (CUB datoteka).

dodatne informacije

Uvijek možete postaviti pitanje stručnjaku Excel Tech Community, zatražiti pomoć u Answers zajednici i predložiti nova značajka ili poboljšanja na web stranici