Tabulky kalendářních dat v Power Pivotu jsou nezbytné pro procházení a výpočet dat v průběhu času. Tento článek poskytuje podrobné informace o tabulkách kalendářních dat a o tom, jak je můžete vytvořit v Power Pivotu. Tento článek konkrétně 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 kalendářních dat do datového modelu.
-
Jak v tabulce kalendářních dat vytvořit nové sloupce kalendářních dat, například Rok, Měsíc a Období.
-
Jak vytvořit relace mezi tabulkami kalendářních dat a tabulkami faktů
-
Jak pracovat s časem.
Tento článek je určený pro uživatele, kteří s Power Pivotem noví. Je ale důležité, abyste už dobře rozuměli importu dat, vytváření relací a vytváření počítaných sloupců a měr.
Tento článek nepopisuje , jak používat funkce jazyka DAX Time-Intelligence ve vzorcích měr. Další informace o vytváření měr pomocí funkcí časového měřítka DAX najdete v tématu Časové měřítko v Power Pivotu v Excelu.
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 kalendářních dat
Téměř všechna analýza dat zahrnuje procházení a porovnávání dat v datech a časech. Můžete například chtít sečíst částky prodeje za minulé fiskální čtvrtletí a pak tyto součty porovnat s ostatními čtvrtletími nebo můžete chtít vypočítat konečný zůstatek účtu na konci měsíce. V každém z těchto případů používáte kalendářní data jako způsob seskupení a agregace prodejních transakcí nebo zůstatků za určité časové období.
Sestava Power View
Tabulka kalendářních dat může obsahovat mnoho různých reprezentací kalendářních dat a času. Například tabulka kalendářních dat bude často obsahovat sloupce jako Fiskální rok, Měsíc, Čtvrtletí nebo Období, které můžete vybrat jako pole ze seznamu polí při vytváření řezů a filtrování dat v kontingenčních tabulkách nebo sestavách Power View.
Seznam polí Power View
Aby sloupce kalendářních dat jako Rok, Měsíc a Čtvrtletí obsahovaly všechna kalendářní data v příslušném rozsahu, musí tabulka kalendářních dat obsahovat alespoň jeden sloupec s souvislou sadou 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 kalendářních dat.
Pokud například data, která chcete procházet, mají data od 1. února 2010 do 30. listopadu 2012 a vy sestavujete 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 kalendářních dat musí obsahovat všechny dny pro každý rok. Pokud budete data pravidelně aktualizovat novějšími daty, možná budete chtít koncové datum spustit za rok nebo dva, abyste nemuseli aktualizovat tabulku kalendářních dat v čase.
Tabulka kalendářních dat s souvislou sadou kalendářních dat
Pokud sestavujete fiskální rok, můžete vytvořit tabulku kalendářních dat s souvislou sadou kalendářních dat pro každý fiskální rok. Pokud například váš 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 ve fiskálním roce 2013), můžete vytvořit tabulku kalendářních dat, která začíná 1. 3. 2009 a zahrnuje alespoň každý den v každém fiskálním roce až do posledního data ve fiskálním roce 2013.
Pokud budete vykazovat kalendářní i fiskální rok, nemusíte vytvářet samostatné tabulky kalendářních dat. Jedna tabulka kalendářních dat může obsahovat sloupce pro kalendářní rok, fiskální rok a dokonce i pro kalendář třinácti čtyř týdnů. Důležité je, že tabulka kalendářních dat obsahuje souvislou sadu kalendářních dat pro všechny zahrnuté roky.
Přidání tabulky kalendářních dat do datového modelu
Do datového modelu můžete přidat tabulku kalendářních dat několika způsoby:
-
Importujte z relační databáze nebo jiného zdroje dat.
-
Vytvořte tabulku kalendářních dat v Excelu a potom zkopírujte nebo propojte novou tabulku v Power Pivotu.
-
Importujte z Microsoft Azure Marketplace.
Pojďme se na ně podívat podrobněji.
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 už existuje tabulka kalendářních dat a relace mezi ní a ostatními daty, která importujete. 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 může obsahovat rozsah kalendářních 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 kalendářní data a konkrétní sloupce, které opravdu potřebujete. To může výrazně zmenšit velikost sešitu a zlepšit výkon.
Průvodce importem tabulky
Ve většině případů nebudete muset vytvářet žádné další sloupce, například Fiskální rok, Týden, Název měsíce atd., protože už v importované tabulce budou existovat. V některých případech ale po importu tabulky kalendářních dat do datového modelu může být potřeba vytvořit další sloupce kalendářních dat v závislosti na konkrétní potřebě generování sestav. Naštěstí se to dá snadno provést pomocí jazyka DAX. Další informace o vytváření polí tabulky kalendářních dat najdete později. Každé prostředí je jiné. Pokud si nejste jistí, jestli vaše zdroje dat mají související tabulku kalendářních dat nebo kalendářních dat, obraťte se na správce databáze.
Vytvoření tabulky kalendářních dat v Excelu
Tabulku kalendářních dat můžete vytvořit v Excelu a pak ji zkopírovat do nové tabulky v datovém modelu. To je opravdu poměrně snadné a dává vám to spoustu flexibility.
Když v Excelu vytvoříte tabulku kalendářních dat, začnete jedním sloupcem s souvislým rozsahem kalendářních dat. Pomocí excelových vzorců pak můžete pomocí excelových vzorců vytvořit další sloupce, například Rok, Čtvrtletí, Měsíc, Fiskální rok, Období atd., nebo je můžete po zkopírování tabulky do datového modelu vytvořit jako počítané sloupce. Vytváření dalších sloupců kalendářních dat v Power Pivotu je popsáno v části Přidání nových sloupců kalendářních dat do tabulky kalendářních dat dále v tomto článku.
Postupy: Vytvoření tabulky kalendářních dat v Excelu a její zkopírování do datového modelu
-
V Excelu zadejte do prázdného listu v buňce A1 název záhlaví sloupce, který identifikuje oblast kalendářních dat. Obvykle se jednáo něco jako Datum, DateTime nebo DateKey.
-
Do buňky A2 zadejte 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 jako tabulku 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.
-
V okně Náhled vložení > 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 pak klikněte na OK.
Nová tabulka kalendářních dat (v tomto příkladu s názvem Kalendář) v Power Pivotu vypadá takto:
Poznámka: Propojenou tabulku můžete vytvořit také pomocí příkazu Přidat do datového modelu. Sešit je ale zbytečně velký, protože obsahuje dvě verze tabulky kalendářních dat; jednu v Excelu a jednu v Power Pivotu.
Poznámka: Datum názvu je klíčové slovo v Power Pivotu. Pokud tabulku, kterou vytvoříte v date power pivotu, pojmenujete, budete muset název tabulky uzavřít do jednoduchých uvozovek do všech vzorců DAX, které na ni odkazují v argumentu. Všechny ukázkové obrázky a vzorce v tomto článku odkazují na tabulku kalendářních dat vytvořenou v Power Pivotu s názvem Kalendář.
V datovém modelu teď máte tabulku kalendářních dat. Pomocí jazyka DAX můžete přidat nové sloupce kalendářních dat, například Rok, Měsíc atd.
Přidání nových sloupců kalendářních dat do tabulky kalendářních dat
Tabulka kalendářních dat s jedním sloupcem kalendářních dat, která obsahuje jeden řádek pro každý den pro každý rok, je důležitá pro definování všech kalendářních dat v rozsahu kalendářních dat. Je také nezbytné pro vytvoření relace mezi tabulkou faktů a tabulkou kalendářních dat. Tento sloupec kalendářního data 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 sestavě Power View. Chcete, aby tabulka kalendářních dat obsahovala sloupce, které vám pomůžou agregovat data pro oblast nebo skupinu kalendářních dat. Můžete například chtít sečíst částky prodeje podle měsíce 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 kalendářních dat sloupce rok, měsíc nebo čtvrtletí, které umožňují agregovat data pro dané období.
Pokud jste importovali tabulku kalendářních dat z relačního zdroje dat, může už obsahovat různé typy sloupců kalendářních dat, které chcete. V některých případech můžete chtít některé z těchto sloupců upravit nebo vytvořit další sloupce kalendářních dat. To platí zejména v případě, že v Excelu vytvoříte vlastní tabulku kalendářních dat a zkopírujete ji do datového modelu. Vytváření nových sloupců kalendářních dat v Power Pivotu je naštěstí díky funkcím data a času v jazyce DAX poměrně snadné.
Tip: Pokud jste s jazykem DAX ještě nepracovali, je skvělým místem, kde se můžete začít učit , rychlý start: Seznámení se základy jazyka 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 Excelu, budete pravděpodobně znát funkce data a času. I když jsou tyto funkce podobné svým protějškům v Excelu, existují některé důležité rozdíly:
-
Funkce Data a čas jazyka DAX používají datový typ datetime.
-
Můžou převzít hodnoty ze sloupce jako argument.
-
Dají se použít k vrácení nebo manipulaci s hodnotami kalendářních dat.
Tyto funkce se často používají při vytváření vlastních sloupců kalendářních dat v tabulce kalendářních dat, takže je důležité jim porozumět. K vytvoření sloupců pro Year, Quarter, FiscalMonth atd. použijeme řadu těchto funkcí.
Poznámka: Funkce Data a Čas v jazyce DAX nejsou stejné jako funkce časového měřítka. Přečtěte si další informace o časovém měřítku v Power Pivotu v Excelu.
Jazyk DAX obsahuje následující funkce data a času:
Existuje mnoho dalších funkcí jazyka DAX, které můžete použít i ve vzorcích. Mnoho zde popsaných vzorců například používá matematické a trigonometrické funkce , jako jsou MOD a TRUNC, logické funkce jako KDYŽ a textové funkce jako FORMÁT . Další informace o dalších funkcích jazyka DAX najdete v části Další zdroje informací dále v tomto článku.
Příklady vzorců pro kalendářní rok
Následující příklady popisují vzorce používané k vytvoření dalších sloupců v tabulce kalendářních dat s názvem Kalendář. Jeden sloupec s názvem Date už existuje a obsahuje souvislý rozsah kalendářních dat od 1. 1. 2010 do 31. 12. 2016.
Rok
=YEAR([date])
Funkce YEAR v tomto vzorci vrátí rok z hodnoty ve sloupci Date. Vzhledem k tomu, že hodnota ve sloupci Datum je datového typu datetime, funkce YEAR ví, jak z ní vrátit rok.
Měsíc
=MĚSÍC([datum])
V tomto vzorci, podobně jako u funkce ROK, můžeme jednoduše použít funkci MĚSÍC k vrácení hodnoty měsíce ze sloupce Datum.
Čtvrtletí
=INT(([Měsíc]+2)/3)
V tomto vzorci použijeme funkci INT k vrácení hodnoty data jako celého čísla. Argument, který zadáme pro funkci INT, je hodnota ze sloupce Month ( Měsíc), přidejte 2 a pak ji vydělte 3, abyste získali čtvrtletí 1 až 4.
Název měsíce
=FORMAT([datum],"mmmm")
Abychom získali název měsíce, použijeme v tomto vzorci funkci FORMAT k převodu číselné hodnoty ze sloupce Datum na text. Jako první argument zadáme sloupec Date a pak formát; chceme, aby název měsíce zobrazoval všechny znaky, takže použijeme "mmmm". Výsledek vypadá takto:
Pokud chceme vrátit název měsíce zkrácený na tři písmena, použili bychom v argumentu format "mmm".
Den týdne
=FORMAT([datum],"ddd")
V tomto vzorci použijeme funkci FORMAT k získání názvu dne. Protože chceme jen zkrácený název dne, zadáme do argumentu format "ddd".
Ukázková kontingenční tabulka
Jakmile máte pole pro kalendářní data, například Rok, Čtvrtletí, Měsíc atd., můžete je použít v kontingenční tabulce nebo sestavě. Následující obrázek například ukazuje pole SalesAmount z tabulky faktů Sales (Prodej) v části VALUES (Hodnoty) a Year and Quarter (Rok a čtvrtletí) z tabulky dimenzí Calendar (Kalendář) v poli ŘÁDKY. 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 fiskální rok začíná 1. července.
Neexistuje žádná funkce, která by z hodnoty data extrahuje fiskální rok, protože počáteční a koncové datum fiskálního roku se často liší od kalendářního roku. Abychom získali fiskální rok, nejprve pomocí funkce KDYŽ otestujeme, jestli je hodnota pro Měsíc menší nebo rovna 6. V druhém argumentu, pokud je hodnota pro Měsíc menší než nebo rovna 6, vrátí hodnotu ze sloupce Year. Pokud ne, vraťte hodnotu z year a přidejte 1.
Dalším způsobem, jak zadat hodnotu koncového měsíce fiskálního roku, je vytvořit míru, která jednoduše určuje měsíc. Příklad: FYE:=6. Místo čísla měsíce pak můžete odkazovat na název míry. Například =KDYŽ([Měsíc]<=[Rok],[Rok][Rok]+1). To poskytuje větší flexibilitu při odkazování na měsíc konce 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 [Month] menší nebo rovna 6, pak vezmeme hodnotu 6 a sečtou hodnotu z Month, jinak odečteme hodnotu 6 od hodnoty [Month].
Fiskální čtvrtletí
=INT(([FiscalMonth]+2)/3)
Vzorec, který používáme pro FiscalQuarter, je v podstatě stejný jako pro čtvrtletí v našem kalendářním roce. Jediným rozdílem je, že místo [Month] zadáme [FiscalMonth].
Svátky nebo zvláštní data
Můžete zahrnout sloupec kalendářních dat, který označuje, že určitá data jsou svátky nebo jiné zvláštní datum. Můžete například chtít sečíst celkové prodeje za nový rok přidáním pole Svátek do kontingenční tabulky, jako průřez nebo filtr. V jiných případech můžete chtít tato data vyloučit z jiných sloupců kalendářních dat nebo z míry.
Zahrnutí svátků nebo zvláštních dnů je poměrně jednoduché. V Excelu můžete vytvořit tabulku s daty, která chcete zahrnout. Potom ho můžete zkopírovat nebo použít příkaz Přidat do datového modelu a přidat ho do datového modelu jako propojenou tabulku. Ve většině případů není nutné vytvářet relaci mezi tabulkou a tabulkou Kalendář. Všechny vzorce, které na ni odkazují, můžou k vrácení hodnot použít funkci LOOKUPVALUE .
Níže je příklad tabulky vytvořené v Excelu, která obsahuje svátky, které se mají přidat do tabulky kalendářních dat:
|
Datum |
Dovolená |
|---|---|
|
1/1/2010 |
Nové roky |
|
11/25/2010 |
Díkuvzdání |
|
12/25/2010 |
Vánoce |
|
01.01.11 |
Nové roky |
|
11/24/2011 |
Díkuvzdání |
|
12/25/2011 |
Vánoce |
|
01.01.12 |
Nové roky |
|
22.11.2012 |
Díkuvzdání |
|
12/25/2012 |
Vánoce |
|
1/1/2013 |
Nové roky |
|
11/28/2013 |
Díkuvzdání |
|
12/25/2013 |
Vánoce |
|
11/27/2014 |
Díkuvzdání |
|
12/25/2014 |
Vánoce |
|
1. 1. 2014 |
Nové roky |
|
11/27/2014 |
Díkuvzdání |
|
12/25/2014 |
Vánoce |
|
1/1/2015 |
Nové roky |
|
11/26/2014 |
Díkuvzdání |
|
12/25/2015 |
Vánoce |
|
01.01.2016 |
Nové roky |
|
11/24/2016 |
Díkuvzdání |
|
12/25/2016 |
Vánoce |
V tabulce kalendářních dat vytvoříme sloupec s názvem Dovolená a použijeme vzorec podobný tomuto:
=LOOKUPVALUE(Svátky[Svátky],Svátky[datum],Kalendář[datum])
Podívejme se na tento vzorec podrobněji.
K získání hodnot ze sloupce Svátek v tabulce Svátky používáme funkci LOOKUPVALUE. V prvním argumentu určíme sloupec, ve kterém bude výsledná hodnota. Sloupec Svátek zadáme v tabulce Svátky , protože se jedná o hodnotu, kterou chceme vrátit.
=LOOKUPVALUE(Svátky[Svátky];Svátky[datum],Kalendář[datum])
Potom zadáme druhý argument, vyhledávací sloupec obsahující data, která chceme vyhledat. Sloupec Date ( Datum ) zadáme v tabulce Svátky takto:
=LOOKUPVALUE(Svátky[Svátky],Svátky[datum];Kalendář[datum])
Nakonec určíme sloupec v tabulce Kalendář , který obsahuje data, která chceme vyhledat v tabulce Svátek . Toto je samozřejmě sloupec Date (Datum ) v tabulce Kalendář .
=LOOKUPVALUE(Svátky[Svátky],Svátky[datum],Kalendář[datum])
Sloupec Svátek 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 potravinářství, často hlásí různá období, například třináct čtyřtýdenních období. Se třinácti čtyřtýdenním obdobím má 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 zvolit, že se má období začínat libovolný 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 tomto případě fiskální rok začíná 1. 7.
Týden
Tato hodnota nám poskytuje číslo týdne začínající prvním celým týdnem ve fiskálním roce. 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ář ve skutečnosti začíná 4. 7. 2010 a pokračuje až do posledního celého týdne v tabulce Kalendář. I když tato hodnota sama o sobě není tak užitečná při analýze, je nutné počítat pro použití v jiných vzorcích 28denního období.
=INT([datum]-40356)/7)
Podívejme se na tento vzorec podrobněji.
Nejprve vytvoříme vzorec, který vrátí hodnoty ze sloupce Date jako celé číslo, například takto:
=INT([datum])
Pak chceme hledat první neděli v prvním fiskálním roce. Vidíme, že je 4. 7. 2010.
Teď od této hodnoty odečtete 40356 (což je celé číslo pro 27. 6. 2010, poslední neděli od předchozího fiskálního roku), abyste získali počet dní od začátku dnů v naší tabulce Kalendář, například takto:
=INT([datum]-40356)
Potom vydělte výsledek 7 (dny v týdnu), například takto:
=INT(([datum]-40356)/7)
Výsledek vypadá takto:
Period
Období v tomto vlastním kalendáři obsahuje 28 dní a začíná vždy v neděli. Tento sloupec vrátí číslo období začínajícího první nedělí v prvním fiskálním roce.
=INT(([Týden]+3)/4)
Podívejme se na tento vzorec podrobněji.
Nejprve vytvoříme vzorec, který vrátí hodnotu ze sloupce Týden jako celé číslo, například takto:
=INT([Týden])
Pak k této hodnotě přidejte hodnotu 3, například takto:
=INT([Týden]+3)
Potom vydělte výsledek 4 takto:
=INT(([Týden]+3)/4)
Výsledek vypadá takto:
Fiskální rok období
Tato hodnota vrátí fiskální rok pro určité období.
=INT(([Období]+12)/13)+2008
Podívejme se na tento vzorec podrobněji.
Nejprve vytvoříme vzorec, který vrátí hodnotu z periody a přidá 12:
= ([Období]+12)
Výsledek vydělíme 13, protože ve fiskálním roce je třináct 28denních období:
=(([Období]+12)/13)
Přidáme rok 2010, protože je to první rok v tabulce:
=(([Období]+12)/13)+2010
Nakonec pomocí funkce INT odebereme libovolný zlomek výsledku a vrátíme celé číslo, když ho vydělíme číslem 13, například takto:
=INT(([Období]+12)/13)+2010
Výsledek vypadá takto:
Období v fiskálním roce
Tato hodnota vrátí číslo období, 1 až 13, počínaje prvním úplným obdobím (začínajícím v neděli) 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 nejprve popíšeme v jazyce, kterému lépe rozumíme. Tento vzorec uvádí, že vydělíte hodnotu [Tečka] číslem 13, abyste získali číslo období (1–13) v roce. Pokud je toto číslo 0, vrátí se 13.
Nejprve vytvoříme vzorec, který vrátí zbytek hodnoty z hodnoty Tečka o 13. Mod ( matematické a trigonometrické funkce) můžeme použít takto:
=MOD([Tečka],13)
To nám většinou dává požadovaný výsledek, s výjimkou případů, kdy je hodnota Období 0, protože tato data nespadají do prvního fiskálního roku, jako v prvních pěti dnech v naší ukázkové tabulce kalendářních dat. Můžeme se o to postarat pomocí funkce KDYŽ. Pokud je výsledek 0, vrátíme 13 takto:
=KDYŽ(MOD([Období);13);MOD([Období];13);13)
Výsledek vypadá takto:
Ukázková kontingenční tabulka
Následující obrázek znázorňuje kontingenční tabulku s polem SalesAmount z tabulky faktů Sales v části HODNOTY a polí PeriodFiscalYear a PeriodInFiscalYear z tabulky dimenzí kalendářního data kalendáře v řádcích. SalesAmount se agreguje pro kontext podle fiskálního roku a 28denního období ve fiskálním roce.
Relace
Pokud chcete po vytvoření tabulky kalendářních dat v datovém modelu začít procházet data v kontingenčních tabulkách a sestavách a agregovat data na základě sloupců v tabulce dimenzí kalendářních dat, musíte vytvořit relaci mezi tabulkou faktů a daty transakcí a tabulkou kalendářních dat.
Vzhledem k tomu, že potřebujete vytvořit relaci založenou na datech, měli byste se ujistit, že jste vytvořili relaci mezi sloupci, jejichž hodnoty jsou datového typu datetime (Date).
Pro každou hodnotu kalendářního data v tabulce faktů musí související vyhledávací sloupec v tabulce kalendářních dat obsahovat odpovídající hodnoty. Například řádek (záznam transakce) v tabulce faktů Sales s hodnotou 15.8.2012 12:00 ve sloupci DateKey musí mít odpovídající hodnotu v souvisejícím sloupci Date v tabulce date (s názvem Calendar). To je jeden z nejdůležitějších důvodů, proč chcete, aby sloupec kalendářních dat v tabulce kalendářních dat obsahoval souvislý rozsah kalendářních dat, který v tabulce faktů zahrnuje jakékoli možné datum.
Poznámka: I když sloupec kalendářního data v každé tabulce musí být stejného datového typu (Date), na formátu každého sloupce nezáleží.
Poznámka: Pokud Power Pivot neumožňuje vytvořit relace mezi těmito dvěma tabulkami, nemusí pole kalendářních dat ukládat datum a čas se stejnou úrovní přesnosti. V závislosti na formátování sloupce můžou hodnoty vypadat stejně, ale mohou být uloženy odlišně. 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 z relačního zdroje dat jsou sloupce data a času často reprezentovány náhradním klíčem, což je celočíselný sloupec, který se používá k reprezentaci jedinečného data. 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žívat sloupce, které obsahují jedinečné hodnoty s datovým typem kalendářního data. I když je použití náhradních klíčů v tradičních datových skladech považováno za osvědčený postup, celočíselné klíče nejsou v Power Pivotu potřeba a můžou ztěžovat seskupení hodnot v kontingenčních tabulkách podle různých datových období.
Pokud se při pokusu o vytvoření relace zobrazí chyba neshody typů, je to pravděpodobně proto, že sloupec v tabulce faktů není datového typu Datum. K tomu může dojít, když Power Pivot nedokáže automaticky převést jiný datový typ než datum (obvykle textový datový typ) na datový typ kalendářního data. Sloupec v tabulce faktů můžete dál používat, ale v novém počítaném sloupci budete muset data převést pomocí vzorce DAX. Další informace najdete v části Převod dat datového typu textu na datový typ data v další části 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 kalendářních dat. Pokud je například v tabulce faktů Sales (Prodej) více polí kalendářních dat, například DateKey (Klíč data), ShipDate (Datum expedice) a ReturnDate (Datum vrácení), všechna můžou mít relace k poli Datum v tabulce kalendářních dat, ale aktivní relace může být jenom jedno z nich. V tomto případě, protože DateKey představuje datum transakce, a tedy nejdůležitější datum, by to nejlépe 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 Total Sales:=SUM([SalesAmount]) se umístí do hodnot VALUES a pole FiscalYear a FiscalQuarter z tabulky kalendářních dat Kalendáře se umístí do řádků.
Tato přímočará kontingenční tabulka funguje správně, protože chceme sečíst celkový prodej podle data transakce v DateKey. Naše míra Total Sales používá kalendářní data v DateKey a sečte se podle fiskálního roku a fiskálního čtvrtletí, protože existuje relace mezi DateKey v tabulce Sales a sloupcem Date v tabulce kalendářních dat.
Neaktivní relace
Ale co kdybychom chtěli sečíst celkové prodeje ne podle data transakce, ale podle data expedice? Potřebujeme relaci mezi sloupcem ShipDate (Datum expedice) v tabulce Sales (Prodej) a sloupcem Date (Datum) v tabulce Calendar (Kalendář). Pokud tento vztah nevytvoříme, naše agregace jsou vždy založené na datu transakce. Můžeme však mít více relací, i když může být aktivní jenom jedna, a protože datum transakce je nejdůležitější, získá aktivní relaci s tabulkou Kalendář.
V tomto případě má ShipDate neaktivní relaci, takže každý vzorec míry vytvořený k agregaci dat na základě data expedice musí zadat neaktivní relaci pomocí funkce USERELATIONSHIP .
Protože například existuje neaktivní relace mezi sloupcem Datum expedice v tabulce Sales (Prodej) a sloupcem Date (Datum) v tabulce Kalendář, můžeme vytvořit míru, která sečte celkové prodeje podle data expedice. K určení relace, která se má použít, používáme podobný vzorec:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Tento vzorec jednoduše uvádí: Výpočet součtu pro SalesAmount, ale filtrování pomocí relace mezi sloupcem ShipDate v tabulce Sales (Prodej) a sloupcem Date (Datum) v tabulce Calendar (Kalendář).
Když teď vytvoříme kontingenční tabulku a vložíme míru Total Sales by Ship Date (Celkové prodeje podle data expedice) do hodnot VALUES a Fiscal Year (Fiskální rok) a Fiscal Quarter (Fiskální čtvrtletí) na řádky, uvidíme stejný celkový součet, ale všechny ostatní součtové částky 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žít pouze jednu tabulku kalendářních dat, ale vyžaduje, aby všechny míry (například Total Sales by Ship Date) odkazovala na neaktivní relaci ve vzorci. Existuje další alternativa, to znamená použít více tabulek kalendářních dat.
Více tabulek kalendářních dat
Dalším způsobem, jak pracovat s více sloupci kalendářních dat v tabulce faktů, je vytvořit několik tabulek kalendářních dat a vytvořit mezi nimi samostatné aktivní relace. Podívejme se znovu na příklad tabulky Sales. Máme tři sloupce s kalendářními daty, podle kterých bychom mohli chtít agregovat data:
-
DateKey s datem prodeje pro každou transakci.
-
Datum expedice – s datem a časem, kdy byly prodané položky odeslány zákazníkovi.
-
A ReturnDate – s datem a časem přijetí jedné nebo více vrácených položek.
Nezapomeňte, že nejdůležitější je pole DateKey s datem transakce. Většinu našich agregací budeme provádět na základě těchto dat, takže určitě budeme chtít vytvořit relaci mezi tímto datem a sloupcem Date v tabulce Kalendář. Pokud nechceme vytvářet neaktivní relace mezi datem ShipDate a ReturnDate a polem Date 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í. Pak mezi nimi můžeme vytvořit aktivní vztahy.
V tomto příkladu jsme vytvořili další tabulku kalendářních 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 pojmenovat tak, aby se odlišily od stejných sloupců v tabulce Kalendář. Vytvořili jsme například sloupce s názvy ShipYear, ShipMonth, ShipQuarter atd.
Pokud vytvoříme kontingenční tabulku a umístíme míru Total Sales do hodnot VALUES a ShipFiscalYear a ShipFiscalQuarter na řádky, zobrazí se stejné výsledky, které jsme viděli při vytvoření neaktivní relace, a speciální počítané pole Total Sales by Ship Date (Celkové prodeje podle data expedice).
Každý z těchto přístupů vyžaduje pečlivé zvážení. Pokud používáte více relací s jednou tabulkou kalendářních dat, možná budete muset vytvořit speciální míry, které přenášejí neaktivní relace pomocí funkce USERELATIONSHIP. Na druhou stranu může být vytváření více tabulek kalendářních dat v seznamu polí matoucí, a protože v datovém modelu máte více tabulek, bude to vyžadovat více paměti. Experimentujte s tím, co vám nejlépe vyhovuje.
Vlastnost Tabulka kalendářních dat
Vlastnost Tabulka kalendářních dat nastavuje metadata potřebná k tomu, aby funkce Time-Intelligence, jako jsou TOTALYTD, PREVIOUSMONTH a DATESBETWEEN, aby správně fungovaly. Když se výpočet spustí pomocí jedné z těchto funkcí, modul vzorců Power Pivotu ví, kam má přejít, aby získal data, která potřebuje.
Upozornění: Pokud tato vlastnost není nastavená, nemusí míry používající funkce jazyka DAX Time-Intelligence vrátit správné výsledky.
Když nastavíte vlastnost Tabulka kalendářních dat, zadáte tabulku kalendářních dat a sloupec kalendářních dat datového typu Datum (datetime).
Postupy: Nastavení vlastnosti Tabulka kalendářních dat
-
V okně PowerPivot vyberte tabulku Kalendář .
-
Na kartě Návrh klikněte na Označit jako tabulku kalendářních dat.
-
V dialogovém okně Označit jako tabulku kalendářních dat vyberte sloupec s jedinečnými hodnotami a datovým typem Datum.
Práce s časem
Všechny hodnoty kalendářních dat s datovým typem Datum v Excelu nebo SQL Serveru jsou ve skutečnosti číslo. V tomto čísle jsou zahrnuté číslice, které odkazují na čas. V mnoha případech je tento čas pro každý řádek půlnoc. Pokud například pole DateTimeKey v tabulce faktů Sales obsahuje hodnoty jako 19.10.2010 00:00:00, znamená to, že hodnoty jsou na denní úrovni přesnosti. Pokud hodnoty pole DateTimeKey obsahují čas, například 19.10.2010 8:44:00 AM, znamená to, že hodnoty jsou na úrovni minut přesnosti. Hodnoty můžou být také na úrovni hodin nebo dokonce sekundové úrovně přesnosti. Úroveň přesnosti v časové hodnotě bude mít významný vliv na způsob vytváření tabulky kalendářních dat a na vztahy mezi ní a tabulkou faktů.
Musíte určit, jestli budete 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 kalendářních dat, například Ráno, Odpoledne nebo Hodina, jako pole data času v oblastech Řádek, Sloupec nebo Filtr kontingenční tabulky.
Poznámka: Dny představují nejmenší časovou jednotku, se kterou můžou funkce časového měřítka jazyka DAX pracovat. Pokud nepotřebujete pracovat s časovými hodnotami, měli byste snížit přesnost dat, abyste jako minimální jednotku použili dny.
Pokud chcete data agregovat na časovou úroveň, bude tabulka kalendářních dat potřebovat sloupec kalendářních dat se zahrnutým časem. Ve skutečnosti bude potřebovat sloupec kalendářních dat s jedním řádkem pro každou hodinu nebo dokonce pro každou minutu každého dne, pro každý rok v rozsahu dat. Je to proto, že pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce faktů a sloupcem kalendářních dat v tabulce kalendářních dat, musíte mít odpovídající hodnoty. Jak si dokážete představit, pokud zahrnete hodně let, může to být pro velmi velkou tabulku kalendářních dat.
Ve většině případů ale chcete data agregovat jenom do dne. Jinými slovy, sloupce jako Rok, Měsíc, Týden nebo Den v týdnu použijete jako pole v oblastech Řádek, Sloupec nebo Filtr kontingenční tabulky. V tomto případě musí sloupec kalendářních dat v tabulce kalendářních dat obsahovat pouze jeden řádek pro každý den v roce, jak jsme popsali dříve.
Pokud váš sloupec kalendářních dat obsahuje časovou úroveň přesnosti, ale budete agregovat jenom na úroveň dne, abyste vytvořili relaci mezi tabulkou faktů a tabulkou kalendářních dat, možná budete muset tabulku faktů upravit vytvořením nového sloupce, který zkrátí hodnoty ve sloupci kalendářního data na hodnotu dne. Jinými slovy, převeďte hodnotu jako 19.10.2010 8:44:00AM na 19.10.2010 12:00:00 AM. Potom můžete vytvořit relaci mezi tímto novým sloupcem a sloupcem kalendářního data v tabulce kalendářních dat, protože hodnoty se shodují.
Podívejme se na příklad. Na tomto obrázku je sloupec DateTimeKey v tabulce faktů Sales (Prodej). Všechny agregace pro data v této tabulce musí být pouze na úrovni dne, a to pomocí sloupců v tabulce kalendářních dat kalendáře, jako jsou Rok, Měsíc, Čtvrtletí atd. Čas zahrnutý v hodnotě není relevantní, pouze skutečné datum.
Vzhledem k tomu, že tato data nemusíme analyzovat na časové úrovni, nepotřebujeme, aby sloupec Datum v tabulce kalendářních dat obsahoval jeden řádek pro každou hodinu a každou minutu každého dne v každém roce. Sloupec Date (Datum) v naší tabulce kalendářních dat tedy vypadá takto:
Pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce Sales a sloupcem Date v tabulce Kalendář, můžeme vytvořit nový počítaný sloupec v tabulce faktů Sales a pomocí funkce TRUNC zkrátit hodnotu data a času ve sloupci DateTimeKey na hodnotu kalendářního data, která odpovídá hodnotám ve sloupci Date v tabulce Calendar. Náš vzorec vypadá takto:
=TRUNC([DateTimeKey],0)
Tím získáme nový sloupec (pojmenovali jsme 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 Date v tabulce Calendar.
Podobně můžeme v tabulce Sales vytvořit počítaný sloupec, který snižuje přesnost času ve sloupci DateTimeKey na hodinovou úroveň přesnosti. V tomto případě nebude funkce TRUNC fungovat, ale přesto můžeme použít jiné funkce DAX Date a Time k extrakci a opětovnému zřetězení nové hodnoty na hodinovou úroveň přesnosti. Můžeme použít vzorec podobný tomuto:
= DATE (YEAR([DateTimeKey]); MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Náš nový sloupec vypadá takto:
Za předpokladu, že sloupec Datum v tabulce kalendářních dat obsahuje hodnoty na hodinovou úroveň přesnosti, můžeme mezi nimi vytvořit relaci.
Lepší použitelnost kalendářních dat
Mnoho sloupců kalendářních dat, které vytvoříte v tabulce kalendářních dat, je nezbytných pro jiná pole, ale ve skutečnosti nejsou při analýze tak užitečné. Například pole DateKey v tabulce Sales, na kterou jsme odkazovali a které jsme si ukázali v 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 hlediska analýzy a vytváření sestav to ale není tak užitečné, protože ho nemůžeme použít jako pole řádku, sloupce nebo filtru v kontingenční tabulce nebo sestavě.
Podobně je v našem příkladu velmi užitečný a kritický sloupec Datum v tabulce Kalendář, ale nemůžete ho použít jako rozměr v kontingenční tabulce.
Aby byly tabulky a sloupce v nich co nejužitečnější a aby se seznamy polí kontingenční tabulky nebo sestavy Power View snadněji navigovaly, je důležité skrýt nepotřebné sloupce v klientských nástrojích. Můžete také chtít skrýt určité tabulky. Tabulka Svátky zobrazená dříve obsahuje kalendářní data svátků, která jsou důležitá pro určité sloupce v tabulce Kalendář, ale sloupce Datum a Svátky v samotné tabulce Svátky nemůžete použít jako pole v kontingenční tabulce. I tady platí, že 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 můžete v Power Pivotu pojmenovat podle potřeby. Mějte ale na paměti, že zejména pokud budete sešit sdílet s jinými uživateli, dobrá konvence vytváření názvů usnadňuje identifikaci tabulek a kalendářních dat, a to nejen v seznamech polí, ale také ve vzorcích Power Pivotu a DAX.
Jakmile budete mít v datovém modelu tabulku kalendářních dat, můžete začít vytvářet míry, které vám pomůžou data na maximum. Některé můžou být stejně jednoduché jako sečtení celkových prodejů za aktuální rok a jiné můžou být složitější, kdy je potřeba filtrovat konkrétní rozsah jedinečných kalendářních dat. Další informace najdete v tématu Míry v Power Pivotu a funkcích časového měřítka.
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 daty transakcí obsahovat data textového datového typu. To znamená, že datum, které se zobrazí jako 2012-12-04T11:47:09, ve skutečnosti vůbec není datum nebo alespoň není typem data, kterému Power Pivot rozumí. Ve skutečnosti je to jen text, který se čte jako datum. Aby bylo možné vytvořit relaci mezi sloupcem kalendářního data v tabulce faktů a sloupcem kalendářního data v tabulce kalendářních dat, musí být oba sloupce datového typu Datum .
Při pokusu o změnu datového typu pro sloupec kalendářních dat, která jsou datovým typem text, na datový typ kalendářního data může Power Pivot obvykle interpretovat kalendářní data a převést je na datový typ skutečného data automaticky. Pokud Power Pivot nemůže provést převod datového typu, zobrazí se chyba neshody typů.
Přesto můžete data převést na datový typ skutečného data. Můžete vytvořit nový počítaný sloupec a použít vzorec DAX k parsování roku, měsíce, dne, času atd. z textových řetězců a pak ho znovu zřetězíte tak, aby Power Pivot mohl číst jako skutečné datum.
V tomto příkladu jsme do Power Pivotu naimportovali tabulku faktů s názvem Prodej. Obsahuje sloupec s názvem DateTime. Hodnoty vypadají takto:
Když se podíváme na kartu Domů ve skupině Power Pivotu Formátování na datový typ, uvidíme, že se jedná o datový typ Text.
Nemůžeme vytvořit relaci mezi sloupcem DateTime a datem v naší tabulce kalendářních dat, protože se datové typy neshodovaly. Pokud se pokusíme změnit datový typ na Datum, zobrazí se chyba neshody typů:
V tomto případě power pivot nemohl převést datový typ z textu na datum. Tento sloupec můžeme dál používat, ale abychom ho dostali do datového typu skutečného data, musíme vytvořit nový sloupec, který text analyzuje a znovu vytvoří na hodnotu, kterou Power Pivot dokáže nastavit jako datový typ Datum.
Nezapomeňte, že z části Práce s časem dříve v tomto článku; pokud není nutné, aby vaše analýza měla úroveň přesnosti v čase dne, měli byste převést data v tabulce faktů na denní úroveň přesnosti. S ohledem na to chceme, aby hodnoty v novém sloupci byly na denní úrovni přesnosti (s výjimkou času). Hodnoty ve sloupci DateTime můžeme převést na datový typ kalendářního data a pomocí následujícího vzorce odebrat časovou úroveň přesnosti:
=DATE(LEFT([DateTime);4); MID([DateTime];6;2); MID([DateTime];9;2))
Tím získáme nový sloupec (v tomto případě s názvem Date). Power Pivot dokonce rozpozná hodnoty jako kalendářní data a automaticky nastaví datový typ na Datum.
Pokud chceme zachovat úroveň přesnosti času, jednoduše rozšíříme vzorec 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))
Když teď máme sloupec Date (Datum) datového typu Date (Datum), můžeme vytvořit relaci mezi ním a sloupcem kalendářního data v kalendářním datu.
Další zdroje
Rychlý úvod: Naučte se základy jazyka DAX za 30 minut
