Power Query pozwala na pobranie danych z wielu różnych źródeł i ich przekształcenie. Niewielu użytkowników PQ ma jednak świadomość, że wykonane w PQ transformacje można przesłać do bazodanowego źródła jak natywne zapytanie SQL. Właśnie ta możliwość, nazywana Query Folding, jest bardzo ważną cechą. Sprawdźmy, jak ona działa i jakie ma ograniczenia.
Działanie Query Folding
Łącząc się z serwerowym źródłem danych (np. bazą Microsoft SQL Server), Power Query tłumaczy wykonane operacje na język źródła danych. W przypadku wspominanego SQL Servera wygenerowane zostanie zapytanie w języku T-SQL, a kalkulacje zostaną przeprowadzone już w źródle – przed pobraniem danych przez PQ. Dzięki temu do Query może być pobranych mniej danych, co jest bardzo ważnym aspektem działania Query Folding. Przykładowo, jeśli baza danych zawiera 100 mln rekordów, ale interesują Cię tylko dane z tego roku (5 mln rekordów), Query Folding przyspieszy niesamowicie działanie zapytania, pobierając tylko potrzebną część danych. Operacja ta ma 2 ograniczenia:
1. Nie każde źródło obsługuje Query Folding
Mechanizm Query Folding wspierają:
- relacyjne bazy danych,
- modele Analysis Services,
- Azure Marketplace,
- listy SharePoint,
- Exchange,
- Active Directory,
- HDFS i niektóre operacje na systemie plików.
Query Folding nie działa m.in. dla plików Excel i tekstowych – wszystkie zmiany wykonane są lokalnie na naszym komputerze.
2. Nie wszystkie wykonane działania mogą być przeniesione na źródło
W przypadku baz danych dużą część operacji możesz przerzucić na źródło, m.in.:
- agregacja,
- filtracja,
- pivot/unpivot,
- proste kalkulacje liczbowe,
- proste transformacje tekstowe,
- złączenia wertykalne (union),
- złączenia horyzontalne (join).
Szczegółowa dokumentacja w tym zakresie po stronie Power Query jest niestety uboga i potrzebne są własne testy.
Część z wykonywanych w Power Query operacji może być przerzucona do źródła, a część nie. Taka sytuacja nazwa się Partially Query Folding. Ważne jest, że gdy następuje czynność nieprzerzucana na źródło, wszystkie występujące w sekwencji po niej nie zostaną wykonane na źródle. Dlatego warto jest zaplanować działania w taki sposób, by jak najwięcej z nich zostało wykonanych na źródle: na początku wykonywane na źródle, na końcu nie.
Przykład Query Folding dla bazy SQL Server
Połączę się z tabelą znajdującą się w bazie danych SQL Server. W tym celu uruchamiam zakładkę Dane> Pobieranie i przekształcanie > Nowe zapytanie > Z bazy danych > Z bazy danych programu SQL Server.
Wprowadzam parametry: nazwę serwera oraz bazę danych.
Wybieram tabelę z danymi dotyczącymi budżetu i ładuję ją jako połączenie do Power Query. By pokazać jakie działania będą przekazywane na źródło, dokonam różnych modyfikacji danych.
W pierwszej kolejności przefiltruję kolumnę typ danych i wybiorę z niej jedynie typ budżet.
Następnie grupuję dane według kolumn MPK, Kategorii biznesowej, Konta, Spółki, Typu danych oraz partnera. Polem agregacji będzie Kwota w kPLN.
W kolejnym kroku w kolumnie Partner odfiltruję wartości null.
W kolumnie Typ danych zmieniam poprzez Prawy przycisk myszy na nazwie kolumny > Przekształć > Małe litery.
Po wykonaniu wszystkich tych operacji widzę listę zastosowanych kroków.
Monitorowanie działania Query Folding
By przekonać się, które z operacji są przekazane do źródła w Power Query, wystarczy wybrać z listy kroków jeden z nich, wyświetlić menu podręczne. Powinna się na nim znaleźć pozycja Wyświetl zapytanie natywne. Jeżeli jest ona aktywna, to wybrany krok oraz wszystkie przed nim są przekazane do źródła (Query Folding działa w całości). Jeżeli jest inaczej, wówczas wybrany krok na pewno nie jest przekazany do źródła i możliwe że wcześniejsze również (by dowiedzieć się który z kroków hamuje przekazanie danych do źródła należy sprawdzić dla nich aktywność opcji Wyświetl zapytanie natywne).
Przykładem operacji, która nie jest przekazywana do źródła to transpozycja. Przekształć > Tabela >Transponuj.
Na liście kroków pojawił się nowy Trasponowano tabelę. Jak widać poniżej, opcja Wyświetl zapytanie natywne jest nieaktywna dla tego kroku, więc od tego kroku działanie nie jest przekazywane do źródła.
Kiedy dane nie są przekazywane do źródła?
Istnieje jeszcze wiele innych sytuacji, w których nie istnieje możliwość przekazania danych do źródła (Query Folding zostaje wyłączone):
- gdy używamy źródła, które nie obsługuje Query Folding.
- użycie funkcji .Buffer() języka M – funkcja ta powoduje zaczytanie wszystkich danych do pamięci Power Query. Przydaje się, gdy chcemy uniknąć wielokrotnego pobierania danych z bazy SQL, z dysku czy witryny internetowej. Polecenie buffer załaduje dane do pamięci i przestanie składać zapytania do źródła.
- użycie własnego polecenia SQL do pozyskania danych z bazy SQL
- niektóre z transformacji np. filtrowanie według opcji Ten miesiąc dla kolumny z datą.
Wyświetlenie zapytania natywnego nie jest możliwe
- ustawienia poziomów prywatności (opisane we wcześniejszym artykule https://excelbi.pl/ignoruj-poziom-prywatnosci-prosty-sposob-na-przyspieszenie-power-query/), mają one znaczenie gdy przesyłane są dane wrażliwe pomiędzy źródłami danych.
- użycie opcji usunięcia wierszy z błędami: Narzędzia główne > Zmniejsz wiersze > Usuń błędy.
- użycie i definiowanie własnych funkcji w Power Query.
Opcja Wyświetl zapytanie natywne wyłączyła się w innej sytuacji?
Podziel się w komentarzu.
Witam,
Mam pytanie związane z zapytaniami Natywnymi w PQ. Stworzyłem plik, który pobiera parametr z komórki Excel a następnie wartość tego parametru zostaje wklejona w treści kwerendy SQL do źródła danych ODBC. Po każdej zmianie parametru i odświeżeniu wszystkich zapytań pojawia się pytanie czy PQ ma uruchomić to zapytanie. Czy jest możliwość, aby Excel (PQ) Nie pytało o to za każdym razem?
Podeślij na maila kontaktowego, jak wygląda otrzymany komunikat.
Nie znałem tematu, przyznaję. Dzięki.
Ale to jest pomyłka! To robi strasznego SQLa, koszmarnego wręcz.
Uwaga, to zapytanie w normalnym SQLu:
***
SELECT COUNT(*) AS Liczność
FROM faktura
WHERE nazwa=’BRONEX’
GROUP BY nazwa
***
…tworzy taki, oto koszmarek:
***
select count(1) as [Liczność]
from
(
select [_].[t0_0]
from
(
select convert(nvarchar(max), [_].[NAZWA]) as [t0_0],
[_].[WART_NET] as [t1_0]
from
(
select [NAZWA],
[WART_NET]
from [dbo].[faktura] as [$Table]
) as [_]
) as [_]
where [_].[t0_0] = 'BRONEX’ and [_].[t0_0] is not null
) as [rows]
group by [t0_0]
***
Dziękuję, postoję.
Wojciech Gardziński
Hej Wojtek, potwierdzam, że zapytania natywne w Power Query nie są optymalne i nie ma na to innego sposobu, jak poznać SQL i wkleić gotowe zapytanie 🙂