Vytvorenie pamäťového dátového modelu pomocou Excelu a doplnku Power Pivot

V Exceli 2013 alebo novšom môžete vytvoriť údajové modely, ktoré obsahujú milióny riadkov, a potom vykonať účinnú analýzu údajov v porovnaní s týmito modelmi. Dátové modely je možné vytvoriť pomocou doplnku PowerPivot alebo bez neho na podporu ľubovoľného počtu kontingenčných tabuliek, grafov a vizualizácií funkcie Power View v tom istom zošite.

Poznámka: V tomto článku sa popisujú dátové modely v Exceli 2013. Rovnaké funkcie na modelovanie údajov a funkcie Power Pivot zavedené v Exceli 2013 sa však tiež týkajú Excelu 2016. Medzi týmito verziami Excelu je skutočne malý rozdiel.

Napriek tomu, že v Exceli môžete jednoducho vytvárať obrovské dátové modely, existuje niekoľko dôvodov, prečo nie. Prvé veľké modely, ktoré obsahujú zástupné množstvá tabuliek a stĺpcov, sú pri väčšine analýz zbytočne a vytvárajú ťažkopádny zoznam polí. Po druhé, veľké modely využívajú cennú pamäť, čo negatívne ovplyvňuje iné aplikácie a zostavy, ktoré zdieľajú rovnaké systémové zdroje. Nakoniec v Office 365 v SharePointe Online aj v aplikácii Excel Web App obmedzte veľkosť excelového súboru na 10 MB. V prípade údajových modelov zošita, ktoré obsahujú milióny riadkov, prejdete do limitu 10 MB veľmi rýchlo. Pozrite si tému Špecifikácia a obmedzenia dátového modelu.

V tomto článku sa naučíte, ako vytvoriť pevne vytvorený model, s ktorým pracujete, a menej pamäte. Postupom času sa naučíte najvhodnejšie postupy pri efektívnom návrhu modelu vyplatíte si cestu pre akýkoľvek model, ktorý vytvoríte a použijete, či ho zobrazujete v Exceli 2013, Office 365 SharePointe Online, na serveri Office Web Apps alebo v SharePointe 2013.

Zvážte tiež spustenie Optimalizátora veľkosti zošita. Vykoná analýzu excelového zošita a ak je to možné, skomprimuje ho. Stiahnite si optimalizáciu veľkosti zošita.

Obsah tohto článku

Kompresné pomery a nástroj na analýzu v pamäti

Neexistujúci stĺpec s nedostatkom pamäte nie je ničím neexistujúcim

Dva príklady stĺpcov, ktoré by mali byť vždy vylúčené

Vylúčenie nepotrebných stĺpcov

Čo je filtrovanie len potrebných riadkov?

Čo ak potrebujeme stĺpec, môžeme stále znížiť náklady na vesmír?

Úprava stĺpcov dátumu a času

Úprava SQL dotazu

Použitie vypočítavaných mier DAX namiesto stĺpcov

Ktoré 2 stĺpce by ste mali uchovať?

Záver

Súvisiace prepojenia

Kompresné pomery a nástroj na analýzu v pamäti

Dátové modely v Exceli používajú nástroj na analýzu v pamäti na ukladanie údajov v pamäti. Motor implementuje výkonné kompresné techniky s cieľom znížiť požiadavky na ukladací priestor a zmenšiť množinu výsledkov, kým nie je zlomok pôvodnej veľkosti.

V priemere môžete očakávať, že dátový model bude 7 až 10-krát menší ako tie isté údaje na mieste jeho pôvodu. Ak napríklad importujete 7 MB údajov z databázy servera SQL Server, model údajov v programe Excel môže byť jednoducho 1 MB alebo menej. Stupeň kompresie skutočne dosiahnutý závisí predovšetkým od počtu jedinečných hodnôt v každom stĺpci. Čím viac jedinečných hodnôt, tým väčšia je pamäť potrebná na ich uloženie.

Prečo hovoríme o kompresii a jedinečných hodnotách? Keďže vytvorenie účinného modelu, ktorý minimalizuje využitie pamäte, je všetko o maximalizácii kompresie a najjednoduchším spôsobom, ako to urobiť, je odstrániť všetky stĺpce, ktoré naozaj nepotrebujete, najmä ak tieto stĺpce obsahujú veľký počet jedinečných hodnôt.

Poznámka:  Rozdiely v požiadavkách na ukladanie jednotlivých stĺpcov môžu byť obrovské. V niektorých prípadoch je lepšie mať viacero stĺpcov s malým počtom jedinečných hodnôt namiesto jedného stĺpca s vysokým počtom jedinečných hodnôt. Časť o optimalizácii DateTime sa podrobne vzťahuje na túto techniku.

Neexistujúci stĺpec s nedostatkom pamäte nie je ničím neexistujúcim

Najvhodnejší stĺpec s pamäťou je ten, ktorý ste nikdy neimportovali na prvom mieste. Ak chcete vytvoriť účinný model, pozrite sa do každého stĺpca a položte si otázku, či prispieva k analýze, ktorú chcete vykonať. Ak to tak nie je, alebo si nie ste istí, nechajte to. Nové stĺpce môžete vždy pridať neskôr, ak ich potrebujete.

Dva príklady stĺpcov, ktoré by mali byť vždy vylúčené

Prvý príklad sa vzťahuje na údaje pochádzajúce z údajového skladu. V dátovom sklade je bežné nájsť artefakty procesov ETL, ktoré načítavajú a obnovujú údaje v sklade. Pri načítaní údajov sa vytvoria stĺpce ako "Create date", "Update date" a "ETL Run". V modeli nie je potrebný žiadny z týchto stĺpcov a pri importovaní údajov by sa mal zrušiť ich výber.

Druhý príklad zahŕňa vynechanie stĺpca primárneho kľúča pri importovaní tabuľky faktov.

Mnohé tabuľky vrátane tabuliek faktov obsahujú hlavné kľúče. Pre väčšinu tabuliek, napríklad tie, ktoré obsahujú údaje o zákazníkoch, zamestnancoch alebo predajných údajoch, budete chcieť použiť hlavný kľúč tabuľky, aby ste ho mohli použiť na vytvorenie vzťahov v modeli.

Tabuľky faktov sú odlišné. V tabuľke faktov sa hlavný kľúč používa na jednoznačnú identifikáciu jednotlivých riadkov. Ak je to potrebné na účely normalizácie, je menej užitočné v dátovom modeli, kde sa majú použiť len tie stĺpce, ktoré sa používajú na analýzu alebo na vytvorenie vzťahov tabuliek. Z tohto dôvodu pri importovaní z tabuľky faktov Nezahŕňajte jeho hlavný kľúč. Primárne kľúče v tabuľke faktov spotrebujú obrovské množstvo priestoru v modeli, ale neposkytujú žiadne výhody, pretože sa nemôžu použiť na vytváranie vzťahov.

Poznámka:  V dátových skladoch a viacrozmerných databázach sa veľké tabuľky zložené z prevažne číselných údajov často označujú ako "tabuľky faktov". Tabuľky faktov zvyčajne zahŕňajú údaje o výkone podniku alebo transakčné údaje, ako sú napríklad údaje o predaji a nákladných bodoch, ktoré sú agregované a zarovnané na organizačné jednotky, produkty, segmenty trhu, geografické oblasti atď. Všetky stĺpce v tabuľke faktov, ktoré obsahujú pracovné údaje alebo ktoré možno použiť na krížové odkazy na údaje uložené v iných tabuľkách, by mali byť zahrnuté do modelu na podporu analýzy údajov. Stĺpec, ktorý chcete vylúčiť, je stĺpcom primárneho kľúča tabuľky faktov, ktorý pozostáva z jedinečných hodnôt, ktoré existujú iba v tabuľke faktov a nikde inde. Keďže tabuľky faktov sú tak obrovské, niektoré z najväčších ziskov v modelovej efektivite sú odvodené z vylúčenia riadkov alebo stĺpcov z tabuliek faktov.

Vylúčenie nepotrebných stĺpcov

Účinné modely obsahujú len tie stĺpce, ktoré budete v zošite skutočne potrebovať. Ak chcete určiť, ktoré stĺpce sú zahrnuté v modeli, budete musieť použiť Sprievodcu importom tabuľky v doplnku Power Pivot na importovanie údajov , a nie dialógové okno Import údajov v Exceli.

Pri spustení Sprievodcu importom tabuľky vyberte tabuľky, ktoré sa majú importovať.

Sprievodca importom tabuliek v doplnku PowerPivot

Pre každú tabuľku môžete kliknúť na tlačidlo Ukážka & filtra a vybrať časti tabuľky, ktoré naozaj potrebujete. Odporúčame najprv zrušiť začiarknutie všetkých stĺpcov a potom postupovať, ak chcete skontrolovať požadované stĺpce, a to po zvážení toho, či sa vyžadujú na analýzu.

Tabla Ukážka v Sprievodcovi importom tabuliek

Čo je filtrovanie len potrebných riadkov?

Mnohé tabuľky v podnikových databázach a dátových skladoch obsahujú historické údaje, ktoré sa nahromadili počas dlhého časového obdobia. Okrem toho možno zistíte, že tabuľky, ktoré vás zaujímajú, obsahujú informácie o oblastiach podniku, ktoré nie sú potrebné pre konkrétnu analýzu.

Pomocou Sprievodcu importom tabuľky môžete filtrovať historické alebo nesúvisiace údaje, a tak ušetriť veľa miesta v modeli. Na nasledujúcom obrázku sa použije filter dátumu na načítanie iba tých riadkov, ktoré obsahujú údaje za aktuálny rok, s výnimkou historických údajov, ktoré nie sú potrebné.

Tabla Filter v Sprievodcovi importom tabuliek

Čo ak potrebujeme stĺpec, môžeme stále znížiť náklady na vesmír?

Existuje niekoľko ďalších techník, ktoré môžete použiť na vytvorenie stĺpca, ktorý je lepším kandidátom na kompresiu. Nezabudnite, že jedinou vlastnosťou stĺpca, ktorý ovplyvňuje kompresiu, je počet jedinečných hodnôt. V tejto časti sa naučíte, ako možno niektoré stĺpce upraviť tak, aby sa znížil počet jedinečných hodnôt.

Úprava stĺpcov dátumu a času

V mnohých prípadoch sa v stĺpcoch DateTime zaberajú veľa miesta. Našťastie existuje niekoľko spôsobov, ako znížiť požiadavky na ukladací priestor pre tento typ údajov. Tieto postupy sa budú líšiť v závislosti od spôsobu používania stĺpca a úrovne pohodlia pri vytváraní dotazov SQL.

V stĺpcoch DateTime je k dispozícii dátumová časť a časový interval. Ak sa spýtate sami seba, či potrebujete stĺpec, položte rovnakú otázku viackrát na stĺpec datetime:

  • Potrebujem časovú časť?

  • Potrebujem časovú časť na úrovni hodín? minút? Sekúnd? milisekundách?

  • Mám viacero stĺpcov DateTime, pretože chcem vypočítať rozdiel medzi nimi, alebo len na agregovanie údajov podľa roku, mesiaca, štvrťroka atď.

Spôsob, akým odpovedáte na každú z týchto otázok, určuje vaše možnosti riešenia v stĺpci DateTime.

Všetky tieto riešenia vyžadujú úpravu dotazu SQL. Ak chcete upraviť dotaz jednoduchšie, mali by ste v každej tabuľke filtrovať aspoň jeden stĺpec. Filtrovaním stĺpca zmeníte konštrukciu dotazu z skráteného formátu (SELECT *) na príkaz SELECT, ktorý obsahuje plne kvalifikovaní názvy stĺpcov, ktoré sa oveľa ľahšie upravujú.

Pozrime sa na dotazy, ktoré ste vytvorili. V dialógovom okne Vlastnosti tabuľky môžete prejsť na Editor dotazov a zobraziť aktuálny dotaz SQL pre každú tabuľku.

Pás s nástrojmi v okne doplnku PowerPivot s príkazom Vlastnosti tabuľky

Z vlastností tabuľky vyberte položku Editor dotazov.

Otvorenie Editora dotazov z dialógového okna Vlastnosti tabuľky

V editore dotazov sa zobrazuje SQL dotaz použitý na vyplnenie tabuľky. Ak ste v priebehu importu odfiltroval ktorýkoľvek stĺpec, váš dotaz obsahuje plne kvalifikovaní názvy stĺpcov:

SQL dotaz použitý na načítanie údajov

Na rozdiel od toho, ak ste importovali tabuľku v plnom rozsahu bez toho, aby ste museli začiarknuť ktorýkoľvek stĺpec alebo použiť filter, zobrazí sa dotaz ako "Select * from" (vybrať * z), ktorý sa bude ťažšie upravovať:

SQL dotaz s predvolenou skrátenou syntaxou

Úprava SQL dotazu

Teraz, keď viete, ako nájsť dotaz, môžete ho upraviť, aby sa ďalej zmenšila veľkosť modelu.

  1. Ak v stĺpcoch obsahujúcich meny alebo desatinné údaje nepotrebujete desatinné čísla, použite túto syntax na odstránenie desatinných miest:

    Vyberte položku ROUND ([Decimal_column_name]; 0)... .”

    Ak potrebujete centy, ale nie zlomky centov, nahraďte hodnotu 0 číslom 2. Ak používate záporné čísla, môžete zaokrúhliť na jednotky, desiatky, stovky atď.

  2. Ak máte stĺpec DateTime s názvom dbo. Bigtable. [Dátum a času] a nepotrebujete časovú časť, použite syntax, aby ste sa zbavili času:

    Vyberte položku OBSADEnie (dbo. Bigtable. [Dátumový dátum] ako dátum) ako [dátumový termín])

  3. Ak máte stĺpec DateTime s názvom dbo. Bigtable. [Date Time] a potrebujete aj dátumové aj časové časti, namiesto jedného časového stĺpca použite viacero stĺpcov v dotaze SQL:

    Vyberte položku OBSADEnie (dbo. Bigtable. [Dátumový dátum] ako dátum) ako [dátumové obdobie],

    DatePart (hh, dbo. Bigtable. [Date Time]) ako [dátumové časové hodiny],

    DatePart (mi, dbo. Bigtable. [Date Time]) ako [dátumové časové minúty],

    DatePart (SS, dbo. Bigtable. [Date Time]) ako [Date Time seconds],

    DatePart (MS, dbo. Bigtable. [Date Time]) ako [dátumové obdobie milisekundy] "

    Použite toľko stĺpcov, koľko potrebujete na uloženie každej časti v samostatných stĺpcoch.

  4. Ak potrebujete hodiny a minúty a preferujete ich spoločne ako jeden časový stĺpec, môžete použiť syntax:

    Timefromparts (DatePart (hh; dbo. Bigtable. [Date Time]), DatePart (mm, dbo. Bigtable. [Date Time])) ako [Date Time HourMinute]

  5. Ak máte dva stĺpce DateTime, napríklad [čas začiatku] a [čas ukončenia], a to, čo naozaj potrebujete, je časový rozdiel medzi nimi v sekundách ako stĺpec s názvom [Trvanie], odstráňte obidva stĺpce zo zoznamu a pridajte:

    "DateDiff (SS; [Počiatočný dátum], [koncový dátum]) ako [Trvanie]"

    Ak použijete kľúčové slovo MS namiesto SS, dostanete trvanie v milisekundách.

Použitie vypočítavaných mier DAX namiesto stĺpcov

Ak ste predtým pracovali s jazykom výrazu DAX, možno už viete, že vypočítavané stĺpce sa používajú na odvodenie nových stĺpcov na základe iného stĺpca v modeli, zatiaľ čo Vypočítavané miery sú definované raz v modeli, ale vyhodnotené iba v prípade, že sa používajú v Kontingenčná tabuľka alebo iná zostava.

Jednou z techník na úsporu pamäte je nahradenie bežných alebo vypočítavaných stĺpcov vypočítavanými mierami. Klasický príklad je jednotková cena, množstvo a súčet. Ak máte všetky tri, môžete ušetriť miesto tým, že zachová len dva a vypočítavate tretiu pomocou jazyka DAX.

Ktoré 2 stĺpce by ste mali uchovať?

Vo vyššie uvedenom príklade Udržujte množstvo a jednotkovú cenu. Tieto dva majú menej hodnôt ako súčet. Ak chcete vypočítať súčet, pridajte vypočítavané mierky, napríklad:

"Celkovýpredaj: = sumx (' predajná tabuľka ', ' predajná tabuľka ' [jednotková cena] * ' predajná tabuľka ' [množstvo])"

Vypočítavané stĺpce sú ako bežné stĺpce v oboch zaberajú miesto v modeli. Vypočítavané miery sa naopak vypočítavajú pri lete a nezaberajú miesto.

Záver

V tomto článku sme hovorili o niekoľkých prístupoch, ktoré vám môžu pomôcť vytvoriť efektívnejší model s pamäťou. Spôsob, ako zmenšiť veľkosť súboru a požiadavky na pamäť dátového modelu, je znížiť celkový počet stĺpcov a riadkov a počet jedinečných hodnôt, ktoré sa zobrazujú v jednotlivých stĺpcoch. Tu je niekoľko techník, na ktoré sa vzťahuje:

  • Odstránenie stĺpcov je samozrejme najlepším spôsobom, ako ušetriť miesto. Vyberte stĺpce, ktoré naozaj potrebujete.

  • Niekedy môžete odstrániť stĺpec a nahradiť ho vypočítavaným ukazovateľom v tabuľke.

  • Nemusíte potrebovať všetky riadky v tabuľke. Riadky môžete filtrovať v Sprievodcovi importom tabuľky.

  • Vo všeobecnosti platí, že rozbitie jedného stĺpca na viaceré odlišné časti je vhodným spôsobom na zmenšenie počtu jedinečných hodnôt v stĺpci. Každá z častí bude mať malý počet jedinečných hodnôt a kombinovaný súčet bude menší ako pôvodný jednotný stĺpec.

  • V mnohých prípadoch potrebujete tiež odlišné časti, ktoré sa majú použiť ako rýchle filtre v zostavách. Ak je to potrebné, môžete vytvoriť hierarchie z častí, ako napríklad hodiny, minúty a sekundy.

  • Mnohokrát, stĺpce obsahujú viac informácií, ako potrebujete. Predpokladajme napríklad, že stĺpec obsahuje desatinné čísla, ale ste použili formátovanie na skrytie všetkých desatinných miest. Zaokrúhľovanie môže byť veľmi účinné pri znižovaní veľkosti číselného stĺpca.

Teraz, keď ste urobili to, čo môžete zmenšiť veľkosť zošita, Zvážte tiež spustenie nástroja na optimalizáciu veľkosti zošita. Vykoná analýzu excelového zošita a ak je to možné, skomprimuje ho. Stiahnite si optimalizáciu veľkosti zošita.

Súvisiace prepojenia

Špecifikácie a obmedzenia dátového modelu

Stiahnutie nástroja na optimalizáciu veľkosti zošita

PowerPivot: účinná analýza údajov a modelovanie údajov v Exceli

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×