Primjer izrade kocke u VBA. OLAP-CUBE (dinamičko upravljanje izvješćivanjem)

Samostalna datoteka kocke (.cub) pohranjuje podatke u obliku u kocki online analitičke obrade (OLAP). Ovi podaci mogu predstavljati dio baze podataka OLAP podaci s OLAP poslužitelja ili se može stvoriti 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 izvanmrežnih datoteka kocke iz Microsoft SQL Server Analysis Services, podložno uvjetima i licenciranju Microsoftove instalacije SQL poslužitelj. 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 trebate 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 način rada OLAP, 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

OLAP (Online Analytical Processing) podatkovne kocke omogućuju vam 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 Server, i OLAP baza 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 pojednostavljuje navigaciju kroz nju. Osim toga, mnogo je lakše kreirati izvješća ako unaprijed znate gdje se nalaze podaci koje izdvajate i koji su drugi podaci povezani s njima.

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 kocku koju 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 zaokretne tablice 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 na stožerne tablice OLAP

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 stožernu tablicu i kliknite na gumb OLAP alati kontekstualna kartica Analiza, uključena u skup kontekstualnih kartica Rad sa zaokretnim tablicama. Odaberite tim Izvanmrežni OLAP način rada(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 vrijemeć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 prethodne verzije Excel 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 poslužiteljski dio 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 Do radna bilježnica ili ga 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 novi element 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. Ova postavka omogućuje Excelu pristup svim članovima nadređene hijerarhije prilikom procjene izraza.

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 odaberite stavku iz kontekstnog izbornika (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

U prethodnom članku u ovoj seriji (vidi br. 2’2005) govorili smo o glavnim inovacijama analitičkih usluga u SQL Serveru 2005. Danas ćemo pobliže pogledati alate za izradu OLAP rješenja uključenih u ovaj proizvod.

Ukratko o osnovama OLAP-a

Prije nego počnemo govoriti o alatima za izradu OLAP rješenja, podsjetimo da je OLAP (On-Line Analytical Processing) tehnologija za složenu višedimenzionalnu analizu podataka, čiji je koncept 1993. opisao E.F. Codd, poznati autor relacijski model podaci. Trenutno je podrška za OLAP implementirana u mnogim DBMS-ovima i drugim alatima.

OLAP kocke

Što su OLAP podaci? Da bismo odgovorili na ovo pitanje, razmotrimo jednostavan primjer. Pretpostavimo da u korporativnoj bazi podataka određenog poduzeća postoji skup tablica koje sadrže podatke o prodaji robe ili usluga, a na njihovoj osnovi kreiran je prikaz Računi s poljima Country (država), City (grad), CustomerName (naziv tvrtke klijenta), Salesperson (voditelj prodaje), OrderDate (datum slanja narudžbe), CategoryName (kategorija proizvoda), ProductName (naziv proizvoda), ShipperName (prijevoznik), ExtendedPrice (plaćanje robe), dok posljednje od ovih polja je, zapravo, predmet analize.

Odabir podataka iz takvog prikaza može se izvršiti pomoću sljedećeg upita:

ODABERITE državu, grad, ime kupca, prodavača,

Datum narudžbe, naziv kategorije, naziv proizvoda, naziv pošiljatelja, proširena cijena

IZ Fakture

Pretpostavimo da nas zanima ukupna vrijednost narudžbi kupaca iz različitih zemalja. Da biste dobili odgovor na ovo pitanje potrebno je podnijeti sljedeći zahtjev:

SELECT Country, SUM (ExtendedPrice) FROM Fakture

GRUPIRAJ PO zemlji

Rezultat ovog upita bit će jednodimenzionalni skup agregiranih podataka (u ovom slučaju zbrojeva):

Zemlja	SUM (proširena cijena)
Argentina	7327.3
Austrija	110788.4
Belgija	28491.65
Brazil	97407.74
Kanada	46190.1
Danska	28392.32
Finska	15296.35
Francuska	69185.48
	209373.6
	...

Ako želimo znati ukupnu cijenu narudžbi koje su poslali kupci iz različitih zemalja i isporučile različite dostavne službe, moramo pokrenuti upit koji sadrži dva parametra u klauzuli GROUP BY:

ODABERITE zemlju, ime pošiljatelja, SUM (proširena cijena) IZ faktura

GRUPIRAJ PO DRŽAVI, Nazivu pošiljatelja

Na temelju rezultata ovog upita možete izraditi tablicu koja izgleda ovako:

Ovaj skup podataka naziva se zaokretna tablica.

ODABERITE Zemlju, ime pošiljatelja, SUM prodajne osobe (proširena cijena) IZ faktura

GRUPIRAJ PO DRŽAVI, nazivu pošiljatelja, godini

Na temelju rezultata ovog upita može se konstruirati trodimenzionalna kocka (slika 1).

Dodavanje Dodatne mogućnosti za analizu možete izraditi kocku s teoretski bilo kojim brojem dimenzija, a uz zbrojeve ćelije OLAP kocke mogu sadržavati i rezultate izračuna drugih agregatnih funkcija (npr. prosječne, maksimalne, minimalne vrijednosti, broj zapisi izvornog prikaza, odgovarajući ovaj skup parametri). Polja iz kojih se izračunavaju rezultati nazivaju se mjere kocke.

Hijerarhije u dimenzijama

Pretpostavimo da nas ne zanima samo ukupna vrijednost narudžbi kupaca u različite zemlje, ali i ukupni trošak narudžbi kupaca u različitim gradovima iste zemlje. U ovom slučaju možete iskoristiti činjenicu da vrijednosti iscrtane na osi imaju različite razine detalja - to je opisano unutar koncepta hijerarhije promjena. Recimo da se države nalaze na prvoj razini hijerarhije, a gradovi na drugoj. Imajte na umu da počevši od SQL Servera 2000, usluge analize podržavaju takozvane neuravnotežene hijerarhije, koje sadrže, na primjer, članove čija "djeca" nisu sadržana na susjednim razinama hijerarhije ili nedostaju za neke članove promjene. Tipičan primjer takve hijerarhije je uzimanje u obzir činjenice da u različitim zemljama mogu, ali ne moraju postojati administrativno-teritorijalne jedinice kao što su država ili regija, smještene u geografskoj hijerarhiji između zemalja i gradova (slika 2).

Imajte na umu da je nedavno bilo uobičajeno razlikovati tipične hijerarhije, na primjer one koje sadrže geografske ili vremenske podatke, kao i podržavati postojanje nekoliko hijerarhija u jednoj dimenziji (osobito za kalendarsku i fiskalnu godinu).

Stvaranje OLAP kocki u SQL Serveru 2005

SQL Server 2005 kocke se stvaraju pomoću koristeći SQL Server Business Intelligence Development Studio. Ovaj alat je posebna verzija Vizualni studio 2005, dizajniran za rješavanje ove klase zadatke (a ako već imate instalirano razvojno okruženje, popis predložaka projekata nadopunjuje se projektima dizajniranim za izradu rješenja temeljenih na SQL Severu i njegovim analitičkim uslugama). Konkretno, predložak Analysis Services Project dizajniran je za kreiranje rješenja temeljenih na analitičkim uslugama (slika 3).

Da biste izradili OLAP kocku, prvo morate odlučiti od kojih podataka ćete je oblikovati. Najčešće se OLAP kocke grade na temelju relacijskih skladišta podataka sa shemama zvijezda ili pahuljica (o njima smo govorili u prethodnom dijelu članka). SQL paket uključuje primjer takve pohrane: bazu podataka AdventureWorksDW, za korištenje koje kao izvora trebate pronaći mapu Data Sources u Solution Exploreru, odaberite kontekstni izbornik Novi izvor podataka i dosljedno odgovarati na pitanja odgovarajućeg čarobnjaka (slika 4).

Zatim se preporuča kreirati Data Source View na kojem će se kreirati kocka. Da biste to učinili, trebate odabrati odgovarajuću stavku kontekstnog izbornika u mapi Data Source Views i dosljedno odgovarati na pitanja čarobnjaka. Rezultat ovih radnji bit će podatkovna shema, uz pomoć koje će se izgraditi prikaz izvora podataka, au rezultirajućoj shemi, umjesto izvornih, možete navesti "prijateljska" imena tablica (Sl. 5) .

Ovako opisanu kocku moguće je prenijeti na poslužitelj analitičkih usluga odabirom opcije Deploy iz kontekstnog izbornika projekta i pregledom njenih podataka (Slika 7).

Pri izradi kocki trenutno se koriste mnoge značajke nova verzija SQL Server, kao što je prikaz izvora podataka. Opis izvornih podataka za konstruiranje kocke, kao i opis strukture kocke, sada se radi pomoću alata Visual Studio poznatog mnogim programerima, što je značajna prednost nove verzije ovog proizvoda - proučavanje novih alata od strane programera analitičkih rješenja u ovom slučaju je minimizirano.

Imajte na umu da u kreiranoj kocki možete mijenjati sastav mjera, brisati i dodavati atribute dimenzija te dodavati izračunate atribute članova dimenzija na temelju postojećih atributa (Sl. 8).

Riža. 8. Dodajte izračunati atribut

Osim toga, SQL Server 2005 kocke mogu automatski grupirati ili sortirati članove dimenzije prema vrijednosti atributa, definirati odnose između atributa, implementirati odnose više-prema-više, odrediti ključne poslovne metrike i još mnogo toga (saznajte kako se svi ovi koraci mogu pronaći u Vodič za SQL Server Analysis Services sustav pomoći ovog proizvoda).

U sljedećim dijelovima ove publikacije nastavit ćemo istraživati analitičke usluge SQL Servera 2005 i saznati što je novo u području podrške za Data Mining.

Možda će nekima korištenje OLAP tehnologije (On-line Analytic Processing) pri izradi izvješća djelovati pomalo egzotično, pa im korištenje OLAP-CUBE uopće nije jedan od najvažnijih zahtjeva pri automatizaciji proračuna i upravljačkog računovodstva.

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 pri konstruiranju upravljačkog izvješća, možemo dati jednostavan 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 softverskom proizvodu, 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.

Valja napomenuti da se sva takva preslagivanja ne bilježe. Odnosno, nakon zatvaranja izvješća ili nakon njegovog ponovnog izračuna, svi imenici će biti prikazani u izvješću u skladu s konfiguriranom metodologijom.

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.

Napomena: Ovo predavanje pokriva osnove projektiranja podatkovnih kocki za OLAP skladišta podataka. Primjer prikazuje način konstruiranja podatkovne kocke pomoću CASE alata.

Svrha predavanja

Nakon proučavanja gradiva na ovom predavanju znat ćete:

u čemu je podatkovna kocka OLAP skladište podataka ;
kako dizajnirati podatkovnu kocku za OLAP skladišta podataka ;
što je dimenzija podatkovne kocke;
kako je činjenica povezana s podatkovnom kockom;
što su atributi dimenzije;
što je hijerarhija;
što je metrika podatkovne kocke;

i nauči:

izgraditi višedimenzionalne karte ;
dizajn jednostavan višedimenzionalne karte.

Uvod

OLAP tehnologija nije jedinstvena softver, ne programski jezik. Ako pokušamo pokriti OLAP u svim njegovim pojavnim oblicima, onda je to skup koncepata, načela i zahtjeva koji su u osnovi softverski proizvodi, što analitičarima olakšava pristup podacima.

Analitičari su glavni potrošači korporativnih informacija. Posao analitičara je pronaći uzorke u velikim količinama podataka. Stoga se analitičar neće obazirati na pojedinačnu činjenicu da je određenog dana serija kemijskih olovaka prodana kupcu Ivanovu - njemu su potrebne informacije o stotinama i tisućama sličnih događaja. Pojedinačne činjenice u skladištu podataka mogu biti od interesa, na primjer, za računovođu ili voditelja odjela prodaje, čija je nadležnost podrška određenom ugovoru. Analitičaru jedan zapis nije dovoljan - on će, na primjer, možda trebati informacije o svim ugovorima prodajnog mjesta za mjesec, tromjesečje ili godinu. Analitičar možda neće biti zainteresiran za kupčev TIN ili njegov telefonski broj - on radi s određenim numeričkim podacima, što je bit njegove profesionalne djelatnosti.

Centralizacija i prikladno strukturiranje nisu sve što je potrebno analitičaru. Treba mu alat za pregled i vizualizaciju informacija. Tradicionalnim izvješćima, čak i onima izgrađenim na temelju jedinstvenog skladišta podataka, nedostaje određena fleksibilnost. Ne mogu se "izokrenuti", "proširiti" ili "skupiti" da bi se dobio željeni prikaz podataka. Što više "odsječaka" i "odsječaka" podataka analitičar može istražiti, to više ideja ima, koje zauzvrat zahtijevaju sve više i više "odsječaka" za provjeru. OLAP služi kao takav alat za analizu podataka od strane analitičara.

Iako OLAP nije nužan atribut skladišta podataka, sve se više koristi za analizu informacija akumuliranih u tom skladištu podataka.

Operativni podaci prikupljaju se iz različitih izvora, čiste, integriraju i pohranjuju u skladište podataka. Štoviše, već su dostupni za analizu pomoću različitih alata za izvješćivanje. Zatim se podaci (cijeli ili djelomično) pripremaju za OLAP analizu. Mogu se učitati u posebnu OLAP bazu podataka ili ostaviti u relacijskoj bazi podataka. Najvažniji element korištenja OLAP-a su metapodaci, odnosno informacije o strukturi, položaju i transformacija podataka. Zahvaljujući njima, osigurana je učinkovita interakcija različitih komponenti za pohranu.

Tako, OLAP se može definirati kao skup alata za višedimenzionalnu analizu podataka akumuliranih u skladištu podataka. Teoretski, OLAP alati mogu se primijeniti izravno na operativne podatke ili njihove točne kopije. Međutim, postoji rizik podvrgavanja podataka analizi koji nisu prikladni za ovu analizu.

OLAP na klijentu i poslužitelju

OLAP se temelji na višedimenzionalnoj analizi podataka. Može se izraditi pomoću različitih alata, koji se mogu podijeliti na klijentske i poslužiteljske OLAP alate.

OLAP klijentski alati su aplikacije koje izračunavaju agregatne podatke (zbrojeve, prosjeke, maksimalne ili minimalne vrijednosti) i prikazuju ih, dok su sami agregatni podaci sadržani u predmemoriji unutar adresnog prostora takvog OLAP alata.

Ako su izvorni podaci sadržani u desktop DBMS-u, izračun skupnih podataka izvodi sam OLAP alat. Ako je izvor izvornih podataka poslužiteljski DBMS, mnogi klijentski OLAP alati šalju poslužitelju SQL upite koji sadrže operator GROUP BY i kao rezultat primaju skupne podatke izračunate na poslužitelju.

U pravilu je OLAP funkcionalnost implementirana u alatima statistička obrada podaci (od proizvoda ove klase na rusko tržište naširoko se koriste proizvodi tvrtki Stat Soft i SPSS) iu nekim proračunskim tablicama. Konkretno, dobre alate za višedimenzionalnu analizu ima Microsoft Excel 2000. Pomoću ovog proizvoda možete stvoriti i spremiti kao datoteku malu lokalnu višedimenzionalnu OLAP kocku i prikazati njezine dvo- ili trodimenzionalne dijelove.

Puno razvojni alati sadrže biblioteke klasa ili komponenti koje vam omogućuju stvaranje aplikacija koje implementiraju jednostavnu OLAP funkcionalnost (kao što su, na primjer, komponente Decision Cube u Borland Delphiju i Borland C++Builderu). Osim toga, mnoge tvrtke nude kontrole ActiveX i druge biblioteke koje implementiraju sličnu funkcionalnost.

Imajte na umu da se klijentski OLAP alati koriste, u pravilu, s malim brojem dimenzija (obično se ne preporučuje više od šest) i malom raznolikošću vrijednosti za ove parametre - na kraju krajeva, rezultirajući skupni podaci moraju stati u adresni prostor takvog alata, a njihov broj eksponencijalno raste kako se broj povećava mjerenja Stoga čak i najprimitivniji klijentski OLAP alati u pravilu omogućuju preliminarni izračun potrebne količine RAM memorija da u njemu stvori višedimenzionalnu kocku.

Mnogi (ali ne svi) OLAP klijentski alati omogućuju vam da spremite sadržaj predmemorije sa skupnim podacima kao datoteku, što vam zauzvrat omogućuje da izbjegnete njihovo ponovno izračunavanje. Imajte na umu da se ova prilika često koristi za otuđenje agregiranih podataka u svrhu njihovog prijenosa drugim organizacijama ili za objavljivanje. Tipičan primjer takvih otuđivih agregatnih podataka je statistika morbiditeta u različitim regijama i u različitim dobnim skupinama, što je otvorene informacije, koju su objavila ministarstva zdravstva raznih zemalja i Svjetska zdravstvena organizacija. Pritom, sami izvorni podaci, koji predstavljaju informacije o konkretnim slučajevima bolesti, povjerljivi su podaci zdravstvenih ustanova i ni u kojem slučaju ne bi smjeli dospjeti u ruke osiguravajućih društava, a još manje postati javni.

Ideja spremanja predmemorije sa skupnim podacima u datoteku dalje je razvijena u poslužiteljskim OLAP alatima, u kojima se spremaju i mijenjaju skupni podaci, kao i podrška za pohranu koja ih sadrži. zasebna aplikacija ili proces koji se naziva OLAP poslužitelj. Klijentske aplikacije mogu zatražiti takvu višedimenzionalnu pohranu i primiti određene podatke kao odgovor. Neke klijentske aplikacije također mogu stvoriti takve trgovine ili ih ažurirati na temelju promijenjenih izvornih podataka.

Prednosti korištenja poslužiteljskih OLAP alata u usporedbi s klijentskim OLAP alatima slične su prednostima korištenja poslužiteljskih DBMS-ova u usporedbi s onima za stolna računala: u slučaju korištenja poslužiteljskih alata, izračun i pohranjivanje agregatnih podataka odvija se na poslužitelju, a klijentska aplikacija prima samo rezultate upita protiv njih, što omogućuje općenito smanjenje mrežnog prometa, vrijeme isporuke zahtjeve i zahtjeve za resursima koje troši aplikacija klijenta. Imajte na umu da se alati za analizu i obradu podataka na razini poduzeća u pravilu temelje na poslužiteljskim OLAP alatima, na primjer, Oracle Express Server, Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, proizvodi iz Crystal Decisions, Business Objects, Cognos, SAS Institut. Budući da svi vodeći proizvođači poslužiteljskih DBMS-ova proizvode (ili imaju licencu od drugih tvrtki) jedan ili drugi poslužiteljski OLAP alat, izbor je prilično širok i u gotovo svim slučajevima možete kupiti OLAP poslužitelj od istog proizvođača kao i sam poslužitelj baze podataka .

Imajte na umu da vam mnogi klijentski OLAP alati (osobito Microsoft Excel 2003, Seagate Analysis, itd.) omogućuju pristup poslužiteljskim OLAP pohranama, djelujući u ovom slučaju kao klijentske aplikacije koje izvode takve upite. Osim toga, postoji mnogo proizvoda koji su klijentske aplikacije za OLAP alate raznih proizvođača.

Tehnički aspekti višedimenzionalne pohrane podataka

Višedimenzionalna skladišta podataka sadrže skupne podatke različitih stupnjeva detalja, na primjer, količine prodaje po danu, mjesecu, godini, po kategoriji proizvoda itd. Svrha pohranjivanja zbirnih podataka je smanjenje vrijeme isporuke zahtjeva, budući da u većini slučajeva za analize i prognoze nisu zanimljivi detaljni, već zbirni podaci. Stoga se pri izradi višedimenzionalne baze podataka uvijek izračunavaju i pohranjuju neki skupni podaci.

Imajte na umu da spremanje svih skupnih podataka nije uvijek opravdano. Činjenica je da kada se dodaju nove dimenzije, količina podataka koji čine kocku eksponencijalno raste (ponekad se govori o “eksplozivnom rastu” količine podataka). Točnije, stupanj rasta volumena agregatnih podataka ovisi o broju dimenzija kocke i članovima dimenzija na različitim razinama hijerarhije tih dimenzija. Za rješavanje problema "eksplozivnog rasta" koriste se različite sheme koje omogućuju postizanje prihvatljive brzine izvršenja upita pri izračunavanju ne svih mogućih skupnih podataka.

I sirovi i skupni podaci mogu se pohraniti u relacijske ili višedimenzionalne strukture. Stoga se trenutno koriste tri metode pohrane podataka.

MOLAP(Multidimensional OLAP) - izvorni i skupni podaci pohranjuju se u višedimenzionalnu bazu podataka. Pohranjivanje podataka u višedimenzionalne strukture omogućuje vam manipuliranje podacima kao višedimenzionalni niz, zbog čega je brzina izračunavanja agregatnih vrijednosti jednaka za bilo koju dimenziju. Međutim, u ovom slučaju višedimenzionalna baza podataka je suvišna, budući da višedimenzionalni podaci u cijelosti sadrže izvorne relacijske podatke.
ROLAP(Relational OLAP) - izvorni podaci ostaju u istoj relacijskoj bazi podataka gdje su se izvorno nalazili. Zbirni podaci smješteni su u servisne tablice posebno kreirane za njihovo pohranjivanje u istoj bazi podataka.
HOLAP(Hybrid OLAP) - izvorni podaci ostaju u istoj relacijskoj bazi podataka gdje su se izvorno nalazili, a agregatni podaci pohranjuju se u višedimenzionalnu bazu podataka.

Neki OLAP alati podržavaju pohranu podataka samo u relacijske strukture, neki samo u višedimenzionalne. Međutim, većina modernih poslužiteljskih OLAP alata podržava sve tri metode pohrane podataka. Odabir načina pohrane ovisi o volumenu i strukturi izvornih podataka, zahtjevima za brzinom izvršavanja upita i učestalosti ažuriranja OLAP kocki.

Također imajte na umu da velika većina modernih OLAP alata ne pohranjuje "prazne" vrijednosti (primjer "prazne" vrijednosti bio bi izostanak prodaje sezonskog proizvoda izvan sezone).

Osnovni OLAP koncepti

FAMSI test

Tehnologija za složenu višedimenzionalnu analizu podataka naziva se OLAP (On-Line Analytical Processing). OLAP je ključna komponenta organizacije skladišta podataka. Koncept OLAP-a opisao je 1993. godine Edgar Codd, poznati istraživač baza podataka i autor relacijskog modela podataka. Godine 1995. na temelju zahtjeva koje je Codd postavio tzv FASMI test(Fast Analysis of Shared Multidimensional Information) - brza analiza zajedničkih višedimenzionalnih informacija, uključujući sljedeće zahtjeve za aplikacije za višedimenzionalnu analizu:

Brzo(Brzo) - pružanje korisniku rezultata analize u prihvatljivom vremenu (obično ne duljem od 5 s), čak i po cijenu manje detaljne analize;
Analiza(Analiza) - sposobnost implementacije bilo kojeg logičkog i Statistička analiza, karakteristika ovu aplikaciju, te spremanje u obliku dostupnom krajnjem korisniku;
Podijeljeno(Zajednički) - višekorisnički pristup podacima uz podršku za odgovarajuće mehanizme zaključavanja i autorizirana sredstva pristupa;
Višedimenzionalno(Multidimensional) - višedimenzionalni konceptualni prikaz podataka, uključujući punu podršku za hijerarhije i višestruke hijerarhije (ovo je ključni zahtjev OLAP-a);
Informacija(Informacije) - aplikacija mora moći pristupiti svim potrebnim informacijama, bez obzira na njihovu količinu i mjesto pohrane.

Valja napomenuti da se OLAP funkcionalnost može implementirati na različite načine, od najjednostavnijih alata za analizu podataka u uredskim aplikacijama do distribuiranih analitički sustavi na temelju poslužiteljskih proizvoda.

Višedimenzionalni prikaz informacija

kocke

OLAP pruža praktične, brze načine pristupa, pregledavanja i analiziranja poslovnih informacija. Korisnik dobiva prirodan, intuitivan model podataka, organizirajući ih u obliku višedimenzionalnih kocki (Cubes). Osi višedimenzionalnog koordinatnog sustava glavni su atributi analiziranog poslovnog procesa. Na primjer, za prodaju to može biti proizvod, regija, vrsta kupca. Kao jedna od dimenzija koristi se vrijeme. Na sjecištima mjernih osi (Dimensions) nalaze se podaci koji kvantitativno karakteriziraju proces – mjere (Measures). To mogu biti količine prodaje u komadima ili u novčanom iznosu, stanja zaliha, troškovi itd. Korisnik koji analizira informacije može "rezati" kocku u različitim smjerovima, dobiti sažetak (na primjer, po godini) ili, obrnuto, detaljan ( po tjednu ) informacije i provodi druge manipulacije koje mu padnu na pamet tijekom procesa analize.

Kao mjere u trodimenzionalnoj kocki prikazanoj na sl. 26.1 koriste se prodajni iznosi, a kao dimenzije vrijeme, proizvod i trgovina. Mjerenja su prikazana na određenim razinama grupiranja: proizvodi su grupirani po kategoriji, trgovine po zemlji, a podaci o vremenu transakcije po mjesecu. Malo kasnije ćemo detaljnije pogledati razine grupiranja (hijerarhije).

Riža. 26.1.

"Rezanje" kocke

Čak je i trodimenzionalnu kocku teško prikazati na zaslonu računala tako da su vrijednosti mjera od interesa vidljive. Što možemo reći o kockama s više od tri dimenzije? Za vizualizaciju podataka pohranjenih u kocki, u pravilu se koriste poznati dvodimenzionalni, tj. tablični prikazi sa složenim hijerarhijskim naslovima redaka i stupaca.

Dvodimenzionalni prikaz kocke može se dobiti "rezanjem" poprečno duž jedne ili više osi (dimenzija): fiksiramo vrijednosti svih dimenzija osim dvije i dobivamo pravilnu dvodimenzionalnu tablicu. Vodoravna os tablice (zaglavlja stupaca) predstavlja jednu dimenziju, okomita os (zaglavlja redaka) drugu, a ćelije tablice predstavljaju vrijednosti mjera. U ovom se slučaju skup mjera zapravo smatra jednom od dimenzija: ili odabiremo jednu mjeru za prikaz (a zatim možemo postaviti dvije dimenzije u naslove retka i stupca), ili prikazujemo nekoliko mjera (a zatim jednu od osi tablice će biti zauzete nazivima mjera, a druge vrijednostima jedine "nerezane" dimenzije).

(razine). Na primjer, oznake prikazane u nisu podržane od strane svih OLAP alata. Na primjer, Microsoft Analysis Services 2000 podržava obje vrste hijerarhije, ali Microsoft OLAP Services 7.0 podržava samo one uravnotežene. Broj razina hijerarhije, najveći dopušteni broj članova jedne razine i najveći mogući broj samih dimenzija mogu se razlikovati u različitim OLAP alatima.

Arhitektura OLAP aplikacija

Sve što je gore rečeno o OLAP-u u biti se odnosi na višedimenzionalnu prezentaciju podataka. Kako se podaci pohranjuju, grubo rečeno, ne tiče se krajnjeg korisnika niti programera alata koji klijent koristi.

Višedimenzionalnost u OLAP aplikacijama može se podijeliti u tri razine.

Višedimenzionalni prikaz podataka - alati za krajnje korisnike koji pružaju višedimenzionalnu vizualizaciju i manipulaciju podacima; Višedimenzionalni prikazni sloj apstrahira fizičku strukturu podataka i tretira podatke kao višedimenzionalne.
Višedimenzionalna obrada - alat (jezik) za formuliranje višedimenzionalnih upita (tradicionalni relacijski SQL jezik ispada da je ovdje neprikladno) i procesor sposoban obraditi i izvršiti takav zahtjev.
Višedimenzionalna pohrana je način fizičkog organiziranja podataka koji osigurava učinkovito izvršavanje višedimenzionalnih upita.

Prve dvije razine obavezne su u svim OLAP alatima. Treća razina, iako raširena, nije potrebna, jer se podaci za višedimenzionalni prikaz mogu izvući iz uobičajenih relacijskih struktura; Višedimenzionalni procesor upita u ovom slučaju prevodi višedimenzionalne upite u SQL upite koje izvršava relacijski DBMS.

Specifični OLAP proizvodi u pravilu su ili višedimenzionalni alat za prezentaciju podataka (OLAP klijent - na primjer, Pivot Tables u Excelu 2000 od Microsofta ili ProClarity od Knosysa) ili višedimenzionalni poslužiteljski DBMS (OLAP poslužitelj - na primjer, Oracle Express Server ili Microsoftove OLAP usluge).

Višedimenzionalni sloj obrade obično je ugrađen u OLAP klijent i/ili OLAP poslužitelj, ali se može izolirati u svom čistom obliku, kao što je Microsoftova komponenta Pivot Table Service.