V Exceli 2013 alebo novšom môžete vytvárať dátové modely obsahujúce milióny riadkov a potom s týmito modelmi vykonať výkonnú analýzu údajov. Dátové modely je možné vytvoriť pomocou doplnku Power Pivot alebo bez neho, aby bolo možné podporovať ľubovoľný počet kontingenčných tabuliek, grafov a vizualizácií Funkcie Power View v tom istom zošite.
Poznámka: Tento článok popisuje dátové modely v Exceli 2013. Rovnaké funkcie modelovania údajov a doplnku Power Pivot, ktoré boli zavedené v Exceli 2013, sa však vzťahujú aj na Excel 2016. Medzi týmito verziami Excelu je v skutočnosti len malý rozdiel.
Hoci v Exceli môžete jednoducho vytvárať obrovské dátové modely, existuje niekoľko dôvodov, prečo to tak nie je. Po prvé, veľké modely, ktoré obsahujú veľké množstvo tabuliek a stĺpcov, sú príliš komplikované pre väčšinu analýz 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é prostriedky. V Microsoft 365 napokon SharePoint Online aj Excel Web App obmedzujú veľkosť excelového súboru na 10 MB. V prípade dátových modelov zošitov, ktoré obsahujú milióny riadkov, dosiahnete limit 10 MB pomerne rýchlo. Pozrite si špecifikácie a obmedzenia dátového modelu.
V tomto článku sa dozviete, ako vytvoriť pevne zostavený model, s ktorým sa jednoduchšie pracuje a využíva menej pamäte. Ak sa oboznámite s najvhodnejšími postupmi efektívneho návrhu modelu, vyplatí sa to všetkým modelom, ktoré vytvoríte a použijete, bez ohľadu na to, či si ho prezeráte v Exceli 2013, Microsoft 365 SharePointe Online, na Office Web Apps Serveri 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átor veľkosti zošita.
Obsah tohto článku
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 na zníženie požiadaviek na ukladanie, zmenšuje množinu výsledkov, kým nie je zlomok jeho 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 v mieste pôvodu. Ak napríklad importujete 7 MB údajov z SQL Server databázy, dátový model v Exceli môže mať jednoducho veľkosť 1 MB alebo menej. Stupeň skutočne dosiahnutej kompresie 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 viac pamäte sa vyžaduje na ich uloženie.
Prečo hovoríme o kompresii a jedinečných hodnotách? Keďže vytvorenie efektívneho modelu, ktorý minimalizuje využitie pamäte, spočíva v 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é množstvo jedinečných hodnôt.
Poznámka: Rozdiely v požiadavkách na ukladací priestor pre jednotlivé stĺpce môžu byť obrovské. V niektorých prípadoch je lepšie mať viacero stĺpcov s nízkym počtom jedinečných hodnôt a nie jeden stĺpec s vysokým počtom jedinečných hodnôt. Táto technika je podrobne popísaná v časti o optimalizáciách dátumu a času.
Nič neprekoná neexistujúci stĺpec pre nízke využitie pamäte
Najefektívnejším stĺpcom s pamäťou je stĺpec, ktorý ste nikdy neimportovali. Ak chcete vytvoriť efektívny model, pozrite sa na každý stĺpec a položte si otázku, či prispieva k analýze, ktorú chcete vykonať. Ak to tak nie je alebo si nie ste istí, vynechajte to. Ak budete potrebovať nové stĺpce, môžete ich kedykoľvek pridať neskôr.
Dva príklady stĺpcov, ktoré by mali byť vždy vylúčené
Prvý príklad sa týka údajov, ktoré pochádzajú zo skladu údajov. V sklade údajov je bežné nájsť artefakty procesov ETL, ktoré načítavajú a obnovujú údaje v sklade. Po načítaní údajov sa vytvoria stĺpce ako "vytvoriť dátum", "dátum aktualizácie" a "SPUSTIŤ ETL". Žiadny z týchto stĺpcov nie je v modeli potrebný a pri importovaní údajov by sa mal zrušiť výber.
Druhý príklad zahŕňa vynechanie stĺpca hlavného kľúča pri importe tabuľky faktov.
Mnohé tabuľky vrátane tabuliek faktov majú primárne kľúče. Pre väčšinu tabuliek, napríklad tie, ktoré obsahujú údaje o zákazníkovi, zamestnancovi alebo predaji, budete chcieť primárny 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 primárny kľúč používa na jedinečnú identifikáciu každého riadka. Hoci je to potrebné na účely normalizácie, je menej užitočné v dátovom modeli, v ktorom chcete iba tie stĺpce, ktoré sa používajú na analýzu alebo vytvorenie vzťahov tabuliek. Z tohto dôvodu pri importe z tabuľky faktov nezahŕňajte jej hlavný kľúč. Primárne kľúče v tabuľke faktov spotrebúvajú obrovské množstvo priestoru v modeli, ale neposkytujú žiadne výhody, pretože ich nemožno použiť na vytvorenie vzťahov.
Poznámka: V skladoch údajov a multidimenzionálnych databázach sa veľké tabuľky pozostávajúce z prevažne číselných údajov často označujú ako "tabuľky faktov". Tabuľky faktov zvyčajne obsahujú údaje o výkonnosti podniku alebo transakciách, ako sú napríklad údajové body predaja a nákladov, ktoré sú agregované a zosúladené s jednotkami organizácie, produktmi, segmentmi trhu, geografickými oblasťami 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ĺpec hlavného 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é, jedny z najväčších ziskov v efektívnosti modelu sú odvodené 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 určiť, ktoré stĺpce sú zahrnuté v modeli, budete musieť použiť Sprievodcu importom tabuľky v doplnku Power Pivot, aby ste mohli importovať údaje namiesto dialógového okna Import údajov v Exceli.
Keď spustíte Sprievodcu importom tabuľky, vyberiete tabuľky, ktoré sa majú importovať.
Pre každú tabuľku môžete kliknúť na tlačidlo Ukážka & filter a vybrať časti tabuľky, ktoré naozaj potrebujete. Odporúčame najprv zrušiť začiarknutie všetkých stĺpcov a potom pokračovať a skontrolovať požadované stĺpce po zvážení, či sú potrebné na analýzu.
A čo filtrovanie len potrebných riadkov?
Mnohé tabuľky v podnikových databázach a skladoch údajov obsahujú historické údaje nahromadené v priebehu dlhého časového 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 odfiltrovať historické alebo nesúvisiace údaje, čím ušetríte veľa miesta v modeli. Na nasledujúcom obrázku sa filter dátumu používa na načítanie iba riadkov obsahujúcich údaje za aktuálny rok s výnimkou historických údajov, ktoré nebudú potrebné.
Čo ak potrebujeme stĺpec; môžeme ešte znížiť jeho náklady na priestor?
Existuje niekoľko ďalších techník, pomocou ktorých môžete zmeniť stĺpec na lepšieho kandidáta na kompresiu. Nezabudnite, že jedinou charakteristikou stĺpca, ktorý ovplyvňuje kompresiu, je počet jedinečných hodnôt. V tejto časti sa dozviete, ako možno upraviť niektoré stĺpce tak, aby sa znížil počet jedinečných hodnôt.
Úprava stĺpcov dátumu a času
V mnohých prípadoch stĺpce dátumu a času zaberajú veľa miesta. Našťastie existuje niekoľko spôsobov, ako znížiť požiadavky na ukladací priestor pre tento typ údajov. Tieto techniky sa budú líšiť v závislosti od spôsobu používania stĺpca a úrovne pohodlia pri vytváraní dotazov SQL.
Stĺpce dátumu a času obsahujú časť dátumu a čas. Keď si položíte otázku, či potrebujete stĺpec, viackrát položte rovnakú otázku pre stĺpec Dátum a čas:
-
Potrebujem časť času?
-
Potrebujem časť času na úrovni hodín? Minút? Sekúnd? Milisekundách?
-
Mám viacero stĺpcov dátumu a času, pretože chcem vypočítať rozdiel medzi nimi alebo len agregovať údaje podľa rokov, mesiacov, štvrťrokov atď.
Spôsob odpovede na každú z týchto otázok určuje možnosti na spracovanie stĺpca Dátum a čas.
Všetky tieto riešenia vyžadujú úpravu dotazu SQL. Ak chcete zjednodušiť úpravu dotazu, mali by ste vyfiltrovať aspoň jeden stĺpec v každej tabuľke. Filtrovaním stĺpca zmeníte konštrukciu dotazu zo skráteného formátu (SELECT *) na príkaz SELECT, ktorý obsahuje úplné názvy stĺpcov, ktoré sa oveľa jednoduchšie upravujú.
Pozrime sa na dotazy, ktoré sú pre vás vytvorené. V dialógovom okne Vlastnosti tabuľky môžete prepnúť do editora dotazov a zobraziť aktuálny dotaz SQL pre každú tabuľku.
V časti Vlastnosti tabuľky vyberte položku Editor Power Query.
Editor Power Query zobrazuje dotaz SQL použitý na vyplnenie tabuľky. Ak ste počas importu vyfiltrovali ľubovoľný stĺpec, dotaz obsahuje úplné názvy stĺpcov:
|
Naopak, ak ste importovali tabuľku ako celok bez toho, aby ste zrušovali začiarknutie ľubovoľného stĺpca alebo použili filter, dotaz sa zobrazí ako "Select * from", čo bude ťažšie upraviť:
|
Úprava dotazu SQL
Teraz, keď už viete, ako nájsť dotaz, môžete ho upraviť a ďalej zmenšiť veľkosť modelu.
-
Ak v prípade stĺpcov obsahujúcich menu alebo desatinné čísla nepotrebujete desatinné čísla, použite túto syntax na odstránenie desatinných miest:
"SELECT 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ď.
-
Ak máte stĺpec dátumu a času s názvom dbo. Bigtable. [Dátum a čas] a časť Čas nepotrebujete, použite syntax na odstránenie času:
"SELECT CAST (dbo. Bigtable. [Dátum a čas] ako dátum) AS [Dátum a čas]) "
-
Ak máte stĺpec dátumu a času s názvom dbo. Bigtable. [Dátum a čas] a potrebujete časti Dátum aj Čas, použite viacero stĺpcov v dotaze SQL namiesto jedného stĺpca Datetime:
"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 [hodiny dátumu a času],
datepart(mi, dbo. Bigtable. [Dátum a čas]) ako [Minúty dátumu a času],
datepart(ss, dbo. Bigtable. [Dátum a čas]) ako [date time seconds],
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 uprednostňujete ich spolu 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 č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 ss použijete kľúčové slovo ms, trvanie sa zobrazí v milisekundách
Použitie vypočítaných mierok DAX namiesto stĺpcov
Ak ste už predtým pracovali s jazykom výrazov DAX, možno už viete, že vypočítané stĺpce sa používajú na odvodenie nových stĺpcov na základe iného stĺpca v modeli, zatiaľ čo vypočítané mierky sú v modeli definované raz, ale vyhodnocované len pri použití v kontingenčnej tabuľke alebo inej zostave.
Jednou z metód šetriacich pamäť je nahradenie bežných alebo vypočítaných stĺpcov vypočítanými mierami. Klasickým príkladom sú Jednotková cena, Množstvo a Súčet. Ak máte všetky tri, môžete ušetriť miesto údržbou len dvoch a výpočtom tretieho pomocou jazyka DAX.
Ktoré 2 stĺpce by ste si mali ponechať?
V príklade uvedenom vyššie ponechajte množstvo a jednotkovú cenu. Tieto dve hodnoty majú menej hodnôt ako súčet. Ak chcete vypočítať súčet, pridajte vypočítanú mierku, napríklad:
"TotalSales:=sumx('Tabuľka predaja','Tabuľka predaja'[Jednotková cena]*'Tabuľka predaja'[Množstvo])"
Vypočítané stĺpce sú ako bežné stĺpce v tom, že oba zaberajú miesto v modeli. Naopak vypočítavané mierky sa vypočítavajú za chodu a nezaberajú miesto.
Záver
V tomto článku sme sa rozprávali o niekoľkých prístupoch, ktoré vám môžu pomôcť vytvoriť pamäťovo efektívnejší model. Spôsob, ako zmenšiť požiadavky na veľkosť súboru a 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 každom stĺpci. Tu je niekoľko techník, na ktoré sa vzťahujeme:
-
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čítanou mierkou v tabuľke.
-
Možno nebudete potrebovať všetky riadky v tabuľke. Riadky môžete filtrovať v Sprievodcovi importom tabuľky.
-
Rozdelenie jedného stĺpca na viaceré odlišné časti je vo všeobecnosti vhodným spôsobom na zníž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ý zjednotený stĺpec.
-
V mnohých prípadoch potrebujete ako rýchle filtre v zostavách použiť aj odlišné časti. V prípade potreby môžete vytvoriť hierarchie z častí ako Hodiny, Minúty a Sekundy.
-
Stĺpce mnohokrát obsahujú viac informácií, než potrebujete. Predpokladajme napríklad, že stĺpec ukladá desatinné miesta, ale použili ste formátovanie na skrytie všetkých desatinných miest. Zaokrúhlenie môže byť veľmi efektívne pri zmenšení veľkosti číselného stĺpca.
Teraz, keď ste urobili všetko pre zmenšenie veľkosti 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
