Memóriahasználat szempontjából hatékony adatmodell létrehozása az Excel és a Power pivot bővítmény használatával

Az Excel 2013-es vagy újabb verziójában több millió sorból álló adatmodelleket hozhat létre, majd hatékony adatelemzést végezhet ezekkel a modellekkel szemben. Az adatmodelleket létrehozhatja a Power Pivot bővítményekkel vagy anélkül, hogy a munkafüzetben tetszőleges számú kimutatást, diagramot és Power View-megjelenítést támogassanak.

Megjegyzés: Ez a cikk az Excel 2013 adatmodelljeit ismerteti. Az Excel 2013-ban bevezetett adatmodellezési és Power pivot-funkciók azonban az Excel 2016 is érvényesek. Az Excel ezekből a verziókból ténylegesen nem lehet különbséget tennie.

Habár a nagy méretű adatmodelleket egyszerűen építhet az Excelben, több oka lehet annak, hogy nem. Első lépésként a táblázatok és oszlopok sokaságát tartalmazó nagy méretű modellek a legtöbb elemzés esetében túlzásnak számítanak, és nehézkes mezőlista legyen a listában. Másodszor a nagy méretű modellek értékes memóriát használnak, amely negatívan befolyásolja az ugyanazon rendszererőforrásokat használó többi alkalmazást és jelentést. Végül Office 365 a SharePoint Online és az Excel Web App egyaránt 10 MB-ra korlátozza az Excel-fájlok méretét. Többmillió sort tartalmazó munkafüzet-adatmodellek esetén a 10 MB-os korlát elég gyorsan fog működni. Lásd az adatmodell specifikációja és korlátaicímű témakört.

Ebből a cikkből megtudhatja, hogy miként hozhat létre olyan szorosan kialakított modellt, amely egyszerűbben használható, és kevesebb memóriát használ. Időt vesz igénybe, hogy a hatékony modellek tervezésével kapcsolatos gyakorlati tanácsokat kifizeti az út minden olyan modellhez, amelyet Ön hoz létre és használ, akár az Excel 2013-ban, Office 365 a SharePoint Online-ban, Office Web Apps kiszolgálón vagy a SharePoint 2013-ban.

Fontolja meg a munkafüzet méret-optimalizáló futtatását is. Elemzi az Excel-munkafüzetet, és ha lehetséges, tömöríti tovább. Töltse le a munkafüzet méretének optimalizálását.

A témakör tartalma

Tömörítési arányok és a memóriában található elemző motor

Semmi sem fogható egy nem létező oszlop a memória alacsony kihasználásához

Két példa a mindig kizárandó oszlopokra

A szükségtelen oszlopok kizárása

Mi a teendő, ha csak a szükséges sorokat szűrem?

Mi a teendő, ha szükségünk van az oszlopra; továbbra is csökkentheti a tárterület költségét?

A DateTime oszlopok módosítása

Az SQL-lekérdezés módosítása

DAX számított mértékek használata oszlopok helyett

Melyik 2 oszlopot kell megtartania?

Befejezés

Kapcsolódó hivatkozások

Tömörítési arányok és a memóriában található elemző motor

Adatmodellek az Excelben a memóriában tárolt adattároló motor segítségével tárolhatók az adatmemória. A motor hatékony tömörítési technikákat vezet be a tárolási követelmények csökkentéséhez, így csökken az eredményhalmaz, amíg az eredeti méret része.

Az adatmodellek átlagosan 7 – 10 szor kisebbek lehetnek, mint a származási pontban lévők. Ha például 7 MB adatot importál egy SQL Server-adatbázisból, az Excel adatmodelljében az adatmodell is könnyen 1 MB vagy kevesebb lehet. A ténylegesen elért tömörítés foka az egyes oszlopok egyedi értékeinek számától függ. A további egyedi értékek, annál több memória szükséges a tároláshoz.

Miért beszélünk a tömörítésről és az egyedi értékekről? Mivel a hatékony modell kiépítése a memóriahasználat minimalizálása érdekében szól, mindezt a maximális tömörítési mód, valamint a legegyszerűbben úgy teheti meg, hogy megszabadítson minden olyan oszlopot, amelyet nem igazán szükséges, különösen akkor, ha ezek az oszlopok nagy számú egyedi értéket tartalmaznak.

Megjegyzés:  Az egyes oszlopok tárolási követelményei közötti különbségek is óriásiak lehetnek. Bizonyos esetekben jobb, ha több oszlopot kell kidolgozni, és nem egy oszlopot, hanem nagy számú egyedi értéket. A DateTime-optimalizálásról szóló szakasz részletesen ismerteti ezt a technikát.

Semmi sem fogható egy nem létező oszlop a memória alacsony kihasználásához

A legtöbb memória-hatékony oszlop az, amelyet soha nem importált az első helyen. Ha hatékony modellt szeretne létrehozni, nézze meg az egyes oszlopokat, és kérdezze meg, hogy hozzájárul-e az elvégezni kívánt elemzéshez. Ha nem, vagy nem biztos benne, hagyja ki. Ha szüksége van rájuk, bármikor hozzáadhat új oszlopokat.

Két példa a mindig kizárandó oszlopokra

Az első példa az adatraktárból származó adatforrásokra vonatkozik. Az adattárházban gyakran előfordul, hogy olyan ETL-folyamatokat talál, amelyek a raktárban tárolt adatok betöltődik és frissíthetők. Az adatfeltöltéskor az oszlopok, például a "Létrehozás dátuma", a "frissítés dátuma" és az "ETL Run" érték jön létre. A modellben nincs szükség ezeknek az oszlopoknak a kiválasztására, és az adatimportáláskor meg kell jelölni őket.

A második példa az elsődlegeskulcs-oszlop kihagyását vonja maga után egy egyedkapcsolat-táblázat importálásakor.

Az elsődleges kulcsokkal több tábla, többek között a ténybeli táblázatok is szerepelnek. A legtöbb tábla (például az ügyfél, az alkalmazott vagy az értékesítési adatok) esetében a tábla elsődleges kulcsát szeretné használni a modellben lévő kapcsolatok létrehozásához.

Az egyedkapcsolat-táblák eltérőek. A tények táblázatában az elsődleges kulcs az egyes sorok egyedi felismeréséhez használatos. A normalizálás szempontjából azonban kevésbé hasznos az adatmodellben, ha csak azokat az oszlopokat szeretné használni, amelyek az elemzéshez vagy a táblák közötti kapcsolatok létrehozásához szükségesek. Ebből az okból kifolyólag, ha egy egyedkapcsolat-táblából importál, ne vegye fel az elsődleges kulcsát. Az elsődleges kulcsok az egyes egyedkapcsolatokban óriási mennyiségű helyet fogyasztanak a modellben, de nem adnak előnyt, mivel nem hozhatók létre kapcsolatok.

Megjegyzés:  Az adatraktárakban és többdimenziós adatbázisokban a többnyire numerikus adatforrásokból álló nagyméretű táblázatok gyakran "egyedkapcsolati táblázatnak" nevezzük. A táblák jellemzően az üzleti teljesítménnyel vagy a tranzakcióval kapcsolatos adatokat tartalmazzák, például az értékesítési és költség adatpontokat, amelyeket összesítenek, és a szervezeti egységekhez, termékekhez, piaci szegmensekhez és földrajzi területekhez igazítottak. Az adatelemzés támogatásához szerepelnie kell a modell minden olyan oszlopának, amely üzleti adatokat tartalmaz, vagy amelyek a más táblákban tárolt adatokat kereszthivatkozással szolgálják. A kihagyni kívánt oszlop az egyedkapcsolat tábla elsődleges kulcs oszlopa, amely olyan egyedi értékekből áll, amelyek csak az egyedkapcsolati táblázatban találhatók, és sehol nem. Mivel a ténybeli táblázatok olyan hatalmasak, a modell hatékonyságának néhány legjelentősebb része abból ered, hogy az egyes sorok vagy oszlopok kizárják a tényt a táblázatból.

A szükségtelen oszlopok kizárása

A hatékony modellek csak azokat az oszlopokat tartalmazzák, amelyekre ténylegesen szüksége lesz a munkafüzetben. Ha meg szeretné adni, hogy mely oszlopok szerepeljenek a modellben, a Power pivot beépülő modul tábla importálás varázslójával kell importálnia az adatot az Excel alkalmazásban az "adatimportálás" párbeszédpanel helyett.

A tábla importálása varázsló indításakor kiválaszthatja az importálandó táblákat.

Tábla importálása varázsló a PowerPivot bővítményben

Minden táblázat esetében kattintson az előnézeti & szűrő gombra, és válassza ki a táblázatnak azt a részét, amelyet igazán szükséges. Azt javasoljuk, hogy először törölje a jelet az összes oszlopból, majd a kívánt oszlopok ellenőrzéséhez végezze el a szükséges műveleteket, miután megtekintette, hogy szükségesek-e az elemzéshez.

A Tábla importálása varázsló villámnézete

Mi a teendő, ha csak a szükséges sorokat szűrem?

A vállalati adatbázisokban és az adatraktárakban sok táblázat a hosszú időn át összegyűjtött múltbeli adatokat tartalmazza. Az is előfordulhat, hogy az érdekli, hogy az Ön által kívánt táblázatok az üzleti területekre vonatkozóan nem szükségesek az adott elemzéshez.

A tábla importálása varázsló segítségével kiszűrheti a múltbeli vagy a nem kapcsolódó adatok körét, és így sok helyet takaríthat meg a modellben. Az alábbi képen egy Dátumszűrő használható csak az aktuális évre vonatkozó adatokat tartalmazó sorok beolvasására, kivéve azokat a múltbeli adatokat, amelyekre nincs szükség.

A Tábla importálása varázsló szűrőablaktáblája

Mi a teendő, ha szükségünk van az oszlopra; továbbra is csökkentheti a tárterület költségét?

Több további technikát is alkalmazhat, ha jobb választást szeretne végezni az oszlop tömörítéséhez. Ne feledje, hogy a tömörítést érintő oszlop egyetlen jellemzője az egyedi értékek száma. Ebből a szakaszból megtudhatja, hogy egyes oszlopok hogyan módosíthatók az egyedi értékek számának csökkentése érdekében.

A DateTime oszlopok módosítása

A sok esetben a DateTime oszlopok sok helyet foglalnak. Szerencsére az adattípusok tárolási követelményei többféle módon is csökkenthetők. A technikák eltérőek lehetnek attól függően, hogy hogyan használja az oszlopot, és hogy milyen kényelmet nyújt az SQL-lekérdezések létrehozásakor.

A DateTime oszlopok tartalmazzák a dátumok egy részét és időpontját. Amikor megkérdezi, hogy szüksége van-e egy oszlopra, kérdezzen rá többször a DateTime oszlopra:

  • Szükségem van az időrészre?

  • Szükségem van az időpontra az órák szintjén? perc? Másodperc? ezredmásodpercben?

  • Több datetime oszlopot is használhatok, mert szeretném kiszámítani a különbséget a közöttük, vagy csak az adatokat év, hónap, negyedév stb.

A fenti kérdések megválaszolásában a DateTime oszlop kezelésének lehetőségei határozzák meg.

A fenti megoldások mindegyikéhez szükség van egy SQL-lekérdezés módosítására. A lekérdezések módosításának megkönnyítéséhez célszerű legalább egy oszlopot kiszűrni minden táblázatban. Egy oszlop kiszűrésével a lekérdezések építését rövidített formátumban (SELECT *) változtathatja egy olyan SELECT utasításra, amely a teljesen minősített oszlopnevek, amelyek sokkal egyszerűbben módosíthatók.

Lássuk az Önnek létrehozott lekérdezéseket. A táblázat tulajdonságai párbeszédpanelen átválthat a lekérdezéstervező elemre, és megtekintheti az aktuális SQL-lekérdezést az egyes táblákhoz.

A PowerPivot ablakának menüszalagja a Táblázat tulajdonságai paranccsal

A táblázat tulajdonságai csoportban válassza a lekérdezéstervezőlehetőséget.

A Lekérdezésszerkesztő megnyitása a Táblázat tulajdonságainak szerkesztése párbeszédpanelről

A Lekérdezéstervező a tábla feltöltéséhez használt SQL-lekérdezést jeleníti meg. Ha az importálás során bármely oszlopot kiszűrt, a lekérdezés teljesen minősített oszlopnév-neveket tartalmaz:

Az adatok beolvasásához használt SQL-lekérdezés

Ha a táblázatot teljes egészében importálta, és nem ellenőriz egy oszlopot, vagy szűrőt alkalmaz, a lekérdezést "select * from" ("select * from") néven fogja látni, ami még nehezebben módosítható:

Az alapértelmezett, rövidebb szintaxist használó SQL-lekérdezés

Az SQL-lekérdezés módosítása

Most, hogy tudja, hogyan találhatja meg a lekérdezést, módosíthatja, hogy tovább csökkentse a modell méretét.

  1. Ha a pénznem vagy a decimális adatot tartalmazó oszlopok esetében nincs szüksége a tizedesjegyekre, használja ezt a szintaxist, ha nem szeretné, hogy megszabaduljunk a tizedesjegyek:

    "SELECT ROUND ([Decimal_column_name]; 0)... .”

    Ha szüksége van a Centre, de a centek Törtei nem, cserélje le a 0-t a 2 értékre. Ha negatív számokat használ, kerekítéssel elvégezheti az egységeket, a tízest, a több százat stb.

  2. Ha van egy dbo nevű datetime oszlopa. Nagytabla. [Dátum idő] és nincs szüksége az időrészre, a szintaxis segítségével megszabadíthatja az időt:

    "SELECT CAST (dbo. Nagytabla. [Dátum idő] as Date) AS [dátum idő]) "

  3. Ha van egy dbo nevű datetime oszlopa. Nagytabla. [Dátum idő], és a dátum-és időrészekre egyaránt szüksége van, az egyetlen datetime oszlop helyett több oszlopot is használhat az SQL-lekérdezésben:

    "SELECT CAST (dbo. Nagytabla. [Dátum idő] as Date) AS [dátum idő],

    DatePart (hh, dbo. Nagytabla. [Dátum idő]) as [dátum idő óra];

    DatePart (mi, dbo. Nagytabla. [Dátum idő]) as [dátum idő perc],

    DatePart (SS, dbo. Nagytabla. [Dátum idő]) as [dátum idő másodperc],

    DatePart (MS, dbo. Nagytabla. [Dátum idő]) as [dátum idő ezredmásodperc] "

    Annyi hasábot használhat, amennyit külön oszlopban kell az egyes részeknek mentenie.

  4. Ha órákra és percekre van szüksége, és egy időoszlopként szeretné őket használni, használhatja a következő szintaxist:

    Timefromparts (DatePart (hh; dbo. Nagytabla. [Dátum idő]), DatePart (mm, dbo. Nagytabla. [Dátum idő])) as [dátum idő Óraperc]

  5. Ha van két datetime oszlopa, például a [kezdési idő] és a [befejezési idő], és amit igazán szükséges, az időkülönbség a másodpercek között, az [időtartam] oszlopban távolítsa el a két oszlopot, és vegye fel a következőt:

    "DateDiff (SS; [kezdési dátum]; [záró dátum]) as [időtartam]"

    Ha az SS helyett az MS kulcsszót használja, az időtartamot ezredmásodpercben kapja meg

DAX számított mértékek használata oszlopok helyett

Ha korábban már felhasználta a DAX-kifejezés nyelvét, előfordulhat, hogy a számított oszlopok a modell egy másik oszlopán alapuló új oszlopok készítéséhez szükségesek, míg a kiszámított mértékek a modellben egyszer kerülnek meghatározásra, a kiértékelés azonban csak a Kimutatást vagy más jelentést.

Az egyik memória-mentési technikával a normál vagy számított oszlopokat számítja ki számított mértékekkel. A klasszikus példa az Egységár, a mennyiség és az összeg. Ha mind a három területe van, a helyet úgy takaríthatja meg, hogy csak kettőt tart, és a harmadikat a DAX segítségével számítja ki.

Melyik 2 oszlopot kell megtartania?

A fenti példában a mennyiség és az Egységár megtartása. Ez a két érték kevesebb, mint az összeg. Az összeg kiszámításához vegyen fel egy számított mértéket, például:

"Összesértékesítés: = SUMX (' Sales table ', ' Sales table ' [Egységár] * ' Sales table ' [mennyiség])"

A számított oszlopok olyanok, mint a normál hasábok, amelyekben mindkét terület felveszi a modellt. A számított mértékeket ezzel ellentétben a program a beúszáskor számítja ki, és nem vesz fel helyet.

Befejezés

Ebben a cikkben több olyan megközelítésről beszélünk, amelyek segíthetnek a további memória-hatékony modell kialakításában. Az adatmodell fájlméretének és memória-követelményei csökkentésének módja az oszlopok és sorok teljes számának csökkentése, valamint az egyes oszlopokban megjelenő egyedi értékek száma. Az alábbiakban felsorolunk néhány technikát:

  • Az oszlopok eltávolítása természetesen a legjobb módszer a hely mentésére. Döntse el, hogy mely oszlopokra van szüksége.

  • Időnként eltávolíthat egy oszlopot, és egy számított mértékkel helyettesítheti a táblázatban.

  • Előfordulhat, hogy nincs szükség a táblázat összes sorára. A sorok kiszűrése a tábla importálása varázslóban végezhető el.

  • Általánosságban elmondható, hogy egy oszlopban több különálló részre oszthatók meg, így csökkenthető az egyes oszlopok egyedi értékeinek száma. A részek mindegyike kis számú egyedi értékkel fog rendelkezni, és a kombinált összeg kisebb lesz az eredeti egyesített oszlopnál.

  • Sok esetben szüksége van arra is, hogy a kimutatásokban szeletelőként is használni lehessen a különálló részeket. Szükség esetén hierarchiákat hozhat létre a szakaszok, például órák, percek és másodpercek alapján.

  • Sok esetben az oszlopok több információt tartalmaznak, mint amennyire szüksége van rájuk. Tegyük fel például, hogy egy oszlop tizedesjegyeket tárol, de az összes tizedesjegy elrejtéséhez formázást alkalmazott. A kerekítés nagyon hatékony lehet egy numerikus oszlop méretének csökkentése érdekében.

Most, hogy elvégezte a munkafüzet méretének csökkentését, fontolja meg a munkafüzet méretének optimalizálását is. Elemzi az Excel-munkafüzetet, és ha lehetséges, tömöríti tovább. Töltse le a munkafüzet méretének optimalizálását.

Kapcsolódó hivatkozások

Az adatmodell specifikációja és korlátozásai

Munkafüzet mérete-optimalizáló letöltése

Power Pivot: Hatékony adatelemzés és adatmodellezés az Excelben

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×