V Excel 2013 nebo novějších verzích můžete vytvářet datové modely obsahující miliony řádků a pak provádět výkonnou analýzu dat s těmito modely. Datové modely můžete vytvářet pomocí doplňku Power Pivot nebo bez něj, aby podporovaly libovolný počet kontingenčních tabulek, grafů a Power View vizualizací ve stejném sešitu.

Poznámka: Tento článek popisuje datové modely v Excel 2013. Stejné datové modelování a funkce Power Pivotu zavedené v Excel 2013 se ale vztahují i na Excel 2016. V těchto verzích aplikace je v podstatě malý Excel rozdíl.

I když můžete snadno vytvářet obrovské datové modely v Excel, existuje několik důvodů, proč to ne. Za prvé, velké modely, které obsahují velké množství tabulek a sloupců, jsou pro většinu analýz přemáhavé a tvoří těžkopádný seznam polí. Za druhé, velké modely používají cennou paměť a negativně ovlivňují jiné aplikace a sestavy, které sdílejí stejné systémové prostředky. A konečně v Microsoft 365, SharePoint Online i Excel Web App omezují velikost souboru Excel na 10 MB. U datových modelů sešitů, které obsahují miliony řádků, naběhnou do limitu 10 MB docela rychle. Viz specifikace a limity datového modelu.

V tomto článku se dozvíte, jak vytvořit těsně vytvořený model, se který se snadněji pracuje a využívá méně paměti. Využití času na to, abyste se naučili osvědčené postupy při efektivním návrhu modelu, se vyplatí pro každý model, který vytvoříte a používáte, ať už si ho prohlížíte v Excel 2013, Microsoft 365 SharePoint Online, na serveru Office Web Apps nebo v SharePoint 2013.

Zvažte taky spuštění nástroje Workbook Size Optimizer. Ten udělá analýzu excelového sešitu, a pokud je to možné, dál ho zkomprimuje. Stáhněte si Optimalizátor velikosti sešitu.

V tomto článku

Kompresní poměry a analytický modul v paměti

Datové modely v Excel pomocí analytického modulu v paměti k ukládání dat do paměti. Modul implementuje výkonné techniky komprese, které snižují požadavky na úložiště, a zmenšuje sadu výsledků, dokud nebude zlomek původní velikosti.

V průměru můžete očekávat, že datový model bude 7 až 10krát menší než stejná data v místě jeho původu. Pokud třeba importujete 7 MB dat z SQL Server databáze, může být datový model v Excel 1 MB nebo menší. Míra skutečně dosažené komprese závisí především na počtu jedinečných hodnot v jednotlivých sloupcích. Čím více jedinečných hodnot, tím více paměti je potřeba k jejich uložení.

Proč se bavíme o kompresi a jedinečných hodnotách? Vzhledem k tomu, že vytvoření efektivního modelu, který minimalizuje využití paměti, je o maximalizaci komprese a nejjednodušší způsob, jak to udělat, je zbavit se sloupců, které ve skutečnosti nepotřebujete, zvlášť pokud tyto sloupce obsahují velký počet jedinečných hodnot.

Poznámka: Rozdíly v požadavcích na úložiště pro jednotlivé sloupce mohou být obrovské. V některých případech je lepší mít více sloupců s nízkým počtem jedinečných hodnot a ne jeden sloupec s vysokým počtem jedinečných hodnot. Oddíl optimalizace Data a času podrobně popisuje tuto techniku.

Nic nepřebije neexistující sloupec pro nízké využití paměti.

Nejefektivnějším sloupcem paměti je sloupec, který jste nikdy neimportoval(a). Pokud chcete vytvořit efektivní model, podívejte se na každý sloupec a zeptejte se sami sebe, jestli přispívá k analýze, kterou chcete provést. Pokud tomu tak není nebo si nejste jistí, vynechejte ho. Pokud je budete potřebovat, můžete později přidat nové sloupce.

Dva příklady sloupců, které by měly být vždy vyloučeny

První příklad se týká dat, která pocházejí z datového skladu. V datovém skladu je běžné najít artefakty procesů ETL, které načítá a aktualizují data ve skladě. Sloupce jako "datum vytvoření", "datum aktualizace" a "SPUSTIT ETL" se vytvoří při načtení dat. Žádný z těchto sloupců není v modelu potřeba a měl by být při importu dat zrušen.

Druhý příklad zahrnuje vynechání sloupce primárního klíče při importu tabulky faktů.

Mnoho tabulek, včetně tabulek faktů, má primární klíče. U většiny tabulek, například těch, které obsahují data zákazníků, zaměstnanců nebo prodejů, budete chtít primární klíč tabulky, abyste ho mohli použít k vytvoření relací v modelu.

Tabulky faktů se liší. V tabulce faktů se primární klíč používá k jedinečné identifikaci jednotlivých řádků. I když je to nutné pro účely normalizace, je to méně užitečné v datovém modelu, kde chcete použít jenom ty sloupce, které se používají k analýze nebo k vytvoření relací mezi tabulkami. Z tohoto důvodu při importu z tabulky faktů nezahrnujte jeho primární klíč. Primární klíče v tabulce faktů spotřebovávají obrovské množství místa v modelu, ale nemají žádnou výhodu, protože je nelze použít k vytváření relací.

Poznámka: V datových skladech a multidimenzionálních databázích se velké tabulky skládající se převážně z číselných dat často označují jako "tabulky faktů". Tabulky faktů obvykle zahrnují obchodní výkon nebo data transakcí, jako jsou datové body prodeje a nákladů, které jsou agregované a zarovnané s organizačními jednotkami, produkty, segmenty trhu, geografickými oblastmi a tak dále. Všechny sloupce v tabulce faktů, které obsahují obchodní data nebo které je možné použít k křížové referenci dat uložených v jiných tabulkách, by měly být zahrnuty do modelu pro podporu analýzy dat. Sloupec, který chcete vyloučit, je sloupec primárního klíče tabulky faktů, který se skládá z jedinečných hodnot, které existují jenom v tabulce faktů a nikde jinde. Vzhledem k tomu, že tabulky faktů jsou tak obrovské, jsou některé z největších nárůstů efektivity modelu odvozené od vyloučení řádků nebo sloupců z tabulek faktů.

Jak vyloučit nepotřebné sloupce

Efektivní modely obsahují jenom ty sloupce, které budete ve skutečnosti potřebovat v sešitu. Pokud chcete řídit, které sloupce jsou součástí modelu, budete muset k importu dat místo dialogového okna Importovat data v aplikaci Excel použít Průvodce importem tabulky v doplňku Power Pivot.

Když spustíte Průvodce importem tabulky, vyberte tabulky, které chcete importovat.

Průvodce importem tabulky v doplňku PowerPivot

U každé tabulky můžete kliknout na tlačítko Náhled & filtr a vybrat části tabulky, které opravdu potřebujete. Doporučujeme nejdřív zrušit zaškrtnutí všech sloupců a potom pokračovat v kontrole požadovaných sloupců, a to po zvážení, jestli jsou pro analýzu potřeba.

Podokno náhledu v Průvodci importem tabulky

A co filtrování jenom potřebných řádků?

Mnoho tabulek v podnikových databázích a datových skladech obsahuje historická data shromážděná po dlouhou dobu. Kromě toho můžete zjistit, že tabulky, které vás zajímají, obsahují informace o oblastech podniku, které nejsou nutné pro vaši konkrétní analýzu.

Pomocí průvodce importem tabulky můžete vyfiltrovat historická nebo nesouvisející data, a ušetřit tak v modelu velké množství místa. Na následujícím obrázku se pomocí filtru data načítá jenom řádky, které obsahují data pro aktuální rok, s výjimkou historických dat, která nebudou potřeba.

Podokno filtru v Průvodci importem tabulky

Co když potřebujeme sloupec; můžeme ještě snížit náklady na místo?

Existuje několik dalších technik, které můžete použít, aby byl sloupec lepším kandidátem pro kompresi. Nezapomeňte, že jedinou vlastností sloupce, který má vliv na kompresi, je počet jedinečných hodnot. V této části se dozvíte, jak můžete některé sloupce upravit, aby se snížil počet jedinečných hodnot.

Úprava sloupců Datum a čas

V mnoha případech sloupce Datetime zachytá hodně místa. Naštěstí existuje řada způsobů, jak snížit požadavky na úložiště pro tento datový typ. Techniky se budou lišit podle toho, jak sloupec použijete, a na vaší úrovni pohodlí při vytváření SQL dotazů.

Sloupce data a času obsahují část data a čas. Když se ptáte sami sebe, jestli potřebujete sloupec, zeptejte se u sloupce Datetime několikrát na stejnou otázku:

  • Potřebuji časovou část?

  • Potřebuji časovou část na úrovni hodin? , minuty? , Sekundy? , milisekundy?

  • Mám víc sloupců Datum a čas, protože chci vypočítat rozdíl mezi nimi nebo jenom agregovat data podle roku, měsíce, čtvrtletí a tak dále.

Způsob, jakým odpovíte na každou z těchto otázek, určuje možnosti pro řešení sloupce Datum a čas.

Všechna tato řešení vyžadují úpravu SQL dotazu. Pokud chcete usnadnit úpravy dotazů, měli byste vyfiltrovat aspoň jeden sloupec v každé tabulce. Filtrováním sloupce změníte konstrukci dotazu ze zkráceného formátu (SELECT *) na příkaz SELECT, který obsahuje plně kvalifikované názvy sloupců, které se mnohem snadněji upraví.

Podívejme se na dotazy, které jsou pro vás vytvořené. V dialogovém okně Vlastnosti tabulky můžete přepnout do editoru dotazů a zobrazit aktuální SQL dotazu pro každou tabulku.

Pás karet v okně PowerPivot zobrazující příkaz Vlastnosti tabulky

V okně Vlastnosti tabulky vyberte Editor dotazů.

Otevření Editoru dotazů z dialogu Vlastnosti tabulky

Editor dotazů zobrazuje SQL dotaz použitý k vyplnění tabulky. Pokud jste během importu vyfiltroval některý sloupec, dotaz obsahuje plně kvalifikované názvy sloupců:

Dotaz SQL použitý k načtení dat

Naopak pokud jste tabulku naimportoval(a) v celém rozsahu, aniž byste zrušením zaškrtnutí jakéhokoli sloupce nebo použití jakéhokoli filtru, zobrazí se dotaz jako "Vybrat * z", což bude obtížnější změnit:

Dotaz SQL zobrazený pomocí standardní kratší syntaxe

Úprava SQL dotazu

Teď, když víte, jak najít dotaz, můžete ho upravit tak, aby se velikost modelu dál zmenšuje.

  1. Pokud u sloupců obsahujících měnu nebo desetinná data nepotřebujete desetinná místa, použijte tuto syntaxi k odstranění desetinných míst:

    "SELECT ROUND([Decimal_column_name];0)... .”

    Pokud potřebujete centy, ale ne zlomky centů, nahraďte 0 a 2. Pokud používáte záporná čísla, můžete zaokrouhlit na jednotky, desítky, stovky atd.

  2. Pokud máte sloupec Datetime s názvem dbo. Velká tabulka. [Datum a čas] a nepotřebujete část Čas, použijte syntaxi, abyste se času zbavili:

    "SELECT CAST (dbo. Velká tabulka. [Datum a čas] jako datum) AS [Datum a čas]) "

  3. Pokud máte sloupec Datetime s názvem dbo. Velká tabulka. [Datum a čas] a potřebujete části Datum a Čas, použijte více sloupců v SQL místo jednoho sloupce Datum a čas:

    "SELECT CAST (dbo. Velká tabulka. [Datum a čas] jako datum ) AS [Datum a čas],

    datepart(hh, dbo. Velká tabulka. [Datum a čas]) as [Hodiny data a času],

    datepart(mi, dbo. Velká tabulka. [Datum a čas]) as [Minuty data a času],

    datepart(ss, dbo. Velká tabulka. [Datum a čas]) as [Datum a čas Sekundy],

    datepart(ms, dbo. Velká tabulka. [Datum a čas]) as [Milisekundy data a času]"

    Použijte tolik sloupců, kolik potřebujete k uložení jednotlivých částí do samostatných sloupců.

  4. Pokud potřebujete hodiny a minuty a dáváte jim přednost společně jako jeden časový sloupec, můžete použít syntaxi:

    Timefromparts(datepart(hh, dbo. Velká tabulka. [Datum a čas]), datepart(mm, dbo. Velká tabulka. [Datum a čas])) as [Datum a čas HodinaMinute]

  5. Pokud máte dva sloupce data a času, například [Čas zahájení] a [Čas ukončení], a opravdu potřebujete časový rozdíl mezi nimi v sekundách jako sloupec s názvem [Doba trvání], odeberte oba sloupce ze seznamu a přidejte:

    "datediff(ss,[Počáteční datum],[Koncové datum]) jako [Doba trvání]"

    Pokud místo ss použijete klíčové slovo ms, získáte dobu trvání v milisekundách.

Použití počítaných hodnot jazyka DAX místo sloupců

Pokud jste dříve pracovali s jazykem výrazu jazyka JAZYKA DAX, možná už víte, že počítané sloupce se používají k odvození nových sloupců na základě nějakého jiného sloupce v modelu, zatímco počítané míry jsou v modelu definované jednou, ale vyhodnocují se jenom při použití v kontingenční tabulce nebo jiné sestavě.

Jednou z metod ukládání paměti je nahrazení běžných nebo počítaných sloupců počítanou měnou. Klasickým příkladem je Jednotková cena, Množství a Součet. Pokud máte všechny tři, můžete ušetřit místo tak, že zachováte jenom dvě a vypočítáte třetí z nich pomocí jazyka DAX.

Které 2 sloupce byste měli zachovat?

Ve výše uvedeném příkladu mějte množství a jednotkovou cenu. Tito dva mají méně hodnot než Součet. Pokud chcete vypočítat součet, přidejte počítanou míru, třeba:

"TotalSales:=sumx('Sales Table';'Sales Table'[Jednotková cena]*'Tabulka prodejů'[Množství])"

Počítané sloupce jsou jako běžné sloupce, které zachytá místo v modelu. Naopak počítané míry se počítají za běhu a nevyučují mezeru.

Závěr

V tomto článku jsme mluvili o několika přístupech, které vám pomůžou vytvořit model efektivněji využívající paměť. Způsob, jak zmenšit požadavky na velikost souboru a paměť datového modelu, je snížit celkový počet sloupců a řádků a počet jedinečných hodnot, které se zobrazují v jednotlivých sloupcích. Tady jsou některé techniky, které jsme proše mohli krýt:

  • Odebrání sloupců je samozřejmě nejlepší způsob, jak ušetřit místo. Rozhodněte se, které sloupce opravdu potřebujete.

  • Někdy můžete sloupec odebrat a nahradit ho počítanou hodnotou v tabulce.

  • Možná nepotřebujete všechny řádky v tabulce. Řádky můžete vyfiltrovat v Průvodci importem tabulky.

  • Obecně platí, že rozdělení jednoho sloupce na více různých částí je dobrý způsob, jak snížit počet jedinečných hodnot ve sloupci. Každá část bude mít malý počet jedinečných hodnot a kombinovaný součet bude menší než původní sjednocený sloupec.

  • V mnoha případech také potřebujete různé části, které se mají v sestavách používat jako průřezy. V případě potřeby můžete vytvářet hierarchie z částí, jako jsou hodiny, minuty a sekundy.

  • Sloupce kolikrát obsahují víc informací, než potřebujete. Předpokládejme například, že sloupec ukládá desetinná místa, ale u všech desetinných míst jste použili formátování. Zaokrouhlení může být velmi efektivní při zmenšení velikosti číselného sloupce.

Teď, když jste udělali, co jste mohli, abyste zmenšili velikost sešitu, zvažte také spuštění Nástroje pro optimalizaci velikosti sešitu. Ten udělá analýzu excelového sešitu, a pokud je to možné, dál ho zkomprimuje. Stáhněte si Optimalizátor velikosti sešitu.

Související odkazy

Specifikace a limity datového modelu

Optimalizace velikosti sešitu

PowerPivot: Výkonné analýzy a modelování dat v Excelu

Potřebujete další pomoc?

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojit se systém Microsoft Office insiderům

Byly tyto informace užitečné?

Jak jste spokojeni s kvalitou jazyka?
Co ovlivnilo váš názor?

Děkujeme vám za zpětnou vazbu.

×