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

Důležité :  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

V Excelu 2013 a novějších verzích můžete vytvářet datové modely obsahující miliony řádků a potom provádění výkonných analýz dat proti tyto modely. Datové modely se dají vytvořit pomocí hesla nebo bez doplněk Power Pivot na podporu libovolný počet kontingenčních tabulkách, grafů a vizualizací Power View ve stejném sešitu.

Poznámka : Tento článek popisuje datových modelech v Excelu 2013. Však stejné modelování dat a doplňku Power Pivot funkcích v Excelu 2013 použít do Excelu 2016. Je efektivně malé rozdíl mezi těchto verzích Excelu.

I když v Excelu můžete snadno vytvářet obrovské datové modely, je z několika důvodů vhodné to nedělat. Zaprvé, rozsáhlé modely obsahující spoustu tabulek a sloupců jsou moc náročné pro většinu analýz a jejich výsledkem je nepřehledný seznam polí. Zadruhé, velké modely spotřebovávají cennou paměť, a negativně tak ovlivňují jiné aplikace a sestavy, které sdílí stejné systémové prostředky. A nakonec, v Office 365 jak SharePoint Online, tak Excel Web App omezují velikost excelového souboru na 10 MB. S datovými modely, které by v sešitu obsahovaly miliony řádků, byste dosáhli limitu 10 MB hodně rychle. Viz Specifikace a limity datového modelu.

V tomto článku se dozvíte, jak sestavit pečlivě navržený model, se kterým se bude snadněji pracovat a který spotřebuje míň paměti. Když si uděláte čas na to, abyste se seznámili s osvědčenými postupy pro návrh efektivních modelů, vyplatí se vám to později při vytváření a používání všech dalších modelů. Nezáleží na tom, jestli je budete zobrazovat v Excelu 2013, v SharePointu Online, v Office 365, 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 Workbook Size Optimizer.

V tomto článku

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

Není nic lepšího než neexistující sloupec pro nízkou spotřebu paměti

Dva příklady sloupců, které byste měli vždycky vyloučit

Vyloučení zbytečných sloupců

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

Co když budeme potřebovat sloupci; dá jsme pořád udělat ke zmenšení potřebného místa?

Úprava sloupců data a času

Úprava dotazu SQL

Použití vypočítaných hodnot namísto sloupců DAX

Které 2 sloupce máte zachovat?

Uzavření

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. Modul pomocí výkonných kompresních metod snižuje požadavky na paměť tím, že sadu výsledků ve finále zmenší až 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 na začátku. Pokud například importujete 7 MB dat z databáze SQL Serveru, může mít datový model v Excelu snadno velikost 1 MB nebo ještě menší. 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 potřebujete k jejich uložení.

Proč mluvíme o kompresi a jedinečných hodnotách? Protože sestavení efektivního modelu, který minimalizuje využití paměti, spočívá především v maximalizaci komprese a nejjednodušší způsob, jak toho dosáhnout, 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 mezi požadavky na úložiště jednotlivých sloupců můžou být obrovské. Někdy je lepší mít víc sloupců s menším počtem jedinečných hodnot, než mít jeden sloupec s mnoha jedinečnými hodnotami. Podrobný popis této metody najdete v části, která se zabývá optimalizací data a času.

Pro nízkou spotřebu paměti není nic lepšího než neexistující sloupec

Úplně nejefektivnější využití paměti má sloupec, který jste vůbec neimportovali. Když chcete vytvořit efektivní model, podívejte se na každý sloupec a položte si otázku, jestli má nějaký přínos pro analýzu, kterou chcete provést. Pokud ne nebo si nejste jistí, vynechte ho. V případě potřeby můžete vždycky přidat nové sloupce později.

Dva příklady sloupců, které byste měli vždycky vyloučit

První příklad se vztahuje k datům, která pochází z datového skladu. V něm je možné najít artefakty procesů ETL (extrakce, transformace a načítání), které načítají a obnovují data ve skladu. Při načítání dat se vytváří 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 by neměly být vybrané.

Druhý příklad se týká vypuštění sloupce primárního klíče při importu tabulky faktů.

Mnoho tabulek, včetně tabulek faktů, obsahuje primární klíče. U většiny tabulek, například těch, které obsahují informace o zákaznících, zaměstnancích nebo prodejích, jsou primární klíče žádoucí, protože je můžete použít k vytvoření relací v modelu.

U tabulek faktů je to jinak. V tabulce faktů primární klíč slouží k jednoznačné identifikaci každého řádku. Je tedy potřeba za účelem normalizace, ale už tolik ne v datovém modelu, ve kterém chcete mít jenom sloupce používané pro analýzu nebo pro nastavení relací tabulek. Proto když importujete z tabulky faktů, nezahrnujte její primární klíč. Primární klíče v tabulce faktů zabírají v modelu mimořádné hodně místa a při tom nepřinášejí žádný užitek, protože je nejde použít k vytvoření relací.

Poznámka : V datových skladech a multidimenzionálních databázích se velké tabulky obsahující většinou číselná data často označují jako „tabulky faktů“. Tabulky faktů obvykle zahrnují data o firemním výkonu nebo transakční data, jako například datové body prodejů nebo nákladů, které jsou agregované a přiřazené organizačním jednotkám, produktům, tržním segmentům, zeměpisným oblastem a podobně. Všechny sloupce v tabulce faktů, která obsahuje obchodní data nebo která může sloužit ke křížovým odkazům na data uložená v jiných tabulkách, by měly být zahrnuté do modelu a podporovat analýzu dat. Sloupec, který byste měli vyloučit, je sloupec primárního klíče tabulky faktů, který obsahuje jedinečné hodnoty vyskytující se jenom v dané tabulce faktů a nikde jinde. Protože jsou tabulky faktů tak obrovské, jednou z nejlepších možností, jak zefektivnit modely, je vyloučit z nich řádky nebo sloupce.

Vyloučení zbytečných sloupců

Efektivní modely obsahují jenom sloupce, které budete v sešitu skutečně potřebovat. Pokud chcete určovat, které sloupce se do modelu zahrnou, budete muset namísto dialogu Importovat data v Excelu použít k importu dat Průvodce importem tabulky v doplňku Power Pivot.

Po spuštění Průvodce importem tabulky vyberete tabulky, které chcete importovat.

Průvodce importem tabulky v doplňku PowerPivot

U každé tabulky můžete kliknout na tlačítko Náhled a filtr a vybrat ty části tabulky, které opravdu potřebujete. Doporučujeme, abyste nejprve zrušili zaškrtnutí u všech sloupců a potom zaškrtávali sloupce, které po zvážení vyhodnotíte jako potřebné pro analýzu.

Podokno náhledu v Průvodci importem tabulky

Jak je to s filtrováním jenom nezbytných řádků?

Mnoho tabulek v podnikových databázích a datových skladech obsahuje historická data nashromážděná za dlouhou dobu. Kromě toho můžete zjistit, že tabulky, které vás zajímají, obsahují informace z oblastí, které vaše konkrétní analýza nevyžaduje.

Pomocí Průvodce importem tabulky můžete odfiltrovat historická nebo nesouvisející data a ušetřit tak v modelu hodně místa. Na následujícím obrázku se pomocí filtru kalendářních dat načtou jenom řádky obsahující data z aktuálního roku a vyloučí se historická data, která nebudou potřeba.

Podokno filtru v Průvodci importem tabulky

Co když sloupec potřebujeme, dá se i tak něco udělat ke zmenšení potřebného místa?

Existuje několik dalších metod, které můžete použít, když chcete, aby byla komprese sloupce úspěšnější. Nezapomeňte, že jedinou charakteristikou sloupce, která má vliv na kompresi, je počet jedinečných hodnot. V této části se dozvíte, jak je možné některé sloupce upravit, aby se množství jedinečných hodnot snížilo.

Úprava sloupců data a času

V mnoha případech zabírá sloupec data a času hodně místa. Naštěstí je několik způsobů, jak omezit nároky na úložiště u tohoto typu dat. Tyto metody se budou lišit podle toho, jak sloupec používáte a jak se vyznáte v sestavování dotazů SQL.

Sloupce data a času obsahují datovou a časovou část. Když si pokládáte otázku, jestli sloupec potřebujete, u sloupce data a času si ji položte víckrát:

  • Potřebuji časovou část?

  • Potřebuji časovou část na úrovni hodin? Minut? Sekund? Milisekund?

  • Mám víc sloupců data a času, protože potřebuji počítat jejich rozdíl, nebo chci jen agregovat data podle roku, měsíce, čtvrtletí atd.?

To, jak odpovíte na každou z těchto otázek, určuje vaše možnosti pro práci se sloupcem data a času.

Všechna tato řešení vyžadují úpravu dotazu SQL. Aby byla úprava dotazu snadnější, měli byste odfiltrovat alespoň jeden sloupec v každé tabulce. Odfiltrová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ů, jejichž úpravy jsou daleko snadnější.

Podívejme se na dotazy, které jsou pro vás vytvořené. Z dialogu Vlastnosti tabulky můžete přejít 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 dialogu Vlastnosti tabulky vyberte Editor dotazů.

Otevření Editoru dotazů z dialogu Vlastnosti tabulky

Editor dotazů zobrazí dotaz SQL použitý k vyplnění tabulky. Pokud jste při importu odfiltrovali jakýkoli sloupec, dotaz obsahuje plně kvalifikované názvy sloupců:

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

Pokud jste naproti tomu importovali celou tabulku, aniž byste zrušili zaškrtnutí u některého ze sloupců nebo použili nějaký filtr, zobrazí se dotaz ve tvaru „Select * from “, který bude obtížnější upravit:

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

Úprava dotazu SQL

Teď když víte, jak dotaz najít, můžete ho upravit a dál tak snížit velkost modelu.

  1. U sloupců obsahujících data v podobě měny nebo desetinných čísel můžete pomocí následující syntaxe odstranit desetinnou část, pokud jí nepotřebujete:

    “SELECT ROUND([Název_sloupce_s_desetinnými_hodnotami],0)… .“

    Pokud potřebujete haléřové částky, ale ne jejich zlomky, nahraďte 0 za 2. Když použijete záporná čísla, můžete zaokrouhlovat na jednotky, desítky, stovky atd.

  2. Pokud máte sloupec data a času s názvem dbo.Bigtable.[Datum Čas] a nepotřebujete časovou část, odstraníte jí pomocí následující syntaxe:

    “SELECT CAST (dbo.Bigtable.[Datum Čas] as date) AS [Datum Čas]) “

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

    “SELECT 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]) as [Datum Čas Sekundy],

    datepart(ms, dbo.Bigtable.[Datum Čas]) as [Datum Čas Milisekundy]”

    Každou část uložte do samostatného sloupce – můžete jich vytvořit, kolik potřebujete.

  4. Pokud potřebujete hodiny a minuty a chcete je mít společně jako jeden sloupec času, můžete použít následující syntaxi:

    Timefromparts(datepart(hh, dbo.Bigtable.[Datum Čas]), datepart(mm, dbo.Bigtable.[Datum Čas])) as [Datum Čas HodinyMinuty]

  5. Pokud máte dva sloupce data a času, například [Čas zahájení] a [Čas ukončení], a to, co skutečně potřebujete, je časový rozdíl mezi nimi vyjádřený v sekundách jako sloupec [Trvání], odeberte ze seznamu oba sloupce a přidejte:

    “datediff(ss,[Čas zahájení],[Čas ukončení]) as [Trvání]”

    Pokud použijete klíčové slovo ms namísto ss, dostanete dobu trvání v milisekundách.

Použití vypočítaných hodnot DAX namísto sloupců

Pokud jste už dřív pracovali s jazykem výrazů DAX, možná už víte, že počítané sloupce slouží k odvození nových sloupců na základě jiných sloupců v modelu, zatímco počítané hodnoty se v modelu definují jednou, ale vyhodnocují se jenom při použití v kontingenční tabulce nebo jiné sestavě.

Jednou z metod šetření paměti je nahrazení standardních nebo počítaných sloupců počítanými hodnotami. Klasickým příkladem jsou hodnoty Jednotková cena, Množství a Celkem. Pokud máte všechny tři, můžete ušetřit místo tak, že zachováte jen dvě a dopočítáte třetí pomocí jazyka DAX.

Které dva sloupce máte zachovat?

Ve výše uvedeném příkladu zachovejte sloupce Množství a Jednotková cena. Tyto dva sloupce obsahují míň hodnot než sloupec Celkem. Když chcete vypočítat Celkem, přidáte počítanou hodnotu takto:

“ProdejCelkem:=sumx(‘Tabulka prodejů’,’Tabulka prodejů’[Jednotková cena]*’Tabulka prodejů’[Množství])”

Počítané sloupce jsou stejné jako standardní sloupce v tom, že oba zabírají místo v modelu. Oproti tomu počítané hodnoty se počítají v průběhu procesu a místo nezabírají.

Závěr

V tomto článku jsme mluvili o několika metodách, které vám můžou pomoct vytvořit model, který bude efektivně využívat paměť. Velikost souboru a požadavky na paměť omezíte tak, že snížíte celkový počet sloupců a řádků a počet jedinečných hodnot, které jsou obsažené v každém sloupci. Tady je několik metod, které jsme probrali:

  • Nejlepší způsob šetření místem je samozřejmě odebrat sloupce. Rozhodněte se, které sloupce skutečně potřebujete.

  • V některých případech můžete sloupec odebrat a v tabulce ho nahradit počítanou hodnotou.

  • Taky nemusíte potřebovat všechny řádky tabulky. V Průvodci importem tabulky můžete řádky odfiltrovat.

  • Obecně je dobrou metodou, jak snížit počet jedinečných hodnot ve sloupci, rozdělení jednoho sloupce do samostatných částí. Každá z částí bude obsahovat malý počet jedinečných hodnot a jejich celkové množství bude menší než v původním sjednoceném sloupci.

  • V mnoha případech taky tyto oddělené části potřebujete, abyste je v sestavách mohli používat jako průřezy. Tam, kde se to hodí, můžete vytvořit hierarchie z částí jako Hodiny, Minuty a Sekundy.

  • Sloupce taky často obsahují víc informací, než potřebujete. Ve sloupci můžou být například uložené desetinné hodnoty, které jste ale všechny skryli pomocí formátování. Zaokrouhlením hodnot můžete významně snížit velikost číselného sloupce.

Teď, když jste dokončili, můžete zmenšit velikost sešitu, zvažte také používá nástroj pro optimalizaci velikosti sešitu. Analyzuje sešitu aplikace Excel a pokud je to možné, zkomprimuje dál. Stáhněte si Nástroj pro optimalizaci velikosti sešitu.

Související odkazy

Specifikace a limity datového modelu

Workbook Size Optimizer ke stažení

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

Poznámka : Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Sdílení Facebook Facebook Twitter Twitter E-mail E-mail

Byly tyto informace užitečné?

Výborně! Je ještě něco dalšího, co byste nám chtěli dát vědět?

Jak bychom ho mohli vylepšit?

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

×