การสนับสนุนคิวรีดั้งเดิมในตัวเชื่อมต่อแบบกําหนดเองของ Power Query

Note

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

หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับตัวเชื่อมต่อแบบกําหนดเองของ Power Query ไปที่ ภาพรวม Power Query SDK

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

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

ข้อกําหนดเบื้องต้น

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

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

คุณยังสามารถดูตัวเชื่อมต่อตัวอย่างเวอร์ชันที่เสร็จสมบูรณ์ได้จาก โฟลเดอร์ เสร็จสิ้น ในที่เก็บ GitHub

ปรับเปลี่ยน SQLCapabilities ของตัวเชื่อมต่อของคุณ

ในเรกคอร์ด SqlCapabilities ของตัวเชื่อมต่อตัวอย่าง คุณสามารถค้นหาฟิลด์เรกคอร์ดที่มีชื่อ Sql92Translation และค่า PassThrough สําหรับฟิลด์นั้น เขตข้อมูลใหม่นี้จําเป็นสําหรับคิวรีดั้งเดิมที่จะส่งผ่านโดยใช้ Power Query โดยไม่มีการตรวจสอบความถูกต้องใดๆ

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

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

สร้างไฟล์ตัวเชื่อมต่อ (เป็น .mez หรือ .pqx) และโหลดลงใน Power BI Desktop สําหรับการทดสอบด้วยตนเองและเพื่อกําหนดเป้าหมายสําหรับคิวรีดั้งเดิมของคุณ

ทดสอบความสามารถของคิวรีดั้งเดิมของตัวเชื่อมต่อของคุณด้วยตนเอง

Note

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

วิธีที่การสนับสนุนแบบสอบถามแบบเนทีฟจะถูกนําไปใช้ในบทความนี้คือผู้ใช้จะถูกร้องขอให้ป้อนค่าสามค่า:

• ชื่อเซิร์ฟเวอร์
• ชื่อฐานข้อมูล
• คิวรีดั้งเดิมที่ระดับฐานข้อมูล

ตอนนี้ภายใน Power BI Desktop ไปที่ประสบการณ์การรับข้อมูล และค้นหาตัวเชื่อมต่อที่มีชื่อ ตัวอย่าง SqlODBC

สําหรับกล่องโต้ตอบตัวเชื่อมต่อ ให้ป้อนพารามิเตอร์สําหรับเซิร์ฟเวอร์และชื่อฐานข้อมูลของคุณ จากนั้นเลือก ตกลง

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

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

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

แหล่งที่มา คือชื่อของขั้นตอนก่อนหน้า ซึ่งในกรณีนี้ เป็นเพียงฟังก์ชันที่เผยแพร่ของตัวเชื่อมต่อของคุณด้วยพารามิเตอร์ที่ส่งผ่าน รายการและเรกคอร์ดภายในนั้นจะช่วยนําทางตารางไปยังแถวที่ระบุ แถวถูกกําหนดโดยเกณฑ์จากเรกคอร์ดที่ฟิลด์ ชื่อ ต้องเท่ากับ AdventureWorks2019 และฟิลด์ ชนิด ต้องเท่ากับ ฐานข้อมูล เมื่อแถวอยู่แล้ว ด้านนอก [Data] ของรายการ {} จะช่วยให้ Power Query เข้าถึงค่า ภายในเขตข้อมูล ซึ่งในกรณีนี้คือตาราง คุณสามารถกลับไปที่ขั้นตอนก่อนหน้า (แหล่งที่มา) เพื่อทําความเข้าใจการนําทางนี้ได้ดียิ่งขึ้น

ทดสอบคิวรีเนทีฟ

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

แทนที่สูตรภายในแถบสูตรด้วยสูตรต่อไปนี้ แล้วเลือก Enter

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

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

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

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

ใช้ตรรกะคิวรีดั้งเดิมในตัวเชื่อมต่อของคุณ

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

วิธีที่คุณสามารถแปลนี้ให้สําเร็จได้คือการเพิ่มฟิลด์เรกคอร์ด NativeQueryProperties ใหม่ลงในเรกคอร์ด เผยแพร่ ของตัวเชื่อมต่อ ซึ่งในกรณีนี้คือ SqlODBC.Publish เรกคอร์ด NativeQueryPropertiesเรกคอร์ดมีบทบาทสําคัญในการกําหนดว่าตัวเชื่อมต่อจะโต้ตอบกับValue.NativeQueryฟังก์ชันอย่างไร

ฟิลด์เรกคอร์ดใหม่ประกอบด้วยสองฟิลด์:

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

ขั้นตอนการนําทาง

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

ในกรณีนี้ มีขั้นตอนการนําทางเพียงขั้นตอนเดียวที่ประกอบด้วยสองฟิลด์:

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

ข้อมูลดังกล่าวจะถูกแปลเป็นรหัสต่อไปนี้ ควรเพิ่มรหัสนี้เป็นเขตข้อมูลใหม่ในเรกคอร์ดของคุณSqlODBC.Publish

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

สําคัญ

ชื่อของฟิลด์จะคํานึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ และต้องใช้ตามที่แสดงในตัวอย่างด้านบน ข้อมูลทั้งหมดที่ส่งผ่านไปยังฟิลด์ ไม่ว่าจะ ConstantValueเป็น , IndexNameหรือ FieldDisplayName ต้องได้มาจากรหัส M ของตัวเชื่อมต่อ

สําหรับค่าที่จะส่งผ่านจากสิ่งที่ผู้ใช้ป้อน คุณสามารถใช้FieldDisplayNameคู่และIndexName สําหรับค่าที่คงที่หรือกําหนดไว้ล่วงหน้า และผู้ใช้ConstantValueปลายทางไม่สามารถส่งผ่านได้ คุณสามารถใช้คู่และIndexName ในแง่นี้ เรกคอร์ด NavigationSteps ประกอบด้วยสองฟิลด์:

• ดัชนี: กําหนดฟิลด์และค่าที่จะใช้เพื่อนําทางไปยังเรกคอร์ดที่มีเป้าหมายสําหรับ Value.NativeQuery ฟังก์ชัน
• FieldAccess: กําหนดฟิลด์ที่เก็บเป้าหมาย ซึ่งโดยทั่วไปจะเป็นตาราง

ตัวเลือกเริ่มต้น

ฟิลด์นี้ DefaultOptions ช่วยให้คุณสามารถส่งผ่านพารามิเตอร์เพิ่มเติมไปยัง Value.NativeQuery ฟังก์ชันเมื่อใช้ความสามารถคิวรีดั้งเดิมสําหรับตัวเชื่อมต่อของคุณ

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

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

เมื่อมีการเปลี่ยนแปลงเหล่านี้ ให้สร้างตัวเชื่อมต่อและโหลดลงใน Power BI Desktop เพื่อทดสอบและตรวจสอบความถูกต้อง

ทดสอบและตรวจสอบตัวเชื่อมต่อ

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

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

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

Note

ถ้าตัวเชื่อมต่อของคุณมีความสามารถในการพับคิวรี และได้กําหนด EnableFolding=true ไว้อย่างชัดเจนว่าเป็นส่วนหนึ่งของเรกคอร์ดตัวเลือกสําหรับ Value.NativeQueryคุณสามารถทดสอบตัวเชื่อมต่อของคุณเพิ่มเติมในตัวแก้ไข Power Query ได้โดยตรวจสอบว่าการแปลงเพิ่มเติมพับกลับไปยังแหล่งที่มาหรือไม่