Käytännöllinen työ olap-tekniikalla excelissä. Olap kuutiot excelissä. Kaavan kirjoittaminen tyhjästä

1 tehtävä (yleinen) (2 pistettä).

Käsittele tietoja tilastofunktioiden avulla laskentataulukon avulla.
1. Luokan oppilaista (10 henkilöä) annetaan tiedot, sisältäen matematiikan yhden kuukauden arvosanat. Laske viisi-, nelos-, kakkos- ja kolmosluku, selvitä kunkin opiskelijan keskipistemäärä ja koko ryhmän keskiarvopisteet. Luo kaavio, joka kuvaa ryhmän arvosanojen prosenttiosuutta.

2.1 tehtävä (2 pistettä).

Neljä ystävää matkustaa kolmella kulkuvälineellä: junalla, lentokoneella ja laivalla. Nikolai purjehti 150 km veneellä, matkusti 140 km junalla ja lensi 1100 km lentäen. Vasily purjehti 200 km veneellä, matkusti 220 km junalla ja lensi 1160 km lentokoneella. Anatoli lensi 1200 km lentäen, matkusti 110 km junalla ja purjehti 125 km veneellä. Maria matkusti 130 km junalla, lensi 1500 km lentokoneella ja purjehti 160 km veneellä.
Rakenna laskentataulukko yllä olevien tietojen perusteella.

Lisää taulukkoon sarake, joka näyttää kunkin kaverin matkustamien kilometrien kokonaismäärän.

Laske kilometrien kokonaismäärä, jonka lapset matkustivat junalla, lensivät lentokoneella ja purjehtivat veneellä (kullakin kuljetustyypillä erikseen).

Laske kaikkien ystävien kilometrien kokonaismäärä.

Määritä ystävien matkustamien kilometrien enimmäis- ja vähimmäismäärä kaikilla kulkuvälineillä.

Määritä keskimääräinen kilometrimäärä kaikille kuljetuksille.

2.2 tehtävä (2 pistettä).

Luo taulukko "Euroopan järvet" käyttämällä seuraavia tietoja pinta-alasta (neliökilometristä) ja suurimmasta syvyydestä (m): Laatoka 17 700 ja 225; Onega 9510 ja 110; Kaspianmeri 371 000 ja 995; Wenern 5550 ja 100; Chudskoje Pskovskin kanssa 3560 ja 14; Balaton 591 ja 11; Geneve 581 ja 310; Wettern 1900 ja 119; Konstanz 538 ja 252; Mälaren 1140 ja 64. Selvitä alueen suurin ja pienin järvi, syvin ja matalin järvi.

2.3 tehtävä (2 pistettä).

Luo taulukko "Euroopan joet" käyttämällä seuraavia tietoja pituudesta (km) ja altaan pinta-alasta (tuhatta neliökilometriä): Volga 3688 ja 1350; Tonava 2850 ja 817; Rein 1330 ja 224; Elbe 1150 ja 148; Veiksel 1090 ja 198; Loire 1020 ja 120; Ural 2530 ja 220; Don 1870 ja 422; Sena 780 ja 79; Thames 340 ja 15. Määritä pisin ja lyhin joki, laske jokien kokonaispinta-ala, jokien keskimääräinen pituus Venäjän Euroopan osassa.

Tehtävä 3 (2 pistettä).

Pankki kirjaa useille organisaatioille myönnettyjen lainojen maksujen oikea-aikaisuuden. Lainasumma ja organisaation jo maksama summa ovat tiedossa. Velallisille määrätään sakkoja: jos yritys on maksanut lainaa takaisin yli 70 prosenttia, sakko on 10 prosenttia velan määrästä, muuten sakko on 15 prosenttia. Laske sakko kullekin organisaatiolle, keskimääräinen sakko, kokonaisrahoitus, jonka pankki aikoo saada lisäksi. Määritä budjettijärjestöjen keskimääräinen sakko.

Etsi materiaalia mihin tahansa oppituntiin,

OLAP-asiakastyökalut ovat sovelluksia, jotka laskevat aggregoidut tiedot (summat, keskiarvot, maksimi- tai vähimmäisarvot) ja näyttävät ne, kun taas itse kootut tiedot ovat välimuistissa tällaisen OLAP-työkalun osoiteavaruudessa.

Jos lähdetiedot sisältyvät työpöydän DBMS-järjestelmään, OLAP-työkalu itse suorittaa aggregoidun tiedon laskennan. Jos alkutietojen lähde on palvelimen DBMS, monet OLAP-asiakastyökalut lähettävät SQL-kyselyitä, jotka sisältävät GROUP BY -käskyn, palvelimelle ja vastaanottavat sen seurauksena palvelimelta laskettuja aggregoituja tietoja.

Pääsääntöisesti OLAP-toiminnallisuus on toteutettu tilastotietojen käsittelytyökaluissa (tämän luokan tuotteista StatSoftin ja SPSS:n tuotteet ovat laajalti käytössä Venäjän markkinoilla) ja joissakin laskentataulukoissa. Erityisesti Microsoft Excelissä on moniulotteisia analyysityökaluja. Tällä tuotteella voit luoda ja tallentaa tiedostona pienen paikallisen moniulotteisen OLAP-kuution ja näyttää siitä kaksi- tai kolmiulotteisia poikkileikkauksia.

Sovelluspaketin lisäosat Microsoft Office tiedon louhinta ovat joukko toimintoja, jotka tarjoavat pääsyn tiedon louhinta- ja käsittelyominaisuuksiin Microsoft Office -sovelluksista, mikä mahdollistaa ennakoivan analyysin paikallinen tietokone. Sen ansiosta, että palvelut on sisäänrakennettu Microsoftin alustat SQL Server Microsoft Office -sovellusympäristöstä saatavien tiedonlouhinta- ja -käsittelyalgoritmien ansiosta yrityskäyttäjät voivat helposti poimia arvokasta tietoa monimutkaisista tietojoukoista muutamalla napsautuksella. Office-tietojen poiminnan ja käsittelyn apuohjelmien avulla loppukäyttäjät voivat suorittaa analyysin suoraan Microsoft Excelissä ja Microsoft Visiossa.

SISÄÄN Microsoftin koostumus Office 2007 sisältää kolme erillistä OLAP-komponenttia:

1. Data Mining Client for Excelin avulla voit luoda ja hallita SSAS-pohjaisia ​​tiedonlouhintaprojekteja Excel 2007:ssä.
2. taulukko-analyysityökalut Excel-sovellukset Voit käyttää SSAS:n sisäänrakennettuja tiedonpoisto- ja käsittelyominaisuuksia Excel-laskentataulukoihin tallennettujen tietojen analysointiin.
3. Visio-tiedonlouhintamallien avulla voit visualisoida päätöspuita, regressiopuita, klusterikaavioita ja riippuvuusverkkoja Visio-kaavioissa.

Ensimmäinen pivot-taulukoiden käyttöliittymä, jota kutsutaan myös pivot-raporteiksi, sisällytettiin Exceliin vuonna 1993 (Excel-versio 5.0). Huolimatta monista hyödyllisistä toiminnallisuutta, useimmat Excel-käyttäjät eivät käytännössä käytä sitä työssään. Kokeneetkin käyttäjät tarkoittavat usein termillä "yhteenvetoraportti" jotain, joka on rakennettu monimutkaisilla kaavoilla. Yritetään popularisoida pivot-taulukoiden käyttöä taloustieteilijöiden päivittäisessä työssä. Artikkelissa käsitellään teoreettinen perusta luoda yhteenvetoraportteja, antaa käytännön suosituksia niiden käyttöön ja tarjota myös esimerkki useisiin taulukoihin perustuvien tietojen käyttämisestä.

Monimuuttujatietojen analyysitermit

Useimmat taloustieteilijät ovat kuulleet termit "moniulotteinen data", "virtuaalinen kuutio", "OLAP-tekniikat" jne. Mutta yksityiskohtaisesta keskustelusta käy yleensä ilmi, että melkein kaikilla ei ole aavistustakaan, mistä he puhuvat. me puhumme. Toisin sanoen ihmiset tarkoittavat jotain monimutkaista, joka ei yleensä liity heidän päivittäiseen toimintaansa. Itse asiassa tämä ei ole totta.

Moniulotteinen data, mittaukset

On turvallista sanoa, että taloustieteilijät kohtaavat lähes jatkuvasti moniulotteista tietoa, mutta he yrittävät esittää sen ennalta määrätyssä muodossa laskentataulukoiden avulla. Moniulotteisuus tarkoittaa tässä kykyä syöttää, tarkastella tai analysoida samaa tietoa muutoksineen ulkomuoto, käyttämällä erilaisia ​​ryhmittelyjä ja tietojen lajittelua. Esimerkiksi myyntisuunnitelma voidaan analysoida seuraavilla kriteereillä:

• tavaratyypit tai -ryhmät;
• tuotemerkit tai tuoteluokat;
• ajanjaksot (kuukausi, vuosineljännes, vuosi);
• ostajat tai ostajaryhmät;
• myyntialueet
• ja niin edelleen.

Jokaista yllä olevista kriteereistä kutsutaan "ulottuvuudeksi" moniulotteisen data-analyysin kannalta. Voidaan sanoa, että mittaus luonnehtii tietoa tietyn arvojoukon mukaan. Erityinen moniulotteisen tiedon mittaustyyppi on "data". Esimerkissämme myyntisuunnitelman tiedot voivat olla:

• myynnin määrä;
• Myyntihinta;
• yksilöllinen alennus
• ja niin edelleen.

Teoriassa data voi olla myös moniulotteisen tiedon vakioulottuvuus (voit esimerkiksi ryhmitellä tiedot myyntihinnan mukaan), mutta data on yleensä silti erityinen arvo.

Voidaan siis sanoa, että sisään käytännön työ Taloustieteilijät käyttävät kahdenlaisia ​​tietoja: moniulotteinen data ( todelliset ja suunnitellut numerot, joilla on monia ominaisuuksia) ja hakuteokset (ominaisuudet tai datamittaukset).

OLAP

Lyhenne OLAP (online analyyttinen käsittely) tarkoittaa kirjaimellisesti "reaaliaikaista analyyttistä käsittelyä". Määritelmä ei ole kovin tarkka, sen alle voidaan sisällyttää melkein mikä tahansa raportti mistä tahansa ohjelmistotuotteesta. Sen merkityksessä OLAP tarkoittaa teknologiaa erikoisraporttien kanssa työskentelyyn, mukaan lukien ohjelmisto, moniulotteisen strukturoidun tiedon hankkimiseen ja analysointiin. Yksi suosituista OLAP-tekniikoita toteuttavista ohjelmistotuotteista on SQL Server Analysis Server. Jotkut jopa pitävät häntä virheellisesti tämän konseptin ohjelmistototeutuksen ainoana edustajana.

Virtuaalinen datakuutio

"Virtuaalinen kuutio" ( moniulotteinen kuutio, OLAP-kuutio) on joidenkin erikoistuneiden ohjelmistotoimittajien ehdottama erikoistermi. OLAP-järjestelmät yleensä valmistelevat ja tallentavat tiedot omiin rakenteisiinsa, ja erityiset analyysirajapinnat (kuten Excelin yhteenvetoraportit) pääsevät käsiksi näiden virtuaalikuutioiden tietoihin. Lisäksi tällaisen erillisen tallennustilan käyttö ei ole ollenkaan välttämätöntä moniulotteisen tiedon käsittelyyn. Yleisesti, virtuaalinen kuutio– Tämä on joukko erityisesti optimoituja moniulotteisia tietoja, joita käytetään yhteenvetoraporttien luomiseen. Se voidaan hankkia joko erikoisohjelmiston kautta tai yksinkertaisen pääsyn kautta tietokantataulukoihin tai mihin tahansa muuhun lähteeseen, kuten Excel-laskentataulukkoon.

Pivot-taulukko

Pivot Table on käyttöliittymä moniulotteisen tiedon näyttämiseen. Tämän käyttöliittymän avulla voit ryhmitellä, lajitella, suodattaa ja järjestää tietoja erilaisten analyyttisten näytteiden saamiseksi. Raportin päivittäminen tapahtuu yksinkertaisin keinoin käyttöliittymä, tiedot kootaan automaattisesti tiettyjen sääntöjen mukaisesti ilman, että vaaditaan lisätietojen tai toistuvien tietojen syöttämistä. Excelin pivot-taulukon käyttöliittymä on ehkä suosituin ohjelmistotuote moniulotteisen tiedon käsittelyyn. Se tukee sekä ulkoisia tietolähteitä (OLAP-kuutiot ja relaatiotietokannat) että sisäisiä laskentataulukkoalueita tietolähteenä. Versiosta 2000 (9.0) alkaen Excel tukee myös graafista muotoa moniulotteisten tietojen näyttämiseen - pivot-kaaviota.

Excelin PivotTable-käyttöliittymän avulla voit järjestää moniulotteisen tiedon mitat laskentataulukkoalueelle. Yksinkertaisuuden vuoksi voit ajatella pivot-taulukkoa raporttina, joka sijaitsee solualueen päällä (itse asiassa solumuodot ovat tietyssä määrin sidottu pivot-taulukon kenttiin). Excel-pivot-taulukossa on neljä tietoa näyttävää aluetta: suodatin, sarakkeet, rivit ja tiedot. Dataulottuvuuksia kutsutaan Pivot-taulukon kentät. Näillä kentillä on omat ominaisuutensa ja näyttömuotonsa.

Haluan vielä kerran huomauttaa, että Excelin pivot-taulukko on tarkoitettu vain tietojen analysointiin ilman mahdollisuutta muokata tietoja. Tarkempi merkitys olisi termin "pivot report" laaja käyttö, ja juuri näin tätä käyttöliittymää kutsuttiin vuoteen 2000 asti. Mutta jostain syystä kehittäjät hylkäsivät sen myöhemmissä versioissa.

Pivot-taulukoiden muokkaaminen

Määritelmänsä mukaan OLAP-tekniikka ei periaatteessa tarkoita mahdollisuutta muuttaa lähdetietoja raporttien kanssa työskennellessä. Markkinoille on kuitenkin muodostunut kokonainen luokka ohjelmistojärjestelmät, joka toteuttaa sekä analyysin että tietojen suoran muokkauksen ominaisuudet moniulotteisissa taulukoissa. Pohjimmiltaan tällaiset järjestelmät keskittyvät budjetointiongelmien ratkaisemiseen.

Excelin sisäänrakennettujen automaatiotyökalujen avulla voit ratkaista monia epätyypillisiä ongelmia. Esimerkki Excelin pivot-taulukoiden muokkaustoteutuksesta laskentataulukkotietojen perusteella löytyy verkkosivuiltamme.

Moniulotteisen tiedon valmistelu

Tullaanpa käytännön sovellus Pivot-taulukot. Yritetään analysoida myyntitietoja eri suuntiin. Tiedosto pivotableexample.xls koostuu useista levyistä. Arkki Esimerkki sisältää perustiedot tietyn ajanjakson myynnistä. Esimerkin yksinkertaistamiseksi analysoimme yksittäistä numeerista indikaattoria – myyntimäärää kg. Tärkeimmät tietomitat ovat: tuote, ostaja ja rahdinkuljettaja (varustamo). Lisäksi niitä on useita lisämitat tiedot, jotka ovat tuotteen ominaisuuksia: tyyppi, merkki, kategoria, toimittaja sekä ostaja: tyyppi. Nämä tiedot kerätään Hakemistot-sivulle. Käytännössä tällaisia ​​mittauksia voi olla paljon enemmän.

Arkki Esimerkki sisältää tavallinen parannuskeino tietojen analysointi – automaattinen suodatin. Katsomalla esimerkkiä taulukon täyttämisestä on selvää, että myyntitiedot päivämäärän mukaan (ne on järjestetty sarakkeisiin) sopivat normaaliin analyysiin. Lisäksi automaattisen suodattimen avulla voit yrittää tehdä yhteenvedon tiedoista yhden tai useamman keskeisen ehdon yhdistelmien perusteella. Merkeistä, luokista ja tyypeistä ei ole mitään tietoa. Tietoja ei voi ryhmitellä automaattisella summauksella tietyn avaimen mukaan (esimerkiksi asiakkaiden mukaan). Lisäksi päivämäärät ovat kiinteät, eikä tietyn ajanjakson, esimerkiksi 3 päivän yhteenvetotietoja ole mahdollista tarkastella automaattisilla keinoilla.

Yleensä ennalta määritetyn päivämääräsijainnin läsnäolo tässä esimerkissä on taulukon suurin haittapuoli. Järjestämällä päivämäärät sarakkeisiin määritimme etukäteen tämän taulukon ulottuvuuden, jolloin estimme itseltämme mahdollisuuden käyttää analyysiä pivot-taulukoiden avulla.

Ensinnäkin meidän on päästävä eroon tästä puutteesta - ts. poista yhden lähdetietoulottuvuuden ennalta määritetty sijainti. Esimerkki oikeasta taulukosta Myynti.

Taulukko on muodoltaan tietokirjausloki. Tässä päivämäärä on sama dataulottuvuus. On myös huomattava, että myöhempää analyysiä varten pivot-taulukoissa rivien suhteellinen sijainti suhteessa toisiinsa (eli lajittelu) on täysin yhdentekevä. Relaatiotietokantojen tietueilla on nämä ominaisuudet. Pivot-taulukoiden käyttöliittymä on ensisijaisesti tarkoitettu suurten tietokantamäärien analysointiin. Siksi sinun on noudatettava näitä sääntöjä, kun työskentelet solualueiden muodossa olevan tietolähteen kanssa. Kukaan ei kuitenkaan kiellä Excelin käyttöliittymätyökalujen käyttöä työssään - pivot-taulukot analysoivat vain dataa, ja lähdesolujen muotoilu, suodattimet, ryhmittelyt ja lajittelu voivat olla mielivaltaisia.

Automaattisuodattimesta yhteenvetoraporttiin

Teoriassa on jo mahdollista tehdä analyysi kolmiulotteisesti käyttämällä Myynti-arkin dataa: tavarat, asiakkaat ja kuljettajat. Tällä lomakkeella ei ole tietoja tuotteiden ja asiakkaiden ominaisuuksista, minkä vuoksi niitä ei voida näyttää yhteenvetotaulukossa. Normaalissa tilassa, jossa luodaan pivot-taulukko lähteelle Excel-tiedot ei salli tietojen linkittämistä useista taulukoista tiettyjen kenttien avulla. Voit kiertää tämän rajoituksen ohjelmisto– katso tämän artikkelin esimerkkitäydennys verkkosivuillamme. Jotta et turvautuisi ohjelmistomenetelmiin tietojen käsittelyssä (varsinkin koska ne eivät ole yleisiä), sinun tulee lisätä lisäominaisuuksia suoraan päiväkirjalomakkeeseen - katso SalesAnalysis-lomake.

VLOOKUP-toimintojen käyttö helpottaa alkuperäisen tiedon täydentämistä puuttuvilla ominaisuuksilla. Nyt automaattisen suodattimen avulla voit analysoida tietoja eri ulottuvuuksissa. Mutta ryhmittymisongelma on edelleen ratkaisematta. Esimerkiksi summan seuraaminen vain tuotemerkkikohtaisesti tiettyinä päivinä on melko ongelmallista. Jos rajoitat itseäsi Excelin kaavat, sinun on muodostettava lisänäytteitä SUMIF-toiminnolla.

Katsotaan nyt, mitä ominaisuuksia pivot-taulukon käyttöliittymä tarjoaa. Arkilla YhteenvetoAnalysis rakensi useita raportteja useiden solujen perusteella, joissa oli taulukkotietoja Myyntianalyysi.

Ensimmäinen analyysitaulukko rakennettiin Excel 2007 -käyttöliittymän kautta Ribbon\Insert\PivotTable(Excel 2000-2003 -valikossa Data\PivotTable).

Toinen ja kolmas taulukko luotiin kopioimalla ja myöhemmin määrittämällä. Kaikkien taulukoiden tietolähde on sama. Voit tarkistaa tämän muuttamalla lähdetietoja, minkä jälkeen sinun on päivitettävä yhteenvetoraportin tiedot.

Meidän näkökulmastamme tiedon näkyvyyden edut ovat ilmeiset. Voit vaihtaa suodattimia, sarakkeita ja rivejä ja piilottaa minkä tahansa kokoisia arvoryhmiä, käyttää manuaalista vetämistä ja pudotusta sekä automaattista lajittelua.

Ominaisuudet ja muotoilu

Tietojen suoran näyttämisen lisäksi pivot-taulukoiden ulkoasun näyttämiseen on laaja valikoima vaihtoehtoja. Voit piilottaa tarpeettomat tiedot suodattimilla. Yksittäiselle elementille tai kentälle on helpompi käyttää kontekstivalikon kohtaa Poistaa(versiossa 2000-2003 Piilottaa).

On myös suositeltavaa asettaa pivot-taulukon muiden elementtien näyttöä ei solumuotoilun avulla, vaan pivot-taulukon kentän tai elementin asettamisen avulla. Tätä varten sinun on siirrettävä hiiren osoitin halutun elementin kohdalle, odotettava, että erityinen kohdistimen muoto (nuolen muodossa) tulee näkyviin, ja valitse sitten valittu elementti yhdellä napsautuksella. Valinnan jälkeen voit muuttaa näkymää nauhan, kontekstivalikon kautta tai kutsua normaalin solumuodon valintaikkunan:

Lisäksi Excel 2007 esitteli monia ennalta määritettyjä Pivot-taulukon näyttötyylejä:

Huomaa, että ohjaussuodattimet ja vetämisalueet ovat aktiivisia kaaviossa.

Pääsy ulkoisiin tietoihin

Kuten jo todettiin, kenties suurin vaikutus pivot-taulukoiden käytöstä voidaan saada tietoja käytettäessä ulkoisista lähteistä– OLAP-kuutiot ja tietokantakyselyt. Tällaiset lähteet tallentavat tyypillisesti suuria määriä tietoa ja niillä on myös ennalta määritetty relaatiorakenne, jonka avulla on helppo määrittää moniulotteisen datan dimensiot (pivot-taulukon kentät).

Excel tukee monenlaisia ​​ulkoisia tietolähteitä:

Suurin vaikutus ulkoisten tietolähteiden käytöstä saadaan käyttämällä automaatiotyökaluja (VBA-ohjelmia) sekä tiedon hankkimiseen että sen esikäsittelyyn pivot-taulukoissa.

Online Analytical Processing (OLAP) on tekniikka, jota käytetään suurten yritystietokantojen järjestämiseen ja liiketoimintatiedon tukemiseen. OLAP-tietokannat on jaettu yhteen tai useampaan kuutioon, ja kuution järjestelmänvalvoja järjestää jokaisen kuution sopimaan tietojen haku- ja analysointitapaan, mikä helpottaa tarvitsemiesi PivotTable-raporttien ja PivotChart-raporttien luomista ja käyttöä.

Tässä artikkelissa

Mitä on yritysanalytiikka?

Liiketoimintaanalyytikko haluaa usein saada suuremman kuvan yrityksestä nähdäkseen laajempia trendejä koostetietojen perusteella sekä tarkastellakseen trendejä useisiin muuttujiin jaoteltuina. Business Intelligence on prosessi, jossa poimitaan tietoja OLAP-tietokannasta ja analysoidaan tiedot, jotta saadaan oivalluksia, joita voidaan käyttää tietoon perustuvien liiketoimintapäätösten ja toimien tekemiseen. Esimerkiksi OLAPin ja liiketoimintaanalytiikan avulla voit vastata seuraaviin yritysdataa koskeviin kysymyksiin.

Miten kaikkien tuotteiden kokonaismyynti vuonna 2007 verrattuna myyntiin vuodesta 2006?

Miten tämä on verrattuna päivämäärään ja kellonaikaan etuusjaksoittain viimeisen viiden vuoden ajalta?

Kuinka paljon asiakkaat käyttivät rahaa 35:een viime vuonna ja miten tämä käyttäytyminen on muuttunut ajan myötä?

Kuinka monta tuotetta myytiin kahdessa tietyssä maassa/alueella tässä kuussa verrattuna samaan kuukauteen viime vuonna?

Mikä on kunkin asiakkaan ikäryhmän kannattavuuden jakautuminen (sekä marginaaliprosentti että kokonaissumma) tuoteluokittain?

Etsi parhaita ja alhaisia ​​myyjiä, jakelijoita, toimittajia, asiakkaita, kumppaneita ja asiakkaita.

Mikä on online-analyyttinen käsittely (OLAP)?

OLAP (Online Analytical Processing) -tietokannat yksinkertaistavat business intelligence -kyselyjä. OLAP on tietokantatekniikka, joka on optimoitu kyselyihin ja raportteihin tapahtumien käsittelyn sijaan. OLAPin tietolähde on online-transaktioiden käsittelyn (OLTP) tietokannat, jotka tyypillisesti tallennetaan tietovarastoihin. OLAP-tiedot poimitaan näistä historiallisista tiedoista ja yhdistetään rakenteiksi, jotka mahdollistavat monimutkaisen analyysin. OLAP-tiedot on myös järjestetty hierarkkisesti ja tallennettu kuutioihin taulukoiden sijaan. Se on monimutkainen tekniikka, joka käyttää moniulotteisia rakenteita tarjotakseen nopea pääsy dataan analysointia varten. Tässä organisaatiossa PivotTable-raportti tai PivotChart-raportti voi helposti näyttää korkean tason yhteenvetotietoja, kuten kokonaisen maan tai alueen myynnin kokonaismäärät, ja myös tietoja sivustoista, joissa myynti on erityisen vahvaa tai heikkoa.

OLAP-tietokannat on suunniteltu nopeuttamaan tietojen lataamista. Koska se on OLAP-palvelin, ei Microsoft Office Excel, laskee aggregoidut arvot, mikä edellyttää pienempien tietojen lähettämistä Exceliin raporttia luotaessa tai muokattaessa. Tämän lähestymistavan avulla voit työskennellä enemmän raakadatan kanssa kuin jos tiedot olisi järjestetty perinteiseen tietokantaan, jossa Excel hakee kaikki yksittäiset tietueet ja laskee koostearvot.

OLAP-tietokannat sisältävät kahta päätyyppiä dataa: mittoja, jotka ovat numeerisia tietoja, määriä ja keskiarvoja, joita käytetään tietoon perustuvien liiketoimintapäätösten tekemiseen, ja dimensiot, jotka ovat näiden mittareiden järjestämiseen käytettyjä luokkia. OLAP-tietokannat auttavat järjestämään tietoja useiden yksityiskohtaisten tasojen avulla käyttämällä samoja luokkia, jotka tiedät tietojen analysointiin.

Seuraavissa osissa kuvataan jokainen komponentti yksityiskohtaisesti alla.

Kuutio Tietorakenne, joka ryhmittelee mitat kunkin analysoitavan ulottuvuuden tasoihin ja hierarkioihin. Kuutiot yhdistävät useita ulottuvuuksia, kuten aika, maantiede ja tuotelinjat yhteenvetotietoihin, kuten myyntiin ja varastoon. Kuutiot eivät ole "kuutioita" tiukasti matemaattisessa mielessä, koska niillä ei välttämättä ole samoja puolia. Ne edustavat kuitenkin osuvaa metaforaa monimutkaiselle käsitteelle.

Mitat Joukko kuutiossa olevia arvoja, jotka perustuvat kuution faktataulukon sarakkeeseen ja joka on tyypillisesti numeerinen arvo. Mitat ovat keskeisiä arvoja kuutiossa, jotka esikäsitellään, käsitellään ja analysoidaan. Yleisimpiä esimerkkejä ovat myynti, tuotot, tulot ja kulut.

Jäsen Hierarkian elementti, joka edustaa yhtä tai useampaa tiedon esiintymää. Elementti voi olla ainutlaatuinen tai ei-ainutlaatuinen. Esimerkiksi 2007 ja 2008 edustavat yksilöllisiä jäseniä aikaulottuvuuden vuositasolla, kun taas tammikuu edustaa ei-yksilöillisiä jäseniä kuukausitasolla, koska aikaulottuvuus sisältää enemmän kuin yhden tammikuun, koska se sisältää tietoja useammalta kuin vuodelta.

Laskettu elementti Dimensiojäsen, jonka arvo lasketaan ajon aikana lausekkeen avulla. Laskettujen jäsenten arvot voidaan johtaa muiden jäsenten arvoista. Esimerkiksi laskennallinen elementti, voitto, voidaan määrittää vähentämällä elementin arvo plus kulut, elementin arvosta, myynti.

mittaus Joukko yhdestä tai useammasta järjestetystä kuutiotasojen hierarkioista, joita käyttäjä ymmärtää ja käyttää tietojen analysoinnin perustana. Maantieteellinen ulottuvuus voi esimerkiksi sisältää maan/alueen, osavaltion/alueen ja kaupunkitason. Lisäksi aikaulottuvuus voi sisältää hierarkian vuoden, vuosineljänneksen, kuukauden ja päivän tasoilla. PivotTable- tai PivotChart-raportissa kustakin hierarkiasta tulee joukko kenttiä, joita voit laajentaa ja tiivistää näyttääksesi alemman tai korkeamman tason.

Hierarkia Looginen puurakenne, joka järjestää ulottuvuuden jäsenet siten, että jokaisella jäsenellä on yksi pääjäsen ja nolla tai useampi alaosa. Lapsi on hierarkian aikaisemman ryhmän jäsen, joka liittyy suoraan nykyiseen jäseneen. Esimerkiksi aikahierarkiassa, joka sisältää tasot neljännes, kuukausi ja päivä, tammikuu on Qtr1:n ali. Pääelementti on hierarkian alemman tason jäsen, joka liittyy suoraan nykyiseen jäseneen. Pääarvo on yleensä kaikkien alielementtien arvojen konsolidointi. Esimerkiksi aikahierarkiassa, joka sisältää tasot neljännes, kuukausi ja päivä, Qtr1 on tammikuun ylätaso.

Taso Hierarkiassa tiedot voidaan järjestää alemmille ja korkeammille tarkkuustasoille, kuten vuosille, neljänneksille, kuukausille ja päivätasoille aikahierarkiassa.

OLAP toimii Excelissä

Haetaan OLAP-tietoja Voit muodostaa yhteyden OLAP-tietolähteisiin samalla tavalla kuin muihin ulkoisiin tietolähteisiin. Voit käsitellä tietokantoja, jotka on luotu käyttämällä Microsoft SQL Server OLAP Services versiota 7.0, Microsoft SQL Server Analysis Services versiota 2000 ja Microsoft SQL Server Analysis Services versiota 2005, Microsoft OLAP -palvelintuotteita. Excel voi toimia myös kolmansien osapuolien OLAP-tuotteiden kanssa, jotka ovat yhteensopivia OLE-DB for OLAP:n kanssa.

OLAP-tiedot voidaan näyttää vain PivotTable-raporttina tai PivotChart-raporttina tai PivotTable-raportista muunnetussa taulukkofunktiossa, mutta ei ulkoisena tietoalueena. Voit tallentaa OLAP-pivot-taulukon ja -kaavion raportteja raporttimalleihin ja luoda Office Data Connection (ODC) -tiedostoja yhteyden muodostamiseksi OLAP-tietokantoihin OLAP-kyselyitä varten. Kun avaat ODC-tiedoston Excelissä, näet tyhjän PivotTable-raportin valmiina sijoitettavaksi.

Kuutiotiedostojen luominen offline-käyttöön Voit luoda erillisen kuutiotiedoston (.cub), jossa on osa OLAP-palvelintietokannan tiedoista. Offline-kuutiotiedostoja käytetään OLAP-tietojen käsittelemiseen, kun et ole yhteydessä verkkoon. Kuution avulla voit työskennellä enemmän Pivot-taulukko- tai PivotChart-raportin datan kanssa kuin muuten ja saada tiedot nopeammin. Voit luoda kuutiotiedostoja vain, jos käytät tätä ominaisuutta tukevaa OLAP-toimittajaa, kuten Microsoft SQL Analysis Services -versiota 2005.

Palvelimen toiminnot Palvelintoiminto on valinnainen toiminto, jonka OLAP-kuution järjestelmänvalvoja voi määrittää palvelimelle, joka käyttää kuutioelementtiä tai mittaa parametrina kyselyssä tietojen hakemiseen kuutiosta tai toisen sovelluksen, kuten selaimen, käynnistämiseksi. Excel tukee URL-osoitteita, raportteja, rivijoukkoja, yksityiskohtia ja palvelinpuolen yksityiskohtia, mutta ei tue omaa natiivilausetta ja tietojoukkoa.

KPI Suorituskyvyn avainindikaattori on palvelimelle määritetty erityinen laskettu mitta, jonka avulla voit seurata "suorituskyvyn keskeisiä indikaattoreita", mukaan lukien tila (nykyinen arvo vastaa tiettyä numeroa). ja trendi (arvot ajan myötä). Kun ne näkyvät, palvelin voi lähettää vastaavat kuvakkeet, jotka ovat samanlaisia ​​kuin uusi Excel-kuvake, asettumaan tilatason ylä- tai alapuolelle (esimerkiksi pysäytyskuvaketta varten) sekä pyörittämään arvoa ylös- tai alaspäin (esim. suuntanuolikuvake).

Alustaminen palvelimella Kuution järjestelmänvalvojat voivat luoda mittoja ja laskettuja jäseniä käyttämällä värimuotoilua, kirjasinmuotoilua ja ehdollisia muotoilusääntöjä, jotka voidaan määrittää yrityksen vakiomuotoisiksi liiketoimintasäännöiksi. Esimerkiksi tulojen palvelinmuoto voi olla valuuttanumeromuoto, solun väri on vihreä, jos arvo on suurempi tai yhtä suuri kuin 30 000, ja punainen, jos arvo on pienempi kuin 30 000, ja kirjasintyyli on lihavoitu, jos arvo on pienempi tai yhtä suuri kuin 30 000 ja jos arvo on positiivinen - tavallinen. suurempi tai yhtä suuri kuin 30 000 Etsi lisätietoja.

Officen käyttöliittymän kieli Kuution järjestelmänvalvoja voi määrittää käännökset palvelimella oleville tiedoille ja virheille käyttäjille, joiden on tarkasteltava Pivot-taulukon tietoja eri kielellä. Tämä toiminto on määritetty tiedostoyhteyden ominaisuudeksi, ja käyttäjän tietokoneen kieli- ja maa-asetusten on vastattava käyttöliittymän kieltä.

OLAP-tietolähteiden käyttämiseen tarvittavat ohjelmistokomponentit

OLAP-palveluntarjoaja Jotta voit määrittää OLAP-tietolähteet Excelille, tarvitset jonkin seuraavista OLAP-palveluntarjoajista.

Microsoft OLAP Provider Excel sisältää tietolähdeohjaimen ja asiakasohjelmiston Microsoft SQL Server olap Services versiolla 7.0, Microsoft SQL Server olap versiolla 2000 (8.0) ja Microsoft SQL Server Analysis Services versiolla 2005 (9 ,0) luotuihin tietokantoihin.

Kolmannen osapuolen OLAP-palveluntarjoajat Muut OLAP-tuotteet vaativat lisäohjaimien ja asiakasohjelmistojen asentamisen. Jotta Excelin ominaisuuksia voidaan käyttää OLAP-tietojen kanssa, kolmannen osapuolen tuotteen on oltava OLAP-standardin OLE-DB-standardin mukainen ja yhteensopiva Microsoft Officen kanssa. Lisätietoja kolmannen osapuolen OLAP-palveluntarjoajan asentamisesta ja käytöstä on kohdassa Järjestelmänvalvoja tai OLAP-tuotteesi myyjältä.

Palvelintietokannat ja kuutiotiedostot Excelin OLAP-asiakasohjelmisto tukee yhteyksiä kahden tyyppisiin OLAP-tietokantoihin. Jos OLAP-palvelimen tietokanta on online-tilassa, voit hakea lähdetiedot suoraan siitä. Jos sinulla on erillinen kuutiotiedosto, joka sisältää OLAP-tietoja tai kuutiomääritystiedoston, voit muodostaa yhteyden kyseiseen tiedostoon ja hakea siitä lähdetietoja.

Tietolähteet Tietolähde tarjoaa pääsyn kaikkiin OLAP-tietokannan tai offline-kuutiotiedoston tietoihin. Kun olet luonut OLAP-tietolähteen, voit perustaa raportit siihen ja palauttaa OLAP-tiedot Exceliin PivotTable-raporttina tai PivotChart-raporttina tai PivotTable-raportista muunnetussa taulukkofunktiossa.

Microsoft Query Queryn avulla voit hakea tietoja ulkoisesta tietokannasta, kuten Microsoft SQL tai Microsoft Access. Sinun ei tarvitse käyttää kyselyä tietojen hakemiseen kuutiotiedostoon liitetystä OLAP-pivot-taulukosta. Lisäinformaatio .

Erot OLAP- ja ei-OLAP-lähdetietojen ominaisuuksissa

Jos käytät PivotTable-raportteja ja Pivot-kaavioita OLAP-lähdetiedoista ja muista lähdetietotyypeistä, näet joitakin ominaisuuksien eroja.

Tietojen poiminta OLAP-palvelin palauttaa uusia tietoja Exceliin aina, kun raportin asettelu muuttuu. Muun tyyppisissä ulkoisissa tietolähteissä kyselyt tehdään kaikista lähdetiedoista kerralla tai voit määrittää parametrit, joita haetaan vain, kun näytetään erilaisia ​​raporttisuodatinkenttäelementtejä. Lisäksi sinulla on useita muita vaihtoehtoja raportin päivittämiseen.

OLAP-lähdetietoihin perustuvissa raporteissa raporttisuodatinkentän asetukset eivät ole käytettävissä, taustakysely ei ole käytettävissä eikä muistin optimointivaihtoehto ole käytettävissä.

Huomautus: Muistin optimointivaihtoehto ei ole myöskään käytettävissä OLEDB-tietolähteille ja solualueisiin perustuville PivotTable-raporteille.

Kenttätyypit OLAP-lähdetiedot. ulottuvuuskenttiä voidaan käyttää vain riveinä (riveinä), sarakkeina (luokka) tai sivukentänä. Mittakenttiä voidaan käyttää vain arvokenttinä. Muissa lähdetietotyypeissä kaikkia kenttiä voidaan käyttää missä tahansa raportin osassa.

Pääsy yksityiskohtaisiin tietoihin OLAP-lähdetiedoille palvelin määrittää käytettävissä olevat yksityiskohdat ja laskee yhteenvetoarvot, joten yhteenvetoarvot muodostavat tietotietueet eivät välttämättä ole käytettävissä. Palvelin voi kuitenkin tarjota ominaisuuskenttiä, jotka voit näyttää. Muissa lähdetietotyypeissä ei ole ominaisuuskenttiä, mutta voit näyttää perustiedot tietokenttä- ja elementtiarvoista sekä näyttää elementtejä ilman tietoja.

OLAP-raporttisuodatinkentillä ei ehkä ole Kaikki elementtejä ja joukkuetta Näytä raporttisuodatinsivut ei saatavilla.

Alkuperäinen lajittelujärjestys OLAP-lähdetiedoissa elementit näytetään ensin siinä järjestyksessä, jossa OLAP-palvelin palauttaa ne. Voit lajitella tai järjestellä kohteita uudelleen manuaalisesti. Muissa lähdetietotyypeissä uuden raportin elementit lajitellaan ensin elementin nimen mukaan nousevaan järjestykseen.

Nimi OLAP-palvelimet tarjoavat yhteenvetoarvot suoraan raporttiin, joten et voi muuttaa arvokenttien yhteenvetotoimintoja. Muissa lähdetietotyypeissä voit muuttaa arvokentän koontifunktiota ja käyttää useita yhteenvetofunktioita samalle arvokentälle. Et voi luoda laskettuja kenttiä ja laskettuja jäseniä raportteihin, joissa on OLAP-lähdetietoja.

Välisummat Raporteissa, joissa on OLAP-lähdetietoja, et voi muuttaa välisummien yhteenvetotoimintoa. Muilla lähdetietotyypeillä voit muuttaa välisummien kokonaisfunktioita ja näyttää tai piilottaa kaikkien rivi- ja sarakekenttien välisummat.

OLAP-lähdetiedoissa voit sisällyttää tai sulkea pois piilotettuja jäseniä, kun lasket välisummia ja loppusummia. Muissa lähdetietotyypeissä voit sisällyttää raporttisuodatinkenttien piilotetut elementit välisummiin, mutta muiden kenttien piilotetut elementit suljetaan oletuksena pois.