Hiba történt egy kockafájl Excelből létrehozásakor. OLAP-kocka létrehozása a Microsoft Query segítségével

Rizs. 2. OLAP kocka szelet

Forgás(3. ábra) - a jelentésben vagy a megjelenített oldalon bemutatott mérések helyének megváltoztatásának művelete. Például egy elforgatási művelet magában foglalhatja egy táblázat sorainak és oszlopainak átrendezését. Ezenkívül az adatkocka elforgatásával a táblázatból kimaradt méretek a helyükre kerülnek a megjelenített oldalon lévő méretekkel, és fordítva.

kockákra OLAP adatok(Online Analytical Processing – operatív adatelemzés) lehetővé teszi a többdimenziós adatok hatékony kinyerését és elemzését. Más típusú adatbázisoktól eltérően az OLAP adatbázisokat kifejezetten analitikai feldolgozásra és mindenféle adatkészlet gyors kinyerésére tervezték. Valójában számos lényeges különbség van a szabványos relációs adatbázisok között, mint például az Access vagy SQL szerverés OLAP adatbázisok.

Rizs. 1. OLAP-kocka Excel-munkafüzethez való csatlakoztatásához használja a parancsot Az Analytics szolgáltatásokból

Töltse le a jegyzetet a vagy a

A relációs adatbázisokban az információk rekordokként jelennek meg, amelyek egymás után kerülnek hozzáadásra, törlésre és frissítésre. Az OLAP adatbázisok csak egy pillanatképet tárolnak az adatokból. Az OLAP-adatbázisban az információk egyetlen adatblokkként archiválódnak, és csak igény szerinti kimenetre szolgálnak. Bár hozzá lehet adni egy OLAP adatbázishoz új információ, a meglévő adatokat ritkán szerkesztik, még kevésbé törlik.

A relációs adatbázisok és az OLAP adatbázisok szerkezetileg különböznek egymástól. A relációs adatbázisok jellemzően egymáshoz kapcsolódó táblák halmazából állnak. Egyes esetekben egy relációs adatbázis annyi táblát tartalmaz, hogy nagyon nehéz meghatározni, hogyan kapcsolódnak egymáshoz. Az OLAP-adatbázisokban az egyes adatblokkok közötti kapcsolatokat előre meghatározzák, és az OLAP-kockáknak nevezett struktúrában tárolják. Az adatkockák teljes információt tárolnak az adatbázis hierarchikus felépítéséről és kapcsolatairól, ami nagyban leegyszerűsíti a navigációt. Emellett sokkal egyszerűbb riportokat készíteni, ha előre tudja, hogy a kinyert adatok hol találhatók, és milyen egyéb adatok vannak hozzárendelve.

A fő különbség a relációs adatbázisok és az OLAP adatbázisok között az információ tárolásának módja. Az OLAP-kockákban lévő adatok ritkán jelennek meg általánosan. Az OLAP adatkockák általában előre megtervezett formátumban tartalmazzák az információkat. Így az adatok kockákban történő csoportosítása, szűrése, rendezése és egyesítése műveletei az információkkal való feltöltést megelőzően kerülnek végrehajtásra. Ez a lehető legegyszerűbbé teszi a kért adatok lekérését és megjelenítését. A relációs adatbázisokkal ellentétben nincs szükség az információk megfelelő rendszerezésére a képernyőn való megjelenítés előtt.

Az OLAP adatbázisokat általában informatikai rendszergazdák hozzák létre és tartják karban. Ha szervezete nem rendelkezik az OLAP-adatbázisok kezeléséért felelős struktúrával, akkor forduljon a relációs adatbázis-adminisztrátorhoz, és kérjen legalább néhány OLAP-megoldást a vállalati hálózaton.

Csatlakozás OLAP adatkockához

Az OLAP-adatbázis eléréséhez először kapcsolatot kell létesítenie az OLAP-kockával. Először lépjen a Szalag fülre Adat. Kattintson a gombra Más forrásokbólés válassza ki a parancsot a legördülő menüből Az Analytics szolgáltatásokból(1. ábra).

Amikor kiválasztja az Adatkapcsolat varázsló megadott parancsát (2. ábra). Fő feladata, hogy segítsen létrehozni a kapcsolatot a használni kívánt szerverrel Excel program az adatok kezelése során.

1. Először meg kell adnia az Excel regisztrációs adatait. Írja be a kiszolgáló nevét a párbeszédpanel mezőibe, regisztrációs névés adathozzáférési jelszót, amint az ábra mutatja. 2. Kattintson a gombra További. Ha a segítségével csatlakozik fiókot Windows, majd állítsa be a kapcsolót Használja a Windows hitelesítést.

2. A legördülő listából válassza ki azt az adatbázist, amellyel dolgozni fog (3. ábra). A jelenlegi példa az Analysis Services Tutorial adatbázist használja. Miután kiválasztotta ezt az adatbázist, az alábbi lista kéri, hogy importálja a benne elérhető összes OLAP-kockát. Válassza ki a kívánt adatkockát és kattintson a gombra További.

Rizs. 3. Válasszon ki egy működő adatbázist és OLAP kocka, amelyet adatelemzésre kíván használni

3. A következő varázsló párbeszédpanelen, az ábrán látható. 4, meg kell adnia leíró információkat a létrehozandó kapcsolatról. ábrán látható párbeszédpanel összes mezője. 4 nem kötelező kitölteni. Az aktuális párbeszédablakot mindig figyelmen kívül hagyhatja kitöltése nélkül, és ez semmilyen módon nem befolyásolja a kapcsolatot.

Rizs. 4. Módosítsa a kapcsolatleíró információkat

4. Kattintson a gombra Kész a kapcsolat létrehozásának befejezéséhez. Egy párbeszédpanel jelenik meg a képernyőn Adatok importálása(5. ábra). Állítsa be a kapcsolót Jelentés Pivot tábla és kattintson az OK gombra a pivot tábla létrehozásának megkezdéséhez.

OLAP kocka szerkezet

Amikor pivot táblát hoz létre egy OLAP adatbázisból, észre fogja venni, hogy a munkaablak ablaka Pivot tábla mezői más lesz, mint egy normál pivot tábla esetében. Ennek oka a PivotTable elrendezésében rejlik, hogy pontosan tükrözze a hozzá csatolt OLAP-kocka szerkezetét. Ahhoz, hogy a lehető leggyorsabban navigálhasson egy OLAP-kockán, alaposan meg kell ismerkednie az összetevőivel és azok interakciójával. ábrán. A 6. ábra egy tipikus OLAP-kocka alapvető felépítését mutatja.

Mint látható, az OLAP-kocka fő összetevői a dimenziók, a hierarchiák, a szintek, a tagok és a mértékek:

• Méretek. Az elemzett adatelemek főbb jellemzői. Gyakori példák a dimenziókra: Termékek, Ügyfél és Alkalmazott. ábrán. A 6. ábra a Termékek dimenzió felépítését mutatja.
• Hierarchiák. Szintek előre meghatározott összesítése egy adott dimenzióban. A hierarchia lehetővé teszi összefoglaló adatok létrehozását és elemzését a struktúra különböző szintjein anélkül, hogy belemélyedne az e szintek közötti kapcsolatokba. ábrán látható példában. A 6. ábra szerint a Termékek dimenziónak három szintje van, amelyek a termékkategóriák egyetlen hierarchiájába vannak összesítve.
• Szintek. A szintek olyan kategóriák, amelyek egy közös hierarchiába vannak összesítve. Tekintse a rétegeket adatmezőknek, amelyek egymástól függetlenül lekérdezhetők és elemezhetők. ábrán. 6 csak három szint van: Kategória, Alkategória és Terméknév.
• tagok. Egy elem adatok a dimenzión belül. A tagok általában dimenziókból, hierarchiákból és szintekből álló OLAP-struktúrán keresztül érhetők el. ábra példájában. A Terméknév szinthez 6 tag van meghatározva. A többi szintnek saját tagjai vannak, amelyek nem jelennek meg a szerkezetben.
• Intézkedések- ez valós adat OLAP kockákban. A mérések saját méretükben vannak tárolva, amelyeket mértékdimenzióknak nevezünk. A mértékek lekérdezhetők a dimenziók, hierarchiák, szintek és tagok bármilyen kombinációjával. Ezt az eljárást „szeletelési” intézkedéseknek nevezik.

Most, hogy ismeri az OLAP-kockák szerkezetét, nézzük meg újra a Kimutatás mezőlistáját. A rendelkezésre álló mezők elrendezése egyértelművé válik, és nem okoz panaszt. ábrán. A 7. ábra azt mutatja, hogy a mezőlista hogyan ábrázolja az OLAP pivot tábla elemeit.

Az OLAP pivot tábla mezőinek listájában a mértékek jelennek meg először, és egy összeg ikon (szigma) jelzi őket. Csak ezek az adatelemek lehetnek a VALUE régióban. Utánuk a listában a méretek láthatók, táblázatos képpel ellátott ikonnal. Példánk az Ügyfél dimenziót használja. Ez a dimenzió számos hierarchiát tartalmaz. A hierarchia kiterjesztése után megtekintheti az egyes adatszinteket. Egy OLAP-kocka adatszerkezetének megtekintéséhez egyszerűen navigáljon a pivot tábla mezőinek listájában.

Az OLAP Pivot Tables korlátai

Amikor az OLAP-kimutatástáblákkal dolgozik, ne feledje, hogy az Analysis Services OLAP-környezetében használja a kimutatás-adatforrást. Ez azt jelenti, hogy az adatkocka minden viselkedési aspektusát, a dimenzióktól kezdve a kockában szereplő mérőszámokig, szintén az OLAP analitikai szolgáltatások vezérlik. Ez viszont az OLAP pivot táblákon végrehajtható műveletek korlátozásához vezet:

• A kimutatástáblázat ÉRTÉKEK területén a mértékeken kívül nem helyezhet el mezőket;
• az összegzéshez használt függvényt nem lehet megváltoztatni;
• Nem hozhat létre számított mezőt vagy számított tételt;
• A mezőnevek minden módosítása azonnal megszakad, miután a mezőt eltávolították a kimutatástáblából;
• Az oldalmező paramétereinek módosítása nem megengedett;
• parancs nem elérhető Előadásoldalakat;
• opció letiltva Előadásaláírásokatelemeket ha az értékterületen nincsenek mezők;
• opció letiltva Részösszegek a szűrő által kiválasztott oldalelemek szerint;
• paraméter nem elérhető Háttérkérés;
• után dupla kattintás A kimutatástábla gyorsítótárából csak az első 1000 rekord jelenik meg az ÉRTÉKEK mezőben;
• jelölőnégyzet letiltva Optimalizálásmemória.

Autonóm adatkockák létrehozása

Egy szabványos pivot táblában a forrásadatok a helyi merevlemezen tárolódnak. Így mindig kezelheti őket, és módosíthatja a szerkezetet, még a hálózathoz való hozzáférés nélkül is. De ez semmiképpen sem vonatkozik az OLAP pivot táblákra. Az OLAP pivot táblákban a gyorsítótár nem a helyi merevlemezen található. Ezért közvetlenül a hálózatról való lekapcsolás után helyi hálózat az OLAP pivot tábla többé nem fog működni. Egy ilyen táblázatban egyetlen mezőt sem fog tudni áthelyezni.

Ha továbbra is elemeznie kell az OLAP-adatokat, amikor nem csatlakozik hálózathoz, hozzon létre egy offline adatkockát. Ez egy külön fájl, amely a pivot tábla gyorsítótárát képviseli. Ez a fájl olyan OLAP-adatokat tárol, amelyeket a rendszer a helyi hálózatról való leválasztás után tekint meg. Önálló adatkocka létrehozásához először hozzon létre egy OLAP pivot táblát. Vigye a kurzort a pivot táblába, és kattintson a gombra OLAP eszközök kontextuális lap Elemzés, amely a környezetfüggő lapok készletében található Munka a kimutatástáblákkal. Válasszon ki egy csapatot Offline mód OLAP(8. ábra).

Egy párbeszédpanel jelenik meg a képernyőn Beállítások elem élettartam OLAP(9. ábra). Kattintson a gombra Offline adatfájl létrehozása. A képernyőn megjelenik a Data Cube File Creation Wizard első ablaka. Kattintson a gombra További az eljárás folytatásához.

A második lépésben (10. ábra) jelölje meg azokat a méreteket és szinteket, amelyeket az adatkocka tartalmazni fog. A párbeszédpanelen ki kell választania az OLAP adatbázisból importálni kívánt adatokat. Csak azokat a méreteket kell kiválasztani, amelyekre a számítógép helyi hálózatról való leválasztása után szükség lesz. Minél több dimenziót ad meg, annál nagyobb lesz az autonóm adatkocka.

Kattintson a gombra További hogy folytassa a harmadik lépéssel (11. ábra). Ebben az ablakban ki kell választani azokat a tagokat vagy adatelemeket, amelyek nem fognak szerepelni a kockában. Ha a jelölőnégyzet nincs bejelölve, a megadott elem nem lesz importálva, és felesleges helyet foglal el a helyi merevlemezen.

Adja meg az adatkocka helyét és nevét (12. ábra). Az adatkocka fájlok kiterjesztése .cub.

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 működőképet Excel munkafüzetek, amely a kiválasztott adatkockához társított pivot táblát 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 az offline adatkockafájlt, és frissítheti azt, valamint a kapcsolódó adattáblázatot. Kérjük, vegye figyelembe, hogy bár az offline adatkockát akkor használják, ha nincs hálózati hozzáférés, frissíteni kell, ha a hálózati kapcsolat helyreáll. Az offline adatkocka frissítésének kísérlete a hálózati kapcsolat megszakadása után kudarcot okoz.

Adatkocka függvények használata pivot táblákban

Az OLAP-adatbázisokban használt adatkocka-függvények pivot táblából is futtathatók. BAN BEN elavult verziók Az Excelben csak az Analysis Pack bővítmény telepítése után férhetett hozzá az adatkocka funkciókhoz. Az Excel 2013-ban ezek a funkciók be vannak építve a programba, ezért használhatók. Ahhoz, hogy teljes mértékben megértsük képességeiket, nézzünk meg egy konkrét példát.

Az egyik legtöbb egyszerű módokon Az adatkocka funkcióinak elsajátítása magában foglalja az OLAP pivot tábla adatkocka-képletekké alakítását. Ez az eljárás nagyon egyszerű, és lehetővé teszi az adatkocka-képletek gyors beszerzését anélkül, hogy azokat a semmiből hozná létre. A fő elv az, hogy a kimutatástábla összes celláját le kell cserélni olyan képletekre, amelyek az OLAP adatbázishoz kapcsolódnak. ábrán. A 13. ábra egy OLAP adatbázishoz társított pivot táblát mutat.

Vigye a kurzort a pivot tábla bármely pontjára, kattintson a gombra OLAP eszközök környezetfüggő szalaglap Elemzésés válassz egy csapatot Konvertálás képletekre(14. ábra).

Ha a kimutatástáblázat jelentésszűrő mezőt tartalmaz, az 1. ábrán látható párbeszédpanel jelenik meg a képernyőn. 15. Ebben az ablakban megadhatja, hogy az adatszűrő legördülő listáit képletté kívánja-e alakítani. Ha a válasz igen, a legördülő listák törlődnek, és helyette statikus képletek jelennek meg. Ha a jövőben legördülő listák használatával kívánja módosítani a pivot tábla tartalmát, törölje a jelölést a párbeszédpanel egyetlen négyzetéből. Ha kompatibilitási módban dolgozik egy kimutatáson, az adatszűrők automatikusan képletté alakulnak, előzetes figyelmeztetés nélkül.

Néhány másodperc múlva a kimutatás helyett olyan képletek jelennek meg, amelyek az adatkockákon futnak, és kimenetet biztosítanak az Excel ablakban. szükséges információ. Kérjük, vegye figyelembe, hogy ez eltávolítja a korábban alkalmazott stílusokat (16. ábra).

Rizs. 16. Vessen egy pillantást a képletsorra: a cellák az adatkocka képleteket tartalmazzák

Mivel a megtekintett értékek már nem részei a kimutatásobjektumnak, hozzáadhat oszlopokat, sorokat és számított tagokat, kombinálhatja őket más külső forrásokkal, és testreszabhatja a jelentést. különböző utak, beleértve a húzó és ejtő képleteket.

Számítások hozzáadása az OLAP pivot táblákhoz

Az Excel korábbi verzióiban az OLAP pivot táblák nem tették lehetővé az egyéni számításokat. Ez azt jelenti, hogy nem lehetett hozzáadni egy további elemzési szintet az OLAP-pivotTable-okhoz ugyanúgy, mint a számított mezők és tagok hozzáadása a szokásos kimutatásokhoz (további információért kérjük, győződjön meg róla, hogy ismeri ezt az anyagot, mielőtt folytatná olvasás ).

Az Excel 2013 új OLAP-eszközöket – számított mértékeket és MDX-kifejezések számított tagjait – vezet be. Már nem korlátozódik a DBA által biztosított OLAP-kockában lévő mértékek és tagok használatára. kapsz további jellemzők elemzés egyéni számítások létrehozásával.

Bevezetés az MDX-be. Ha kimutatástáblát használ egy OLAP-kockával, akkor MDX (Multidimensional Expressions) lekérdezéseket ad ki az adatbázisnak. Az MDX egy lekérdezési nyelv, amelyet többdimenziós forrásokból (például OLAP-kockákból) származó adatok lekérésére használnak. Amikor egy OLAP-pivotTable-t módosítanak vagy frissítenek, a megfelelő MDX-lekérdezések elküldésre kerülnek az OLAP-adatbázisba. A lekérdezés eredménye visszakerül az Excelbe, és megjelenik a kimutatásterületen. Ez lehetővé teszi az OLAP adatokkal való munkát anélkül helyi példány pivot tábla gyorsítótár.

Számított mértékek és MDX-tagok létrehozásakor MDX nyelvi szintaxist kell használnia. Ezzel a szintaxissal a pivot tábla lehetővé teszi a számítások interakcióját szerver oldal OLAP adatbázisok. A könyvben tárgyalt példák alapvető MDX-terveken alapulnak, amelyek újat mutatnak be Excel függvények 2013. Ha összetett számított mértékeket és MDX-tagokat kell létrehoznia, időt kell szánnia az MDX képességeinek további megismerésére.

Számított mértékek létrehozása. A számított mérték a számított mező OLAP verziója. Az ötlet egy új adatmező létrehozása a meglévő OLAP mezőkön végrehajtott néhány matematikai művelet alapján. ábrán látható példában. 17. pontja alapján egy OLAP összesítő táblázatot használnak, amely tartalmazza az áruk listáját és mennyiségét, valamint az egyes áruk értékesítéséből származó bevételeket. Hozzá kell adnunk egy új mérőszámot, amely kiszámítja a cikk egységenkénti átlagárát.

Elemzés Munka a kimutatástáblákkal. A legördülő menüben OLAP eszközök tárgy kiválasztása (18. ábra).

Rizs. 18. Válasszon ki egy menüpontot MDX Számított mérték

Egy párbeszédpanel jelenik meg a képernyőn Hozzon létre egy számított mértéket(19. ábra).

Kovesd ezeket a lepeseket:

2. Válassza ki azt a mértékcsoportot, amelyben az új számított mérték található. Ha ezt nem teszi meg, az Excel automatikusan az első elérhető mértékcsoportba helyezi az új mértéket.

3. A terepen MDX kifejezés(MDX) Írja be az új mértéket meghatározó kódot. A beviteli folyamat felgyorsításához használja a bal oldali listát a számításokhoz használandó meglévő mértékek kiválasztásához. Kattintson duplán a kívánt mértékre, hogy hozzáadja az MDX mezőhöz. A következő MDX-et használják az átlagos egységértékesítési ár kiszámításához:

4. Kattintson az OK gombra.

Ügyeljen a gombra Ellenőrizze az MDX-et, amely az ablak jobb alsó részén található. Kattintson erre a gombra az MDX szintaxis helyességének ellenőrzéséhez. Ha a szintaxis hibákat tartalmaz, megjelenik egy üzenet.

Miután befejezte az új számított mérték létrehozását, lépjen a listára Pivot tábla mezőiés válassza ki (20. ábra).

A számított mérték hatóköre csak az aktuális munkafüzetre vonatkozik. Más szavakkal, a számított mértékek nem közvetlenül az OLAP-kiszolgálókockában jönnek létre. Ez azt jelenti, hogy senki sem férhet hozzá a kiszámított mértékhez, hacsak nem nyitja meg általános hozzáférés a munkafüzetbe, különben nem teszi közzé az interneten.

Számított MDX-tagok létrehozása. Az MDX számított tag a normál számított tag OLAP változata. Az ötlet egy új adatelem létrehozása a meglévő OLAP-elemeken végrehajtott néhány matematikai művelet alapján. ábrán látható példában. 22, OLAP pivot táblát használnak, amely tartalmazza a 2005–2008 közötti értékesítési információkat (negyedéves bontásban). Tegyük fel, hogy az első és a második negyedév adatait szeretné összesíteni egy új elem, az első félév létrehozásával. A harmadik és negyedik negyedévre vonatkozó adatokat is kombináljuk, új elemet alkotva az év második felében.

Rizs. 22. Új MDX számított tagokat fogunk hozzáadni az év első feléhez és az év második feléhez

Helyezze a kurzort bárhová a kimutatásban, és válassza ki a környezetfüggő lapot Elemzés kontextuális lapok készletéből Munka a kimutatástáblákkal. A legördülő menüben OLAP eszközök tárgy kiválasztása MDX számítógépes tag(23. ábra).

Egy párbeszédpanel jelenik meg a képernyőn (24. ábra).

Rizs. 24. Ablak Számított tétel létrehozása

Kovesd ezeket a lepeseket:

1. Adjon nevet a számított mértéknek!

2. Válassza ki azt a szülőhierarchiát, amelyhez új számított tagokat hoz létre. Egy építkezésen Szülő elemértéket rendeljen hozzá Minden. Ennek köszönhetően Excel beállítások egy kifejezés kiértékelésekor hozzáfér a szülőhierarchia összes eleméhez.

3. Az ablakban MDX kifejezésÍrja be az MDX szintaxist. Időt takaríthat meg a bal oldali lista segítségével az MDX-ben használni kívánt meglévő tagok kiválasztásához. Kattintson duplán a kiválasztott elemre, és az Excel hozzáadja az ablakhoz MDX kifejezés. ábrán látható példában. 24, az első és a második negyedév összegét számítják ki:

..&& +

.. && +

.. && + …

4. Kattintson az OK gombra. Az Excel megjeleníti az újonnan létrehozott MDX számított tagot a kimutatásban. ábrán látható módon. A 25. ábra szerint az új számított elem a többi számított elemmel együtt megjelenik a kimutatásban.

ábrán. A 26. ábra egy hasonló folyamatot szemléltet az év második felében számított tétel létrehozásához.

Figyeljük meg, hogy az Excel meg sem kísérli az eredeti MDX-tagok eltávolítását (27. ábra). A PivotTable továbbra is a 2005–2008-as évekhez tartozó rekordokat jeleníti meg negyedévenkénti bontásban. Ebben az esetben ez nem nagy baj, de a legtöbb esetben el kell rejteni az „extra” elemeket a konfliktusok elkerülése érdekében.

Rizs. 27. Az Excel a létrehozott MDX számított tagot eredeti tagként jeleníti meg. De még mindig jobb az eredeti elemek törlése az ütközések elkerülése érdekében

Ne feledje: A számított tagok csak az aktuális munkafüzetben találhatók. Más szavakkal, a számított mértékek nem közvetlenül az OLAP-kiszolgálókockában jönnek létre. Ez azt jelenti, hogy senki sem férhet hozzá a számított mértékhez vagy a számított taghoz, hacsak meg nem osztja a munkafüzetet vagy közzé nem teszi az interneten.

Vegye figyelembe, hogy ha egy OLAP-kockában a szülő hierarchia vagy a szülőelem megváltozik, az MDX által számított elem már nem működik. Ezt az elemet újra kell létrehoznia.

OLAP számítások kezelése. Az Excel olyan felületet biztosít, amely lehetővé teszi a számított mértékek és MDX-tagok kezelését az OLAP pivot táblákban. Helyezze a kurzort bárhová a kimutatásban, és válassza ki a környezetfüggő lapot Elemzés kontextuális lapok készletéből Munka a kimutatástáblákkal. A legördülő menüben OLAP eszközök tárgy kiválasztása Számításkezelés. Az ablakban Számításkezelés Három gomb áll rendelkezésre (28. ábra):

• Teremt. Hozzon létre egy új számított mértéket vagy számított MDX-tagot.
• Változás. Módosítsa a kiválasztott számítást.
• Töröl. Törölje a kiválasztott számítást.

Rizs. 28. Párbeszédpanel Számításkezelés

Végezzen „mi lenne, ha” elemzést az OLAP-adatokon. Az Excel 2013-ban elvégezheti az OLAP kimutatástábláiban lévő adatok „mi lenne, ha” elemzését. Ennek köszönhetően új lehetőség Módosíthatja az értékeket a kimutatásban, és a változtatások alapján újraszámíthatja a mértékeket és a tagokat. A változtatásokat az OLAP-kockába is visszaterjesztheti. A mi lenne, ha elemzési képességeinek kihasználásához hozzon létre egy OLAP-pivotTable-t, és válassza ki a környezetfüggő lapot Elemzés Munka a kimutatástáblákkal. A legördülő menüben OLAP eszközök Válassz csapatot Mi lenne, ha elemzés –> Mi lenne, ha elemzés engedélyezése(29. ábra).

Ettől kezdve megváltoztathatja a pivot tábla értékeit. A kiválasztott érték módosításához a kimutatástáblában kattintson rá jobb gombbal, majd helyi menü tárgy kiválasztása (30. ábra). Az Excel újra futtatja a kimutatás összes számítását az Ön által végzett módosításokkal, beleértve a számított mértékeket és a számított MDX-tagokat is.

Rizs. 30. Válasszon ki egy elemet Vegye figyelembe a változást a pivot tábla kiszámításakor hogy módosítsa a pivot táblát

Alapértelmezés szerint a PivotTable-ban végzett szerkesztések a mi lenne, ha elemzési módban helyiek. Ha a módosításokat az OLAP-kiszolgálóra szeretné terjeszteni, válassza ki a módosítások közzétételére szolgáló parancsot. Válasszon egy környezetfüggő lapot Elemzés, amely a környezetfüggő lapok készletében található Munka a kimutatástáblákkal. A legördülő menüben OLAP eszközök elemek kiválasztása Mi lenne, ha elemzés – > Módosítások közzététele(31. ábra). A parancs futtatása engedélyezi a visszaírást az OLAP-kiszolgálón, ami azt jelenti, hogy a változtatások továbbíthatók a forrás OLAP-kockára. (Az OLAP-kiszolgáló módosításainak terjesztéséhez rendelkeznie kell a megfelelő jogosultságokkal a kiszolgáló eléréséhez. Forduljon az adatbázis-adminisztrátorhoz, hogy segítsen megszerezni az OLAP-adatbázis írási hozzáférési engedélyeit.)

A jegyzet Jelen, Alexander könyve alapján készült. . 9. fejezet

A harmadik cikk nagy mennyiségű adat feldolgozásával foglalkozik Excel használatával, leírja a pivot táblák használatának előnyeit. Általában ennek a cikknek kellett volna az elsőnek lennie a sorozatban, ha ennek vagy annak a munkamódszernek az előnyeiről beszélünk. Valójában a Pivot Table felületet kifejezetten nagy mennyiségű adat elemzésére hozták létre, amelyek nem csak táblázatos tartományokban, hanem külső adatforrásokban is tárolhatók. A pivot táblák működési elveinek és gyakorlati felhasználásának megismerése jelentősen optimalizálhatja a közgazdászok napi munkáját. Az adatelemzés szintjének emelése pedig a vállalat jobb irányításához és a különböző szintű vezetők helyes vezetői döntéseinek meghozatalához vezet.

A pivot táblákkal és a többdimenziós adatelemzéssel kapcsolatos általános elméleti kérdéseket egy másik cikk ismerteti weboldalunkon.

Itt részletesebben kitérünk a pivot tábla felületet használó adatfeldolgozás konkrét módszereire. Használja a fájlt példaként nwdata_pivot.xls.

Pivot táblák használata

Egyedi értékek mintavétele

Az egyik legnépszerűbb pivot tábla segítségével megoldott feladat az egyedi értékek kiválasztása egy listából vagy adattömbből. A PivotTable felület használatával ezt a problémát a legelegánsabb módon oldhatja meg - képletek használata nélkül.

A lapon található példában Minta országok listáját és az adattömbben szereplő említések számát mutatja.

Az adatmezőnek tartalmaznia kell a művelet típusát – „mennyiség”. Ez a beállítás lehetővé teszi a nem numerikus forrásadatmezők feldolgozását a kimutatás adatterületén. A számlálási művelet alternatívája a szabványos COUNTIF függvény. Hozzon létre egy készletet egyedi értékek elvileg csak képletek használata is lehetséges (lásd az 1. részt), de ehhez nagyon bonyolult képletekre lesz szükség, kiszámított címzéssel. Vagyis ebben a feladatban a pivot tábla használata a legoptimálisabb megoldás a megoldásra.

Értékek összege

A Pivot Table felület használatának másik népszerű feladata az adathalmaz egyedi rekordjainak összegzése.

A lapon található példában Összegösszefoglaló adatok készültek az egyes országok rendeléseiről:

Az adatmezőben szereplő "Sum" művelettípus csak numerikus mezőket engedélyez. A gyakorlatban szinte soha nem alkalmazzák a forrásadatok más típusú összesítését.

Egy probléma szabványos képletekkel történő megoldásához használhatja a SUMIF függvényt. A nehézség nyilván nem az értékek konszolidációjában adódik, hanem az előző példához hasonlóan egy egyedi lista (a példában az országok nevei) kiválasztásában.

2D elemzés

A korábban ismertetett példák egy kritériumon alapuló adatelemzést mutatnak be. A táblázatok lehetővé teszik az adatok két dimenzióban való megjelenítését: oszlopok és sorok. A kimutatástábláknak is vannak ilyen adatmegjelenítési területei.

A lapon található példában asztal ország és dátum szerinti jelentés készült, amely a rendelések számának időbeli változásait mutatja. Kérjük, vegye figyelembe, hogy a dátumtípus mezőben további csoportosítás kerül alkalmazásra: hónap és év szerint.

A több kritérium szerinti összegzés is lehetséges a szabványos Excel SUMIFS, SUMPRODUCT függvényeken, valamint a tömbfeldolgozó függvényeken keresztül (lásd az 1. részt). Ehhez az opcióhoz azonban a paraméterek korábban ismert értékei - a választógombok - szükségesek. Ezenkívül a képletekkel végzett számítások lényegesen több időt igényelnek, ami nagy mennyiségű adat esetén jelentős termelékenységcsökkenéshez vezethet.

Többváltozós elemzés

A terület sorok és oszlopok szerinti vizuális elemzése mellett a kimutatástáblázatokban globális szűrőt is használhat a forrásadatok egy vagy több mezője szerint. Erre a célra szánták speciális terület a kimutatástábla felett található cellákban.

Példa egy lapon Szűrő bemutatja, hogy egy pivot tábla szűrőterülete segítségével megtekinthetők egy ország vállalatainak adatai:

A szűrőmező áthelyezhető a sorok vagy oszlopok területére, lehetővé téve az információk nagyobb tömbjének megtekintését. A leírt szűrőterületen kívül további adatszűrés is elvégezhető a kulcsmezők listáinak beállításával a sor- vagy oszlopterületeken.

A munkalapképleteket használó pivot tábla szűrők használatának analógja a legtöbb esetben a tömbfeldolgozási képletek.

Példák lapokon pivot1És pivot2 lehetőségeket jelenít meg ugyanazon információk megjelenítésére a kimutatás különböző méretbeállításaival.

Adatokkal való munka

Adatfrissítés

A pivot tábla mind a cellák tetszőleges területén található adatokon, mind pedig külső adatforrásokon alapulhat. Nézzük először az első lehetőséget. Azok. Az elemzéshez szükséges adatokat egy sor munkacella tárolja Excel lap.

Pivot tábla formájú riport készíthető mind egyszeri, mind változó forrásadatkészlettel folyamatos használatra. Utóbbi lehetőség nagyobb lehetőségeket biztosít a felhasználónak az interaktív munkára: a jelentési űrlapot egyszer kell konfigurálni és formázni, majd a forrásadatok szerkesztésekor automatikusan megtörténik a végleges forma módosítása. Ebben az esetben a riport nem csak az adatokat módosítja, hanem sorokat, oszlopokat is tud hozzáadni és törölni, amit munkalapképletekkel gyakorlatilag lehetetlen megvalósítani.

A Kimutatás varázsló lehetővé teszi adatforrásként használandó cellatartomány megadását. Ha az információk frissítése során új sorokat adtak hozzá, előfordulhat, hogy nem szerepelnek a kimutatástábla adatforrásában, és ennek megfelelően a rendszer nem elemzi őket megfelelően. Ezt a funkciót meglehetősen nehéz követni nagy mennyiségű adat feldolgozásakor.

Meglévő kimutatástáblázat adatforrás-tartományát az Excel 2007–2010 speciális párbeszédpaneljén módosíthatja. Az Excel korábbi verzióiban ezt az interfész funkciót a Kimutatás varázslóban valósították meg, amikor az aktív kimutatási táblából indult. A varázsló megnyitása után egy lépéssel vissza kell lépnie:

Az adatforrás korrekciói is elvégezhetők programozottan. Például a VBA-szerkesztő számítási ablakán keresztül (azonnali):

Annak érdekében, hogy ne gondoljon a pivot tábla adatok forrástartományának méretének helyességére, az építés során kezdetben megadhat egy sor tartományt nagy margóval. Például, ha tudja, hogy a sorok várható mennyisége nem haladja meg a 10 000-et, azonnal beállíthatja ezt az értéket tartományméretként. A gyakorlatban az ilyen redundancia nem vezet látható lassuláshoz a PivotTable felület működésében. Az üres értékeket elrejtheti a jelentésdimenziókban. Ennek a módszernek a hátránya mindenekelőtt a „dátum” típusú mezőkkel való munka során nyilvánul meg. A standard pivot tábla felület lehetővé teszi különféle csoportosítások megvalósítását a „dátum” típussal (hónap, negyedév szerint), de ha elérhető üres értékek Ezek a beállítások már nem érhetők el, mert az Excel az oszlopot szövegoszlopként határozza meg.

Az adatforrás vezérlésének megfontolt módszerein kívül javasoljuk az aktív munkalap pivot táblájának sortartományának programozási módszerekkel történő konfigurálását. Ha az adatforrás a lap teljes munkaterületét elfoglalja, akkor a következő parancsot használhatja:

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData, "!")) & _ Range(Application.ConvertFormula(_ ActiveSheet.PivotTables) (1).Forrásadatok, xlR1C1, xlA1) _).Worksheet.Used Range.Address(ReferenceStyle:=xlR1C1)

A legmegbízhatóbb, de lassú módon, a forráslap sorainak szekvenciális ellenőrzése, majd a tulajdonság kitöltése SourceData aktív pivot tábla. Vegye figyelembe, hogy ezt a tulajdonságot csak az R1C1 címzés tárolja.

A makrók esemény szerint hívhatók meg Worksheet_Activate, vagy konfiguráljon egy gyorsbillentyűt.

Munka elemzési eredményekkel

A kimutatás egy Excel-munkalap cellatartományában található. Munkalapképletek írása a kimutatástábla határain belül nem megengedett, sem manuálisan, sem programozott módszerekkel. Elméletileg lehetséges a pivot tábla határain belüli cellákkal dolgozni külső képletek hivatkozásaival. A gyakorlatban gyakran a VLOOKUP függvényt a pivot tábla oszlopában való keresésre is használják. Ezt a módszert nagyon óvatosan kell használni - az összesítő jelentés felülete megköveteli a megjelenített adatok helyzetének megváltoztatását derékszögű koordináták munkalapot anélkül, hogy ez bármilyen hatással lenne az adatok forrására. Vagyis nincs garancia arra, hogy a pivot táblán belüli képletben megadott hivatkozás mikor a megfelelő értéket fogja megjeleníteni további munka a fájllal. Az adatforrás azonban nem változhat.

Elérhető alternatív módokon a pivot tábla eredményeinek feldolgozása:

1. PivotTable értékek másolása és beillesztése egy másik lapra (a " Speciális betét") további adatkereséssel már ebben a kialakult cellatartományban. Egy egyszerű táblázatban sokkal nehezebb megsérteni az adatok integritását, mint egy összefoglaló táblázatban. Nyilvánvalóan ennek a munkamódszernek a fő hátránya a kézi műveletek alkalmazása az adatforrás minden frissítése után.
2. Használja a GETPIVOTDATA funkciót (Excel 2002 és újabb). Ez a funkció magában foglalja az adatok elérését nem a munkalap koordinátái, hanem a pivot tábla méretei alapján. Az OLAP kocka típusú adatforrásokhoz speciális funkciók állnak rendelkezésre az adatok és dimenziók elérésére: CUBEVALUE, CUBEMEMBER és mások (Excel 2007-2010). Ez a módszer kényelmetlen és jelentősen lelassítja a munkát, ha sok különböző értéket kell beszereznie egy pivot táblában.
3. Az eredmények eléréséhez iratkozzon ki a kimutatásból. Ehelyett használja a munkalap képleteit (lásd az 1. részt). Ez a módszer, bár nehezen kivitelezhető, akkor lehet a legkényelmesebb, ha más számítások is az eredményeken alapulnak, és az adatforrást gyakran frissítik.

Pivot tábla interfész verziói

Az új xlsx fájlformátumban (Excel 2007-2010) jelentősen megváltoztak a pivot tábla felületének lehetőségei. BAN BEN előző verziók felületen (97-2003) csak „kozmetikai” változtatások történtek:

• Excel 2000 (9.0) – alap verzió Pivot tábla felület.
• Excel XP (10.0) - új GETPIVOTDATE funkció
• Excel 2003 (11.0) – Úgy tűnik, egyáltalán nem történt módosítás
• Excel 2007 (12.0) – egy új verzió Pivot tábla interfész kiterjesztett tartományok támogatásával. Javított teljesítmény, megváltozott kinézet felület. A régi formátummal való kompatibilitás megmarad.
• Excel 2010 (14.0) – PowerPivot bővítmény támogatása. Munkavégzés frissített OLAP kockákkal.

Főbb változások az új fájlformátumban (2007-2010):

• Egy oszlop egy pivot tábla több mezőjét is tartalmazhatja, behúzással kiemelve (tömörített formában).
• Szeletek A kimutatások lehetővé teszik az aktuális szűrési értékkészlet vizuális megjelenítését.
• A szűrőterület méretei támogatják a többszörös kijelölést.
• A méretelemek elrejthetők/megjeleníthetők a fejléccel egy cellában található gombokkal.
• Számos új paraméter található a mező és a tábla tulajdonságaiban.
• A kimutatástábla stílusok segítségével bármikor módosíthatja a jelentések megjelenését.

A különbségek jobb megértéséhez töltse le és nyissa meg a példafájlokat nwdata_pivot1.xlsxÉs nwdata_pivot2.xlsx(az archívumban nwdata_pivot.zip). Az első fájl a jelentést a régi formátumban mutatja be, a második - az újban, a forrásadatok ugyanazok.

A pivot tábla felületének belső felépítése

A pivot tábla működési elveinek jobb megértéséhez nézzük meg az interfész belső felépítését.

Pivot tábla gyorsítótár

Kimutatás létrehozásakor vagy frissítésekor a kiválasztott forrástípustól függetlenül az Excel az adatokat egy köztes tárolóba, ún. pivot tábla gyorsítótár. Az adatok gyorsítótárban való rendszerezésének struktúrája lehetővé teszi az adatok összesítésének és számításainak jelentős optimalizálását a pivot táblában. Az adatok saját gyorsítótárában való tárolása lehetővé teszi különböző adatforrások használatát, miközben megőrzi a hasonló funkciókat.

A gyorsítótárban lévő adatok frissítése a pivot táblázat felületének „Frissítés” gombjára kattintva (a szalagon vagy a helyi menüben található gomb), vagy meghatározott időközönként, ha a paraméterekben ilyen beállítás szerepel. Mód Excel számítások(automatikus vagy kézi) semmilyen módon nem befolyásolja a pivot táblát.

Több pivot tábla (vagy diagram) képes megjeleníteni ugyanabból a gyorsítótárból származó adatokat. Ez a munkaopció ugyanazon adatok több jelentési űrlapjának megjelenítésére szolgál a mérésbeállítási felület használata nélkül. Ebben az esetben az egyik tábla frissítésekor az ugyanazon a gyorsítótáron alapuló automatikusan újraépül.

VBA objektumok

Az adatokhoz programozott módszerekkel való hozzáférés a pivot tábla objektumok - objektum szintjén lehetséges Pivot tábla. Más PivotTable objektumok felelősek az elemek és adatok elrendezéséért és vizuális megjelenítéséért. Ide tartoznak a mezők gyűjteményei: PivotFields, ColumnFields, RowFields, PageFields, DataFields. A mezőérték-beállítások az objektumgyűjteményekben érhetők el PivotItems.

Univerzális képesség az adatok közvetlen elérésére a gyorsítótárban (objektum PivotCache) valamilyen oknál fogva nem biztosítják az Excel fejlesztői. A logika nem teljesen világos. Mint már említettük, a gyorsítótár adatait külön tárolják, és még xlsx fájlban is láthatók, ha ezt a fájlt zip-archívumként nyitja meg. Az adatforrás típusától függően próbálkozhat a tulajdonság használatával SourceData(tartomány alapú pivot táblákhoz) ill Rekordkészlet(az „adatbázis lekérdezés” típusú forrásokhoz).

Számított mezők és kimutatás objektumok ( Számított mezők, CalculatedItems) saját számítási mechanizmussal és képletfüggőségi fával rendelkeznek, amely nem kapcsolódik az Excel munkalap képletekhez. A gyakorlatban azt javasoljuk, hogy lehetőség szerint kerüljük el a nagyszámú számított mezőt a pivot táblákban, mivel ez a számítások jelentős lelassulásához vezet. A cellatartományok formájú adatforrások esetén gyakran egyszerűen hozzáadhat egy oszlopot egy szabályos képlettel a forrásadatokhoz, adatbázis-lekérdezések esetén pedig számításokat közvetlenül az SQL-lekérdezés törzséhez adhat hozzá.

Az adatforrások típusai

Globálisan az adatforrások három típusra oszthatók:

1. Sejttartományok
2. Adatbázis lekérdezések
3. Az OLAP-kockák és a PowerPivot2010, mint az OLAP-mechanizmus megvalósításának egyik lehetősége.

Tartományok

Az első lehetőség a leggyakoribb a gyakorlatban; A példák előző leírása kifejezetten a cellák tartományában tárolt adatokra vonatkozik.

A szabványos Excel felület nem teszi lehetővé több cellatartomány alapján összefoglaló jelentés készítését. Ennek a korlátozásnak az oka nem egészen világos. Fennáll a gyanú, hogy a fejlesztők egyszerűen nem tudnak intuitív felhasználói felületet kínálni a probléma megoldására. A feladat technikai megvalósítása nem tűnik túl bonyolultnak - csak ki kell töltenie az adatgyorsítótárat. fejezetben Kiegészítők Weboldalunk bemutatja saját megoldásunkat komplex összefoglaló jelentések készítésére.

Adatbázis lekérdezések

Az adatbázis-lekérdezések különféle technikai mechanizmusokkal valósíthatók meg: Microsoft Query, ADO, ODBC. Az adathozzáférési felülettől függetlenül ennek a megoldásnak az egyesítő tényezője a kimutatástábla gyorsítótárának feltöltése közvetlenül egy külső forrásból. A pivot táblával való további munka során a lekérdezés újra végrehajtható, majd az adatok ismét a gyorsítótárba kerülnek. Ez a módszer lehetővé teszi a külső forrásokból (számviteli rendszerekből) származó adatok valós időben történő elemzését. Ha megszakad a kapcsolat az adatforrással, a gyorsítótárban lévő legfrissebb adatok elemzése végezhető el.

OLAP kockák

Az OLAP-kocka középfokú információ-előkészítést biztosít a pivot táblákban végzett többdimenziós elemzéshez. A kocka információkat tárol a rendelkezésre álló mezőtípusokról (dimenzió vagy adat), a hierarchikus mezőfüggőségekről, az összesített értékekről (részösszegekről) és más számított elemekről. A kockák használatának fő előnye a közvetlen adatbázis-lekérdezésekkel szemben a nagy teljesítmény, mivel az adatokat a köztes tárolóban mozgatják és összesítik. A hátránya is nyilvánvaló ez a módszer– Az OLAP kockaadatok irreleváns információkat tartalmazhatnak, ami a tárolási beállításoktól függ.

Az Office 2007 előtt egy egyszerű OLAP-kockát lehetett készíteni a használatával Microsoft segítség Kérdezzen, de be legújabb verziói Ez a funkció ismeretlen okok miatt le lett tiltva. A fejlesztők határozottan javasolják az SQL Server Analysis Service használatát az OLAP-kockák létrehozásához és konfigurálásához. Az ajánlás hasznos, de egyrészt ez a szolgáltatás csak az SQL Server fizetős verzióiban szerepel, másrészt komoly tanulmányozást igényel mind az interfész, mind az MDX-lekérdezések feldolgozásának nyelve.

A cikk példája egy archívumot mutat be nwdata_cube.zip két fájllal nwdata_cube.cub, nwdata_cube.xls. Kérjük, vegye figyelembe a PivotTable felület változásait, ha OLAP-kockát használ adatforrásként:

• Hierarchikus dimenziók jelenléte, nincs lehetőség megváltoztatni a szülő ill gyermek elem néhány helyen.
• Nem megengedett a méretek áthelyezése az adatterületre és fordítva.
• A részösszegek minden elemnél megjelennek, az aktuális csoportszűrő nem.

PowerPivot

Egy speciális PowerPivot-bővítmény érhető el az Excel 2010-hez, amely általában egy alternatív mechanizmus az OLAP-kockák megvalósításához. A PowerPivot segítségével rekordok millióit dolgozhatja fel különféle információs fájlokból és adatbázisokból óriási teljesítménnyel. Ugyanakkor a végső adatelemzés felhasználói felülete az Excel 2010-ben valósul meg.

Nagyon valószínű, hogy ez a bővítmény alapvető funkcióként bekerül az Excel következő verziójába. Nagyon reméljük, hogy egy külön cikket vagy akár egy cikksorozatot szentelhetünk a PowerPivot működésének leírására. Ma a PowerPivot + Excel talán a legtöbb erős eszköz nagy mennyiségű adat elemzéséhez.

Hivatalos PowerPivot webhely.

Valójában nagyon kényelmes többdimenziós CUBE használata a vezetői jelentéskészítés során. A költségvetési formátumok fejlesztése során találkozhat a többváltozós űrlapok problémájával (erről bővebben a 8. „Költségvetés beállításának technológiája egy vállalatnál” című könyvben, valamint a „Vállalati könyvelés beállítása és automatizálása” című könyvben olvashat.

Ennek oka az a tény, hogy egy vállalat hatékony irányítása egyre részletesebb részletezést igényel vezetői jelentés. Vagyis a rendszer egyre több különböző analitikai szakaszt használ (in információs rendszerek az analitikát referenciakönyvek határozzák meg).

Ez természetesen oda vezet, hogy a vezetők minden őket érdeklő analitikai szekcióban jelentést szeretnének kapni. Ez azt jelenti, hogy a jelentéseket valahogy „lélegezni” kell. Más szóval azt mondhatjuk, hogy ebben az esetben arról beszélünk, hogy ugyanazon jelentés jelentése különböző elemzési szempontokból adjon információt. Ezért a statikus jelentések sok modern vezetőnek már nem felelnek meg. Szükségük van arra a dinamikára, amit egy többdimenziós CUBE tud nyújtani.

Így az OLAP technológia már azzá vált kötelező elem modern és fejlett információs rendszerekben. Ezért a szoftvertermék kiválasztásakor figyelni kell arra, hogy az OLAP technológiát használ-e.

Sőt, meg kell tudni különböztetni a valódi KOCKÁKAT az utánzatoktól. Az egyik ilyen szimuláció a pivot táblák az MS Excelben. Igen, ez az eszköz úgy néz ki, mint egy CUBE, de valójában nem az, mivel ezek statikus, nem dinamikus táblázatok. Ezen túlmenően, sokkal rosszabb a lehetőségük arra, hogy hierarchikus könyvtárak elemeiből készítsenek jelentéseket.

Hogy megerősítsük a CUBE használatának relevanciáját a vezetői jelentéskészítés során, idézhetünk legegyszerűbb példaértékesítési költségvetéssel. A vizsgált példában a következő elemzési részek relevánsak a vállalat számára: termékek, fióktelepek és értékesítési csatornák. Ha ez a három elemzés fontos a vállalat számára, akkor az értékesítési költségvetés (vagy jelentés) több változatban is megjeleníthető.

Megjegyzendő, hogy ha költségvetési sorokat hoz létre három elemzési szakasz alapján (mint a vizsgált példában), akkor ez meglehetősen összetett létrehozást tesz lehetővé. költségvetési modellekés készítsen részletes jelentéseket a CUBE segítségével.

Például egy értékesítési költségvetés összeállítható egyetlen elemzés (könyvtár) használatával. A „Termékek” elemzése alapján felépített értékesítési költségvetés példája itt található 1.ábra.

Rizs. 1. Példa egy értékesítési költségvetésre, amely az OLAP-CUBE „Termékek” elemzése alapján készült

Ugyanaz az értékesítési költségvetés két elemzés (könyvtár) segítségével összeállítható. A két „Termékek” és „Ágazatok” elemzése alapján felépített értékesítési költségvetés példája itt található. 2. ábra.

Rizs. 2. Példa egy értékesítési költségvetésre, amely az INTEGRAL szoftvercsomag OLAP-CUBE-jában található két „Termékek” és „Ágazatok” elemzése alapján készült

.

Ha több építkezésre van szükség részletes jelentéseket, akkor ugyanazt az értékesítési költségvetést három elemzés (könyvtár) segítségével állíthatja össze. Példa egy értékesítési költségvetésre, amely három elemzés alapján: „Termékek”, „Ágazatok” és „Értékesítési csatornák” található 3. ábra.

Rizs. 3. Példa egy értékesítési költségvetésre, amely az INTEGRAL szoftvercsomag OLAP-CUBE-jában található három elemzés „Termékek”, „Ágazatok” és „Értékesítési csatornák” alapján készült

Emlékeztetni kell arra, hogy a jelentések generálására használt CUBE lehetővé teszi az adatok különböző sorrendben történő megjelenítését. Tovább 3. ábra Az értékesítési költségvetés először termékenként, majd ágazatonként, majd értékesítési csatornánként „bővül”.

Ugyanazok az adatok eltérő sorrendben is bemutathatók. Tovább 4. ábra ugyanaz az értékesítési költségvetés „bővül” először termékenként, majd értékesítési csatornánként, majd ágazatonként.

Rizs. 4. Példa egy értékesítési költségvetésre, amely az INTEGRAL szoftvercsomag OLAP-CUBE-jában található három elemzési elem „Termékek”, „Elosztási csatornák” és „Árak” alapján épül fel.

Tovább 5. ábra ugyanazt az értékesítési költségvetést először fiókok, majd termékek, majd értékesítési csatornák „bontják ki”.

Rizs. 5. Példa egy értékesítési költségvetésre, amely az „INTEGRAL” OLAP-CUBE szoftvercsomagban található három elemzési elem „ágak”, „termékek” és „értékesítési csatornák” alapján épül fel.

Valójában ez nem minden lehetséges lehetőség az értékesítési költségvetés visszavonására.

Ezenkívül figyelnie kell arra, hogy a CUBE lehetővé teszi a munkát hierarchikus struktúra segédkönyvek. A bemutatott példákban a hierarchikus címtárak a „Termékek” és a „Terjesztési csatornák”.

A felhasználó szemszögéből ő az ebben a példában több vezetői jelentést kap (lásd Rizs. 1-5), és a beállítások szempontjából szoftver termék- ez egy jelentés. Egyszerűen a CUBE használatával többféleképpen is megtekintheti.

Természetesen a gyakorlatban nagyon sok lehetőség van a különböző vezetői jelentések kiadására, ha cikkük egy vagy több elemzőn alapul. Maga az elemzési készlet pedig a felhasználók részletszükségleteitől függ. Igaz, nem szabad elfelejteni, hogy egyrészt minél nagyobb az elemző, annál részletesebb jelentéseket lehet készíteni. Másrészt ez azt jelenti, hogy a pénzügyi költségvetés-tervezési modell összetettebb lesz. Mindenesetre, ha van KUB, a cégnek lehetősége lesz megtekinteni a szükséges beszámolókat különböző változatokban, az érdeklődési körnek megfelelően.

Szükséges megemlíteni az OLAP-CUBE néhány további funkcióját.

Egy többdimenziós hierarchikus OLAP-CUBE-ban több dimenzió van: sortípus, dátum, sorok, 1. könyvtár, 2. könyvtár és 3. könyvtár (lásd. Rizs. 6). Természetesen a jelentés annyi gombot jelenít meg referenciakönyvekkel, amennyi a költségvetési sorban található maximális összeget segédkönyvek. Ha egyetlen költségvetési soron sincs egyetlen referenciakönyv sem, akkor a jelentésben nem lesz egyetlen hivatkozási könyv gomb sem.

Kezdetben az OLAP-CUBE minden dimenzió mentén épül fel. Alapértelmezés szerint a jelentés elkészítésekor a méretek pontosan azokon a területeken helyezkednek el, amelyeken látható 6. ábra. Ez azt jelenti, hogy egy dimenzió, például a „Dátum” a függőleges méretek (méretek az oszlop területén), a „Sorok”, „1. könyvtár”, „2. könyvtár” és „3. könyvtár” méretek területén található - a a vízszintes méretek területe (méretek a terület soraiban), a „Sor típusa” dimenzió pedig a „ki nem bontott” méretek területén (méretek az oldal területén). Ha egy dimenzió az utolsó területen található, akkor a jelentésben szereplő adatok nem „bővülnek” az adott dimenzióban.

Ezen méretek mindegyike elhelyezhető a három terület bármelyikében. A mérések átvitele után a jelentés azonnal átépül a szerint új konfiguráció mérések. Például felcserélheti a dátumot és a sorokat referenciakönyvekkel. Vagy áthelyezheti az egyik referenciakönyvet a függőleges mérési területre (lásd. Rizs. 7). Más szóval, „csavarhatja” a jelentést az OLAP-CUBE-ban, és kiválaszthatja a felhasználó számára legkényelmesebb jelentéskimeneti opciót.

Rizs. 7. Példa egy jelentés újraépítésére az INTEGRAL szoftvercsomag mérési konfigurációjának megváltoztatása után

A mérési konfiguráció megváltoztatható a fő CUBE űrlapon vagy a változástérkép-szerkesztőben (lásd. Rizs. 8). Ebben a szerkesztőben az egérrel is áthúzhatja a méréseket egyik területről a másikra. Ezenkívül egy területen a méréseket felcserélheti.

Ezenkívül ugyanabban a formában beállíthat néhány mérési paramétert. Minden dimenziónál testreszabhatja az összegek helyét, az elemek rendezési sorrendjét és az elemek nevét (lásd. Rizs. 8). Azt is megadhatja, hogy melyik elem neve jelenjen meg a jelentésben: rövidített (Name) vagy teljes (FullName).

Rizs. 8. Az INTEGRAL szoftvercsomag mérési térképszerkesztője

A mérési paramétereket közvetlenül mindegyikben szerkesztheti (lásd. Rizs. 9). Ehhez kattintson a mérés neve melletti gombon található ikonra.

Rizs. 9. Példa az 1. címtár szerkesztésére Termékek és szolgáltatások itt

Ezzel a szerkesztővel kiválaszthatja a jelentésben megjeleníteni kívánt elemeket. Alapértelmezés szerint minden elem megjelenik a jelentésben, de szükség esetén egyes elemek vagy mappák elhagyhatók. Ha például csak egy termékcsoportot kell megjelenítenie a jelentésben, akkor a mérésszerkesztőben törölnie kell az összes többi jelölését. Ezt követően a jelentés csak egy termékcsoportot fog tartalmazni (lásd. Rizs. 10).

Ebben a szerkesztőben az elemeket is rendezheti. Ezenkívül az elemek átrendezhetők különböző utak. Egy ilyen átcsoportosítás után a jelentés azonnal újjáépül.

Rizs. 10. Példa a kimenetre az INTEGRAL szoftvercsomagban csak egy termékcsoport (mappa) jelentésében

A dimenziószerkesztőben gyorsan létrehozhat saját csoportokat, az ott található könyvtárakból elemeket húzhat át stb. Alapértelmezés szerint csak az Egyéb csoport jön létre automatikusan, de más csoportok is létrehozhatók. Így a dimenziószerkesztő segítségével beállíthatja, hogy a referenciakönyvek mely elemei és milyen sorrendben jelenjenek meg a jelentésben.

Valójában az összes ilyen változtatást kezdetben a vonalak felállításakor meg lehetett volna tenni.

A korlátozások segítségével például azt is megadhatja, hogy mely elemek vagy könyvtárcsoportok jelenjenek meg a jelentésben, és melyek ne.

jegyzet: ennek a cikknek a témáját részletesebben tárgyaljuk a workshopokon "Vállalkozás költségvetésének kezelése"És "A vezetői számvitel szervezése és automatizálása" a cikk szerzője, Alexander Karpov vezette.

Ha a felhasználónak szinte rendszeresen csak bizonyos elemeket vagy könyvtármappákat kell megjelenítenie a jelentésben, akkor érdemes ezeket a beállításokat előre elvégezni a jelentéssorok létrehozásakor. Ha a felhasználó számára fontosak a jelentésekben a címtárelemek különféle kombinációi, akkor a módszertan beállításakor nem kell semmilyen korlátozást beállítani. Az összes ilyen korlátozás gyorsan konfigurálható a mérésszerkesztővel.

Az önálló kockafájl (.cub) online analitikai feldolgozási (OLAP) kockában tárolja az adatokat. Ezek az adatok az OLAP-kiszolgálóról származó OLAP-adatbázis részét képezhetik, vagy bármely OLAP-adatbázistól függetlenül is létrejöttek. Ha továbbra is szeretne dolgozni a kimutatásokkal és kimutatásokkal, amikor a kiszolgáló nem elérhető, vagy offline állapotban van, használjon offline kockafájlt.

További információ az offline kockákról

Ha egy OLAP-kiszolgálóról származó adatforráson alapuló kimutatással vagy kimutatással dolgozik, használja az Offline kocka varázslót a forrásadatok másolása egy külön offline kockafájlba a számítógépen. Az offline fájlok létrehozásához telepítenie kell egy OLAP-adatszolgáltatót, amely támogatja ezeket a képességeket, például a Microsoft SQL Server Analysis Services MSOLAP-ját.

Jegyzet: A Microsoft SQL Server Analysis Services szolgáltatásból származó önálló kockafájlok létrehozására és használatára a Microsoft SQL Server telepítési feltételei és licencei vonatkoznak. Tekintse át az SQL Server verziójához tartozó megfelelő licencelési információkat.

Az Offline Cube varázsló használata

Offline kockafájl létrehozásához használja az Offline kocka varázslót az adatok egy részhalmazának kiválasztásához az OLAP adatbázisban, majd mentse el azt. A jelentésnek nem kell a fájlban szereplő összes mezőt tartalmaznia, és az OLAP adatbázisban elérhető bármely dimenziója és adatmezője közül választhat. A fájlméret minimalizálása érdekében csak azokat az adatokat szerepeltetheti, amelyeket meg szeretne jeleníteni a jelentésben. Az összes méretet kihagyhatja, és a legtöbb méréstípusnál több mint-et is kihagyhat alacsony szint részletek és elemek felső szint, amelyeket nem kell megjeleníteni. Offline fájl esetén az adatbázisban az adott elemekhez rendelkezésre álló tulajdonságmezőkbe felvehető összes elem is mentésre kerül.

Az adatok offline módba állítása, majd az adatok online visszaállítása

Ehhez először létre kell hoznia egy kimutatást vagy kimutatást, amely a kiszolgáló adatbázisán alapul, majd a jelentésből önálló kockafájlt kell létrehoznia. Ezt követően, amikor jelentéssel dolgozik, bármikor válthat a kiszolgáló adatbázisa és az offline fájl között (például amikor a laptop otthon vagy útközben, majd visszaállítja a számítógép hálózati kapcsolatát).

Az alábbiakban leírjuk az adatok offline módba helyezésének és online visszaállításának alapvető lépéseit.

Jegyzet:

Kattintson a PivotTable jelentésre. Ha ez egy kimutatásdiagram jelentés, válassza ki a kapcsolódó kimutatást.

A "lapon" Elemzés" csoportban számításokat kattintson a gombra OLAP szolgáltatásés nyomja meg a gombot Offline OLAP.

Válasszon ki egy elemet OLAP csatlakozással majd kattintson a gombra rendben.

Ha a rendszer kéri, hogy keressen adatforrást, kattintson a gombra Keresse meg a forrástés keressen egy OLAP-kiszolgálót a hálózaton.

Kattintson az offline kockafájlon alapuló kimutatás-jelentésre.

Excel 2016-ban: A " lapon adat" csoportban kérések és kapcsolatok Mindent frissíteniés nyomja meg a gombot Frissítés.

Excel 2013 programban: A " lapon adat" csoportban kapcsolatokat kattintson a gomb melletti nyílra Mindent frissíteniés nyomja meg a gombot Frissítés.

A "lapon" Elemzés" csoportban számításokat kattintson a gombra OLAP szolgáltatásés nyomja meg a gombot Offline OLAP.

Kattintson a gombra Offline OLAP mód, és akkor - .

Jegyzet: Állj meg a párbeszédpanelen.

Figyelem:

Offline kockafájl létrehozása OLAP-kiszolgáló adatbázisból

Jegyzet: Ha az OLAP-adatbázis nagy, és a kockafájl szükséges az adatok nagy részhalmazához való hozzáférés biztosításához, sok szabad hely lemezen, és a fájl mentése sokáig tarthat. A teljesítmény javítása érdekében javasolt önálló kockafájlok létrehozása MDX-szkript segítségével.

Probléma: A számítógépemnek nincs elég lemezterülete kocka mentésekor.

Az OLAP adatbázisokat nagy mennyiségű részletes adat kezelésére tervezték, így a kiszolgálón tárolt adatbázis lényegesen több helyet foglalhat el, mint amennyi a helyi merevlemezen elérhető. Ha nagy mennyiségű adatot választ ki egy offline adatkockához, előfordulhat, hogy nem lesz elég szabad lemezterülete. A következő megközelítés segít csökkenteni az offline kockafájl méretét.

Szabadítson fel lemezterületet, vagy válasszon másik lemezt A kockafájl mentése előtt távolítsa el a lemezről. szükségtelen fájlokat vagy mentse a fájlt egy hálózati meghajtóra.

Kevesebb adat belefoglalása egy offline kockafájlba Fontolja meg, hogyan minimalizálhatja a fájlban található adatok mennyiségét úgy, hogy a fájl tartalmazza a kimutatáshoz vagy kimutatáshoz szükséges összes adatot. Próbálja ki az alábbi lépéseket.

Offline kockafájl csatlakoztatása OLAP-kiszolgáló adatbázishoz

Offline kockafájl frissítése és újbóli létrehozása

A szerverkockából vagy új offline kockafájlból származó legfrissebb adatokból létrehozott offline kockafájl frissítése jelentős időt vehet igénybe, és nagy mennyiségű ideiglenes lemezterületet igényel. Futtassa ezt a folyamatot, ha nincs szüksége azonnali hozzáférésre más fájlokhoz, miután megbizonyosodott arról, hogy elegendő hely van a merevlemezen.

Probléma: Frissítéskor nem jelennek meg új adatok a jelentésben.

A forrásadatbázis elérhetőségének ellenőrzése Előfordulhat, hogy az offline kockafájl nem tud csatlakozni a forráskiszolgáló adatbázisához új adatok beszerzéséhez. Győződjön meg arról, hogy a kocka adatforrásának számító kiszolgálón lévő eredeti adatbázist nem nevezték át vagy helyezték át másik helyre. Győződjön meg arról, hogy a szerver elérhető és csatlakoztatható.

Új adatok keresése Kérdezze meg adatbázis-adminisztrátorától, hogy a jelentésben szereplő adatok frissültek-e.

Az adatbázis-szervezet megváltoztathatatlanságának ellenőrzése Ha az OLAP-kiszolgálókockát módosították, előfordulhat, hogy át kell szerveznie a jelentést, létre kell hoznia egy offline kockafájlt, vagy futtassa az OLAP-kocka létrehozása varázslót a módosított adatok eléréséhez. Ha többet szeretne megtudni az adatbázis-módosításokról, forduljon adatbázis-adminisztrátorához.

Egyéb adatok belefoglalása az offline kockafájlba

A módosított offline kockafájl mentése időigényes lehet, és bele kell dolgozni Microsoft Excel nem lehetséges a fájl mentése közben. Futtassa ezt a folyamatot, ha nincs szüksége azonnali hozzáférésre más fájlokhoz, miután megbizonyosodott arról, hogy elegendő hely van a merevlemezen.

Ellenőrizze, hogy van-e hálózati kapcsolat, és hogy az OLAP-kiszolgáló forrásadatbázisa, amelyből az offline kockafájl adatokat szerezte, elérhető-e.

Kattintson egy önálló kockafájlból létrehozott kimutatásra, vagy egy kapcsolódó kimutatás jelentésre egy kimutatáshoz.

A lapon Lehetőségek csoportban Szolgáltatás kattintson a gombra OLAP szolgáltatásés nyomja meg a gombot Offline OLAP mód.

Kattintson a gombra Offline OLAP mód, és akkor - Offline adatfájl szerkesztése.

Kövesse az Offline kocka varázslót a fájlba foglalandó egyéb adatok kiválasztásához. Az utolsó lépésben adja meg a módosítani kívánt fájl nevét és elérési útját.

Jegyzet: A fájl mentésének megszakításához kattintson a gombra Állj meg a párbeszédpanelen Kockafájl létrehozása - folyamat.

Offline kockafájl törlése

Figyelem: Ha egy jelentéshez töröl egy offline kockafájlt, akkor a jelentést többé nem használhatja offline állapotban, és nem hozhat létre offline kockafájlt a jelentéshez.

Zárjon be minden olyan munkafüzetet, amely az offline kockafájlt használó jelentéseket tartalmazza, vagy gondoskodjon arról, hogy az összes ilyen jelentést törölje.

BAN BEN Microsoft Windows Keresse meg és törölje az offline kockafájlt (CUB-fájlt).

további információ

Mindig feltehet kérdést az Excel Tech Community specialistájának, kérhet segítséget a Válaszok közösségben, és javasolhat is új funkció vagy fejlesztés a weboldalon