เปลี่ยนความโกลาหลให้เป็นกำไร: ทำความเข้าใจกระบวนการ ETL

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

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

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

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

ความรู้พื้นฐาน ETL

ETL ย่อมา จาก Extract, Transform และ Load ETL คือกลุ่มของกระบวนการที่ออกแบบมาเพื่อเปลี่ยนการจัดเก็บข้อมูลที่ซับซ้อนนี้เป็นกระบวนการที่มีการจัดระเบียบ เชื่อถือได้ และทำซ้ำได้ เพื่อช่วยให้บริษัทของคุณสร้างยอดขายเพิ่มขึ้นด้วยข้อมูลที่คุณมีอยู่แล้ว

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

มาดำดิ่งลงไปในสามขั้นตอนของ ETL เพื่ออธิบายขั้นตอนอย่างละเอียด

การสกัด

การสกัดเป็นกระบวนการรับข้อมูลจากแหล่งเดียวหรือหลายแหล่ง แหล่งที่มาสามารถมีรูปแบบและโครงสร้างที่แตกต่างกัน เช่น เอกสาร สเปรดชีต ไฟล์ CSV ไฟล์แฟล็ต ฐานข้อมูลเชิงสัมพันธ์ เช่น Oracle, MySQL, SQL Server, ฐานข้อมูลที่ไม่ใช่เชิงสัมพันธ์ และอื่นๆ

การสกัดมีสองประเภทหลัก: เต็ม และ บางส่วน

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

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

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

การแปลงร่าง

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

อาจจำเป็นต้องใช้พื้นที่การแสดงละคร ทั้งนี้ขึ้นอยู่กับปัจจัยหลายประการ พื้นที่การแสดงละครคือพื้นที่จัดเก็บระดับกลางที่ใช้เพื่อเก็บข้อมูลชั่วคราวที่แยกจากแหล่งข้อมูลที่จะแปลง

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

มีงานหลายอย่างที่ดำเนินการระหว่างขั้นตอนการเปลี่ยนแปลง:

• การเลือก: เกณฑ์การเลือกข้อมูลที่ดึงออกมา การคัดเลือกสามารถทำได้ในระหว่างขั้นตอนการสกัด ระหว่างขั้นตอนการเปลี่ยนแปลง หรือทั้งสองขั้นตอน
• บูรณาการ: การรวมข้อมูลจากขั้นตอนการดึงข้อมูลไปยังพื้นที่การแสดงละคร ชุดค่าผสมนี้หมายถึงการเพิ่ม การลบ และการอัปเดตข้อมูลในพื้นที่จัดเตรียมตามข้อมูลที่แยกออกมา
• เข้าร่วม: ใช้เพื่อเข้าร่วมข้อมูลที่แยกออกมา คล้ายกับการรวม SQL (การรวมภายใน, การรวมด้านซ้าย, การเข้าร่วมทางขวา, การเข้าร่วมแบบเต็ม ฯลฯ )
• การ ทำความสะอาดหรือขัดผิว: ลบข้อมูลที่ไม่สอดคล้องหรือไม่ถูกต้อง หรือข้อมูลที่มีข้อผิดพลาดเพื่อปรับปรุงคุณภาพข้อมูล การทำงานกับแหล่งข้อมูลหลายแหล่งเพิ่มโอกาสในการมีปัญหาข้อมูลที่ต้องทำความสะอาด เช่น:
  • ความสมบูรณ์ของการอ้างอิง (ลูกค้าที่มีหมวดหมู่ไม่มีอยู่จริง)
  • ค่าที่หายไป (ลูกค้าที่ไม่มี ID)
  • เอกลักษณ์ (มากกว่าหนึ่งคนที่มี SSN เดียวกัน)
  • สะกดผิด (ซัน ดิเอโก, แคนนาดา, แอล.แองเจเลส)
  • ค่าที่ขัดแย้งกัน (Alex dob 04.27.1974, Alex dob 04.14.2000)
  • และอื่น ๆ อีกมากมาย
• การ สรุป: สรุปชุดข้อมูลสำหรับใช้ภายหลัง
• การ รวม: ข้อมูลที่รวบรวมและสรุปในกลุ่ม
• การ รวมบัญชี: ข้อมูลจากหลายแหล่งหรือโครงสร้างที่รวมอยู่ในชุดข้อมูลเดียว

ต่อไปนี้เป็นประเภทการแปลงทั่วไปบางส่วน:

• ลบข้อมูลที่ซ้ำกัน
• การแยกและการรวมเข้าด้วยกัน
• การแปลง (วันที่ เวลา มาสก์ตัวเลข การวัด)
• การเข้ารหัส (ชายเป็น M)
• การคำนวณ (item_value = unit_Price * ปริมาณ)
• การสร้างคีย์

กำลังโหลด

สุดท้ายแต่ไม่ท้ายสุด กระบวนการสุดท้ายใน ETL คือการโหลดข้อมูลไปยังปลายทาง การโหลดคือการแทรกข้อมูลที่แปลงแล้ว (จากพื้นที่จัดเตรียมหรือไม่) ลงในที่เก็บ โดยปกติคือฐานข้อมูลคลังข้อมูล

ข้อมูลการโหลดมีสามประเภทหลัก: เต็มหรือเริ่มต้น เพิ่มขึ้น และรีเฟรช

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

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

การทดสอบ

การทดสอบเป็นหนึ่งในขั้นตอนที่สำคัญที่สุดของ ETL และยังถูกมองข้ามไปมากที่สุดอีกด้วย

การแปลงข้อมูลจากแหล่งที่มาและโครงสร้างต่างๆ และโหลดลงในคลังข้อมูลนั้นซับซ้อนมากและอาจทำให้เกิดข้อผิดพลาดได้ ข้อผิดพลาดที่พบบ่อยที่สุดได้อธิบายไว้ในขั้นตอนการเปลี่ยนแปลงข้างต้น

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

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

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

ในตัวอย่างของบริษัทเคสโทรศัพท์มือถือ เรากำลังทำงานกับแหล่งที่มาที่แตกต่างกันสามแหล่ง (ฐานข้อมูล Oracle, Salesforce และสเปรดชีตที่เป็นกรรมสิทธิ์) และรูปแบบที่แตกต่างกัน ขั้นตอนการทดสอบสามารถใช้ข้อมูลตัวอย่างจากแหล่งที่มาเดิมและเปรียบเทียบกับข้อมูลที่อยู่ในพื้นที่จัดเตรียมเพื่อรับประกันว่าการดึงข้อมูลจะเกิดขึ้นโดยไม่มีข้อผิดพลาด

ข้อมูลตัวอย่าง—ซึ่งในกรณีนี้อาจเป็นข้อมูลการขายจากสถานที่ต่างๆ สามแห่ง (ร้านค้า, คีออสก์เก่า, คีออสก์ใหม่)—จะถูกนำมาเปรียบเทียบกับแหล่งที่มาดั้งเดิม ต้องวิเคราะห์ความแตกต่าง หากมี เพื่อดูว่ายอมรับได้หรือไม่ หรือเป็นข้อผิดพลาด

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

ในบางกรณี ข้อมูลที่มีข้อผิดพลาดจะต้องถูกกำจัดและมีการเตือนเพื่อแจ้งให้ผู้รับผิดชอบทราบ

ตัวอย่างการทดสอบบางส่วน:

• ข้อมูลต้องมีการตรวจสอบความถูกต้อง
• คุณภาพของข้อมูล
• ผลงาน
• กฎของข้อมูล
• การสร้างแบบจำลองข้อมูล

การบันทึก

การบันทึกกระบวนการ ETL เป็นการรับประกันหลักว่าคุณมีระบบที่สามารถบำรุงรักษาและแก้ไขได้ง่าย

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

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

กรณีเดียวที่ฉันสามารถจินตนาการได้ว่าบันทึกที่ใดไม่สำคัญนักก็คือระบบอัตโนมัติขนาดเล็กมาก ซึ่งกระบวนการทำงานด้วยตนเองและมีข้อมูลจำนวนเล็กน้อยที่สามารถตรวจสอบได้ด้วยมือ

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

มีสามขั้นตอนหลักในการสร้างระบบบันทึก: สร้าง เก็บถาวร และ วิเคราะห์

• Generate เป็นกระบวนการบันทึกสิ่งที่เกิดขึ้นระหว่างการดำเนินการของไปป์ไลน์ ETL: เมื่อกระบวนการเริ่มต้น ไฟล์หรือตารางใดกำลังถูกแตก ข้อมูลที่บันทึกไว้ในพื้นที่จัดเตรียม ข้อความแสดงข้อผิดพลาด และอื่นๆ ข้อมูลสำคัญทั้งหมดที่สามารถช่วยวิศวกรต้องถูกบันทึก การ แจ้งเตือน : ระวังอย่าสร้างข้อมูลมากจนเสียเวลาและพื้นที่และจะไม่เป็นประโยชน์
• เก็บถาวร ข้อมูลบันทึกหมายถึงการเก็บบันทึกการดำเนินการที่ผ่านมาเพื่อค้นหาสถานการณ์ในอดีต เพื่อระบุข้อผิดพลาดหรือเปรียบเทียบกับสถานการณ์ปัจจุบันที่ต้องการปรับปรุง สิ่งสำคัญคือต้องตรวจสอบความเกี่ยวข้องของจุดเฉพาะในประวัติศาสตร์ที่จะบันทึก—ข้อมูลจากเมื่อนานมาแล้วซึ่งโครงสร้างเปลี่ยนแปลงหลายครั้งนั้นไม่คุ้มค่าที่จะเก็บไว้
• วิเคราะห์ . การวิเคราะห์บันทึกมีความสำคัญอย่างยิ่ง การจัดเก็บข้อมูลจำนวนมากที่ไม่ได้วิเคราะห์นั้นไม่สมเหตุสมผล เพียงแค่เสียเวลาและเงินในการสร้างและจัดเก็บข้อมูล การวิเคราะห์บันทึกมีความสำคัญไม่เพียงแค่ช่วยค้นหาข้อผิดพลาดเท่านั้น แต่ยังช่วยระบุจุดปรับปรุงและเพิ่มคุณภาพข้อมูลโดยรวมด้วย

ผลงาน

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

การออกแบบระบบการบันทึกควรใช้ความพยายามมากแค่ไหน?

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

บริษัทที่มีข้อมูลจำนวนมาก กระบวนการต่างๆ มากมาย แหล่งข้อมูลและรูปแบบที่แตกต่างกัน และไปป์ไลน์ที่ซับซ้อนต้องลงทุนเพื่อสร้างระบบการบันทึก ดังที่เราได้กล่าวไปแล้ว ระบบบันทึกที่ดีสามารถประหยัดเวลาและเงินได้มาก

สำหรับแนวทางที่ชัดเจนยิ่งขึ้น โดยไม่คำนึงถึงขนาดของบริษัท จำนวนข้อมูล และความถี่ในการรวมระบบ กระบวนการ ETL จะต้อง สร้างผลกำไร การลงทุนด้านเวลา เงิน และทรัพยากรทางเทคนิคจะต้องสร้างผลตอบแทน—ดังที่นักเศรษฐศาสตร์ มิลตัน ฟรีดแมนกล่าวว่า: "ไม่มีอาหารกลางวันฟรี"

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