PowerPivot for Excel - ตารางค้นหาใน Data Warehouse

หนึ่งในสิ่งที่ฉันแสดงมากที่สุดเกี่ยวกับ PowerPivot for Excel คือความสามารถในการเพิ่มตารางการค้นหาลงในชุดข้อมูลของคุณ ข้อมูลส่วนใหญ่ที่คุณใช้งานไม่ได้มีทุกสาขาที่คุณต้องการสำหรับการวิเคราะห์ของคุณ ตัวอย่างเช่นคุณอาจมีฟิลด์วันที่ แต่ต้องจัดกลุ่มข้อมูลตามไตรมาส คุณสามารถเขียนสูตรได้ แต่ง่ายต่อการสร้างตารางค้นหาแบบธรรมดาภายในสภาพแวดล้อมของ PowerPivot

นอกจากนี้คุณยังสามารถใช้ตารางค้นหานี้สำหรับการจัดกลุ่มอื่นเช่นชื่อเดือนและครึ่งปีแรก / ครึ่งหลังของปี ในข้อกำหนดในการเก็บข้อมูลคุณเป็นจริงในการสร้างตารางมิติข้อมูลวันที่ ในบทความนี้ฉันจะให้ตารางข้อมูลขนาดตัวอย่างสองชุดเพื่อเพิ่มประสิทธิภาพโครงการ PowerPivot for Excel ของคุณ

ตารางขนาดตัวอักษร (ค้นหาใหม่)

ลองดูตารางที่มีข้อมูลคำสั่งซื้อ (ข้อมูล Contoso จาก Microsoft รวมถึงชุดข้อมูลชุดนี้) สมมติว่าตารางมีฟิลด์สำหรับลูกค้าวันที่สั่งคำสั่งรวมและประเภทใบสั่ง เราจะเน้นที่ฟิลด์ประเภทคำสั่งซื้อ สมมติว่าฟิลด์ประเภทคำสั่งมีค่าเช่น:

• เน็ตบุ๊ค
• เดสก์ท็
• จอภาพ
• โปรเจ็คเตอร์
• เครื่องพิมพ์
• สแกนเนอร์
• กล้องดิจิทัล
• กล้องดิจิตอล SLR
• กล้องฟิล์ม
• กล้องวิดีโอ
• โทรศัพท์สำนักงาน
• โทรศัพท์สมาร์ท
• พีดีเอ
• โทรศัพท์มือถือ

ในความเป็นจริงคุณจะมีรหัสสำหรับเหล่านี้ แต่เพื่อให้ตัวอย่างนี้ง่ายสมมติว่านี่เป็นค่าที่แท้จริงในตารางใบสั่ง

การใช้ PowerPivot for Excel ทำให้คุณสามารถจัดกลุ่มคำสั่งซื้อตามประเภทคำสั่งได้อย่างง่ายดาย จะทำอย่างไรถ้าคุณต้องการการจัดกลุ่มอื่น ตัวอย่างเช่นสมมติว่าคุณต้องมี "หมวดหมู่" เช่นคอมพิวเตอร์กล้องและโทรศัพท์ ตารางสั่งซื้อไม่มีฟิลด์ "หมวดหมู่" แต่คุณสามารถสร้างเป็นตารางค้นหาใน PowerPivot for Excel ได้โดยง่าย

ตารางค้นหาตัวอย่างทั้งหมดอยู่ใน ตารางที่ 1 นี่คือขั้นตอน:

• ขั้นตอนที่ 1: คุณต้องมีรายการที่แตกต่างจากฟิลด์ประเภทสำหรับตารางค้นหาของคุณ นี่จะเป็นช่องค้นหาของคุณ จากชุดข้อมูลของคุณให้สร้างรายการค่าที่แตกต่างจากฟิลด์ประเภทคำสั่ง ป้อนรายการ "ประเภทต่างๆ" ลงในสมุดงาน Excel ป้ายกำกับคอลัมน์ Type
• ขั้นที่ 2: ในคอลัมน์ถัดจากคอลัมน์การค้นหาของคุณ (พิมพ์) ให้เพิ่มฟิลด์ใหม่ที่คุณต้องการจัดกลุ่ม ในตัวอย่างของเราให้เพิ่มคอลัมน์ที่มีป้ายชื่อว่า Category
• ขั้นที่ 3: สำหรับแต่ละค่าในรายการค่าที่แตกต่างกันของคุณ (ประเภทในตัวอย่างนี้) ให้เพิ่มค่า "Category" ที่สอดคล้องกัน ในตัวอย่างง่ายๆของเราให้ป้อนคอมพิวเตอร์เครื่องหรือโทรศัพท์ลงในหมวดหมู่
• ขั้นตอนที่ 4: คัดลอกตารางข้อมูลประเภทและประเภทลงในคลิปบอร์ดของคุณ
• ขั้นตอนที่ 5: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิกวางซึ่งจะนำมาในตารางค้นหาใหม่ของคุณ ให้ชื่อตารางและตรวจสอบว่าคุณได้เลือก "ใช้แถวแรกเป็นส่วนหัวของคอลัมน์" คลิกตกลง คุณได้สร้างตารางค้นหาใน PowerPivot แล้ว
• ขั้นตอนที่ 6: สร้างความสัมพันธ์ระหว่างฟิลด์ Type ในตาราง Order และประเภทในฟิลด์ lookup table คลิกริบบิ้น Design และเลือก Create Relationship ทำการเลือกในกล่องโต้ตอบสร้างความสัมพันธ์และคลิกสร้าง

เมื่อคุณสร้าง PivotTable ใน Excel โดยใช้ข้อมูล PowerPivot คุณจะสามารถจัดกลุ่มตามหมวดหมู่ใหม่ได้ โปรดจำไว้ว่า PowerPivot for Excel สนับสนุนเฉพาะ Inner Joins เท่านั้น หากคุณมี "ประเภทคำสั่ง" ที่หายไปจากตารางค้นหาระเบียนทั้งหมดที่ตรงกันสำหรับประเภทนั้นจะหายไปจาก PivotTable ใด ๆ ที่ยึดตามข้อมูล PowerPivot คุณจะต้องตรวจสอบเป็นครั้งคราว

ตารางวันที่ (ค้นหา)

ตารางการค้นหาวันที่มักจะต้องการในโครงการ PowerPivot for Excel ส่วนใหญ่ของคุณ ชุดข้อมูลส่วนใหญ่มีฟิลด์วันที่บางประเภท มีฟังก์ชันคำนวณปีและเดือน

อย่างไรก็ตามหากคุณต้องการข้อความเดือนที่เกิดขึ้นจริงหรือไตรมาสคุณต้องเขียนสูตรที่ซับซ้อน การใส่ตารางวันที่ (การค้นหา) เป็นเรื่องง่ายมากและตรงกับเดือนในชุดข้อมูลหลักของคุณ คุณจะต้องเพิ่มคอลัมน์ลงในตารางคำสั่งซื้อของคุณเพื่อแสดงหมายเลขเดือนจากฟิลด์วันที่สั่ง สูตร DAX สำหรับ "month" ในตัวอย่างของเราคือ "= MONTH ([Order Date]) ซึ่งจะแสดงตัวเลขระหว่าง 1 ถึง 12 สำหรับแต่ละระเบียนตารางมิติข้อมูลของเราจะระบุค่าอื่นซึ่งเชื่อมโยงไปยังหมายเลขเดือน จะช่วยให้คุณมีความยืดหยุ่นในการวิเคราะห์ของคุณตารางมิติข้อมูลวันตัวอย่างแบบสมบูรณ์อยู่ด้านล่างใน ตารางที่ 2

มิติข้อมูลวันที่หรือตารางค้นหาจะประกอบด้วยระเบียน 12 รายการ คอลัมน์เดือนจะมีค่าเป็น 1 - 12 คอลัมน์อื่น ๆ จะรวมถึงข้อความเดือนที่ย่อข้อความเต็มเดือนไตรมาส ฯลฯ ต่อไปนี้เป็นขั้นตอน:

• ขั้นที่ 1: คัดลอกตารางจากตารางที่ 2 ด้านล่างและวางลงใน PowerPivot คุณสามารถสร้างตารางนี้ใน Excel แต่ฉันช่วยคุณประหยัดเวลา คุณควรจะสามารถวางได้โดยตรงจากข้อมูลที่เลือกด้านล่างหากคุณใช้ Internet Explorer PowerPivot ดึงข้อมูลการจัดรูปแบบตารางในการทดสอบของฉัน หากคุณกำลังใช้เบราเซอร์อื่นคุณอาจต้องวางลงใน Excel ก่อนและคัดลอกจาก Excel เพื่อรับรูปแบบตาราง
• ขั้นตอนที่ 2: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิกวางซึ่งจะนำตารางการค้นหาของคุณที่คัดลอกมาจากตารางด้านล่างหรือจาก Excel ให้ชื่อตารางและตรวจสอบว่าคุณได้เลือก "ใช้แถวแรกเป็นส่วนหัวของคอลัมน์" คลิกตกลง คุณได้สร้างตารางค้นหาวันที่ใน PowerPivot แล้ว

• ขั้นที่ 3 : สร้างความสัมพันธ์ระหว่างเขตข้อมูลเดือนในตารางใบสั่งและฟิลด์ MonthNumber ในตารางค้นหา คลิกริบบิ้น Design และเลือก Create Relationship ทำการเลือกในกล่องโต้ตอบสร้างความสัมพันธ์และคลิกสร้าง

อีกครั้งด้วยการเพิ่มมิติข้อมูลวันที่คุณจะสามารถจัดกลุ่มข้อมูลในตารางสาระสำคัญของคุณโดยใช้ค่าต่างๆจากตารางค้นหาวันที่ การจัดกลุ่มตามไตรมาสหรือชื่อของเดือนจะคลี่คลาย

ตารางมิติข้อมูลตัวอย่าง (ค้นหา)

ตารางที่ 1

ตารางที่ 2