PowerPivot pro Excel - vyhledávací tabulka v datovém skladu

Jedna z věcí, které nejvíce uvádím v seznamu PowerPivot for Excel, je možnost přidat tabulky vyhledávání do datových sad. Většinu času, data, s nimiž pracujete, nemají všechna pole potřebná k vaší analýze. Můžete například mít pole s datem, ale musíte data seskupit podle čtvrtletí. Můžete napsat vzorec, ale je snadnější vytvořit jednoduchou vyhledávací tabulku v prostředí PowerPivot.

Tuto vyhledávací tabulku můžete také použít pro další seskupení, například název měsíce a první nebo druhou polovinu roku. V termínech datového skladování skutečně vytváříte tabulku dimenzí dat. V tomto článku vám dám pár příkladných rozměrových tabulek, které podpoří projekt PowerPivot for Excel.

Tabulka nové dimenze textů (vyhledávací)

Vezměme v úvahu tabulku s údaji o objednávce (data Contoso od společnosti Microsoft obsahují datovou množinu podobnou tomuto). Předpokládejme, že tabulka má pole pro zákazníka, datum objednávky, celkový počet objednávek a typ objednávky. Zaměřujeme se na pole typu objednávky. Předpokládejme, že pole typu objednávky obsahuje hodnoty jako:

• Netbooky
• Stolní počítače
• Monitory
• Projektory
• Tiskárny
• Skenery
• Digitální fotoaparáty
• Digitální zrcadlovky
• Filmové kamery
• Videokamery
• Kancelářské telefony
• Chytré telefony
• PDA
• Mobilní telefony Příslušenství

Ve skutečnosti byste měli kódy pro tyto, ale aby tento příklad jednoduchý, předpokládejme, že to jsou skutečné hodnoty v tabulce objednávek.

Pomocí aplikace PowerPivot pro aplikaci Excel můžete snadno uspořádat objednávky podle typu objednávky. Co kdybyste chtěli jinou skupinu? Předpokládejme například, že potřebujete seskupení typu "kategorie", jako jsou počítače, fotoaparáty a telefony. Tabulka objednávek neobsahuje pole "kategorie", ale můžete ji snadno vytvořit jako vyhledávací tabulku v programu PowerPivot for Excel.

Celá ukázková tabulka je v tabulce 1 níže. Zde jsou kroky:

• Krok 1: Pro vyhledávací tabulku potřebujete oddělený seznam. Toto bude vaše vyhledávací pole. Ze sady dat vytvořte z pole typu objednávky zřetelný seznam hodnot. Zadejte zřetelný seznam "typů" do sešitu aplikace Excel. Označte typ sloupce.
• Krok 2: Ve sloupci vedle vyhledávacího sloupce (Typ) přidejte nové pole, které chcete seskupit. V našem příkladu přidejte sloupec se štítkem s názvem Kategorie.
• Krok 3: Pro každou hodnotu ve vašem odlišném seznamu hodnot (typy v tomto příkladu) přidejte odpovídající kategorie "Kategorie". V našem jednoduchém příkladu zadejte do sloupce Kategorie buď počítače, fotoaparáty nebo telefony.
• Krok 4: Zkopírujte tabulku dat a typů do schránky.
• Krok 5: Otevřete sešit aplikace Excel s údaji o objednávce v programu PowerPivot pro aplikaci Excel. Spusťte okno PowerPivot. Klepnutím na tlačítko Vložit vložte novou vyhledávací tabulku. Zadejte název tabulky a ujistěte se, zda je zaškrtnuto "Použít první řádek jako záhlaví sloupce". Klepněte na tlačítko OK. V PowerPivot jste vytvořili vyhledávací tabulku.
• Krok 6: Vytvořte vztah mezi pole Typ v tabulce Objednávky a pole Kategorie ve vyhledávací tabulce. Klikněte na návrhovou stuhu a zvolte Vytvořit vztah. Proveďte výběr v dialogu Vytvořit vztah a klikněte na Vytvořit.

Při vytváření kontingenční tabulky v aplikaci Excel na základě dat PowerPivot budete moci seskupit podle vašeho nové kategorie. Mějte na paměti, že PowerPivot pro Excel podporuje pouze Inner Joins. Pokud z vyhledávací tabulky chybí "typ objednávky", všechny odpovídající záznamy tohoto typu chybí z libovolné kontingenční tabulky na základě dat PowerPivot. Budete je muset zkontrolovat čas od času.

Tabulka dimenze (vyhledávání)

Tabulka vyhledávání dat bude s největší pravděpodobností potřebná ve většině projektů PowerPivot for Excel. Většina datových sad má nějaký typ datových polí. K dispozici jsou funkce pro výpočet roku a měsíce.

Pokud však potřebujete text skutečného měsíce nebo čtvrtletí, musíte napsat komplexní vzorec. Je mnohem jednodušší zahrnout tabulku dimenze (vyhledávání) a porovnat ji s číslem měsíce ve vaší hlavní sadě dat. K tabulce objednávek budete muset přidat sloupec, který bude reprezentovat číslo měsíce z pole Datum objednávky. Vzorec DAX pro "měsíc" v našem příkladu je "= MONTH ([Datum objednávky]). Tím se v každém záznamu vrátí číslo od 1 do 12. V tabulce dimenzí se uvedou alternativní hodnoty, které odkazují na číslo měsíce. vám poskytne flexibilitu ve vaší analýze. Úplná tabulka rozměrů dat vzorku je uvedena níže v tabulce 2 .

Dimenze data nebo vyhledávací tabulka bude obsahovat 12 záznamů. Měsíční sloupec bude mít hodnoty 1 až 12. Ostatní sloupce budou obsahovat zkrácený měsíční text, celý měsíční text, čtvrtletí apod. Zde jsou kroky:

• Krok 1: Zkopírujte tabulku z níže uvedené tabulky 2 a vložte ji do PowerPivotu. Tuto tabulku můžete vytvořit v aplikaci Excel, ale ušetřím vám čas. Pokud používáte aplikaci Internet Explorer, měli byste mít možnost vložit přímo níže vybrané údaje. PowerPivot převezme formátování tabulky při testování. Pokud používáte jiný prohlížeč, možná budete muset nejprve vložit do aplikace Excel a zkopírovat jej z aplikace Excel a zvednout formátování tabulky.
• Krok 2: Otevřete sešit aplikace Excel s údaji o objednávce v programu PowerPivot for Excel. Spusťte okno PowerPivot. Klepnutím na tlačítko Vložit vložte vyhledávací tabulku zkopírované z níže uvedené tabulky nebo z aplikace Excel. Zadejte název tabulky a ujistěte se, zda je zaškrtnuto "Použít první řádek jako záhlaví sloupce". Klepněte na tlačítko OK. V PowerPivot jste vytvořili vyhledávací tabulku data.

• Krok 3 : Vytvořte vztah mezi políkem Měsíc v tabulce Objednávky a pole MonthNumber ve vyhledávací tabulce. Klikněte na návrhovou stuhu a zvolte Vytvořit vztah. Proveďte výběr v dialogu Vytvořit vztah a klikněte na Vytvořit.

Opět s přidáním dimenze data budete moci seskupit data do kontingenčního tabulky pomocí libovolné z různých hodnot z vyhledávací tabulky data. Seskupení podle čtvrtletí nebo názvu měsíce bude rychlé.

Tabulky rozměrů vzorků (vyhledávání)

stůl 1

Tabulka 2