Udostępnij przez

Obsługa natywnych zapytań w niestandardowych łącznikach Power Query

Uwaga / Notatka

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ć własne zapytania natywne do źródła danych, aby pobrać szukane dane. Można również umożliwić obsługę składania zapytań w trakcie tego procesu i kolejnych procesach przekształcania wykonywanych w Power Query.

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

Wymagania wstępne

W tym artykule jako punkt wyjścia wykorzystano przykład, który używa sterownika SQL ODBC 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 jest zainstalowany, aby móc korzystać z tego samouczka.

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

Zmień SQLCapabilities swojego łącznika

W rekordzie SqlCapabilities przykładowego łącznika można znaleźć pole 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 przyjdzie do korzystania z funkcjonalności, która nie jest obsługiwana, ponieważ nie została 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 / Notatka

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.

Zrzut ekranu przedstawiający łącznik znajdujący się wewnątrz środowiska pobierania danych programu Power BI Desktop.

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

Zrzut ekranu przedstawiający okno dialogowe łącznika z serwerem i bazą danych jako parametry.

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.

Zrzut ekranu przedstawiający opcję przekształcania danych z menu kontekstowego w oknie Nawigator.

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 nawigować do celu 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 niej po prostu ułatwiają nawigowanie tabeli do określonego wiersza. Wiersz jest definiowany przez kryteria z rekordu, w którym pole Nazwa musi być równe AdventureWorks2019, a pole Rodzaj musi być równe Baza 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).

Zrzut ekranu przedstawiający tabelę przedstawiającą wartości i pola, które zostały użyte do kroku nawigacji.

Testowanie zapytania natywnego

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

Zrzut ekranu przedstawiający przycisk fx wewnątrz formuły użytej do utworzenia kroku niestandardowego.

Zastąp formułę na pasku formuły następującą formułą, a następnie wybierz 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.

Zrzut ekranu przedstawiający uprawnienie wymagane do uruchomienia tego natywnego komunikatu ostrzegawczego zapytania bazy danych.

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.

Zrzut ekranu przedstawiający sposób zatwierdzania natywnego okna dialogowego zapytania bazy danych.

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

Zrzut ekranu przedstawiający zapytanie natywne wykonane w ramach początkowego tworzenia i testowania łącznika.

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 Publish łącznika, którym w tym przypadku jest rekord SqlODBC.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 opisują kroki pozwalające nawigować do określonych danych, które chcesz zapytać przy użyciu funkcji Value.NativeQuery. W ramach każdego rekordu definiuje, jakie parametry są wymagane, aby taka 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ą określone przez określoną implementację łącznika, takie jak nazwy pól, które nie są wyświetlane użytkownikowi podczas doświadczenia pozyskiwania danych. Te pola mogą obejmować Name, , KindDatai 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 w niezmienionej formie od tego, co użytkownik końcowy wprowadził podczas procesu 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

Nazwa pól jest rozróżnialna pod względem wielkości liter i należy ją używać dokładnie tak, jak pokazano w powyższym przykładzie. Wszystkie informacje przekazywane do pól , ConstantValueIndexNamelub FieldDisplayName muszą pochodzić z kodu M łącznika.

Dla wartości, które zostaną przekazane z tego, co wprowadził użytkownik, 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 Twój łącznik posiada taką funkcjonalność, możesz użyć następującego przykładowego kodu dla 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 opcji 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.

Zrzut ekranu przedstawiający okno dialogowe łącznika z wyświetlonym polem dla długiego tekstu zapytania natywnego.

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

Zrzut ekranu przedstawiający okno dialogowe z podglądem tabeli wykonanego zapytania natywnego.

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

Uwaga / Notatka

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

  • Last updated on