แชร์ผ่าน

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

  • บทความ

หมายเหตุ

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

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

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

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

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

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

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

คุณยังสามารถดูเวอร์ชันที่เสร็จสิ้นของตัวเชื่อมต่อตัวอย่างจาก โฟลเดอร์ Finish ในที่เก็บ 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 เพื่อทดสอบด้วยตนเอง และกําหนดเป้าหมายสําหรับคิวรีดั้งเดิมของคุณ

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

หมายเหตุ

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

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

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

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

Screenshot of the connector found inside the get data experience of Power BI Desktop.

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

Screenshot of connector dialog with server and database as parameters.

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

Screenshot of the transform data option from the contextual menu inside the Navigator window.

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

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

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

Screenshot of a table that shows the values and fields that were used for the navigation step.

ทดสอบคิวรีในระบบของฐานข้อมูล

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

Screenshot of the fx button inside the formula that's used to create a custom step.

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

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

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

Screenshot of the permission is required to run this native database query warning message.

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

Screenshot showing how to approve a native database query dialog.

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

Screenshot of the native query executed in initial connector development and testing.

ใช้ตรรกะคิวรีในระบบของฐานข้อมูลในตัวเชื่อมต่อของคุณ

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

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

เขตข้อมูลระเบียนใหม่ประกอบด้วยสองเขตข้อมูล:

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

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

สําหรับกรณีนี้ มีขั้นตอนการนําทางเดียวเท่านั้นที่ประกอบด้วยสองเขตข้อมูล:

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

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

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

สำคัญ

ชื่อของเขตข้อมูลต้องตรงตามตัวพิมพ์ใหญ่-เล็ก และต้องใช้ตามที่แสดงในตัวอย่างข้างต้น ข้อมูลทั้งหมดที่ส่งผ่านไปยังเขตข้อมูล อย่างใดอย่างหนึ่ง, ConstantValueIndexNameหรือ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 ที่คุณป้อนไว้ก่อนหน้านี้เมื่อทดสอบตัวเชื่อมต่อ

Screenshot of the connector dialog with the native query long text field shown.

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

Screenshot of the dialog with a table preview of the executed native query.

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

หมายเหตุ

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