V Excelu můžete vytvořit datové modely obsahující miliony řádků a pak s těmito modely provádět výkonné analýzy dat. Datové modely je možné vytvořit pomocí doplňku Power Pivot nebo bez něj, aby bylo možné podporovat libovolný počet kontingenčních tabulek, grafů a vizualizací power view ve stejném sešitu.
I když v Excelu můžete snadno vytvářet velké datové modely, existuje několik důvodů, proč to neuděláte. Za prvé, velké modely, které obsahují velké množství tabulek a sloupců, jsou pro většinu analýz příliš náročné a představují těžkopádný seznam polí. Za druhé velké modely využívají cennou paměť, což negativně ovlivňuje ostatní aplikace a sestavy, které sdílejí stejné systémové prostředky. Nakonec v Microsoft 365 omezují SharePoint Online i Excel Web App velikost excelového souboru na 10 MB. U datových modelů sešitů, které obsahují miliony řádků, narazíte na limit 10 MB poměrně rychle. Viz Specifikace a omezení datového modelu.
V tomto článku se dozvíte, jak vytvořit pevně vytvořený model, se kterým se snadněji pracuje a využívá méně paměti. Když si uděláte čas na seznámení s osvědčenými postupy v oblasti efektivního návrhu modelu, vyplatí se vám každý model, který vytvoříte a použijete, ať už si ho prohlížíte v Excelu, Microsoft 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 Optimalizátor velikosti sešitu.
V tomto článku
Kompresní poměry a analytický modul v paměti
Datové modely v Excelu používají k ukládání dat v paměti analytický modul v paměti. Modul implementuje výkonné techniky komprese, které snižují požadavky na úložiště a zmenšují 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 vzniku. Pokud například importujete 7 MB dat z databáze SQL Serveru, datový model v Excelu může mít snadno velikost 1 MB nebo méně. Míra skutečně dosažené 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 o maximalizaci komprese a nejjednodušší způsob, jak to udělat, je zbavit se 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ě pro jednotlivé sloupce 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 věnovaná optimalizaci data a času se této technice podrobně věnuje.
Kvůli nízkému využití paměti nic nepřekoná neexistující sloupec.
Nejefektivněji využívající paměť je sloupec, který jste nikdy neimportovali. Pokud chcete vytvořit efektivní model, podívejte se na jednotlivé sloupce a zeptejte se sami sebe, jestli přispívá k analýze, kterou chcete provést. Pokud ne nebo si nejste jistí, vynechte to. Nové sloupce můžete kdykoliv přidat později, pokud je budete potřebovat.
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čítají a aktualizují data ve skladu. Sloupce jako "datum vytvoření", "datum aktualizace" a "spuštění ETL" se vytvoří při načtení dat. Žádný z těchto sloupců není v modelu potřeba a při importu dat by měl být zrušen výběr.
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 o zákaznících, zaměstnancích nebo prodejích, budete potřebovat primární klíč tabulky, abyste ho mohli použít k vytváření relací v modelu.
Tabulky faktů se liší. V tabulce faktů se primární klíč používá k jedinečné identifikaci každého řádku. I když je to nezbytné pro účely normalizace, je méně užitečné v datovém modelu, kde chcete použít pouze 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 její primární klíč. Primární klíče v tabulce faktů spotřebovávají v modelu obrovské množství místa, ale neposkytují žádné výhody, 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í převážně z číselných dat často označují jako "tabulky faktů". Tabulky faktů obvykle zahrnují data o výkonu firmy nebo transakcích, jako jsou datové body prodeje a nákladů, které jsou agregované a sladěné s organizačními jednotkami, produkty, segmenty trhu, geografickými oblastmi atd. Všechny sloupce v tabulce faktů, které obsahují obchodní data nebo které je možné použít k křížovému odkazu na data uložená v jiných tabulkách, by měly být zahrnuté 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í pouze v tabulce faktů a nikde jinde. Vzhledem k tomu, že tabulky faktů jsou tak obrovské, jsou některé z největších výhod v efektivitě 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 v sešitu potřebovat. Pokud chcete určit, které sloupce jsou součástí modelu, budete muset k importu dat použít Průvodce importem tabulky v doplňku Power Pivot místo dialogového okna Importovat data v Excelu.
Při spuštění Průvodce importem tabulky vyberete, které tabulky se mají importovat.
U každé tabulky můžete kliknout na tlačítko Náhled & filtr a vybrat části tabulky, které opravdu potřebujete. Doporučujeme nejprve zrušit zaškrtnutí všech sloupců a potom pokračovat ve kontrole požadovaných sloupců po zvážení, jestli jsou pro analýzu potřeba.
A co filtrování jenom potřebných řádků?
Mnoho tabulek v podnikových databázích a datových skladech obsahuje historická data nashromážděná za dlouhá časová období. Kromě toho můžete zjistit, že tabulky, které vás zajímají, obsahují informace pro oblasti podnikání, které nejsou potřeba 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 spoustu místa v modelu. Na následujícím obrázku se filtr kalendářních dat používá k načtení jenom řádků, které obsahují data pro aktuální rok, s výjimkou historických dat, která nebudou potřeba.
Co kdybychom potřebovali sloupec; můžeme přesto snížit jeho náklady na prostor?
Existuje několik dalších technik, které můžete použít k vytvoření sloupce jako lepšího kandidáta 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 některé sloupce upravit, abyste snížili počet jedinečných hodnot.
Úprava sloupců datetime
V mnoha případech zabírají sloupce Datetime hodně místa. Naštěstí existuje několik způsobů, jak snížit požadavky na úložiště pro tento datový typ. Techniky se budou lišit v závislosti na tom, jak sloupec používáte, a na vaší úrovni pohodlí při vytváření dotazů SQL.
Sloupce Datetime obsahují část data a čas. Když se sami sebe zeptáte, jestli potřebujete sloupec, položte stejnou otázku několikrát pro sloupec Datetime:
-
Potřebuji časovou část?
-
Potřebuji časovou část na úrovni hodin? minuty? Sekundy? milisekundy?
-
Mám více sloupců Datetime, protože chci vypočítat rozdíl mezi nimi, nebo jenom agregovat data podle roku, měsíce, čtvrtletí atd.
Způsob odpovědi na každou z těchto otázek určuje, jaké máte možnosti pro práci se sloupcem Datetime.
Všechna tato řešení vyžadují úpravu dotazu SQL. Pokud chcete usnadnit úpravy dotazů, měli byste v každé tabulce vyfiltrovat alespoň jeden sloupec. Filtrováním sloupce změníte strukturu dotazu ze zkráceného formátu (SELECT *) na příkaz SELECT, který obsahuje plně kvalifikované názvy sloupců, které se dají mnohem snadněji upravit.
Pojďme se podívat 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 dotazů.
Editor dotazů zobrazí dotaz SQL použitý k naplnění tabulky. Pokud jste během importu vyfiltrovali libovolný sloupec, dotaz obsahuje plně kvalifikované názvy sloupců:
|
Naopak pokud jste importovali celou tabulku bez zrušení zaškrtnutí sloupce nebo použití filtru, zobrazí se dotaz "Select * from" (Vybrat * z), což bude obtížnější:
|
Úprava dotazu SQL
Teď, když víte, jak dotaz najít, můžete ho upravit a zmenšit tak velikost modelu.
-
Pokud u sloupců obsahujících měnu nebo desetinná čísla nepotřebujete, použijte k odstranění desetinných míst tuto syntaxi:
"SELECT ROUND([Decimal_column_name],0)... .”
Pokud potřebujete centy, ale ne zlomky centů, nahraďte 0 2. Pokud použijete záporná čísla, můžete zaokrouhlit na jednotky, desítky, stovky atd.
-
Pokud máte sloupec Datetime s názvem dbo. Bigtable. [Datum a čas] a část Čas nepotřebujete, použijte syntaxi, abyste se zbavili času:
"SELECT CAST (dbo. Bigtable. [Datum a čas] jako datum) AS [Datum a čas]) "
-
Pokud máte sloupec Datetime s názvem dbo. Bigtable. [Datum a čas] a potřebujete část Datum a Čas, použijte v dotazu SQL více sloupců místo jednoho sloupce Datetime:
"SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. Bigtable. [Datum a čas]) jako [Hodiny data a času],
datepart(mi, dbo. Bigtable. [Datum a čas]) jako [Date Time Minutes],
datepart(ss, dbo. Bigtable. [Datum a čas]) jako [Datum a čas v sekundách],
datepart(ms, dbo. Bigtable. [Datum a čas]) jako [Datum a čas milisekund]"
K uložení každé části v samostatných sloupcích použijte tolik sloupců, kolik potřebujete.
-
Pokud potřebujete hodiny a minuty a dáváte přednost dohromady jako jeden časový sloupec, můžete použít syntaxi :
Timefromparts(datepart(hh, dbo. Bigtable. [Datum a čas]), datepart(mm, dbo. Bigtable. [Datum a čas])) as [Date Time HourMinute]
-
Pokud máte dva sloupce datetime, například [Počáteční čas] a [Koncový čas], a co opravdu potřebujete, je časový rozdíl mezi nimi v sekundách ve sloupci s názvem [Doba trvání], odeberte oba sloupce ze seznamu a přidejte:
"datediff(ss,[Počáteční datum],[Koncové datum]) as [Duration]"
Pokud místo ss použijete klíčové slovo ms, získáte dobu trvání v milisekundách.
Použití počítaných měr DAX místo sloupců
Pokud jste dříve pracovali s jazykem výrazů DAX, možná už víte, že počítané sloupce se používají k odvození nových sloupců na základě některého jiného sloupce v modelu, zatímco počítané míry jsou v modelu definovány 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čítanými mírami. Klasickým příkladem je Unit Price (Jednotková cena), Quantity (Množství) a Total (Celkem). Pokud máte všechny tři, můžete ušetřit místo tím, že ponecháte jen dva 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 ponechte Množství a Jednotková cena. Tyto dvě mají míň hodnot než součet. Pokud chcete vypočítat součet, přidejte počítanou míru, například:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Počítané sloupce jsou jako běžné sloupce v tom, že oba zabírají místo v modelu. Naproti tomu počítané míry se počítají za běhu a nezabírají místo.
Závěr
V tomto článku jsme mluvili o několika přístupech, které vám můžou pomoct vytvořit model efektivnější z hlediska paměti. Způsob, jak snížit požadavky datového modelu na velikost souborů a paměť, je snížit celkový počet sloupců a řádků a počet jedinečných hodnot zobrazených v každém sloupci. Zde jsou některé techniky, které jsme probrali:
-
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 mírou v tabulce.
-
Možná nebudete potřebovat všechny řádky v tabulce. Řádky můžete vyfiltrovat v Průvodci importem tabulky.
-
Obecně platí, že rozdělení jednoho sloupce do více jedinečný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 sloučený součet bude menší než původní sjednocený sloupec.
-
V mnoha případech potřebujete také jedinečné části, které se budou používat jako průřezy v sestavách. V případě potřeby můžete vytvořit hierarchie z částí, jako jsou Hodiny, Minuty a Sekundy.
-
Častokrát obsahují sloupce více informací, než potřebujete. Předpokládejme například, že sloupec obsahuje desetinná místa, ale použili jste formátování ke skrytí všech desetinných míst. Zaokrouhlování může být velmi efektivní při zmenšení velikosti číselného sloupce.
Teď, když jste udělali vše, co můžete, abyste zmenšili velikost sešitu, zvažte také spuštění Optimalizátoru 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
