V Excelu můžete vytvářet datové modely obsahující miliony řádků a potom na základě těchto modelů provádět výkonné analýzy dat. Datové modely je možné vytvářet s doplňkem Power Pivot nebo bez něj a podporovat tak libovolný počet kontingenčních tabulek, grafů a vizualizací Power View ve stejném sešitu.
I když můžete v Excelu snadno vytvářet obrovské datové modely, existuje několik důvodů, proč to nedělat. Za prvé, velké modely, které obsahují velké množství tabulek a sloupců, jsou pro většinu analýz přehnané a představují těžkopádný seznam polí. Za druhé, velké modely spotřebovávají cennou paměť, což negativně ovlivňuje další aplikace a sestavy, které sdílejí stejné systémové prostředky. V Microsoftu 365 omezují SharePoint Online i Excel Web App velikost excelového souboru na 10 MB. U datových modelů sešitu, které obsahují miliony řádků, velmi rychle narazíte na limit 10 MB. Podívejte se na specifikace a omezení datového modelu.
V tomto článku se dozvíte, jak vytvořit pevně sestavený model, se kterým se snadněji pracuje a využívá méně paměti. Věnujte čas seznámení se s osvědčenými postupy efektivního návrhu modelů a u každého modelu, který vytvoříte a používáte, ať už si ho prohlížíte v Excelu, Microsoftu 365 SharePointu Online, na Office Web Apps Serveru nebo na SharePointu.
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 nástroj Workbook Size Optimizer.
V tomto článku
Kompresní poměry a modul pro analýzu v paměti
Datové modely v Excelu používají k ukládání dat do paměti modul analýzy v paměti. Modul implementuje výkonné kompresní techniky ke snížení požadavků na úložiště a zmenšuje sadu výsledků na 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ě svého vzniku. Pokud například importujete 7 MB dat z databáze serveru SQL Server, datový model v Excelu může snadno obsahovat 1 MB nebo méně. Skutečně dosažený stupeň komprese závisí především na počtu jedinečných hodnot v každém sloupci. Čím více jedinečných hodnot, tím více paměti je potřeba k jejich uložení.
Proč mluvíme o kompresi a jedinečných hodnotách? Protože vytvoření efektivního modelu, který minimalizuje využití paměti, je především o maximalizaci komprese a nejjednodušší způsob, jak toho dosáhnout, je zbavit se všech sloupců, které ve skutečnosti nepotřebujete, zejména pokud tyto sloupce obsahují velký počet jedinečných hodnot.
Poznámka
Rozdíly v požadavcích na úložiště jednotlivých sloupců můžou 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 než jeden sloupec s vysokým počtem jedinečných hodnot. Část o optimalizacích data a času se touto technikou zabývá podrobněji.
Nic nepřekoná neexistující sloupec pro nízké využití paměti
Sloupec, který nejefektivněji využívá paměť, je ten, který jste nikdy neimportovali. Pokud chcete vytvořit efektivní model, podívejte se na každý sloupec a zeptejte se, jestli přispívá k analýze, kterou chcete provést. Pokud ne nebo si nejste jistí, text vynechejte. Nové sloupce můžete podle potřeby přidat později.
Dva příklady sloupců, které byste měli vždy vyloučit
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čítají a aktualizují data ve skladu. Při načtení dat se vytvoří sloupce jako "datum vytvoření", "datum aktualizace" a "spuštění ETL". Žádný z těchto sloupců není v modelu potřeba a při importu dat byste měli jejich výběr zrušit.
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, třeba těch, které obsahují údaje o zákaznících, zaměstnancích nebo prodejích, budete potřebovat primární klíč tabulky, abyste ho mohli použít k vytvoření relací v modelu.
Tabulky faktů jsou jiné. V tabulce faktů slouží primární klíč k jednoznačné identifikaci každého řádku. I když je nezbytná pro účely normalizace, je méně užitečná v datovém modelu, kde chcete pro analýzu nebo vytvoření relací mezi tabulkami použít jenom ty sloupce. Z tohoto důvodu při importu z tabulky faktů neuvádějte její primární klíč. Primární klíče v tabulce faktů spotřebovávají obrovské množství místa v modelu, ale neposkytují žádný užitek, protože je nelze použít k vytvoření relací.
Poznámka
V datových skladech a multidimenzionálních databázích se velké tabulky, které se skládají převážně z číselných dat, často označují jako "tabulky faktů". Tabulky faktů obvykle obsahují údaje o obchodních výsledcích nebo transakcích, například body dat o prodejích a nákladech, které jsou agregované a zarovnané podle organizačních jednotek, produktů, segmentů trhu, geografických oblastí atd. V zájmu podpory analýzy dat by měly být do modelu zahrnuty všechny sloupce v tabulce faktů, které obsahují obchodní data nebo které lze použít ke křížovým odkazům na data uložená v jiných tabulkách. 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. Protože jsou tabulky faktů obrovské, některé z největších zvýšení efektivity modelu plynou z vyloučení řádků nebo sloupců z tabulek faktů.
Jak vyloučit nadbytečné sloupce
Efektivní modely obsahují jenom ty sloupce, které budete v sešitu skutečně potřebovat. Pokud chcete určit, které sloupce budou do modelu zahrnuté, budete muset k importu dat použít Průvodce importem tabulky v doplňku PowerPivot , ne dialogové okno Importovat data v Excelu.
Po spuštění Průvodce importem tabulky vyberte tabulky, které chcete importovat.
U každé tabulky můžete kliknout na tlačítko Náhled & filtr a vybrat části tabulky, které skutečně potřebujete. Doporučujeme nejdříve zrušit zaškrtnutí všech sloupců a potom přistoupit ke kontrole požadovaných sloupců po zvážení, jestli jsou tyto sloupce nutné pro analýzu.
Co takhle vyfiltrovat jenom nezbytné řádky?
Mnoho tabulek v podnikových databázích a datových skladech obsahuje historická data nahromaděná za dlouhá časová období. Navíc můžete zjistit, že tabulky, které vás zajímají, obsahují informace z oblastí podniku, které nejsou pro vaši konkrétní analýzu nutné.
Pomocí Průvodce importem tabulky můžete odfiltrovat historická nebo nesouvisející data a ušetřit tak spoustu místa v modelu. Na následujícím obrázku je pomocí filtru dat načteno jenom řádky, které obsahují data pro aktuální rok, s výjimkou historických dat, která nebudou potřeba.
Co když potřebujeme sloupec; Můžeme ještě snížit jeho náklady na prostor?
Existuje několik dalších technik, které můžete použít, aby byl sloupec vhodnějším kandidátem na kompresi. Mějte na paměti, že jedinou charakteristikou sloupce, která ovlivňuje kompresi, je počet jedinečných hodnot. V této části se dozvíte, jak je možné některé sloupce upravit a omezit tak počet jedinečných hodnot.
Úprava sloupců data a času
Sloupce Datum a čas často zabírají hodně místa. Naštěstí existuje řada způsobů, jak požadavky na úložiště pro tento typ dat snížit. Techniky se budou lišit v závislosti na tom, jak sloupec používáte a jaká je vaše úroveň pohodlí při vytváření dotazů SQL.
Sloupce Datum a čas obsahují část kalendářního data a času. Když si položíte otázku, jestli potřebujete sloupec, položte u sloupce Datum a čas stejnou otázku několikrát:
- Potřebuju časovou část?
- Potřebuji časovou část na úrovni hodin? , minut? , sekundy? , milisekundy?
- Mám víc sloupců data a času, protože chci vypočítat rozdíl mezi nimi, nebo jenom agregovat data podle roku, měsíce, čtvrtletí atd.
Způsob, jakým na každou z těchto otázek odpovíte, určuje, jakým způsobem naložíte se sloupcem Datum a čas.
Všechna tato řešení vyžadují úpravu dotazu SQL. Pokud chcete usnadnit úpravy dotazů, měli byste v každé tabulce odfiltrovat aspoň jeden sloupec. Vyfiltrová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 dají daleko snadněji upravovat.
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í dotaz SQL pro každou tabulku.
Ve vlastnostech tabulky vyberte Editor Power Query.
Editor Power Query zobrazí dotaz SQL použitý k naplnění tabulky. Pokud jste během importu některý sloupec vyfiltrovali, bude dotaz obsahovat plně kvalifikované názvy sloupců:
Pokud jste naopak importovali tabulku jako celek a nezrušíte zaškrtnutí libovolného sloupce nebo nepoužijete filtr, zobrazí se dotaz jako "Vybrat * z", který se bude upravovat obtížněji:
|
|---|
Úprava dotazu SQL
Když už víte, jak dotaz najít, můžete ho upravit a velikost modelu dále zmenšit.
- Pokud u sloupců obsahujících desetinná čísla nebo měnová data nepotřebujete, zbavte se těchto desetinných míst pomocí této syntaxe:
"SELECT ROUND([Decimal_column_name],0)... .”
Pokud potřebujete centy, ale ne zlomky centů, nahraďte 0 číslem 2. Pokud používáte záporná čísla, můžete je zaokrouhlit na jednotky, desítky, stovky atd. - Pokud máte sloupec Datum a čas s názvem dbo. Bigtable. [Datum a čas] a nepotřebujete část Čas, použijte syntaxi, abyste se zbavili času:
"SELECT CAST (dbo. Bigtable. [Datum a čas] jako datum) AS [Datum a čas]) " - Pokud máte sloupec Datum a čas s názvem dbo. Bigtable. [Datum a čas] a potřebujete obě části obsahující datum i čas, použijte v dotazu SQL více sloupců místo jednoho sloupce Datum a čas:
"SELECT CAST (dbo. Bigtable. [Datum a čas] jako datum ) AS [Datum a čas],
DatePart(hh, dbo. Bigtable. [Datum a čas]) as [Datum a Hodiny],
DatePart(mi, dbo. Bigtable. [Datum a čas]) as [Datum Čas Minuty],
DatePart(SS, DBo. Bigtable. [Datum a čas]) as [Datum a Sekundy],
DatePart (ms, dbo. Bigtable. [Datum a čas]) as [Datum Čas Milisekundy]"
Použijte tolik sloupců, kolik potřebujete, abyste každou část uložili do samostatných sloupců. - Pokud potřebujete hodiny a minuty a dáváte přednost tomu, aby společně tvořily jeden časový sloupec, můžete použít syntaxi:
Timefromparts(datepart(hh, dbo. Bigtable. [Datum a čas]), DatePart(mm, dbo. Bigtable. [Datum a čas])) as [Datum Čas HodinaMinuta] - Pokud máte dva sloupce data a času, třeba [Počáteční čas] a [Koncový čas], ale ve skutečnosti 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]) as [Trvání]"
Pokud použijete klíčové slovo ms místo ss, zobrazí se doba trvání v milisekundách
Použití počítaných měr jazyka DAX místo sloupců
Pokud jste už s jazykem výrazů jazyka DAX pracovali, asi víte, že počítané sloupce slouží k odvození nových sloupců založených na některém jiném sloupci modelu, zatímco počítané míry jsou v modelu definované jednou, ale vyhodnocují se jen při použití v kontingenční tabulce nebo jiné sestavě.
Jednou z technik, jak ušetřit paměť, je nahradit běžné nebo počítané sloupce počítanými mírami. Klasickým příkladem je Jednotková cena, Množství a Celkem. Pokud máte všechny tři možnosti, můžete ušetřit místo tím, že budete udržovat jenom dvě z nich a třetí budete počítat pomocí jazyka DAX.
Které 2 sloupce byste měli zachovat?
V předchozím příkladu ponechte Mnozstvi a Jednotková cena. Tyto dvě položky mají méně hodnot než Součet. Pokud chcete vypočítat Součet, přidejte počítanou míru, například:
"TotalSales:=sumx('Tabulka prodejů','Tabulka prodejů'[Jednotková cena]*'Tabulka prodejů'[Množství])"
Počítané sloupce podobně jako běžné sloupce zabírají v modelu místo. Počítané míry se naproti tomu počítají průběžně a nezabírají žádné místo.
Závěr
V tomto článku jsme hovořili o několika přístupech, které vám můžou pomoct vytvořit model efektivnější z hlediska paměti. Způsob, jak zmenšit velikost souboru a paměťové požadavky datového modelu, je zmenšení celkového počtu sloupců a řádků a počtu jedinečných hodnot, které se v každém sloupci objevují. Tady je několik technik, které jsme probrali:
- Odebrání sloupců je samozřejmě nejlepší způsob, jak ušetřit místo. Rozhodněte se, které sloupce skutečně potřebujete.
- Někdy můžete odebrat sloupec a nahradit ho počítanou mírou z tabulky.
- Možná nebudete potřebovat všechny řádky v tabulce. Řádky můžete odfiltrovat v Průvodci importem tabulky.
- Obecně platí, že rozdělení jednoho sloupce na několik jedinečných částí je dobrý způsob, jak omezit počet jedinečných hodnot ve sloupci. Každá část bude obsahovat malý počet jedinečných hodnot a celkový součet bude menší než původní jednotný sloupec.
- V mnoha případech také potřebujete odlišné části, které použijete jako průřezy v sestavách. V případě potřeby můžete vytvářet hierarchie z částí, jako jsou hodiny, minuty a sekundy.
- Sloupce také často obsahují více informací, než je potřeba. Předpokládejme například, že ve sloupci jsou uloženy desetinné hodnoty, ale vy jste použili formátování ke skrytí všech desetinných míst. Zaokrouhlení může být velmi účinné při zmenšování velikosti číselného sloupce.
Teď, když jste zmenšili velikost sešitu, 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 nástroj Workbook Size Optimizer.