การพับแบบสอบถามและการเชื่อมต่อกับ SQL Server

ในบทช่วยสอนนี้ เราจะเรียนรู้วิธีเชื่อมต่อกับเซิร์ฟเวอร์ SQL นอกจากนี้ เราจะหารือเกี่ยวกับวิธีการทำงานของการพับแบบสอบถามใน Power Query และวิธีที่เราสามารถเรียกใช้คำสั่ง SQL ใน LuckyTemplates ภายใน Power Query เราสามารถแยกและจัดการข้อมูลจากแหล่งต่างๆ ด้วยการพับแบบสอบถาม เราสามารถออฟโหลดการแปลงข้อมูลไปยังแหล่งที่มา แทนที่จะทำใน LuckyTemplates

การพับข้อความค้นหามีประสิทธิภาพมากกับฐานข้อมูลขนาดใหญ่เพื่อให้แน่ใจว่าการปรับปรุงประสิทธิภาพสำหรับรายงานของคุณ

สารบัญ

• การเชื่อมต่อกับฐานข้อมูล SQL Server สำหรับการพับแบบสอบถาม
• ทำความเข้าใจการพับแบบสอบถามใน Power Query
• การระบุและแก้ไขการพับแบบสอบถามที่เสียหาย
• ประโยชน์ของการเชื่อมต่อ SQL Server และเรียกใช้ภาษา SQL
• บทสรุป

การเชื่อมต่อกับฐานข้อมูล SQL Server สำหรับการพับแบบสอบถาม

เราจะทำบทช่วยสอนนี้ในตัวแก้ไข Power Query ขั้นแรก คลิกSQL Serverภายใต้New Source

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

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

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

จากนั้น หากเราคลิกตัวเลือกขั้นสูงจะมีส่วนที่เราสามารถเขียนคำสั่ง SQL ได้ เราจะเรียนรู้วิธีการดำเนินการนี้ในภายหลัง

ในตัวอย่างนี้ เราต้องการครั้งละหนึ่งตารางเท่านั้น ดังนั้นเราจะไม่นำคอลัมน์หรือตารางความสัมพันธ์เข้ามา ในกรณีนั้น เราต้องยกเลิกการเลือกตัวเลือกนั้น

สุดท้ายให้คลิกตกลง

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

ด้วยเหตุนี้ เราจึงมีข้อมูลในตัวแก้ไข Power Query ของเรา

ทำความเข้าใจการพับแบบสอบถามใน Power Query

ภายใน แผง แหล่งที่มา คลิกขวาที่การนำทาง จากนั้นเลือกดูข้อความค้นหาดั้งเดิม

ด้วยวิธีนี้ เราจะสามารถเห็นคำสั่งที่ดำเนินการโดยเครื่องนี้ โปรแกรม Power Query สร้างคำสั่งนี้เพื่อทำงานใน SQL Server ตอนนี้ให้คลิกตกลง .

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

เมื่อเราคลิกขวาที่ตัวกรองตัวใดตัวหนึ่ง เราจะเห็นว่า ตัวเลือก View Native Queryยังคงมีอยู่

แบบสอบถาม SQLนี้ จากตัว กรองของเราถูกเรียกใช้ในSQL Server เมื่อเราไม่ได้ใช้ตัวกรอง เราจะได้ 5 ล้านแถว ตอนนี้เราได้ใช้ตัวกรองแล้ว มีเพียง 4 ล้านแถวเท่านั้น

ซึ่งหมายความว่า LuckyTemplates กำลังแยก 4 ล้านแถวแทนที่จะเป็น 5 ล้านแถวจาก SQL Server ด้วยเหตุนี้ จำนวนแถวจึงลดลงและจำนวนการโหลดจากเครือข่ายของเราก็ลดลงเช่นกัน

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

เราได้เพิ่มขั้นตอนอื่นโดยลบหนึ่งคอลัมน์ออกจากตารางของเรา หากเราคลิกขวา เราจะเห็น ตัวเลือก View Native Queryซึ่งหมายความว่ายังทำงานอยู่

การระบุและแก้ไขการพับแบบสอบถามที่เสียหาย

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

ซึ่งจะเพิ่ม ขั้นตอน Change Typeภายใต้APPLIED STEPS หากเราคลิกขวา เราจะเห็นว่าView Native Queryถูกปิดใช้งานแล้ว ซึ่งหมายความว่าQuery Foldingใช้งานไม่ได้

เมื่อQuery Foldใช้งานไม่ได้ การแปลงอื่นๆ ที่เราจะทำจะทำใน LuckyTemplates Power Query แต่ไม่ใช่ในระบบต้นทางอีกต่อไป

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

อีกตัวอย่างหนึ่ง สมมติว่าเราต้องการกรอง OrderDate เพื่อแสดงเฉพาะวันที่หลังวันที่ 1 มกราคม 2012

หากเราดูตัวกรองนั้นในขั้นตอนที่ใช้ตัว เลือก ดูข้อความค้นหาเนทีฟจะไม่สามารถมองเห็นได้

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

สำหรับตัวอย่างนี้ เราจะเพียงคลิกขวาที่ขั้นตอนการกรองล่าสุดที่เราสร้างขึ้นแล้วคลิกย้ายก่อนหรือเพียงแค่ลากไปที่ด้านบนสุดของการเปลี่ยนแปลงประเภทการเปลี่ยนแปลง

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

ประโยชน์ของการเชื่อมต่อ SQL Server และเรียกใช้ภาษา SQL

เช่น เราต้องการแสดงข้อมูลยอดขายรวมตามรูปแบบประเทศ ดังรูป

ใน ตาราง FactInternetSalesเรามี คอลัมน์ SalesAmountแต่ไม่มีข้อมูลประเทศ

เรายังคงสามารถรับข้อมูลประเทศได้เนื่องจากเรามีคอลัมน์SalesTerritoryKey

สิ่งที่เราต้องทำคือนำ ตาราง DimSalesมาไว้ที่นี่ เพื่อให้เรารวมเข้ากับFactInternetSales ของเรา ได้ จากนั้นเราต้องนำคอลัมน์ประเทศมาจัดกลุ่มตามคอลัมน์ประเทศ ซึ่งซับซ้อนมากและอาจใช้เวลานาน ดังนั้น แทนที่จะทำทั้งหมดนั้นในPower Queryซึ่งไม่มีประสิทธิภาพ เราควรทำบนSQLแทน

ในการทำเช่น นั้นเพียงคลิกNew Source > SQL Server

มาเชื่อมต่อกับเซิร์ฟเวอร์ของเราอีกครั้งชื่อ localhost และAdventureWorksDW2012เป็นฐานข้อมูลของเรา

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

เราไม่ต้องการรวมคอลัมน์ความสัมพันธ์ ดังนั้นเราจะยกเลิกการเลือกตัวเลือกนี้ที่นี่ จากนั้นคลิก ปุ่ม OKเพื่อเรียกใช้คำสั่งนี้

หลังจากดำเนินการคำสั่ง เราจะเห็นหน้าต่าง แสดงตัวอย่างนี้แสดงยอดขายรวมตามเขตการขาย

ดังนั้นเราจึงสามารถรับผลลัพธ์ที่คล้ายกันของ ตาราง SalesByCountryได้โดยใช้คำสั่ง SQL อย่างง่าย แทนที่จะจัดการกับตารางและคอลัมน์ต่างๆ ใน ​​Power Query ของเรา

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

การดาวน์โหลดและติดตั้ง SQL Server
SQL สำหรับผู้ใช้ LuckyTemplates – หลักสูตร LuckyTemplates ใหม่
การสืบค้นข้อมูลจากแหล่งข้อมูลหลายแห่ง

บทสรุป

ในบทช่วยสอนนี้ เราได้เรียนรู้ว่าQuery Foldคืออะไร และค้นพบประโยชน์ของมัน เรายังได้กล่าวถึงขั้นตอนในการเชื่อมต่อPower QueryกับSQL Server

นอกจากนี้ เราได้พูดคุยเกี่ยวกับประโยชน์ของการเชื่อมต่อกับSQL Serverและการสร้างการแปลงบนSQL Serverแทนที่จะทำบนPower Query

หวังว่าคุณจะได้เห็นว่าการแปลงทั้งหมดใน SQL มีประสิทธิภาพมากขึ้นและเร็วขึ้นอย่างไรเมื่อเทียบกับการทำในPower Query

สิ่งที่ดีที่สุด

ฮาฟิซ