สร้างตัวแบบข้อมูลที่มีประสิทธิภาพในด้านหน่วยความจําโดยใช้ Excel และ Add-in Power Pivot

ใน Excel คุณสามารถสร้างตัวแบบข้อมูลที่มีนับล้านแถว แล้วดําเนินการวิเคราะห์ข้อมูลอย่างมีประสิทธิภาพกับตัวแบบเหล่านี้ สามารถสร้างตัวแบบข้อมูลโดยใช้หรือไม่มี Add-in ของ Power Pivot เพื่อสนับสนุน PivotTable, แผนภูมิ และการแสดงภาพ Power View จํานวนเท่าใดก็ได้ในเวิร์กบุ๊กเดียวกัน

แม้ว่าคุณจะสามารถสร้างโมเดลข้อมูลขนาดใหญ่ใน Excel ได้อย่างง่ายดาย แต่ก็มีหลายเหตุผลที่จะไม่ทําเช่นนั้น ประการแรก โมเดลขนาดใหญ่ที่มีตารางและคอลัมน์จํานวนมากนั้นมากเกินไปสําหรับการวิเคราะห์ส่วนใหญ่ และทําให้รายการเขตข้อมูลยุ่งยาก ประการที่สอง โมเดลขนาดใหญ่ใช้หน่วยความจําอันมีค่า ซึ่งส่งผลลบต่อแอปพลิเคชันและรายงานอื่นๆ ที่ใช้ทรัพยากรระบบเดียวกัน สุดท้าย ใน Microsoft 365 ทั้ง SharePoint Online และ Excel Online จะจํากัดขนาดของไฟล์ Excel ไว้ที่ 10 MB สําหรับตัวแบบข้อมูลเวิร์กบุ๊กที่มีนับล้านแถว คุณจะมีขีดจํากัด 10 MB อย่างรวดเร็ว ดูข้อมูลจําเพาะและขีดจํากัดของตัวแบบข้อมูล

ในบทความนี้ คุณจะได้เรียนรู้วิธีการสร้างโมเดลที่สร้างขึ้นอย่างแน่นหนา ซึ่งง่ายต่อการทํางานและใช้หน่วยความจําน้อยลง การใช้เวลาเพื่อเรียนรู้แนวทางปฏิบัติในการออกแบบโมเดลที่มีประสิทธิภาพจะให้ผลตอบแทนในอนาคตสําหรับโมเดลใดก็ตามที่คุณสร้างและใช้ ไม่ว่าคุณจะดูใน Excel, Microsoft 365 SharePoint Online, บนเซิร์ฟเวอร์ Office Web Apps หรือใน SharePoint

ให้พิจารณาใช้ Workbook Size Optimizer ด้วยเช่นกัน โดยจะวิเคราะห์เวิร์กบุ๊ก Excel ของคุณและถ้าเป็นไปได้จะบีบอัดไฟล์ให้คุณด้วย ดาวน์โหลด Workbook Size Optimizer

ในบทความนี้

• อัตราส่วนการบีบอัดและเครื่องมือการวิเคราะห์ในหน่วยความจํา

• ไม่มีอะไรจะดีไปกว่าคอลัมน์ที่ไม่มีอยู่สําหรับการใช้หน่วยความจําเหลือน้อย

  • สองตัวอย่างของคอลัมน์ที่ควรแยกออกเสมอ
  • วิธีการแยกคอลัมน์ที่ไม่จําเป็นออก

• แล้วการกรองเฉพาะแถวที่จําเป็นล่ะ

• จะเกิดอะไรขึ้นถ้าเราต้องการคอลัมน์ เรายังสามารถลดต้นทุนพื้นที่ได้หรือไม่?

  • การปรับเปลี่ยนคอลัมน์และเวลาวันที่
  • การปรับเปลี่ยนคิวรีของ SQL

• การใช้การวัดจากการคํานวณของ DAX แทนการใช้คอลัมน์

  • 2 คอลัมน์ใดที่คุณควรเก็บ

• บทสรุป

• ลิงก์ที่เกี่ยวข้อง

อัตราส่วนการบีบอัดและเครื่องมือการวิเคราะห์ในหน่วยความจํา

ตัวแบบข้อมูลใน Excel ใช้เครื่องมือการวิเคราะห์ในหน่วยความจําเพื่อจัดเก็บข้อมูลในหน่วยความจํา เครื่องยนต์ใช้เทคนิคการบีบอัดที่ทรงพลังเพื่อลดความต้องการในการจัดเก็บ โดยย่อชุดผลลัพธ์จนเหลือเศษเสี้ยวของขนาดเดิม

โดยเฉลี่ยแล้ว คุณสามารถคาดหวังว่าตัวแบบข้อมูลจะเล็กกว่าข้อมูลเดียวกัน ณ จุดต้นกําเนิด 7 ถึง 10 เท่า ตัวอย่างเช่น ถ้าคุณกําลังนําเข้าข้อมูล 7 MB จากฐานข้อมูล SQL Server ตัวแบบข้อมูลใน Excel อาจมีขนาด 1 MB หรือน้อยกว่าได้อย่างง่ายดาย ระดับของการบีบอัดที่เกิดขึ้นจริงนั้นขึ้นอยู่กับจํานวนของค่าที่ไม่ซ้ํากันในแต่ละคอลัมน์เป็นหลัก ยิ่งค่าที่ไม่ซ้ํากันมีค่าเท่าใด ก็ยิ่งต้องใช้หน่วยความจํามากขึ้นในการจัดเก็บค่าเหล่านั้น

ทําไมเราถึงพูดถึงการบีบอัดและค่าที่ไม่ซ้ํากัน? เนื่องจากการสร้างแบบจําลองที่มีประสิทธิภาพที่ลดการใช้หน่วยความจําให้เหลือน้อยที่สุดนั้นเกี่ยวกับการขยายการบีบอัดให้ใหญ่สุด และวิธีที่ง่ายที่สุดในการดําเนินการดังกล่าวคือการกําจัดคอลัมน์ใดๆ ที่คุณไม่ต้องการจริงๆ โดยเฉพาะอย่างยิ่งถ้าคอลัมน์เหล่านั้นมีค่าที่ไม่ซ้ํากันจํานวนมาก

ไม่มีอะไรจะดีไปกว่าคอลัมน์ที่ไม่มีอยู่สําหรับการใช้หน่วยความจําเหลือน้อย

คอลัมน์ที่มีประสิทธิภาพในด้านหน่วยความจํามากที่สุดคือคอลัมน์ที่คุณไม่เคยนําเข้าตั้งแต่แรก ถ้าคุณต้องการสร้างแบบจําลองที่มีประสิทธิภาพ ให้ดูที่แต่ละคอลัมน์และถามตัวเองว่าคอลัมน์ดังกล่าวมีส่วนช่วยในการวิเคราะห์ที่คุณต้องการดําเนินการหรือไม่ หากไม่แสดงหรือคุณไม่แน่ใจ ให้ปล่อยไว้ คุณสามารถเพิ่มคอลัมน์ใหม่ในภายหลังได้เสมอถ้าคุณต้องการ

สองตัวอย่างของคอลัมน์ที่ควรแยกออกเสมอ

ตัวอย่างแรกเกี่ยวข้องกับข้อมูลที่มาจากคลังข้อมูล ในคลังข้อมูล เป็นเรื่องปกติที่จะพบอาร์ทิแฟกต์ของกระบวนการ ETL ที่โหลดและรีเฟรชข้อมูลในคลังสินค้า คอลัมน์เช่น "วันที่สร้าง" "วันที่อัปเดต" และ "ETL run" จะถูกสร้างขึ้นเมื่อโหลดข้อมูล ไม่มีคอลัมน์เหล่านี้ที่จําเป็นในตัวแบบข้อมูล และควรยกเลิกการเลือกเมื่อคุณนําเข้าข้อมูล

ตัวอย่างที่สองเกี่ยวกับการละเว้นคอลัมน์คีย์หลักเมื่อนําเข้าตารางข้อเท็จจริง

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

ตารางข้อเท็จจริงนั้นแตกต่างกัน ในตารางข้อเท็จจริง คีย์หลักจะถูกใช้เพื่อระบุแต่ละแถวโดยไม่ซ้ํากัน แม้ว่าจะจําเป็นสําหรับวัตถุประสงค์ในการทํา Normalization แต่ก็มีประโยชน์น้อยกว่าในตัวแบบข้อมูลที่คุณต้องการใช้เฉพาะคอลัมน์เหล่านั้นเพื่อการวิเคราะห์หรือสร้างความสัมพันธ์ของตาราง ด้วยเหตุนี้ เมื่อนําเข้าจากตารางข้อเท็จจริง คุณจึงไม่ต้องใส่คีย์หลัก คีย์หลักในตารางข้อเท็จจริงใช้พื้นที่จํานวนมากในตัวแบบ แต่ก็ไม่ได้ให้ประโยชน์ใดๆ เนื่องจากไม่สามารถใช้เพื่อสร้างความสัมพันธ์ได้

วิธีการแยกคอลัมน์ที่ไม่จําเป็นออก

ตัวแบบที่มีประสิทธิภาพจะมีเฉพาะคอลัมน์ที่คุณต้องการจริงๆ ในเวิร์กบุ๊กของคุณเท่านั้น ถ้าคุณต้องการควบคุมคอลัมน์ที่รวมอยู่ในตัวแบบ คุณจะต้อง ใช้ตัวช่วยสร้างการนําเข้าตารางใน Add-in Power Pivot เพื่อนําเข้าข้อมูล แทนที่จะใช้กล่องโต้ตอบ "นําเข้าข้อมูล" ใน Excel

เมื่อคุณเริ่มตัวช่วยสร้างการนําเข้าตาราง ให้คุณเลือกตารางที่จะนําเข้า

สําหรับตารางแต่ละตาราง คุณสามารถคลิกปุ่ม แสดงตัวอย่าง & ตัวกรอง และเลือกส่วนของตารางที่คุณต้องการจริงๆ เราขอแนะนําให้คุณยกเลิกการเลือกคอลัมน์ทั้งหมดก่อน จากนั้นดําเนินการตรวจสอบคอลัมน์ที่คุณต้องการ หลังจากพิจารณาแล้วว่าจําเป็นต้องใช้สําหรับการวิเคราะห์หรือไม่

แล้วการกรองเฉพาะแถวที่จําเป็นล่ะ

ตารางจํานวนมากในฐานข้อมูลขององค์กรและคลังข้อมูลมีข้อมูลในอดีตที่สะสมมาเป็นเวลานาน นอกจากนี้ คุณอาจพบว่าตารางที่คุณสนใจมีข้อมูลสําหรับพื้นที่ของธุรกิจที่ไม่ต้องการสําหรับการวิเคราะห์เฉพาะของคุณ

ด้วยการใช้ตัวช่วยสร้างการนําเข้าตาราง คุณสามารถกรองข้อมูลในอดีตหรือไม่เกี่ยวข้องออก และช่วยประหยัดเนื้อที่ในตัวแบบได้มาก ในรูปต่อไปนี้ ตัวกรองวันที่จะใช้เพื่อเรียกใช้เฉพาะแถวที่มีข้อมูลสําหรับปีปัจจุบัน โดยไม่รวมข้อมูลในอดีตที่ไม่จําเป็นต้องใช้

จะเกิดอะไรขึ้นถ้าเราต้องการคอลัมน์ เรายังสามารถลดต้นทุนพื้นที่ได้หรือไม่?

มีเทคนิคเพิ่มเติมอีกสองสามอย่างที่คุณสามารถนําไปใช้เพื่อทําให้คอลัมน์เป็นตัวเลือกที่ดีสําหรับการบีบอัด โปรดจําไว้ว่าคุณลักษณะเดียวของคอลัมน์ที่มีผลต่อการบีบอัดคือจํานวนของค่าที่ไม่ซ้ํากัน ในส่วนนี้ คุณจะได้เรียนรู้วิธีปรับเปลี่ยนบางคอลัมน์เพื่อลดจํานวนของค่าที่ไม่ซ้ํากัน

การปรับเปลี่ยนคอลัมน์และเวลาวันที่

ในหลายๆ กรณี คอลัมน์วันที่และเวลาใช้พื้นที่จํานวนมาก โชคดีที่มีหลายวิธีที่จะลดข้อกําหนดที่เก็บข้อมูลสําหรับชนิดข้อมูลนี้ เทคนิคต่างๆ จะแตกต่างกันไปโดยขึ้นอยู่กับวิธีที่คุณใช้คอลัมน์ และระดับความสบายใจของคุณในการสร้างคิวรี SQL

คอลัมน์วันที่และเวลาจะมีส่วนวันที่และเวลา เมื่อคุณถามตัวเองว่าคุณต้องการคอลัมน์หรือไม่ ให้ถามคําถามเดียวกันหลายๆ ครั้งสําหรับคอลัมน์วันที่และเวลา

• ฉันต้องการส่วนเวลาหรือไม่?
• ฉันต้องการส่วนเวลาในระดับชั่วโมงหรือไม่? นาที , วินาที? มิลลิวินาทีใช่หรือไม่
• ฉันมีคอลัมน์วันที่และเวลาหลายคอลัมน์เพราะฉันต้องการคํานวณความแตกต่างระหว่างคอลัมน์เหล่านั้น หรือเพียงเพื่อรวมข้อมูลตามปี เดือน ไตรมาส และอื่นๆ

วิธีตอบคําถามแต่ละข้อเหล่านี้จะกําหนดตัวเลือกของคุณสําหรับการจัดการกับคอลัมน์เวลาวันที่

โซลูชันทั้งหมดนี้จําเป็นต้องมีการปรับเปลี่ยนคิวรี SQL เมื่อต้องการทําให้การปรับเปลี่ยนคิวรีง่ายขึ้น คุณควรกรองออกอย่างน้อยหนึ่งคอลัมน์ในทุกตาราง ด้วยการกรองคอลัมน์ออก คุณจะเปลี่ยนการสร้างคิวรีจากรูปแบบย่อ (SELECT *) เป็นคําสั่ง SELECT ที่มีชื่อคอลัมน์แบบเต็ม ซึ่งสามารถปรับเปลี่ยนได้ง่ายกว่ามาก

ลองมาดูคิวรีที่สร้างขึ้นสําหรับคุณ จากกล่องโต้ตอบคุณสมบัติตาราง คุณสามารถสลับไปยังตัวแก้ไขคิวรีและดูคิวรี SQL ปัจจุบันสําหรับแต่ละตาราง

จากคุณสมบัติตาราง ให้เลือกตัวแก้ไขคิวรี

ตัวแก้ไขตัวแก้ไขคิวรีแสดงคิวรี SQL ที่ใช้ในการสร้างตาราง ถ้าคุณกรองคอลัมน์ใดๆ ในระหว่างการนําเข้า คิวรีของคุณมีชื่อคอลัมน์แบบเต็ม:

ในทางกลับกัน ถ้าคุณนําเข้าตารางทั้งหมดโดยไม่ได้ยกเลิกการเลือกคอลัมน์หรือนําตัวกรองไปใช้ คุณจะเห็นคิวรีเป็น "เลือก * จาก" ซึ่งจะปรับเปลี่ยนได้ยากขึ้น:

การปรับเปลี่ยนคิวรีของ SQL

ตอนนี้คุณก็ทราบวิธีค้นหาคิวรีแล้ว คุณสามารถปรับเปลี่ยนเพื่อลดขนาดของคิวรีของคุณเพิ่มเติม

1. สําหรับคอลัมน์ที่มีข้อมูลสกุลเงินหรือทศนิยม ถ้าคุณไม่ต้องการตําแหน่งทศนิยม ให้ใช้ไวยากรณ์นี้เพื่อกําจัดตําแหน่งทศนิยม
   "SELECT ROUND([Decimal_column_name],0)... .”
   ถ้าคุณต้องการเศษส่วนของเศษของเซ็นต์ ให้แทนที่ 0 ด้วย 2 ถ้าคุณใช้จํานวนลบ คุณสามารถปัดเศษเป็นหน่วย สิบ หลักร้อย และอื่นๆ
2. ถ้าคุณมีคอลัมน์วันที่และเวลาที่ชื่อว่า dbo บิ๊กเทเบิล [Date Time] และคุณไม่จําเป็นต้องใช้ส่วน Time ให้ใช้ไวยากรณ์เพื่อกําจัดเวลา:
   "เลือกแคสต์ (DBO. บิ๊กเทเบิล [วันที่ เวลา] เป็นวันที่ AS [วันที่ เวลา ]) "
3. ถ้าคุณมีคอลัมน์วันที่และเวลาที่ชื่อว่า dbo บิ๊กเทเบิล [วันที่เวลา และคุณต้องการทั้งส่วนวันที่และเวลา ให้ใช้หลายคอลัมน์ในคิวรีของ SQL แทนคอลัมน์วันที่และเวลาคอลัมน์เดียว:
   "เลือกแคสต์ (DBO. บิ๊กเทเบิล [วันที่, เวลา] เป็นวันที่ ), AS [วันที่, เวลา],
   DatePart(hh, dbo. บิ๊กเทเบิล [วันที่ เวลา]) เป็น [วันที่ เวลา ชั่วโมง]
   DatePart(mi, dbo. บิ๊กเทเบิล [วันที่ เวลา]) เป็น [วันที่ เวลา นาที]
   DatePart(ss, dbo. บิ๊กเทเบิล [วันที่ เวลา]) เป็น [วันที่ เวลา วินาที]
   DatePart(ms, dbo. บิ๊กเทเบิล [วันที่ เวลา]) As [Date Time Milliseconds]"
   ใช้คอลัมน์ได้มากเท่าที่คุณต้องการเพื่อจัดเก็บแต่ละส่วนในคอลัมน์แยกต่างหาก
4. ถ้าคุณต้องการชั่วโมงและนาที และคุณต้องการให้ทั้งสองอย่างอยู่ด้วยกันเป็นคอลัมน์เวลาเดียว คุณสามารถใช้ไวยากรณ์ต่อไปนี้
   Timefromparts(datepart(hh, dbo. บิ๊กเทเบิล [วันที่ เวลา]), datepart(mm, dbo. บิ๊กเทเบิล [วันที่ เวลา])) เป็น [วันที่ เวลา ชั่วโมง นาที]
5. ถ้าคุณมีคอลัมน์วันที่และเวลาสองคอลัมน์ เช่น [เวลาเริ่มต้น] และ [เวลาสิ้นสุด] และสิ่งที่คุณต้องการจริงๆ คือความแตกต่างของเวลาระหว่างคอลัมน์เหล่านั้นในหน่วยวินาทีเป็นคอลัมน์ที่เรียกว่า [ระยะเวลา] ให้เอาทั้งสองคอลัมน์ออกจากรายการและเพิ่ม:
   "datediff(ss,[Start Date],[End Date]) as [Duration]"
   ถ้าคุณใช้คําสําคัญ ms แทน ss คุณจะได้ระยะเวลาในหน่วยมิลลิวินาที

การใช้การวัดจากการคํานวณของ DAX แทนการใช้คอลัมน์

ถ้าคุณเคยทํางานกับภาษานิพจน์ DAX มาก่อน คุณอาจทราบอยู่แล้วว่าคอลัมน์จากการคํานวณจะถูกใช้เพื่อรับคอลัมน์ใหม่โดยยึดตามคอลัมน์อื่นๆ ในตัวแบบ ในขณะที่การวัดจากการคํานวณจะถูกกําหนดเพียงครั้งเดียวในตัวแบบ แต่จะถูกประเมินเมื่อใช้ใน PivotTable หรือรายงานอื่นเท่านั้น

เทคนิคหนึ่งในการประหยัดหน่วยความจําคือการแทนที่คอลัมน์ปกติหรือคอลัมน์จากการคํานวณด้วยการวัดจากการคํานวณ ตัวอย่างคลาสสิกคือ ราคาต่อหน่วย ปริมาณ และผลรวม ถ้าคุณมีทั้งสามแล้ว คุณสามารถประหยัดเนื้อที่ได้โดยการเก็บแค่สองรายการไว้และคํานวณรายการที่สามโดยใช้ DAX

2 คอลัมน์ใดที่คุณควรเก็บ

ในตัวอย่างข้างต้น ให้เก็บ ปริมาณ และ หน่วยราคา ไว้ สองค่านี้มีค่าน้อยกว่าผลรวม เมื่อต้องการคํานวณผลรวม ให้เพิ่มหน่วยวัดจากการคํานวณ เช่น:

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

คอลัมน์จากการคํานวณจะเหมือนกับคอลัมน์ปกติตรงที่ทั้งสองอย่างจะกินพื้นที่ในตัวแบบ ในทางตรงข้าม การวัดจากการคํานวณจะคํานวณพร้อมกันและไม่ใช้พื้นที่

บทสรุป

ในบทความนี้ เราได้พูดคุยเกี่ยวกับแนวทางต่างๆ ที่สามารถช่วยคุณสร้างโมเดลที่มีประสิทธิภาพในด้านหน่วยความจํามากขึ้น วิธีที่จะลดขนาดไฟล์และความต้องการของหน่วยความจําของตัวแบบข้อมูลคือ การลดจํานวนโดยรวมของคอลัมน์และแถว และจํานวนของค่าที่ไม่ซ้ํากันที่ปรากฏในแต่ละคอลัมน์ ต่อไปนี้คือเทคนิคบางอย่างที่เรากล่าวถึง:

• แน่นอนว่าการเอาคอลัมน์ออกเป็นวิธีที่ดีที่สุดในการประหยัดพื้นที่ ตัดสินใจว่าคุณต้องการคอลัมน์ใดจริงๆ
• ในบางครั้ง คุณสามารถเอาคอลัมน์ออกและแทนที่ด้วยการวัดจากการคํานวณในตารางได้
• คุณอาจไม่จําเป็นต้องใส่แถวทั้งหมดในตาราง คุณสามารถกรองแถวออกในตัวช่วยสร้างการนําเข้าตารางได้
• โดยทั่วไปแล้ว การแบ่งคอลัมน์เดียวออกเป็นส่วนต่างๆ เป็นหลายส่วนที่แตกต่างกันเป็นวิธีที่ดีในการลดจํานวนค่าที่ไม่ซ้ํากันในคอลัมน์ แต่ละส่วนจะมีค่าที่ไม่ซ้ํากันจํานวนน้อย และผลรวมจะน้อยกว่าคอลัมน์แบบรวมเดิม
• ในหลายกรณี คุณยังต้องการส่วนที่แตกต่างกันเพื่อใช้เป็นตัวแบ่งส่วนข้อมูลในรายงานของคุณ เมื่อเหมาะสม คุณสามารถสร้างลําดับชั้นจากส่วนต่างๆ เช่น ชั่วโมง นาที และวินาที
• หลายครั้งที่คอลัมน์มีข้อมูลมากกว่าที่คุณต้องการ ตัวอย่างเช่น สมมติว่าคอลัมน์มีตําแหน่งทศนิยม แต่คุณได้นําการจัดรูปแบบไปซ่อนตําแหน่งทศนิยมทั้งหมดไปใช้ การปัดเศษมีประสิทธิภาพมากในการลดขนาดของคอลัมน์ตัวเลข

ตอนนี้คุณได้ทําสิ่งต่างๆ เพื่อลดขนาดของเวิร์กบุ๊กของคุณแล้ว ให้พิจารณาใช้ Workbook Size Optimizer โดยจะวิเคราะห์เวิร์กบุ๊ก Excel ของคุณและถ้าเป็นไปได้จะบีบอัดไฟล์ให้คุณด้วย ดาวน์โหลด Workbook Size Optimizer

ลิงก์ที่เกี่ยวข้อง

ข้อกำหนดและขีดจำกัดของตัวแบบข้อมูล

Workbook Size Optimizer

PowerPivot: การวิเคราะห์ข้อมูลและการสร้างตัวแบบข้อมูลใน Excel อย่างมีประสิทธิภาพ