เปรียบเทียบเครื่องมือในการ ETL ระหว่าง Power Query กับ SSIS

ชุดกลไก ETL ผู้เขียนบรรยายไว้ในบทความชื่อ “กลไก ETL คืออะไร” และได้แนะนำ Microsoft SQL Server Integration Service (SSIS) ว่าเป็นแพลตฟอร์มสำหรับ ETL คุณภาพสูง เอาไว้ในบทความดังกล่าว
แต่ยังมีเครื่องมือสำหรับ ETL อีกตัวหนึ่งชื่อ Power Query ได้รับความนิยมมากในขณะนี้ เพราะเป็นส่วนหนึ่งของ Power BI Desktop (หรือเป็น add-in ของ Microsoft Excel) เหมาะกับงานในลักษณะ Self-Service ETL และมีความซับซ้อนน้อยกว่า SSIS
 

มีอะไรใน Power Query

Power Query เป็นเครื่องมือในการสร้าง Query ที่ดำเนินการ ตามลำดับ เพื่อนำเข้าข้อมูลและปรับรูปร่างข้อมูล (ในเครื่องมือนี้ มักใช้คำว่า Data Shaping แทนคำว่า Data Transformation) จากแหล่งข้อมูลอย่างน้อยหนึ่งแหล่งข้อมูล (สามารถศึกษาการทำงานของ Power Query ได้ที่ บทความ Power Query ทำงานอย่างไร)
ดังภาพ จะแสดง Query Formula ของลำดับที่ชื่อว่า “Removed Columns” เป็นการตัดคอลัมน์ Color และ Model ออก

เราสามารถดู Query ทั้งหมดได้ผ่านทาง Advanced Editor ดังภาพ จะเห็นลำดับการดำเนินการปรับรูปร่างข้อมูลทั้งหมด ภายใต้ประโยค Let

ภาษา Power Query Formula Language  มักเรียกอย่างไม่เป็นทางการว่า "ภาษา M" รองรับแหล่งข้อมูลขนาดใหญ่ ผลลัพธ์ของ Power Query กรณีใช้ใน Microsoft Excel จะออกมาในรูปแบบตารางบน Worksheet สามารถนำไปดำเนินการคำนวณต่อได้ ส่วนกรณีใช้ใน Power BI จะส่งต่อข้อมูลไปดำเนินการหาผลรวมในส่วนของ Data Model ต่อไป
 
การสร้างลำดับการ Query เพื่อปรับรูปร่างข้อมูลทำได้โดย
 

• ผ่าน UI ที่ออกแบบมาให้ใช้งานง่าย
  • สามารถดึงข้อมูลเข้า นำข้อมูลมารวมกัน
  • ทำ Data Cleansing และข้อมูลที่ผิดพลาดออก
  • ปรับลำดับก่อนหลังของการ Query เพิ่มลำดับ หรือลบลำดับการ Query
  • เปลี่ยนชนิดข้อมูลเพื่อความเหมาะสม
• ผ่านภาษา M ที่ซับซ้อนมากขึ้น

โดยการปรับรูปร่างส่วนใหญ่มักเป็น
 

• การลบแถวข้อมูล หรือลบคอลัมน์
• การเปลี่ยนแปลงชนิดข้อมูล
• Pivot หรือ Unpivot คอลัมน์
• รวมกลุ่มแถวข้อมูล
• เปลี่ยนชื่อตาราง
• ระบุและแก้ไขข้อผิดพลาด
• ผนวกหลาย Queries เข้าเป็น Query เดียว

สำหรับ Power Query ก็ถือเป็นเครื่องมือ ETL ที่ฝังผลลัพธ์ไปใช้งานต่อใน Excel หรือ Power BI ได้เลย แต่ไม่ได้หมายความว่าสามารถใช้ทดแทนแพลตฟอร์มสำหรับ ETL ระดับองค์กรอย่าง SSIS ได้
 

มีอะไรใน SSIS ที่ Power Query ไม่มี

ก่อนอื่นการสร้างกลไก ETL บน Microsoft SQL Integration Service (SSIS) นั้น ต้องพัฒนาผ่าน Visual Studio ที่ติด SQL Server Data Tools (SSDT) และลง Extension ชื่อ “SQL Server Integration Services Projects” (กรณีใช้ Visual Studio 2019)
เมื่อพัฒนาเสร็จก็จะ Deploy ไปไว้บน SSIS Catalog (สามารถเปิดใช้งานได้บน Microsoft SQL Server Database Engine ที่ลงคอมโพเนนต์ชื่อ Integration Services เพื่อสามารถขับเคลื่อน SSIS Packages ได้) และสามารถตั้งให้ทำงานอัตโนมัติตามคาบเวลาได้
SSIS ประกอบด้วยกลไกในส่วนของ Control Flow และ Data Flow โดย Data Flow เป็นส่วนหนึ่งของ Control Flow ดังแสดงในภาพ

SSIS Data Flow

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

จากภาพ เป็นตัวอย่าง Data Flow การนำข้อมูล Subcategory จากแหล่งข้อมูลต้นทาง (ในที่นี้คือตาราง staging.Subcategory) ไปใส่ปลายทาง (ตารางปลายทางคือ dw.DimSubCategory)
โดยข้อมูลจะมี CategoryID ติดมาด้วย ให้นำไป Lookup หาว่ามี Category นี้แล้วหรือไม่ (ในที่นี้ Lookup ไปที่ตาราง dw.DimCategory เป็นการ Validate ข้อมูล เพื่อไม่ให้มีข้อมูลที่ไม่สอดคล้องเข้าสู่ Data Warehouse)
หากพบว่ามีก็ดำเนินต่อ ลำดับถัดไปจะนำ SubCategoryID ไป Lookup หาว่ามีข้อมูลในตารางปลายทางแล้วหรือไม่ (ตารางปลายทางคือ dw.DimSubCategory)

• ถ้ามีแล้ว ในทำการ Update ข้อมูล
• แต่ถ้าไม่มีให้ Insert ข้อมูลใหม่เข้าไป ตัวอย่าง Data Flow ที่ยกมานี้ในมุมมองผู้เขียนคิดว่า Power Query ก็สามารถทำได้ แต่ไม่ง่ายนัก

Transformations ทั้งหมดที่มีใน SSIS Data Flow แยกเป็นกลุ่ม ได้ดังนี้

ในกลุ่มของ Row Transformations ,Rowset Transformations และ Split and Join Transformations นั้นในส่วนของ Power Query ก็สามารถทำได้บ้าง แต่

• แม้ว่า Power Query จะสามารถเขียน Customer Column จากนิพจน์ได้บ้าง แต่บน SSIS สามารถกำหนดตัวแปร และพารามิเตอร์ขึ้นใช้งาน  เครื่องมืออย่าง Derived Column สามารถเขียนนิพจน์ที่ซับซ้อนอันเกิดจากส่วนผสมระหว่างค่าคงที่ ค่าจากต้นทาง ค่าจากตัวแปร ค่าจากพารามิเตอร์ ตัวดำเนินการแบบต่าง ๆ และฟังก์ชั่นที่มีให้ใช้หลากหลายกลุ่มฟังก์ชั่น
• ไม่มีเครื่องมือเหมือน OLE DB Command ที่สามารถส่งคำสั่งไปที่ต้นทางหรือปลายทาง เพื่อให้แสดงบทบาทเป็น Source, Destination หรือ Transformation ที่ซับซ้อนขึ้นได้
• ไม่มีเครื่องมือเหมือน Percentage Sampling และ Row Sampling เพื่อสุ่มข้อมูล
• ไม่มีเครื่องมือเหมือน Conditional Split เพื่อกระจายข้อมูลตามนิพจน์ที่ซับซ้อน
• ไม่มีเครื่องมือเหมือน CDC Splitter เพื่อรองรับการ ETL แบบ Incremental จำนวนมากผ่าน Change Data Capture (CDC)
• ไม่มีเครื่องมือเหมือนในกลุ่ม Auditing, BI และ Custom Transformations
  • อาจสามารถทำ Data Cleansing ได้บ้างใน Power Query แต่ไม่ดีเท่าเรียกใช้ Data Quality Service (DQS)
  • Slow Changing Dimension สำคัญมากสำหรับการสร้าง Dimension ที่รองรับข้อมูล Historical แต่บน Power Query สามารถทำได้ด้วย ภาษา M ที่ซับซ้อน แต่บน SSIS ทำผ่าน Wizard ไม่กี่ขั้นตอน แถมบนภาษา M ใช้ไปแล้วมักเกิดข้อผิดพลาดขึ้นบ่อยครั้ง
  • กลุ่ม Custom Transformations สามารถพัฒนาผ่านภาษา Visual Basic หรือ Visual C# เพื่อสร้างกลไก Transformation ขึ้นใหม่ได้เอง แต่ Power Query ทำไม่ได้

ในฝั่ง SSIS Data flow นั้นยังสามารถดักข้อผิดพลาดในแต่ละลำดับไม่ว่าจะเป็นในส่วนของ Source, Destination หรือ Transformation ต่าง ๆ

จากภาพเป็นการดักข้อผิดพลาดที่ Source ซึ่งสามารถเลือกตอบสนองต่อข้อผิดพลาดได้ 3 วิธีบนแต่ละคอลัมน์ คือ
 

• Fail Component คือหยุดไม่ดำเนินการต่อ
• Ignore Failure ดำเนินการต่อ ส่วนใหญ่มักจะให้ค่าคอลัมน์ที่เกิดข้อผิดพลาดมีค่าเป็น NULL
• Redirect row สามารถแยกท่อลำเลียงข้อมูล (Data flow Pipeline) ออกไปอีกทาง เฉพาะแถวข้อมูลที่มีคอลัมน์ที่เกิดข้อผิดพลาด

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

SSIS Control Flow

อย่างที่ผู้เขียนบอกไปแล้วว่าถ้าจะเทียบ Power Query กับ SSIS คงเทียบได้แค่ในส่วนของ Data Flow เท่านั้น แต่ SSIS ยังมีส่วนของ Control Flow อยู่ด้วย

• Control Flow คือการพัฒนากระบวนการก่อน และหลังการเรียกใช้ Data Flow Task โดยที่ไม่ต้องลงมือเขียน Code แต่อย่างใด แค่นำ Tasks และ Containers มาเชื่อมด้วย Precedent Constraint เพื่อพิจารณาว่า Task หรือ Container ก่อนหน้านั้น ดำเนินการสำเร็จ, ดำเนินการล้มเหลว หรือดำเนินผ่านเสร็จ (จะสำเร็จหรือล้มเหลวก็ได้) ถึงจะมาดำเนินการ Task หรือ Container ถัดไป

ผู้เขียนขอไม่นำ Tasks ทั้งหมดใน Control Flow มาเล่าเพราะอย่างไรเสียทั้งหมดนี้ก็ไม่มีใน Power Query แต่จะแสดงตัวอย่างแทน

จากภาพเป็นการวนลูปอ่านไฟล์เข้ามาทีละไฟล์ เพื่อ ETL เพื่อให้ได้ตาราง dw.DimProductCategory, dw.DimProductSubCategory และตาราง dw.DimProduct โดยดำเนินการผ่าน Data Flow Task
(ตัวอย่างของการ ETL ตาราง dw.DimProductSubCategory คือตัวอย่างของ Data Flow ก่อนหน้านี้)

เมื่อได้ตาราง Dimension ทั้ง 3 ตารางเสร็จ ก็จะย้ายไฟล์ที่อ่านปัจจุบันไปไว้ใน Archive Folder
เมื่อวนลูปอ่านไฟล์เข้ามี ETL จนครบทุกไฟล์หากไม่พบข้อผิดพลาด จะทำการส่งเมล์แจ้งว่า ETL สำเร็จ
แต่หากเกิดข้อผิดพลาดขึ้นขณะ ETL ก็จะส่งเมล์ไปแจ้งเตือนว่า ETL ไม่สำเร็จ (Precedent Constraint เส้นสีเขียวคือกรณี Success และเส้นสีแดงคือกรณี Failure)
นอกเหนือจากตัวอย่างที่ยกมาแล้ว SSIS Control Flow สามารถ
 

• ดักรับและตอบสนองต่อข้อผิดพลาด จะเห็นว่า Precedent Constraint ชนิด Failure สามารถใช้เพื่อดักรับและตอบสนองต่อข้อผิดพลาดได้ นอกเหนือจากนั้น ยังมีสิ่งที่เรียกว่า Event Handlers ที่สามารถตอบสนองต่อเหตุการณ์ต่าง ๆ ได้ละเอียดขึ้นกว่า Precedent Constraint ชนิด Failure
• ติดตามการทำงานผ่านการบันทึก Log แบบเดิม หรือผ่านทาง SSIS Catalog Report แบบใหม่ก็ได้

สรุป

จะเห็นว่า SSIS นั้นถูกออกแบบมาเป็นแพลตฟอร์มสำหรับ ETL ระดับองค์กรอย่างแท้จริง ซึ่งการใช้งาน SSIS นั้นมีรายละเอียดค่อนข้างเยอะ
และเป้าหมายหลักของ SSIS คือการ ETL ให้ได้ Dimensional Model (Data Warehouse ตามแนวทางของ Ralph Kimball) เครื่องไม้เครื่องมือจึงสอดคล้องกับ Model ชนิดนี้

ผู้เขียนพัฒนาหลักสูตร ETL with SQL Server Integration Service (SSIS) ไว้ที่สถาบัน 9Expert ผู้อ่านที่สนใจสามารถติดตามหลักสูตรฝึกอบรมได้
สำหรับ Power Query ก็ถือเป็นเครื่องมือที่มีประสิทธิภาพในการ ETL เพื่อใช้งานในลักษณะ Self-Service BI ซึ่งผู้เขียนสรุปความแตกต่างไว้เป็นตารางดังนี้