PowerPivot pre Excel - Tabuľka vyhľadávania v Data Warehouse

Jednou z vecí, ktoré najviac uvádzam v programe PowerPivot for Excel, je schopnosť pridať vyhľadávacie tabuľky do súborov údajov. Väčšinu času údaje, s ktorými pracujete, nemajú všetky oblasti, ktoré potrebujete na analýzu. Môžete napríklad mať pole dátumu, ale musíte zoskupiť údaje štvrťročne. Môžete napísať vzorec, ale je jednoduchšie vytvoriť jednoduchú vyhľadávaciu tabuľku v prostredí PowerPivot.

Túto tabuľku vyhľadávania môžete použiť aj pre iné zoskupenie, ako napríklad názov mesiaca a prvú / druhú polovicu roka. V termínoch na ukladanie dát vlastne vytvárate tabuľku dimenzií dátumu. V tomto článku vám poskytnem pár príkladov rozmerových tabuliek na zvýšenie vášho projektu PowerPivot for Excel.

Tabuľka nových dimenzií textu (vyhľadávanie)

Pozrime sa na tabuľku s údajmi o objednávke (údaje Contoso od spoločnosti Microsoft obsahujú súbor údajov podobný tomuto). Predpokladajme, že tabuľka obsahuje polia pre zákazníka, dátum objednávky, celkový počet objednávok a typ objednávky. Zameriavame sa na pole typu objednávky. Predpokladajme, že pole typu objednávky obsahuje hodnoty ako:

• netbooky
• Stolové počítače
• monitory
• projektory
• tlačiarne
• skenery
• Digitálne fotoaparáty
• Digitálne zrkadlovky
• Filmové kamery
• videokamery
• Kancelárske telefóny
• Chytré telefóny
• PDA
• Mobilné telefóny Príslušenstvo

V skutočnosti by ste mali kódy pre tieto, ale aby tento príklad jednoduchý, predpokladajme, že sú to aktuálne hodnoty v tabuľke objednávok.

Pomocou programu PowerPivot pre program Excel by ste mohli ľahko zoskupovať objednávky podľa typu objednávky. Čo ak chcete mať iné zoskupenie? Predpokladajme napríklad, že potrebujete zoskupenie "kategórie", ako sú počítače, fotoaparáty a telefóny. Tabuľka objednávok neobsahuje pole kategórie, ale môžete ju ľahko vytvoriť ako tabuľku vyhľadávania v programe PowerPivot for Excel.

Celá vzorová vyhľadávacia tabuľka je uvedená nižšie v tabuľke 1 . Tu sú kroky:

• Krok 1: Pre vyhľadávaciu tabuľku potrebujete odlišný zoznam od typu poľa. Toto bude vaše vyhľadávacie pole. Z vášho súboru údajov vytvorte z poľa typu objednávky jasný zoznam hodnôt. Zadajte odlišný zoznam "typov" do zošita programu Excel. Označte typ stĺpca.
• Krok 2: V stĺpci vedľa vyhľadávacieho stĺpca (Typ) pridajte nové pole, na ktoré chcete zoskupiť. V našom príklade pridajte stĺpec so štítkom s názvom Kategória.
• Krok 3: Pre každú hodnotu vo vašom odlišnom zozname hodnôt (typy v tomto príklade) pridajte zodpovedajúce hodnoty kategórie. V našom jednoduchom príklade zadajte Počítače, Kamery alebo Telefóny do stĺpca Kategória.
• Krok 4: Skopírujte tabuľku dát typu a kategórie do schránky.
• Krok 5: Otvorte zošit programu Excel s údajmi o objednávke v nástroji PowerPivot for Excel. Spustite okno PowerPivot. Kliknite na možnosť Prilepiť, čím sa zobrazí vaša nová vyhľadávacia tabuľka. Zadajte názov tabuľky a uistite sa, že začiarknete možnosť "Použiť prvý riadok ako hlavičky stĺpcov". Kliknite na tlačidlo OK. Vytvorili ste vyhľadávaciu tabuľku v programe PowerPivot.
• Krok 6: Vytvorte vzťah medzi typom poľa v tabuľke Objednávky a kategóriou pole vo vyhľadávacej tabuľke. Kliknite na návrhovú stužku a vyberte Vytvoriť vzťah. Vyberte voľby v dialógovom okne Vytvoriť vzťah a kliknite na položku Vytvoriť.

Keď vytvoríte kontingenčnú tabuľku v programe Excel založenú na údajoch PowerPivot, budete môcť zoskupiť podľa poľa vašej novej kategórie. Majte na pamäti, že PowerPivot pre Excel podporuje iba Inner Joins. Ak máte vo vyhľadávacej tabuľke chýbajúci typ objednávky, všetky príslušné záznamy tohto typu budú chýbať z akejkoľvek kontingenčnej tabuľky založenej na údajoch PowerPivot. Budete to musieť skontrolovať čas od času.

Tabuľka dimenzie (vyhľadávanie)

Vyhľadávacia tabuľka s dátumom bude s najväčšou pravdepodobnosťou potrebná vo väčšine projektov programu PowerPivot for Excel. Väčšina dátových súborov má nejaký typ poľa dátumov. K dispozícii sú funkcie na výpočet roka a mesiaca.

Ak však potrebujete skutočný text mesiaca alebo štvrťrok, musíte napísať zložitý vzorec. Je to oveľa jednoduchšie zahrnúť tabuľku dimenzie dátumu (vyhľadávanie) a zosúladiť ju s číslom mesiaca vo vašej hlavnej množine údajov. Na tabuľku objednávok budete musieť pridať stĺpec, ktorý reprezentuje číslo mesiaca z poľa dátum objednávky. Vzorec DAX pre "mesiac" v našom príklade je "= MONTH ([Dátum objednávky]), čím sa v každom zázname vráti číslo medzi 1 a 12. V tabuľke dimenzií sa uvedú alternatívne hodnoty, ktoré odkazujú na číslo mesiaca. vám poskytne flexibilitu vo vašej analýze.Kompletná tabuľka rozmerov vzorových údajov je uvedená nižšie v tabuľke 2 .

Rozmer dátumu alebo vyhľadávacia tabuľka bude obsahovať 12 záznamov. Mesačný stĺpec bude mať hodnoty 1 - 12. Ďalšie stĺpce budú obsahovať skrátený mesiac text, celý mesiac text, štvrťrok atď. Tu sú kroky:

• Krok 1: Skopírujte tabuľku z tabuľky 2 a vložte ju do PowerPivotu. Túto tabuľku môžete vytvoriť v programe Excel, ale ušetrím vám čas. Ak používate program Internet Explorer, mali by ste prilepiť priamo z vybratých údajov nižšie. PowerPivot zdvihne formátovanie tabuľky v mojom testovaní. Ak používate iný prehliadač, možno budete musieť najprv prilepiť do programu Excel a skopírovať ho z programu Excel, aby ste vybrali formátovanie tabuľky.
• Krok 2: Otvorte zošit programu Excel s údajmi o objednávke v aplikácii PowerPivot for Excel. Spustite okno PowerPivot. Kliknite na možnosť Prilepiť, čím sa zobrazí vyhľadávací tabuľka skopírovaná z nižšie uvedenej tabuľky alebo z programu Excel. Zadajte názov tabuľky a uistite sa, že začiarknete možnosť "Použiť prvý riadok ako hlavičky stĺpcov". Kliknite na tlačidlo OK. Vytvorili ste vyhľadávaciu tabuľku dátumov v aplikácii PowerPivot.

• Krok 3 : Vytvorte vzťah medzi políčko Mesiac v tabuľke Objednávky a pole MonthNumber vo vyhľadávacej tabuľke. Kliknite na návrhovú stužku a vyberte Vytvoriť vzťah. Vyberte voľby v dialógovom okne Vytvoriť vzťah a kliknite na položku Vytvoriť.

Opätovne s pridaním dimenzie dátumu budete môcť zoskupiť údaje do kontingenčnej tabuľky pomocou ľubovoľnej z rôznych hodnôt z tabuľky vyhľadávania dátumu. Zoskupenie podľa štvrťroka alebo mena mesiaca bude okamžité.

Tabuľky rozmerov (vyhľadávanie)

stôl 1

Tabuľka 2