Raportowanie SQL - od zapytania T-SQL do serwera raportów

Raport z bazy danych powstaje na jeden z dwóch sposobów. Analityk pisze zapytanie w T-SQL i wkleja wynik do arkusza albo serwer buduje zestawienie sam, według zapisanej definicji. Pytanie z tytułu dotyczy granicy między tymi sposobami: kiedy wystarczy dobrze napisane zapytanie, a kiedy potrzebny jest osobny mechanizm raportowania.

Artykuł opisuje trzy poziomy raportowania SQL. Pierwszy to zapytanie T-SQL, drugi to widok lub procedura składowana, a trzeci to serwer raportów, czyli w praktyce SQL Server Reporting Services (SSRS). Dla każdego poziomu podano koszt utrzymania oraz wpływ na bazę, z której korzysta magazyn albo dział księgowości.

Raportowanie SQL a zapytanie doraźne

Zapytanie doraźne odpowiada na jedno pytanie i po użyciu znika w historii edytora. Raport ma właściciela i parametry, a do tego harmonogram oraz numer wersji, więc za miesiąc da się go uruchomić z takim samym układem kolumn. Różnica jest organizacyjna, a nie techniczna: ten sam SELECT może być zapytaniem doraźnym albo elementem raportu.

Zestawienie poniżej porządkuje cztery sposoby dostarczania danych według tego, kto je uruchamia i gdzie leży definicja.

Reguła: raport uruchamiany co miesiąc wymaga zapisanej definicji oraz przypisanego właściciela, a parametry muszą mieć określone typy. Plik z zapytaniem na pulpicie analityka tego nie zapewnia.

Pierwszy sygnał, że zapytanie przestało wystarczać, to pytanie „które zapytanie było prawidłowe”. Gdy dwie osoby liczą przychód miesiąca dwoma różnymi plikami i dostają różne kwoty, brakuje jednej wspólnej definicji. Widok albo procedura w bazie rozwiązują ten problem, bo logika filtrowania siedzi w jednym miejscu.

Definicje widoków i procedur oraz pliki RDL warto trzymać w repozytorium kodu, tak jak kod aplikacji. Każda zmiana ma wtedy autora i datę, a do tego opis, a wdrożenie na środowisko produkcyjne odbywa się skryptem. Bez tego po roku nikt nie wie, dlaczego suma w raporcie zmieniła się o kilka procent.

Zapytania T-SQL w raportach operacyjnych

Raport operacyjny odpowiada na pytanie o stan bieżący albo o krótki okres wstecz, na przykład o wydania z ostatniego tygodnia. Podstawą jest składnia opisana w dokumentacji klauzuli GROUP BY w Transact-SQL: agregat liczony po grupach wierszy z jednej lub wielu tabel. Wyniki służą kierownikowi zmiany, więc czas odpowiedzi liczy się w sekundach.

Agregacje i złączenia

Typowy raport łączy nagłówek dokumentu z pozycjami i słownikiem kontrahentów, a potem sumuje wartość po miesiącu. Najczęstszy błąd polega na zsumowaniu wartości z nagłówka po złączeniu z pozycjami: nagłówek powtarza się tyle razy, ile ma pozycji, i suma rośnie. Wartości z poziomu nagłówka sumuje się przed złączeniem albo w osobnym zapytaniu.

-- przykładowy schemat, nie struktura konkretnego produktu
CREATE VIEW dbo.v_ObrotyMiesieczne AS
SELECT YEAR(d.DataWystawienia)  AS Rok,
       MONTH(d.DataWystawienia) AS Miesiac,
       k.Nazwa                  AS Kontrahent,
       SUM(p.Ilosc * p.CenaNetto) AS WartoscNetto
FROM dbo.Dokument AS d
JOIN dbo.Pozycja AS p ON p.DokumentId = d.Id
JOIN dbo.Kontrahent AS k ON k.Id = d.KontrahentId
WHERE d.Typ = 'WZ'
GROUP BY YEAR(d.DataWystawienia), MONTH(d.DataWystawienia), k.Nazwa;

Zakres dat wymaga osobnej uwagi. Warunek BETWEEN @Od AND @Do na kolumnie z czasem pomija dokumenty z ostatniego dnia po godzinie 00:00, bo data końcowa oznacza północ. Bezpieczniejszy jest przedział półotwarty: >= @Od AND < DATEADD(DAY, 1, @Do). Ten zapis działa tak samo dla typów datetime, datetime2 i date.

Widoki i procedury składowane

Widok jest zapisanym zapytaniem SELECT, więc nie przyjmuje parametrów, a filtr nakłada narzędzie odczytujące. Procedura składowana przyjmuje parametry z typami, dzięki czemu plan wykonania odpowiada konkretnym wartościom, a uprawnienie EXECUTE można nadać bez dostępu do tabel. Opis widoków w bazie znajduje się w dokumentacji Microsoft Learn o widokach.

CREATE PROCEDURE dbo.rpt_ObrotyKontrahenta
    @Od date,
    @Do date,
    @KontrahentId int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT k.Nazwa, SUM(p.Ilosc * p.CenaNetto) AS WartoscNetto
    FROM dbo.Dokument AS d
    JOIN dbo.Pozycja AS p ON p.DokumentId = d.Id
    JOIN dbo.Kontrahent AS k ON k.Id = d.KontrahentId
    WHERE d.DataWystawienia >= @Od
      AND d.DataWystawienia <  DATEADD(DAY, 1, @Do)
      AND (@KontrahentId IS NULL OR d.KontrahentId = @KontrahentId)
    GROUP BY k.Nazwa
    OPTION (RECOMPILE);
END;

Klauzula OPTION (RECOMPILE) ma sens przy parametrze opcjonalnym, bo jeden plan wykonania nie pasuje jednocześnie do wersji z kontrahentem i bez niego. Kosztem jest kompilacja przy każdym wywołaniu, więc przy raportach uruchamianych kilka razy dziennie jest to zaniedbywalne. Przy zapytaniach wywoływanych setki razy na minutę trzeba to ocenić osobno.

Obciążenie bazy przez raporty

Raport, który czyta kilkaset tysięcy wierszy, konkuruje o zasoby z transakcjami użytkowników. W domyślnym trybie READ COMMITTED czytelnik zakłada blokady współdzielone, więc czeka na zatwierdzenie zmian, a piszący czeka na zwolnienie blokad odczytu. Przy długim raporcie kończy się to skargami, że magazyn „zawiesza się o dziesiątej”. Mechanizmy blokad opisuje przewodnik po blokowaniu transakcji i wersjonowaniu wierszy.

Reguła: podpowiedź NOLOCK skraca czas raportu, ale zwraca dane, które mogą zostać wycofane. Do zestawień finansowych i rozliczeniowych się jej nie stosuje.

Poziom READ COMMITTED SNAPSHOT włącza się na poziomie bazy i po włączeniu obowiązuje wszystkie zapytania w domyślnym trybie, bez zmian w kodzie aplikacji. Wersje wierszy trafiają do tempdb, a każdy zmieniany wiersz dostaje kilkanaście dodatkowych bajtów znacznika. Zmiana wymaga chwili bez innych połączeń z bazą, więc wykonuje się ją w oknie serwisowym. Dokładny opis opcji znajduje się w dokumentacji SET TRANSACTION ISOLATION LEVEL.

ALTER DATABASE MagazynPrzyklad
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

Indeksy pod raporty

Raport filtrujący po typie dokumentu i dacie korzysta z indeksu nieklastrowego, który zawiera obie kolumny w kluczu. Kolumny tylko czytane, jak identyfikator kontrahenta, dopisuje się przez INCLUDE, dzięki czemu zapytanie nie wraca do tabeli po dodatkowe dane. Indeks filtrowany, dostępny od wersji 2008, obejmuje tylko wybrany podzbiór wierszy, na przykład dokumenty otwarte.

CREATE NONCLUSTERED INDEX IX_Dokument_Typ_Data
ON dbo.Dokument (Typ, DataWystawienia)
INCLUDE (KontrahentId);

CREATE NONCLUSTERED INDEX IX_Dokument_Otwarte
ON dbo.Dokument (DataWystawienia)
WHERE Status = 0;

Każdy dodatkowy indeks spowalnia zapis, bo aplikacja musi go uaktualnić przy każdej zmianie dokumentu. Liczbę indeksów pod raporty ustala się więc na podstawie zapytań uruchamianych naprawdę często. Ogólne zasady projektowania opisuje dokumentacja indeksów SQL Server, a projektowanie schematów pod aplikacje biznesowe omawia strona o programowaniu baz danych.

Kopia raportowa bazy

Gdy raporty analityczne obciążają serwer produkcyjny mimo indeksów, przenosi się je na kopię bazy. Kopię można odtwarzać z kopii zapasowej raz na dobę albo zasilać dziennikiem transakcji w regularnych odstępach. Cena to opóźnienie danych: raport z kopii pokazuje stan sprzed kilkunastu minut albo poprzedniej nocy.

Taki układ nadaje się do zestawień miesięcznych i rozliczeń, a nie do ekranu kierownika zmiany, który potrzebuje stanu z ostatniej minuty. Raporty operacyjne zostają więc na bazie produkcyjnej z włączonym wersjonowaniem wierszy. Analizy wielomiesięczne przenosi się na kopię i uruchamia nocą.

Serwer raportów zamiast plików z zapytaniami

Serwer raportów dodaje do zapytania to, czego baza nie przechowuje: układ strony, formularz parametrów, eksport do PDF i Excela oraz kontrolę dostępu. Definicję raportu zapisuje się w formacie RDL (Report Definition Language), czyli w pliku XML z zestawami danych i parametrami oraz opisem układu. Przegląd usług opisuje dokumentacja SQL Server Reporting Services.

Raporty projektuje się graficznie w programie Report Builder albo w SQL Server Data Tools. Projektant przeciąga tabele i wykresy na powierzchnię raportu, a plik RDL powstaje w tle. Odbiorca otwiera raport w przeglądarce i wybiera parametry z listy, bez znajomości SQL. Pełny opis narzędzia do projektowania wydruków zawiera strona o SQL Report Builder i raportach RDL.

Serwer wykonuje raport na żądanie albo według harmonogramu i zapisuje wynik w wybranym formacie. Subskrypcje pozwalają wysłać plik e-mailem lub zapisać go w udziale sieciowym bez udziału użytkownika. Architekturę serwera i jego bazy opisuje osobny tekst o serwerze raportów, a rozszerzone zastosowania, takie jak raporty mobilne, znajdują się w artykule o SSRS i kwerendach SQL Server.

Harmonogramy subskrypcji SSRS realizują zadania agenta SQL Server, więc usługa SQL Server Agent musi działać na serwerze raportów. Awaria agenta objawia się tym, że raporty przestają się wysyłać, choć ręczne uruchomienie w przeglądarce działa. Przy diagnozie najpierw sprawdza się stan usługi agenta, a dopiero potem definicję subskrypcji.

Osobny kierunek stanowią narzędzia analityczne z wykresami interaktywnymi. Serwer raportów pozostaje wtedy źródłem zestawień tabelarycznych i wydruków, a analiza eksploracyjna przechodzi do osobnego narzędzia. Rozróżnienie tych warstw omawia artykuł o Microsoft SQL Reporting Services.

Studio Raporty.net - menu i uprawnienia

Studio Raporty.net jest aplikacją SoftwareStudio do udostępniania zestawień z baz SQL. Po zalogowaniu użytkownik widzi menu raportów. Układ i zawartość menu definiuje administrator, a każdy widzi tylko raporty przypisane do swojej roli, co ogranicza ryzyko błędnego użycia danych z innego działu.

• Menu raportów - administrator definiuje sekcje i przypisuje raporty do pozycji menu.
• Źródła danych - system pozwala zdefiniować wiele źródeł, dzięki czemu zestawienia łączą dane z różnych systemów w jednym miejscu.
• Uprawnienia - lista użytkowników określa, kto może się logować i które zestawienia otwiera.
• Ustawienia - część ustawień jest globalna, a część zapisuje się indywidualnie dla użytkownika.

Wygląd zestawienia konfiguruje się przez skorowidze, takie jak typy kolumn czy kolory tła. Osobny skorowidz obejmuje funkcje agregujące. Administrator ustawia układ kolumn i ich wyrównanie oraz filtry, a mechanizmy filtrowania i agregacji pozwalają wyciągać wnioski z dużych zbiorów bez pisania zapytań. Dla zestawień z konkretnego ERP przykład opisuje strona o raportowaniu HermesSQL w Studio Raporty.

Raporty na urządzeniach mobilnych

Raporty przeglądane na smartfonie albo tablecie wymagają układu, który skaluje się do małego ekranu. Aplikacja SQL RAPORTY.net udostępnia raporty w przeglądarce niezależnie od urządzenia, a użytkownik może porządkować je w folderach i przekazywać wybranym osobom. Dzięki temu administrator nadal decyduje, kto i kiedy przegląda dane.

Raportowanie SQL w magazynie i serwisie

W systemach magazynowych typowe zestawienia dotyczą stanu na dzień oraz rotacji, a osobno czasu realizacji zleceń. Liczy się je z tabel ruchów, więc jakość raportu zależy od kompletności zapisu, a nie od narzędzia raportowego. Wskaźniki i sposób ich obliczania omawia artykuł o raportowaniu w systemie WMS z użyciem KPI i SSRS.

Podobny mechanizm działa w obsłudze serwisowej: zgłoszenia i statusy leżą w tabelach razem z terminami, a raport pokazuje otwarte sprawy i czas ich obsługi. Oprogramowanie do zgłoszeń opisuje strona o programie do obsługi serwisu. Gdy potrzebne jest zestawienie pisane przez asystenta, opcje opisuje tekst o asystencie do raportów SQL.

Kryteria wyboru poziomu raportowania

Wybór między zapytaniem a serwerem raportów sprowadza się do czterech pytań o skalę i odbiorców. Odpowiedzi ustalają, ile pracy administracyjnej zaakceptuje organizacja.

• Częstotliwość - raport uruchamiany częściej niż raz w miesiącu zasługuje na zapisaną definicję i harmonogram.
• Odbiorcy - jeśli dane czyta więcej niż kilka osób, potrzebne są uprawnienia i jedno źródło prawdy.
• Wolumen - zestawienia z milionów wierszy wymagają indeksów, wersjonowania wierszy lub kopii raportowej.
• Forma wyniku - wydruk z układem strony i eksport do PDF wskazują na serwer raportów.

Wnioski dla konkretnej firmy zależą od infrastruktury i liczby użytkowników. Przy wdrożeniu raportowania w systemie opartym na SQL Server pomaga zespół, który zna zarówno bazę, jak i procesy biznesowe, na przykład zespół usług programistycznych SoftwareStudio. Wersję demonstracyjną systemów firmy można obejrzeć pod adresem demo.softwarestudio.com.pl.