V Exceli môžete vytvoriť dátové modely obsahujúce milióny riadkov a potom v porovnaní s týmito modelmi vykonať účinnú analýzu údajov. Dátové modely možno vytvoriť s doplnkom Power Pivot alebo bez neho, aby podporovali ľubovoľný počet kontingenčných tabuliek, grafov a vizualizácií Power View v jednom zošite.
Aj keď v Exceli môžete jednoducho vytvoriť veľké dátové modely, existuje niekoľko dôvodov, prečo to neurobiť. Po prvé, veľké modely obsahujúce veľké množstvo tabuliek a stĺpcov sú pre väčšinu analýz prehnané a vytvárajú ťažkopádny zoznam polí. Po druhé, veľké modely zaberajú cennú pamäť, čo má negatívny vplyv na iné aplikácie a zostavy, ktoré zdieľajú rovnaké systémové prostriedky. A nakoniec, v službe Microsoft 365 sú obmedzenia veľkosti excelového súboru na 10 MB služby SharePoint Online aj aplikácie Excel Web App. V prípade dátových modelov zošita, ktoré obsahujú milióny riadkov, sa limit 10 MB dosiahne pomerne rýchlo. Pozrite si špecifikácie a obmedzenia dátového modelu.
V tomto článku sa dozviete, ako vytvoriť dôkladne zostavený model, ktorý sa jednoduchšie pracuje a zaberá menej pamäte. Ak si vytvoríte a použijete čas na oboznámenie sa s najvhodnejšími postupmi v oblasti efektívneho navrhovania modelov, vyplatíte neskôr, či už ho zobrazujete v Exceli, službe Microsoft 365 SharePoint Online, na serveri aplikácií Office Web Apps alebo v SharePointe.
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
Pri nízkom využití pamäte sa nič nevyrovná neexistujúcemu stĺpcu
Čo ak potrebujeme stĺpec; Môžeme stále znížiť náklady na priestor?
Kompresné pomery a nástroj analýzy v pamäti
Dátové modely v Exceli používajú nástroj na analýzu pamäte na ukladanie údajov do pamäte. Motor implementuje výkonné kompresné techniky na zníženie požiadaviek na skladovanie a zmenšuje množinu výsledkov, kým nedosiahne zlomok svojej pôvodnej veľkosti.
V priemere možno očakávať, že dátový model bude 7 až 10-krát menší než tie isté údaje v mieste pôvodu. Ak napríklad importujete 7 MB údajov z databázy SQL Server, dátový model v Exceli môže mať pokojne 1 MB alebo menej. Stupeň kompresie v skutočnosti dosiahnutý závisí predovšetkým od počtu jedinečných hodnôt v jednotlivých stĺpcoch. Čím viac jedinečných hodnôt, tým viac pamäte je potrebné na ich uloženie.
Prečo hovoríme o kompresii a jedinečných hodnotách? Pretož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 zbaviť sa všetkých stĺpcov, ktoré v skutočnosti 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ť namiesto jedného stĺpca s vysokým počtom jedinečných hodnôt viac stĺpcov s nízkym počtom jedinečných hodnôt. Táto technika sa podrobne zaoberá v časti o optimalizáciách dátumu a času.
Pri nízkom využití pamäte sa nič nevyrovná neexistujúcemu stĺpcu
Pamätovo najúčinnejší stĺpec je ten, 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 nie je to tak alebo si nie ste istí, vynechajte ho. Nové stĺpce môžete kedykoľvek pridať neskôr, ak to budete potrebovať.
Dva príklady stĺpcov, ktoré by mali byť vždy vylúčené
Prvý príklad sa týka údajov pochádzajúcich zo skladu údajov. V sklade údajov sa bežne nachádzajú artefakty procesov ETL, ktoré načítavajú a obnovujú údaje v sklade. Stĺpce ako "create date" (dátum vytvorenia), "update date" (dátum aktualizácie) a "ETL run" (spustenie ETL) sa vytvoria po načítaní údajov. Žiadny z týchto stĺpcov nie je v modeli potrebný a ich výber by ste nemali zrušiť pri importe údajov.
V druhom príklade sa pri importe tabuľky faktov vynechá stĺpec primárneho kľúča.
Mnohé tabuľky vrátane tabuliek faktov majú primárne kľúče. V prípade väčšiny tabuliek, napríklad tých, ktoré obsahujú údaje o zákazníkoch, zamestnancoch alebo predaji, budete chcieť použiť 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 jednoznačnú identifikáciu každého riadka. Hoci je nevyhnutná na účely normalizácie, menej je užitočná v dátovom modeli, v ktorom sa majú na analýzu alebo vytvorenie vzťahov tabuliek používať iba tie stĺpce. Z tohto dôvodu pri importe z tabuľky faktov nezahŕňajte jej primárny kľúč. Primárne kľúče v tabuľke faktov zaberajú v modeli obrovské množstvo miesta, ale neposkytujú žiadnu výhodu, pretože ich nemožno použiť na vytvorenie vzťahov.
Poznámka
V dátových skladoch a viacrozmerných databázach sa veľké tabuľky, ktoré obsahujú prevažne číselné údaje, často označujú ako tabuľky faktov. Tabuľky faktov zvyčajne zahŕňajú údaje o obchodnej výkonnosti alebo transakciách, ako sú napríklad údaje o predaji a nákladoch, ktoré sú agregované a zosúladené 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é odkazovanie 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 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ú veľmi veľké, niektoré z najväčších zlepšenia efektivity modelu sa dosiahnu vylúčením 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 skutočne potrebovať. Ak chcete určiť, ktoré stĺpce budú zahrnuté v modeli, na import údajov treba namiesto dialógového okna Import údajov v Exceli použiť Sprievodcu importom tabuľky v doplnku Power Pivot .
Pri spustení Sprievodcu importom tabuľky vyberiete tabuľky, ktoré sa majú importovať.
V prípade každej tabuľky môžete kliknúť na tlačidlo Ukážka & Filter a vybrať časti tabuľky, ktoré naozaj potrebujete. Odporúča sa 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é pre analýzu.
Ako je to s filtrovaním len potrebných riadkov?
Mnohé tabuľky v podnikových databázach a skladoch údajov obsahujú historické údaje zhromaždené počas dlhých časových období. Okrem toho možno zistíte, že tabuľky, ktoré vás zaujímajú, obsahujú informácie o oblastiach podnikania, ktoré nie sú potrebné pre vašu konkrétnu analýzu.
Pomocou Sprievodcu importom tabuľky môžete odfiltrovať historické alebo nesúvisiace údaje, a ušetriť tak veľa miesta v modeli. Na nasledujúcom obrázku je filter dátumu použitý na načítanie iba riadkov, ktoré obsahujú údaje za aktuálny rok, s výnimkou historických údajov, ktoré nebudú potrebné.
Čo ak potrebujeme stĺpec; Môžeme stále znížiť náklady na priestor?
Existuje niekoľko ďalších techník, ktoré môžete použiť, aby sa stĺpec stal lepším kandidátom na kompresiu. Nezabudnite, že jedinou charakteristikou stĺpca, ktorá má vplyv na kompresiu, je počet jedinečných hodnôt. V tejto časti sa dozviete, ako môžete niektoré stĺpce zmeniť, aby sa znížil počet jedinečných hodnôt.
Úprava stĺpcov dátumu a času
Stĺpce dátumu a času často zaberajú veľa miesta. Našťastie existuje množstvo spôsobov, ako znížiť požiadavky na ukladací priestor pre tento typ údajov. Postupy sa budú líšiť v závislosti od spôsobu používania stĺpca a úrovne vášho komfortu pri vytváraní dotazov SQL.
Stĺpce dátumu a času obsahujú časť dátumu a čas. Keď sa sami seba opýtate, či potrebujete stĺpec, viackrát položte rovnakú otázku pre stĺpec s dátumom.
- Potrebujem časovú časť?
- Potrebujem časovú časť na úrovni hodín? , minút? , Sekundy? , milisekundy?
- Mám viac stĺpcov dátumu a času preto, lebo chcem vypočítať rozdiel medzi nimi, alebo len preto, aby som agregoval údaje podľa roka, mesiaca, štvrťroka atď.
Odpovede na každú z týchto otázok určujú vaše možnosti pri práci so stĺpcom Dátum a čas.
Všetky tieto riešenia vyžadujú úpravu dotazu SQL. Na zjednodušenie úprav dotazov by ste mali v každej tabuľke odfiltrovať aspoň jeden stĺpec. Odfiltrovaním stĺpca zmeníte konštrukciu dotazu zo skráteného formátu (SELECT *) na príkaz SELECT obsahujúci úplné názvy stĺpcov, ktoré sa dajú oveľa ľahšie upraviť.
Pozrime sa na dotazy, ktoré boli vytvorené pre vás. V dialógovom okne Vlastnosti tabuľky môžete prepnúť na Editor 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 odfiltrovali akýkoľvek stĺpec, dotaz obsahuje úplné názvy stĺpcov:
Ak ste však importovali celú tabuľku bez zrušenia začiarknutia akéhokoľvek stĺpca alebo použitia filtra, dotaz sa zobrazí s textom Vybrať * z ", čo bude zložitejšie upravovať:
|
|---|
Úprava dotazu SQL
Teraz už viete, ako nájsť dotaz, môžete ho upraviť a ešte tak zmenšiť veľkosť vášho modelu.
- Ak v prípade stĺpcov obsahujúcich údaje o mene alebo desatinných miestach desatinné miesta nepotrebujete, pomocou tejto syntaxe desatinné miesta odstráňte:
"SELECT ROUND([Decimal_column_name],0)... .”
Ak potrebujete centy, ale nie zlomky centov, nahraďte znaky 0 číslom 2. Pri použití záporných čísel môžete zaokrúhliť na jednotky, desiatky, stovky atď. - Ak máte stĺpec Dátum a čas s názvom dbo. Veľká tabuľka. [Dátum a čas] a nepotrebujete časť Čas, pomocou syntaxe odstráňte čas:
"VYBERTE VYSIELANIE (dbo. Veľká tabuľka. [Dátum a čas] ako dátum) AS [Dátum a čas]) " - Ak máte stĺpec Dátum a čas s názvom dbo. Veľká tabuľka. [Dátum a čas] a potrebujete časti Dátum a Čas, použite v dotaze SQL namiesto jedného stĺpca Dátum a čas viacero stĺpcov:
"VYBERTE VYSIELANIE (dbo. Veľká tabuľka. [Dátum a čas] ako dátum ) AS [dátum a čas],
DatePart(HH; dbo. Veľká tabuľka. [Dátum a čas]) as [Dátum a čas hodiny],
DatePart(mi, dbo. Veľká tabuľka. [Dátum a čas]) ako [Dátum a čas a minúty],
datepart(zs; dbo. Veľká tabuľka. [Dátum a čas]) ako [Dátum a čas v sekundách],
datepart(ms; dbo. Veľká tabuľka. [Dátum a čas]) ako [Dátum a čas milisekundy]"
Použite toľko stĺpcov, koľko potrebujete na uloženie jednotlivých častí v samostatných stĺpcoch. - 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. Veľká tabuľka. [Dátum a čas]), datepart(mm, dbo. Veľká tabuľka. [Dátum a čas])) ako [Dátum, Čas, HodinaMinúta] - Ak máte dva stĺpce s dátumom a časom, napríklad [Počiatočný čas] a [Čas ukončenia], a skutočne potrebujete časový rozdiel medzi nimi v sekundách v stĺpci s názvom [Trvanie], odstráňte oba stĺpce zo zoznamu a pridajte:
"datediff(ss;[počiatočný dátum],[koncový dátum]) as [trvanie]"
Ak použijete kľúčové slovo ms namiesto ss, trvanie bude v milisekundách
Použitie vypočítavaných mier jazyka DAX namiesto stĺpcov
Ak ste už s výrazovým jazykom DAX pracovali, pravdepodobne viete, že vypočítané stĺpce sa používajú na odvodenie nových stĺpcov na základe nejakého iného stĺpca v modeli, zatiaľ čo vypočítané miery sú v modeli definované raz, ale vyhodnocujú sa iba v kontingenčnej tabuľke alebo inej zostave.
Jedným z spôsobov šetrenia pamäte je nahradenie bežných alebo vypočítavaných stĺpcov vypočítavanými mierami. Klasickým príkladom sú Jednotková cena, Množstvo a Celkom. Ak máte všetky tri položky, miesto môžete ušetriť tak, že ponecháte len dve a tretí vypočítate pomocou jazyka DAX.
Ktoré 2 stĺpce by ste si mali ponechať?
Vo vyššie uvedenom príklade uchovávajte údaje Množstvo a Jednotkovú cenu. Tieto dve položky obsahujú menej hodnôt ako súčet. Ak chcete vypočítať súčet, pridajte vypočítanú mieru, ako napríklad:
"TotalSales:=sumx('Tabuľka predaja','Tabuľka predaja'[Jednotková cena]*'Tabuľka predaja'[Množstvo])"
Vypočítavané stĺpce sú ako bežné stĺpce v tom, že oba v modeli zaberajú miesto. Vypočítavané miery sa naopak vypočítavajú za chodu a nezaberajú priestor.
Záver
V tomto článku sme spomenuli niekoľko prístupov, ktoré vám môžu pomôcť vytvoriť pamäťovo účinnejší model. Spôsob zníženia veľkosti súboru a požiadaviek na pamäť dátového modelu je zníženie celkového počtu stĺpcov a riadkov a počtu jedinečných hodnôt zobrazujúcich sa v každom stĺpci. Tu sú niektoré techniky, ktoré sme preštudovali:
- Odstránenie stĺpcov je samozrejme najlepším spôsobom, ako ušetriť miesto. Rozhodnite sa, ktoré stĺpce naozaj potrebujete.
- Niekedy je možné stĺpec odstrániť a nahradiť ho vypočítanou mierou v tabuľke.
- Možno nebudete potrebovať všetky riadky v tabuľke. V Sprievodcovi importom tabuľky môžete filtrovať riadky.
- Vo všeobecnosti platí, že rozdelenie jedného stĺpca na viacero samostatných častí je 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 skombinovaný súčet bude menší ako pôvodný zjednotený stĺpec.
- V mnohých prípadoch je tiež potrebné použiť jednotlivé časti ako rýchle filtre v zostavách. Podľa potreby môžete hierarchie vytvoriť z častí, ako sú hodiny, minúty a sekundy.
- Stĺpce často obsahujú viac informácií, ako potrebujete. Predpokladajme napríklad, že stĺpec obsahuje desatinné miesta, ale na skrytie všetkých desatinných miest ste použili formátovanie. Zaokrúhľovanie môže byť veľmi účinné pri zmenšovaní veľkosti číselného stĺpca.
Teraz, keď ste urobili všetko pre zmenšenie veľkosti zošita, 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.
Súvisiace prepojenia
Špecifikácie a obmedzenia dátového modelu
Power Pivot: účinná analýza údajov a modelovanie údajov v Exceli