Tabulky dat v Power Pivotu jsou nezbytné pro procházení a výpočet dat v průběhu času. Tento článek obsahuje podrobné informace o tabulkách dat a o tom, jak je můžete vytvořit v Power Pivotu. Konkrétně tento článek popisuje:
-
Proč je tabulka kalendářních dat důležitá pro procházení a výpočet dat podle kalendářních dat a času.
-
Jak pomocí Power Pivotu přidat tabulku dat do datového modelu.
-
Jak v tabulce data vytvořit nové sloupce data, například Rok, Měsíc a Období.
-
Jak vytvořit relace mezi tabulkami dat a tabulkami faktů
-
Jak pracovat s časem
Tento článek je určený pro nové uživatele Power Pivotu. Je ale důležité, abyste už dobře porozuměli importu dat, vytváření relací a vytváření počítaných sloupců a míry.
Tento článek nepopisuje, jak používat funkce jazyka DAX Time-Intelligence měrné vzorce. Další informace o tom, jak vytvářet míry pomocí funkcí daxové časové řady, najdete v tématu Time Intelligence v PowerPivotu v Excel.
Poznámka: V Power Pivotu jsou názvy "míra" a "počítané pole" synonymní. V tomto článku používáme míru názvu. Další informace najdete v tématu Míry v Power Pivotu.
Obsah
Principy tabulek dat
Téměř všechny analýzy dat zahrnují procházení a porovnávání dat v datech a časech. Můžete třeba chtít sečíst částky prodeje za minulé fiskální čtvrtletí a porovnat tyto součty s jinými čtvrtletími nebo můžete chtít vypočítat konečný zůstatek na konci měsíce pro účet. V každém z těchto případů používáte kalendářní data jako způsob seskupení a agregování prodejních transakcí nebo zůstatků za určité časové období.
Power View sestavy
Tabulka kalendářních dat může obsahovat mnoho různých znázornění kalendářních dat a času. Tabulka dat bude například často mít sloupce, jako je fiskální rok, měsíc, čtvrtletí nebo období, které můžete vybrat jako pole ze seznamu polí při krájení a filtrování dat v kontingenčních tabulkách nebo Power View sestavách.
Power View pole
Pokud chcete, aby sloupce kalendářních dat, jako jsou Rok, Měsíc a Čtvrtletí, zahrnovaly všechna kalendářní data v příslušném rozsahu, musí mít tabulka kalendářních dat aspoň jeden sloupec se souvislou řadou kalendářních dat. To znamená, že tento sloupec musí mít jeden řádek pro každý den pro každý rok zahrnutý v tabulce dat.
Pokud má například data, která chcete procházet, kalendářní data od 1. února 2010 do 30. listopadu 2012 a vy sestavíte kalendářní rok, budete chtít tabulku kalendářních dat s alespoň rozsahem dat od 1. ledna 2010 do 31. prosince 2012. Každý rok v tabulce dat musí obsahovat všechny dny pro každý rok. Pokud budete data pravidelně aktualizovat novějšími daty, budete možná chtít koncové datum spustit o rok nebo dva, takže tabulku s datem nemusíte aktualizovat podle času.
Tabulka kalendářních dat se souvislou sadu kalendářních dat
Pokud sestavíte fiskální rok, můžete vytvořit tabulku kalendářních dat se souvislou sadu kalendářních dat pro každý fiskální rok. Pokud například fiskální rok začíná 1. března a máte data pro fiskální roky 2010 až do aktuálního data (například v 1. březnu 2013), můžete vytvořit tabulku dat, která začíná 1. 3. 2009 a zahrnuje minimálně každý den v každém fiskálním roce až do posledního data fiskálního roku 2013.
Pokud budete hlásit kalendářní rok i fiskální rok, není nutné vytvářet samostatné tabulky kalendářních dat. Jedna tabulka kalendářních dat může obsahovat sloupce kalendářního roku, fiskálního roku a dokonce i třinácti čtyřtýdenního kalendáře. Důležité je, že tabulka kalendářních dat obsahuje souvislé sady kalendářních dat pro všechny roky.
Přidání tabulky dat do datového modelu
Do datového modelu můžete přidat tabulku dat několika způsoby:
-
Import z relační databáze nebo jiného zdroje dat
-
Vytvořte tabulku dat v Excel a pak zkopírujte nebo vytvořte odkaz na novou tabulku v Power Pivotu.
-
Importujte z Microsoft Azure Marketplace.
Podívejme se na každý z těchto detailů.
Import z relační databáze
Pokud importujete některá nebo všechna data z datového skladu nebo jiného typu relační databáze, je pravděpodobné, že mezi ní a ostatními importovanámi daty už existuje tabulka dat a relace. Data a formát budou pravděpodobně odpovídat datům ve vašich datech faktů a data pravděpodobně začínají dobře v minulosti a jdou daleko do budoucnosti. Tabulka kalendářních dat, kterou chcete importovat, může být velmi velká a obsahuje rozsah dat nad rámec toho, co budete muset zahrnout do datového modelu. Pomocí pokročilých funkcí filtru Průvodce importem tabulky v Power Pivotu můžete selektivně zvolit jenom data a konkrétní sloupce, které opravdu potřebujete. To může výrazně zmenšit velikost sešitu a zvýšit výkon.
Průvodce importem tabulky
Ve většině případů není nutné vytvářet žádné další sloupce, jako je fiskální rok, týden, název měsíce atd., protože už budou v importované tabulce existovat. V některých případech ale po importu tabulky data do datového modelu budete možná muset vytvořit další sloupce data v závislosti na konkrétních potřebach sestav. Naštěstí se to dá snadno provést pomocí jazyka DAX. Další informace o vytváření polí tabulky dat se dozvíte později. Každé prostředí se liší. Pokud si nejste jistí, jestli zdroje dat mají související tabulku s datem nebo kalendářem, poraďte se se správcem databáze.
Vytvoření tabulky dat v Excel
Tabulku dat můžete vytvořit v Excel a pak ji zkopírovat do nové tabulky v datovém modelu. To je velmi snadné a poskytuje vám to velkou flexibilitu.
Když vytvoříte tabulku kalendářních dat v Excel, začínáte jedním sloupcem souvislé oblasti kalendářních dat. Potom můžete v listu Excel vytvořit další sloupce, například Rok, Čtvrtletí, Měsíc, Fiskální rok, Období atd. pomocí vzorců Excel nebo po zkopírování tabulky do datového modelu je můžete vytvořit jako počítané sloupce. Vytvoření dalších sloupců s datem v Power Pivotu je popsané v části Přidání nových sloupců data do tabulky dat dále v tomto článku.
Postupy: Vytvoření tabulky dat v Excel a zkopírování do datového modelu
-
Do Excel prázdném listu zadejte do buňky A1název záhlaví sloupce, který identifikuje oblast kalendářních dat. Obvykle to bude něco jako Date, DateTime nebo DateKey.
-
Do buňky A2zadejte počáteční datum. Například 1.1.2010.
-
Klikněte na úchyt a přetáhněte ho dolů na číslo řádku, které obsahuje koncové datum. Například 31. 12. 2016.
-
Vyberte všechny řádky ve sloupci Datum (včetně názvu záhlaví v buňce A1).
-
Ve skupině Styly klikněte na Formátovat jakotabulku a vyberte styl.
-
V dialogovém okně Formátovat jako tabulku klikněte na OK.
-
Zkopírujte všechny řádky včetně záhlaví.
-
V Power Pivotu klikněte na kartě Domů na Vložit.
-
Do pole Vložit náhled > název tabulky zadejte název, například Datum nebo Kalendář. Nechte zaškrtnuté políčko Použít prvnířádek jako záhlaví sloupců a klikněte na OK.
Nová tabulka kalendářních dat (v tomto příkladu s názvem Kalendář) v Power Pivotu vypadá takhle:
Poznámka: Propojenou tabulku můžete vytvořit také pomocí funkce Přidat do datového modelu. Sešit je ale zbytečně velký, protože sešit má dvě verze tabulky dat. jeden v Excel a jeden v PowerPivotu.
Poznámka: Datum názvu je klíčové slovo v Power Pivotu. Pokud tabulku, kterou vytvoříte v Power Pivot Date, pojmete, budete muset název tabulky uzavřít do jednoduchých uvozovek ve vzorcích jazyka DAX, které na něj odkazují v argumentu. Všechny příklady obrázků a vzorců v tomto článku odkazují na tabulku kalendářních dat vytvořenou v Power Pivotu s názvem Kalendář.
Teď máte v datovém modelu tabulku dat. Pomocí jazyka DAX můžete přidat nové sloupce data, například Rok, Měsíc atd.
Přidání nových sloupců data do tabulky dat
Tabulka kalendářních dat s jedním sloupcem kalendářních dat, která má jeden řádek pro každý den pro každý rok, je důležitá pro definování všech dat v oblasti dat. Je také nutné vytvořit relaci mezi tabulkou faktů a tabulkou dat. Tento sloupec s jedním datem s jedním řádkem pro každý den ale není užitečný při analýze podle kalendářních dat v kontingenční tabulce nebo Power View sestavě. Chcete, aby tabulka kalendářních dat zahrnovala sloupce, které vám pomůžou agregovat data pro oblast nebo skupinu kalendářních dat. Můžete třeba chtít sečíst částky prodeje podle měsíců nebo čtvrtletí nebo můžete vytvořit míru, která vypočítá meziroční růst. V každém z těchto případů potřebuje tabulka dat sloupce rok, měsíc nebo čtvrtletí, které vám umožní agregovat data za toto období.
Pokud jste tabulku dat importovali ze zdroje relačních dat, může už obsahovat různé typy sloupců data, které chcete. V některých případech můžete chtít některé z těchto sloupců upravit nebo vytvořit další sloupce data. To platí zejména v případě, že vytvoříte vlastní tabulku dat v Excel a zkopírujete ji do datového modelu. Vytvoření nových sloupců s datem v Power Pivotu je naštěstí snadné díky funkcím Data a Čas v jazyku DAX.
Tip: Pokud jste ještě s jazykem DAX nepracovali, je skvělým místem pro zahájení výuky rychlý start: Základní informace o jazyku DAX za 30 minut na Office.com.
Funkce data a času jazyka DAX
Pokud jste někdy pracovali s funkcemi data a času ve vzorcích Excel, pravděpodobně se seznámíte s funkcemi data a času. I když jsou tyto funkce podobné jejich protějškům v Excel, existují některé důležité rozdíly:
-
Funkce data a času jazyka DAX používají datový typ datum a čas.
-
Jako argument mohou přechytát hodnoty ze sloupce.
-
Můžete je použít k vrácení nebo manipulaci s hodnotami data.
Tyto funkce se často používají při vytváření vlastních sloupců data v tabulce dat, takže je důležité je pochopit. Řadu těchto funkcí použijeme k vytvoření sloupců pro rok, čtvrtletí, fiskální měsíce atd.
Poznámka: Funkce data a času v jazyku DAX nejsou stejné jako funkce Časové řady. Přečtěte si další informace o time intelligence v PowerPivotu v Excel 2013.
Jazyk DAX obsahuje následující funkce data a času:
Ve vzorcích můžete použít i mnoho dalších funkcí jazyka DAX. Například mnoho vzorců popsaných tady používá matematické a trigonometrické funkce, jako jsou MOD a TRUNC, logické funkce jako KDYŽa textové funkce, jako je FORMÁT Další informace o dalších funkcích jazyka DAX najdete v části Další zdroje dále v tomto článku.
Příklady vzorců pro kalendářní rok
Následující příklady popisují vzorce použité k vytvoření dalších sloupců v tabulce kalendářních dat s názvem Kalendář. Jeden sloupec s názvem Datum už existuje a obsahuje souvislé období od 1. 1. 2010 do 31. 12. 2016.
Rok
=ROK([datum])
V tomto vzorci vrátí funkce ROK rok od hodnoty ve sloupci Datum. Vzhledem k tomu, že hodnota ve sloupci Datum je datového typu datum a čas, funkce ROK ví, jak z něj vrátit rok.
Měsíc
=MĚSÍC([datum])
V tomto vzorci, podobně jako u funkce ROK, můžeme jednoduše pomocí funkce MONTH vrátit hodnotu měsíce ze sloupce Datum.
Čtvrtletí
=INT(([Měsíc]+2)/3)
V tomto vzorci používáme funkci INT k vrácení hodnoty data jako celého čísla. Argument, který určíme pro funkci INT, je hodnota ze sloupce Měsíc, přičte hodnotu 2 a potom ji vydělíte hodnotou 3, aby bylo naše čtvrtletí, 1 až 4.
Název měsíce
=FORMAT([datum];"mmmm")
V tomto vzorci použijeme funkci FORMAT k převedení číselné hodnoty ze sloupce Datum na text. Jako první argument určíme sloupec Datum a potom formát. chceme, aby se v názvu našeho měsíce zobrazují všechny znaky, takže použijeme "mmmm". Náš výsledek vypadá takhle:
Pokud chceme vrátit název měsíce zkrácený na tři písmena, použijeme v argumentu format "mmm".
Den týdne
=FORMAT([datum];"ddd")
V tomto vzorci používáme funkci FORMAT k získání názvu dne. Vzhledem k tomu, že chceme jenom zkrácený název dne, zadáme "ddd" v argumentu format.
Ukázková kontingenční tabulka
Až budete mít pole pro kalendářní data, jako je rok, čtvrtletí, měsíc atd., můžete je použít v kontingenční tabulce nebo sestavě. Například následující obrázek znázorňuje pole SalesAmount z tabulky faktů Prodej v oblasti HODNOTY a Rok a Čtvrtletí z tabulky dimenze Kalendář v řádcích. SalesAmount se agreguje pro kontext roku a čtvrtletí.
Příklady vzorců pro fiskální rok
Fiskální rok
=KDYŽ([Měsíc]<= 6;[Rok];[Rok]+1)
V tomto příkladu začíná fiskální rok 1. července.
Neexistuje žádná funkce, která by extrahovat fiskální rok z hodnoty kalendářního data, protože počáteční a koncové datum fiskálního roku se často liší od kalendářního roku. Pokud chcete získat fiskální rok, nejdřív použijeme funkci KDYŽ k testování, jestli je hodnota pro Měsíc menší nebo rovna 6. Pokud je hodnota argumentu Měsíc menší nebo rovna 6, vrátí ve druhém argumentu hodnotu ze sloupce Rok. Pokud ne, vraťte hodnotu z pole Rok a přidejte hodnotu 1.
Dalším způsobem, jak zadat hodnotu konce měsíce fiskálního roku, je vytvořit míru, která jednoduše určuje měsíc. Příklad: FYE:=6. Potom můžete místo čísla měsíce odkazovat na název míry. Příklad: =KDYŽ([Měsíc]<=[FYE];[Rok];[Rok]+1). To poskytuje větší flexibilitu při odkazování na koncový měsíc fiskálního roku v několika různých vzorcích.
Fiskální měsíc
=KDYŽ([Měsíc]<= 6; 6+[Měsíc]; [Měsíc]- 6)
V tomto vzorci určíme, jestli je hodnota pro [Měsíc] menší nebo rovna 6, pak 6 a přičítáme hodnotu z month, jinak odečteme 6 od hodnoty od [Měsíc].
Fiskální čtvrtletí
=INT(([FiscalMonth]+2)/3)
Vzorec, který používáme pro FiscalQuarter, je hodně stejný jako pro čtvrtletí v našem kalendářním roce. Jediný rozdíl je v tom, že místo [Měsíc] určíme [FiscalMonth].
Svátky nebo zvláštní data
Můžete zahrnout sloupec kalendářních dat, který označuje určitá kalendářní data jako svátky nebo jiné zvláštní datum. Můžete třeba chtít sečíst celkové prodeje za nový rok přidáním pole Svátky do kontingenční tabulky, jako průřezu nebo filtru. V ostatních případech můžete tato data vyloučit z jiných sloupců kalendářních dat nebo z míry.
Včetně svátků nebo zvláštních dnů je docela jednoduché. Tabulku můžete vytvořit v Excel, která obsahuje data, která chcete zahrnout. Potom ho můžete zkopírovat nebo použít k přidání do datového modelu jako propojené tabulky. Ve většině případů není nutné vytvářet relaci mezi tabulkou a tabulkou Kalendář. Všechny vzorce, které na něj odkazují, vracejí hodnoty pomocí funkce LOOKUPVALUE.
Níže je příklad tabulky vytvořené v Excel, která obsahuje svátky, které se mají přidat do tabulky dat:
|
Datum |
Dovolená |
|---|---|
|
1/1/2010 |
Nové roky |
|
11/25/2010 |
Díkůvzdání |
|
12/25/2010 |
Vánoce |
|
01.01.11 |
Nové roky |
|
11/24/2011 |
Díkůvzdání |
|
12/25/2011 |
Vánoce |
|
01.01.12 |
Nové roky |
|
22.11.2012 |
Díkůvzdání |
|
12/25/2012 |
Vánoce |
|
1/1/2013 |
Nové roky |
|
11/28/2013 |
Díkůvzdání |
|
12/25/2013 |
Vánoce |
|
11/27/2014 |
Díkůvzdání |
|
12/25/2014 |
Vánoce |
|
1. 1. 2014 |
Nové roky |
|
11/27/2014 |
Díkůvzdání |
|
12/25/2014 |
Vánoce |
|
1/1/2015 |
Nové roky |
|
11/26/2014 |
Díkůvzdání |
|
12/25/2015 |
Vánoce |
|
01.01.2016 |
Nové roky |
|
11/24/2016 |
Díkůvzdání |
|
12/25/2016 |
Vánoce |
V tabulce dat vytvoříme sloupec s názvem Svátky a použijeme vzorec, jako je tento:
=LOOKUPVALUE(Svátky[Svátky];Svátky[datum];Kalendář[datum])
Podívejme se na tento vzorec důkladněji.
Funkci LOOKUPVALUE používáme k získání hodnot ze sloupce Svátky v tabulce Svátky. V prvním argumentu určíme sloupec, ve kterém bude naše výsledná hodnota. Sloupec Svátky určíme v tabulce Svátky, protože to je hodnota, kterou chceme vrátit.
=LOOKUPVALUE(Svátky[Svátky],Svátky[datum];Kalendář[datum])
Potom zadáme druhý argument, vyhledávací sloupec, který obsahuje data, která chcete vyhledat. Sloupec Datum určíme v tabulce Svátky, například:
=LOOKUPVALUE(Svátky[Svátky],Svátky[datum],Kalendář[datum])
Nakonec v tabulce Kalendář určíme sloupec, který obsahuje data, která chceme vyhledat v tabulce Svátky. Toto je samozřejmě sloupec Datum v tabulce Kalendář.
=LOOKUPVALUE(Svátky[Svátky];Svátky[datum],Kalendář[datum])
Sloupec Svátky vrátí název svátku pro každý řádek, který má hodnotu data, která odpovídá datu v tabulce Svátky.
Vlastní kalendář – třináct čtyřtýdenních období
Některé organizace, jako je maloobchod nebo potravinová služba, často hlásí různá období, například třináct čtyřtýdenních období. U třinácti čtyřtýdenního kalendáře je každé období 28 dní. Proto každé období obsahuje čtyři pondělí, čtyři úterý, čtyři středy atd. Každé období obsahuje stejný počet dní a svátky obvykle spadají do stejného období každý rok. Můžete si vybrat, jestli chcete začít období v kterýkoli den v týdnu. Stejně jako u kalendářních dat nebo fiskálního roku můžete pomocí jazyka DAX vytvořit další sloupce s vlastními daty.
V následujících příkladech začíná první celé období první neděli fiskálního roku. V takovém případě začíná fiskální rok 7.1.
Týden
Tato hodnota nám dává číslo týdne počínaje prvním celým týdnem fiskálního roku. V tomto příkladu začíná první celý týden v neděli, takže první celý týden v prvním fiskálním roce v tabulce Kalendář skutečně začíná 4. 7. 2010 a pokračuje posledním celým týdnem v tabulce Kalendář. I když tato hodnota sama o sobě není užitečná při analýze, je nutné vypočítat pro použití v jiných vzorcích období 28 dnů.
=INT([datum]-40356)/7)
Podívejme se na tento vzorec důkladněji.
Nejdřív vytvoříme vzorec, který vrátí hodnoty ze sloupce Datum jako celé číslo, třeba takto:
=INT([datum])
Potom chceme hledat první neděli v prvním fiskálním roce. Vidíme, že je 4. 7. 2010.
Od této hodnoty odečítáte 40356 (což je celé číslo pro 27. 6. 2010, poslední neděli od předchozího fiskálního roku) a získáte tak počet dnů od začátku dnů v tabulce Kalendář, například:
=INT([datum]-40356)
Výsledek pak vydělte 7 (dny v týdnu), například:
=INT(([datum]-40356)/7)
Výsledek vypadá takhle:
Period
Období v tomto vlastním kalendáři obsahuje 28 dní a vždy začne v neděli. Tento sloupec vrátí číslo období začínajícího první neděli v prvním fiskálním roce.
=INT(([Týden]+3)/4)
Podívejme se na tento vzorec důkladněji.
Nejdřív vytvoříme vzorec, který vrátí hodnotu ze sloupce Týden jako celé číslo, třeba takto:
=INT([Týden])
Pak k této hodnotě přidejte 3, například:
=INT([Týden]+3)
Výsledek pak vydělte 4, například:
=INT(([Týden]+3)/4)
Výsledek vypadá takhle:
Fiskální rok období
Tato hodnota vrátí fiskální rok za určité období.
=INT(([Období]+12)/13)+2008
Podívejme se na tento vzorec důkladněji.
Nejdřív vytvoříme vzorec, který vrátí hodnotu z období a přidá 12:
= ([Období]+12)
Výsledek vydělíme 13, protože fiskální rok má třináct 28 denních období:
=(([Období]+12)/13)
Přidáme rok 2010, protože to je první rok v tabulce:
=(([Období]+12)/13)+2010
Nakonec použijeme funkci INT k odebrání libovolného zlomku výsledku a vrácení celého čísla, pokud je děleno číslem 13, například:
=INT(([Období]+12)/13)+2010
Výsledek vypadá takhle:
Období v fiskálním roce
Tato hodnota vrátí číslo období 1 až 13 počínaje prvním úplným obdobím (počínaje nedělí) v každém fiskálním roce.
=KDYŽ(MOD([Období];13); MOD([Období];13);13)
Tento vzorec je trochu složitější, takže ho nejdřív popíšeme v jazyce, který lépe chápeme. Tento vzorec uvádí, že pokud chcete v roce získat číslo období (1–13), vydělte hodnotu od [Období] číslem 13. Pokud je toto číslo 0, vrátí hodnotu 13.
Nejdřív vytvoříme vzorec, který vrátí zbytek hodnoty z období do 13. Můžeme použít mody (matematické a trigonometrické funkce) takhle:
=MOD([Období];13)
To nám většinou dává výsledek, který chceme, s výjimkou případů, kdy je hodnota pro období 0, protože tato data nespadají do prvního fiskálního roku, jako třeba v prvních pěti dnech naší příkladné tabulky kalendářních dat. Můžeme se o to postarat pomocí funkce KDYŽ. V případě, že je výsledek 0, vrátíme hodnotu 13, například:
=KDYŽ(MOD([Období];13),MOD([Období];13);13)
Výsledek vypadá takhle:
Ukázková kontingenční tabulka
Na následujícím obrázku je zobrazena kontingenční tabulka s polem SalesAmount z tabulky faktů Prodej v polích VALUES a PeriodFiscalYear a PeriodInFiscalYear z tabulky dimenze Kalendářní datum v řádcích. SalesAmount se agreguje pro kontext podle fiskálního roku a 28denního období fiskálního roku.
Relace
Když v datovém modelu vytvoříte tabulku dat, začnete procházet data v kontingenčních tabulkách a sestavách a agregovat data na základě sloupců v tabulce dimenze data, musíte vytvořit relaci mezi tabulkou faktů s transakčními daty a tabulkou dat.
Vzhledem k tomu, že potřebujete vytvořit relaci založenou na datech, musíte zajistit, abyste vytvořili relaci mezi sloupci, jejichž hodnoty jsou datového typu datum a čas (Datum).
Pro každou hodnotu data v tabulce faktů musí související vyhledávací sloupec v tabulce dat obsahovat odpovídající hodnoty. Například řádek (záznam transakce) v tabulce faktů Prodej s hodnotou 15.8.2012 12:00 ve sloupci DateKey musí mít odpovídající hodnotu v souvisejícím sloupci Datum v tabulce data (s názvem Kalendář). To je jeden z nejdůležitějších důvodů, proč chcete, aby sloupec s datem v tabulce kalendářních dat obsahoval souvislé období, které zahrnuje libovolné možné datum v tabulce faktů.
Poznámka: I když sloupec data v každé tabulce musí mít stejný datový typ (Datum), na formátu každého sloupce nezáleží..
Poznámka: Pokud vám Power Pivot nedovolí vytvářet relace mezi oběma tabulkami, nemusí pole data ukládat datum a čas na stejnou úroveň přesnosti. V závislosti na formátování sloupce mohou hodnoty vypadat stejně, ale budou uloženy jinak. Přečtěte si další informace o práci s časem.
Poznámka: Nepoužívejte v relacích celočíselné náhradní klíče. Při importu dat ze zdroje relačních dat jsou sloupce data a času často znázorněny náhradním klíčem, což je celočíselný sloupec, který představuje jedinečné datum. V Power Pivotu byste se měli vyhnout vytváření relací pomocí celočíselného klíče data a času a místo toho použít sloupce, které obsahují jedinečné hodnoty s datovým typem data. Použití náhradních klíčů je sice považováno za osvědčený postup v tradičních datových skladech, ale v Power Pivotu nejsou potřeba celočíselné klíče a může být obtížné seskupit hodnoty v kontingenčních tabulkách podle různých období data.
Pokud se při pokusu o vytvoření relace zobrazí chyba Neshoda typu, je pravděpodobné, že sloupec v tabulce faktů není datového typu Datum. K tomu může dojít v případě, že Power Pivot nemůže automaticky převést datový typ typu datum(obvykle textový datový typ). Sloupec v tabulce faktů můžete přesto použít, ale budete muset data převést pomocí vzorce jazyka DAX v novém počítaném sloupci. Další informace najdete v části Převod kalendářních dat textového typu na datový typ kalendářního data dále v dodatku.
Více relací
V některých případech může být nutné vytvořit více relací nebo vytvořit více tabulek dat. Pokud je například v tabulce faktů Prodej více polí kalendářních dat, například DateKey, ShipDate a ReturnDate, mohou mít všechny relace s polem Datum v tabulce kalendářních dat kalendáře, ale jenom jedna z nich může být aktivní relace. V tomto případě, protože DateKey představuje datum transakce, a proto nejdůležitější datum, bude to nejlepší sloužit jako aktivní relace. Ostatní mají neaktivní vztahy.
Následující kontingenční tabulka vypočítá celkové prodeje podle fiskálního roku a fiskálního čtvrtletí. Míra s názvem Celkové prodeje se vzorcem Celkový prodej:=SUMA([SalesAmount])se umístí do polí HODNOTY a pole FiskálníRok a Fiskálník z tabulky kalendářních dat se umístí do řádků.
Tato přímá kontingenční tabulka funguje správně, protože chceme shrnout celkové prodeje podledata transakcev datekey. Naše míra Celkový prodej používá kalendářní data v datekey a sečtená fiskálním rokem a fiskálním čtvrtletím, protože v tabulce Prodej existuje relace mezi datem DateKey a sloupcem Datum v tabulce Kalendářní datum.
Neaktivní relace
Ale co když bychom chtěli sečíst celkové prodeje ne podle data transakce, ale podle data odeslání? Potřebujeme relaci mezi sloupcem Datum Odeslání v tabulce Prodej a sloupcem Datum v tabulce Kalendář. Pokud tento vztah nevytváříme, jsou naše agregace vždy založené na datu transakce. Můžeme ale mít víc relací, i když může být aktivní jenom jedna, a protože nejdůležitější je datum transakce, získá aktivní relaci s tabulkou Kalendář.
V tomto případě má datum odeslání neaktivní vztah, takže každý vzorec míry vytvořený k agregaci dat na základě dat expedice musí určit neaktivní relaci pomocí funkce USERELATIONSHIP.
Například vzhledem k tomu, že mezi sloupcem Datum Odeslání v tabulce Prodej a sloupcem Datum v tabulce Kalendář existuje neaktivní relace, můžeme vytvořit míru, která sečte celkové prodeje podle data expedice. Vzorec, jako je tento, používáme k určení relace, která se má použít:
Celkové prodeje podle data expedice:=CALCULATE(SUMA(Prodej[SalesAmount]), USERELATIONSHIP(Prodej[Datum Odeslání], Kalendář[Datum]))
Tento vzorec jednoduše uvádí: Výpočet součtu pro SalesAmount, ale filtrovat pomocí relace mezi sloupcem Datum Odeslání v tabulce Prodej a sloupcem Datum v tabulce Kalendář.
Pokud teď vytvoříme kontingenční tabulku a do polí HODNOTY a Fiskální rok a Fiskální čtvrtletí zahrneme celkové součet, ale všechny ostatní částky součtu pro fiskální rok a fiskální čtvrtletí se liší, protože jsou založené na datu expedice a ne na datu transakce.
Použití neaktivních relací umožňuje používat jenom jednu tabulku dat, ale vyžaduje, aby všechny míry (například celkové prodeje podle data expedice) odkazují na neaktivní relaci ve vzorci. Existuje další alternativa, to znamená použít více tabulek dat.
Více tabulek dat
Další způsob práce s více sloupci dat v tabulce faktů je vytvoření více tabulek dat a vytvoření samostatných aktivních relací mezi nimi. Podívejme se znovu na příklad tabulky Prodej. Máme tři sloupce s kalendářními daty, na které bychom mohli chtít agregovat data:
-
A DateKey with the date of sale for each transaction.
-
A ShipDate – with the date and time when the items sold were shipped to the customer.
-
A ReturnDate – s datem a časem, kdy byla přijata jedna nebo více vrácených položek.
Nezapomeňte, že nejdůležitější je pole DateKey s datem transakce. Většinu agregací budeme na základě těchto dat dělat, takže určitě budeme chtít relaci mezi ní a sloupcem Datum v tabulce Kalendář. Pokud nechcete vytvářet neaktivní relace mezi datem odeslání a datem vrácení a polem Datum v tabulce Kalendář, což vyžaduje speciální vzorce měr, můžeme vytvořit další tabulky kalendářních dat pro datum expedice a datum vrácení. Potom mezi nimi můžeme vytvořit aktivní relace.
V tomto příkladu jsme vytvořili další tabulku dat s názvem ShipCalendar. To samozřejmě také znamená vytvoření dalších sloupců kalendářních dat, a protože jsou tyto sloupce kalendářních dat v jiné tabulce kalendářních dat, chceme je pojmenováovat způsobem, který je odlišuje od stejných sloupců v tabulce Kalendář. Vytvořili jsme například sloupce s názvem ShipYear, ShipMonth, ShipQuarter atd.
Pokud vytvoříme naši kontingenční tabulku a do polí HODNOTY a ShipFiscalYear a ShipFiscalQuarter vidíme stejné výsledky, jako jsme viděli při vytváření neaktivní relace a speciálního počítaného pole Celkové prodeje podle data expedice.
Každý z těchto přístupů vyžaduje pečlivé zvážení. Při použití více relací s jednou tabulkou dat bude pravděpodobně třeba pomocí funkce USERELATIONSHIP vytvořit zvláštní míry přenosu neaktivních relací. Na druhou stranu může být vytvoření více tabulek dat v seznamu polí matoucí, a protože máte v datovém modelu víc tabulek, bude to vyžadovat víc paměti. Experimentujte s tím, co vám nejlépe funguje.
Vlastnost Tabulka data
Vlastnost Tabulka kalendářních dat nastaví metadata nezbytná pro správné Time-Intelligence, jako jsou TOTALYTD, PREVIOUSMONTH a DATESBETWEEN. Když se výpočet spustí pomocí některé z těchto funkcí, modul vzorců Power Pivotu ví, kde získat data, která potřebuje.
Upozornění: Pokud tato vlastnost není nastavená, nemusí míry Time-Intelligence daxu vrátit správné výsledky.
Když nastavíte vlastnost Tabulka dat, zadáte v ní tabulku data a sloupec data datového typu Datum (datum a čas).
Postupy: Nastavení vlastnosti Tabulka dat
-
V PowerPivot vyberte tabulku Kalendář.
-
Na kartě Návrh klikněte na Označit jako tabulku data.
-
V dialogovém okně Označit jako tabulku dat vyberte sloupec s jedinečnými hodnotami a datovým typem Datum.
Práce s časem
Všechny hodnoty data s datovým typem Datum v Excel nebo SQL Server jsou ve skutečnosti číslo. Součástí tohoto čísla jsou číslice, které odkazují na čas. V mnoha případech je tato doba pro každý řádek půlnoc. Pokud má například pole DateTimeKey v tabulce faktů Prodej hodnoty jako 19.10.2010 12:00:00, znamená to, že hodnoty jsou na denní úrovni přesnosti. Pokud mají hodnoty pole DateTimeKey čas, například 19.10.2010 8:44:00, znamená to, že hodnoty jsou na úrovni přesnosti minut. Hodnoty mohou být také přesné na úrovni hodin nebo dokonce na úroveň přesnosti sekund. Úroveň přesnosti v časové hodnotě bude mít významný vliv na to, jak vytvoříte tabulku dat a vztahy mezi ní a tabulkou faktů.
Musíte určit, jestli chcete data agregovat na denní úroveň přesnosti nebo na časovou úroveň přesnosti. Jinými slovy, můžete chtít použít sloupce v tabulce dat, jako jsou pole data Ráno, Odpoledne nebo Hodina jako čas v oblasti Řádek, Sloupec nebo Filtr kontingenční tabulky.
Poznámka: Dny jsou nejmenší jednotkou času, se kterou pracujou funkce DAX Time Intelligence. Pokud s časovými hodnotami pracovat nepotřebujete, měli byste přesnost dat omezit tak, aby jako minimální jednotku byly dny.
Pokud chcete data agregovat na časovou úroveň, bude tabulka dat potřebovat sloupec data s zahrnutou hodnotou času. Ve skutečnosti bude pro každý rok v rozsahu dat potřebovat sloupec data s jedním řádkem pro každou hodinu nebo dokonce každou minutu každého dne. Je to proto, že pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce faktů a sloupcem data v tabulce dat, musíte mít odpovídající hodnoty. Jak si dovedete představit, pokud zahrnete hodně let, může to představovat velmi velkou tabulku s datem.
Ve většině případů ale chcete data agregovat jenom do dne. Jinými slovy, jako pole v oblasti Řádek, Sloupec nebo Filtr kontingenční tabulky použijete sloupce jako Rok, Měsíc, Týden nebo Den v týdnu. V tomto případě musí sloupec data v tabulce dat obsahovat jenom jeden řádek pro každý den v roce, jak jsme popsali dříve.
Pokud sloupec s datem obsahuje časovou úroveň přesnosti, ale budete agregovat jenom na denní úroveň, abyste vytvořili relaci mezi tabulkou faktů a tabulkou dat, budete možná muset tabulku faktů upravit vytvořením nového sloupce, který zkrátí hodnoty ve sloupci datum na hodnotu dne. Jinými slovy, převést hodnotu jako 19.10.2010 8:44:00do19.10.2010 12:00:00 . Potom můžete vytvořit relaci mezi tímto novým sloupcem a sloupcem data v tabulce dat, protože se hodnoty shodují.
Podívejme se na příklad. Tento obrázek ukazuje sloupec DateTimeKey v tabulce faktů Prodej. Všechny agregace dat v této tabulce musí být pouze na úrovni dne pomocí sloupců v tabulce kalendářních dat, jako je rok, měsíc, čtvrtletí atd. Čas zahrnutý do hodnoty není relevantní, jenom skutečné datum.
Vzhledem k tomu, že tato data nemusíme analyzovat na časovou úroveň, nepotřebujeme sloupec Datum v tabulce kalendářních dat tak, aby zahrnoval jeden řádek pro každou hodinu a každou minutu každého dne v každém roce. Takže sloupec Datum v naší tabulce dat vypadá takhle:
Pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce Prodej a sloupcem Datum v tabulce Kalendář, můžeme vytvořit nový počítaný sloupec v tabulce faktů Prodej a pomocí funkce ZKRÁTIT zkrátit hodnotu data a času ve sloupci DateTimeKey na hodnotu data, která odpovídá hodnotám ve sloupci Datum v tabulce Kalendář. Náš vzorec vypadá takhle:
=ZKRÁCENÍ([DateTimeKey];0)
Tím získáte nový sloupec (s názvem DateKey) s datem ze sloupce DateTimeKey a časem 12:00:00 pro každý řádek:
Teď můžeme vytvořit relaci mezi tímto novým sloupcem (DateKey) a sloupcem Datum v tabulce Kalendář.
Podobně můžeme vytvořit počítaný sloupec v tabulce Prodej, který zmenší přesnost času ve sloupci DateTimeKey na úroveň přesnosti hodin. V takovém případě funkce TRUNC nebude fungovat, ale k extrahování a opětovnému zřetězování nové hodnoty na úroveň přesnosti hodin můžeme použít jiné funkce daxu Datum a čas. Můžeme použít vzorec, jako je tento:
= DATUM (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0; 0)
Náš nový sloupec vypadá takhle:
Pokud sloupec Datum v tabulce dat obsahuje hodnoty na úrovni přesnosti hodin, můžeme mezi nimi vytvořit relaci.
Zušlechtování dat
Mnoho sloupců data, které vytvoříte v tabulce dat, je potřeba pro jiná pole, ale ve skutečnosti nejsou užitečné pro analýzu. Například pole DateKey v tabulce Prodej, na které odkazujeme a která se zobrazuje v celém tomto článku, je důležité, protože pro každou transakci se tato transakce zaznamená jako k určitému datu a času. Z pohledu analýzy a vytváření sestav to ale není všechno tak užitečné, protože ho v kontingenční tabulce nebo sestavě nemůžeme použít jako pole řádku, sloupce nebo filtru.
Podobně je v našem příkladu sloupec Datum v tabulce Kalendář velmi užitečný, ve skutečnosti důležitý, ale nemůžete ho použít jako rozměr v kontingenční tabulce.
Pokud chcete, aby tabulky Power View sloupce v nich byly co nejužitečnější Power View usnadnily navigaci v seznamech polí sestavy, je důležité skrýt nepotřebné sloupce z klientských nástrojů. Můžete taky chtít skrýt určité tabulky. Tabulka Svátky zobrazená dříve obsahuje data svátků, která jsou důležitá pro určité sloupce v tabulce Kalendář, ale nemůžete použít sloupce Datum a Svátky v tabulce Svátky jako pole v kontingenční tabulce. Pokud chcete usnadnit navigaci v seznamech polí, můžete skrýt celou tabulku Svátky.
Dalším důležitým aspektem práce s kalendářními daty jsou zásady vytváření názvů. Tabulky a sloupce v PowerPivotu můžete pojmnovat podle toho, co chcete. Mějte ale na paměti, hlavně pokud budete sešit sdílet s jinými uživateli, dobrá pravidla vytváření názvů usnadňují identifikaci tabulek a kalendářních dat, a to nejen v seznamech polí, ale také v Power Pivotu a ve vzorcích jazyka DAX.
Až budete mít v datovém modelu tabulku dat, můžete začít vytvářet míry, které vám pomůžou data co nejvíce vyučovat. Některé mohou být stejně jednoduché jako sečtení celkových prodejů pro aktuální rok a jiné mohou být složitější, kde je potřeba filtrovat podle určitého rozsahu jedinečných dat. Další informace najdete v části Míry ve funkcích Power Pivot a Time Intelligence.
Dodatek
Převod kalendářních dat textového typu na datový typ kalendářního data
V některých případech může tabulka faktů s transakčními daty obsahovat data textového datového typu. To znamená, že datum, které se zobrazuje jako 2012-12-04T11:47:09, není ve skutečnosti vůbec datum, nebo aspoň ne typ data, které Power Pivot dokáže pochopit. Je to jenom text, který se čte jako datum. Pokud chcete vytvořit relaci mezi sloupcem data v tabulce faktů a sloupcem data v tabulce dat, musí být oba sloupce datového typu Datum.
Když se obvykle pokusíte změnit datový typ sloupce kalendářních dat, který je textovým datovým typem, na datový typ kalendářního data, power pivot dokáže data interpretovat a převést na datový typ skutečného data automaticky. Pokud Power Pivot nemůže provést převod datového typu, zobrazí se chyba neshody typu.
Data ale můžete přesto převést na datový typ skutečného data. Můžete vytvořit nový počítaný sloupec a použít vzorec jazyka DAX k analýze roku, měsíce, dne, času atd. z textových řetězců a potom ho zřetězit dohromady tak, aby Power Pivot mohl číst jako skutečné datum.
V tomto příkladu jsme do Power Pivotu naimportli tabulku faktů s názvem Prodej. Obsahuje sloupec s názvem DateTime. Hodnoty se zobrazují takhle:
Pokud se podíváme na Datový typ ve skupině Formátování na kartě Domů power pivotu, vidíme, že jde o datový typ Text.
Nemůžeme vytvořit relaci mezi sloupcem DateTime a sloupcem Datum v naší tabulce dat, protože datové typy se neshodují. Pokud se pokusíme změnit datový typ na Datum, zobrazí se chybová zpráva o neshodě typů:
V tomto případě se power pivotu nepodařilo převést datový typ z textu na datum. Tento sloupec můžeme použít, ale abychom ho mohli přetvořit do datového typu skutečného data, musíme vytvořit nový sloupec, který analyzuje text a znovu ho vytvoří na hodnotu, kterou Power Pivot může nastavit jako datový typ Datum.
Nezapomeňte, že v části Práce s časem uvedené výše v tomto článku; Pokud není potřeba, aby vaše analýza byla na denní úrovni přesnosti, měli byste data v tabulce faktů převést na denní úroveň přesnosti. S tímto vědomím chceme, aby hodnoty v našem novém sloupci byly na denní úrovni přesnosti (s výjimkou času). Hodnoty ve sloupci Datum a čas můžeme převést na datový typ data a úroveň přesnosti času odebrat pomocí následujícího vzorce:
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))
Tím získáte nový sloupec (v tomto případě s názvem Datum). Power Pivot dokonce rozpozná hodnoty jako kalendářní data a nastaví datový typ automaticky na Datum.
Pokud chceme zachovat úroveň přesnosti času, jednoduše vzorec rozšíříme tak, aby zahrnoval hodiny, minuty a sekundy.
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2)) +
TIME(MID([DateTime];12;2), MID([DateTime];15;2), MID([DateTime];18;2))
Teď, když máme sloupec Datum datového typu Datum, můžeme vytvořit relaci mezi datem a sloupcem data v datu.
Další zdroje informací
Rychlý úvod: Naučte se základy jazyka DAX za 30 minut
