V Excel verzie 2013 alebo novšej môžete vytvoriť dátové modely obsahujúce milióny riadkov a potom s týmito modelmi vykonávať výkonnú analýzu údajov. Dátové modely možno vytvoriť pomocou doplnku Power Pivot alebo bez neho na podporu ľubovoľného počtu kontingenčných tabuliek, grafov a vizualizácií Power View v tom istom zošite.
Poznámka: Tento článok popisuje dátové modely vo Excel 2013. Rovnaké funkcie modelovania údajov a doplnku Power Pivot uvedené vo Excel 2013 sa však vzťahujú aj na Excel 2016. Medzi týmito verziami balíka Excel.
Hoci v Exceli môžete jednoducho vytvárať veľké dátové Excel, existuje niekoľko dôvodov, prečo to tak nie je. V prvom rade sú veľké modely, ktoré obsahujú množstvo tabuliek a stĺpcov, pre väčšinu analýz nadácia, a preto si vytvorte ťažkopádny zoznam polí. Potom veľké modely využívajú cennú pamäť a negatívne ovplyvňujú ostatné aplikácie a zostavy, ktoré zdieľajú rovnaké systémové zdroje. V Microsoft 365 SharePoint Online Excel Web App veľkosť súboru Excel na 10 MB. V prípade dátových modelov zošitov, ktoré obsahujú milióny riadkov, sa pomerne rýchlo zobrazí limit 10 MB. Pozrite si časť Špecifikácie a obmedzenia dátového modelu.
V tomto článku sa naučíte, ako vytvoriť úzko vytvorený model, ktorý sa jednoduchšie pracuje a používa menej pamäte. Ak sa oboznámite s najvhodnejšími postupmi pri efektívnom návrhu modelu, každý model, ktorý vytvoríte a použijete, sa oplatí používať bez ohľadu na to, či ho zobrazujete v balíku Excel 2013, Microsoft 365 SharePoint Online, na Office Web Apps Serveri alebo v balíku SharePoint 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átor veľkosti zošita.
Obsah tohto článku
Compression ratios and the in-memory analytics engine
Dátové modely v Excel používať nástroj na analýzu v pamäti na ukladanie údajov v pamäti. Tento nástroj implementuje výkonné spôsoby kompresie s cieľom znížiť požiadavky na ukladací priestor a zmenšovať množinu výsledkov, až kým nebude zlomkom pôvodnej veľkosti.
V priemere môžete očakávať, že dátový model bude o 7 až 10-krát menší ako tie isté údaje v mieste pôvodu. Ak napríklad importujete 7 MB údajov z SQL Server databázy, dátový model v Excel môže mať veľkosť 1 MB alebo menej. Stupeň dosiahnutého stupňa kompresie závisí najmä od počtu jedinečných hodnôt v jednotlivých stĺpcoch. Ak je viac jedinečných hodnôt, tým viac pamäte sa vyžaduje na ich uloženie.
Prečo hovorí o kompresii a jedinečných hodnotách? Keďže vytvorenie efektívneho modelu, ktorý minimalizuje využitie pamäte, ide o maximalizáciu kompresie a najjednoduchší spôsob, ako to urobiť, je odstrániť všetky nepotrebné stĺpce, a to najmä vtedy, ak tieto stĺpce obsahujú veľký počet jedinečných hodnôt.
Poznámka: Rozdiely v požiadavkách na ukladací priestor jednotlivých stĺpcov môžu byť veľké. V niektorých prípadoch je lepšie mať viacero stĺpcov s nízkym počtom jedinečných hodnôt namiesto jedného stĺpca s vysokým počtom jedinečných hodnôt. Časť optimalizácií dátumu a času podrobne popisuje túto techniku.
Pri nízkom používaní pamäte nič neprerazilo neexistujúci stĺpec
Najefektívnejší stĺpec je ten, ktorý ste na prvom mieste nikdy neimportovaní. Ak chcete vytvoriť efektívny model, pozrite sa na každý stĺpec a spýtajte sa, či prispieva k analýze, ktorú chcete vykonať. Ak to tak nie je alebo si nie ste istí, môžete to nechať tak. Ak budete potrebovať nové stĺpce, môžete ich vždy pridať neskôr.
Dva príklady stĺpcov, ktoré by sa mali vždy vylúčiť
Prvý príklad sa týka údajov pochádzajúcich zo skladu údajov. V sklade údajov sa často nájsť artefakty procesov ETL, ktoré načítavajú a obnovú údaje v sklade. Pri načítaní údajov sa vytvoria stĺpce ako dátum vytvorenia, dátum aktualizácie a spustenie ETL. Žiadny z týchto stĺpcov nie je v modeli potrebný a pri importe údajov by sa mal zrušiť jeho výber.
Druhý príklad zahŕňa vynechanie stĺpca primárneho kľúča pri importe tabuľky faktov.
Mnohé tabuľky vrátane tabuliek faktov majú primárne kľúče. Vo väčšine tabuliek, napríklad v prípade tabuliek, ktoré obsahujú údaje o zákazníkoch, zamestnancoch alebo predaji, budete potrebovať primárny kľúč tabuľky, aby ste ho mohli použiť na vytvorenie vzťahov v modeli.
Tabuľky faktov sú rôzne. V tabuľke faktov sa primárny kľúč používa na jednoznačnú identifikáciu každého riadka. Aj keď je to potrebné na účely normalizácie, v dátovom modeli je menej užitočné, ak chcete použiť iba stĺpce používané na analýzu alebo vytvoriť vzťahy medzi tabuľkami. Z tohto dôvodu nezahŕňajú pri importe z tabuľky faktov jeho primárny kľúč. Primárne kľúče v tabuľke faktov spotrebúvajú nesmierne množstvo priestoru v modeli, no neposkytujú žiadnu výhodu, pretože ich nemožno použiť na vytvorenie vzťahov.
Poznámka: V skladoch údajov a vo viacrozmerných databázach sa veľké tabuľky, ktoré pozostávajú väčšinou z číselných údajov, často označujú ako tabuľky faktov. Tabuľky faktov zvyčajne obsahujú údaje o výkone podniku alebo transakcii, ako sú napríklad údajové body predaja a nákladové body, ktoré sú spojené a zarovnané s organizačnými jednotkami, produktmi, segmentmi trhu, geografickými oblasťami atď. Všetky stĺpce v tabuľke faktov, ktoré obsahujú obchodné údaje alebo ktoré možno použiť na krížové referenčné údaje uložené v iných tabuľkách, by sa mali zahrnúť do modelu na podporu analýzy údajov. Stĺpec, ktorý chcete vylúčiť, je stĺpec primárneho kľúča tabuľky faktov, ktorý obsahuje jedinečné hodnoty, ktoré existujú len v tabuľke faktov a nikde inde. Keďže tabuľky faktov sú také veľké, určité z najväčších nárastov efektívnosti modelu sa odvodia z vylúčenia riadkov alebo stĺpcov z tabuliek faktov.
Ako vylúčiť nepotrebné stĺpce
Efektívne modely obsahujú iba tie stĺpce, ktoré budete v zošite potrebovať. Ak chcete ovládať stĺpce zahrnuté v modeli, na importovanie údajov namiesto dialógového okna Import údajov v dialógovom okne Import údajov v doplnku Excel budete musieť použiť Sprievodcu importom tabuľky Excel.
Po spustení Sprievodcu importom tabuľky vyberiete tabuľky, ktoré sa majú importovať.
Pre každú tabuľku môžete kliknúť na tlačidlo & Zobraziť ukážku a vybrať časti tabuľky, ktoré naozaj potrebujete. Odporúčame najprv zrušiť začiarknutie všetkých stĺpcov a potom pokračovať v kontrole požadovaných stĺpcov po zvážení, či sú potrebné na analýzu.
A čo filtrovanie iba potrebných riadkov?
Mnohé tabuľky v podnikových databázach a skladoch údajov obsahujú historické údaje akumulované za dlhé časové obdobia. Okrem toho môžete zistiť, že tabuľky, ktoré vás zaujímajú, obsahujú informácie o oblastiach podniku, ktoré nie sú potrebné na vašu konkrétnu analýzu.
Pomocou Sprievodcu importom tabuľky môžete filtrovať historické alebo nesúvisiace údaje, a tým ušetriť veľa miesta v modeli. Na nasledujúcom obrázku sa používa filter dátumu na načítanie iba riadkov obsahujúcich údaje za aktuálny rok s výnimkou historických údajov, ktoré nebudú potrebné.
Čo ak budeme potrebovať stĺpec; môžeme stále znížiť náklady na priestor?
Existuje niekoľko ďalších techník, ktoré môžete použiť na to, aby stĺpec bol lepším kandidátom na kompresiu. Pamätajte, že jedinou charakteristikou stĺpca, ktorá ovplyvňuje kompresiu, je počet jedinečných hodnôt. V tejto časti sa dozviete, ako je možné upraviť niektoré stĺpce tak, aby sa znížil počet jedinečných hodnôt.
Úprava stĺpcov dátumu a času
Stĺpce dátumu a času v mnohých prípadoch zaberú veľa miesta. Našťastie existuje niekoľko spôsobov, ako znížiť požiadavky na ukladací priestor tohto typu údajov. Tieto metódy sa budú líšiť v závislosti od toho, ako stĺpec používate, a vašej úrovne pohodlia pri SQL dotazov.
Stĺpce dátumu a času zahŕňajú časť s dátumom a čas. Keď sa pýtate, či potrebujete stĺpec, položte rovnakú otázku viackrát v prípade stĺpca DátumaČas:
-
Potrebujem čas?
-
Potrebujem časový rozsah na úrovni hodín? , minúty? , Sekundy? , milisekundy?
-
Mám viacero stĺpcov dátumu a času, pretože chcem vypočítať rozdiel medzi nimi alebo len agregovať údaje podľa roka, mesiaca, štvrťroka atď.
Spôsob, akým odpovedáte na každú z týchto otázok, určuje vaše možnosti pri riešení problémov so stĺpcom Dátum a čas.
Všetky tieto riešenia vyžadujú úpravu SQL dotazu. Na zjednodušenie úpravy dotazu je potrebné odfiltrovať aspoň jeden stĺpec v každej tabuľke. Filtrovaním stĺpca zmeníte konštrukcie dotazu z skráteného formátu (SELECT *) na príkaz SELECT, ktorý obsahuje úplné názvy stĺpcov, ktoré sa omnoho jednoduchšie upravujú.
Pozrime sa na dotazy, ktoré sú pre vás vytvorené. V dialógovom okne Vlastnosti tabuľky môžete prepnúť na editor dotazov a zobraziť aktuálny SQL dotazu pre každú tabuľku.
V časti Vlastnosti tabuľky vyberte položku Editor dotazov.
Editor dotazov zobrazí SQL použitý na vyplnenie tabuľky. Ak ste počas importu filtrovali akýkoľvek stĺpec, dotaz obsahuje úplné názvy stĺpcov:
|
Naopak, ak ste importli celú tabuľku bez toho, aby ste zrušiť začiarknutie ľubovoľného stĺpca alebo použiť ľubovoľný filter, dotaz sa zobrazí ako "Select * from" (Vyberte * z), čo bude zložitejšie upraviť:
|
Úprava SQL dotazu
Teraz, keď už viete nájsť dotaz, môžete ho upraviť a ďalej zmenšiť veľkosť modelu.
-
Ak v stĺpcoch obsahujúcich údaje meny alebo desatinných miest nepotrebujete desatinné čísla, použite na zbaviť desatinných miest túto syntax:
"SELECT ROUND([Decimal_column_name];0)... .”
Ak potrebujete cent, ale nie zlomky centov, nahraďte ich číslom 0 číslom 2. Ak použijete záporné čísla, môžete ich zaokrúhliť na jednotky, desiatky, stovky atď.
-
Ak máte stĺpec Datetime s názvom dbo. Bigtable. [Dátum a čas] a časť Čas nepotrebujete, pomocou syntaxe sa zbavte času:
"SELECT CAST (dbo. Bigtable. [Dátum a čas] ako dátum) AS [Dátum a čas]) "
-
Ak máte stĺpec Datetime s názvom dbo. Bigtable. [Dátum a čas] a potrebujete časti Dátum a Čas, namiesto jedného stĺpca Dátum a čas použite viaceré stĺpce SQL dotazu:
"SELECT CAST (dbo. Bigtable. [Dátum a čas] ako dátum ) AS [Dátum a čas],
datepart(hh, dbo. Bigtable. [Dátum a čas]) ako [Dátumové hodiny],
datepart(mi, dbo. Bigtable. [Dátum a čas]) ako [Minúty dátumu a času],
datepart(ss, dbo. Bigtable. [Dátum a čas]) ako [Dátum a čas],
datepart(ms, dbo. Bigtable. [Dátum a čas]) as [Date Time Milliseconds]"
Použite toľko stĺpcov, koľko potrebujete na uloženie každej časti do samostatných stĺpcov.
-
Ak potrebujete hodiny a minúty a dávate im prednosť ako jeden časový stĺpec, môžete použiť syntax:
Timefromparts(datepart(hh, dbo. Bigtable. [Dátum a čas]), datepart(mm, dbo. Bigtable. [Dátum a čas])) as [Date Time HourMinute]
-
Ak máte dva stĺpce dátumu a času, napríklad [Čas začatia] a [Čas ukončenia], a v skutočnosti potrebujete, je časový rozdiel medzi nimi v sekundách ako stĺpec s názvom [Trvanie], odstráňte zo zoznamu oba stĺpce a pridajte:
"datediff(ss,[Počiatočný dátum],[Koncový dátum]) ako [Trvanie]"
Ak namiesto slov použijete kľúčové slovo ms, trvanie bude v milisekundách.
Používanie vypočítaných opatrení dax namiesto stĺpcov
Ak ste už predtým pracovali s výrazom jazyka 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čítané hodnoty sú v modeli definované raz, ale vyhodnocujú sa len pri použití v kontingenčnej tabuľke alebo inej zostave.
Jedným z techník na úsporu pamäte je nahradenie bežných alebo vypočítaných stĺpcov vypočítaými metódami. Klasickým príkladom je Jednotková cena, Množstvo a Celkom. Ak máte všetky tri, môžete ušetriť miesto tak, že zachováte len dva a vypočítate tretí použitím súboru DAX.
Ktoré 2 stĺpce by ste mali ponechať?
Vo vyššie uvedenom príklade si ponechajte množstvo a jednotkovú cenu. Tieto dve majú menej hodnôt ako súčet. Ak chcete vypočítať súčet, pridajte vypočítanú mieru, napríklad:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Vypočítané stĺpce sú ako bežné stĺpce v tom, že v modeli zaberú miesto. Vypočítané hodnoty sa vypočítavajú za muchy a nezaberú miesto.
Záver
V tomto článku sme spomínali niekoľko prístupov, ktoré vám pomôžu vytvoriť pamäťovo efektívnejší model. Ak chcete 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, ktoré sa týkajú:
-
Odstránenie stĺpcov je samozrejme najlepším spôsobom, ako ušetriť miesto. Rozhodnite sa, ktoré stĺpce naozaj potrebujete.
-
Niekedy môžete stĺpec odstrániť a nahradiť ho vypočítanú mieru v tabuľke.
-
Možno nepotrebujete všetky riadky v tabuľke. Riadky môžete filtrovať pomocou Sprievodcu importom tabuľky.
-
Vo všeobecnosti platí, že na zníženie počtu jedinečných hodnôt v stĺpci je vhodné rozdeliť jeden stĺpec na viaceré odlišné časti. Každá z častí bude mať malý počet jedinečných hodnôt a celkový súčet bude menší ako pôvodný zjednotený stĺpec.
-
V mnohých prípadoch je potrebné použiť jednotlivé časti ako rýchle filtre v zostavách. V prípade potreby môžete vytvoriť hierarchie z častí, ako sú hodiny, minúty a sekundy.
-
Stĺpce často obsahujú viac informácií, než potrebujete. Predpokladajme napríklad, že stĺpec obsahuje desatinné čísla, ale použili ste formátovanie na skrytie všetkých desatinných miest. Zaokrúhľovanie môže byť veľmi efektívne pri zmenšení veľkosti číselného stĺpca.
Teraz, keď ste už urobili všetko, čo vám umožňuje zmenšiť veľkosť zošita, zvážte aj 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átor veľkosti zošita.
Súvisiace prepojenia
Špecifikácie a obmedzenia dátového modelu
Power Pivot: účinná analýza údajov a modelovanie údajov v Exceli
