Vytvorenie pamäťovo účinného údajového modelu pomocou Excelu a doplnok Power Pivot

Dôležité : Tento článok je strojovo preložený, prečítajte si vyhlásenie. Anglickú verziu tohto článku nájdete tu a môžete ju použiť ako referenciu.

V programe Excel 2013 alebo novšej verzii, môžete vytvoriť údajových modelov, ktoré obsahujú milióny riadkov a potom vykonávanie účinnej analýzy údajov proti tieto modely. Dátové modely je možné vytvoriť s alebo bez Power Pivot doplnok na podporu ľubovoľný počet kontingenčných tabuliek, grafov a vizualizácií Power View v tom istom zošite.

Poznámka : Tento článok popisuje dátové modely v programe Excel 2013. Však rovnaký modelovanie údajov a Power Pivot funkcie podporované v programe Excel 2013 požiadať aj do Excelu 2016. Nie je efektívne malý rozdiel medzi týmito verziami programu Excel.

Hoci môžete jednoducho zostaviť veľké dátové modely v Exceli, niekoľko dôvodov, prečo nie je k. Prvý, veľké modelov, ktoré obsahujú zástupy tabuliek a stĺpcov sú nastavenie pre väčšinu analýzy, a pre náročný zoznam polí. Druhý, veľké modely pomocou hodnotné pamäť, negatívne ovplyvní ostatné aplikácie a zostáv, ktoré zdieľajú rovnaké systémové zdroje. Napokon, v službách Office 365 SharePoint Online a aplikácii Excel Web App obmedziť veľkosť súboru programu Excel na 10 MB. Zošit dátových modelov, ktoré obsahujú milióny riadkov, budete spustiť veľmi rýchlo limit 10 MB. Pozrite si špecifikácie a obmedzenia modelu údajov.

V tomto článku sa naučíte vytvoriť úspornejšie navrhnutý model, s ktorým sa ľahšie pracuje a ktorý používa menej pamäte. Venovať čas štúdiu najlepších postupov efektívneho návrhu modelov sa oplatí, pretože ich využijete pri vytváraní a používaní ľubovoľného modelu, bez ohľadu na to, či ho zobrazujete v Exceli 2013, SharePointe Online v službách Office 365, na serveri Office Web Apps Server alebo v SharePointe 2013.

Zvážte tiež spustený optimalizátor veľkosti zošita. Analyzuje zošit programu Excel a ak je to možné, skomprimuje ho ďalej. Stiahnite si optimalizátor veľkosti zošita.

Obsah článku

Kompresné pomery a analytický mechanizmus v pamäti

Nie je nič lepšie neexistujúci stĺpec na používanie s nedostatkom pamäte

Dva príklady stĺpcov, ktoré treba vždy vylúčiť

Vylúčenie nepotrebných stĺpcov

Je možné vyfiltrovať len potrebné riadky?

Čo robiť, ak potrebujeme stĺpec. môžete sme zaberal menej miesta?

Úprava stĺpcov dátumu a času

Úprava SQL dotazu

Vypočítaných pomocou vzorca DAX opatrenia namiesto stĺpcov

2 stĺpce, ktoré treba zachovať?

Záver

Súvisiace prepojenia

Kompresné pomery a analytický mechanizmus v pamäti

Dátové modely v Exceli používajú na ukladanie údajov do pamäte analytický mechanizmus v pamäti. Tento mechanizmus obsahuje výkonné kompresné technológie na zníženie požiadaviek na ukladací priestor, pričom výsledný objem ukladaných údajov je len zlomkom pôvodnej hodnoty.

Môžete očakávať, že údajový model bude po kompresii priemerne 7 až 10-násobne menší ako pôvodný model obsahujúci tie isté údaje. Ak napríklad importujete 7 MB údajov z databázy aplikácie SQL Server, ľahko dosiahnete veľkosť údajového modelu v Exceli 1 MB alebo aj menej. Aktuálne dosiahnutý stupeň kompresie závisí hlavne od počtu jedinečných hodnôt v každom stĺpci. Čím viac je jedinečných hodnôt, tým viac pamäte sa vyžaduje na ich uloženie.

Prečo hovoríme o kompresii a o jedinečných hodnotách? Pretože vytvorenie účinného modelu, ktorý minimalizuje využívanie pamäte, znamená predovšetkým maximalizáciu kompresie, a najjednoduchším spôsobom, ako to dosiahnuť, je odstránenie všetkých stĺpcov, ktoré v skutočnosti nepotrebujete, najmä ak obsahujú veľký počet 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ť viac stĺpcov s malým počtom jedinečných hodnôt ako jeden stĺpec s veľkým počtom jedinečných hodnôt. Táto metóda je podrobne popísaná v časti článku o optimalizácii dátumu a času.

Neexistujúci stĺpec nezaberá žiadne miesto v pamäti

Predovšetkým treba povedať, že pamäťovo najefektívnejší stĺpec je taký, ktorý vôbec neimportujete. Ak chcete vytvoriť efektívny model, prezrite každý stĺpec a položte si otázku, či daný stĺpec prispieva k analýze, ktorú chcete vykonať. Ak neprispieva alebo si nie ste istí, stĺpec vynechajte. Ak to bude potrebné, nové stĺpce budete môcť neskôr kedykoľvek pridať.

Dva príklady stĺpcov, ktoré treba vždy vylúčiť

Prvý príklad súvisí s údajmi, ktoré pochádzajú zo skladu údajov. V skladoch údajov môžeme bežne nájsť artefakty ETL procesov, ktoré načítavajú a obnovujú údaje v sklade údajov. Pri načítavaní údajov sa vytvárajú stĺpce ako Create date (Dátum vytvorenia), Update date (Dátum aktualizácie) a ETL run (Spustenie ETL). Žiadny z týchto stĺpcov však v modeli nie je potrebný a pri importe údajov treba výber týchto stĺpcov zrušiť.

V druhom príklade bol pri importovaní tabuľky faktov vynechaný stĺpec primárneho kľúča.

Mnohé tabuľky vrátane tabuliek faktov majú primárny kľúč. Pri väčšine tabuliek, napríklad takých, ktoré obsahujú údaje o zákazníkoch, zamestnancoch alebo predaji, budete chcieť primárny kľúč zahrnúť, aby ste ho mohli použiť na vytvorenie vzťahov v modeli.

Pri tabuľkách faktov je to inak. V tabuľke faktov sa primárny kľúč používa na jedinečnú identifikáciu každého riadka. Primárny kľúč je nevyhnutný na účely normalizácie, avšak v údajovom modeli, v ktorom chcete mať iba stĺpce používané na analýzu alebo na vytvorenie vzťahov medzi tabuľkami, nie je až taký užitočný. Z toho dôvodu pri importovaní údajov z tabuľky faktov neimportujte jej primárny kľúč. Primárne kľúče v tabuľke faktov zaberajú v modeli enormné miesto a pritom nie sú vôbec potrebné, pretože na vytvorenie vzťahov ich nie je možné použiť.

Poznámka : V skladoch údajov a multidimenzionálnych databázach sa rozsiahle tabuľky pozostávajúce prevažne z číselných údajov často označujú ako tabuľky faktov. Tabuľky faktov zvyčajne zahŕňajú údaje o výkonnosti podniku alebo údaje transakcií, napríklad údajové body týkajúce sa predaja a nákladov agregované a usporiadané podľa organizačných jednotiek, produktov, segmentov trhu, geografických oblastí atď. Všetky stĺpce tabuľky faktov, ktoré obsahujú podnikové údaje alebo ktoré sa dajú použiť ako krížové údaje uložené v ďalších tabuľkách, je potrebné zahrnúť do modelu, aby bola zabezpečená podpora analýzy údajov. Vylúčiť sa dá stĺpec s primárnym kľúčom tabuľky faktov pozostávajúci z jedinečných hodnôt, ktoré existujú len v tabuľke faktov a nikde inde. Pretože tabuľky faktov sú také obrovské, vylúčenie riadkov alebo stĺpcov tabuliek faktov patrí k najúčinnejším prostriedkom dosiahnutia efektívnosti modelu.

Vylúčenie nepotrebných stĺpcov

Efektívne modely obsahovať len stĺpce, ktoré naozaj potrebujete v zošite. Ak chcete určiť stĺpce, ktoré sú zahrnuté v modeli, budete musieť použiť Sprievodcu importom tabuľky v doplnku Power Pivot doplnok na importovanie údajov namiesto "Importovať údaje" dialógové okno v programe Excel.

Po spustení Sprievodcu importom tabuliek vyberiete, ktoré tabuľky chcete importovať.

Sprievodca importom tabuliek v doplnku PowerPivot

Pri každej tabuľke môžete kliknúť na tlačidlo Zobraziť ukážku a filtrovať a vybrať tie časti tabuľky, ktoré skutočne potrebujete. Odporúča sa najprv zrušiť výber všetkých stĺpcov a až potom, keď zvážite, či je daný stĺpec na účely analýzy potrebný, vybrať požadované stĺpce.

Tabla Ukážka v Sprievodcovi importom tabuliek

Je možné vyfiltrovať len potrebné riadky?

Mnohé tabuľky v podnikových databázach a skladoch údajov obsahujú historické údaje zhromaždené počas dlhého časového obdobia. Okrem toho možno zistíte, že tabuľky, ktoré vás zaujímajú, obsahujú aj podnikové informácie o takých oblastiach, ktoré pri konkrétnej analýze nie sú potrebné.

Pomocou Sprievodcu importom tabuliek môžete odfiltrovať historické alebo nesúvisiace údaje a v modeli tak ušetriť veľa miesta. Na nasledujúcom obrázku bol filter dátumu použitý na načítanie riadkov obsahujúcich údaje z aktuálneho roka a vylúčenie nepotrebných historických údajov.

Tabla Filter v Sprievodcovi importom tabuliek

Ak je stĺpec potrebný, dá sa zabezpečiť, aby zaberal menej miesta?

Existuje niekoľko ďalších spôsobov, ktorými môžete stĺpec upraviť tak, aby bol vhodnejší na kompresiu. Nezabudnite, že jedinou vlastnosťou stĺpca, ktorá ovplyvňuje kompresiu, je počet jedinečných hodnôt. V tejto časti si ukážeme, ako sa dajú niektoré stĺpce upraviť tak, aby sa počet jedinečných hodnôt znížil.

Ú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 veľa spôsobov, ako pri tomto type údajov znížiť požiadavky na ukladací priestor. Spôsoby sa líšia v závislosti od toho, akým spôsobom stĺpec používate, a od toho, na akej úrovni ovládate vytváranie SQL dotazov.

Stĺpce dátumu a času sa skladajú z dvoch častí: dátumu a času. Keď si kladiete otázku, či stĺpec potrebujete, v prípade stĺpca dátumu a času si položte aj tieto otázky:

  • Potrebujem časť hodnoty času?

  • Potrebujem časť hodnoty času na úrovni hodín, minút, sekúnd, milisekúnd?

  • Potrebujem viac stĺpcov dátumu a času, pretože chcem vypočítať rozdiely medzi nimi, alebo mi stačí vykonať agregáciu údajov podľa rokov, mesiacov, štvrťrokov atď.?

Možnosti úpravy stĺpca dátumu a času závisia od odpovede na každú z týchto otázok.

Všetky tieto riešenia vyžadujú úpravu SQL dotazu. Na zjednodušenie úpravy dotazu by ste mali v každej tabuľke odfiltrovať aspoň jeden stĺpec. Odfiltrovaním stĺpca zmeníte stavbu dotazu zo skráteného formátu (SELECT *) na príkaz SELECT obsahujúci úplné názvy stĺpcov, ktoré sa upravujú oveľa jednoduchšie.

Pozrime sa na dotazy, ktoré sa vytvárajú automaticky. Z dialógového okna Vlastnosti tabuľky môžete prepnúť na Editor dotazov a pozrieť si aktuálny SQL dotaz pre každú tabuľku.

Ribbon in PowerPivot window showing Table Properties command

V dialógovom okne Vlastnosti tabuľky vyberte položku Editor dotazov.

Otvorte Editor dotazov v dialógovom okne Vlastnosti tabuľky

V Editore dotazov sa zobrazí SQL dotaz, ktorý bol použitý na vyplnenie tabuľky. Ak ste pri importe niektorý stĺpec odfiltrovali, dotaz obsahuje úplné názvy stĺpcov:

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

Naproti tomu, ak ste importovali kompletnú tabuľku, teda nezrušili ste výber žiadneho stĺpca ani ste nepoužili žiadny filter, zobrazí sa dotaz vo forme príkazu Select * from, ktorý sa upravuje ťažšie:

Dotaz SQL pomocou predvoleného nastavenia sa kratšie syntax

Úprava SQL dotazu

Teraz už dotaz viete nájsť, takže ho môžete upraviť a tým ďalej zmenšiť veľkosť modelu.

  1. V prípade stĺpcov obsahujúcich finančné čiastky alebo čísla s desatinnými miestami, ak desatinné miesta nepotrebujete, na odstránenie desatinných miest použite túto syntax:

    “SELECT ROUND([názov_stĺpca_obsahujúceho_desatinné_miesta],0)… .”

    Ak potrebujete sumy v centoch, nie však ďalšie desatinné miesta, číslicu 0 nahraďte číslicou 2. Ak použijete záporné čísla, môžete zaokrúhľovať na jednotky, desiatky, stovky atď.

  2. Ak máte stĺpec dátumu a času s názvom dbo.Bigtable.[Date Time] a časť hodnoty času nepotrebujete, použite syntax, ktorá odstráni časovú zložku:

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. Ak máte stĺpec dátumu a času s názvom dbo.Bigtable.[Date Time] a potrebujete časť hodnoty dátumu aj časť hodnoty času, namiesto jedného stĺpca dátumu a času použite v SQL dotaze viac stĺpcov:

    SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds].

    Na uloženie každej časti v samostatnom stĺpci použite toľko stĺpcov, koľko potrebujete.

  4. Ak potrebujete hodiny a minúty v jednom stĺpci času, môžete použiť syntax:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Ak máte dva stĺpce s dátumom a časom, napríklad [Start Time] (Čas začatia) a [End Time] (Čas ukončenia), a potrebujete časový rozdiel v sekundách v stĺpci s názvom [Duration] (Trvanie), odstráňte zo zoznamu obidva stĺpce a pridajte výraz:

    “datediff(ss,[Start Date],[End Date] as [Duration]”

    Ak namiesto kľúčového slova ss použijete ms, dostanete trvanie v milisekundách.

Použitie mier vypočítaných pomocou vzorca DAX namiesto stĺpcov

Ak ste už pracovali s výrazmi DAX, asi už viete, že vypočítané stĺpce sa používajú na odvodenie nových stĺpcov založených na niektorom inom stĺpci modelu, zatiaľ čo vypočítané miery sú definované v modeli len raz a vyhodnocujú sa len vtedy, keď sa použijú v kontingenčnej tabuľke alebo inej zostave.

Jedným zo spôsobov šetriacich pamäť je nahradenie bežných alebo vypočítaných stĺpcov vypočítanými mierami. Klasickým príkladom sú stĺpce Jednotková cena, Množstvo a Celkom. Ak máte všetky tri stĺpce, môžete ušetriť miesto tak, že zachováte len dva a hodnoty v treťom stĺpci sa vyrátajú pomocou vzorca DAX.

Ktoré dva stĺpce treba zachovať?

Vo vyššie uvedenom príklade treba zachovať stĺpce Množstvo a Jednotková cena. Tieto dva stĺpce obsahujú menej hodnôt ako stĺpec Celkom. Ak chcete vypočítať hodnoty stĺpca Celkom, pridajte vypočítanú mieru, napríklad:

“CelkovýPredaj:=sumx(‘Tabuľka Predaj’;’TabuľkaPredaj’[Jednotková cena]*’Tabuľka Predaj’[Množstvo])“

Vypočítané stĺpce sú podobné bežným stĺpcom v tom, že v modeli tiež zaberajú miesto. Naopak, vypočítané miery sa počítajú priebežne a nezaberajú miesto.

Záver

V tomto článku sme popísali niektoré postupy, ktoré vám môžu pomôcť vytvoriť pamäťovo efektívnejší model. Cestou k menšej veľkosti súboru údajového modelu a k nižším požiadavkám na pamäť je zníženie celkového počtu stĺpcov a riadkov a počtu jedinečných hodnôt jednotlivých stĺpcov. Tu sú niektoré spôsoby, ktoré sme v článku uviedli:

  • Odstránenie niektorých stĺpcov je pochopiteľne najlepším spôsobom šetrenia miesta. Rozhodnite sa, ktoré stĺpce skutočne potrebujete.

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

  • Možno nepotrebujete všetky riadky tabuľky. Riadky môžete odfiltrovať pomocou Sprievodcu importom tabuliek.

  • Všeobecne platí, že rozdelenie stĺpca na niekoľko častí je dobrým spôsobom na zníženie počtu jedinečných hodnôt v stĺpci. Každá časť bude mať menší počet jedinečných hodnôt a ich celkový počet bude menší ako v pôvodnom jednotnom stĺpci.

  • V mnohých prípadoch budete potrebovať samostatné časti aj na to, aby ste ich mohli používať v zostavách ako rýchle filtre. V prípade potreby môžete vytvárať hierarchie napríklad z častí Hodiny, Minúty a Sekundy.

  • Stĺpce často obsahujú viac informácií ako potrebujete. Ak sú v stĺpci napríklad uložené aj desatinné miesta, no použijete formátovanie, pri ktorom sa žiadne desatinné miesta nezobrazujú. Zaokrúhľovanie môže byť veľmi efektívnym spôsobom, ako zmenšiť veľkosť číselného stĺpca.

Teraz, keď to urobíte, čo môžete zmenšiť veľkosť zošita, vezmite do úvahy aj so systémom optimalizátor veľkosti zošita. Analyzuje zošit programu Excel a ak je to možné, skomprimuje ho ďalej. Stiahnite si optimalizátor veľkosti zošita.

Súvisiace prepojenia

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

Optimalizátor veľkosti zošita na stiahnutie

Power Pivot: Účinná analýza údajov a modelovanie údajov v Exceli

Poznámka : Vyhlásenie týkajúce sa strojového prekladu: Tento článok bol preložený počítačovým systémom bez zásahu človeka. Poskytovaním týchto strojových prekladov umožňuje spoločnosť Microsoft aj používateľom, ktorí nehovoria po anglicky, využívať obsah o produktoch, službách a technológiách spoločnosti Microsoft. Článok bol preložený strojovo, môže preto obsahovať chyby týkajúce sa slovnej zásoby, syntaxe alebo gramatiky.

Rozšírte svoje zručnosti
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.

×