ใน Excel 2013 หรือใหม่กว่า คุณสามารถสร้างตัวแบบข้อมูลที่มีหลายล้านแถว แล้ววิเคราะห์ข้อมูลอย่างมีประสิทธิภาพกับโมเดลเหล่านี้ ตัวแบบข้อมูลสามารถสร้างขึ้นโดยใช้หรือไม่ Power Pivot Add-in ของ Add-in เพื่อสนับสนุน PivotTable, แผนภูมิ และการแสดงผลPower Viewภาพในเวิร์กบุ๊กเดียวกัน
หมายเหตุ: บทความนี้จะอธิบายเกี่ยวกับตัวแบบข้อมูลใน Excel 2013 อย่างไรก็ตาม ฟีเจอร์การสร้างตัวแบบข้อมูลและ Power Pivot เดียวกันที่เริ่มใช้ใน Excel 2013 จะExcel 2016ด้วย มีความแตกต่างเล็กน้อยอย่างมีประสิทธิภาพระหว่างเวอร์ชันExcelเหล่านี้
แม้ว่าคุณจะสามารถสร้างตัวแบบข้อมูลขนาดใหญ่Excelได้อย่างง่ายดาย แต่มีหลายเหตุผลที่ไม่ควรสร้าง ก่อนอื่น โมเดลขนาดใหญ่ที่มีหลายพื้นที่ของตารางและคอลัมน์จะเต็มไปด้วยการวิเคราะห์มากที่สุด และรายการเขตข้อมูลที่ยุ่งยาก ขั้นตอนที่สอง โมเดลขนาดใหญ่ใช้หน่วยความจําที่มีค่ามากขึ้น ส่งผลกระทบต่อแอปพลิเคชันและรายงานอื่นๆ ที่แชร์ทรัพยากรระบบเดียวกัน สุดท้าย ใน Microsoft 365 ทั้ง SharePoint Online และ Excel Web App จะจํากัดขนาดไฟล์ Excel เป็น 10 MB For workbook data models that contain millions of rows, you'll run into the 10 MB limit pretty quickly. ดู ข้อจํากัดและข้อจํากัดของตัวแบบข้อมูล
ในบทความนี้ คุณจะได้เรียนรู้วิธีการสร้างโมเดลที่สร้างอย่างแน่นหนาที่ง่ายต่อการใช้งานและใช้หน่วยความจําน้อยลง ใช้เวลาเรียนรู้หลักปฏิบัติที่ดีที่สุดในการออกแบบโมเดลอย่างมีประสิทธิภาพจะหยุดอยู่ตลอดถนนเพื่อใช้งานโมเดลใดๆ ที่คุณสร้างและใช้งาน ไม่ว่าคุณจะดูใน Excel 2013, Microsoft 365 SharePoint Online, บนเซิร์ฟเวอร์ Office Online หรือใน SharePoint 2013
ให้พิจารณาใช้ ตัวปรับขนาดเวิร์กบุ๊กให้เหมาะสม ด้วย It analyzes your Excel workbook and if possible, compresses it further. ดาวน์โหลดWorkbook Size Optimizer
ในบทความนี้
อัตราส่วนการบีบอัดและกลไกการวิเคราะห์ในหน่วยความจํา
ตัวแบบข้อมูลใน Excelเครื่องมือการวิเคราะห์ในหน่วยความจําเพื่อจัดเก็บข้อมูลในหน่วยความจํา เครื่องมือจะใช้เทคนิคการบีบอัดที่มีประสิทธิภาพเพื่อลดความต้องการในการเก็บข้อมูล การลดขนาดชุดผลลัพธ์จนกว่าจะมีขนาดเดิม
โดยเฉลี่ย คุณสามารถคาดว่าตัวแบบข้อมูลจะมีขนาดเล็กกว่าข้อมูลเดียวกัน 7 ถึง 10 เท่าณ จุดเริ่มของตัวแบบข้อมูล ตัวอย่างเช่น ถ้าคุณนําเข้าข้อมูลขนาด 7 MB จากฐานข้อมูล SQL Server ตัวแบบข้อมูลใน Excel สามารถมีได้ 1 MB หรือน้อยกว่านั้น โดยทั่วไปแล้ว ระดับของการบีบอัดจะบรรลุผลแล้วโดยขึ้นอยู่กับจํานวนค่าที่ไม่เหมือนใครในแต่ละคอลัมน์ ยิ่งค่าไม่จําเป็นต้องระบุหน่วยความจํามากขึ้นก็ยิ่งจําเป็นต้องจัดเก็บหน่วยความจํามากขึ้นเท่านั้น
เหตุใดเราจึงพูดถึงการบีบอัดและค่าที่ไม่เหมือนใคร เนื่องจากการสร้างตัวแบบที่มีประสิทธิภาพที่ย่อการใช้หน่วยความจําให้น้อยที่สุดนั้นเป็นเรื่องเกี่ยวกับการเพิ่มการบีบอัดให้มากที่สุด และวิธีที่ง่ายที่สุดในการพยายามลบคอลัมน์ใดๆ ที่คุณไม่ต้องการจริงๆ ออก โดยเฉพาะถ้าคอลัมน์เหล่านั้นมีค่าไม่จําเป็นจํานวนมาก
หมายเหตุ: ข้อแตกต่างในความต้องการที่เก็บข้อมูลของแต่ละคอลัมน์อาจมีขนาดใหญ่ ในบางกรณี การมีคอลัมน์จํานวนมากที่มีจํานวนค่าไม่แน่นอนต่ากว่าคอลัมน์หนึ่งคอลัมน์จะมีจํานวนค่าเฉพาะสูง ส่วนการปรับ Datetime ให้เหมาะสมจะครอบคลุมเทคนิคนี้อย่างละเอียด
ไม่มีอะไรเอาชนะคอลัมน์ที่ไม่มีอยู่จริงเมื่อใช้หน่วยความจําเหลือน้อย
คอลัมน์ที่มีประสิทธิภาพในหน่วยความจํามากที่สุดคือคอลัมน์ที่คุณไม่เคยนําเข้าในที่แรก ถ้าคุณต้องการสร้างแบบอย่างมีประสิทธิภาพ ให้ดูที่แต่ละคอลัมน์และถามตัวเองว่าคอลัมน์นั้นสนับสนุนการวิเคราะห์ที่คุณต้องการหรือไม่ หากไม่ หรือคุณไม่แน่ใจ ให้ปล่อยไว้ คุณสามารถเพิ่มคอลัมน์ใหม่ในภายหลังได้เสมอถ้าคุณต้องการ
ตัวอย่างสองคอลัมน์ที่ควรถูกแยกออกเสมอ
ตัวอย่างแรกเกี่ยวข้องกับข้อมูลที่มาจากคลังสินค้าข้อมูล ในคลังสินค้าของข้อมูล โดยทั่วไปจะค้นหาวัตถุของกระบวนการ ETL ที่โหลดและรีเฟรชข้อมูลในคลังสินค้า คอลัมน์ เช่น "วันที่สร้าง", "วันที่อัปเดต" และ "เรียกใช้ ETL" จะถูกสร้างขึ้นเมื่อข้อมูลถูกโหลด ไม่มีคอลัมน์เหล่านี้ที่ต้องใช้ในรูปแบบและควรยกเลิกการเลือกเมื่อคุณนําเข้าข้อมูล
ตัวอย่างที่สองเกี่ยวข้องกับการละเว้นคอลัมน์คีย์หลักเมื่อนําเข้าตารางข้อเท็จจริง
ตารางหลายตารางรวมถึงตารางข้อเท็จจริง จะมีคีย์หลัก ตารางส่วนใหญ่ เช่น ตารางที่มีข้อมูลลูกค้า พนักงาน หรือการขาย คุณจะต้องการคีย์หลักของตารางเพื่อให้คุณสามารถใช้เพื่อสร้างความสัมพันธ์ในรูปแบบได้
ตารางข้อเท็จจริงจะแตกต่างกัน ในตารางข้อเท็จจริง คีย์หลักจะใช้เพื่อระบุแถวแต่ละแถวอย่างไม่ไม่เหมือนใคร ในขณะที่จําเป็นเพื่อวัตถุประสงค์ในการปรับมาตรฐาน การใช้งานจะมีประโยชน์น้อยลงในรูปแบบข้อมูลที่คุณต้องการเฉพาะคอลัมน์ที่ใช้เพื่อการวิเคราะห์หรือสร้างความสัมพันธ์ของตาราง ด้วยเหตุผลนี้ เมื่อนําเข้าจากตารางข้อเท็จจริง ไม่ต้องรวมคีย์หลัก คีย์หลักในตารางข้อเท็จจริงจะใช้ช่องว่างจํานวนมหาศาลในรูปแบบ แต่ยังไม่มีประโยชน์ เนื่องจากไม่สามารถใช้ในการสร้างความสัมพันธ์ได้
หมายเหตุ: ในคลังสินค้าและฐานข้อมูลหลายมิติข้อมูล ตารางขนาดใหญ่ที่มีข้อมูลตัวเลขส่วนใหญ่มักจะเรียกว่า "ตารางข้อเท็จจริง" โดยทั่วไปแล้ว ตารางข้อเท็จจริงจะรวมประสิทธิภาพทางธุรกิจหรือข้อมูลธุรกรรม เช่น จุดข้อมูลยอดขายและต้นทุน ที่รวบรวมและจัดแนวกับหน่วยองค์กร ผลิตภัณฑ์ ส่วนการตลาด ภูมิภาคทางภูมิศาสตร์ และอื่นๆ คอลัมน์ทั้งหมดในตารางข้อเท็จจริงที่มีข้อมูลธุรกิจหรือข้อมูลที่สามารถใช้เพื่อการอ้างอิงโยงที่เก็บไว้ในตารางอื่นควรรวมอยู่ในตัวแบบเพื่อสนับสนุนการวิเคราะห์ข้อมูล คอลัมน์ที่คุณต้องการแยกเป็นคอลัมน์คีย์หลักของตารางข้อเท็จจริง ซึ่งประกอบด้วยค่าเฉพาะที่มีอยู่ในตารางข้อเท็จจริงเท่านั้นและไม่มีที่อื่น เนื่องจากตารางข้อเท็จจริงมีขนาดใหญ่มาก ประสิทธิภาพในโมเดลที่มากที่สุดบางส่วนจะได้รับมาจากการแยกแถวหรือคอลัมน์จากตารางข้อเท็จจริง
วิธีการแยกคอลัมน์โดยไม่จําเป็น
ตัวแบบที่มีประสิทธิภาพจะมีเฉพาะคอลัมน์ที่คุณต้องการในเวิร์กบุ๊กของคุณเท่านั้น ถ้าคุณต้องการควบคุมคอลัมน์ที่รวมอยู่ในตัวแบบ คุณจะต้องใช้ตัวช่วยสร้างการนําเข้าตารางในAdd-in Power Pivotเพื่อนําเข้าข้อมูลแทนกล่องโต้ตอบ "นําเข้าข้อมูล" ใน Excel
เมื่อคุณเริ่มตัวช่วยสร้างการนําเข้าตาราง ให้คุณเลือกตารางที่จะนําเข้า
ในแต่ละตาราง คุณสามารถคลิกปุ่ม แสดงตัวอย่าง&ตัวกรอง แล้วเลือกส่วนของตารางที่คุณต้องการจริงๆ เราขอแนะนนะให้คุณยกเลิกการเลือกคอลัมน์ทั้งหมดก่อน แล้วดําเนินการตรวจสอบคอลัมน์ที่คุณต้องการ หลังจากพิจารณาว่าคอลัมน์เหล่านั้นต้องการการวิเคราะห์หรือไม่
แล้วการกรองเฉพาะแถวที่จําเป็นล่ะ
ตารางมากมายในฐานข้อมูลขององค์กรและคลังสินค้าของข้อมูลจะมีข้อมูลในอดีตที่สะสมได้ในช่วงเวลานาน นอกจากนี้ คุณอาจพบว่าตารางที่คุณสนใจมีข้อมูลในพื้นที่ของธุรกิจที่ไม่ต้องใช้ในการวิเคราะห์เฉพาะของคุณ
เมื่อใช้ตัวช่วยสร้างการนําเข้าตาราง คุณสามารถกรองข้อมูลในอดีตหรือข้อมูลที่ไม่เกี่ยวข้องได้ ดังนั้น คุณจึงประหยัดพื้นที่ในโมเดลได้มาก ในรูปภาพต่อไปนี้ ตัวกรองวันที่จะถูกใช้เพื่อเรียกใช้เฉพาะแถวที่มีข้อมูลในปีปัจจุบันเท่านั้น โดยไม่รวมข้อมูลในอดีตที่ไม่ต้องการ
จะเกิดอะไรขึ้นถ้าเราต้องการคอลัมน์ เรายังสามารถลดค่าใช้จ่ายในพื้นที่ได้หรือไม่
มีเทคนิคเพิ่มเติมสองสามอย่างที่คุณสามารถปรับใช้เพื่อให้คอลัมน์เป็นผู้สมัครการบีบอัดที่ดียิ่งขึ้น โปรดอย่าลืมว่าลักษณะเดียวของคอลัมน์ที่มีผลต่อการบีบอัดคือจํานวนของค่าที่ไม่เหมือนใคร ในส่วนนี้ คุณจะได้เรียนรู้เกี่ยวกับวิธีการปรับเปลี่ยนบางคอลัมน์เพื่อลดจํานวนค่าที่ไม่เหมือนใคร
การปรับเปลี่ยนคอลัมน์ Datetime
ในหลายกรณี คอลัมน์เวลาวันที่ใช้เวลานานมาก โชคดีที่มีหลายวิธีในการลดความต้องการด้านที่เก็บข้อมูลให้กับชนิดข้อมูลนี้ เทคนิคจะแตกต่างกันโดยขึ้นอยู่กับวิธีที่คุณใช้คอลัมน์ และระดับความสบายของคุณในการสร้างคิวSQLคิวรี
คอลัมน์ Datetime จะมีส่วนวันที่และเวลา เมื่อคุณถามตัวเองว่าคุณต้องการคอลัมน์หรือไม่ ให้ถามคําถามเดียวกันหลายครั้งกับคอลัมน์ เวลาวันที่:
-
ฉันต้องมีส่วนเวลาหรือไม่
-
ฉันต้องมีส่วนเวลาที่ระดับชั่วโมงหรือไม่ นาทีใช่หรือไม่ วินาทีใช่หรือไม่ , มิลลิวินาที?
-
ฉันมีคอลัมน์ เวลาวันที่ หลายคอลัมน์เนื่องจากฉันต้องการคํานวณความแตกต่างระหว่างคอลัมน์เหล่านั้น หรือเพียงเพื่อรวมข้อมูลตามปี เดือน ไตรมาส และอื่นๆ
วิธีที่คุณตอบคําถามแต่ละข้อเหล่านี้จะระบุตัวเลือกของคุณในการจัดการกับคอลัมน์ เวลาวันที่
โซลูชันเหล่านี้ทั้งหมดต้องการการปรับเปลี่ยนคิวSQLคิวรี เมื่อต้องการปรับเปลี่ยนคิวรีได้ง่ายขึ้น คุณควรกรองคอลัมน์อย่างน้อยหนึ่งคอลัมน์ในทุกตาราง ด้วยการกรองคอลัมน์ คุณเปลี่ยนการสร้างคิวรีจากรูปแบบตัวย่อ (SELECT *) เป็นข้อความ SELECT ที่มีชื่อคอลัมน์ที่มีคุณสมบัติครบถ้วน ซึ่งปรับเปลี่ยนได้ง่ายขึ้นมาก
ลองมาดูคิวรีที่สร้างขึ้นมาให้คุณ จากกล่องโต้ตอบ คุณสมบัติตาราง คุณสามารถสลับไปยังตัวแก้ไขคิวรี และดูคิวรีปัจจุบันSQLคิวรีปัจจุบันของแต่ละตาราง
จาก คุณสมบัติตาราง ให้เลือก ตัวแก้ไขคิวรี
ตัวแก้ไขคิวรี จะแสดงSQLคิวรีที่ใช้สร้างตาราง ถ้าคุณกรองคอลัมน์ใดๆ ออกจากการนําเข้า คิวรีของคุณจะมีชื่อคอลัมน์แบบเต็มดังนี้
|
ในทางตรงกันข้าม ถ้าคุณนําเข้าตารางโดยทั้งหมด โดยไม่เลือกคอลัมน์ใดๆ หรือนําตัวกรองใดๆ ไปใช้กับ คุณจะเห็นคิวรีเป็น "เลือก * จาก " ซึ่งจะปรับเปลี่ยนได้ยากขึ้น:
|
การปรับเปลี่ยนคิวSQLรี
Now that you know how to find the query, you can modify it to further reduce the size of your model.
-
ถ้าคอลัมน์ที่ประกอบด้วยข้อมูลสกุลเงินหรือทศนิยม ถ้าคุณไม่ต้องการทศนิยม ให้ใช้ไวยากรณ์นี้เพื่อกรอจุดทศนิยม
"SELECT ROUND([Decimal_column_name],0)... .”
ถ้าคุณต้องการเศษส่วนแต่ไม่ใช่เศษส่วนของเซ็นต์ ให้แทนที่ 0 ด้วย 2 ถ้าคุณใช้ตัวเลขติดลบ คุณสามารถปัดเศษเป็นหน่วย หลักสิบ และอื่นๆ
-
ถ้าคุณมีคอลัมน์ Datetime ที่ชื่อ dbo Bigtable [Date Time] และคุณไม่ต้องใช้ส่วน เวลา ให้ใช้ไวยากรณ์เพื่อกล่างเวลา:
"SELECT CAST (dbo. Bigtable [เวลาวันที่] เป็นวันที่) AS [เวลาวันที่]) "
-
ถ้าคุณมีคอลัมน์ Datetime ที่ชื่อ dbo Bigtable [เวลาวันที่] และคุณต้องการทั้งส่วน วันที่และเวลา ให้ใช้หลายคอลัมน์ในคิวรี SQL แทนคอลัมน์ วันที่และเวลา เดียว:
"SELECT CAST (dbo. Bigtable [เวลาวันที่] เป็นวันที่ ) เป็น [เวลาวันที่],
datepart(hh, dbo. Bigtable [เวลาวันที่]) เป็น [เวลาของวันที่],
datepart(mi, dbo. Bigtable [เวลาวันที่]) เป็น [นาทีของเวลาวันที่],
datepart(ss, dbo. Bigtable [เวลาวันที่]) เป็น [Date Time Seconds],
datepart(ms, dbo. Bigtable [เวลาวันที่]) เป็น [Date Time Milliseconds]"
ใช้คอลัมน์ได้มากเท่าที่คุณต้องการเพื่อเก็บแต่ละส่วนในคอลัมน์ที่แยกต่างหาก
-
ถ้าคุณต้องการชั่วโมงและนาที และคุณต้องการให้คอลัมน์เหล่านั้นอยู่ด้วยกันเป็นคอลัมน์เวลาเดียว คุณสามารถใช้ไวยากรณ์ :
Timefromparts(datepart(hh, dbo. Bigtable [เวลาวันที่]), datepart(mm, dbo. Bigtable [เวลาวันที่])) เป็น [Date Time HourMinute]
-
ถ้าคุณมีคอลัมน์เวลาวันที่สองคอลัมน์ เช่น [เวลาเริ่มต้น] และ [เวลาสิ้นสุด] และสิ่งที่คุณต้องการจริงๆ คือความแตกต่างของเวลาระหว่างคอลัมน์ในไม่กี่วินาทีเป็นคอลัมน์ที่ชื่อว่า [ระยะเวลา] ให้เอาทั้งคอลัมน์ออกจากรายการและเพิ่ม:
"datediff(ss,[วันที่เริ่มต้น],[วันที่สิ้นสุด]) เป็น [ระยะเวลา]"
ถ้าคุณใช้ ms หลักแทน ss คุณจะได้รับระยะเวลาเป็นมิลลิวินาที
การใช้การวัดที่คํานวณของ DAX แทนคอลัมน์
ถ้าคุณเคยใช้งานภาษานิพจน์ DAX มาก่อน คุณอาจทราบอยู่แล้วว่ามีการใช้คอลัมน์จากการคํานวณในการหาคอลัมน์ใหม่โดยยึดตามคอลัมน์อื่นในรูปแบบข้อมูล ในขณะที่การวัดจากการคํานวณจะถูกกําหนดเพียงครั้งเดียวในโมเดล แต่จะถูกประเมินเมื่อใช้ในรายงาน PivotTable หรือรายงานอื่นๆ เท่านั้น
เทคนิคการบันทึกหน่วยความจําหนึ่งวิธีคือการแทนที่คอลัมน์ปกติหรือคอลัมน์ที่คํานวณด้วยการวัดที่คํานวณ ตัวอย่างแบบคลาสสิกคือราคาต่อหน่วย ปริมาณ และผลรวม ถ้าคุณมีทั้งสามอย่าง คุณสามารถประหยัดพื้นที่ได้โดยการรักษาไว้เพียงสองส่วน และคํานวณจากสามรายการโดยใช้ DAX
คุณควรเก็บ 2 คอลัมน์ใดไว้
ในตัวอย่างข้างต้น ให้คงปริมาณและราคาต่อหน่วยไว้ สองค่าเหล่านี้มีค่าน้อยกว่าผลรวม เมื่อต้องการคํานวณ ผลรวม ให้เพิ่มการวัดแบบคํานวณ เช่น
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
คอลัมน์ที่คํานวณจะเหมือนกับคอลัมน์ปกติซึ่งทั้งสองคอลัมน์ใช้พื้นที่มากขึ้นในรูปแบบข้อมูล ในทางตรงกันข้าม การวัดที่ใช้ในการคํานวณจะถูกคํานวณระหว่างการคํานวณและอย่าเว้นวรรค
บทสรุป
ในบทความนี้ เราได้พูดคุยเกี่ยวกับวิธีการต่างๆ ที่สามารถช่วยให้คุณสร้างโมเดลที่มีประสิทธิภาพในหน่วยความจํามากขึ้น วิธีการลดขนาดไฟล์และความต้องการหน่วยความจําของตัวแบบข้อมูล คือการลดจํานวนโดยรวมของคอลัมน์และแถว และจํานวนค่าไม่จําเป็นที่ปรากฏในแต่ละคอลัมน์ ต่อไปนี้เป็นเทคนิคบางอย่างที่เราครอบคลุม:
-
การเอาคอลัมน์ออกแน่นอนว่าเป็นวิธีที่ดีที่สุดในการประหยัดพื้นที่ เลือกคอลัมน์ที่คุณต้องการจริงๆ
-
ในบางครั้ง คุณสามารถเอาคอลัมน์ออกและแทนที่ด้วยการวัดที่คํานวณในตาราง
-
คุณอาจไม่ต้องใช้แถวทั้งหมดในตาราง คุณสามารถกรองแถวใน ตัวช่วยสร้างการนําเข้าตาราง
-
โดยทั่วไปแล้ว การแยกคอลัมน์เดียวออกเป็นหลายส่วนที่แตกต่างกันคือวิธีที่ดีในการลดจํานวนค่าเฉพาะในคอลัมน์ แต่ละส่วนจะมีจํานวนค่าไม่แน่นอนอยู่น้อย และผลรวมรวมจะมีน้อยกว่าคอลัมน์รวมเดิม
-
ในหลายกรณี คุณยังต้องมีส่วนที่แตกต่างกันเพื่อใช้เป็นตัวแบ่งส่วนข้อมูลในรายงานของคุณ เมื่อเหมาะสม คุณสามารถสร้างล.ก.ล.ต. จากส่วนต่างๆ เช่น ชั่วโมงนาที และ วินาที
-
หลายครั้ง คอลัมน์จะมีข้อมูลมากกว่าที่คุณต้องการเช่นกัน ตัวอย่างเช่น สมมติว่าคอลัมน์จัดเก็บทศนิยม แต่คุณใช้การจัดรูปแบบเพื่อซ่อนจุดทศนิยมทั้งหมด การปัดเศษอาจมีประสิทธิภาพมากในการลดขนาดของคอลัมน์ตัวเลข
เมื่อคุณได้สิ่งที่คุณสามารถลดขนาดเวิร์กบุ๊กของคุณเสร็จแล้ว ให้พิจารณาใช้ตัวปรับขนาดเวิร์กบุ๊กให้เหมาะสมด้วย It analyzes your Excel workbook and if possible, compresses it further. ดาวน์โหลดWorkbook Size Optimizer
ลิงก์ที่เกี่ยวข้อง
ข้อกำหนดและขีดจำกัดของตัวแบบข้อมูล
ตัวปรับขนาดเวิร์กบุ๊กให้เหมาะสม
PowerPivot: การวิเคราะห์ข้อมูลและการสร้างตัวแบบข้อมูลใน Excel อย่างมีประสิทธิภาพ
