Przeczytaj w języku angielskim

Udostępnij za pośrednictwem


Natywna obsługa zapytań w łącznikach niestandardowych dodatku Power Query

Uwaga

W tym artykule opisano zaawansowane tematy dotyczące implementacji natywnej obsługi zapytań dla łączników niestandardowych, a także składania zapytań na ich podstawie. W tym artykule założono, że masz już działającą wiedzę na temat tych pojęć.

Aby dowiedzieć się więcej na temat łączników niestandardowych dodatku Power Query, zobacz Omówienie zestawu POWER Query SDK.

W dodatku Power Query możesz wykonywać niestandardowe zapytania natywne względem źródła danych, aby pobrać szukane dane. Można również włączyć możliwość obsługi składania zapytań w całym procesie i kolejnych procesów przekształcania wykonywanych wewnątrz dodatku Power Query.

Celem tego artykułu jest pokazanie, jak można zaimplementować taką możliwość dla łącznika niestandardowego.

Wymagania wstępne

W tym artykule użyto jako punktu początkowego przykładu, który używa sterownika ODBC SQL dla źródła danych. Implementacja natywnej funkcji zapytań jest obecnie obsługiwana tylko w przypadku łączników ODBC, które są zgodne ze standardem SQL-92.

Przykładowy łącznik używa sterownika SQL Server Native Client 11.0 . Upewnij się, że ten sterownik został zainstalowany, aby wykonać czynności opisane w tym samouczku.

Możesz również wyświetlić ukończoną wersję przykładowego łącznika z folderu Finish w repozytorium GitHub.

Modyfikowanie wartości SQLCapabilities łącznika

W rekordzie SqlCapabilities przykładowego łącznika można znaleźć pole rekordu o nazwie Sql92Translation i wartości PassThrough . To nowe pole jest niezbędne do przekazania zapytania natywnego przy użyciu dodatku Power Query bez konieczności sprawdzania poprawności.

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"
]),

Przed przejściem do przodu upewnij się, że to pole jest wyświetlane w łączniku. Jeśli nie, zobaczysz ostrzeżenia i błędy później, gdy dojdzie do korzystania z funkcji, która nie jest obsługiwana, ponieważ nie jest zadeklarowana przez łącznik.

Skompiluj plik łącznika (jako plik mez lub pqx) i załaduj go do programu Power BI Desktop na potrzeby testowania ręcznego i zdefiniuj element docelowy dla zapytania natywnego.

Ręczne testowanie natywnych możliwości zapytań łącznika

Uwaga

W tym artykule użyjemy przykładowej bazy danych AdventureWorks2019. Możesz jednak postępować zgodnie z dowolną wybraną bazą danych programu SQL Server i wprowadzić niezbędne zmiany, jeśli chodzi o szczegóły wybranej bazy danych.

Sposób implementacji natywnej obsługi zapytań w tym artykule polega na tym, że użytkownik zostanie poproszony o wprowadzenie trzech wartości:

  • Nazwa serwera
  • Nazwa bazy danych
  • Zapytanie natywne na poziomie bazy danych

Teraz w programie Power BI Desktop przejdź do środowiska Pobierz dane i znajdź łącznik o nazwie SqlODBC Sample.

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

W oknie dialogowym łącznika wprowadź parametry serwera i nazwę bazy danych. Następnie wybierz opcję OK.

Screenshot of connector dialog with server and database as parameters.

Zostanie wyświetlone nowe okno nawigatora. W nawigatorze można wyświetlić natywne zachowanie nawigacji ze sterownika SQL, który wyświetla hierarchiczny widok serwera i baz danych w nim. Kliknij prawym przyciskiem myszy bazę danych AdventureWorks2019 , a następnie wybierz pozycję Przekształć dane.

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

Ten wybór umożliwia wyświetlenie edytora Power Query i podgląd elementów docelowych zapytania natywnego, ponieważ wszystkie zapytania natywne powinny być uruchamiane na poziomie bazy danych. Sprawdź pasek formuły ostatniego kroku, aby lepiej zrozumieć, jak łącznik powinien przejść do miejsca docelowego zapytań natywnych przed ich wykonaniem. W takim przypadku na pasku formuły są wyświetlane następujące informacje:

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

Źródło jest nazwą poprzedniego kroku, który w tym przypadku jest po prostu opublikowaną funkcją łącznika z przekazanymi parametrami. Lista i rekord w nim po prostu ułatwiają przechodzenie do tabeli do określonego wiersza. Wiersz jest definiowany przez kryteria z rekordu, w którym pole Nazwa musi być równe AdventureWorks2019 , a pole Kind musi być równe bazie danych. Po zlokalizowaniu [Data] wiersza na zewnątrz listy {} program Power Query może uzyskać dostęp do wartości w polu Dane , co w tym przypadku jest tabelą. Aby lepiej zrozumieć tę nawigację, możesz wrócić do poprzedniego kroku (źródło).

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

Testowanie zapytania natywnego

Po zidentyfikowaniu elementu docelowego utwórz niestandardowy krok po kroku nawigacji, wybierając ikonę fx na pasku formuły.

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

Zastąp formułę na pasku formuły następującą formułą, a następnie wybierz klawisz Enter.

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

Po zastosowaniu tej zmiany powinno pojawić się ostrzeżenie poniżej paska formuły z żądaniem uprawnienia do uruchamiania natywnego zapytania względem źródła danych.

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

Wybierz pozycję Edytuj uprawnienie. Zostanie wyświetlone nowe okno dialogowe Zapytanie natywnej bazy danych, które próbuje ostrzec o możliwościach uruchamiania zapytań natywnych. W tym przypadku wiemy, że ta instrukcja SQL jest bezpieczna, więc wybierz pozycję Uruchom , aby wykonać polecenie.

Screenshot showing how to approve a native database query dialog.

Po uruchomieniu zapytania w edytorze Power Query zostanie wyświetlony podgląd zapytania. Ta wersja zapoznawcza sprawdza, czy łącznik może uruchamiać zapytania natywne.

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

Implementowanie natywnej logiki zapytań w łączniku

Po zebraniu informacji z poprzednich sekcji celem jest przetłumaczenie takich informacji na kod łącznika.

Sposobem, w jaki można wykonać to tłumaczenie, jest dodanie nowego pola rekordu NativeQueryProperties do rekordu publikowania łącznika, co w tym przypadku jest rekordemSqlODBC.Publish. Rekord NativeQueryProperties odgrywa kluczową rolę w definiowaniu sposobu interakcji łącznika z funkcją Value.NativeQuery .

Nowe pole rekordu składa się z dwóch pól:

  • NavigationSteps: to pole definiuje sposób, w jaki nawigacja powinna być wykonywana lub obsługiwana przez łącznik. Zawiera listę rekordów, które przedstawiają kroki przechodzenia do określonych danych, które mają być wykonywane przy użyciu Value.NativeQuery funkcji . W ramach każdego rekordu definiuje, jakie parametry są wymagane lub potrzebne, aby taki nawigacja dotarła do żądanego celu.
  • DefaultOptions: to pole pomaga określić, jak niektóre parametry opcjonalne powinny być dołączone lub dodane do rekordu Value.NativeQuery opcji. Udostępnia zestaw opcji domyślnych, które mogą być używane podczas wykonywania zapytań względem źródła danych.

Kroki nawigacji można podzielić na dwie grupy. Pierwszy zawiera te wartości, które są wprowadzane przez użytkownika końcowego, takie jak nazwa serwera lub bazy danych, w tym przypadku. Drugi zawiera te wartości, które są pochodne przez określoną implementację łącznika, takie jak nazwa pól, które nie są wyświetlane użytkownikowi podczas pobierania danych. Te pola mogą obejmować Name, , DataKindi inne w zależności od implementacji łącznika.

W tym przypadku był tylko jeden krok nawigacji składający się z dwóch pól:

  • Nazwa: to pole jest nazwą bazy danych, która została przekazana przez użytkownika końcowego. W tym przypadku wartość to AdventureWorks2019, ale to pole powinno być zawsze przekazywane zgodnie z tym, co użytkownik końcowy wprowadził podczas pobierania danych.
  • Rodzaj: to pole to informacje, które nie są widoczne dla użytkownika końcowego i są specyficzne dla łącznika lub implementacji sterownika. W tym przypadku ta wartość określa, do jakiego typu obiektu należy uzyskać dostęp. W przypadku tej implementacji to pole będzie stałą wartością składającą się z ciągu Database.

Takie informacje zostaną przetłumaczone na następujący kod. Ten kod powinien zostać dodany jako nowe pole do rekordu SqlODBC.Publish .

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

Ważne

W nazwie pól jest rozróżniana wielkość liter i należy jej użyć, jak pokazano w powyższym przykładzie. Wszystkie informacje przekazywane do pól , ConstantValueIndexNamelub FieldDisplayName muszą pochodzić z kodu M łącznika.

W przypadku wartości, które zostaną przekazane z wprowadzonego przez użytkownika, możesz użyć pary FieldDisplayName i IndexName. W przypadku wartości, które są stałe lub wstępnie zdefiniowane i nie mogą być przekazywane przez użytkownika końcowego, możesz użyć pary ConstantValue i IndexName. W tym sensie rekord NavigationSteps składa się z dwóch pól:

  • Indeksy: definiuje pola i wartości, których należy użyć, aby przejść do rekordu zawierającego element docelowy Value.NativeQuery funkcji.
  • FieldAccess: określa, które pole zawiera obiekt docelowy, który jest często tabelą.

DefaultOptions

Pole DefaultOptions umożliwia przekazywanie opcjonalnych parametrów do Value.NativeQuery funkcji podczas korzystania z natywnej funkcji zapytania dla łącznika.

Aby zachować składanie zapytań po zapytaniu natywnym i zakładając, że łącznik ma możliwości składania zapytań, możesz użyć następującego przykładowego kodu dla programu EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Po wprowadzeniu tych zmian skompiluj łącznik i załaduj go do programu Power BI Desktop na potrzeby testowania i walidacji.

Testowanie i weryfikowanie łącznika

W programie Power BI Desktop z nowym łącznikiem niestandardowym uruchom łącznik z poziomu środowiska Pobierania danych . Podczas uruchamiania łącznika zauważysz, że okno dialogowe ma teraz długie pole tekstowe o nazwie Zapytanie natywne, a w nawiasie ma wymagane pola, aby działały. Wprowadź te same wartości dla serwera, bazy danych i instrukcji SQL wprowadzonej wcześniej podczas testowania łącznika.

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

Po wybraniu przycisku OK zostanie wyświetlony podgląd tabeli wykonanego zapytania natywnego w nowym oknie dialogowym.

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

Wybierz przycisk OK. Nowe zapytanie zostanie załadowane w edytorze Power Query, w którym można przeprowadzić dalsze testowanie łącznika zgodnie z potrzebami.

Uwaga

Jeśli łącznik ma możliwości składania zapytań i jawnie zdefiniował EnableFolding=true jako część opcjonalnego rekordu dla Value.NativeQueryprogramu , możesz dodatkowo przetestować łącznik w edytorze Power Query, sprawdzając, czy dalsze przekształcenia są składane z powrotem do źródła, czy nie.