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.
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.
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.
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.
W oknie dialogowym łącznika wprowadź parametry serwera i nazwę bazy danych. Następnie wybierz opcję OK.
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.
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).
Po zidentyfikowaniu elementu docelowego utwórz niestandardowy krok po kroku nawigacji, wybierając ikonę fx na pasku formuły.
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.
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.
Po uruchomieniu zapytania w edytorze Power Query zostanie wyświetlony podgląd zapytania. Ta wersja zapoznawcza sprawdza, czy łącznik może uruchamiać zapytania natywne.
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
, , Data
Kind
i 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 , ConstantValue
IndexName
lub 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ą.
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.
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.
Po wybraniu przycisku OK zostanie wyświetlony podgląd tabeli wykonanego zapytania natywnego w nowym oknie dialogowym.
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.NativeQuery
programu , 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.