Excel połączony z bazą danych SQL przez Power Query: SQL Server, PostgreSQL i MySQL

Skąd czytać: baza produkcyjna, replika czy kopia

Każde odświeżenie arkusza to zapytanie wysłane do bazy. Zanim klikniesz Pobierz dane, zdecyduj, w którą maszynę to zapytanie ma trafiać.

Microsoft opisuje odświeżanie danych jako ponowne uruchomienie tego samego zapytania, którym dane pobrano za pierwszym razem. Prosty rachunek: dziesięć osób trzyma otwarty przez cały dzień ten sam plik z odświeżaniem co pięć minut, czyli 10 × 12 = 120 odświeżeń na godzinę, średnio dwa na minutę, a każde to co najmniej jedno zapytanie. Jeśli z tej samej bazy korzysta sklep albo system sprzedaży, te zapytania dzielą z nim jeden serwer.

Bezpieczniejsze źródła to replika tylko do odczytu albo nocna kopia bazy. Różnice między nimi opisuje przewodnik o raportach z bazy danych. Dla Excela liczy się jeszcze jedno: jeśli arkusz ma pokazywać stan na koniec poprzedniego dnia, nocna kopia daje te same liczby co produkcja, a w ciągu dnia nie dokłada produkcji ani jednego zapytania.

Sprawdź też, czy Excel to w ogóle dobre narzędzie. Odświeża dane tylko w otwartym pliku, a każda osoba, która ma odświeżać arkusz, potrzebuje dostępu do bazy. Jeśli liczby mają czekać gotowe rano, trafiać do klienta albo arkusz ma zapisywać zmiany w bazie, lepsze będzie inne rozwiązanie; opisuję je w ostatniej sekcji.

Konto tylko do odczytu i widok zamiast tabel

Excel powinien widzieć w bazie dokładnie to, co ma trafić do arkusza, i nic ponad to. Najprościej osiągniesz to widokiem i prawem SELECT nadanym tylko na ten widok.

Widok to zapytanie zapisane w bazie, które w Nawigatorze Excela wygląda jak zwykła tabela. Microsoft opisuje widoki w SQL Server jako mechanizm bezpieczeństwa: użytkownik czyta dane przez widok, choć nie ma uprawnień do tabel pod spodem. W PostgreSQL dostęp do tabel użytych w widoku jest domyślnie sprawdzany według uprawnień właściciela widoku, a nie osoby, która z niego czyta; wyjątkiem jest widok z opcją security_invoker.

Widok porządkuje też samą pracę. Kolumny mają zrozumiałe nazwy, filtry i złączenia są zapisane raz, a przebudowa tabel nie psuje arkusza, dopóki widok zwraca te same kolumny.

Czym różni się db_datareader od db_owner w SQL Server i co daje rola pg_read_all_data w PostgreSQL, opisuje sekcja o uprawnieniach konta raportowego. Dla Excela warto pójść krok dalej: osobny schemat z widokami i prawo SELECT tylko na te widoki, jak w przykładach niżej. Nazwy są przykładowe, a widok raport.sprzedaz_miesieczna musi już istnieć.

CREATE LOGIN excel_raport WITH PASSWORD = N'<silne-haslo>';
GO
USE firma;
GO
CREATE USER excel_raport FOR LOGIN excel_raport;
GRANT SELECT ON OBJECT::raport.sprzedaz_miesieczna TO excel_raport;

-- Azure SQL Database: użytkownik zawarty w bazie, bez loginu na serwerze
-- CREATE USER excel_raport WITH PASSWORD = N'<silne-haslo>';

CREATE LOGIN [FIRMA\Excel-Raporty] FROM WINDOWS;
GO
USE firma;
GO
CREATE USER [FIRMA\Excel-Raporty] FOR LOGIN [FIRMA\Excel-Raporty];
GRANT SELECT ON OBJECT::raport.sprzedaz_miesieczna TO [FIRMA\Excel-Raporty];

CREATE ROLE excel_raport LOGIN;
-- hasło przez \password, żeby nie trafiło do historii poleceń ani do logu serwera
\password excel_raport
GRANT USAGE ON SCHEMA raport TO excel_raport;
GRANT SELECT ON raport.sprzedaz_miesieczna TO excel_raport;

Połączenie krok po kroku (SQL Server)

Kroki dotyczą Excela z Microsoft 365 na Windowsie; nazwy poleceń podaję po polsku, a w nawiasie po angielsku. Power Query nie obsługuje kolumn zaszyfrowanych funkcją Always Encrypted, więc widok dla Excela powinien je pomijać.

1. Otwórz złączeNa karcie Dane wybierz Pobierz dane, potem Z bazy danych i Z bazy danych programu SQL Server (Data > Get Data > From Database > From SQL Server Database).
2. Podaj serwer i bazęW polu Serwer wpisz nazwę serwera, a w polu Baza danych nazwę bazy; gdy ją podasz, Nawigator pokaże obiekty tylko z niej. Pole na instrukcję SQL zostaw na razie puste. Jeśli Excel nie dosięga serwera, zacznij od zapory: według Microsoftu domyślna instancja SQL Server słucha na porcie TCP 1433, a nazwana na porcie dynamicznym, który klient odnajduje przez usługę SQL Server Browser na porcie UDP 1434.
3. Wybierz sposób logowaniaDomyślnie Excel proponuje konto Windows, na którym pracujesz; druga typowa opcja to Baza danych, czyli login i hasło SQL Server. Konto organizacyjne Microsoft Entra ID zadziała tylko na serwerze, który obsługuje ten sposób logowania; inaczej pojawi się błąd o nieobsługiwanej metodzie OAuth.
4. Nie zgadzaj się odruchowo na połączenie nieszyfrowanePole Szyfruj połączenie jest domyślnie zaznaczone, a gdy serwer nie zestawi szyfrowanego połączenia, Power Query zapyta, czy połączyć się bez szyfrowania. Przy serwerze dostępnym spoza sieci firmowej poproś raczej administratora o szyfrowanie. Listę zaufanych serwerów dla certyfikatu z podpisem własnym dokumentacja złącza opisuje tylko dla Power BI Desktop i bramy danych, więc dla Excela prostszy jest certyfikat od powszechnie uznanego urzędu certyfikacji.
5. Wybierz dane w NawigatorzeZaznacz widok albo tabelę. Załaduj (Load) od razu wstawia dane do arkusza, a Przekształć dane (Transform Data) otwiera edytor, w którym usuniesz zbędne kolumny i odfiltrujesz wiersze. Pole Dodaj te dane do modelu danych (Add this data to the Data Model) zaznacz tylko wtedy, gdy potrzebujesz relacji między tabelami albo więcej wierszy, niż mieści arkusz, bo według Microsoftu model zwiększa rozmiar pliku.

PostgreSQL i MySQL: czym różni się połączenie

Przy PostgreSQL i MySQL przechodzisz przez te same okna co przy SQL Server. Inaczej wygląda logowanie, czasem trzeba doinstalować sterownik, a w części wersji Excela tych złączy w ogóle nie ma.

• Gdzie w menuDane > Pobierz dane > Z bazy danych, a tam pozycja dla PostgreSQL albo MySQL (From PostgreSQL Database, From MySQL Database). Jeśli jej nie widzisz, sprawdź wersję Excela w tabeli w sekcji o Macu, przeglądarce i limitach.
• LogowanieZłącze PostgreSQL przyjmuje login i hasło bazy albo konto Microsoft Entra ID. Złącze MySQL w Excelu przyjmuje login i hasło bazy albo konto Windows.
• Dodatkowe składnikiGdy złącze MySQL zgłosi brak dodatkowych składników, strona pomocy Excela odsyła do sterownika MySQL Connector/ODBC, a dokumentacja złącza, pisana pod Power BI Desktop, wymaga pakietu Oracle MySQL Connector/NET. Dla PostgreSQL Microsoft opisuje wbudowanego dostawcę Npgsql tylko w Power BI Desktop (od grudnia 2019 r.), o Excelu nie pisząc wprost. Oba złącza sprawdź więc na komputerze docelowym, zanim obiecasz je zespołowi.
• Szyfrowanie przy PostgreSQLSzyfrowane połączenie wymaga serwera skonfigurowanego do połączeń SSL, a na komputerze z Excelem może być potrzebny certyfikat serwera w magazynie zaufanych głównych urzędów certyfikacji. Bez tego Power Query zaproponuje połączenie nieszyfrowane.

Własne zapytanie SQL i składanie zapytań

Złącze pozwala wpisać własne zapytanie SQL zamiast wybierać tabelę. Dokumentacja Microsoftu opisuje dwie konsekwencje takiego wyboru: jedną dla bezpieczeństwa, drugą dla wydajności kolejnych kroków.

Zapytanie wpisujesz w opcjach zaawansowanych złącza, w polu instrukcji SQL (SQL statement). Microsoft pisze wprost, że choć natywne zapytania mają z założenia niczego nie zmieniać, Power Query nie gwarantuje, że zapytanie nie wpłynie na bazę. Na koncie tylko do odczytu nawet pomyłkowe UPDATE skończy się błędem uprawnień. Operacji DDL, takich jak DROP TABLE, CREATE TABLE czy ALTER TABLE, Power Query nie obsługuje.

Zapytanie wpisane w oknie złącza wykonuje się bez pytania. Gdy natywne zapytanie trafia do pliku inną drogą, na przykład przez edytor zaawansowany albo w pliku od innej osoby, Power Query pyta o zgodę przy każdym nowym tekście zapytania, bo wykona się ono z Twoimi danymi logowania. Monit wyłączysz w Dane > Pobierz dane > Opcje zapytania, w części Zabezpieczenia, ale w pliku krążącym między osobami lepiej go zostawić.

Druga konsekwencja dotyczy wydajności. Składanie zapytań (query folding) to tłumaczenie kroków z edytora, na przykład usunięcia kolumn czy filtra, na zapytanie wykonywane przez bazę, która zwraca wtedy tylko potrzebne wiersze. Według Microsoftu wspierają to zwykle źródła z własnym silnikiem zapytań, jak SQL Server, a pliki CSV i skoroszyty Excela nie.

Według Microsoftu po zapytaniu wpisanym w oknie złącza, na przykład SQL Server, kolejne kroki z edytora nie składają się do bazy. Wyjątkiem jest PostgreSQL, gdzie według dokumentacji składanie natywnych zapytań jest domyślnie włączone. W pozostałych przypadkach masz dwa wyjścia: zapisać SQL w bazie jako widok i wybrać go w Nawigatorze albo użyć Value.NativeQuery(Źródło, "SELECT …", null, [EnableFolding = true]), gdzie Źródło to krok wskazujący bazę. Microsoft wymienia tę metodę między innymi dla SQL Server i PostgreSQL, ale nie dla MySQL.

Podgląd tego, co naprawdę trafia do bazy, daje polecenie Wyświetl zapytanie natywne (View Native Query) w menu kroku. Nie każde złącze je udostępnia, a wskaźniki składania i plan zapytania są według Microsoftu dostępne tylko w Power Query Online.

Odświeżanie: przy otwarciu pliku, co kilka minut i w tle

Dane w arkuszu nie zmieniają się same: widzisz stan z ostatniego odświeżenia. Zasady odświeżania ustawiasz osobno dla każdego zapytania, we właściwościach połączenia.

Otwórz Dane > Zapytania i połączenia, na karcie Połączenia kliknij zapytanie prawym przyciskiem i wybierz Właściwości. Na karcie Użycie, w części Sterowanie odświeżaniem, są trzy najważniejsze ustawienia.

• Odśwież dane podczas otwierania plikuKażde otwarcie skoroszytu pobiera aktualne dane. Jeśli połączenia danych zewnętrznych są na komputerze wyłączone, trzeba je włączyć w Centrum zaufania albo zapisać plik w zaufanej lokalizacji.
• Odśwież coExcel powtarza zapytanie co wskazaną liczbę minut, dopóki plik jest otwarty. Przy wielu użytkownikach mnoży to liczbę zapytań do bazy.
• Włącz odświeżanie w tleMożesz dalej pracować w arkuszu, zanim zapytanie się skończy. Microsoft zaznacza jednak, że w tle nie uruchomisz zapytania OLAP ani zapytania, które pobiera dane do modelu danych.

Poświadczenia, limit czasu i ręczne odświeżanie

Trzy sprawy z dokumentacji Microsoftu, które decydują o tym, czy plik z połączeniem działa bezpiecznie: hasła, limit czasu i odświeżanie.

Według Microsoftu Power Query szyfruje poświadczenia i przechowuje je oddzielnie od modelu danych. Zobaczysz je, zmienisz albo usuniesz w Dane > Pobierz dane > Ustawienia źródła danych, gdzie obok źródeł bieżącego skoroszytu są uprawnienia globalne (Global permissions), wspólne dla wszystkich Twoich plików.

Ostrzeżenie Microsoftu, że zapisane hasła nie są szyfrowane, dotyczy hasła w klasycznej definicji połączenia (karta Definicja we właściwościach), jaką tworzą starsze kreatory, na przykład Microsoft Query, a na Macu droga przez ODBC. Wyczyść tam pole Zapisz hasło, a Excel zapyta o nie przy pierwszym odświeżeniu w każdej sesji programu. W firmie z domeną Windows wygodniej logować się kontem Windows: w pliku nie ma wtedy hasła do bazy, a dostęp nadajesz grupie.

Według dokumentacji złączy SQL Server, PostgreSQL i MySQL domyślny limit czasu polecenia wynosi 10 minut. Zmienisz go w opcjach zaawansowanych złącza (Command timeout in minutes), ale najpierw sprawdź, czy agregacji nie da się przenieść do widoku albo widoku materializowanego, żeby Excel dostawał gotowy wynik.

Ręcznie odświeżysz zaznaczone zapytanie skrótem Alt+F5, a wszystkie połączenia w skoroszycie skrótem Ctrl+Alt+F5. Trwające odświeżanie przerwiesz klawiszem Esc, a odświeżanie w tle zatrzymasz, klikając dwukrotnie komunikat na pasku stanu i wybierając Zatrzymaj odświeżanie (Stop Refresh).

Pułapka w edytorze Power Query: odświeżenie podglądu pobiera nowe dane do lokalnej pamięci podręcznej, ale według Microsoftu nie aktualizuje tabeli w arkuszu ani modelu danych. To zrobi dopiero odświeżenie zapytania w samym Excelu.

Plik dla kilku osób: dostęp, poświadczenia i poziomy prywatności

Plik z połączeniem do bazy, który działa u autora, u innej osoby może skończyć się błędem odświeżania: według Microsoftu odbiorca skoroszytu może potrzebować własnego dostępu do źródła.

Przed udostępnieniem zdecyduj więc: albo każda osoba dostaje własne konto do odczytu, albo przy logowaniu kontem Windows prawo do widoku dostaje grupa w domenie.

Poświadczenia i adres źródła zmienisz w Dane > Pobierz dane > Ustawienia źródła danych (Data Source Settings). Tam też przepniesz plik z bazy testowej na replikę bez budowania zapytań od nowa. Uważaj na polecenie czyszczenia uprawnień: Microsoft ostrzega, że wyczyszczonych uprawnień nie da się przywrócić.

Poziomy prywatności (None, Private, Organizational, Public) liczą się, gdy łączysz dane z bazy z innym źródłem, na przykład z listą klientów wpisaną w arkuszu. Power Query nie połączy danych, jeśli mogłoby to przesłać je ze źródła bardziej chronionego do mniej chronionego: według przykładu Microsoftu scalenie prywatnej tabeli ze skoroszytu z organizacyjną tabelą SQL Server zostanie zablokowane. Dokumentacja ostrzega też, że źle ustawione poziomy mogą doprowadzić do wycieku wrażliwych danych, więc opcji ich ignorowania nie włączaj w plikach, które wychodzą poza Twój komputer.

Mac, Excel w przeglądarce i limity

To, czy połączysz się z bazą, zależy od wersji Excela bardziej niż od samej bazy. Tabela niżej zbiera to, co Microsoft podaje na stronie o źródłach danych Power Query w wersjach Excela.

Na Macu Power Query jest dostępny dla subskrybentów Microsoft 365, a edytor zapytań działa od wersji 16.69. W tabeli Microsoftu SQL Server figuruje dla Maca tylko jako źródło, które można odświeżać, a nowe źródło SQL Server zaimportujesz tam wyłącznie w wersji beta programu Insider. Zapytanie zbudowane na Windowsie odświeżysz więc na Macu, ale nowe połączenie w zwykłej wersji utworzysz tylko starszą drogą: poleceniem From SQL Server ODBC albo przez Microsoft Query ze sterownikiem ODBC.

Excel w przeglądarce według tej samej strony obsługuje SQL Server i PostgreSQL z logowaniem nazwą użytkownika i hasłem, ale nie odświeży zapytań załadowanych do modelu danych ani źródła, które wymaga lokalnej bramy danych, czyli według dokumentacji Power BI bazy niedostępnej bezpośrednio z internetu. Odświeżanie w przeglądarce ma limit 1000 połączeń na użytkownika.

Arkusz mieści 1 048 576 wierszy i 16 384 kolumny; większy wynik zagreguj w widoku albo załaduj do modelu danych. W 64-bitowym Excelu rozmiar pliku ogranicza tylko dostępna pamięć, a w 32-bitowym Excel, skoroszyt i dodatki dzielą 2 GB przestrzeni adresowej, z czego na model danych przypada według Microsoftu do 500–⁠700 MB, czasem mniej.

Połączenie z bazą SQL w wersjach Excela według tabeli Microsoftu

Tabela jest szersza niż ekran: przesuń ją palcem w bok.

Kiedy Excel przestaje wystarczać

Excel z Power Query odświeża dane tylko w otwartym pliku i mieści ograniczoną liczbę wierszy. Te i trzy inne sygnały mówią, że czas na inne narzędzie.

• Dane mają być gotowe, zanim ktokolwiek otworzy plikExcel odświeża dane tylko w otwartym pliku. Odświeżanie według harmonogramu daje usługa w chmurze, na przykład Power BI, która z bazą w sieci firmowej łączy się przez lokalną bramę danych instalowaną na komputerze z Windows; według Microsoftu brama używa połączeń wychodzących, więc nie trzeba otwierać portów przychodzących. Porównanie znajdziesz w przewodniku Looker Studio czy Power BI.
• Liczba ma przychodzić sama, mailem albo w plikuTakie zestawienie najlepiej liczyć w samej bazie według harmonogramu, a Excel może być jednym z odbiorców. Jak to zaplanować w SQL Server, Azure SQL Database i PostgreSQL, pokazuje sekcja o harmonogramie raportu.
• Wynik nie mieści się w arkuszuPrzy ponad milionie wierszy do Excela powinno trafiać podsumowanie z widoku. Model danych przesuwa limit, ale przy każdym odświeżeniu i tak pobiera wszystkie wiersze z bazy.
• Dane mają widzieć osoby spoza firmyKlient albo partner nie powinien dostawać konta w Twojej bazie tylko po to, żeby odświeżyć arkusz. Lepszy jest raport w pliku albo panel z własnymi uprawnieniami; takie wdrożenia opisuje strona usługi analityka danych.
• Arkusz ma zapisywać zmiany w baziePower Query tylko pobiera i przekształca dane. Formularz, z którego zmiany trafiają do bazy, to zadanie dla aplikacji webowej z kontrolą uprawnień i walidacją, a nie dla natywnego zapytania na koncie z prawem zapisu.

Pytania i odpowiedzi