Az OLAP klienseszközök olyan alkalmazások, amelyek összesített adatokat (összegeket, átlagokat, maximumokat, ill minimális értékeket).

Ha a forrásadatokat egy asztali DBMS tartalmazza, akkor az összesített adatok kiszámítását maga az OLAP eszköz végzi el. Ha a forrásadatok forrása egy kiszolgálói DBMS, akkor sok ügyfél OLAP-eszköz GROUP BY záradékot tartalmazó SQL-lekérdezéseket küld a kiszolgálónak, és ennek eredményeként a kiszolgálón kiszámított összesített adatokat kap.

Általános szabály, hogy az OLAP funkcionalitást statisztikai adatfeldolgozó eszközökben valósítják meg (az ebbe az osztályba tartozó termékektől a orosz piac A StatSoft és az SPSS termékeket széles körben használják) és egyes táblázatokban. Különösen a többváltozós elemzés eszközei Microsoft Excel. Ezzel a termékkel létrehozhat és fájlként menthet egy kis helyi többdimenziós OLAP kockát, és megjelenítheti annak 2D vagy 3D metszeteit.

Alkalmazáscsomag-kiegészítők Microsoft Office Az adatkinyeréshez és -feldolgozáshoz olyan funkciók csoportja, amelyek hozzáférést biztosítanak a Microsoft Office alkalmazásokból származó adatkinyerési és -feldolgozási képességekhez, és ezáltal lehetővé teszik a prediktív elemzést helyi számítógép. Annak a ténynek köszönhetően, hogy a beépített szolgáltatások Microsoft platformok Az SQL Server adatkinyerési és -feldolgozó algoritmusai elérhetőek a Microsoft Office alkalmazáskörnyezetből, az üzleti felhasználók könnyen kinyerhetnek értékes információkat összetett halmazok adatok csak néhány kattintással. A Data Extraction and Processing Office Suite bővítmény lehetővé teszi a végfelhasználók számára, hogy elemzéseket végezzenek közvetlenül a Microsoft Excel és a Microsoft Visio alkalmazásokban.

A Microsoft Office 2007 három különálló OLAP-összetevőt tartalmaz:

1. A Data Extraction and Processing Client for Excel segítségével SSAS-alapú adatkinyerési és feldolgozási projekteket hozhat létre és kezelhet az Excel 2007-en belül;
2. táblázatelemző eszközök Excel alkalmazások lehetővé teszi az SSAS beépített információ-kinyerési és -feldolgozási funkcióinak használatát az Excel-táblázatokban tárolt adatok elemzéséhez;
3. A Visio alkalmazás adatkinyerési és -kezelési sablonjai lehetővé teszik döntési fák, regressziós fák, fürtdiagramok és függőségi hálózatok megjelenítését Visio diagramokon.

1.1. táblázat. Oracle termékek OLAP és üzleti intelligencia számára

Alap típusa	Termék

Az első PivotTable felület, más néven Pivot Reports, 1993-ban került be az Excelbe ( Excel verziók 5.0). Annak ellenére, hogy sok hasznos funkcionalitás, a legtöbb Excel-felhasználó munkájában gyakorlatilag nem használják. Még a tapasztalt felhasználók is gyakran az "összefoglaló jelentés" kifejezés alatt olyasmit értenek, ami összetett képletekkel készült. Próbáljuk meg népszerűsíteni a pivot táblák használatát a közgazdászok mindennapi munkájában. Ez a cikk az összefoglaló jelentések készítésének elméleti alapjait tárgyalja, gyakorlati javaslatokat ad ezek használatára, valamint példát ad több táblázat alapján az adatok elérésére.

A többváltozós adatelemzés feltételei

A legtöbb közgazdász hallott már a „többdimenziós adatok”, „virtuális kocka”, „OLAP technológiák” stb. kifejezéseket. De egy részletes beszélgetés során általában kiderül, hogy szinte mindenki nem igazán tudja, mit kérdéses. Ez azt jelenti, hogy az emberek valami összetettet jelentenek, és általában nem relevánsak napi tevékenységeik szempontjából. Valójában nem.

Többdimenziós adatok, méretek

Nyugodtan kijelenthetjük, hogy a közgazdászok szinte mindig többdimenziós adatokkal foglalkoznak, de ezeket előre meghatározott módon igyekeznek bemutatni táblázatok segítségével. A többdimenziósság itt azt a képességet jelenti, hogy ugyanazt az információt változtatással be lehet vinni, megtekinteni vagy elemezni kinézet, az adatok különféle csoportosításait és rendezéseit alkalmazva. Például egy értékesítési tervet a következő kritériumok szerint lehet elemezni:

• árutípusok vagy -csoportok;
• márkák vagy termékkategóriák;
• időszakok (hónap, negyedév, év);
• vásárlók vagy vevők csoportjai;
• értékesítési régiók
• stb.

A többváltozós adatelemzés fenti kritériumainak mindegyikét "dimenziónak" nevezik. Azt mondhatjuk, hogy egy dimenzió egy bizonyos értékkészletre vonatkozó információt jellemzi. A többdimenziós információ mérésének egy speciális típusa az „adat”. Példánkban az értékesítési terv adatai a következők lehetnek:

• értékesítés volumene;
• Eladási ár;
• egyéni kedvezmény
• stb.

Elméletileg az adatok a többdimenziós információk szabványos dimenziói is lehetnek (például csoportosíthatja az adatokat eladási ár alapján), de az adatok általában egy speciális értéktípust jelentenek.

Így elmondható, hogy in praktikus munka A közgazdászok kétféle információt használnak: többdimenziós adatok ( tényleges és tervezett számok sok funkcióval) és könyvtárak (adatok jellemzői vagy mérései).

OLAP

Az OLAP (online analitikai feldolgozás) rövidítés szó szerinti fordításban úgy hangzik, mint „valós idejű analitikai feldolgozás”. A definíció nem túl konkrét, szinte bármilyen szoftvertermékről szóló jelentés összefoglalható ez alatt. Az OLAP definíció szerint olyan technológiát jelent, amely speciális jelentésekkel való munkavégzésre szolgál, beleértve a szoftvereket is, többdimenziós strukturált adatok megszerzésére és elemzésére. Az OLAP technológiákat megvalósító népszerű szoftvertermékek egyike az SQL Server Analysis Server. Vannak, akik tévesen őt tartják a koncepció szoftveres megvalósításának egyetlen képviselőjének.

Virtuális adatkocka

A "virtuális kocka" (többdimenziós kocka, OLAP-kocka) egy szakkifejezés, amelyet néhány speciális gyártó használt. szoftver. Az OLAP rendszerek általában saját struktúrájukban készítik elő és tárolják az adatokat, és ezekből a virtuális kockákból speciális elemzési felületek (például Excel összefoglaló jelentések) érik el az adatokat. Ugyanakkor egy ilyen dedikált tároló használata egyáltalán nem szükséges többdimenziós információk feldolgozásához. Általánosságban, virtuális kocka- ez egy speciálisan optimalizált többdimenziós adattömb, amelyet összefoglaló jelentések készítésére használnak. Speciális szoftvereszközökkel és adatbázistáblázatokhoz vagy bármely más forráshoz, például Excel-táblázathoz való egyszerű hozzáféréssel is beszerezhető.

Pivot tábla

"Pivot Report" (pivot table, Pivot Table) egy felhasználói felület többdimenziós adatok megjelenítésére. Ezen a felületen csoportosíthatja, rendezheti, szűrheti és módosíthatja az adatokat, hogy különböző analitikai mintákat kapjon. A riport frissítése egyszerű kezelőfelületi eszközökkel történik, az adatok a megadott szabályok szerint automatikusan összesítésre kerülnek, és nincs szükség további információk vagy újbóli bevitelére. Az Excel pivot tábla felülete talán a legnépszerűbb szoftvertermék a többdimenziós adatokkal való munkavégzéshez. Mind a külső adatforrásokat (OLAP-kockák és relációs adatbázisok), mind a belső táblázat-tartományokat támogatja adatforrásként. A 2000-es verziótól (9.0) az Excel is támogatja grafikus forma többdimenziós adatok megjelenítése - Pivot Chart.

Az Excelben megvalósított PivotTable felület lehetővé teszi a többdimenziós adatok dimenzióinak elrendezését a munkalapterületen. Az egyszerűség kedvéért a kimutatástáblázatot úgy is felfoghatja, mint egy cellatartomány tetején elhelyezkedő jelentést (valójában a cellaformátumok bizonyos módon kötődnek a kimutatástábla mezőihez). Az Excel-kimutatás négy megjelenítési területtel rendelkezik: szűrő, oszlopok, sorok és adatok. Az adatdimenziók el vannak nevezve pivot tábla mezői. Ezeknek a mezőknek saját tulajdonságaik és megjelenítési formátumuk van.

Még egyszer szeretném felhívni a figyelmet arra, hogy az Excel pivot tábla kizárólag adatelemzésre szolgál, információszerkesztési lehetőség nélkül. Közelebbi jelentéssel bírna a „pivot jelentés” (Pivot Report) kifejezés széles körben elterjedt használata, és így hívták ezt a felületet egészen 2000-ig. De valamilyen oknál fogva a későbbi verziókban a fejlesztők elhagyták.

Pivot táblák szerkesztése

Definíciója szerint az OLAP technológia elvileg nem jelenti a forrásadatok megváltoztatásának lehetőségét a jelentésekkel végzett munka során. Azonban egy egész osztály szoftverrendszerek, megvalósítva az adatok többdimenziós táblázatokban történő elemzésének és közvetlen szerkesztésének lehetőségét. Az ilyen rendszerek alapvetően a költségvetési problémák megoldására irányulnak.

Az Excel beépített automatizálási eszközeivel számos, nem szabványos feladatot is megoldhat. Példa a pivot táblák szerkesztésére Az Excel alapú adatmunkalap megtalálható weboldalunkon.

Többdimenziós adatok előkészítése

Közelítsük meg a pivot táblák gyakorlati alkalmazását. Próbáljuk meg elemezni az értékesítési adatokat különböző irányú. Fájl pivotableexample.xls több lapból áll. Lap Példa alapvető információkat tartalmaz az értékesítésről egy bizonyos időszakra vonatkozóan. A példa egyszerűsége érdekében elemezzük az egyetlen számszerű mutatót - az eladások mennyiségét kg-ban. A következő kulcsfontosságú adatdimenziók állnak rendelkezésre: termék, vevő és szállító ( szállitó cég). Ezen kívül számos további adatdimenzió is létezik, amelyek a termék attribútumai: típus, márka, kategória, szállító, valamint vevő: típus. Ezeket az adatokat a Címtár lapon gyűjtjük össze. A gyakorlatban sokkal több ilyen mérés lehet.

Lap Példa tartalmaz standard jogorvoslat adatelemzés - autofilter. A táblázat kitöltési példáját tekintve nyilvánvaló, hogy a dátum szerinti értékesítési adatok (oszlopokba rendezve) alkalmasak a normál elemzésre. Ezenkívül az automatikus szűrő használatával megpróbálhatja összegezni az adatokat egy vagy több kulcskritérium kombinációi alapján. Márkákról, kategóriákról és típusokról egyáltalán nincs információ. Nem lehetséges az adatok csoportosítása automatikus összesítéssel egy adott kulcs szerint (például ügyfelek szerint). Ezenkívül a dátumok készlete rögzített, és megtekintheti az összefoglaló információkat egy bizonyos időszakra, például 3 napra, automatikus eszközökkel nem sikerül.

Általánosságban elmondható, hogy ebben a példában egy előre meghatározott dátumhely jelenléte a táblázat fő hátránya. A dátumok oszlopok szerinti rendezésével mintegy előre meghatároztuk ennek a táblának a dimenzióját, így megfosztottuk magunkat attól a lehetőségtől, hogy pivot táblákat használjunk.

Először is meg kell szabadulnunk ettől a hiányosságtól – pl. távolítsa el a forrásadatok egyik dimenziójának előre meghatározott helyét. Érvényes táblázat például egy munkalap Értékesítés.

A táblázat információbeviteli napló formájában készült. Itt a dátum egyenlő adatdimenziót jelent. Azt is meg kell jegyezni, hogy a pivot táblákban történő későbbi elemzéshez a sorok egymáshoz viszonyított relatív helyzete (más szóval a rendezés) teljesen közömbös. A relációs adatbázisokban lévő rekordok rendelkeznek ezekkel a tulajdonságokkal. A nagy mennyiségű adatbázis elemzése elsősorban a pivot táblák felületére összpontosít. Ezért ezeket a szabályokat be kell tartania, ha cellatartományok formájában lévő adatforrással dolgozik. Ugyanakkor senki sem tiltja, hogy az Excel felület eszközeit használja a munkája során – a pivot táblák csak adatokat elemeznek, a formázás, a szűrők, a forráscellák csoportosítása és rendezése tetszőleges lehet.

Az automatikus szűrőtől az összefoglaló jelentésig

Elméletileg az Értékesítési lap adatain már három dimenzióban lehet elemezni: áruk, vásárlók és fuvarozók. A termékek és vásárlók tulajdonságaira vonatkozóan ezen a lapon nem szerepel adat, ami ennek megfelelően nem teszi lehetővé azok megjelenítését az összefoglaló táblázatban. A forrásadatokhoz való pivot tábla létrehozásának normál módjában az Excel nem teszi lehetővé több tábla adatainak összekapcsolását bizonyos mezők szerint. Megkerülheti ezt a korlátozást szoftver eszközök- cm. kiegészítő példa ehhez a cikkhez a weboldalunkon. Annak érdekében, hogy ne vegye igénybe az információfeldolgozás programmódszereit (különösen, mivel ezek nem univerzálisak), hozzá kell tennie további jellemzők közvetlenül a naplóbejegyzési űrlapra – lásd a SalesAnalysis lapot.

A VLOOKUP függvények használata megkönnyíti az eredeti adatok hiányzó jellemzőkkel való kiegészítését. Most az automatikus szűrő alkalmazásával elemezheti az adatokat különböző dimenziók. De a csoportosítás problémája továbbra is megoldatlan. Például elég problémás az összeg nyomon követése csak a márkák esetében bizonyos dátumokon. Ha az Excel képletekre korlátozódik, akkor további mintákat kell készítenie a SUMIF függvény segítségével.

Most pedig nézzük meg, milyen funkciókat kínál a pivot table felület. A lapon CodeAnalysis több jelentést készített egy sor cella alapján lapadatokkal Értékesítési elemzés.

Az első elemző tábla az Excel 2007 felületén keresztül épül fel Szalag \ Beszúrás \ Kimutatás(Excel 2000-2003 menüben Data\PivotTable).

A második és harmadik tábla másolással és későbbi testreszabással jön létre. Az összes tábla adatforrása ugyanaz. Ezt az eredeti adatok megváltoztatásával ellenőrizheti, majd frissítenie kell az összesítő jelentések adatait.

A mi szempontunkból az információ láthatóságának előnyei nyilvánvalóak. Cserélhet szűrőket, oszlopokat és sorokat, elrejthet bizonyos értékcsoportokat bármely dimenzióból, használhat kézi húzást és automatikus rendezést.

Tulajdonságok és formázás

Az adatok közvetlen megjelenítése mellett számos lehetőség áll rendelkezésre a pivot táblák megjelenésének megjelenítésére. A további adatok elrejthetők szűrők segítségével. Egyetlen elemhez vagy mezőhöz egyszerűbb a helyi menüelem használata Töröl(2000-2003-as verzióban Elrejt).

A pivot tábla többi elemének megjelenítését is kívánatos nem cellaformázással, hanem a pivot tábla mezőjének vagy elemének beállításával beállítani. Ehhez vigye az egérmutatót a kívánt elemre, várja meg egy speciális kurzor alakzat megjelenését (nyíl formájában), majd egyetlen kattintással jelölje ki a kiválasztott elemet. A kijelölés után a nézetet a szalagon keresztül módosíthatja, helyi menü vagy hívja a szabványos cellaformátum párbeszédpanelt:

Ezenkívül az Excel 2007 számos előre definiált kimutatási stílust vezetett be:

Figyelje meg, hogy a vezérlőszűrők és a húzási területek aktívak a diagramon.

Hozzáférés a külső adatokhoz

Mint már említettük, a pivot táblák használatának talán a legnagyobb hatása akkor érhető el, ha külső forrásokból – OLAP-kockákból és adatbázis-lekérdezésekből – férünk hozzá az adatokhoz. Az ilyen források általában nagy mennyiségű információt tárolnak, és előre definiált relációs szerkezettel rendelkeznek, ami megkönnyíti a többdimenziós adatok dimenzióinak meghatározását (pivot tábla mezők).

Az Excel számos típusú külső adatforrást támogat:

A külső információforrások felhasználásából a legnagyobb hatást automatizálási eszközök alkalmazásával érhetjük el ( VBA programok) mind az adatgyűjtéshez, mind pedig azok pivot táblákban történő előfeldolgozásához.

Az analitika, OLAP, adattárházak problémái egyre jobban érdeklik az orosz informatikusokat. Számítógépes sajtónk és az internet a mai napig nagyon sok jó, akadémikus jellegű anyagot közölt ebben a témában, beleértve a bevezetőket is. Figyelmébe ajánlunk egy cikket, amelyben szándékosan megpróbáljuk „ujjakon” elmagyarázni az OLAP-ot egy konkrét példa segítségével. A gyakorlat azt mutatja, hogy ilyen magyarázatra van szükség egyes informatikai szakemberek és különösen a végfelhasználók számára.

Tehát az OLAP *1, mint első közelítés, „az ujjakon”, az adatok elemzésének és a jelentések készítésének speciális módjaként definiálható. Lényege, hogy a felhasználó rendelkezésére áll egy többdimenziós táblázat, amely automatikusan összesíti az adatokat különböző szakaszokban, és lehetővé teszi a számítások és a jelentés formájának interaktív kezelését. Ez a cikk az OLAP technológiájáról és alapműveleteiről fog beszélni egy élelmiszer-nagykereskedelemmel foglalkozó vállalkozás számláinak elemzésének példáján keresztül.

*1. OLAP - On-Line Analytical Processing, operatív adatok elemzése.

Eszközként a legegyszerűbb és legolcsóbb osztályba tartozó OLAP rendszert, az OLAP klienst *1 veszik számításba. Például az OLAP-kliensek számából a legegyszerűbb terméket választják - az Intersoft Lab „Contour Standard”-ját. (Az érthetőség kedvéért a cikk későbbi részében az általánosan elfogadott OLAP-kifejezéseket félkövér betűkkel jelöljük, és az angol megfelelőkkel együtt.)

*1. Az OLAP-rendszerek besorolásával kapcsolatos további információkért tekintse meg az "OLAP Made in Russia" című cikket a PC Week/RE-ben, 3/2001.

Tehát kezdjük a rendszerrel. Először le kell írnia az adatforrást (adatforrást) - a tábla elérési útját és mezőit. Ez annak a felhasználónak a feladata, aki ismeri az adatbázis fizikai megvalósítását. A végfelhasználók számára lefordítja a tábla nevét és a mezőit tartományi kifejezésekre. Az „adatforrás” mögött egy helyi tábla, tábla vagy SQL szerver nézet vagy tárolt eljárás található.

Valószínűleg egy adott adatbázisban a számlákat nem egy, hanem több táblában tárolják. Ezenkívül előfordulhat, hogy egyes mezők vagy rekordok nem használhatók elemzésre. Ezért egy Minta (eredménykészlet vagy lekérdezés) jön létre, amelyben a következők vannak konfigurálva: a táblák kulcsmezők szerinti kombinálásának algoritmusa, szűrési feltételek és a visszaadott mezők halmaza. Nevezzük el válogatásunkat „Számlák”-nak, és ebbe tegyük bele a „Számlák” adatforrás összes mezőjét. Így egy informatikus egy szemantikai réteg létrehozásával elrejti az adatbázis fizikai megvalósítását a végfelhasználó elől.

Ezután az OLAP jelentés konfigurálva van. Ezt megteheti a téma szakértője. Először is, a lapos adatminta mezőit két csoportra osztjuk: tényekre (tények vagy intézkedések) és mérésekre (dimenziók). A tények számok, a mérések pedig "kivágások", amelyekben a tények összegzésre kerülnek. Példánkban a dimenziók a következők lesznek: „Régió”, „Város”, „Ügyfél”, „Termék”, „Dátum”, a tény pedig egy lesz - a számla „Összeg” mezője. Valójában egy vagy több összesítő algoritmust kell kiválasztani. Az OLAP nem csak az eredmények összegzésére, hanem összetettebb számítások elvégzésére is képes, egészen a statisztikai elemzésig. Több összesítő algoritmus kiválasztása virtuális, számított tényeket hoz létre. A példában egy aggregációs algoritmus van kiválasztva - „Összeg”.

Az OLAP rendszerek speciális tulajdonsága a régebbi időszakokra vonatkozó dimenziók és adatok generálása egy dátum alapján, valamint az ezen időszakokra vonatkozó összesítések automatikus kiszámítása. Válasszuk ki az „Év”, „Negyed” és „Hónap” periódusokat, miközben az egyes napokra vonatkozóan nem lesz adat a jelentésben, de megjelennek a generált „Év”, „Negyed” és „Hónap” dimenziók. Nevezzük el a jelentést „Értékesítési elemzés”-nek, és mentsük el. Befejeződött az elemző alkalmazás felületének elkészítési munkája.

Most, amikor ezt a felületet naponta vagy havonta futtatja, a felhasználó egy táblázatot és grafikont fog látni, amelyben a számlák tételenként, vevőnként és időszakonként vannak összesítve.

Annak érdekében, hogy az adatokkal való manipuláció intuitív legyen, a dinamikus táblázat kezelésének eszközei magának a táblázatnak az elemei - oszlopai és sorai. A felhasználó mozgathat, törölhet, szűrhet és egyéb OLAP-műveleteket hajthat végre. A táblázat automatikusan kiszámítja az új részösszegeket és végső összegeket.

Például a „Termék” oszlop első helyre húzásával („áthelyezés” művelet) összehasonlító jelentést kapunk - „A termékek értékesítési volumenének összehasonlítása az évre”. Egy évre vonatkozó adatok összesítéséhez húzza a „Negyed” és a „Hónap” oszlopot a táblázat tetejére – „inaktív mérési terület”. Az erre a területre átvitt „Negyed” és „Hónap” dimenzió zárva lesz („dimenzió bezárása” művelet), azaz kizárásra kerül a jelentésből; évre vonatkozóan összegzik a tényeket. Annak ellenére, hogy a dimenziók zártak, az adatszűréshez ("szűrő" művelet) konkrét évek, negyedévek és hónapok állíthatók be.

A jobb áttekinthetőség érdekében változtassuk meg az OLAP táblát illusztráló grafikon típusát és annak helyét a képernyőn.

Az adatokba való elmélyülés („drill down” művelet) lehetővé teszi, hogy részletesebb információkhoz jusson a minket érdeklő termék eladásairól. A „Kávé” termékkel szembeni „+” jelre kattintva láthatjuk annak értékesítési volumenét régiónként. Az uráli régió megnyitása után értékesítési volumeneket kapunk az uráli régió városaival összefüggésben, belemélyedve a Jekatyerinburg adataiba, megtekinthetjük a város nagykereskedelmi vásárlóinak adatait.

A szűrők beállításához nyílt méréseket is használhat. A moszkvai és jekatyerinburgi édességeladások dinamikájának összehasonlításához állítsunk be szűrőket a „Termék” és a „Város” dimenziókhoz.

Zárja be a szükségtelen méréseket, és válassza ki a "Vonal" grafikon típusát. Az eredményül kapott grafikonon nyomon követheti az eladások dinamikáját, értékelheti a szezonális ingadozásokat, valamint a csökkenések és az áruk értékesítésének növekedése közötti kapcsolatot a különböző városokban.

Így meg voltunk győződve arról, hogy az OLAP technológia lehetővé teszi a felhasználó számára, hogy egy felületről tucatnyi különféle jelentést bocsásson ki, egy dinamikus OLAP táblát az egérrel kezelve. Az ilyen eszközzel rendelkező programozó feladata nem a jelentési űrlapok rutinkódolása, hanem egy OLAP kliens beállítása az adatbázisokhoz. Ugyanakkor a jelentéskezelési módszerek intuitívak a végfelhasználó számára.

Valójában az OLAP a táblázatok ötletének természetes kiterjesztése és továbbfejlesztése. Lényegében az OLAP vizuális interfész is egy táblázatkezelő, de egy erőteljes számítási motorral és egy speciális adatmegjelenítési és -kezelési szabvánnyal van felszerelve. Ezenkívül egyes OLAP-kliensek az MS Excel bővítményeiként vannak implementálva. Ezért a táblázatokban magabiztos „fehérgallérosok” milliomodik serege is gyorsan elsajátítja az OLAP-eszközöket. Számukra ez egy „bársonyos forradalom”, amely új lehetőségeket biztosít, de nem jár átképzési kötelezettséggel.

Ha az olvasó a cikk elolvasása után nem veszítette el érdeklődését az OLAP iránt, akkor az elején említett anyagokra hivatkozhat. Az ilyen anyagok gyűjteményeit számos internetes webhelyen teszik közzé, beleértve az Intersoft labor webhelyet - www.iso.ru. Innen letöltheti a Kontur Standard rendszer demó verzióját is a cikkben leírt példával.

Egy szabványos kimutatásban a forrásadatok a helyi merevlemezen tárolódnak. Így bármikor kezelheti és átszervezheti őket, még akkor is, ha nincs hozzáférése a hálózathoz. Ez azonban semmiképpen sem vonatkozik az OLAP kimutatásokra. Az OLAP PivotTable-ban a gyorsítótár soha nem tárolódik a helyi merevlemezen. Ezért közvetlenül a lekapcsolás után helyi hálózat a kimutatás meghibásodik. Egyik mezőt sem tudja majd áthelyezni benne.

Ha az offline állapotba kapcsolás után is elemeznie kell az OLAP-adatokat, hozzon létre egy offline adatkockát. Az offline adatkocka az külön fájl, amely egy kimutatás-gyorsítótár, amely a helyi hálózatról való leválasztás után megtekintett OLAP-adatokat tárolja. A pivot táblába másolt OLAP adatok kinyomtathatók, a http://everest.ua oldal ezt részletesen leírja.

Egy önálló adatkocka létrehozásához először hozzon létre egy OLAP-kimutatást. Vigye a kurzort a kimutatásba, és kattintson az OLAP-eszközök gombra az Eszközök környezeti lapon, amely a Kimutatáseszközök környezeti lapcsoport része. Válassza ki az Offline OLAP parancsot (9.8. ábra).

Rizs. 9.8. Hozzon létre egy offline adatkockát

A képernyőn megjelenik az Offline Cube beállítások párbeszédpanel. OLAP adatok. Kattintson az Offline adatfájl létrehozása gombra. Elindította az Adatkockafájl létrehozása varázslót. Az eljárás folytatásához kattintson a Tovább gombra.

Először meg kell adnia azokat a méreteket és szinteket, amelyek szerepelni fognak az adatkockában. A párbeszédpanelen ki kell választania az OLAP adatbázisból importált adatokat. Az ötlet az, hogy csak azokat a méreteket adja meg, amelyekre szükség lesz, miután a számítógépet leválasztják a helyi hálózatról. Minél több dimenziót ad meg, annál nagyobb lesz az offline adatkocka.

Kattintson a Tovább gombra, hogy továbblépjen a varázsló következő párbeszédpaneljéhez. Lehetővé teszi olyan tagok vagy adatelemek megadását, amelyek nem fognak szerepelni a kockában. Különösen nem lesz szüksége az Internetes értékesítés kiterjesztett összegére, így az nem lesz bejelölve a listában. A jelölőnégyzet törlése azt jelzi, hogy a megadott elem nem lesz importálva, és extra helyet foglal el a helyi merevlemezen.

Az utolsó lépésben adja meg az adatkocka helyét és nevét. Esetünkben a kockafájl neve MyOfflineCube.cub lesz, és a Work mappában található.

Az adatkocka-fájlok kiterjesztése .kölyök

Egy idő után az Excel elmenti az offline adatkockát a megadott mappába. A teszteléshez kattintson duplán a fájlra, amely automatikusan létrehoz egy Excel-munkafüzetet, amely a kiválasztott adatkockához társított kimutatást tartalmaz. Létrehozása után az offline adatkockát szétoszthatja minden érdeklődő felhasználó számára, aki offline LAN módban dolgozik.

Miután csatlakozott a helyi hálózathoz, megnyithatja és frissítheti az offline adatkockafájlt, valamint a megfelelő adattáblázatot. Fő elv azt mondja, hogy az offline adatkocka csak akkor működik, ha a helyi hálózat megszakad, de a kapcsolat helyreállítása után szükségszerűen frissül. Az offline adatkocka frissítésére tett kísérlet a kapcsolat megszakadása után sikertelen lesz.

Munka offline kockafájlokkal

Az offline kockafájl (.cub) OLAP (Online Analytical Processing) kocka formájában tárolja az adatokat. Ezek az adatok az OLAP-kiszolgálón lévő OLAP-adatbázis részét képezhetik, vagy az OLAP-adatbázistól függetlenül is előállíthatók. Használjon offline kockafájlt a kimutatás- és kimutatás-jelentésekkel való munka folytatásához, amikor a kiszolgáló nem elérhető, vagy offline állapotban van.

Biztonsági megjegyzés: Legyen óvatos, amikor olyan offline kockafájlt használ vagy terjeszt, amely érzékeny vagy személyes adatokat tartalmaz. Kockafájl helyett ajánlatos az adatokat munkafüzetben tárolni, hogy az adatokhoz való hozzáférést a jogkezeléssel szabályozhassuk. további információ megtalálható az Információs jogok kezelése az Office-ban.

Ha OLAP-kiszolgáló forrásadatokon alapuló kimutatással vagy kimutatásdiagram jelentéssel dolgozik, az Offline kocka varázsló segítségével a forrásadatokat egy külön offline kockafájlba másolhatja a számítógépen. Ezen offline fájlok létrehozásához olyan OLAP-adatszolgáltatóra van szükség, amely támogatja ezt a képességet, például a Microsoft SQL Server Analysis Services MSOLAP-ját, amely telepítve van a számítógépre.

Jegyzet: A Microsoft SQL Server Analysis Services szolgáltatásból származó offline kockafájlok létrehozása és használata feltételekhez és licencekhez kötött. Microsoft telepítések SQL szerver. Tekintse át a vonatkozó SQL Server-kiadás licencinformációit.

Munka az Offline Cube varázslóval

Offline kockafájl létrehozásához kiválaszthatja az adatok egy részhalmazát az OLAP-adatbázisban az offline kockavarázsló segítségével, majd mentheti azt. A jelentésnek nem kell tartalmaznia a fájlban található összes mezőt, és nem kell ezek közül egyet sem kijelölni, illetve az OLAP adatbázisban elérhető adatmezőket. A fájl minimális számának csökkentése érdekében csak azokat az adatokat szerepeltetheti, amelyeket meg szeretne jeleníteni a jelentésben. Az összes dimenziót elhagyhatja, és a legtöbb dimenziótípus esetében kizárhatja azokat az alacsonyabb szintű információkat és a felső szintű elemeket is, amelyeket nem szeretne megjeleníteni. Az összes felvett elem esetében az adatbázisban az adott elemekhez rendelkezésre álló tulajdonságmezőket a rendszer az offline fájlban is tárolja.

Adatok átvitele ide offline módés fordított kapcsolatuk

Ehhez először létre kell hoznia egy kimutatást vagy kimutatást a kiszolgáló adatbázisa alapján, majd a jelentésből önálló kockafájlt kell létrehoznia. Ezt követően bármikor átkapcsolhatja a jelentést a szerver adatbázis és az offline fájl között. Például, ha használ laptop otthoni és videós utazáshoz, majd csatlakoztassa újra a számítógépet a hálózathoz.

Az alábbiakban ismertetjük a követendő fő lépéseket elem élettartam az adatokkal, majd vigye vissza az adatokat az internetre.

Hozzon létre vagy nyisson meg egy kimutatást vagy kimutatást az offline módban elérni kívánt OLAP-adatok alapján.

Hozzon létre egy offline kockafájlt a számítógépén. fejezetben Hozzon létre egy offline kockafájlt egy OLAP-kiszolgáló adatbázisából(a cikkben lent).

Leválasztás a hálózatról és offline kockafájl használata.

Lépjen online, és csatolja újra a kockafájlt offline módban. Nézze meg a részt Az offline kockafájl újracsatlakoztatása az OLAP-kiszolgáló adatbázisához(a cikkben lent).

Frissítse az offline kockafájlt új adatokkal, és hozza létre újra az offline kockafájlt. Nézze meg a részt frissítse és hozza létre újra az offline kockafájlt(a cikkben lent).

BLOG

Csak minőségi hozzászólások

Mik azok az Excel PivotTable-ok és az OLAP-kockák?

Nézze meg a cikkhez készült videót:

OLAP- ez angol. online analitikai feldolgozás, valós idejű analitikai adatfeldolgozási technológia. közérthető nyelven- többdimenziós adatokkal rendelkező tároló (Cube), még egyszerűbb - csak egy adatbázis, amelyből Excelben lehet adatokat gyűjteni és az Excel eszközzel elemezni - PivotTables.

Pivot táblák egy felhasználói felület többdimenziós adatok megjelenítésére. Más szóval - egy speciális fajta táblázat, amellyel szinte bármilyen jelentést készíthet.

Az egyértelműség érdekében hasonlítsuk össze a "Szabványos táblázatot" a "Pivot Table"-val.

Normál asztal:

Pivot tábla:

Fő különbség Pivot táblák az ablak jelenléte Pivot tábla mezőlista amelyek közül választhat Szükséges Mezőkés automatikusan kap minden asztalt!

Hogyan kell használni

Nyisson meg egy OLAP-kockához csatlakoztatott Excel-fájlt, például "BIWEB"-et:

Most ez mit jelent, és hogyan kell használni?

Húzza át a szükséges mezőket, hogy például a következő táblázatot kapja meg:

« Pluszok» lehetővé teszi, hogy részletezze a jelentést. Ebben a példában a „Márka” a „Rövidített nevek”-ig, a „Negyed” pedig a „Hónap”-ig lesz lefúrva, azaz. Így:

Analitikai függvények az Excelben (kockafüggvények)

A Microsoft folyamatosan új funkciókkal bővíti az Excelt az adatok elemzése és megjelenítése terén. Az Excelben az információkkal való munka három viszonylag független rétegként ábrázolható:

• "helyesen" rendszerezett forrásadatok
• az adatfeldolgozás matematikája (logikája).
• adatok bemutatása

Rizs. 1. Adatelemzés Excelben: a) nyers adatok, b) mérés Power Pivotban, c) műszerfal; A kép nagyításához kattintson rá jobb gombbal, és válassza ki Kép megnyitása új lapon

Jegyzet letöltése Word vagy pdf formátumban, példák Excel formátumban

Kockafüggvények és pivot táblák

Az adatok bemutatásának legegyszerűbb és egyben nagyon hatékony eszközei a pivot táblák. Felépíthetők az alábbi adatokból: a) egy Excel-munkalap, b) egy OLAP-kocka vagy c) egy Power Pivot adatmodell. Az utolsó két esetben a pivot tábla mellett analitikus függvényekkel (kockafüggvényekkel) készíthetünk jelentést Excel-lapon. A pivot táblák egyszerűbbek. A kockafüggvények összetettebbek, de nagyobb rugalmasságot biztosítanak, különösen a jelentéskészítésben, ezért széles körben használják az irányítópultokban.

A következő tárgyalás a Power Pivot modellen, illetve néhány esetben az OLAP-kockákon alapuló kockaképletekre és kimutatástáblákra vonatkozik.

Egy egyszerű módja annak, hogy megkapja a kockák funkcióit

Amikor (ha) elkezdte tanulni a VBA-kódot, megtanulta, hogy a kód beszerzésének legegyszerűbb módja egy makró rögzítése. Továbbá a kód szerkeszthető, ciklusok, ellenőrzések, stb. adhatók hozzá. Hasonlóképpen, a kockafüggvények halmazának beszerzésének legegyszerűbb módja a pivot tábla konvertálása (2. ábra). Álljon a pivot tábla bármelyik cellájára, és lépjen a lapra Elemzés, kattintson a gombra Alapok OLAP, és nyomja meg Konvertálás képletekre.

Rizs. 2. PivotTable konvertálása kockafüggvénykészletté

A számok mentésre kerülnek, és ezek nem értékek, hanem képletek, amelyek adatokat nyernek ki a Power Pivot adatmodellből (3. ábra). A kapott táblázatot formázhatja. Különösen törölhet és szúrhat be sorokat és oszlopokat a táblázatba. A szelet megmarad, és ez befolyásolja a táblázat adatait. Az eredeti adatok frissítésekor a táblázatban szereplő számok is frissülnek.

Rizs. 3. Kockaképletek alapján készült táblázat

CUBEVALUE() függvény

Talán ez a kockák fő funkciója. Egyenértékű a területtel Értékek Pivot tábla. A CUBEVALUE lekéri az adatokat egy Power Pivot kockából vagy modellből, és megjeleníti azokat a kimutatástáblán kívül. Ez azt jelenti, hogy Önt nem korlátozzák a pivot tábla határai, és számtalan lehetőséggel készíthet jelentéseket.

Képlet írása a semmiből

Nem kell konvertálnia a kész kimutatást. Bármilyen kockaképletet a semmiből írhat. Például a következő képletet írjuk be a C10 cellába (4. ábra):

Rizs. 4. A C10 cellában lévő CUBEVALUE() függvény minden év kerékpárértékesítését adja vissza, mint a kimutatásban

Kis trükk. A kockaképletek könnyebb olvashatósága érdekében kívánatos, hogy minden sorba csak egy argumentum kerüljön. Az Excel ablakot kicsinyítheti. Ehhez kattintson az ikonra Gurulj be az ablakba a képernyő jobb felső sarkában található. Ezután állítsa be az ablak méretét vízszintesen. Alternatív lehetőség– kényszeríti a képlet szövegét a tördelésre új sor. Ehhez a képletsorban vigye a kurzort arra a helyre, ahová az átvitelt szeretné végrehajtani, és nyomja meg az Alt + Enter billentyűt.

Rizs. 5. Az ablak kicsinyítése

A CUBEVALUE() függvény szintaxisa

Az Excel súgója teljesen pontos és teljesen használhatatlan kezdőknek:

CUBEVALUE(kapcsolat, [elem_kifejezés1], [elem_kifejezés2], ...)

Kapcsolat kötelező érv; egy szöveges karakterlánc, amely a kockával való kapcsolat nevét reprezentálja.

elem_kifejezés– opcionális érv; egy MDX-et képviselő szöveges karakterlánc, amely egy elemet vagy sort ad vissza a kockában. Ezenkívül az "elem_kifejezés" a CUBESET függvény segítségével definiált halmaz is lehet. Használja a "member_expression"-t szeletként, hogy meghatározza a kocka azon részét, amelyre összesített értéket szeretne visszaadni. Ha nincs mérték megadva az elem_kifejezésben, akkor a rendszer az adott kocka alapértelmezett mértékét használja.

Mielőtt folytatná a CUBEVALUE függvény szintaxisának magyarázatát, néhány szót a kockákról, az adatmodellekről és a rejtjelekről tuple.

Néhány háttér az OLAP-kockákról és a Power Pivot adatmodellekről

OLAP adatkockák ( O n l ine A elemző P rocessing - operatív adatelemzés) kifejezetten analitikai feldolgozásra és gyors adatkinyerésre lettek kifejlesztve belőlük. Képzeljünk el egy háromdimenziós teret, ahol a tengelyek időszakok, városok és áruk (5a. ábra). Egy ilyen koordináta rács csomópontjai különféle mérőszámok értékeit tartalmazzák: értékesítési volumen, nyereség, költségek, eladott egységek száma stb. Most képzelje el, hogy több tucat vagy akár több száz mérés van... és van még egy sok intézkedés. Ez lesz a többdimenziós OLAP-kocka. Az OLAP-kockák létrehozása, konfigurálása és naprakészen tartása az informatikai szakemberek dolga.

Rizs. 5a. 3D OLAP kocka

Az Excel elemző képletek (kockaképletek) kivonják a tengelycímeket (például Idő), az elemek nevei ezeken a tengelyeken (augusztus, szeptember), a mértékek értéke a koordináták metszéspontjában. Ez a struktúra teszi lehetővé, hogy a kocka alapú pivot táblák és kockaképletek olyan rugalmasak legyenek, és alkalmazkodjanak a felhasználók igényeihez. Pivot táblák alapúak Excel lapok ne használjon mértékeket, így azok nem olyan rugalmasak az adatelemzési célokra.

Power Pivot - Viszonylag új funkció Microsoft. Ez egy beépített Excel és némileg független környezet ismerős felülettel. A Power Pivot sokkal jobb, mint a szabványos kimutatások. Ugyanakkor a kockák fejlesztése a Power Pivotban viszonylag egyszerű, és ami a legfontosabb, nem igényel informatikus részvételt. A Microsoft megvalósítja szlogenjét: „Üzleti elemzés – a tömegekhez!”. Bár a Power Pivot modellek nem 100%-os kockák, kockáknak is nevezhetjük őket (további részletekért lásd Mark Moore Power Pivot bevezető tanfolyamát és Rob Colley hosszabb kiadását, a Power Pivot DAX Formulas-t).

A kocka fő összetevői a dimenziók, a hierarchiák, a szintek, az elemek (vagy tagok; angolul Members) és a mértékek (mértékek). Mérés - az elemzett adatok fő jellemzője. Például termékkategória, időszak, értékesítési földrajzi terület. A dimenzió olyan dolog, amelyet a pivot tábla egyik tengelyére helyezhetünk. Minden dimenziótól eltekintve egyedi értékek tartalmaz egy elemet, amely összesíti ennek a dimenziónak az összes tagját.

A mérések alapja hierarchia. Például egy termékkategória felosztható alkategóriákra, majd modellekre, végül terméknevekre (5b. ábra) A hierarchia lehetővé teszi összefoglaló adatok létrehozását és elemzését a szerkezet különböző szintjein. Példánkban a hierarchia Kategória tartalmazza a 4 szint.

Elemek(egyéni tagok) minden szinten jelen vannak. Például a Kategória szint négy elemből áll: Kiegészítők, Kerékpárok, Ruházat, Alkatrészek. A többi szintnek megvannak a maga elemei.

intézkedéseket kiszámított értékek, például értékesítési mennyiség. A kockákban lévő mértékek saját dimenziójukban, úgynevezett (lásd a 9. ábrát lent). Az intézkedéseknek nincs hierarchiája. Minden mérték kiszámít és tárol egy értéket minden dimenzióhoz és minden taghoz, és az alapján szeletel, hogy mely méretelemeket helyezzük el a tengelyen. Azt is megmondják, hogy milyen koordinátákat állítsunk be, vagy melyik szűrőkörnyezetet állítjuk be. Például a 2. ábrán. 5a minden kis kockában ugyanazt a mértéket számítják ki - Profit. A mérték által visszaadott érték pedig a koordinátáktól függ. Az 5a. ábra jobb oldalán az látható, hogy a Profit (három koordinátában) Moszkvában októberben almán = 63 000 rubel. A mérték értelmezhető, és a mérések egyikeként. Például a 2. ábrán. 5a tengely helyett Áruk, helytengely Intézkedések elemekkel Az értékesítés volumene, Nyereség, Egység eladva. Ezután minden cella lesz valamilyen érték, például Moszkva, szeptember, értékesítési volumen.

Tuple- több különböző méretű elem, megadva a koordinátákat a kocka tengelyei mentén, amelyekben a mértéket számítjuk. Például a 2. ábrán. 5a Tuple= Moszkva, október, alma. Szintén érvényes sor a Perm, alma. Egy másik az alma, augusztus. A leíróban nem szereplő dimenziók implicit módon jelen vannak a leíróban, és az alapértelmezett tag képviseli őket. Így egy többdimenziós tér cellája mindig definiálva van teljes készlet koordinátákat, még akkor is, ha néhányat kihagyunk a sorból. Nem foglalhat két azonos dimenziójú elemet egy sorba, a szintaxis ezt nem teszi lehetővé. Például az érvénytelen sor Moszkva és Perm, alma. Egy ilyen többdimenziós kifejezés megvalósításához két sorra van szüksége: Moszkva és alma + Perm és alma.

Elemek halmaza– több azonos dimenziójú elem. Például alma és körte. Sorok halmaza- több sor, amelyek mindegyike ugyanabban a sorrendben azonos méretekből áll. Például egy két sorból álló készlet: Moszkva, alma és Perm, banán.

Automatikus kiegészítés a mentéshez

Térjünk vissza a CUBEVALUE függvény szintaxisához. Használjuk az automatikus kiegészítést. Kezdje el beírni a képletet egy cellába:

Az Excel felajánlja az Excel-munkafüzetben elérhető összes kapcsolatot:

Rizs. 6. A Power Pivot adatmodellhez való csatlakozást mindig ThisWorkbookDataModelnek hívják

Rizs. 7. Csatlakozások kockákhoz

Folytassuk a képlet beírását (esetünkben az adatmodellhez):

Az automatikus kiegészítés az összes elérhető táblát és adatmodell mértékét javasolja:

Rizs. 8. Az első szint elérhető elemei - táblázatok nevei és mértékegységei (kiemelve)

Válasszon egy ikont Intézkedések. Vmerre mutat:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Az automatikus kiegészítés minden elérhető intézkedést javasol:

Rizs. 9. A második szint elérhető elemei az intézkedéscsomagban

Válasszon mértéket. Idézőjelek hozzáadása, záró zárójel, Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Rizs. 10. CUBEVALUE képlet Excel cellában

Hasonlóképpen hozzáadhat egy harmadik argumentumot a képlethez:

VBA az Excelben Excel.PivotTable objektum és a Kimutatások és OLAP-kockák használata az Excelben

10.8 Munkavégzés kimutatástáblákkal (PivotTable objektum)

Excel.PivotTable objektum, programozottan dolgozzon a kimutatástáblákkal és az OLAP kockákkal az Excelben VBA, PivotCache objektum segítségével, hozzon létre egy kimutatástábla elrendezést

A legtöbb vállalkozás működése során a tevékenységekre vonatkozó ún. nyers adatok halmozódnak fel. Például egy kereskedelmi vállalkozás esetében az áruk értékesítésére vonatkozó adatok gyűjthetők - minden vásárláshoz külön, vállalkozások számára sejtes kommunikáció- statisztika betöltése bázisállomások stb. Nagyon gyakran egy vállalkozás vezetésének analitikus információra van szüksége, amelyet nyers információk alapján állítanak elő - például annak kiszámításához, hogy az egyes termékek milyen hozzájárulást jelentenek a vállalkozás bevételéhez vagy a szolgáltatás minőségéhez egy adott területen. állomás. Nagyon nehéz ilyen információkat nyers információból kinyerni: nagyon összetett SQL-lekérdezéseket kell végrehajtania, amelyek hosszú ideig tartanak, és gyakran zavarják a folyamatban lévő munkát. Ezért egyre több jelenleg nyers adat kerül fel először az Adattárházba, majd az OLAP kockákba, amelyek nagyon kényelmesek az interaktív elemzéshez. Az OLAP kockákat legegyszerűbben többdimenziós tábláknak tekinthetjük, amelyekben a szokásos két dimenzió (oszlopok és sorok, mint a normál táblázatokban) helyett sok méret is előfordulhat. A "metszeti" kifejezést általában a kocka méreteinek leírására használják. Például egy marketing osztálynak szüksége lehet időre, régióra, terméktípusra, értékesítési csatornára stb. A kockák segítségével (szemben a szabványos SQL lekérdezésekkel) nagyon könnyű választ kapni olyan kérdésekre, mint „hány ilyen típusú terméket értékesítettek a tavalyi év negyedik negyedévében az északnyugati régióban regionális forgalmazókon keresztül.

Természetesen ilyen kockák nem hozhatók létre normál adatbázisokban. Az OLAP kockákhoz speciális szaktudás szükséges szoftver termékek. Az SQL Server a Microsoft Analysis Services nevű OLAP-adatbázisával érkezik. Vannak OLAP megoldások az Oracle, IBM, Sybase stb.

Az ilyen kockákkal való munkavégzéshez egy speciális kliens van beépítve az Excelbe. Oroszul úgy hívják Pivot tábla(a grafikus képernyőn a menün keresztül érhető el Adat -> Pivot tábla), és angolul - Pivot tábla. Ennek megfelelően az ügyfél által képviselt objektumot PivotTable-nek nevezik. Megjegyzendő, hogy nem csak OLAP kockákkal, hanem Excel táblákban vagy adatbázisokban lévő szokásos adatokkal is tud működni, de sok funkció elvész.

A PivotTable és a PivotTable objektum a Panorama Software szoftvertermékei, amelyeket a Microsoft vásárolt meg és integrált az Excelbe. Ezért a kimutatás-objektummal való munka némileg eltér a többi Excel-objektum használatától. Gyakran nehéz kitalálni, mit kell tenni. Ezért ajánlatos aktívan használni a makrórögzítőt tippek fogadására. Ugyanakkor a pivot táblákkal való munka során a felhasználóknak gyakran ugyanazokat az ismétlődő műveleteket kell végrehajtaniuk, ezért sok esetben szükség van az automatizálásra.

Hogyan néz ki programozottan dolgozni egy kimutatási táblával?

Az első dolog, amit tennünk kell, hogy létrehozunk egy PivotCache objektumot, amely az OLAP-forrásból lekért rekordok készletét képviseli. Nagyon feltételesen ez a PivotCache objektum összehasonlítható a QueryTable-lel. Csak egy PivotCache objektum használható kimutatás-objektumként. A PivotCache objektum a PivotCaches gyűjtemény Add() metódusával jön létre:

Dim PC1 PivotCache-ként

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

A PivotCaches egy szabványos gyűjtemény, és a részletes átgondolást érdemlő metódusok közül csak az Add() metódus nevezhető meg benne. Ez a módszer két paramétert igényel:

• Forrás típus- kötelező, meghatározza a pivot tábla adatforrásának típusát. Dönthet úgy, hogy egy Excel-tartomány, adatbázisból származó adatok, külső adatforrás, egy másik kimutatás stb. alapján hoz létre kimutatást. A gyakorlatban általában csak akkor van értelme az OLAP-ot használni, ha sok adat van – ennek megfelelően speciális külső tárhely(például Microsoft Analysis Services). Ebben a helyzetben az xlExternal van kiválasztva.
• SourceData- minden esetben kötelező, kivéve ha az első paraméter értéke xlExternal. Szigorúan véve meghatározza azt az adattartományt, amely alapján a kimutatás létrejön. Általában egy Range objektumot vesz fel.

A következő feladat a PivotCache objektum paramétereinek konfigurálása. Mint már említettük, ez az objektum nagyon hasonlít a QueryTable-re, és tulajdonságai és metódusai nagyon hasonlóak. Néhány a legtöbb fontos tulajdonságaités módszerek:

• ADOConnection- egy ADO Connection objektum visszaküldésének képessége, amely automatikusan létrejön a csatlakozáshoz külső forrás adat. A kapcsolat tulajdonságainak további konfigurálására szolgál.
• kapcsolat- pontosan ugyanúgy működik, mint az azonos nevű QueryTable objektumtulajdonság. El tud fogadni egy kapcsolati karakterláncot, egy kész Recordset objektumot, szöveges fájl, Webes kérés. Microsoft fájl lekérdezés. Az OLAP-pal végzett munka során leggyakrabban a kapcsolati karakterláncot közvetlenül írják (mivel nincs értelme egy Recordset objektum fogadásának, például az adatok megváltoztatásához - az OLAP adatforrások szinte mindig csak olvashatók). Például ennek a tulajdonságnak a beállítása a Foodmart adatbázishoz (Analysis Services mintaadatbázishoz) való csatlakozáshoz a LONDON szerveren a következőképpen nézhet ki:

PC1.Connection = "OLEDB; Szolgáltató=MSOLAP.2; Adatforrás=LONDON1; Kezdeti katalógus = FoodMart 2000"

• tulajdonságait CommandTypeés parancsszöveg ugyanúgy írja le az adatbázis-kiszolgálónak küldött parancs típusát és magának a parancsnak a szövegét. Például az Értékesítési kocka eléréséhez és annak teljes gyorsítótárba helyezéséhez az ügyfélen használhatja a hasonló kódokat

• ingatlan Helyi kapcsolat lehetővé teszi az Excel által létrehozott helyi kockához (*.cub fájlhoz) való csatlakozást. Természetesen erősen ellenjavallt az ilyen fájlok használata "gyártási" adatkötetekkel való munkavégzéshez - csak elrendezések készítésére stb.
• ingatlan Használt memória visszaadja a számot véletlen hozzáférésű memória, amelyet a PivotCache használ. Ha az ezen a kimutatás-gyorsítótáron alapuló kimutatás még nincs létrehozva és megnyitva, akkor 0-t ad vissza. Használható annak ellenőrzésére, hogy az alkalmazás működik-e gyenge klienseken.
• ingatlan OLAP Igaz értéket ad vissza, ha a PivotCache csatlakozik az OLAP-kiszolgálóhoz.
• OptimizeCache- a gyorsítótár szerkezetének optimalizálásának képessége. Az adatok kezdeti betöltése tovább tart, de ezután a munka sebessége megnőhet. OLE DB források esetén nem működik.

A PivotCache objektum többi tulajdonságai megegyeznek a QueryTable objektum tulajdonságaival, ezért itt nem tárgyaljuk őket.

A PivotCache objektum fő metódusa a CreatePivotTable() metódus. Ennek a módszernek a segítségével a következő lépést hajtjuk végre - egy pivot tábla (PivotTable objektum) létrehozását. Ez a módszer négy paramétert vesz igénybe:

• TableDestination az egyetlen kötelező paraméter. Elfogad egy Range objektumot, amelynek bal felső sarkába kerül a pivot táblázat.
• táblázat neve- Pivot tábla neve. Ha nincs megadva, akkor a „PivotTable1” űrlap neve automatikusan létrejön.
• adatokat olvasni- ha True értékre van állítva, akkor a kocka összes tartalma automatikusan gyorsítótárba kerül. Nagyon óvatosnak kell lennie ezzel a paraméterrel, mert helytelen használata drámaian megnövelheti a kliens terhelését.
• Alapértelmezett verzió- Ez a tulajdonság általában nincs megadva. Lehetővé teszi a létrehozandó kimutatás verziójának megadását. Alapértelmezés szerint a legfrissebb verzió használatos.

A kimutatástábla létrehozása a munkafüzet első lapjának első cellájában így nézhet ki:

PC1.CreatePivotTable Range("A1")

A pivot tábla létrejött, de közvetlenül a létrehozás után üres. Négy területet biztosít a forrásból származó mezők elhelyezésére (a grafikus képernyőn mindezt akár az ablak segítségével is beállíthatjuk Pivot tábla mezőlista- automatikusan vagy gombbal nyílik Elrendezés a PivotTable varázsló utolsó képernyőjén):

• oszlopterület- tartalmazza azokat a dimenziókat („szakasz”, amelyben az adatok elemzésére kerül sor), amelynek tagjai kisebbek;
• vonalterület- azok a dimenziók, amelyek tagjai több;
• oldal területe- azokat a méréseket, amelyekkel csak szűrni kell (például csak ilyen és olyan régióra, vagy csak ilyen és ilyen évre kell adatokat megjeleníteni);
• adatterület- valójában a táblázat központi része. Azok a számszerű adatok (például az eladások mennyisége), amelyeket elemezünk.

Nehéz arra hagyatkozni, hogy a felhasználó helyesen helyezze el az elemeket mind a négy területen. Ezenkívül eltarthat egy ideig. Ezért gyakran szükséges az adatokat programozottan elrendezni egy kimutatásban. Ezt a műveletet a CubeField objektum segítségével hajtják végre. Ennek az objektumnak a fő tulajdonsága az Orientation, ez határozza meg, hogy ez vagy az a mező hol lesz. Tegyük például az Ügyfelek dimenziót az oszlopterületre:

PT1.CubeFields(""). Orientation = xlColumnField

Ezután - az Idő dimenzió a karakterláncok területén:

PT1.CubeFields(""). Tájolás = xlRowField

Ezután - a Termék dimenziót az oldal területére:

PT1.CubeFields(""). Tájolás = xlPageField

És végül a mutató (számszerű adatok az elemzéshez) Egységértékesítés:

PT1.CubeFields("."). Orientation = xlDataField