Egy memóriahasználat szempontjából hatékony adatmodell létrehozása az Excel és a Power Pivot bővítmény segítségével

Megjegyzés:  Szeretnénk, ha minél gyorsabban hozzáférhetne a saját nyelvén íródott súgótartalmakhoz. Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Kérjük, hogy a lap alján írja meg, hogy hasznos volt-e az Ön számára az itt található információ. Az eredeti angol nyelvű cikket itt találja .

Az Excel 2013-as vagy újabb tartalmazó sorok millióit adatmodelleket készíthet, és hajtsa végre az alábbi modellek alapján hatékony adatelemzés. Adatmodellek hozhat létre, vagy a kimutatásokban, diagramok és Power View nézetbeli megjelenítés tetszőleges számú támogatási ugyanabban a munkafüzetben Power Pivot bővítmény nélkül.

Megjegyzés: Ez a cikk ismerteti az adatmodellek az Excel 2013-ban. Azonban az azonos adatmodellezése és jelent meg az Excel 2013 Power Pivot-szolgáltatások is vonatkozik az Excel 2016-ban. Az Excel e verziója között hatékony kis különbség van.

Bár nagyon nagy adatmodellek az Excel programban egyszerűen készíthet, több oka nem gombra. Első, nagyméretű levő táblázatok és oszlopok multitudes tartalmazó overkill a legtöbb elemzések, és ellenőrizze a lehető mezőlistát. Második, nagyméretű adatmodellek értékes memóriát, negatív hatással a többi alkalmazást, és a rendszer ugyanazokat az erőforrásokat használó jelentések használata Végül az Office 365-ben a SharePoint Online és az Excel Web App korlátozza a 10 MB-os Excel-fájl méretét. A munkafüzet-adatmodelljeihez tartalmazó sorok millióit futtatják a 10 MB-os korlát be igazán gyors. Lásd: az adatmodell specifikációja és korlátozásai.

Ebből a cikkből megismerheti, hogy miként építhet fel egy szigorúan szerkesztett modellt, amely egyszerűen használható és kevesebb memóriát használ. A hatékony modellek tervezésének megismerésére fordított idő minden modell létrehozása és használata esetén megtérül, akár Excel 2013, akár Office 365 SharePoint Online, Office Web Apps Server vagy SharePoint 2013 alkalmazásban tekinti meg azokat.

Fontolja meg a munkafüzetméret-optimalizáló is futtató. Az Excel-munkafüzet elemzi azt, és ha lehet, összetömöríti további. Töltse le a munkafüzetméret-optimalizáló.

Tartalom

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

Semmi sem veheti fel egy nem létező oszloppal az alacsony memóriahasználat terén

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

Szükségtelen oszlopok kizárása

Mi a teendő csak a szükséges sorok szűrése

Mi történik, ha szükséges; oszlopok azt is csökkenthető a helyigény?

Dátum és idő oszlopok módosítása

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

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

Melyik 2 oszlopot kell megtartani?

Elfogadásáról

Kapcsolódó hivatkozások

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

Az Excel adatmodelljei a memóriában található elemző motort használják az adatok tárolásához a memóriában. A motor hatékony tömörítési technikákat használva csökkenti a tárolási követelményeket, amíg az eredményül kapott halmaz az eredeti méret töredéke nem lesz.

Az adatmodellek várhatóan 7-10-szer kisebbek, mint ugyanazok az adatok az eredeti helyen. Ha például 7 MB adatot importált egy SQL Server adatbázisból, az Excelben az adatmodell 1 MB vagy akár kevesebb is lehet. A ténylegesen elért tömörítés mértéke elsődlegesen az egyes oszlopokban lévő egyedi értékektől függ. Minél több az egyedi érték, annál több memóriára van szükség a tárolásukhoz.

Miért is beszélünk a tömörítésről és az egyedi értékekről? Ennek oka, hogy a memóriahasználatot minimalizáló hatékony modellépítés a tömörítés maximalizálásáról szól, és a legkönnyebb módja annak, hogy megszabaduljon azoktól az oszlopoktól, amelyekre valójában nincs szüksége, főleg ha az oszlopok nagyszámú egyedi értéket tartalmaznak.

Megjegyzés: Az egyedi oszlopok tárolási követelményeiben igen nagy lehet az eltérés. Egyes esetekben előnyösebb, ha több oszlopa van kevés egyedi értékkel, mintha egyetlen oszlopa lenne nagyszámú egyedi értékkel. A dátum/idő optimalizálásával foglalkozó szakasz részletesen ismerteti ezt a technikát.

Semmi sem veheti fel a versenyt az alacsony memóriahasználat terén egy nem létező oszloppal

Memóriahasználat szempontjából az a leghatékonyabb oszlop, amelyet egyáltalán nem importált. Ha hatékony modellt szeretne építeni, minden egyes oszlop esetén vizsgálja meg, hogy az hozzájárul-e az elvégzendő elemzéshez. Ha nem, vagy nem biztos benne, hagyja ki. Később szükség szerint bármikor hozzáadhat új oszlopokat.

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

Az első példa adattárházból származó adatokra vonatkozik. Az adattárházban gyakran találhatók olyan adatbetöltési (ETL-) folyamatok objektumai, amelyek betöltik és frissítik az adattárház adatait. Az adatok betöltésekor oszlopok (például „létrehozás dátuma”, „frissítés dátuma” és „adatbetöltés futtatása”) jönnek létre. Az oszlopok egyikére sincs szükség a modellben, és az adatok importálásakor törölni kell a kijelölésüket.

A második példában a ténytábla importálásakor kimarad az elsődleges kulcsoszlop.

Számos táblában (többek között a ténytáblákban) elsődleges kulcs található. A legtöbb tábla (például az ügyfél-, alkalmazotti vagy értékesítési adatokat tartalmazók) esetén szükség van a tábla elsődleges kulcsára, hogy a kapcsolatok létrehozásához használhassa azt a modellben.

A ténytáblák különbözőek. Ezekben az elsődleges kulcs egyedileg azonosítja az egyes sorokat. Miközben normalizációs célokból szükséges, kevésbé hasznos az adatmodellekben, ahol csak azokra az oszlopokra van szükség, amelyeket elemzésre vagy a tábla kapcsolatainak létrehozására használ. Emiatt a ténytáblák importálásakor ne szerepeltesse azok elsődleges kulcsát. A ténytáblák elsődleges kulcsai rendkívül nagy helyet foglalnak el a modellben, miközben semmi előnyük nincs, mivel nem használhatók a kapcsolatok létrehozására.

Megjegyzés: Az adatbázisokban és a többdimenziós adatbázisokban a többnyire számadatot tartalmazó nagy táblák „ténytáblák” néven ismertek. A ténytáblák általában üzleti teljesítménnyel vagy tranzakciókkal kapcsolatos adatokat, többek között értékesítési és költségadatpontokat tartalmaznak, amelyeket szervezeti egységekbe, termékekbe, piaci szegmensekbe, földrajzi régiókba stb. összesítenek és rendeznek. A ténytáblákban található összes olyan oszlopot, amely üzleti adatokat tartalmaz, vagy amely más táblákban lévő adatokra mutató kereszthivatkozásokhoz használható, fel kell venni a modellbe az adatelemzés támogatásához. A kizárandó oszlop annak a ténytáblának az elsődlegeskulcs-oszlopa, amely kizárólag csak a ténytáblában található egyedi értékekből áll. Mivel a ténytáblák hatalmasak, a modellek hatékonyságának legbiztosabb elérése a sorok vagy oszlopok ténytáblából való kizárásával valósítható meg.

Szükségtelen oszlopok kizárása

Hatékony modellek csak azokat az oszlopokat, hogy valóban szüksége van a munkafüzet tartalmaz. Ha szeretné szabályozni, hogy melyik oszlopot tartalmaz a modell, használja a tábla importálása varázsló a PowerPivot beépülő modul az adatok importálása helyett a "-adatok importálása" párbeszédpanel, az Excel programban, be kell.

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

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

Minden tábla esetén kattinthat a Villámnézet és szűrés gombra, és kijelölheti a tábla azon részeit, amelyekre valóban szüksége van. Javasoljuk, hogy először törölje az összes oszlop bejelölését, majd döntse el, hogy kellenek-e az elemzéshez, és utána jelölje be a kívánt oszlopokat.

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

Kizárólag a szükséges sorok szűrése

A vállalati adatbázisok és adattárházak számos táblája tartalmaz hosszú időszak során felhalmozódott előzményadatot. Emellett előfordulhat, hogy a kérdéses táblázatok az adott elemzéshez szükségtelen üzleti területek adatait tartalmazzák.

A Tábla importálása varázslóval kiszűrheti az előzmény- és a nem kapcsolódó adatokat, így rengeteg helyet takaríthat meg a modellben. Az alább látható képen adatszűrő segít a kizárólag az aktuális év adatait tartalmazó sorok beolvasásában és a fölösleges előzményadatok kizárásában.

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

Csökkenthető a helyigény a szükséges oszlopok esetén?

Néhány további technikával az oszlopok jobban tömöríthetők. Ne feledje, hogy az oszlopok egyetlen jellemzője befolyásolja a tömörítést: az egyedi értékek száma. Ebben a részben megismerheti, hogy miként módosíthat néhány oszlopot az egyedi értékek számának a csökkentése érdekében.

Dátum/idő adatokat tartalmazó oszlopok módosítása

A dátum/idő adatokat tartalmazó oszlopok gyakran sok helyet foglalnak. Szerencsére számos módon csökkenhetők ennek az adattípusnak a tárolási követelményei. A technikák attól függően változnak, hogy miként használja az oszlopot, és hogyan hozza létre az SQL-lekérdezéseket.

A dátum/idő oszlopok egy dátumrészt és egy időt tartalmaznak. Amikor felteszi magának a kérdést, hogy szüksége van-e egy oszlopra, kérdezze meg többször ugyanazt egy dátum/idő oszlop esetében:

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

  • Milyen szinten van szükségem az időrészre: óra?, perc?, másodperc?, ezredmásodperc?

  • Több dátum/idő oszlopra van szükségem, mert a köztük lévő különbséget szeretném kiszámítani vagy csak összesíteni szeretném az adatokat év, hónap, negyedév stb. alapján?

E kérdésekre adott válaszai határozzák meg a dátum/idő oszlop kezelésének a beállításait.

E megoldások mindegyikéhez SQL-lekérdezést kell módosítani. A lekérdezések módosításának egyszerűsítéséhez legalább egy oszlopot ki kell szűrnie minden táblában. Oszlopok kiszűrésével a lekérdezés szerkezetét a rövidített formátumról (SELECT *) egy SELECT utasításra változtatja, amely sokkal könnyebben módosítható teljesen minősített oszlopneveket tartalmaz.

Vegyük szemügyre az Ön számára létrehozott lekérdezéseket. A Táblázat tulajdonságainak szerkesztése párbeszédpanelen átválthat a lekérdezésszerkesztőre, és megtekintheti az egyes táblázatok SQL-lekérdezését.

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

A Táblázat tulajdonságainak szerkesztése párbeszédpanelen válassza a Lekérdezésszerkesztő elemet.

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

A Lekérdezésszerkesztőben látható a táblázat kitöltéséhez használt SQL-lekérdezés. Ha az importálás során bármilyen oszlopot kiszűrt, a lekérdezés csak teljesen minősített oszlopneveket tartalmaz:

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

Ezzel szemben, ha teljes egészében importálta a táblát, és nem zárt ki egyetlen oszlopot és nem alkalmazott semmilyen szűrőt sem, a lekérdezés “SELECT * FROM ” formátumban jelenik meg, amelynek a módosítása sokkal bonyolultabb:

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

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

A lekérdezések keresésének elsajátítása után módosíthatja is azt, hogy tovább csökkentse a modell méretét.

  1. Ha pénznemet vagy decimális adatokat tartalmazó oszlopok esetén nincs szüksége a tizedesjegyekre, használja az alábbi szintaxist:

    “SELECT ROUND([Tizedes_oszlop_neve],0)… .”

    Ha szüksége van a fillérekre, a törtrészeire azonban nem, helyettesítse a 0 értéket 2-vel. Negatív számok használata esetén kerekíthet egységekre, tízes, százas stb. jegyre.

  2. Ha egy dbo.Nagytabla.[Dátum idő] oszloppal rendelkezik, és nincs szüksége az időrészre, a következő szintaxissal kizárhatja azt:

    “SELECT CAST (dbo.Nagytabla.[Dátum idő] as date) AS [Dátum idő]) “

  3. Ha egy dbo.Nagytabla.[Dátum idő] nevű oszloppal rendelkezik, és szüksége van a dátum- és az időrészre is, az SQL lekérdezésben az egyetlen Dátum/idő oszlop helyett használjon több oszlopot:

    “SELECT CAST (dbo.Nagytabla.[Dátum idő] as date ) AS [Dátum idő],

    datepart(hh, dbo.Nagytabla.[Dátum idő]) date [Dátum idő óra],

    datepart(mi, dbo.Nagytabla.[Dátum idő]) date [Dátum idő perc],

    datepart(ss, dbo.Nagytabla.[Dátum idő]) date [Dátum idő másodperc],

    datepart(ms, dbo..Nagytabla.[Dátum idő]) date [Dátum idő ezredmásodperc]”

    Használjon annyi oszlopot, amennyire szüksége van az egyes részek külön oszlopokban való tárolásához.

  4. Ha órákra és percekre is szüksége van, és egyetlen időoszlopban szeretné őket használni, alkalmazza a következő szintaxist:

    Timefromparts(datepart(hh dbo.Nagytabla.[Dátum idő]), datepart(mm, dbo.Nagytabla.[Dátum idő])) date [Dátum idő ÓraPerc]

  5. Ha két dátum/idő oszlopa van (például [Kezdési idő] és [Befejezési idő]), és valójában a köztük lévő időkülönbségre van szüksége másodpercben [Időtartam] nevű oszlopként, távolítsa el a listából mindkét oszlopot, és adja hozzá a következőt:

    “datediff(ss,[Kezdési idő],[Befejezési idő]) as [Időtartam]”

    Ha az ss kulcsszó helyett ms kulcsszót használ, az időtartamot ezredmásodpercben kapja meg

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

Ha korábban már használta a DAX kifejezésnyelvet, bizonyára tudja, hogy számított oszlopokat használ az új oszlopok származtatásához a modellben lévő egyéb oszlopok alapján, míg a számított mértékek definiálása egyszer történik meg a modellben, a kiértékelésük azonban csak a kimutatásban vagy más jelentésben való használatukkor megy végbe.

Az egyik memóriát megtakarító technika a szabályos vagy számított oszlopok helyettesítése számított mértékekkel. Ennek klasszikus példája az Egységár, Mennyiség és Összesen. Ha mindhárommal rendelkezik, helyet takaríthat meg azzal, ha csak kettőt tart meg, a harmadikat pedig kiszámítja a DAX használatával.

Melyik 2 oszlopot kell megtartani?

A fenti példában tartsa meg a Mennyiség és az Egységár oszlopot. Ezekben kevesebb érték található, mint az Összesen oszlopban. Az Összesen oszlop kiszámításához vegyen fel egy számított mértéket például az alábbi módon:

“ÖsszesÉrtékesítés:=sumx(‘Értékesítési táblázat’,’Értékesítési táblázat’[Egységár]*’Értékesítési táblázat’[Mennyiség])”

A számított oszlopok a szabályos oszlopokra hasonlítanak abból a szempontból, hogy mindkettő helyet foglal el a modellben. Ezzel szemben a számított mértékek számítása menet közben történik, és nem foglalnak helyet.

Befejezés

Ebben a cikkben megismerhetett néhány módszert, amelynek a segítségével a memóriahasználat szempontjából sokkal hatékonyabb modellt építhet fel. A fájlméret és az adatmodellek memóriaigényének csökkentésének módja az oszlopok és sorok teljes számának és az egyes oszlopokban megjelenő egyedi értékek számának a csökkentése. Az alábbi technikákat mutattuk be:

  • Az oszlopok eltávolítása a hely megtakarításának legjobb módja. Döntse el, hogy mely oszlopokra van valóban szüksége.

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

  • Előfordulhat, hogy nincs minden sorra szüksége a táblázatban. A Tábla importálása varázslóval szűrhet ki sorokat.

  • Általában jó módszer, ha egyetlen oszlopot több külön részre oszt fel az egyedi értékek számának csökkentéséhez az oszlopban. Minden egyes részben kisszámú egyedi érték lesz, és az egyesített összeg kisebb lesz, mint az eredeti oszlop.

  • Sok esetben szükség van a külön részek szeletelőkként való használatára is a jelentésekben. Ha lehetséges, hierarchiákat is létrehozhat az egyes részekből (például Óra, Perc, Másodperc).

  • Az oszlopok sokszor a szükségesnél több adatot tartalmaznak. Tételezzük fel például, hogy egy oszlop decimális számokat tartalmaz, de formázással elrejtette az összeset. A kerekítés nagyon hatásos lehet a numerikus oszlopok méretének a csökkentésében.

A munkafüzet méretének csökkentése érdekében is, de elemeket, érdemes megfontolni a munkafüzetméret-optimalizáló is futtató. Az Excel-munkafüzet elemzi azt, és ha lehet, összetömöríti további. Töltse le a munkafüzetméret-optimalizáló.

Kapcsolódó hivatkozások

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

Munkafüzetméret-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.

×