Business Intelligence w enova365 - czym analiza BI różni się od raportu

Raport z systemu ERP odpowiada na pytanie o stan rzeczy: które faktury są niezapłacone albo ile towaru leży na stanie. Analiza Business Intelligence odpowiada na pytanie o zmianę i jej przyczynę: dlaczego koszty projektu rosną i w którym miesiącu spadły przychody. Oba narzędzia korzystają z tych samych danych, ale liczą je w inny sposób.

Moduł BI w enova365 opisano tu według materiału SoftwareStudio. Materiał wymienia indywidualne zestawy analiz i własne wskaźniki umieszczane w dowolnym miejscu systemu. Opisuje też analizę kosztów i przychodów oraz automatyczne odświeżanie danych. Pojęcia ogólne, takie jak wymiary i miary oraz agregacja i hurtownia danych, wyjaśniono jako wiedzę o systemach analitycznych, a nie specyfikację modułu.

Business Intelligence w enova365 - raport operacyjny i analiza BI

Raport operacyjny jest widokiem na bieżące dane transakcyjne. Czyta wiersze z tabel, na których pracują użytkownicy, i pokazuje pojedyncze dokumenty. Powstaje na żądanie, a jego wartość polega na dokładności: zestawienie faktur do zapłaty musi zgadzać się co do grosza z rejestrem.

Analiza BI pracuje na zagregowanych danych i służy do porównań. Pytanie brzmi zwykle: jak zmienia się wartość w czasie albo w podziale na kategorie. Wartość analizy polega na możliwości przechodzenia między poziomami, na przykład od sumy roku do miesiąca, a stamtąd do projektu.

Reguła: raport operacyjny ma odpowiadać na pytanie „co jest”, a analiza BI na pytanie „jak i dlaczego się zmienia”. Zestawienie mieszające oba cele nie sprawdza się w żadnym z nich.

Według materiału SoftwareStudio moduł BI pozwala rozbudowywać standardowe wskaźniki albo definiować nowe, zgodnie z potrzebami użytkowników. Wskaźniki i wykresy umieszcza się w dowolnej lokalizacji systemu ERP, dzięki czemu wynik pojawia się tam, gdzie pracuje odbiorca. Szersze omówienie raportów operacyjnych w enova365 zawiera artykuł o raportowaniu i przygotowaniu zestawień.

Wymiary i miary - model analizy

Każda analiza BI składa się z miar i wymiarów. Miara jest liczbą, którą można zsumować lub uśrednić, na przykład kwota netto albo ilość. Wymiar jest przekrojem, po którym miary się grupuje, na przykład czas lub projekt.

Materiał SoftwareStudio opisuje analizę kosztów i przychodów w odniesieniu do projektów oraz dokumentów. Wspomina też o przekroju według użytkowników. W tym języku koszt oraz przychód są miarami, a projekt i dokument wymiarami. Użytkownik jest kolejnym wymiarem. Ta sama miara daje zupełnie inny obraz, zależnie od wybranego przekroju.

Agregacja i ziarno danych

Agregacja zastępuje wiele wierszy jednym wynikiem, na przykład sumą lub średnią. Ziarno danych określa najdrobniejszy poziom, do którego da się zejść. Jeśli dane zapisano na poziomie pozycji dokumentu, można je zsumować do dokumentu lub do projektu. Z sum miesięcznych nie odtworzy się już pozycji, więc ziarno wybiera się przy projektowaniu modelu, a nie później.

Poniższe zapytanie jest przykładem agregacji po dwóch wymiarach z podsumowaniami. Tabele są hipotetyczne i nie odzwierciedlają schematu enova365. Operator ROLLUP dodaje wiersze sum dla projektu i sumy ogólnej, a funkcja GROUPING odróżnia je od zwykłych wierszy:

SELECT p.KodProjektu,
       YEAR(k.DataKsiegowania) AS Rok,
       SUM(k.KwotaNetto)       AS Koszt,
       GROUPING(p.KodProjektu) AS CzySumaOgolna
FROM dbo.KosztPozycja AS k
JOIN dbo.Projekt AS p ON p.ProjektId = k.ProjektId
GROUP BY ROLLUP (p.KodProjektu, YEAR(k.DataKsiegowania))
ORDER BY p.KodProjektu, Rok;

Wynik zawiera koszt każdego projektu w każdym roku, sumę kosztów projektu oraz sumę całości. Ten mechanizm stoi za drążeniem w dół w panelach BI: rozwinięcie wiersza projektu odsłania jego wiersze roczne. Agregaty księgowe, na których opiera się część analiz finansowych, opisuje artykuł o enova365 księgowość.

Wskaźniki własne i porównanie okresów

Wskaźnik jest miarą z ustaloną formułą oraz filtrem, liczoną za wybrany okres. Wskaźnik bezwzględny, na przykład suma kosztów projektu, odpowiada na pytanie o wielkość. Wskaźnik względny, na przykład udział kosztów w przychodach, pozwala porównać projekty różnej skali. Według materiału SoftwareStudio użytkownik może tworzyć własne wskaźniki i śledzić wartości istotne dla celów biznesowych, a dane pochodzą między innymi z obszaru Finanse i księgowość.

Reguła: każdy wskaźnik ma jedną definicję, czyli formułę z filtrem oraz okres. Dwa panele pokazujące „koszty projektu” według różnych formuł prowadzą do sporu o liczby zamiast do decyzji.

Porównanie okresów wymaga zestawienia wartości z sąsiednich miesięcy lub lat. W T-SQL robi to funkcja okna LAG, która czyta wartość z poprzedniego wiersza bez samozłączenia tabeli. Przykład na hipotetycznej tabeli miesięcznych kosztów:

SELECT Rok, Miesiac, Koszt,
       LAG(Koszt) OVER (ORDER BY Rok, Miesiac) AS KosztPoprzedni,
       Koszt - LAG(Koszt) OVER (ORDER BY Rok, Miesiac) AS Zmiana
FROM dbo.KosztMiesieczny
ORDER BY Rok, Miesiac;

W pierwszym miesiącu kolumna KosztPoprzedni ma wartość NULL, więc panel powinien pokazać brak porównania, a nie zero. Zero sugerowałoby, że w poprzednim okresie koszt wynosił dokładnie zero, co byłoby fałszywą informacją. Materiał SoftwareStudio wspomina również o analizie efektywności pracowników w dedykowanych raportach, co w modelu wymiarowym oznacza po prostu wymiar użytkownika.

Wskaźnik względny na przykładzie

Udział kosztów w przychodach projektu liczy się jako iloraz dwóch sum, a nie średnia ilorazów z pojedynczych dokumentów. Różnica ma znaczenie, bo średnia ilorazów traktuje mały dokument tak samo jak duży i zniekształca wynik. Kolejność jest stała: najpierw agregacja licznika i mianownika, potem dzielenie z ochroną przed zerem, którą zapewnia funkcja NULLIF.

SELECT p.KodProjektu,
       k.Koszt / NULLIF(r.Przychod, 0) AS UdzialKosztow
FROM dbo.Projekt AS p
LEFT JOIN (SELECT ProjektId, SUM(KwotaNetto) AS Koszt
           FROM dbo.KosztPozycja GROUP BY ProjektId) AS k ON k.ProjektId = p.ProjektId
LEFT JOIN (SELECT ProjektId, SUM(KwotaNetto) AS Przychod
           FROM dbo.PrzychodPozycja GROUP BY ProjektId) AS r ON r.ProjektId = p.ProjektId;

Podzapytania agregują koszty i przychody osobno, a dopiero potem łączą wyniki. Złączenie obu tabel pozycji bezpośrednio z projektem powieliłoby każdy koszt tyle razy, ile projekt ma pozycji przychodowych, i zawyżyło sumę. Ten błąd łatwo przeoczyć, bo zapytanie działa poprawnie składniowo, a wynik wygląda wiarygodnie.

Odświeżanie danych i obciążenie bazy

Odświeżanie danych określa, jak bardzo analiza odstaje od rzeczywistości. Materiał SoftwareStudio podaje, że system odświeża dane automatycznie, dzięki czemu użytkownik ma aktualne informacje. Technicznie dostępne są trzy warianty, a każdy odpowiada innemu kompromisowi między aktualnością a obciążeniem serwera.

Zapytanie analityczne czyta wiele wierszy i może blokować użytkowników, którzy w tym samym czasie zapisują dokumenty. Domyślny poziom izolacji READ COMMITTED w SQL Server używa blokad współdzielonych, więc długi odczyt stoi w kolejce za zapisem. Włączenie wersjonowania wierszy pozwala czytelnikom widzieć ostatnią zatwierdzoną wersję bez czekania:

ALTER DATABASE BazaPrzyklad
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

Opcja przenosi część obciążenia do bazy tymczasowej tempdb, w której przechowywane są wersje wierszy. Zmiany tej nie wprowadza się bez uzgodnienia z administratorem, ponieważ klauzula ROLLBACK IMMEDIATE przerywa otwarte transakcje. Harmonogramowanie generowania i wysyłki raportów opisuje artykuł o SQL Report Builder, w którym występuje moduł ssJob.

Panel powinien pokazywać datę i godzinę ostatniego odświeżenia. Bez tej informacji odbiorca nie odróżni danych z dzisiejszego poranka od danych sprzed tygodnia, a decyzja oparta na nieaktualnej liczbie wygląda tak samo wiarygodnie jak ta oparta na świeżej. Znacznik czasu przy wskaźniku kosztuje jedną kolumnę w zapytaniu i zapobiega wielu nieporozumieniom.

Hurtownia danych a baza transakcyjna

Baza transakcyjna jest zoptymalizowana pod krótkie zapisy: wystawienie faktury, przyjęcie towaru. Dane są znormalizowane, więc jedna informacja leży w jednym miejscu, a zapytanie analityczne musi łączyć wiele tabel. Hurtownia danych układa dane pod odczyt: centralna tabela faktów z miarami i otaczające ją tabele wymiarów, czyli schemat gwiazdy. Dane trafiają do niej okresowo w procesie ładowania, na przykład nocą.

Opis modelowania w schemacie gwiazdy zawiera dokumentacja schematu gwiazdy w Microsoft Power BI. Tabele faktów o milionach wierszy korzystają z indeksów kolumnowych, które przechowują dane kolumnami i silnie je kompresują. Ich opis znajduje się w dokumentacji indeksów kolumnowych w SQL Server. Przykład dla hipotetycznej tabeli faktów:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FaktKoszt
ON dbo.FaktKoszt;

Hurtownia nie zawsze jest potrzebna. Przy kilku tysiącach dokumentów miesięcznie wystarcza dobrze zaindeksowana baza, a analizy można wykonywać na replice do odczytu lub w oknie nocnym. Hurtownia zaczyna się opłacać, gdy zapytania analityczne zaczynają spowalniać pracę użytkowników albo gdy dane pochodzą z kilku systemów jednocześnie.

Reguła: ciężkie zapytania analityczne nie powinny działać na bazie transakcyjnej w godzinach pracy. Migawka lub hurtownia przejmują to obciążenie.

Ładowanie danych do hurtowni

Ładowanie składa się z trzech faz, znanych jako ETL: wydobycia danych ze źródła, przekształcenia ich do wspólnego układu i zapisu w tabelach faktów. Przekształcenie obejmuje między innymi ujednolicenie kodów i przeliczenie walut, a także odrzucenie rekordów niepoprawnych. Ładowanie pełne jest proste, lecz przy dużych zbiorach trwa zbyt długo, więc częściej stosuje się ładowanie przyrostowe, które pobiera tylko wiersze zmienione od ostatniego przebiegu.

INSERT INTO dbo.FaktKoszt (DataKsiegowania, ProjektId, KwotaNetto)
SELECT k.DataKsiegowania, k.ProjektId, k.KwotaNetto
FROM dbo.KosztPozycja AS k
WHERE k.DataZmiany > @OstatniePrzeladowanie;

Zapytanie zakłada kolumnę ze znacznikiem czasu zmiany, którą aplikacja aktualizuje przy każdym zapisie. Bez takiej kolumny przyrostowe ładowanie nie wie, które wiersze się zmieniły, i trzeba wrócić do porównywania całych zbiorów. Uruchomieniem zadania zajmuje się zwykle SQL Server Agent według harmonogramu.

Integrację danych z kilku systemów, na przykład magazynowego i księgowego, opisuje artykuł o raportowaniu w systemie WMS z KPI i SSRS. Techniki pracy z bazą omawia tekst o programowaniu baz danych.

Dostarczanie analiz przez panele i harmonogram

Analiza nie ma wartości, dopóki nie dotrze do odbiorcy. Według materiału SoftwareStudio panele dla zarządu łączą dane z ERP oraz z innych systemów, na przykład CRM, a SoftwareStudio buduje je na bazie SSRS, Power BI lub własnych komponentów webowych zintegrowanych z systemami klienta. Moduł ssJob harmonogramuje generowanie i wysyłkę raportów pocztą albo publikację na portalu klienta bez obsługi ręcznej.

Wybór technologii zależy od odbiorcy. Raport paginowany w SSRS sprawdza się, gdy wynik ma być wydrukowany lub zapisany w PDF. Interaktywny panel z filtrami lepiej służy zarządowi, który zmienia przekrój w trakcie spotkania. Serwer raportów i jego konfigurację opisuje artykuł o serwerze raportów, a usługi raportowe SQL Server omawia tekst o raportowaniu w SQL Server Reporting Services.

Zasada wdrożeniowa: panel BI buduje się etapami. Zaczyna się od jednego priorytetowego wskaźnika dla działu, a dopiero po jego wdrożeniu rozszerza analizy o kolejne obszary firmy.

Kontrola produkcji przez raporty ERP jest praktycznym przykładem takiego podejścia, opisanym w artykule o raportach ERP dla produkcji. Przyspieszenie przygotowania zapytań przez narzędzia pomocnicze omawia tekst o asystencie do raportów SQL.

Ofertę wdrożeń SoftwareStudio prezentuje strona usług SoftwareStudio, a system enova365 opisuje producent na stronie enova.pl. Pozycję systemu na rynku omawia artykuł o Soneta enova.