PowerPivot para sa Excel - Lookup Table sa Data Warehouse

Isa sa mga bagay na pinaka-listahan ko tungkol sa PowerPivot para sa Excel ay ang kakayahang magdagdag ng mga lookup table sa iyong mga set ng data. Karamihan ng panahon, ang datos na iyong pinagtatrabahuhan ay walang mga patlang na kailangan mo para sa iyong pag-aaral. Halimbawa, maaari kang magkaroon ng isang patlang ng petsa ngunit kailangan mong i-grupo ang iyong data sa quarter. Maaari kang magsulat ng isang pormula, ngunit mas madaling lumikha ng isang simpleng lookup table sa loob ng PowerPivot na kapaligiran.

Maaari mo ring gamitin ang table lookup na ito para sa isa pang grupo tulad ng pangalan ng buwan at una / ikalawang kalahati ng taon. Sa mga tuntunin ng warehousing ng data, talagang gumagawa ka ng table ng dimensyon ng petsa. Sa artikulong ito, ibibigay ko sa iyo ang isang pares ng mga halimbawa ng mga talahanayan ng dimensyon upang mapahusay ang iyong PowerPivot para sa Excel na proyekto.

Bagong Teksto Sukat (Lookup) Table

Isaalang-alang natin ang isang talahanayan na may data ng order (ang data ng Contoso mula sa Microsoft ay nagsasama ng isang datos ng datos na nakalagay dito). Ipagpalagay na ang talahanayan ay may mga patlang para sa customer, petsa ng order, kabuuang order, at uri ng order. Kami ay mag-focus sa patlang ng uri ng order. Ipagpalagay na ang patlang ng uri ng order ay may mga halaga tulad ng:

• Netbooks
• Mga desktop
• Mga sinusubaybayan
• Projectors
• Mga Printer
• Mga Scanner
• Digital Camera
• Digital SLR Cameras
• Film Camera
• Mga Camcorder
• Mga Opisina ng Telepono
• Mga Smart phone
• PDAs
• Mga Kagamitan sa Cellphone

Sa katotohanan, magkakaroon ka ng mga code para sa mga ito ngunit upang panatilihing simple ang halimbawang ito, ipalagay na ang mga ito ay ang mga aktwal na halaga sa talahanayan ng order.

Paggamit ng PowerPivot para sa Excel, madali mong ma-grupo ang iyong mga order sa pamamagitan ng uri ng order. Paano kung gusto mo ng ibang pagpapangkat? Halimbawa, ipagpalagay na kailangan mo ng isang grupo ng "kategorya" tulad ng mga computer, camera, at telepono. Ang talahanayan ng pagkakasunod-sunod ay walang field na "kategorya," ngunit maaari mo itong madaling gawing isang lookup table sa PowerPivot para sa Excel.

Ang kumpletong sample lookup table ay nasa ibaba sa Table 1 . Narito ang mga hakbang:

• Hakbang 1: Kailangan mo ng isang natatanging listahan mula sa field ng uri para sa iyong lookup table. Ito ang iyong magiging lookup field. Mula sa iyong hanay ng data, lumikha ng isang natatanging listahan ng mga halaga mula sa field ng uri ng order. Ipasok ang hiwalay na listahan ng "mga uri" sa isang workbook sa Excel. Lagyan ng label ang Uri ng haligi.
• Hakbang 2: Sa haligi sa tabi ng iyong hanay ng lookup (Uri), idagdag ang bagong field na nais mong pangkatin. Sa aming halimbawa, magdagdag ng haligi na may label na tinatawag na Kategorya.
• Hakbang 3: Para sa bawat halaga sa iyong natatanging listahan ng mga halaga (mga uri sa halimbawang ito), idagdag ang mga katumbas na "Mga kategorya". Sa aming simpleng halimbawa, ipasok ang alinman sa Mga Computer, Camera o Phones sa haligi ng Kategorya.
• Hakbang 4: Kopyahin ang talahanayan ng Data ng Uri at Kategorya sa iyong clipboard.
• Hakbang 5: Buksan ang workbook ng Excel gamit ang data ng order sa PowerPivot para sa Excel. Ilunsad ang PowerPivot Window. I-click ang I-paste na dadalhin sa iyong bagong lookup table. Bigyan ang pangalan ng talahanayan at tiyakin mong suriin ang "Gamitin ang unang hilera bilang mga header ng hanay." I-click ang OK. Gumawa ka ng lookup table sa PowerPivot.
• Hakbang 6: Gumawa ng isang relasyon sa pagitan ng patlang ng Uri sa talahanayan ng Order at ang patlang ng Kategorya sa lookup table. Mag-click sa Disenyo ng laso at piliin ang Gumawa ng Relasyon. Gawin ang mga seleksyon sa dialog na Lumikha ng Relasyon at i-click ang Lumikha.

Kapag lumikha ka ng isang PivotTable sa Excel batay sa data ng PowerPivot, magagawa mong mag-grupo ayon sa iyong bagong field ng Kategorya. Tandaan na sinusuportahan lamang ng PowerPivot para sa Excel ang Inner na Pagsali. Kung mayroon kang "uri ng order" na nawawala mula sa iyong lookup table, ang lahat ng mga kaukulang talaan para sa ganitong uri ay nawawala mula sa anumang PivotTable batay sa data ng PowerPivot. Kailangan mong suriin ito mula sa oras-oras.

Petsa ng Dimensyon (Lookup) Table

Ang Table ng lookup ng petsa ay malamang na kinakailangan sa karamihan ng iyong PowerPivot para sa mga proyekto ng Excel. Ang karamihan sa mga hanay ng data ay may ilang uri ng (mga) patlang ng petsa. Mayroong mga function upang kalkulahin ang taon at buwan.

Gayunpaman, kung kailangan mo ang aktwal na teksto ng buwan o ang quarter, kailangan mong magsulat ng komplikadong formula. Ito ay mas madali upang isama ang isang talahanayan ng Dimensyon ng Petsa (lookup) at itugma ito sa numero ng buwan sa iyong pangunahing hanay ng data. Kakailanganin mong magdagdag ng haligi sa iyong talahanayan ng pagkakasunud-sunod upang kumatawan sa numero ng buwan mula sa patlang ng petsa ng order. Ang formula ng DAX para sa "buwan" sa aming halimbawa ay "= MONTH ([Petsa ng Order]). Ito ay babalik sa isang numero sa pagitan ng 1 at 12 para sa bawat rekord.Ang talahanayan ng dimensyon ay magbibigay ng kahaliling mga halaga, na may kaugnayan sa numero ng buwan. magbibigay sa iyo ng kakayahang umangkop sa iyong pag-aaral. Ang talahanayan ng sukat ng sukat ng petsa ng sample ay nasa ibaba sa Table 2 .

Kasama sa dimensyon ng petsa o lookup table ang 12 mga talaan. Ang haligi ng buwan ay magkakaroon ng mga halagang 1 - 12. Ang iba pang mga haligi ay isama ang dinaglat na teksto ng buwan, buong buwan na teksto, quarter, at iba pa. Narito ang mga hakbang:

• Hakbang 1: Kopyahin ang talahanayan mula sa Table 2 sa ibaba at idikit sa PowerPivot. Maaari kang lumikha ng mesa na ito sa Excel ngunit ako ay nagse-save ka ng oras. Dapat kang makakapag-paste nang direkta mula sa napiling data sa ibaba kung gumagamit ka ng Internet Explorer. Kinukuha ng PowerPivot ang format ng talahanayan sa aking pagsubok. Kung gumagamit ka ng ibang browser, maaaring kailangan mong i-paste sa Excel muna at kopyahin ito mula sa Excel upang kunin ang pag-format ng talahanayan.
• Hakbang 2: Buksan ang workbook ng Excel gamit ang data ng order sa PowerPivot para sa Excel. Ilunsad ang PowerPivot Window. I-click ang I-paste na dadalhin sa iyong lookup table na kinopya mula sa talahanayan sa ibaba o mula sa Excel. Bigyan ang pangalan ng talahanayan at tiyakin mong suriin ang "Gamitin ang unang hilera bilang mga header ng hanay." I-click ang OK. Gumawa ka ng table lookup ng petsa sa PowerPivot.

• Hakbang 3 : Lumikha ng isang relasyon sa pagitan ng patlang ng Buwan sa talahanayan ng Order at ang patlang na MonthNumber sa lookup table. Mag-click sa Disenyo ng laso at piliin ang Gumawa ng Relasyon. Gawin ang mga seleksyon sa dialog na Lumikha ng Relasyon at i-click ang Lumikha.

Muli, sa pagdaragdag ng isang sukat ng petsa, makakapag-grupo ka ng data sa iyong PivotTable gamit ang alinman sa iba't ibang mga halaga mula sa table lookup ng petsa. Ang pagpapangkat ng quarter o ang pangalan ng buwan ay magiging isang snap.

Sample na Dimensyon (Lookup) Tables

Talahanayan 1

Talahanayan 2