Κοινή χρήση μέσω

Υποστήριξη εγγενών ερωτημάτων σε προσαρμοσμένες συνδέσεις Power Query

  • Άρθρο

Σημείωμα

Αυτό το άρθρο καλύπτει θέματα για προχωρημένους σχετικά με την υλοποίηση υποστήριξης εγγενών ερωτημάτων για προσαρμοσμένες συνδέσεις, καθώς και την αναδίπλωση ερωτημάτων πάνω από αυτές. Αυτό το άρθρο προϋποθέτει ότι έχετε ήδη μια λειτουργική γνώση αυτών των εννοιών.

Για να μάθετε περισσότερα σχετικά με τις προσαρμοσμένες συνδέσεις Power Query, μεταβείτε στην Επισκόπηση SDK του Power Query.

Στο 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 για μη αυτόματη δοκιμή και για να ορίσετε τον προορισμό για το εγγενές ερώτημά σας.

Δοκιμάστε με μη αυτόματο τρόπο τις δυνατότητες εγγενούς ερωτήματος της σύνδεσής σας

Σημείωμα

Για αυτό το άρθρο, θα χρησιμοποιήσετε το δείγμα βάσης δεδομένων AdventureWorks2019. Ωστόσο, μπορείτε να ακολουθήσετε τις οδηγίες με οποιαδήποτε βάση δεδομένων SQL Server της επιλογής σας και να κάνετε τις απαραίτητες αλλαγές όταν αυτό αφορά τις λεπτομέρειες της επιλεγμένης βάσης δεδομένων.

Ο τρόπος υλοποίησης της υποστήριξης εγγενούς ερωτήματος σε αυτό το άρθρο είναι ότι θα ζητηθεί από το χρήστη να καταχωρήσει τρεις τιμές:

  • Όνομα διακομιστή
  • Όνομα βάσης δεδομένων
  • Εγγενές ερώτημα στο επίπεδο βάσης δεδομένων

Τώρα μέσα στο Power BI Desktop, μεταβείτε στην εμπειρία Λήψη δεδομένων και βρείτε τη σύνδεση με το όνομα Δείγμα SqlODBC.

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

Για το παράθυρο διαλόγου σύνδεσης, εισαγάγετε τις παραμέτρους για τον διακομιστή σας και το όνομα της βάσης δεδομένων σας. Στη συνέχεια επιλέξτε OK.

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, αλλά αυτό το πεδίο θα πρέπει να διαβιβάζεται πάντα ως έχει από αυτά που εισήγαγε ο τελικός χρήστης κατά τη διάρκεια της εμπειρίας λήψης δεδομένων.
  • Είδος: Αυτό το πεδίο είναι πληροφορίες που δεν είναι ορατές στον τελικό χρήστη και αφορούν συγκεκριμένα την υλοποίηση της σύνδεσης ή του προγράμματος οδήγησης. Σε αυτήν την περίπτωση, αυτή η τιμή προσδιορίζει τον τύπο αντικειμένου στο οποίο θα πρέπει να γίνεται πρόσβαση. Για αυτή την υλοποίηση, αυτό το πεδίο θα είναι μια σταθερή τιμή που αποτελείται από τη συμβολοσειρά 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

Το 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.

Αφού επιλέξετε OK, εμφανίζεται σε ένα νέο παράθυρο διαλόγου μια προεπισκόπηση πίνακα του εκτελεσθέντος εγγενούς ερωτήματος.

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

Επιλέξτε OK. Θα φορτωθεί τώρα ένα νέο ερώτημα μέσα στο πρόγραμμα επεξεργασίας Power Query, όπου μπορείτε να κάνετε περαιτέρω δοκιμές της σύνδεσής σας, όπως απαιτείται.

Σημείωμα

Εάν η σύνδεσή σας έχει δυνατότητες αναδίπλωσης ερωτήματος και έχει οριστεί EnableFolding=true ρητά ως μέρος της προαιρετικής εγγραφής για Value.NativeQueryτο , τότε μπορείτε να ελέγξετε περαιτέρω τη σύνδεσή σας στο πρόγραμμα επεξεργασίας Power Query, ελέγχοντας εάν πραγματοποιείται περαιτέρω αναδίπλωση μετασχηματισμού στην προέλευση ή όχι.