Vytvoření datového modelu efektivně využívajícího paměť pomocí Excelu a doplňku Power Pivot

V Excelu 2013 nebo novějším můžete vytvářet datové modely obsahující milióny řádků a pak provádět výkonné analýzy dat oproti těmto modelům. Datové modely se dají vytvářet s použitím doplňku Power Pivot nebo bez něj pro podporu jakéhokoli počtu kontingenčních tabulek, grafů a vizualizací v jednom sešitu.

Poznámka: Tento článek popisuje datové modely v Excelu 2013. Stejné funkce pro modelování dat a PowerPivot, které jsou představené v Excelu 2013, se ale týkají také Excelu 2016. V těchto verzích Excelu je skutečně málo rozdílů.

Ačkoli v Excelu můžete snadno vytvářet velké datové modely, může to být z několika důvodů. První, velké modely, které obsahují mnoho tabulek a sloupců, jsou Overkill pro většinu analýz a pro seznam nenáročných polí. Za druhé, velké modely používají paměť, negativně ovlivňují jiné aplikace a sestavy, které sdílejí stejné systémové prostředky. V Microsoft 365 v aplikaci SharePoint Online a Excel Web App limit velikosti excelového souboru na 10 MB Pro datové modely sešitu, které obsahují milióny řádků, se můžete rychle připojit k 10 MB. Viz specifikace a limity datového modelu.

V tomto článku se dozvíte, jak vytvořit těsně konstruovaný model, se kterým pracujete s menším množstvím paměti. Pokud si chcete vyučit osvědčené postupy v návrhu efektivního modelu, využijte možnosti pro jakýkoli model, který vytvoříte a použijete, ať už ho prohlížíte v Excelu 2013, Microsoft 365 SharePointu Online, na Office Web Apps serveru nebo v SharePointu 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 optimalizaci velikosti sešitu.

V tomto článku

Kompresní poměry a modul analýzy v paměti

Žádný nevyrovná neexistující sloupec pro nedostatek paměti

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

Vyloučení zbytečných sloupců

Co filtrování jenom nezbytných řádků?

Co když sloupec potřebujeme; stále můžeme snížit své náklady na prostor?

Úprava sloupců DateTime

Úprava dotazu SQL

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

Které 2 sloupce byste měli zachovat?

Závěr

Související odkazy

Kompresní poměry a modul analýzy v paměti

Datové modely v Excelu používají k ukládání dat do paměti modul analýzy v paměti. Tento modul implementuje výkonné kompresní techniky, aby se snížily požadavky na úložiště, a to až do zlomku původní velikosti.

V průměru můžete datový model očekávat od 7 do 10 krát menší než stejná data ve svém bodě původu. Pokud například importujete 7 MB dat z databáze SQL serveru, může být datový model v Excelu příliš 1 MB nebo menší. Skutečně dosažený stupeň komprese závisí primárně na počtu jedinečných hodnot v jednotlivých sloupcích. Víc jedinečných hodnot, tím více paměti je potřeba k jejich uložení.

Proč hovoříme o kompresi a jedinečných hodnotách? Protože je vytvářen efektivní model, který minimalizuje využití paměti, vše o maximálním množství komprese a nejjednodušším způsobem, jak to udělat, je odstranit všechny sloupce, které nepotřebujete, obzvláště 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 velké. V některých případech je vhodnější mít více sloupců s nízkým počtem jedinečných hodnot, místo jednoho sloupce s vysokým počtem jedinečných hodnot. Oddíl s optimalizací DateTime podrobně pokrývá tento postup.

Žádný nevyrovná neexistující sloupec pro nedostatek paměti

Nejvíce efektivního sloupce paměti je ten, který jste nikdy neimportovali. Pokud chcete vytvořit efektivní model, podívejte se na jednotlivé sloupce a zeptejte se sami na to, jestli přispívá k analýze, kterou chcete provést. Pokud ne nebo si nejste jistí, nechte ho. Nové sloupce můžete kdykoli přidat později, když je potřebujete.

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

První příklad se vztahuje k datům, která pochází 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 vytvářejí sloupce jako "vytvořit datum", "Datum aktualizace" a "ETL Run". V modelu není potřeba žádný z těchto sloupců a při importu dat by měl být oddaný.

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

Mnoho tabulek, včetně tabulek faktů, má primární klíče. U většiny tabulek, jako jsou ty, které obsahují údaje o zákaznících, zaměstnancích nebo prodejích, budete chtít primární klíč tabulky použít k vytvoření relací v modelu.

Tabulky faktů se liší. V tabulce faktů se primární klíč používá k jednoznačné identifikaci každého řádku. V případě potřeby je to pro účely normalizace méně užitečné v datovém modelu, kde chcete mít jenom sloupce, které se používají k analýze, nebo vytvářet relace mezi tabulkami. Z tohoto důvodu nepoužívejte při importu z tabulky fakt primární klíč. Primární klíče v tabulce faktů spotřebovávají velké množství prostoru v modelu a neposkytují žádné výhody, protože nemohou být použity k vytvoření relací.

Poznámka:  V datových skladech a multidimenzionálních databázích se velké tabulky skládající se z převážně číselných dat často označují jako "tabulky faktů". Tabulky faktů obvykle zahrnují výkon nebo data transakcí, jako jsou například prodejní a nákladové body, které jsou agregované a zarovnané na organizační jednotky, produkty, segmenty na trhu, geografické oblasti atd. Do modelu pro podporu analýzy dat by měly být zahrnuty všechny sloupce v tabulce faktů, které obsahují obchodní data nebo které lze použít k 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ý obsahuje jedinečné hodnoty, které existují pouze v tabulce faktů, a pustiny. Vzhledem k tomu, že jsou tabulky faktů tak velké, některé z největších zisků v modelu efektivity jsou odvozeny z vyloučení řádků nebo sloupců z tabulek faktů.

Vyloučení zbytečných sloupců

Efektivní modely obsahují jenom ty sloupce, které ve vaší práci skutečně potřebujete. Chcete-li určit, které sloupce budou do modelu zahrnuty, budete muset v doplňku Power Pivot použít Průvodce importem tabulek, a ne data v dialogovém okně Importovat data.

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

Průvodce importem tabulky v doplňku PowerPivot

U každé tabulky můžete kliknout na tlačítko Filtr & filtrovat a vybrat části tabulky, které opravdu potřebujete. Doporučujeme, abyste nejprve nezaškrtli políčko všechny sloupce, a potom přejděte ke kontrole požadovaných sloupců a po zvážení toho, jestli jsou potřebné pro analýzu.

Podokno náhledu v Průvodci importem tabulky

Co filtrování jenom nezbytných řádků?

Mnoho tabulek v podnikových databázích a datových skladech obsahuje historické údaje shromážděné po dlouhou dobu. Navíc můžete zjistit, že tabulky, které vás zajímají, obsahují informace o oblastech podnikání nepotřebných pro vaši specifickou analýzu.

Pomocí Průvodce importem tabulky můžete odfiltrovat historické nebo nesouvisející data a ušetřit tak hodně prostoru v modelu. Na následujícím obrázku se pomocí filtru data načítají jenom řádky, které obsahují data pro aktuální rok, bez historických dat, která nepotřebujete.

Podokno filtru v Průvodci importem tabulky

Co když sloupec potřebujeme; stále můžeme snížit své náklady na prostor?

K dispozici je několik dalších postupů, které můžete použít pro lepší kandidáty na sloupec. Uvědomte si, že jedinou vlastností sloupce, který ovlivňuje kompresi, je počet jedinečných hodnot. V této části se dozvíte, jak můžete změnit některé sloupce a snížit tak počet jedinečných hodnot.

Úprava sloupců DateTime

V mnoha případech jsou ve sloupcích DateTime mnoho místa. Naštěstí, existuje řada způsobů, jak omezit požadavky na úložiště pro tento datový typ. Postupy se budou lišit v závislosti na tom, jak se sloupec používá, a na úrovni pohodlí při vytváření dotazů SQL.

Sloupce DateTime obsahují část datum a čas. Když se zeptáte sami, jestli potřebujete sloupec, položte stejnou otázku pro sloupec DateTime víckrát:

  • Potřebuji časovou část?

  • Potřebuji časovou část na úrovni hodin? minut? Několik? dosáhne?

  • Mám více sloupců DateTime, protože chci počítat rozdíl mezi nimi, nebo jenom agregovat data podle roků, měsíců, čtvrtletí atd.

Způsob, jakým každý z těchto otázek přijmete, určí možnosti pro práci se sloupcem DateTime.

Všechna tato řešení vyžadují změnu SQL dotazu. Aby se změna dotazu usnadnila, je vhodné filtrovat alespoň jeden sloupec v každé tabulce. Filtrováním sloupce změníte stavbu dotazu ze zkráceného formátu (SELECT *) na příkaz SELECT, který obsahuje plně kvalifikované názvy sloupců, které se mnohem snáze upravují.

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

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

V seznamu Vlastnosti tabulky vyberte Editor dotazů.

Otevření Editoru dotazů z dialogu Vlastnosti tabulky

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

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

Pokud naopak naimportujete tabulku, aniž byste museli zrušit zaškrtnutí jakéhokoli sloupce nebo použili filtr, zobrazí se dotaz jako "SELECT * FROM", který bude složitější změnit:

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

Úprava dotazu SQL

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

  1. Pokud pro sloupce obsahující data typu Měna nebo desetinné číslo nepotřebujete tyto desetinná čísla, použijte k odstranění desetinných míst tuto syntaxi:

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

    Pokud potřebujete centy, ale ne zlomků, nahraďte 0 hodnotou 2. Pokud používáte záporná čísla, můžete se zaokrouhlit na jednotky, číslice, stovky atd.

  2. Pokud máte sloupec DateTime s názvem dbo. BigTable. [Datum čas] a nepotřebujete čas, použijte syntaxi k odstranění času:

    Vyberte CAST (dbo. BigTable. [Datum čas] as Date) AS [datum čas]) "

  3. Pokud máte sloupec DateTime s názvem dbo. BigTable. [Datum čas] a potřebujete obě části data a času, místo jednoho sloupce DateTime použijte více sloupců v SQL dotazu:

    Vyberte CAST (dbo. BigTable. [Datum čas] as Date) AS [datum čas];

    DatePart (hh, dbo. BigTable. [Datum čas]) as [datum čas (hodiny);

    DatePart (mi, dbo. BigTable. [Datum čas]) as [datum čas, minuty],

    DatePart (SS, dbo. BigTable. [Datum čas]) jako [datum čas sekundy],

    DatePart (MS, dbo. BigTable. [Datum čas]) as [datum čas milisekundy] "

    K uložení jednotlivých částí do samostatných sloupců použijte tolik sloupců.

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

    Timefromparts (DatePart (hh; dbo. BigTable. [Datum čas]), DatePart (mm, dbo. BigTable. [Datum čas]) as [datum čas Hodinyminuty]

  5. Pokud máte dva sloupce DateTime, jako je třeba [čas zahájení], [čas ukončení] a co opravdu potřebujete, je č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 [doba trvání]"

    Pokud použijete klíčové slovo MS místo SS, bude doba trvání v milisekundách

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

Pokud jste předtím pracovali s jazykem výrazů jazyka DAX, můžete už vědět, že počítané sloupce se používají k odvození nových sloupců na základě jiného sloupce v modelu, zatímco počítané míry se v modelu definují jednou, ale vyhodnocuje se jenom při použití v Kontingenční tabulka nebo jiná sestava

Jedním z paměťových technik je nahradit normální nebo počítané sloupce pomocí počítaných měr. Klasický příklad je jednotková cena, množství a součet. Pokud máte všechno tři, můžete ušetřit místo tak, že zachová jenom dvě a vypočítáte třetí část pomocí DAX.

Které 2 sloupce byste měli zachovat?

Ve výše uvedeném příkladu zachovat množství a Jednotková cena. Tyto dva mají méně hodnot, než je součet. Chcete-li vypočítat součet, přidejte počítanou míru:

"ProdejCelkem: = SUMX (' tabulka prodejů ', ' tabulka prodejů ' [Jednotková cena] * ' tabulka prodejů ' [množství])

Počítané sloupce jsou jako běžné sloupce v tomto modelu. Naopak počítané míry se počítají v průběhu a neberou se místo toho.

Závěr

V tomto článku jsme mluvíte o několika možnostech, které vám pomůžou vytvořit další model efektivní paměti. Způsob, jak zmenšit velikost souboru a požadavky na paměť datového modelu, je snížit celkový počet sloupců a řádků a počet jedinečných hodnot zobrazených v jednotlivých sloupcích. Tady jsou některé techniky, které jsme poznamenali:

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

  • Někdy můžete sloupec odebrat a nahradit ho počítaným rozměrem v tabulce.

  • Je možné, že nebudete potřebovat všechny řádky v tabulce. V Průvodci importem tabulky můžete filtrovat řádky.

  • Obecně je rozdělení jednoho sloupce na více samostatných částí vhodné pro snížení počtu jedinečných hodnot ve sloupci. Každá z částí bude mít malý počet jedinečných hodnot a celkový součet bude menší než původní sjednocený sloupec.

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

  • Sloupce budou obsahovat více informací, než je potřebujete. Předpokládejme například, že sloupec obsahuje desetinná čísla, ale použili jste formátování, které skryje všechny desetinná místa. Zaokrouhlování může být velmi efektivní při zmenšení velikosti číselného sloupce.

Teď, když jste provedli možnost zmenšit velikost sešitu, zvažte taky, jestli se v něm používá Optimalizace 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 optimalizaci velikosti sešitu.

Související odkazy

Specifikace a limity datového modelu

Stažení Optimalizátoru velikosti sešitu

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

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

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

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×