Create datového modelu efektivního z hlediska paměti pomocí Excelu a doplňku Power Pivot

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í.

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.

Ú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? Minut? Sekund? Milisekund?

• 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.

V části Vlastnosti tabulky vyberte Editor Power Query.

Editor Power Query zobrazí dotaz SQL použitý k naplnění tabulky. Pokud jste během importu vyfiltrovali libovolný sloupec, dotaz obsahuje plně kvalifikované názvy sloupců:

Úprava dotazu SQL

Teď, když víte, jak dotaz najít, můžete ho upravit a zmenšit tak velikost modelu.

1. 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.

2. 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]) "

3. 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.

4. 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]

5. 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.

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.