OLAP je jednostavan! Uvod u analitičku obradu u stvarnom vremenu (OLAP) Softverske komponente potrebne za pristup OLAP izvorima podataka

Problemi analitike, OLAP-a, skladišta podataka sve više zanimaju ruske IT stručnjake. Do danas je naš računalni tisak i internet objavili dosta dobrih, akademskih u načinu prezentiranja materijala o ovoj temi, uključujući i uvodne. Predstavljamo vam članak u kojem namjerno pokušavamo objasniti OLAP "na prstima", koristeći konkretan primjer. Praksa pokazuje da je takvo objašnjenje potrebno nekim informatičkim stručnjacima, a posebno krajnjim korisnicima.

Dakle, OLAP *1 kao prvu aproksimaciju, “na prste”, možemo definirati kao poseban način analize podataka i generiranja izvješća. Njegova je bit pružiti korisniku višedimenzionalnu tablicu koja automatski sažima podatke u različitim odjeljcima i omogućuje interaktivno upravljanje izračunima i oblikom izvješća. Ovaj članak će govoriti o tehnologiji i osnovnim operacijama OLAP-a na primjeru analize faktura za poduzeće koje se bavi trgovinom na veliko prehrambenim proizvodima.

*1. OLAP - On-Line analitička obrada, operativna analiza podataka.

Kao alat razmatrat će se OLAP sustav najjednostavnije i najjeftinije klase, OLAP klijent *1. Na primjer, odabran je najjednostavniji proizvod iz broja OLAP klijenata - "Contour Standard" tvrtke Intersoft Lab. (Radi jasnoće, kasnije u članku, općeprihvaćeni OLAP pojmovi bit će označeni podebljanim slovima i popraćeni engleskim pandanima.)

*1. Za više informacija o klasifikaciji OLAP sustava, pogledajte članak "OLAP Made in Russia" u PC Week/RE, br. 3/2001.

Pa krenimo sa sustavom. Najprije je potrebno opisati izvor podataka (izvor podataka) – put do tablice i njezina polja. To je zadatak korisnika koji poznaje fizičku implementaciju baze podataka. Za krajnje korisnike prevodi naziv tablice i njezina polja u izraze domene. Iza "izvora podataka" nalazi se lokalna tablica, pregled tablice ili SQL poslužitelja ili pohranjena procedura.

Najvjerojatnije se u određenoj bazi podataka fakture ne pohranjuju u jednu, već u nekoliko tablica. Osim toga, neka polja ili zapisi možda se neće koristiti za analizu. Stoga se zatim kreira Uzorak (set rezultata ili upit) u kojem se konfiguriraju: algoritam za kombiniranje tablica po ključnim poljima, uvjeti filtriranja i skup vraćenih polja. Nazovimo naš odabir “Fakture” i stavimo u njega sva polja izvora podataka “Fakture”. Dakle, informatičar kreiranjem semantičkog sloja skriva fizičku implementaciju baze podataka od krajnjeg korisnika.

Zatim se konfigurira OLAP izvješće. To može učiniti stručnjak za određeno područje. Prvo, polja ravnog uzorka podataka podijeljena su u dvije skupine - činjenice (činjenice ili mjere) i mjere (dimenzije). Činjenice su brojke, a mjerenja su „rezovi“ u kojima će se činjenice sažeti. U našem primjeru, dimenzije će biti: "Regija", "Grad", "Kupac", "Proizvod", "Datum", a činjenica će biti jedna - polje "Iznos" na fakturi. Zapravo, mora se odabrati jedan ili više algoritama agregacije. OLAP može ne samo zbrajati rezultate, već i izvoditi složenije izračune, sve do statističke analize. Odabirom više algoritama agregacije stvorit će se virtualne, izračunate činjenice. U primjeru je odabran jedan algoritam agregacije - "Zbroj".

Posebno svojstvo OLAP sustava je generiranje dimenzija i podataka za starija vremenska razdoblja iz datuma i automatski izračun ukupnih iznosa za ta razdoblja. Odaberemo razdoblja “Godina”, “Kvartal” i “Mjesec”, pri čemu u izvještaju neće biti podataka za svaki dan, već će se pojaviti generirane dimenzije “Godina”, “Kvartal” i “Mjesec”. Nazovimo izvještaj "Analiza prodaje" i spremimo ga. Završeni su radovi na izradi sučelja analitičke aplikacije.

Sada, kada ovo sučelje pokreće dnevno ili mjesečno, korisnik će vidjeti tablicu i grafikon u kojem su računi sažeti po stavkama, kupcima i periodima.

Kako bi manipulacije podacima bile intuitivne, alati za upravljanje dinamičkom tablicom su elementi same tablice – njezini stupci i reci. Korisnik može premještati, brisati, filtrirati i izvoditi druge OLAP operacije. Tablica automatski izračunava nove međuzbrojeve i konačne zbrojeve.

Na primjer, povlačenjem (operacija "pomicanje") stupca "Proizvod" na prvo mjesto, dobit ćemo usporedno izvješće - "Usporedba količine prodaje proizvoda za godinu". Da biste agregirali podatke za godinu, samo povucite stupce "Kvartal" i "Mjesec" na vrh tablice - "neaktivno područje mjerenja". Dimenzije "Kvartal" i "Mjesec" prenesene u ovo područje bit će zatvorene (operacija "zatvaranje dimenzije"), tj. isključene iz izvješća; činjenice će biti sažete za godinu. Unatoč činjenici da su dimenzije zatvorene, možete postaviti određene godine, kvartale i mjesece za filtriranje podataka (operacija "filtar").

Radi veće jasnoće, promijenimo vrstu grafikona koji prikazuje OLAP tablicu i njen položaj na ekranu.

Drill down u podacima (operacija "drill down") omogućuje vam dobivanje detaljnijih informacija o prodaji proizvoda koji nas zanima. Klikom na znak "+" nasuprot proizvoda "Kava", vidjet ćemo njegove količine prodaje po regijama. Otvaranjem Uralske regije dobit ćemo količine prodaje u kontekstu gradova Uralske regije, udubljujući se u podatke o Jekaterinburgu, moći ćemo vidjeti podatke o veleprodajnim kupcima ovog grada.

Također možete koristiti otvorena mjerenja za postavljanje filtara. Da bismo usporedili dinamiku prodaje slatkiša u Moskvi i Jekaterinburgu, postavimo filtre za dimenzije "Proizvod" i "Grad".

Zatvorite nepotrebna mjerenja i odaberite vrstu grafikona "Linija". Na dobivenom grafikonu možete pratiti dinamiku prodaje, procijeniti sezonske fluktuacije i odnos između pada i rasta prodaje robe u različitim gradovima.

Tako smo se uvjerili da OLAP tehnologija omogućuje korisniku izdavanje desetaka vrsta različitih izvješća s jednog sučelja, upravljajući dinamičkom OLAP tablicom pomoću miša. Zadatak programera koji posjeduje takav alat nije rutinsko kodiranje izvještajnih obrazaca, već postavljanje OLAP klijenta za baze podataka. U isto vrijeme, metode upravljanja izvješćem su intuitivne za krajnjeg korisnika.

Doista, OLAP je prirodno proširenje i razvoj ideje proračunskih tablica. U biti, OLAP vizualno sučelje je također proračunska tablica, ali opremljena snažnim računalnim motorom i posebnim standardom za prezentiranje i upravljanje podacima. Štoviše, neki OLAP klijenti implementirani su kao dodaci MS Excelu. Stoga milijunta armija “bijelih ovratnika”, koja ima povjerenja u proračunske tablice, brzo svladava i OLAP alate. Za njih je ovo “baršunasta revolucija” koja pruža nove mogućnosti, ali ne uključuje potrebu prekvalifikacije.

Ako čitatelj nakon čitanja ovog članka nije izgubio interes za OLAP, može se pozvati na materijale spomenute na početku. Zbirke takvih materijala objavljene su na brojnim stranicama na Internetu, uključujući stranicu Intersoft lab - www.iso.ru. S njega također možete preuzeti demo verziju sustava Kontur Standard s primjerom opisanim u članku.

Analitička analitička obrada (OLAP) je tehnologija koja se koristi za organiziranje velikih poslovnih baza podataka i podršku poslovnoj inteligenciji. OLAP baze podataka podijeljene su u jednu ili više kocki, a svaku kocku organizira administrator kocke kako bi odgovarala načinu na koji se podaci dohvaćaju i analiziraju kako bi se olakšalo stvaranje i korištenje izvješća zaokretne tablice i zaokretnog grafikona koja su vam potrebna.

U ovom članku

Što je poslovna inteligencija?

Poslovni analitičar često želi dobiti širu sliku poslovanja, vidjeti šire trendove na temelju agregiranih podataka i vidjeti trendove raščlanjene na bilo koji broj varijabli. Poslovna inteligencija je proces izvlačenja podataka iz OLAP baze podataka i analiziranja tih podataka kako bi se dobili uvidi koji se mogu koristiti za donošenje informiranih poslovnih odluka i poduzimanje radnji. Na primjer, s OLAP-om i poslovnom inteligencijom možete odgovoriti na sljedeća pitanja o poslovnim podacima.

Kakva je ukupna prodaja svih proizvoda u 2007. u usporedbi s prodajom od 2006.?

Kako se ovo može usporediti s datumom i vremenom za razdoblje beneficije tijekom proteklih pet godina?

Koliko su novca kupci potrošili na 35 prošle godine i kako se to ponašanje promijenilo tijekom vremena?

Koliko je proizvoda prodano u dvije određene zemlje/regije ovaj mjesec u usporedbi s istim mjesecom prošle godine?

Za svaku dobnu skupinu kupaca. Kakva je raščlamba profitabilnosti (i postotak marže i ukupna) po kategoriji proizvoda?

Potražite najbolje i najniže prodavače, distributere, dobavljače, kupce, partnere i kupce.

Što je online analitička obrada (OLAP)?

OLAP (Online Analytical Processing) baze podataka pojednostavljuju upite poslovne inteligencije. OLAP je tehnologija baze podataka koja je optimizirana za upite i izvješća, a ne za obradu transakcija. Izvor podataka za OLAP su baze podataka za online obradu transakcija (OLTP), koje se obično pohranjuju u skladištima podataka. OLAP podaci izvlače se iz ovih povijesnih podataka i kombiniraju u strukture koje omogućuju složenu analizu. OLAP podaci također su organizirani hijerarhijski i pohranjeni u kocke umjesto u tablice. To je složena tehnologija koja koristi višedimenzionalne strukture za brz pristup podacima za analizu. U ovoj organizaciji, za izvješće zaokretne tablice ili izvješće zaokretnog grafikona, možete jednostavno prikazati sažete podatke visoke razine, kao što su ukupne prodaje za cijelu zemlju ili regiju, te također prikazati pojedinosti o mjestima gdje je prodaja posebno jaka ili slaba.

OLAP baze podataka dizajnirane su za ubrzavanje učitavanja podataka. Budući da OLAP poslužitelj izračunava agregirane vrijednosti, a ne Microsoft Office Excel, manje podataka mora se poslati u Excel kada stvarate ili uređujete izvješće. Ovaj vam pristup omogućuje rad s većom količinom izvornih podataka nego da su podaci organizirani u tradicionalnoj bazi podataka gdje Excel dohvaća sve pojedinačne zapise i izračunava agregirane vrijednosti.

OLAP baze podataka sadrže dvije glavne vrste podataka: mjere, koje su numerički podaci, količine i prosjeci, koji se koriste za donošenje informiranih poslovnih odluka, i dimenzije, koje su kategorije koje se koriste za organiziranje tih mjera. OLAP baze podataka pomažu vam organizirati podatke s mnogo razina detalja koristeći iste kategorije koje poznajete za analizu podataka.

Sljedeći odjeljci detaljno opisuju svaku komponentu u nastavku.

Kubični Struktura podataka koja kombinira mjere preko razina i hijerarhija svake dimenzije koju želite analizirati. Kocke kombiniraju višestruke dimenzije kao što su vrijeme, geografija i linije proizvoda sa sažetim podacima kao što su prodaja i zalihe. Kocke nisu "kocke" u strogom matematičkom smislu, jer ne moraju nužno imati iste strane. Međutim, oni predstavljaju prikladnu metaforu za složen koncept.

mjerenja Skup vrijednosti u kocki koje se temelje na stupcu u tablici činjenica kocke, a koji je obično numerička vrijednost. Mjere su središnje vrijednosti u kocki koje se prethodno obrađuju, obrađuju i analiziraju. Najčešći primjeri su prodaja, prihodi, prihodi i rashodi.

Član Element u hijerarhiji koji predstavlja jedno ili više pojavljivanja podataka. Element može biti jedinstven ili nejedinstven. Na primjer, 2007. i 2008. predstavljaju jedinstvene članove na razini godine vremenske dimenzije, dok siječanj predstavlja nejedinstvene članove na razini mjeseca jer postoji više od jednog siječnja u vremenskoj dimenziji jer sadrži podatke za više od jedne godine.

Izračunati članČlan dimenzije čija se vrijednost procjenjuje u vrijeme izvođenja pomoću izraza. Vrijednosti izračunatih članova mogu se izvesti iz vrijednosti drugih članova. Na primjer, izračunata stavka "dobit" može se odrediti oduzimanjem vrijednosti stavke, kao i troškova, od vrijednosti stavke, prodaje.

mjerenje Skup jedne ili više uređenih hijerarhija razine kocke koje korisnik razumije i koristi kao osnovu za analizu podataka. Na primjer, geografska dimenzija može uključivati ​​razine zemlje/regije, države/regije i grada. Osim toga, vremenska dimenzija može uključivati ​​hijerarhiju s razinama godine, kvartala, mjeseca i dana. U izvješću zaokretne tablice ili izvješću zaokretnog grafikona svaka hijerarhija postaje skup polja koja se mogu proširiti i sažeti kako bi se prikazale niže ili više razine.

Hijerarhija Logička struktura stabla koja raspoređuje članove dimenzije tako da svaki član ima jednog roditelja i nula ili više djece. Podređeni element je član ranije grupe u hijerarhiji koja je izravno povezana s trenutnim članom. Na primjer, u vremenskoj hijerarhiji koja sadrži razine kvartala, mjeseca i dana, siječanj je dijete Qtr1. Nadređeni element je član niže razine u hijerarhiji koji je izravno povezan s trenutnim članom. Vrijednost roditelja obično je konsolidacija vrijednosti svih podređenih elemenata. Na primjer, u vremenskoj hijerarhiji koja sadrži razine kvartala, mjeseca i dana, Qtr1 je roditelj siječnja.

Razina U hijerarhiji se podaci mogu organizirati u niže i više razine detalja, kao što su godine, kvartali, mjeseci i razine dana u vremenskoj hijerarhiji.

OLAP funkcije u Excelu

Dobivanje OLAP podataka Možete se povezati s OLAP izvorima podataka na isti način kao što se možete povezati s drugim vanjskim izvorima podataka. Možete raditi s bazama podataka stvorenim s Microsoft SQL Server OLAP Services verzija 7.0, Microsoft SQL Server Analysis Services verzija 2000 i Microsoft SQL Server Analysis Services verzija 2005, Microsoft OLAP Server Products. Excel također može raditi s OLAP proizvodima treće strane koji su kompatibilni s OLE-DB za OLAP.

OLAP podaci mogu se prikazati samo kao izvješće zaokretne tablice ili zaokretnog grafikona ili u funkciji radnog lista pretvorenoj iz izvješća zaokretne tablice, a ne kao vanjski raspon podataka. Izvješća OLAP zaokretne tablice i zaokretnog grafikona možete spremiti u predloške izvješća i možete stvoriti datoteke Office Data Connection (ODC) za povezivanje s OLAP bazama podataka za OLAP upite. Kada otvorite .odc datoteku u Excelu, prikazuje se prazno izvješće zaokretne tablice i spremno je za postavljanje.

Stvorite kockaste datoteke za izvanmrežnu upotrebu Možete stvoriti samostalnu datoteku kocke (.cub) s podskupom podataka iz baze podataka OLAP poslužitelja. Datoteke izvanmrežne kocke koriste se za rad s OLAP podacima kada niste spojeni na mrežu. S kockom možete raditi s više podataka u izvješću zaokretne tablice ili izvješću zaokretnog grafikona nego inače i dobiti podatke brže. Datoteke kocke možete kreirati samo ako koristite OLAP pružatelja usluga kao što je Microsoft SQL Analysis Services verzija 2005 koja podržava ovu značajku.

Akcije poslužitelja Akcija poslužitelja je izborna funkcija koju administrator OLAP kocke može definirati na poslužitelju koji koristi element kocke ili mjeru kao parametar u upitu za dohvaćanje informacija u kocki ili za pokretanje druge aplikacije kao što je preglednik. Excel podržava URL-ove, izvješća, skupove redaka, analizu i detaljne radnje poslužitelja, ali ne podržava vlastiti izvorni operator i skup podataka.

KPI KPI je posebna izračunata mjera definirana na poslužitelju koja vam omogućuje praćenje "Ključnih pokazatelja uspješnosti", uključujući status (trenutna vrijednost odgovara određenom broju). i trend (vrijednosti tijekom vremena). Kada se prikažu, poslužitelj može poslati odgovarajuće ikone, slične novoj ikoni programa Excel, da se postave iznad ili ispod razina statusa (na primjer, za ikonu zaustavljanja), kao i da pomiču vrijednost gore ili dolje (za na primjer, ikona strelice za smjer).

Formatiranje na poslužitelju Administratori Cubea mogu kreirati mjere i izračunate članove pomoću oblikovanja boja, oblikovanja fonta i pravila uvjetnog oblikovanja koja se mogu dodijeliti kao standardno poslovno pravilo poduzeća. Na primjer, format na strani poslužitelja za prihod može biti format valute broja, boja ćelije je zelena ako je vrijednost veća ili jednaka 30.000 i crvena ako je vrijednost manja od 30.000, a stil fonta je podebljan ako je vrijednost je manji od 30.000, a ako je vrijednost pozitivna – obični. veći ili jednak 30 000. Za više informacija pogledajte.

Uredski jezik sučelja Administrator kocke može definirati prijevode za podatke i pogreške na poslužitelju za korisnike koji trebaju vidjeti informacije zaokretne tablice na drugom jeziku. Ova je značajka definirana kao svojstvo veze datoteke, a lokalizacija i država korisničkog računala moraju odgovarati jeziku sučelja.

Softverske komponente potrebne za pristup OLAP izvorima podataka

OLAP pružatelj usluga Da biste postavili OLAP izvore podataka za Excel, potreban vam je jedan od sljedećih OLAP pružatelja usluga.

Microsoft OLAP pružatelj usluga Excel uključuje upravljački program za izvor podataka i klijentski softver za pristup bazama podataka stvorenim pomoću Microsoft SQL Server olap verzija 7.0, Microsoft SQL Server olap verzija 2000 (8.0) i Microsoft SQL Server Analysis Services verzija 2005 (9,0).

Pružatelji OLAP-a treće strane Ostali OLAP proizvodi zahtijevaju instaliranje dodatnih upravljačkih programa i klijentskog softvera. Da biste koristili Excelove mogućnosti manipulacije OLAP podacima, proizvod treće strane mora biti u skladu sa standardom OLE-DB za OLAP i biti kompatibilan s Microsoft Officeom. Za informacije o instaliranju i korištenju OLAP dobavljača treće strane obratite se svom administratoru sustava ili dobavljaču OLAP proizvoda.

Baze podataka poslužitelja i kockaste datoteke Excel OLAP klijentski softver podržava veze s dvije vrste OLAP baza podataka. Ako je baza podataka na OLAP poslužitelju dostupna na mreži, izvorne podatke iz nje možete dobiti izravno iz nje. Ako imate samostalnu datoteku kocke koja sadrži OLAP podatke ili datoteku definicije kocke, možete se povezati s tom datotekom i dobiti izvorne podatke iz nje.

Izvori podataka Izvor podataka omogućuje pristup svim podacima u OLAP bazi podataka ili izvanmrežnoj datoteci kocke. Nakon što stvorite OLAP izvor podataka, možete temeljiti izvješća na njemu i vratiti OLAP podatke u Excel kao izvješće zaokretne tablice ili zaokretnog grafikona ili kao funkciju lista pretvorenu iz izvješća zaokretne tablice.

Microsoft Query Uz Query možete dohvatiti podatke iz vanjske baze podataka kao što je Microsoft SQL ili Microsoft Access. Ne morate koristiti upit za dohvaćanje podataka iz OLAP zaokretne tablice povezane s kockastom datotekom. Dodatne informacije .

Razlike u OLAP funkcijama i ne-OLAP izvornim podacima

Ako radite s izvješćima zaokretne tablice i zaokretnim grafikonima iz OLAP izvornih podataka i drugih vrsta izvornih podataka, vidjet ćete neke razlike u značajkama.

Ekstrakcija podataka OLAP poslužitelj vraća nove podatke u Excel kad god se promijeni izgled izvješća. S drugim vrstama vanjskih izvora podataka postavljate upit za sve izvorne podatke odjednom ili možete postaviti parametre za upit samo kada prikazujete različite stavke polja filtra izvješća. Osim toga, imate još nekoliko opcija za ažuriranje izvješća.

U izvješćima koja se temelje na OLAP izvornim podacima, opcije polja filtera izvješća nisu dostupne, pozadinski upit nije dostupan, a opcija optimizacije memorije nije dostupna.

Bilješka: Optimizacija memorije također nije dostupna za OLEDB izvore podataka i izvješća zaokretne tablice na temelju niza ćelija.

Vrste polja OLAP izvor podataka. polja dimenzija mogu se koristiti samo kao redovi (redovi), stupci (kategorija) ili polja stranice. Polja mjerenja mogu se koristiti samo kao polja vrijednosti. Za ostale vrste izvornih podataka sva polja mogu se koristiti u bilo kojem dijelu izvješća.

Pristup detaljnim podacima Za izvorne OLAP podatke, poslužitelj određuje dostupne razine detalja i izračunava sažete vrijednosti, tako da zapisi detalja koji čine sažete vrijednosti možda neće biti dostupni. Međutim, poslužitelj može pružiti polja svojstava koja možete prikazati. Druge vrste izvornih podataka nemaju polja svojstava, ali možete prikazati osnovne informacije za podatkovna polja i vrijednosti elemenata te prikazati elemente bez podataka.

Polja filtra OLAP izvješća možda nemaju svi elementi i naredba Prikaži stranice filtera izvješća nije dostupno.

Početni redoslijed sortiranja Za OLAP izvorne podatke, stavke se prvo prikazuju redoslijedom kojim ih vraća OLAP poslužitelj. Možete sortirati ili ručno promijeniti redoslijed elemenata. Za druge vrste izvornih podataka, elementi novog izvješća prvo se sortiraju prema nazivu elementa uzlaznim redoslijedom.

Nimi OLAP poslužitelji daju sažete vrijednosti izravno u izvješće, tako da nije moguće promijeniti funkcije sažetka za polja vrijednosti. Za druge izvorne vrste podataka možete promijeniti agregatnu funkciju za polje vrijednosti i koristiti višestruke funkcije sažetka za isto polje vrijednosti. Ne možete kreirati izračunata polja i izračunate članove u izvješćima s OLAP izvornim podacima.

Međuzbrojevi U izvješćima s izvornim OLAP podacima ne možete promijeniti funkciju sažetka za međuzbrojeve. S drugim vrstama izvornih podataka možete promijeniti funkcije sažetka za međuzbrojeve i prikazati ili sakriti međuzbrojeve za sva polja redaka i stupaca.

Za OLAP izvorne podatke možete uključiti ili isključiti skrivene stavke pri izračunavanju međuzbrojeva i ukupnih zbrojeva. Za druge vrste izvornih podataka možete uključiti skrivene stavke u poljima filtra izvješća u međuzbrojeve, ali skrivene stavke u drugim poljima bit će isključene prema zadanim postavkama.

Rad s OLAP kockom u MS Excelu

1. Dobijte dozvolu za pristup OLAP kocki SQL Server Analysis Services (SSAS).
2. Na računalu mora biti instaliran MS Excel 2016 / 2013 / 2010 (moguć je i MS Excel 2007, ali nije zgodno raditi u njemu, a MS Excel 2003 ima jako lošu funkcionalnost)
3. Otvorite MS Excel, pokrenite čarobnjaka za postavljanje veze s analitičkim servisom:

3.1 Navedite naziv ili IP adresu trenutnog OLAP poslužitelja (ponekad morate navesti broj otvorenog porta, na primjer, 192.25.25.102:80); koristi se autentifikacija domene:

3.2 Odaberite višedimenzionalnu bazu podataka i analitičku kocku (ako imate prava pristupa kocki):

3.3 Postavke za povezivanje s analitičkom uslugom bit će spremljene u odc datoteci na vašem računalu:

3.4 Odaberite vrstu izvješća (zaokretna tablica/graf) i odredite mjesto za njegovo postavljanje:

Ako je veza već stvorena u Excel radnoj knjizi, tada se može ponovno koristiti: glavni izbornik "Podaci" -> "Postojeće veze" -> odaberite vezu u ovoj radnoj knjizi -> umetnite zaokretnu tablicu u navedenu ćeliju.

4. Uspješno povezani s kockom, možete započeti interaktivnu analizu podataka:

Prilikom pokretanja interaktivne analize podataka potrebno je odrediti koja će od polja biti uključena u formiranje redaka, stupaca i filtera (stranica) pivot tablice. Općenito, zaokretna tablica je trodimenzionalna, a treću dimenziju možemo zamisliti kao okomitu na zaslon i vidimo dijelove koji su paralelni s ravninom zaslona i određuju koja je "stranica" odabrana biti prikazan. Filtriranje se može izvršiti povlačenjem odgovarajućih atributa dimenzije u područje filtara izvješća. Filtriranje ograničava prostor kocke, smanjujući opterećenje OLAP poslužitelja, tako da poželjno je prvo instalirati potrebne filtre. Zatim postavljate atribute dimenzija u područja redaka i stupaca, a mjere u područje podataka zaokretne tablice.

Svaki put kada se zaokretna tablica promijeni, MDX izjava se automatski šalje na OLAP poslužitelj i podaci se vraćaju kada se izvrši. Što je veći i složeniji volumen obrađenih podataka, izračunatih pokazatelja, to je duže vrijeme izvršenja upita. Izvršenje zahtjeva možete poništiti pritiskom na tipku Pobjeći. Posljednje izvršene operacije mogu se poništiti (Ctrl+Z) ili ponovno učiniti (Ctrl+Y).

U pravilu, za najčešće korištene kombinacije dimenzijskih atributa, kocka pohranjuje unaprijed izračunate agregirane podatke, pa je vrijeme odgovora na takve upite nekoliko sekundi. Međutim, nemoguće je izračunati sve moguće kombinacije agregacija, budući da to može zahtijevati puno vremena i prostora za pohranu. Izvršavanje masivnih upita na podacima na razini detalja može zahtijevati značajne računalne resurse poslužitelja, tako da njihovo vrijeme izvršenja može biti dugo. Nakon čitanja podataka s diskovnih pogona, poslužitelj ih smješta u RAM predmemoriju, što omogućuje trenutno izvršavanje sljedećih takvih zahtjeva, budući da će podaci biti dohvaćeni iz predmemorije.

Ako mislite da će se vaš zahtjev često koristiti i njegovo vrijeme izvršenja nije zadovoljavajuće, možete se obratiti službi za analitičku razvojnu podršku kako biste optimizirali izvršenje zahtjeva.

Nakon postavljanja hijerarhije u područje redaka/stupaca, moguće je sakriti pojedinačne razine:

Za ključne atribute(rjeđe - za atribute više u hijerarhiji) dimenzije mogu imati svojstva - opisne karakteristike koje se mogu prikazati i u opisima alata i kao polja:

Ako želite prikazati nekoliko svojstava polja odjednom, možete koristiti odgovarajući dijaloški popis:

Korisnički definirani skupovi

Excel 2010 uvodi mogućnost interaktivnog stvaranja prilagođenih (korisnički definiranih) skupova iz članova dimenzije:

Za razliku od skupova stvorenih i pohranjenih središnje na bočnoj strani kocke, prilagođeni skupovi pohranjuju se lokalno u Excel radnoj knjizi i mogu se koristiti kasnije:

Napredni korisnici mogu stvarati skupove koristeći MDX konstrukcije:

Postavljanje svojstava zaokretne tablice

Stavka "Opcije zaokretne tablice..." kontekstnog izbornika (desni klik unutar zaokretne tablice) pruža mogućnost prilagodbe zaokretne tablice, na primjer:
- kartica "Output", opcija "Classic pivot table layout" - pivot tablica postaje interaktivna, polja se mogu povlačiti (Drag&Drop);
- kartica "Izlaz", parametar "Prikaži elemente bez podataka u recima" - stožerna tablica će prikazati prazne retke koji ne sadrže nikakve vrijednosti indikatora za odgovarajuće elemente dimenzija;
- Kartica "Izgled i format", parametar "Zadrži oblikovanje ćelije pri ažuriranju" - u zaokretnoj tablici možete nadjačati i spremiti format ćelije prilikom ažuriranja podataka;

Stvaranje zaokretnih grafikona

Za postojeću OLAP zaokretnu tablicu možete izraditi zaokretni grafikon - tortni, trakasti, histogram, grafikon, raspršeni grafikon i druge vrste grafikona:

U tom slučaju, pivot grafikon će biti sinkroniziran sa pivot tablicom - kada promijenite sastav indikatora, filtera, dimenzija u pivot tablici, pivot tablica se također ažurira.

Izrada nadzornih ploča

Odaberite izvornu stožernu tablicu, kopirajte je u međuspremnik (Ctrl + C) i zalijepite njezinu kopiju (Ctrl + V), u kojoj ćemo promijeniti sastav indikatora:

Za upravljanje višestrukim zaokretnim tablicama u isto vrijeme, umetnimo rezač (nova funkcionalnost dostupna počevši od MS Excel 2010). Povežimo naš Slicer sa zaokretnim tablicama - desnom tipkom miša kliknite unutar rezača, odaberite "Veze sa zaokretnom tablicom ..." iz kontekstnog izbornika. Treba napomenuti da može postojati više ploča za rezanje koje mogu služiti zaokretnim tablicama na različitim listovima u isto vrijeme, što vam omogućuje stvaranje koordiniranih nadzornih ploča.

Ploče rezača mogu se konfigurirati: odaberite ploču, zatim pogledajte "Veličina i svojstva...", "Postavke rezanja", "Dodijeli makronaredbu" u kontekstnom izborniku desnog klika ili u stavci "Opcije" glavnog izbornika. Dakle, moguće je postaviti broj stupaca za elemente (gumbiće) rezača, veličine gumba rezača i panela, definirati shemu boja i stil dizajna rezača iz postojećeg skupa (ili kreirati vlastiti stil), definirajte vlastiti naslov panela, dodijelite programski makro pomoću kojeg možete proširiti funkcionalnost panela.

Izvršavanje MDX upita iz Excela

1. Prije svega, trebate izvršiti operaciju DRILLTHROUGH na nekom indikatoru, tj. spustite se na detaljne podatke (detaljni podaci se prikazuju na posebnom listu), te otvorite popis veza;
2. Otvorite svojstva veze, idite na karticu "Definicija";
3. Odaberite zadanu vrstu naredbe, au tekstualno polje naredbe postavite unaprijed pripremljenu MDX zahtjev;
4. Kada se pritisne gumb, nakon provjere ispravne sintakse zahtjeva i dostupnosti odgovarajućih prava pristupa, zahtjev će se izvršiti na poslužitelju, a rezultat će biti prikazan na trenutnom listu u obliku pravilne ravne tablice.
   Možete pogledati tekst MDX upita koji je generirao Excel instaliranjem besplatnog dodatka koji pruža i druge dodatne funkcije.

Prijevod na druge jezike

Analitička kocka podržava lokalizaciju na ruski i engleski (ako je potrebno, moguća je lokalizacija na druge jezike). Prijevodi se odnose na nazive dimenzija, hijerarhija, atributa, mapa, mjera, kao i elemenata pojedinih hijerarhija ukoliko za njih postoje prijevodi na strani računovodstvenih sustava/pohrane podataka. Za promjenu jezika otvorite svojstva veze i dodajte sljedeću opciju u niz veze:
Proširena svojstva="Locale=1033"
gdje 1033 - engleska lokalizacija
1049 - Ruska lokalizacija

Dodatna proširenja programa Excel za Microsoft OLAP

Mogućnost rada s Microsoftovim OLAP kockama povećat će se ako koristite dodatna proširenja, na primjer, OLAP PivotTable Extensions, zahvaljujući kojima možete koristiti brzo pretraživanje po dimenzijama:

Prvo sučelje zaokretne tablice, također nazvano zaokretna izvješća, uključeno je u Excel 1993. (Excel verzija 5.0). Unatoč mnogim korisnim značajkama, praktički se ne koristi u radu većine korisnika Excela. Čak i iskusni korisnici pod pojmom "sažeto izvješće" često misle na nešto što je izgrađeno pomoću složenih formula. Pokušajmo popularizirati korištenje pivot tablica u svakodnevnom radu ekonomista. U ovom se članku govori o teoretskim osnovama za izradu sumarnih izvješća, daju se praktične preporuke za njihovu upotrebu, a također se daje i primjer pristupa podacima na temelju nekoliko tablica.

Uvjeti multivarijantne analize podataka

Većina ekonomista je čula izraze "višedimenzionalni podaci", "virtualna kocka", "OLAP tehnologije" itd. No uz detaljan razgovor obično se pokaže da gotovo svi zapravo ne razumiju o čemu se radi. Odnosno, ljudi misle na nešto složeno i obično nije relevantno za njihove dnevne aktivnosti. Zapravo nije.

Višedimenzionalni podaci, dimenzije

Slobodno se može reći da se ekonomisti gotovo cijelo vrijeme bave višedimenzionalnim podacima, ali ih pokušavaju prikazati na unaprijed definiran način pomoću proračunskih tablica. Višedimenzionalnost ovdje znači mogućnost unosa, pregleda ili analize istih informacija uz promjenu izgleda, korištenje različitih grupiranja i sortiranja podataka. Na primjer, plan prodaje može se analizirati prema sljedećim kriterijima:

• vrste ili grupe robe;
• marke ili kategorije proizvoda;
• razdoblja (mjesec, kvartal, godina);
• kupci ili grupe kupaca;
• prodajne regije
• i tako dalje.

Svaki od gore navedenih kriterija u smislu multivarijantne analize podataka naziva se "dimenzija". Može se reći da dimenzija karakterizira informacije o određenom skupu vrijednosti. Posebna vrsta mjerenja višedimenzionalnih informacija su "podaci". U našem primjeru podaci plana prodaje mogu biti:

• obujam prodaje;
• Prodajna cijena;
• individualni popust
• i tako dalje.

Teoretski, podaci također mogu biti standardna dimenzija višedimenzionalnih informacija (na primjer, možete grupirati podatke prema prodajnoj cijeni), ali podaci su obično posebna vrsta vrijednosti.

Dakle, možemo reći da u praktičnom radu ekonomisti koriste dvije vrste informacija: višedimenzionalni podaci ( stvarni i planirani brojevi s mnogim značajkama) i imenici (karakteristike ili mjerenja podataka).

OLAP

Skraćenica OLAP (online analytical processing) u doslovnom prijevodu zvuči kao “real-time analytical processing”. Definicija nije vrlo specifična; pod nju se može sažeti gotovo svako izvješće o bilo kojem softverskom proizvodu. Po definiciji, OLAP znači tehnologiju za rad s posebnim izvješćima, uključujući softver, za dobivanje i analizu višedimenzionalnih strukturiranih podataka. Jedan od popularnih softverskih proizvoda koji implementiraju OLAP tehnologije je SQL Server Analysis Server. Neki ga čak pogrešno smatraju jedinim predstavnikom softverske implementacije ovog koncepta.

Virtualna podatkovna kocka

"Virtualna kocka" (višedimenzionalna kocka, OLAP kocka) je tehnički izraz koji su skovali neki specijalizirani proizvođači softvera. OLAP sustavi obično pripremaju i pohranjuju podatke u vlastite strukture, a posebna analitička sučelja (kao što su sažeta izvješća Excela) pristupaju podacima iz tih virtualnih kocki. U isto vrijeme, korištenje takve namjenske pohrane uopće nije potrebno za obradu višedimenzionalnih informacija. Općenito, virtualna kocka- ovo je niz posebno optimiziranih višedimenzionalnih podataka koji se koriste za izradu sažetih izvješća. Može se dobiti putem specijaliziranih softverskih alata i jednostavnim pristupom tablicama baze podataka ili bilo kojem drugom izvoru, kao što je Excel proračunska tablica.

stožerna tablica

"Pivot Report" (zaokretna tablica, Pivot Table) je korisničko sučelje za prikaz višedimenzionalnih podataka. Pomoću ovog sučelja možete grupirati, sortirati, filtrirati i mijenjati lokaciju podataka kako biste dobili različite analitičke uzorke. Izvješće se ažurira korištenjem jednostavnih alata korisničkog sučelja, podaci se automatski agregiraju prema zadanim pravilima i nisu potrebni dodatni ili ponovni unos bilo kakvih informacija. Sučelje zaokretne tablice programa Excel možda je najpopularniji softverski proizvod za rad s višedimenzionalnim podacima. Podržava vanjske izvore podataka (OLAP kocke i relacijske baze podataka) i interne raspone proračunskih tablica kao izvor podataka. Počevši od verzije 2000 (9.0), Excel podržava i grafički oblik prikaza višedimenzionalnih podataka - Pivot Chart.

Sučelje PivotTable implementirano u Excelu omogućuje vam da rasporedite dimenzije višedimenzionalnih podataka u području radnog lista. Radi jednostavnosti, zaokretnu tablicu možete zamisliti kao izvješće koje leži na vrhu niza ćelija (zapravo, postoji određeno vezanje formata ćelija na polja zaokretne tablice). Excel zaokretna tablica ima četiri područja prikaza: filtar, stupce, retke i podatke. Dimenzije podataka su imenovane polja zaokretne tablice. Ova polja imaju vlastita svojstva i format prikaza.

Još jednom vam skrećem pozornost da je zaokretna tablica u Excelu namijenjena isključivo analizi podataka bez mogućnosti uređivanja informacija. Bliži po značenju bio bi raširen termin "pivot izvještaj" (Pivot Report), a tako se ovo sučelje nazivalo sve do 2000. godine. Ali iz nekog razloga, u kasnijim verzijama, programeri su ga napustili.

Uređivanje zaokretnih tablica

OLAP tehnologija po svojoj definiciji načelno ne podrazumijeva mogućnost promjene izvornih podataka pri radu s izvješćima. Ipak, na tržištu se pojavila cijela klasa programskih sustava koji implementiraju mogućnosti analize i izravnog uređivanja podataka u višedimenzionalnim tablicama. U osnovi, takvi su sustavi usmjereni na rješavanje proračunskih problema.

Koristeći Excelove ugrađene alate za automatizaciju, možete riješiti mnoge nestandardne zadatke. Primjer implementacije uređivanja zaokretnih tablica Radni list temeljen na Excelu možete pronaći na našoj web stranici.

Priprema višedimenzionalnih podataka

Pristupimo praktičnoj primjeni stožernih tablica. Pokušajmo analizirati podatke o prodaji u različitim smjerovima. Datoteka pivottableexample.xls sastoji se od nekoliko listova. List Primjer sadrži osnovne podatke o prodaji za određeno razdoblje. Radi jednostavnosti primjera, analizirat ćemo jedini numerički pokazatelj - obujam prodaje u kg. Dostupne su sljedeće ključne dimenzije podataka: proizvod, kupac i prijevoznik (prijevoznička tvrtka). Osim toga, postoji nekoliko dodatnih dimenzija podataka koji su atributi proizvoda: tip, marka, kategorija, dobavljač, kao i kupac: tip. Ovi se podaci prikupljaju na listu imenika. U praksi može biti mnogo više takvih mjerenja.

List Primjer sadrži standardni alat za analizu podataka - autofilter. Gledajući primjer popunjavanja tablice, vidljivo je da su podaci o prodaji po datumima (složeni su u stupce) podložni normalnoj analizi. Osim toga, pomoću autofiltera možete pokušati zbrojiti podatke kombinacijom jednog ili više ključnih kriterija. Nema apsolutno nikakvih informacija o markama, kategorijama i vrstama. Nije moguće grupirati podatke s automatskim sažimanjem po određenom ključu (npr. po kupcima). Osim toga, skup datuma je fiksan i neće biti moguće automatski pregledati sažete informacije za određeno razdoblje, na primjer, 3 dana.

Općenito, prisutnost unaprijed definirane lokacije datuma u ovom primjeru glavni je nedostatak tablice. Rasporedivši datume po stupcima, na neki smo način unaprijed odredili dimenziju ove tablice, čime smo se lišili mogućnosti korištenja analize pomoću pivot tablica.

Prvo, moramo se riješiti ovog nedostatka - tj. ukloniti unaprijed definiranu lokaciju jedne od dimenzija izvornih podataka. Primjer valjane tablice je list Prodajni.

Tablica je u obliku dnevnika unosa podataka. Ovdje je datum jednaka dimenzija podataka. Također treba napomenuti da je za naknadnu analizu u zaokretnim tablicama relativni položaj redaka jedan u odnosu na drugi (drugim riječima, sortiranje) potpuno indiferentan. Zapisi u relacijskim bazama podataka imaju ova svojstva. Upravo je analiza velikih količina baza podataka prvenstveno usmjerena na sučelje zaokretnih tablica. Stoga se morate pridržavati ovih pravila kada radite s izvorom podataka u obliku raspona ćelija. U isto vrijeme, nitko ne zabranjuje korištenje alata Excel sučelja u vašem radu - zaokretne tablice analiziraju samo podatke, a oblikovanje, filtri, grupiranje i sortiranje izvornih ćelija mogu biti proizvoljni.

Od automatskog filtra do sažetog izvješća

Teoretski, podatke o prodajnom listu već je moguće analizirati u tri dimenzije: robu, kupce i prijevoznike. Na ovom listu nema podataka o svojstvima proizvoda i kupaca, što stoga neće omogućiti njihov prikaz u zbirnoj tablici. U normalnom načinu izrade zaokretne tablice za izvorne podatke, Excel ne dopušta povezivanje podataka iz više tablica prema određenim poljima. Ovo ograničenje može se zaobići softverom. dopuniti primjer na ovaj članak na našoj web stranici. Kako ne biste pribjegli programskim metodama obrade informacija (pogotovo jer nisu univerzalne), trebali biste dodati dodatne karakteristike izravno u obrazac za unos dnevnika - pogledajte list SalesAnalysis.

Korištenje funkcija VLOOKUP olakšava dopunu izvornih podataka karakteristikama koje nedostaju. Sada, primjenom autofiltera, možete analizirati podatke u različitim dimenzijama. Ali problem grupiranja ostaje neriješen. Na primjer, praćenje iznosa samo za brendove na određene datume prilično je problematično. Ako ste ograničeni na Excel formule, tada morate izraditi dodatne uzorke pomoću funkcije SUMIF.

Sada da vidimo koje značajke nudi sučelje zaokretne tablice. Na plahti CodeAnalysis napravio nekoliko izvješća na temelju niza ćelija s podacima lista Analiza prodaje.

Prva tablica analize izgrađena je kroz Excel 2007 sučelje Vrpca \ Umetni \ Zaokretna tablica(u izborniku programa Excel 2000-2003 Podaci\Zaokretna tablica).

Druga i treća tablica izrađene su kopiranjem i naknadnim prilagođavanjem. Izvor podataka za sve tablice je isti. To možete provjeriti promjenom izvornih podataka, zatim morate ažurirati podatke sažetih izvješća.

S naše točke gledišta, prednosti u vidljivosti informacija su očite. Možete zamijeniti filtre, stupce i retke, sakriti određene skupine vrijednosti iz bilo koje dimenzije, koristiti ručno povlačenje i ispuštanje i automatsko sortiranje.

Svojstva i oblikovanje

Osim izravnog prikaza podataka, postoji velik skup opcija za prikaz izgleda zaokretnih tablica. Dodatni podaci mogu se sakriti pomoću filtara. Za jedan element ili polje lakše je koristiti stavku kontekstnog izbornika Izbrisati(u verziji 2000-2003 Sakriti).

Također je poželjno postaviti prikaz ostalih elemenata pivot tablice ne kroz formatiranje ćelije, već kroz postavljanje polja ili elementa pivot tablice. Da biste to učinili, pomaknite pokazivač miša na željeni element, pričekajte da se pojavi poseban oblik kursora (u obliku strelice), a zatim odaberite odabrani element jednim klikom. Nakon odabira, možete promijeniti prikaz kroz vrpcu, kontekstni izbornik ili pozvati standardni dijaloški okvir formata ćelije:

Osim toga, Excel 2007 uveo je mnogo unaprijed definiranih stilova prikaza zaokretne tablice:

Primijetite da su kontrolni filtri i područja povlačenja aktivni na grafikonu.

Pristup vanjskim podacima

Kao što je već spomenuto, možda se najveći učinak korištenja zaokretnih tablica može dobiti kada se pristupa podacima iz vanjskih izvora - OLAP kocki i upita baze podataka. Takvi izvori obično pohranjuju velike količine informacija, a imaju i predefiniranu relacijsku strukturu, što olakšava definiranje dimenzija višedimenzionalnih podataka (polja zaokretne tablice).

Excel podržava mnoge vrste vanjskih izvora podataka:

Najveći učinak od korištenja vanjskih izvora informacija može se postići korištenjem alata za automatizaciju (VBA programa) kako za dobivanje podataka tako i za njihovu preliminarnu obradu u pivot tablicama.