Czas raportowania w Excelu szybko mija — trend na wygodne narzędzia do prezentacji i analizy informacji jest widoczny we wszystkich dziedzinach. Od dawna dyskutowaliśmy o cyfryzacji budowania raportów i wybraliśmy system wizualizacji oraz analityki self-service Tableau. Aleksander Bezugły, kierownik działu rozwiązań analitycznych i raportowania Grupy "M.Wideo-Eldorado", opowiedział o doświadczeniach i rezultatach budowy funkcjonalnego dashboardu.
Powiem od razu, nie wszystko, co było zaplanowane, udało się zrealizować, ale doświadczenie było ciekawe, mam nadzieję, że będzie również przydatne dla was. A jeśli ktokolwiek ma pomysły na to, jak można było zrobić to lepiej – będę bardzo wdzięczny za sugestie i pomysły.
Poniżej o tym, z czym się zmierzyliśmy i czego się dowiedzieliśmy.
Od czego zaczynaliśmy
W "M.Wideo-Eldorado" istnieje dobrze opracowany model danych: zorganizowane informacje z wymaganą głębokością przechowywania i ogromna ilość raportów o stałej formie (zobacz szczegóły ). Z nich analitycy tworzą albo tabele przestawne, albo formatowane wysyłki w Excelu, albo ładne prezentacje w PowerPoint dla użytkowników końcowych.
Około dwa lata temu zamiast raportów o stałej formie zaczęliśmy tworzyć raporty analityczne w SAP Analysis (dodatek do Excela, w zasadzie – tabela przestawna nad silnikiem OLAP). Ale to narzędzie nie mogło zaspokoić potrzeb wszystkich użytkowników, większość nadal korzystała z informacji dodatkowo przetworzonych przez analityków.
Nasi użytkownicy końcowi dzielą się na trzy kategorie:
Top management. Żąda informacji w dobrze przedstawionej i zrozumiałej formie.
Middle management, zaawansowani użytkownicy. Zainteresowani badaniem danych i zdolni do samodzielnego tworzenia raportów w obecności narzędzi. To oni stali się kluczowymi użytkownikami raportów analitycznych w SAP Analysis.
Użytkownicy masowi. Nie są zainteresowani samodzielną analizą danych, korzystają z raportów o ograniczonej swobodzie, w formie wysyłek i tabel przestawnych w Excelu.
Nasza idea opierała się na zaspokojeniu potrzeb wszystkich użytkowników i zapewnieniu im jednego wygodnego narzędzia. Zdecydowaliśmy się zacząć od menedżerów wyższego szczebla. Potrzebowali oni wygodnych paneli do analizy kluczowych wyników biznesowych. Tak więc zaczęliśmy od Tableau i na początek wybraliśmy dwa kierunki: wskaźniki sprzedaży w detalicznej i online z ograniczoną głębokością i szerokością analizy, które pokryłyby około 80% danych, o które prosili menedżerowie wyższego szczebla.
Ponieważ użytkownikami pulpitów nawigacyjnych byli menedżerowie wyższego szczebla, pojawił się jeszcze jeden dodatkowy KPI produktu – czas reakcji. Nikt nie będzie czekał 20-30 sekund, aż dane się zaktualizują. Nawigacja powinna była zmieścić się w 4-5 sekundach, a najlepiej działać natychmiast. Niestety, nie udało nam się tego osiągnąć.
Oto jak wyglądał projekt naszego głównego pulpitu nawigacyjnego:
Kluczowa idea – połączyć kluczowe wskaźniki KPI, których w sumie zebrało się 19, z lewej strony i przedstawiać ich dynamikę oraz podziały według kluczowych atrybutów po prawej stronie. Zadanie wydaje się proste, wizualizacja logiczna i zrozumiała, dopóki nie zatoniesz w szczegółach.
Szczegół 1. Objętość danych
Nasza główna tabela sprzedaży za rok zajmuje około 300 mln wierszy. Ponieważ musimy odzwierciedlić dynamikę w porównaniu do ubiegłego i przedubiegłego roku, objętość danych tylko dotyczących rzeczywistej sprzedaży wynosi około 1 miliarda wierszy. A dodatkowo przechowywane są osobno informacje o danych planowanych i bloku sprzedaży internetowej. Dlatego, nawet mimo iż używaliśmy kolumnowej bazy danych w pamięci SAP HANA, czas odpowiedzi na zapytania dotyczące wszystkich wskaźników z jednego tygodnia z bieżących magazynów wynosił około 15-20 sekund. Rozwiązanie tego problemu samo się nasuwa – dodatkowa materializacja danych. Ale w tym również są pułapki, o których powiem później.
Szczegół 2. Nieadtytywne wskaźniki
Wiele naszych KPI związanych jest z liczbą paragonów. I ten wskaźnik to COUNT DISTINCT liczby wierszy (nagłówków paragonów), który pokazuje różne kwoty w zależności od wybranych atrybutów. Na przykład, jak ten wskaźnik oraz jego pochodne powinny być liczone:
Aby zapewnić poprawność obliczeń, można:
- Przeprowadzać obliczenia takich wskaźników na bieżąco w magazynie;
- Przeprowadzać obliczenia na całej objętości danych w Tableau, t.j. na żądanie w Tableau zwracać wszystkie dane według wybranych filtrów w szczegółowości pozycji paragonów;
- Stworzyć materializowaną witrynę, w której zostaną obliczone wszystkie wskaźniki we wszystkich wariantach próbek, dających różne nieaddytywne wyniki.
Jasne jest, że w przykładzie UTE1 i UTE2 – to atrybuty materiału, reprezentujące hierarchię towarową. To nie jest statyczna rzecz, przez nią zarządzanie w firmie, ponieważ za różne grupy towarowe odpowiadają różni menedżerowie. Mieliśmy wiele globalnych przeglądów tej hierarchii, kiedy zmieniały się wszystkie poziomy, gdy przeglądano wzajemne powiązania, a także stałe, punktowe zmiany, gdy jedna grupa przechodzi z jednego węzła do drugiego. W zwykłych raportach wszystko to jest liczone na bieżąco z atrybutów materiału, w przypadku materializacji tych danych konieczne jest opracowanie mechanizmu śledzenia takich zmian i automatycznego przeładowywania danych historycznych. Dość nietrywialne zadanie.
Część 3. Porównanie danych
Ten punkt jest podobny do poprzedniego. Chodzi o to, że w firmie podczas analizy przyjmuje się utworzenie kilku poziomów porównania z poprzednim okresem:
Porównanie z poprzednim okresem (dzień do dnia, tydzień do tygodnia, miesiąc do miesiąca)
W tym porównaniu zakłada się, że w zależności od wybranego przez użytkownika okresu (na przykład 33 tydzień roku) musimy pokazać dynamikę do 32 tygodnia. Gdybyśmy wybrali dane za miesiąc, na przykład maj, to to porównanie pokazałoby dynamikę do kwietnia.
Porównanie z poprzednim rokiem
Głównym niuansem jest to, że porównując dni i tygodnie, nie można wziąć dokładnie tego samego dnia z poprzedniego roku, czyli nie można po prostu wziąć bieżącego roku minus jeden. Trzeba patrzeć na porównywany dzień tygodnia. Natomiast przy porównywaniu miesięcy należy wziąć dokładnie ten sam dzień kalendarzowy z poprzedniego roku. Są też niuanse związane z latami przestępnymi. W źródłowych zbiorach wszystkie informacje są rozłożone na dni, nie ma oddzielnych pól dla tygodni, miesięcy, lat. Dlatego, aby uzyskać pełny analityczny przekrój w panelu, należy liczyć nie jeden okres, na przykład tydzień, ale 4 tygodnie, a następnie te dane jeszcze porównać, odzwierciedlić dynamikę, odchylenia. Odpowiednio, tę logikę tworzenia porównań w dynamice można również realizować albo w Tableau, albo po stronie witryny. Tak, i oczywiście znaliśmy te detale i myśleliśmy o nich już na etapie projektowania, ale ich wpływ na wydajność końcowego dashboardu było trudno przewidzieć.
Podczas wdrażania dashboardu przeszliśmy długą drogę Agile. Naszym zadaniem było jak najszybsze udostępnienie działającego narzędzia do testów z potrzebnymi danymi. Dlatego pracowaliśmy w sprintach i skupialiśmy się na minimalizacji prac po stronie bieżącego magazynu.
Część 1. Wiara w Tableau
Aby uprościć wsparcie IT i szybkie wdrażanie zmian, zdecydowaliśmy się stworzyć logikę obliczania wskaźników nieaddytywnych i porównania przeszłych okresów w Tableau.
Etap 1. Wszystko na żywo, żadnych poprawek witryn.
Na tym etapie połączyliśmy Tableau z bieżącymi witrynami i postanowiliśmy sprawdzić, jak będzie liczone łącznie paragonów za jeden rok.
Wynik:
Odpowiedź była przygnębiająca – 20 minut. Przesył danych przez sieć, duże obciążenie Tableau. Zrozumieliśmy, że logikę z wskaźnikami nieaddytywnymi należy zrealizować na HANA. Nie przerażało nas to zbytnio, mieliśmy już podobne doświadczenie z BO i Analysis i potrafiliśmy budować szybkie witryny w HANA, które dostarczają prawidłowo obliczone wskaźniki nieaddytywne. Teraz pozostało tylko dostosować je do Tableau.
Etap 2. Tunelujemy witryny, żadnej materializacji, wszystko na żywo.
Stworzyliśmy osobny nowy panel, który na bieżąco przekazywał wymagane dane do TABLEAU. Ogólnie uzyskaliśmy dobry wynik, skracając czas generowania wszystkich wskaźników z jednego tygodnia do 9-10 sekund. Szczerze mówiąc, spodziewaliśmy się, że czas odpowiedzi dashboardu w Tableau wyniesie 20-30 sekund przy pierwszym otwarciu, a następnie dzięki pamięci podręcznej od 10 do 12 sekund, co w sumie by nas zadowoliło.
Wynik:
Pierwsze otwarcie dashboardu: 4-5 minut
Każde kliknięcie: 3-4 minuty
Nikt nie spodziewał się takiego dodatkowego wzrostu wydajności panelu.
Część 2. Zanurzenie w Tableau
Etap 1. Analiza wydajności Tableau i szybka optymalizacja
Zaczęliśmy analizować, na co Tableau poświęca najwięcej czasu. W tym celu dysponujemy całkiem dobrym zestawem narzędzi, co jest oczywiście plusem Tableau. Głównym problemem, który zidentyfikowaliśmy, były bardzo skomplikowane zapytania SQL, które generowało Tableau. Były one przede wszystkim związane z:
— transponowaniem danych. Ponieważ w Tableau brakuje narzędzi do transponowania zestawów danych, musieliśmy tworzyć tabelę przez case, aby skonstruować lewą część dashboardu z dokładnym przedstawieniem wszystkich KPI. Rozmiar zapytań SQL w bazie danych osiągał 120 000 znaków.
— wyborem okresu czasu. Takie zapytanie na poziomie bazy danych zajmowało więcej czasu na kompilację niż na wykonanie:
Tzn. przetwarzanie zapytania 12 sekund + 5 sekund wykonanie.
Postanowiliśmy uprościć logikę obliczeń po stronie Tableau i przenieść kolej część obliczeń na panel i poziom bazy danych. To przyniosło dobre rezultaty.
Na początku dokonaliśmy transpozycji na bieżąco, stosując pełne połączenie zewnętrzne na końcowym etapie obliczenia WIDOKU, zgodnie z podejściem opisanym na wiki i .
To znaczy, że stworzyliśmy tabelę konfiguracyjną – macierz transponującą (21x21) i uzyskaliśmy wszystkie wskaźniki w rozbiciu wierszowym.
Było:
Stało się:
Na samo transponowanie bazy danych czas właściwie się nie traci. Zapytanie po wszystkich wskaźnikach za tydzień działało nadal w około 10 sekund. Jednak utracona została elastyczność w budowie dashboardu dla konkretnego wskaźnika, tzn. w prawej części dashboardu, gdzie przedstawiona jest dynamika i szczegółowy podział konkretnego wskaźnika, wcześniej witryna działała w 1-3 sekundy, ponieważ zapytanie dotyczyło jednego wskaźnika, a teraz baza danych zawsze wybierała wszystkie wskaźniki i filtruje wynik już przed zwróceniem rezultatu w Tableau.
W rezultacie prędkość działania dashboardu zmniejszyła się prawie trzy razy.
Wynik:
- 5 sek — parsowanie dashboardu, wizualizacji
- 15-20 sek — przygotowanie do kompilacji zapytań z wykonaniem wstępnych obliczeń w Tableau
- 35-45 sek — kompilacja zapytań SQL i jednoczesne sekwencyjne ich wykonanie w Hana
- 5 sek — przetwarzanie wyników, sortowanie, przeliczenie wizualizacji w Tableau
- Oczywiście takie wyniki nie były akceptowalne dla biznesu, więc kontynuowaliśmy optymalizację.
Etap 2. Minimum logiki w Tableau, pełna materializacja
Rozumieliśmy, że zbudowanie dashboardu z czasem reakcji w kilku sekundach na witrynie, która działa 10 sekund, jest niemożliwe i rozważaliśmy opcje materializacji danych po stronie bazy danych właśnie pod wymagany dashboard. Jednak napotkaliśmy globalny problem, opisany powyżej – nieadekwatne wskaźniki. Nie mogliśmy zrobić tak, aby przy zmianie filtrów lub rozwinięć Tableau elastycznie przełączało się między różnymi witrynami i poziomami, wstępnie obliczonymi dla różnych hierarchii towarowych (w przykładzie trzy zapytania bez UTE, z UTE1 i UTE2 tworzą różne wyniki). Dlatego podjęliśmy decyzję o uproszczeniu dashboardu, porzuceniu hierarchii towarowej w dashboardzie i zobaczeniu, jak szybki może być w uproszczonej wersji.
Tak więc na tym ostatnim etapie zebraliśmy osobne repozytorium, w którym złożono w transponowanej formie wszystkie KPI. Po stronie bazy danych każde zapytanie do takiego repozytorium działa w 0,1 – 0,3 sekundy. W dashboardzie uzyskaliśmy następujące wyniki:
Pierwsze otwarcie: 8-10 sekund
Każde kliknięcie: 6-7 sekund
Czas, który wydaje Tableau, składa się z:
- 0,3 sek — parsowanie dashboardu i kompilacja zapytań SQL
- 1,5-3 sek — wykonanie zapytań SQL w Hana dla głównych wizualizacji (uruchamiane równolegle z pkt. 1)
- 1,5-2 sek. — renderowanie, przeliczenie wizualizacji
- 1,3 sek. — wykonywanie dodatkowych zapytań SQL w celu uzyskania odpowiednich wartości filtrów (Marka, Dział, Miasto, Sklep), parsowanie wyników
Jeśli podsumować w skrócie
Podobał nam się tool Tableau z punktu widzenia wizualizacji. Na etapie projektowania rozważaliśmy różne elementy wizualizacji i wszystkie je znaleźliśmy w bibliotekach, w tym złożone, wielowarstwowe segmentacje i wiele sterowników waterfall.
Implementując dashboardy z kluczowymi wskaźnikami sprzedaży, napotkaliśmy na trudności wydajnościowe, których jeszcze nie udało nam się pokonać. Poświęciliśmy ponad dwa miesiące i uzyskaliśmy funkcjonalnie niepełny dashboard, którego czas reakcji jest na granicy akceptowalnego. Wyciągnęliśmy wnioski:
- Tableau nie obsługuje dużych zbiorów danych. Jeśli w pierwotnym modelu danych masz ponad 10 GB danych (około 200 mln x 50 wierszy), to pulpit nawigacyjny znacznie spowalnia – od 10 sekund do kilku minut na każde kliknięcie. Eksperymentowaliśmy zarówno z live-connect, jak i z ekstraktem. Prędkość działania porównywalna.
- Ograniczenia przy użyciu kilku zbiorów danych. Nie ma możliwości standardowymi narzędziami wskazać powiązań zbiorów danych. Jeśli użyć obejściowych rozwiązań do połączenia zbiorów danych, wpłynie to znacząco na wydajność. W naszym przypadku rozważaliśmy opcję materializacji danych w każdym wymaganym zestawieniu wizualizacji, a na tych zmaterializowanych zbiorach danych dokonywać przełączeń z zachowaniem wcześniej wybranych filtrów — co okazało się niemożliwe do zrealizowania w Tableau.
- W Tableau nie można tworzyć dynamicznych parametrów. Nie możesz uzupełniać parametru, który jest wykorzystywany do filtrowania zbioru danych w ekstrakcie lub przy połączeniu na żywo, wynikiem innego zestawienia z zbioru danych lub rezultatem innego zapytania SQL, tylko natywnym wkładem użytkownika lub stałą.
- Ograniczenia związane z budowaniem dashboardu z elementami OLAP|Tabeli przestawnej.
W MSTR, SAP SAC, SAP Analysis, jeśli dodajesz zestaw danych do raportu, to wszystkie obiekty są domyślnie ze sobą powiązane. W Tableau nie ma tego, połączenia trzeba ustawiać ręcznie. To, może, jest bardziej elastyczne, ale dla wszystkich naszych pulpitów nawigacyjnych to wymóg dotyczący elementów – dlatego to dodatkowy nakład pracy. Co więcej, jeśli robisz powiązane filtry, aby na przykład przy filtrowaniu regionu lista miast była ograniczona tylko do miast w tym regionie, od razu trafiasz na kaskadowe zapytania do bazy danych lub ekstraktu, co znacznie spowalnia pulpit. - Ograniczenia w funkcjach. Zarówno nad ekstraktem, jak i TYM BARDZIEJ nad zestawem danych z Live-connecta nie można robić masowych przekształceń. Można to robić przez Tableau Prep, ale to dodatkowy nakład pracy i jeszcze jedno narzędzie, które trzeba nauczyć się i utrzymać. Na przykład, nie możesz przetransponować danych, połączyć ich samych ze sobą. To zamyka się w przekształceniach w poszczególnych kolumnach lub polach, które trzeba wybierać przez case lub if, co generuje bardzo skomplikowane zapytania SQL, przy których baza głównie traci czas na kompilację tekstu zapytania. Te sztywności narzędzia musiały być rozwiązane na poziomie witryny, co prowadzi do komplikacji w przechowywaniu, dodatkowych ładowaniach i przekształceniach.
Nie skreśliliśmy Tableau. Ale jako narzędzie zdolne do budowania przemysłowych pulpitów nawigacyjnych i środek, za pomocą którego można zastąpić i zdigitalizować cały system korporacyjnego raportowania firmy, Tableau nie jest rozważane.
Obecnie aktywnie opracowujemy podobny pulpit w innym narzędziu i równolegle staramy się przemyśleć architekturę pulpitu w Tableau, aby uprościć ją jeszcze bardziej. Jeśli społeczności będzie to interesować – opowiemy o wynikach.
Czekamy również na wasze pomysły lub sugestie, jak w Tableau można budować szybkie pulpity nad tak dużymi ilościami danych, ponieważ mamy również stronę, na której danych jest znacznie więcej niż w sprzedaży detalicznej.
Źródło: habr.com
