Aplikacje do raportowania - rodzaje narzędzi i kryteria wyboru

Aplikacja do raportowania zamienia rekordy z bazy danych w zestawienie, które da się przeczytać albo wydrukować. Różnice między takimi aplikacjami wynikają z trzech pytań: skąd biorą dane, jak często je odświeżają i kto może zmienić układ raportu bez udziału programisty.

Ten tekst dzieli narzędzia na trzy klasy. Pierwsza to raporty wbudowane w aplikację, druga to serwer raportów, trzecia to hurtownia danych z narzędziem BI. Szczegóły pracy w edytorze opisuje osobny artykuł o SQL Report Builder i plikach RDL, a kwestie serwera - tekst o subskrypcjach w Reporting Services. Tutaj chodzi o wybór rozwiązania.

Aplikacje do raportowania i cztery warstwy pracy z danymi

Każde rozwiązanie da się rozłożyć na cztery warstwy. Pierwsza to źródło danych, czyli baza transakcyjna aplikacji albo jej kopia. Druga to model, który nadaje danym sens biznesowy: łączy tabele i liczy wskaźniki. Trzecia warstwa układa wynik na ekranie lub na stronie, a czwarta dostarcza go odbiorcy.

Wybór aplikacji sprowadza się do pytania, które warstwy obsłuży jedno narzędzie, a które zostaną rozdzielone. Raport wbudowany w aplikację transakcyjną łączy wszystkie cztery w jednym module. Hurtownia rozdziela je na osobne systemy, co daje elastyczność, ale podnosi koszt utrzymania.

Reguła: narzędzie wybiera się po wymaganiu wobec świeżości danych, a nie po wyglądzie kokpitu.

Raporty wbudowane w aplikację transakcyjną

Najprostszy wariant polega na raporcie zaszytym w samej aplikacji: ekran z filtrami i przycisk generowania, po którym pojawia się tabela wynikowa. Programista pisze zapytanie lub procedurę składowaną, a aplikacja wywołuje ją z parametrami wybranymi przez użytkownika. Wynik pokazuje stan sprzed sekundy, bo raport czyta te same tabele, do których zapisuje reszta systemu.

Cena tej prostoty to obciążenie bazy transakcyjnej. Zapytanie agregujące miliony wierszy konkuruje z operacjami zapisu o zasoby i blokady. Przy domyślnym poziomie izolacji READ COMMITTED czytelnik czeka na zapisy, które nie zostały jeszcze zatwierdzone. Po włączeniu opcji READ_COMMITTED_SNAPSHOT odczyt korzysta z wersji wierszy przechowywanych w magazynie wersji, więc nie blokuje pisarzy, ale zwiększa obciążenie tego magazynu. Mechanizm opisuje przewodnik Microsoft Learn o blokowaniu i wersjonowaniu wierszy.

ALTER DATABASE MagazynPrzyklad
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Reguła: raport na bazie transakcyjnej czyta widok, a nie tabele źródłowe, żeby zmiana schematu nie zatrzymała ekranu.

Dobrą praktyką jest oddzielenie logiki raportu od ekranu. Poniższy przykład tworzy widok z dziennymi obrotami i procedurę, która zwraca z niego wycinek dla wybranego okresu. Nazwy obiektów są przykładowe i nie odpowiadają schematowi żadnego produktu.

CREATE VIEW rap.ObrotyDzienne
AS
SELECT d.DataDokumentu,
       k.Nazwa AS Kontrahent,
       COUNT(*) AS LiczbaDokumentow,
       SUM(d.Wartosc) AS Wartosc
FROM dbo.Dokument AS d
JOIN dbo.Kontrahent AS k ON k.KontrahentId = d.KontrahentId
GROUP BY d.DataDokumentu, k.Nazwa;
GO

CREATE PROCEDURE rap.ObrotyZaOkres
    @Od date,
    @Do date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT DataDokumentu, Kontrahent, LiczbaDokumentow, Wartosc
    FROM rap.ObrotyDzienne
    WHERE DataDokumentu >= @Od AND DataDokumentu < DATEADD(DAY, 1, @Do)
    ORDER BY DataDokumentu, Kontrahent;
END;

Zmiana definicji wskaźnika wymaga wtedy edycji jednego obiektu w bazie, a nie poprawiania każdego ekranu. Ta sama procedura może zasilić ekran aplikacji oraz wydruk. Tak działa raportowanie w systemach magazynowych, które opisuje artykuł o wskaźnikach w systemie WMS, i w module reklamacji, gdzie liczą się terminy i raporty reklamacyjne.

Serwer raportów i raporty stronicowane

Serwer raportów oddziela definicję raportu od aplikacji. W SQL Server Reporting Services raport jest plikiem RDL, który opisuje źródło danych z zapytaniem oraz parametry i układ strony. Serwer wykonuje go na żądanie albo według harmonogramu i zwraca wynik w wybranym formacie. Rodzaje raportów opisuje dokumentacja Microsoft Learn o raportach mobilnych i stronicowanych.

Raport stronicowany jest przeznaczony do dokumentów o stałym układzie: listów przewozowych, protokołów, zestawień do druku. Serwer eksportuje go do PDF lub Excela bez zmiany definicji, a formaty pomocnicze opisuje sekcja o eksporcie. Autor korzysta z edytora Report Builder, który opisuje strona Microsoft Learn o Report Builder, a artykuł o Microsoft SQL Reporting Services wyjaśnia samą platformę.

Wersja serwerowa dodaje funkcje, których raport w aplikacji nie ma. Subskrypcja wysyła gotowy plik e-mailem o zadanej godzinie. Migawka zapisuje wynik wykonania, więc kolejne otwarcia nie obciążają bazy. Foldery i role ograniczają widoczność raportów. Przykład zastosowania serwera w magazynie pokazuje strona o serwerze raportów w platformie WMS.

Uwaga techniczna: raport uruchamiany z subskrypcji zwykle nie zna odbiorcy jako użytkownika bazy, więc zawężenie danych do jego oddziału trzeba przekazać jako parametr subskrypcji.

Hurtownia danych i analiza wielowymiarowa

Hurtownia danych ma sens wtedy, gdy pytania biznesowe wychodzą poza jedną aplikację albo wymagają historii, której baza transakcyjna nie przechowuje. Sprzedaż porównana z zapasami i reklamacjami pochodzi z kilku systemów, a każdy z nich ma inne klucze i inny rytm zapisu. Hurtownia ładuje te dane cyklicznie do wspólnego modelu, na przykład zadaniem SQL Agent uruchamianym co noc.

Typowy jest model zwany schematem gwiazdy. Tabela faktów przechowuje zdarzenia z miarami liczbowymi, a wokół niej leżą wymiary, na przykład czas i produkt. Indeks klastrowy columnstore kompresuje takie tabele i przyspiesza agregacje po milionach wierszy. Zasady jego działania wyjaśnia przegląd indeksów columnstore w Microsoft Learn.

CREATE TABLE dwh.FaktSprzedazy
(
    DataId    int           NOT NULL,
    ProduktId int           NOT NULL,
    KlientId  int           NOT NULL,
    Ilosc     decimal(18,3) NOT NULL,
    Wartosc   decimal(18,2) NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FaktSprzedazy
    ON dwh.FaktSprzedazy;

Ładowanie przyrostowe unika przeliczania całej historii. Proces zapamiętuje ostatni przeniesiony identyfikator i przy kolejnym uruchomieniu bierze tylko nowsze wiersze.

DECLARE @OstatniId bigint = 0;  -- odczytywany z tabeli sterującej

INSERT INTO dwh.FaktSprzedazy (DataId, ProduktId, KlientId, Ilosc, Wartosc)
SELECT CONVERT(int, FORMAT(d.DataDokumentu, 'yyyyMMdd')),
       p.ProduktId, d.KontrahentId, p.Ilosc, p.Wartosc
FROM dbo.PozycjaDokumentu AS p
JOIN dbo.Dokument AS d ON d.DokumentId = p.DokumentId
WHERE p.PozycjaId > @OstatniId;

Reguła: hurtownia odpowiada na pytania o przeszłość; stan bieżący nadal czyta się z systemu transakcyjnego.

Nad hurtownią pracują narzędzia BI, takie jak Power BI czy Tableau, z których analityk buduje kokpity bez pisania zapytań. Przykład wymiarów widzianych od strony użytkownika daje tekst o Business Intelligence w enova365, a przykład raportowania w systemie ERP - opis raportowania HermesSQL. Oba pochodzą od innych producentów niż SoftwareStudio i pokazują tylko sposób myślenia o wymiarach.

Kryteria wyboru aplikacji do raportowania

Porównanie trzech podejść najłatwiej przeprowadzić na wspólnych kryteriach. Tabela zestawia je tak, jak wynikają z architektury, bez oceny jakości konkretnych produktów.

Na decyzję wpływają cztery parametry, które warto zapisać przed rozmową z dostawcą.

• Świeżość danych - operator magazynu potrzebuje stanu z tej minuty, a zarząd zwykle wystarczy stan z poranka.
• Liczba źródeł - jedna baza obsłuży widok SQL, a kilka systemów wymaga wspólnego modelu w hurtowni.
• Autor raportów - zmiany układu robione przez użytkowników biznesowych przemawiają za serwerem raportów albo narzędziem BI.
• Wolumen i historia - kilkadziesiąt milionów wierszy w agregacjach uzasadnia columnstore, a kilka tysięcy dokumentów miesięcznie nie.

Często wystarcza rozwiązanie pośrednie: widok raportowy w bazie i raport RDL na serwerze. Kiedy pytanie brzmi tylko, czy zapytanie jednorazowe wystarczy, pomaga tekst o raportowaniu SQL i granicach zapytania doraźnego. Gdy odbiorcy chcą zadawać pytania własnymi słowami, wchodzi w grę asystent do raportów SQL, który tłumaczy opis potrzeby na zapytanie.

Eksport i formaty dostarczania raportów

Format wyjściowy wynika z tego, co odbiorca zrobi z raportem. PDF zachowuje układ strony i nadaje się do archiwum oraz do podpisu, ale nie pozwala na dalszą analizę. Excel przyjmuje dane do własnych obliczeń, więc bywa wybierany także tam, gdzie raport miał służyć tylko do odczytu, i wtedy powstają kopie rozjeżdżające się z bazą.

Plik CSV do wymiany z innym systemem wymaga uzgodnienia kodowania i separatora. Polskie ustawienia regionalne Excela oczekują średnika jako separatora pól i przecinka dziesiętnego, a plik w UTF-8 bez znacznika BOM otwiera się po dwukrotnym kliknięciu z zepsutymi polskimi znakami. Te szczegóły rozstrzygają o tym, czy eksport nadaje się do automatycznego importu.

Dostarczanie na urządzenia mobilne stawia dodatkowe wymagania. Raport z wąską tabelą czyta się na ekranie terminala, a szeroka siatka wymaga przewijania w poziomie. Dla odbiorców w terenie lepszy jest raport z kilkoma wskaźnikami niż pełne zestawienie.

Reguła: jeden raport obsługuje jednego odbiorcę i jedną decyzję; zestawienie, którego nikt nie otwiera, wyłącza się z harmonogramu.

Uprawnienia i bezpieczeństwo danych w raportach

Raport widzi tyle, na ile pozwala konto źródła danych. Jeżeli serwer łączy się z bazą kontem o szerokich prawach, każdy użytkownik z dostępem do raportu może pośrednio zobaczyć więcej, niż przewiduje organizacja. Konto raportowe powinno mieć prawo odczytu wyłącznie do widoków ze schematu raportowego. Zasady nadawania uprawnień opisuje wprowadzenie do uprawnień silnika bazy danych.

Gdy raport ma pokazywać różnym osobom różne wiersze, sprawdza się zabezpieczenie na poziomie wierszy. Funkcja predykatu ogranicza wyniki do oddziału zapisanego w kontekście sesji, a polityka bezpieczeństwa dołącza ją do tabeli. Aplikacja ustawia kontekst przy otwarciu połączenia, a serwer bazy pilnuje reguły niezależnie od tego, które narzędzie wysłało zapytanie. Funkcję opisuje dokumentacja o zabezpieczeniach na poziomie wierszy.

CREATE FUNCTION rap.FiltrOddzialu(@OddzialId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Dozwolone
       WHERE @OddzialId = CAST(SESSION_CONTEXT(N'OddzialId') AS int);
GO

CREATE SECURITY POLICY rap.PolitykaOddzialu
ADD FILTER PREDICATE rap.FiltrOddzialu(OddzialId) ON dbo.Dokument
WITH (STATE = ON);

Osobnej uwagi wymagają raporty z danymi osobowymi, na przykład ewidencja czasu pracy albo dane kontrahentów będących osobami fizycznymi. Ich eksport do plików i wysyłka e-mailem powinny mieć jawnego właściciela, a subskrypcje - ograniczoną listę adresatów.

Utrzymanie raportów po wdrożeniu

Raport uruchomiony raz przy odbiorze działa inaczej niż raport uruchamiany setki razy w miesiącu na rosnącej bazie. Serwer Reporting Services zapisuje każde wykonanie w widoku ExecutionLog3 w bazie serwera raportów. Widok zawiera osobne czasy pobrania danych i renderowania raportu, więc od razu widać, czy wolne jest zapytanie, czy układ strony. Budowę opisuje strona o widoku ExecutionLog3.

SELECT TOP (20) ItemPath, TimeStart,
       TimeDataRetrieval, TimeProcessing, TimeRendering
FROM ReportServer.dbo.ExecutionLog3
WHERE TimeStart >= DATEADD(DAY, -7, SYSDATETIME())
ORDER BY TimeDataRetrieval DESC;

Gdy dominuje czas pobrania danych, trzeba sprawdzić plan wykonania zapytania. Typowe przyczyny to brak indeksu pokrywającego filtr i kolumny wyniku albo funkcja na kolumnie w warunku WHERE, która uniemożliwia wyszukiwanie po indeksie. Raporty z filtrem po dacie zyskują na indeksie nieklastrowym z kolumnami dołączonymi.

Pliki RDL warto trzymać w repozytorium kodu obok skryptów bazy. Wtedy zmiana układu ma autora i datę, a wycofanie błędnej wersji zajmuje minuty. Po każdej zmianie schematu bazy trzeba uruchomić raporty testowe, bo zmieniona nazwa kolumny zatrzymuje raport dopiero w chwili wykonania.

Gdy zakres wykracza poza gotowe narzędzia, wykonanie raportu lub widoku można zlecić zespołowi programistów; ofertę opisują usługi SoftwareStudio. Sam wybór architektury warto jednak zamknąć przed pierwszym projektem, bo późniejsza zmiana warstwy źródłowej oznacza przepisanie zestawów danych. Zestawienie narzędzi jednego producenta z edytorem raportów zawiera artykuł SQL Report Builder i Reporting Services.