Princip tabulek kalendářních dat a jejich vytváření v PowerPivotu v Excelu

Tabulky kalendářních dat v PowerPivotu jsou nezbytné pro procházení a výpočty dat v průběhu času. Tento článek podrobně popisuje tabulky kalendářních dat a jejich vytváření v Power Pivotu. Tento článek popisuje zejména:

• Proč je tabulka kalendářních dat důležitá pro procházení a výpočty dat podle kalendářních dat a času.
• Jak pomocí Power Pivotu přidat tabulku kalendářních dat do datového modelu.
• Jak vytvořit nové sloupce kalendářních dat, jako je Rok, Měsíc a Období, v tabulce kalendářních dat
• Jak vytvořit relace mezi tabulkami kalendářních dat a tabulkami faktů.
• Jak pracovat s časem.

Tento článek je určený pro nové uživatele Power Pivotu. Je však důležité, abyste již 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íry. Další informace o vytváření měr pomocí funkcí časového měřítka jazyka DAX najdete v tématu Časové měřítko v PowerPivotu v Excelu.

Obsah

• Principy tabulek kalendářních dat

• Přidání tabulky kalendářních dat do datového modelu

  • Import z relační databáze

  • Vytvoření tabulky kalendářních dat v Excelu

    • Postup: Vytvoření tabulky kalendářních dat v Excelu a její zkopírování do datového modelu

• Přidání nových sloupců kalendářních dat do tabulky kalendářních dat

  • Funkce data a času jazyka DAX

  • Příklady vzorců pro kalendářní rok

    • Rok
    • Měsíc:
    • Čtvrtletí
    • Název měsíce

  • Příklady vzorců pro fiskální rok

    • Fiskální rok
    • Fiskální měsíc
    • Fiskální čtvrtletí

• Svátky nebo zvláštní data

• Vlastní kalendář – třináct čtyřtýdenních období

  • Týden
  • Period
  • Období fiskálního roku
  • Období ve fiskálním roce

• Relace

  • Více relací
  • Neaktivní relace
  • Více tabulek kalendářních dat

• Vlastnost Tabulka kalendářních dat

• Práce s časem

• Zvýšení použitelnosti dat

• Dodatek

  • Převedení datového typu Text na datový typ Datum
  • Další zdroje

Principy tabulek kalendářních dat

Téměř veškerá analýza dat zahrnuje procházení a porovnávání dat v závislosti na kalendářních datech a časech. Můžete chtít například sečíst částky prodeje za poslední fiskální čtvrtletí a porovnat je s jinými čtvrtletími nebo můžete vypočítat závěreč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 znázornění dat a času. Tabulka kalendářních dat bude například často obsahovat sloupce jako Fiskální rok, Měsíc, Čtvrtletí nebo Období, které můžete vybrat jako pole ze seznamu polí při filtrování a vytváření průřezů dat v kontingenčních tabulkách nebo sestavách nástroje Power View.

Seznam polí Power View

Aby sloupce kalendářních dat jako Rok, Měsíc a Čtvrtletí zahrnovaly všechna data v odpovídajícím rozsahu, musí tabulka kalendářních dat obsahovat aspoň jeden sloupec se souvislou sadou kalendářních dat. To znamená, že tento sloupec musí obsahovat jeden řádek pro každý den pro každý rok zahrnutý do tabulky kalendářních dat.

Pokud například data, která chcete procházet, obsahují data od 1. února 2010 do 30. listopadu 2012 a vytváříte kalendářní rok, budete chtít tabulku kalendářních dat s rozsahem dat od 1. ledna 2010 do 31. prosince 2012. Každý rok ve vaší tabulce kalendářních dat musí obsahovat všechny dny pro každý rok. Pokud budete svoje data pravidelně aktualizovat novějšími daty, bude možná lepší koncové datum zkrátit o rok nebo dva, abyste nemuseli tabulku dat průběžně aktualizovat.

Tabulka kalendářních dat se souvislou sadou kalendářních dat

Pokud vytváříte sestavu pro fiskální rok, můžete vytvořit tabulku dat se souvislou sadou kalendářních dat pro každý fiskální rok. Pokud například fiskální rok začíná 1. března a máte k dispozici data za fiskální roky 2010 až do aktuálního data (například fiskální rok 2013), můžete vytvořit tabulku kalendářních dat, která začíná 1. 3. 2009 a zahrnuje minimálně každý den každého fiskálního roku až do posledního data fiskálního roku 2013.

Pokud budete vykazovat kalendářní rok i fiskální rok, nemusíte vytvářet samostatné tabulky kalendářních dat. Jedna tabulka dat může obsahovat sloupce kalendářního roku, fiskálního roku a dokonce i kalendáře se třináctitýdenním čtyřtýdenním obdobím. Důležité je, aby tabulka kalendářních dat obsahovala souvislou sadu kalendářních dat pro všechny roky (včetně).

Přidání tabulky kalendářních dat do datového modelu

Tabulku kalendářních dat můžete do datového modelu přidat několika způsoby:

• Importovat 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.
• Import z webu Microsoft Azure Marketplace.

Podívejme se na každou z nich 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ž tabulka kalendářních dat a relace mezi ní a ostatními importovanými daty existují. Data a formát budou pravděpodobně odpovídat datům ve vašich datech faktů a data budou pravděpodobně začínat v minulosti a sahají daleko do budoucnosti. Tabulka kalendářních dat, kterou chcete importovat, může být hodně velká a obsahovat rozsah kalendářních dat nad rámec toho, co budete potřebovat zahrnout do datového modelu. Pomocí rozšířených funkcí filtru Průvodce importem tabulky v Power Pivotu můžete selektivně vybrat jenom data a konkrétní sloupce, které skutečně potřebujete. Tímto způsobem se 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, jako je fiskální rok, týden, název měsíce atd., protože ty už budou v importované tabulce. V některých případech ale může být po importu tabulky kalendářních dat do datového modelu potřeba vytvořit další sloupce kalendářních dat podle konkrétních potřeb vytváření sestav. Naštěstí je to snadné pomocí jazyka DAX. Další informace o vytváření polí tabulky kalendářních dat se dozvíte později. Každé prostředí je jiné. Pokud si nejste jistí, jestli vaše zdroje dat mají související kalendářní datum nebo tabulku kalendáře, obraťte se na správce databáze.

Vytvoření tabulky kalendářních dat v Excelu

V Excelu můžete vytvořit tabulku kalendářních dat a potom ji zkopírovat do nové tabulky v datovém modelu. To je opravdu docela snadné a dává vám to velkou flexibilitu.

Když v Excelu vytvoříte tabulku kalendářních dat, začnete s jedním sloupcem se souvislým rozsahem kalendářních dat. V excelovém listu pak můžete pomocí excelových vzorců vytvořit další sloupce, jako je 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. Vytvoření dalších sloupců kalendářních dat v Power Pivotu je popsané v části Přidání nových sloupců kalendářních dat do tabulky kalendářních dat dále v tomto článku.

Postup: Vytvoření tabulky kalendářních dat v Excelu a její zkopírování do datového modelu

1. V Excelu v prázdném listu zadejte do buňky A1 název záhlaví sloupce, který identifikuje rozsah kalendářních dat. Obvykle to bude něco jako Date, DateTime nebo DateKey.

2. Do buňky A2 zadejte počáteční datum. Příklad: 1/1/2010.

3. Klikněte na úchyt a přetáhněte jej dolů na číslo řádku, který obsahuje datum ukončení. Například 12/31/2016.
   

4. Vyberte všechny řádky ve sloupci Datum (včetně názvu záhlaví v buňce A1).

5. Ve skupině Styly klikněte na tlačítko Formátovat jako tabulku a vyberte styl.

6. V dialogovém okně Formátovat jako tabulku klikněte na tlačítko OK.
   

7. Zkopírujte všechny řádky včetně záhlaví.

8. V Power Pivotu klikněte na kartě Domů na Vložit.

9. Do pole Náhled>vložení Název tabulky zadejte název, třeba Datum nebo Kalendář. Možnost Použít první řádek jako záhlaví sloupcůponechte zaškrtnutou a klikněte na OK.
   
   Nová tabulka kalendářních dat (v tomto příkladu pojmenovaná Kalendář) v Power Pivotu vypadá takto:
   

   Poznámka

   Propojenou tabulku můžete vytvořit taky pomocí funkce Přidat do datového modelu. To ale způsobí, že váš sešit bude zbytečně velký, protože obsahuje dvě verze tabulky kalendářních dat. jeden v Excelu a jeden v PowerPivotu.

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, jako je 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 každého roku, je důležitá pro definování všech dat v rozsahu dat. Je taky potřeba k vytvoření relace mezi tabulkou faktů a tabulkou kalendářních dat. Tento jediný sloupec s jedním datem a 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 pomohou agregovat data pro oblast nebo skupinu kalendářních dat. Můžete chtít třeba 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 roku, měsíce nebo čtvrtletí, které vám umožní agregovat data za dané období.

Pokud jste tabulku kalendářních dat importovali z relačního zdroje dat, může už tabulka kalendářních dat obsahovat různé typy sloupců s kalendářními daty, které požadujete. V některých případech můžete chtít některé z těchto sloupců upravit nebo vytvořit další sloupce s kalendářními daty. Platí to 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 PowerPivotu je naštěstí díky funkcím data a času v jazyce DAX poměrně snadné.

Funkce data a času jazyka DAX

Pokud jste někdy pracovali s funkcemi data a času v excelových vzorcích, pravděpodobně budete znát funkce data a času. Přestože jsou tyto funkce podobné svým protějškům v Excelu, je zde několik důležitých rozdílů:

• Funkce data a času jazyka DAX používají datový typ datetime.
• Jako argument mohou mít hodnoty ze sloupce.
• Dají se použít k vrácení hodnot kalendářních dat nebo k manipulaci s nimi.

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 rozumět. Řadu těchto funkcí použijeme k vytvoření sloupců pro rok, čtvrtletí, fiskální měsíc atd.

Jazyk DAX obsahuje následující funkce data a času:

• DATUM
• DATUMHODN
• NÁSLEDUJÍCÍ DEN
• EDATE
• EOMONTH
• HODINA
• MINUTA
• MĚSÍC
• NOW
• SEKUNDA
• TIME
• ČASHODN
• DNES
• DENTÝDNE
• WEEKNUM
• ROK
• YEARFRAC

Existuje mnoho dalších funkcí jazyka DAX, které se dají ve vzorcích použít. Mnoho zde popsaných vzorců například používá matematické a trigonometrické funkce , jako je MOD a USEK, logické funkce , jako je KDYŽ, a textové funkce , jako je FORMÁT . Další informace o dalších funkcích jazyka DAX najdete dále v tomto článku v části Další materiály .

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ý rozsah kalendářních dat od 1. 1. 2010 do 31. 12. 2016.

Rok

=YEAR([datum])

V tomto vzorci vrátí funkce ROK rok z hodnoty ve sloupci Datum. Protože hodnota ve sloupci Date je datového typu datetime, funkce YEAR ví, jak z ní vrátit rok.

Měsíc

=MONTH([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žíváme funkci CELÁ.ČÁST k vrácení hodnoty kalendářního data jako celého čísla. Argumentem, který zadáte u funkce CELÁ.ČÁST, je hodnota ze sloupce Měsíc. Přičtěte 2 a potom ji vydělte 3, abyste dostali čtvrtletí, 1 až 4.

Název měsíce

=FORMAT([datum];"mmmm")

V tomto vzorci pro získání názvu měsíce použijeme funkci FORMAT k převodu číselné hodnoty ze sloupce Datum na text. Jako první argument určíme sloupec Datum a pak formát. Chceme, aby se v názvu měsíce zobrazovaly všechny znaky, proto použijeme "mmmm". Náš výsledek vypadá takto:

Pokud chceme vrátit název měsíce zkrácený na tři písmena, použijeme v argumentu formátu "mmm".

Den týdne

=FORMAT([datum];"ddd")

V tomto vzorci použijeme funkci FORMAT k získání názvu dne. Protože chceme jenom zkrátit název dne, uvedeme v argumentu formátu "ddd".

Ukázková kontingenční tabulka

Až budete mít pole pro kalendářní data jako Rok, Čtvrtletí, Měsíc atd., můžete je použít v kontingenční tabulce nebo sestavě. Na následujícím obrázku například vidíte pole SalesAmount z tabulky faktů Sales v VALUES a pole Year a Quarter z tabulky dimenzí Calendar v ROWS. 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 funkce, která by dokázala 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. Abychom získali fiskální rok, nejdřív pomocí funkce KDYŽ otestujeme, jestli je hodnota pro měsíc menší nebo rovna 6. Pokud je hodnota argumentu Měsíc menší nebo rovna 6, vrátí se ve druhém argumentu hodnota ze sloupce Rok. V opačném případě vrátí hodnotu z tabulky Year a přičte 1.

Dalším způsobem, jak určit hodnotu konce fiskálního roku, je vytvoření míry, která jednoduše určí měsíc. Například, FYE:=6. Místo čísla měsíce pak můžete odkazovat na název taktu. Například =KDYŽ([Měsíc]<=[Fiskální rok];[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, zda je hodnota pro [Měsíc] menší než nebo rovna 6, pak vezmeme 6 a přičteme hodnotu z Měsíc, jinak odečteme 6 od hodnoty z [Měsíc].

Fiskální čtvrtletí

=INT(([FiscalMonth]+2)/3)

Vzorec, který používáme pro fiskální čtvrtletí, je v podstatě stejný jako pro čtvrtletí v našem kalendářním roce. Jediný rozdíl je v tom, že místo [Měsíc] zadáme [FiskalMěsíc].

Svátky nebo zvláštní data

Můžete chtít zahrnout sloupec kalendářních dat, který označuje, že určitá data jsou svátky nebo jiná zvláštní data. Můžete třeba chtít sečíst celkový prodej na Nový rok přidáním pole Svátek do kontingenční tabulky, průřezu nebo filtru. Jindy 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 ji můžete zkopírovat nebo přidat do datového modelu jako propojenou tabulku. Ve většině případů není potřeba vytvářet relaci mezi tabulkou a tabulkou kalendáře. Všechny vzorce, které na něj odkazují, můžou k vrácení hodnot používat 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ý rok
11/25/2010	Den díkůvzdání
12/25/2010	Vánoce
01.01.11	Nový rok
11/24/2011	Den díkůvzdání
12/25/2011	Vánoce
01.01.12	Nový rok
22.11.2012	Den díkůvzdání
12/25/2012	Vánoce
1/1/2013	Nový rok
11/28/2013	Den díkůvzdání
12/25/2013	Vánoce
11/27/2014	Den díkůvzdání
12/25/2014	Vánoce
1. 1. 2014	Nový rok
11/27/2014	Den díkůvzdání
12/25/2014	Vánoce
1/1/2015	Nový rok
11/26/2014	Den díkůvzdání
12/25/2015	Vánoce
01.01.2016	Nový rok
11/24/2016	Den díkůvzdání
12/25/2016	Vánoce

V tabulce kalendářních dat vytvoříme sloupec s názvem Svátek a použijeme takovýto vzorec:

=VYHLEDATHODNOTU(svátky[svátky],svátky[datum],kalendář[datum])

Podívejme se na tento vzorec pozorněji.

Pomocí funkce VYHLEDATHODNOTU získáme hodnoty ze sloupce Svátek v tabulce Svátky. V prvním argumentu určíme sloupec, ve kterém bude výsledná hodnota. Sloupec Svátek určíme v tabulce Svátky, protože to je hodnota, kterou chceme vrátit.

=VYHLEDATHODNOTU(svátky[svátky],svátky[datum],kalendář[datum])

Potom určíme druhý argument, vyhledávací sloupec, který obsahuje data, která chceme hledat. Sloupec Datum určíme v tabulce Svátky takto:

=VYHLEDATHODNOTU(svátky[svátky],svátky[datum],kalendář[datum])

Nakonec určíme sloupec v tabulce kalendáře , ve kterém jsou data, která chceme hledat v tabulce Svátky . To je samozřejmě sloupec Datum v tabulce kalendáře .

=VYHLEDATHODNOTU(svátky[svátky],svátky[datum],kalendář[datum])

Sloupec Svátek vrátí název svátku pro každý řádek, jehož hodnota kalendářního data odpovídá určitému datu v tabulce Svátky.

Vlastní kalendář – třináct čtyřtýdenních období

Některé organizace, jako je maloobchod nebo stravovací služby, často vykazují různá období, například třináct čtyřtýdenních období. S třináctitýdenním kalendářem čtyřtýdenních období 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í má stejný počet dní a prázdniny obvykle spadají do stejného období každý rok. Můžete si vybrat, že perioda začne kterýkoli den v týdnu. Podobně jako u kalendářních dat v kalendářním nebo fiskálním roce můžete pomocí jazyka DAX vytvořit další sloupce s vlastními daty.

V příkladech níže začíná první celé období první nedělí fiskálního roku. V tomto případě začíná fiskální rok 1. 7.

Týden

Tato hodnota udává číslo týdne počínaje prvním celým týdnem ve fiskálním roce. V tomto příkladu začíná první celý týden nedělí, takže první celý týden prvního fiskálního roku v tabulce kalendáře ve skutečnosti začíná 4. 7. 2010 a pokračuje až do posledního celého týdne v tabulce kalendáře. I když tato hodnota sama o sobě není při analýze příliš užitečná, je potřeba s ní počítat pro použití v jiných vzorcích pro období 28 dnů.

=CELÁ.ČÁST([datum]-40356)/7)

Podívejme se na tento vzorec pozorněji.

Nejdřív vytvoříme vzorec, který vrátí hodnoty ze sloupce Datum jako celé číslo. Bude vypadat takto:

=INT([datum])

Pak chceme hledat první neděli v prvním fiskálním roce. Vidíme, že je 7/4/2010.

Teď od této hodnoty odečtěte 40356 (což je celé číslo pro 27. 6. 2010, poslední neděli předchozího fiskálního roku), abyste v naší tabulce kalendáře dostali počet dnů od začátku dnů, třeba takto:

=INT([datum]-40356)

Pak výsledek vydělte 7 (dny v týdnu), takto:

=CELÁ.ČÁST(([datum]-40356)/7)

Výsledek vypadá takto:

Period

Období v tomto vlastním kalendáři obsahuje 28 dní a vždy začíná nedělí. Tento sloupec vrátí číslo období začínající první nedělí v prvním fiskálním roce.

=CELÁ.ČÁST(([Týden]+3)/4)

Podívejme se na tento vzorec pozorněji.

Nejdřív vytvoříme vzorec, který vrátí hodnotu ze sloupce Týden jako celé číslo. Bude vypadat takto:

= INT([Týden])

Potom k této hodnotě přičtěte 3, například:

=INT([Týden]+3)

Pak výsledek vydělte 4, takto:

=CELÁ.ČÁST(([Týden]+3)/4)

Výsledek vypadá takto:

Období fiskálního roku

Tato hodnota vrátí fiskální rok pro určité období.

=CELÁ.ČÁST(([Období]+12)/13)+2008

Podívejme se na tento vzorec pozorněji.

Nejdřív vytvoříme vzorec, který vrátí hodnotu z argumentu Období a přičte 12:

=([Období]+12)

Výsledek vydělíme 13, protože fiskální rok má třináct období po 28 dnech:

=(([Období]+12)/13)

Přičteme rok 2010, protože to je první rok v tabulce:

=(([Období]+12)/13)+2010

Nakonec pomocí funkce CELÁ.ČÁST odebereme část výsledku a vrátíme celé číslo, když ho vydělíme 13, takto:

= CELÁ.ČÁST(([Období]+12)/13)+2010

Výsledek vypadá takto:

Období ve fiskálním roce

Tato hodnota vrátí číslo období (1 – 13) počínaje prvním celým obdobím (začínajícím 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ší, proto ho nejprve popíšeme v jazyce, kterému lépe rozumíme. Tento vzorec uvádí Vydělením hodnoty v poli [Období] číslem 13 získáte číslo období v roce (1–13). Pokud je toto číslo rovno 0, vrátí hodnotu 13.

Nejdřív vytvoříme vzorec, který vrátí zbytek hodnoty z období hodnotou 13. MOD (matematické a trigonometrické funkce) můžeme použít takto:

= MOD([Období];13)

To většinou vrátí požadovaný výsledek, s výjimkou situací, kdy je hodnota v poli Období 0, protože tato data nespadají do prvního fiskálního roku, jako je tomu v prvních pěti dnech naší ukázkové tabulky kalendářních dat. O to se postaráme pomocí funkce KDYŽ. V případě, že je výsledek 0, vrátí se výsledek 13, takto:

= KDYŽ(MOD([Období];13);MOD([Období];13);13)

Výsledek vypadá takto:

Ukázková kontingenční tabulka

Na následujícím obrázku vidíte kontingenční tabulku s polem SalesAmount z tabulky faktů Sales v VALUES a poli PeriodFiscalYear a PeriodInFiscalYear z tabulky dimenzí kalendářních dat v ROWS. SalesAmount se agreguje pro kontext podle fiskálního roku a 28denního období ve fiskálním roce.

Relace

Po vytvoření tabulky kalendářních dat v datovém modelu je potřeba 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 a vytvořit relaci mezi tabulkou faktů a daty transakcí.

Vzhledem k tomu, že potřebujete vytvořit relaci založenou na kalendářních datech, ujistěte se, že tuto relaci vytvoříte mezi sloupci, jejichž hodnoty jsou datového typu datum a čas (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ů 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 kalendářních dat (pojmenované Kalendář). Toto 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á budou zahrnovat všechna možná data ve vaší tabulce faktů.

Pokud se při pokusu o vytvoření relace zobrazí chyba neshody typů, je to pravděpodobně proto, že sloupec v tabulce faktů nemá datový typ Datum. K tomu může dojít, když Power Pivot nedokáže automaticky převést nekalendářní datum (obvykle textový datový typ) na datový typ kalendářní datum. Sloupec můžete v tabulce faktů dál používat, 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 datového typu Text na datový typ Datum dál v příloze.

Více relací

V některých případech může být potřeba vytvořit více relací nebo více tabulek kalendářních dat. Pokud například tabulka faktů Prodej obsahuje více polí kalendářních dat, například DateKey, ShipDate a ReturnDate, mohou mít všechna relaci s polem Date v tabulce kalendářních dat (DateKey), ale pouze jedno z nich může být aktivní relací. Protože v tomto případě představuje datum transakce, a tedy nejdůležitější datum, bude nejvhodnější sloužit jako aktivní relace. Ostatní mají neaktivní vztahy.

Následující kontingenční tabulka vypočítá celkový prodej podle fiskálního roku a fiskálního čtvrtletí. Míra s názvem Celkové prodeje se vzorcem Celkové prodeje:=SUMA([ČástkaProdeje])) se umístí do složky HODNOTY a pole FiscalYear a FiscalQuarter z tabulky kalendářních dat se umístí do funkce ŘÁDKY.

Tato přímočará kontingenční tabulka funguje správně, protože chceme sečíst naše celkové prodeje podle data transakce v DateKey. Naše míra Celkové prodeje používá data v DateKey a je shrnutá podle fiskálního roku a fiskálního čtvrtletí, protože mezi DateKey v tabulce Sales a sloupcem Date v tabulce kalendářních dat existuje vztah.

Neaktivní relace

Ale co kdybychom chtěli sečíst naše celkové prodeje ne podle data transakce, ale podle data expedice? Potřebujeme relaci mezi sloupcem DatumExpedice v tabulce Prodej a sloupcem Datum v tabulce Kalendář. Pokud tuto relaci nevytvoříme, budou naše agregace vždycky založené na datu transakce. Můžeme mít ale víc relací, i když jen jedna z nich může být aktivní, a protože datum transakce je nejdůležitější, získá aktivní relaci s tabulkou kalendáře.

V tomto případě má DatumOdeslání neaktivní relaci, takže jakýkoli vzorec míry vytvořený pro agregaci dat na základě dat expedice musí určit neaktivní relaci pomocí funkce USERELATIONSHIP .

Protože třeba existuje neaktivní relace mezi sloupcem Datum odeslání v tabulce Prodej a sloupcem Datum v tabulce kalendáře, můžeme vytvořit míru, která sečte celkový prodej podle data odeslání. Pomocí tohoto vzorce určíme relaci, která se má použít:

Celkový prodej podle data expedice:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Tento vzorec jednoduše uvádí: Vypočítat součet pro ČástkaProdeje, ale filtrovat pomocí vztahu mezi sloupcem DatumExpedice v tabulce Prodej a sloupcem Datum v tabulce Kalendář.

Když teď vytvoříme kontingenční tabulku a vložíme míru Celkové prodeje podle data expedice do sloupce HODNOTY a pole Fiskální rok a fiskální čtvrtletí do pole ŘÁDKY, zobrazí se stejný celkový součet, ale všechny ostatní částky součtu za fiskální rok a fiskální čtvrtletí se liší, protože jsou založené na datu expedice, ne datu transakce.

Použití neaktivních relací umožňuje použít pouze jednu tabulku kalendářních dat, vyžaduje se však, aby všechny míry (například Celkové prodeje podle data expedice) odkazovaly ve vzorci na neaktivní relaci. Existuje ještě jiná alternativa, a to použití více tabulek kalendářních dat.

Více tabulek kalendářních dat

Další možností, jak pracovat s více sloupci kalendářních dat v tabulce faktů, je vytvořit více tabulek kalendářních dat a vytvořit mezi nimi samostatné aktivní relace. Podívejme se znovu na příklad tabulky Prodej. Máme tři sloupce s kalendářními daty, podle kterých bychom mohli chtít agregovat data:

• Klíč DateKey s datem prodeje pro každou transakci.
• Datum odeslání – s datem a časem, kdy byly prodané položky odeslány zákazníkovi.
• 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 provádět na základě těchto kalendářních dat, takže budeme určitě chtít vztah mezi datem a sloupcem Datum v tabulce kalendáře. Pokud nechceme vytvořit neaktivní relace mezi DatumOdeslá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 dat pro Datum odeslání a Datum vrácení. Pak mezi nimi můžeme vytvářet aktivní vztahy.

V tomto příkladu jsme vytvořili jinou tabulku kalendářních dat nazvanouKalendářExpedice. To samozřejmě znamená taky vytvoření dalších datových sloupců. Vzhledem k tomu, že se tyto datové sloupce nacházejí v jiné tabulce kalendářních dat, chceme je pojmenovat tak, aby se odlišily od stejných sloupců v tabulce kalendáře. Vytvořili jsme třeba sloupce s názvy RokExpedice, MěsícExpedice, ČtvrtletíExpedice atd.

Pokud vytvoříme naši kontingenční tabulku a vložíme míru Celkové prodeje do HODNOT a pole ShipFiscalYear a ShipFiscalQuarter do ROWS, zobrazí se stejné výsledky, jaké jsme viděli, když jsme vytvořili neaktivní relaci a speciální počítané 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 může být nutné vytvořit speciální míry, které přenesou přes neaktivní relace pomocí funkce USERELATIONSHIP. Naopak vytváření více tabulek kalendářních dat může být v seznamu polí matoucí, a protože máte v datovém modelu více tabulek, bude vyžadovat víc 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 Time-Intelligence funkce jako TOTALYTD, PREVIOUSMONTH nebo DATESBETWEEN správně fungovaly. Když se spustí výpočet pomocí některé z těchto funkcí, modul vzorců Power Pivotu ví, kam přejít, aby získal potřebná data.

Když nastavíte vlastnost Tabulka kalendářních dat, zadáte do ní tabulku kalendářních dat a sloupec kalendářních dat datového typu Datum (datum a čas).

Postupy: Nastavení vlastnosti Tabulka kalendářních dat

1. V okně PowerPivot vyberte tabulku kalendáře .
2. Na kartě Návrh klikněte na Označit jako tabulku kalendářních dat.
3. V dialogovém okně Označit jako tabulku kalendářních dat vyberte sloupec s jedinečnými hodnotami a datový typ Datum.

Práce s časem

Všechny hodnoty kalendářních dat s datovým typem Datum v Excelu nebo na SQL Serveru jsou ve skutečnosti čísla. Součástí tohoto čísla jsou číslice, které odkazují na čas. V mnoha případech je tímto časem pro každý řádek půlnoc. Pokud má třeba pole DateTimeKey v tabulce faktů Prodej hodnoty jako 10/19/2010 12:00:00 AM, znamená to, že hodnoty odpovídají denní úrovni přesnosti. Pokud hodnoty polí DateTimeKey obsahují čas, například 19.10.2010 8:44:00 AM, znamená to, že hodnoty odpovídají minutové přesnosti. Hodnoty mohou být také s přesností na hodinovou nebo dokonce sekundovou úroveň. Úroveň přesnosti časové hodnoty bude mít významný vliv na způsob vytvoření tabulky kalendářních dat a vztahy mezi ní a tabulkou faktů.

Potřebujete určit, jestli budete data agregovat s denní nebo časovou přesností. Jinými slovy, jako pole dat v kontingenční tabulce řádků nebo sloupců nebo filtrů můžete chtít použít sloupce typu dopoledne, odpoledne nebo hodinu.

Pokud chcete data agregovat na úroveň času, bude tabulka kalendářních dat potřebovat sloupec kalendářních dat obsahující čas. Ve skutečnosti bude potřebovat sloupec s jedním řádkem pro každou hodinu nebo možná dokonce každou minutu každého dne pro každý rok v rozsahu dat. Důvodem je to, že pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce faktů a sloupcem kalendářního data 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 vytvořit velmi velkou tabulku data.

Ve většině případů ale budete chtít data agregovat jenom za konkrétní den. Jinými slovy, jako pole v oblasti řádků, sloupců nebo filtrů kontingenční tabulky použijete sloupce jako Rok, Měsíc, Týden nebo Den týdne. V tomto případě stačí, aby sloupec kalendářních dat v tabulce kalendářních dat obsahoval pouze jeden řádek pro každý den v roce, jak jsme popsali dříve.

Pokud sloupec kalendářních dat obsahuje časovou úroveň přesnosti, ale vy budete agregovat pouze na úrovni dne, abyste vytvořili relaci mezi tabulkou faktů a tabulkou kalendářních dat, možná budete muset upravit tabulku faktů vytvořením nového sloupce, který zkrátí hodnoty ve sloupci kalendářních dat na hodnotu dne. Jinými slovy, převeďte hodnotu například 19.10.2010 8:44:00 nahodnotu 19.10.2010 12:00:00 dop. 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. Tento obrázek znázorňuje sloupec DateTimeKey v tabulce faktů Prodej. Všechny agregace pro data v této tabulce musí být pouze na úrovni dne, a to pomocí sloupců z tabulky kalendářních dat, jako je Rok, Měsíc, Čtvrtletí atd. Nezáleží na čase zahrnutém do hodnoty, záleží pouze na skutečném datu.

Protože tato data nemusíme analyzovat na úrovni času, 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 Datum v tabulce kalendářních dat tedy vypadá takto:

Pokud chcete vytvořit relaci mezi sloupcem DateTimeKey v tabulce Sales a sloupcem Date v tabulce Calendar, můžeme vytvořit nový počítaný sloupec v tabulce faktů Sales a pomocí funkce USEKNOUT zkrátit hodnotu data a času ve sloupci DateTimeKey na hodnotu data, která odpovídá hodnotám ve sloupci Date v tabulce Kalendář. Náš vzorec vypadá takto:

=USEK([DateTimeKey];0)

Získáme tak nový sloupec (pojmenovaný 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áře.

Podobně můžeme vytvořit počítaný sloupec v tabulce Prodej, který sníží časovou přesnost ve sloupci DateTimeKey na hodinovou přesnost. V tomto případě nebude funkce USEKNOUT fungovat, ale stále můžeme použít jiné funkce data a času jazyka DAX k extrakci a opětovnému zřetězení nové hodnoty s přesností na hodinu. Můžeme použít takovýhle vzorec:

= DATUM (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Náš nový sloupec vypadá takto:

Pokud sloupec kalendářních dat v tabulce kalendářních dat obsahuje hodnoty s přesností na hodiny, můžeme mezi nimi vytvořit relaci.

Zvýšení použitelnosti dat

Mnoho sloupců kalendářních dat, které vytvoříte v tabulce kalendářních dat, je potřebných pro ostatní pole, ale ve skutečnosti nejsou příliš užitečné při analýze. Například pole DateKey v tabulce Prodej, na kterou jsme odkazovali a které jsme ukázali v tomto článku, je důležité, protože u každé transakce je zaznamenáno, že k ní dochází v určitém datu a čase. Z hlediska analýzy a vytváření sestav to ale není až tak užitečné, protože ho nemůžeme použít jako řádkové, sloupcové nebo filtrační pole v kontingenční tabulce nebo sestavě.

Podobně v našem příkladu je sloupec Datum v tabulce kalendáře velmi užitečný, ve skutečnosti důležitý, ale nemůžete ho použít jako dimenzi v kontingenční tabulce.

Pokud chcete, aby tabulky a sloupce v nich byly co nejužitečnější a abyste usnadnili navigaci v seznamech polí kontingenční tabulky nebo sestavy Power View, je důležité skrýt nadbytečné sloupce v klientských nástrojích. Můžete taky skrýt některé tabulky. Výše zobrazená tabulka Svátky obsahuje data svátků, která jsou důležitá pro určité sloupce v tabulce kalendáře. Sloupce Datum a Svátky v tabulce Svátky ale nemůžete použít jako pole v kontingenční tabulce. Chcete-li usnadnit navigaci v seznamech polí, můžete opět skrýt celou tabulku Svátky.

Dalším důležitým aspektem práce s daty jsou zásady vytváření názvů. Tabulky a sloupce v Power Pivotu můžete pojmenovat, jak chcete. Mějte ale na paměti, že zejména pokud budete sešit sdílet s jinými uživateli, dobré zásady vytváření názvů usnadňují identifikaci tabulek a dat nejen v seznamech polí, ale také v Power Pivotu a ve vzorcích jazyka DAX.

Až 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 naplno využít. Některé můžou být třeba jednoduché – třeba shrnout celkové prodeje za aktuální rok, jiné můžou být složitější – potřebujete v nich filtrovat podle určitého rozsahu jedinečných dat. Další informace najdete v tématu Míry v PowerPivotu a o funkcích časového měřítka.

Dodatek

Převedení datového typu Text na datový typ Datum

Tabulka faktů s daty transakcí může v některých případech obsahovat kalendářní data typu Text. To znamená, že datum zobrazené jako 2012-12-04T11:47:09 ve skutečnosti vůbec není datum nebo alespoň ne typ data, kterému Power Pivot rozumí. Je to vlastně jen text, který se čte jako datum. Pokud chcete vytvořit relaci mezi sloupcem kalendářních dat v tabulce faktů a sloupcem kalendářních dat v tabulce kalendářních dat, musí být oba sloupce datového typu Datum .

Když se obvykle pokusíte změnit datový typ sloupce s daty, která jsou textová, na datový typ kalendářní datum, Power Pivot tato data automaticky interpretuje a převede je na datový typ skutečné datum. Pokud Power Pivot nedokáže provést převod datového typu, zobrazí se chyba neshody typů.

Přesto ale můžete kalendářní data převést na skutečný datový typ kalendářního data. Můžete vytvořit nový počítaný sloupec a pomocí vzorce jazyka DAX zanalyzovat z textových řetězců rok, měsíc, den, čas atd. a potom je zřetězit dohromady tak, aby byl Power Pivot vyhodnocený jako skutečné datum.

V tomto příkladu jsme do Power Pivotu importovali tabulku faktů s názvem Prodej. Obsahuje sloupec s názvem DateTime. Hodnoty vypadají takto:

Pokud se podíváme na Datový typ ve skupině Formátování na kartě Domů v Power Pivotu, zjistíme, že jde o datový typ Text.

Relaci mezi sloupci Datum a čas v naší tabulce kalendářních dat nelze vytvořit, protože datové typy se neshodují. Pokud se pokusíme změnit datový typ na Datum, zobrazí se chyba neshody typu:

V tomto případě se doplňku Power Pivot nepovedlo 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é datum, musíme vytvořit nový sloupec, který tento text analyzuje a znovu z něj vytvoří hodnotu. Power Pivot může vytvořit datový typ Datum.

Vzpomeňte si, že v části Práce s časem výše v tomto článku; Pokud není nutné provádět analýzy s přesností na denní dobu, měli byste kalendářní data v tabulce faktů převést na úroveň dne. S ohledem na to chceme, aby hodnoty v našem novém sloupci byly na úrovni dne s přesností (bez času). Pomocí následujícího vzorce můžeme převést hodnoty ve sloupci Datum a čas na datový typ kalendářního data a odebrat úroveň přesnosti času:

=DATUM(LEFT([DateTime];4), MID([DateTime],6,2), MID([DateTime],9,2))

Získáme tak nový sloupec (v tomto případě pojmenovaný Date). Power Pivot dokonce rozpozná hodnoty jako kalendářní data a nastaví datový typ automaticky na Datum.

Pokud chceme zachovat časovou úroveň přesnosti, jednoduše rozšíříme vzorec tak, aby zahrnoval hodiny, minuty a sekundy.

=DATE(LEFT([DateTime];4), MID([DateTime],6,2), MID([DateTime],9,2)) +

ČAS(ČÁST([DatumČas];12;2), ČÁST([DatumČas];15;2), ČÁST([DatumČas];18;2))

Teď, když máme sloupec Datum s datovým typem Datum, můžeme vytvořit relaci mezi ním a sloupcem kalendářního data v kalendářním datu.

Další zdroje

Kalendářní data v PowerPivotu

Výpočty v Power Pivotu

Rychlý úvod: Naučte se základy jazyka DAX za 30 minut

Referenční informace k výrazům analýzy dat

Centrum zdrojů jazyka DAX