Depiwotyzacja raportyzacji

https://xpil.eu/cmk 📋

W świecie hurtowni danych mamy do czynienia nie tylko z różowymi jednorożcami pierdzącymi wonną, wielobarwną tęczą a'la Chanson D'Eau.

Trafiają się też brzydkie, kostropate, utytłane w końskim gównie stare hipopotamy, które w zasadzie powinny już dawno umrzeć ze starości, ale skoro już trafiły do korporacyjnego systemu podtrzymywania życia, straszą teraz uczciwych programistów, wywołując koszmary, nocne moczenie oraz bezdech.

Z jednym takim hipciem miałem (nie)przyjemność wziąć się jakiś czas temu za bary. Z potyczki wyszedłem zwycięsko, wbrew oczekiwaniom czających się tu i ówdzie na moją padlinę sępów.

Doświadczeniem, którego nabrałem w międzyczasie, podzielę się dziś z moimi Czytelnikami.

Proszę się nie obawiać, to nie jest wpis wysoce specjalistyczny. Nie będziemy pisać kodu 😉

Zaczniemy od początku, czyli od opisania sytuacji ogólnej.

Jest sobie firma. Duża, z wieloma działami, oddziałami i przedziałami (a także podziałami, charakterystycznymi dla dużych firm).

Szef jednego z działów wymyślił sobie, że podpisze umowę z zewnętrznym dostawcą danych, firmą, dajmy na to, X.

Jakich danych? To akurat bez większego znaczenia. Mogą to być dane o klientach, o cenach produktów lub o giełdowych kursach stolca. Wsioryba. Ważne, że firma X jest w stanie te dane dostarczać tak często, jak jest to potrzebne.

Umowę podpisano, przez kilka lat wszystko szło jak z płatka: firma zewnętrzna dostarczała nam dane w uzgodnionym formacie, sporadycznie trafiały się babole, które rozwiązywano na bieżąco, wszyscy byli zadowoleni.

Świat jednak nie stoi w miejscu i po trzech latach okazało się, że konkurencja, firma Y, też może te dane dostarczyć. I to nie tylko taniej, ale również w szerszym zakresie.

Co robi w tym momencie firma X?

Szybciutko nadgania zaległości i informuje nas, że oni również mogą dostarczyć te same dane, co firma Y, za te same pieniądze. A jeżeli podpiszemy nowy kontrakt i opłacimy go za trzy lata z góry, mogą nam dać upust.

Skoro tak, to szkoda zachodu na zmianę dostawcy. Podpisujemy nowy kontrakt z firmą X, na kolejnych 36 miesięcy. Po czym odbieramy pierwszą paczkę danych...

... i tu zaczyna się moja przygoda 😉

Albowiem paczka danych w nowej wersji wygląda tak:

Plik XLSX, a w nim:
- pięć linii nagłówka technicznego (data wygenerowania pliku, nazwa pliku, jakieś inne mało istotne śmieci)
- jedna pusta linia
- jeszcze dwie puste linie... a nie, wróć. Wcale nie takie puste. Po przewinięciu kilkadziesiąt kolumn w prawo okazuje się, że w wierszach 7 i 8 znajdują się tu i ówdzie daty. W komórkach scalonych poziomo.
- linia nagłówkowa, z nazwami kolumn. Łącznie prawie 300 kolumn. Niektóre nazwy się powtarzają.
- jedna pusta linia
- pierwszy wiersz danych, z wypełnioną wyłącznie trzecią kolumną (czyli C, reszta kolumn - pusto)
- drugi wiersz danych (i kolejne) - w końcu jakieś konkrety, wszystkie lub prawie wszystkie kolumny wypełnione sensownymi danymi
- kilkaset wierszy niżej - wiersz z podsumowaniem. Dużo pustych komórek, tu i ówdzie jakaś etykieta lub wartość sumaryczna.
- wiersz odstępu
- znów wiersz danych z wypełnioną wyłącznie kolumną trzecią (kolumna C)
- dużo danych.
- podsumowanie
- odstęp (pusta linia)

... i tak dalej, przez mniej więcej dwadzieścia tysięcy linii.

Reasumując: ktoś wziął dane, zrobił z nich raport hierarchiczny, pociągnął go pivotem albo przepuścił przez jakąś inną maszynę-straszynę. W efekcie wyszedł "raport" excelowy o rozmiarach mniej więcej 300 x 20000 komórek, z którego ja mam teraz zrobić coś sensownego. Najlepiej zassać go do stale rosnącej hurtowni danych w celu dalszych analiz.

Moja pierwsza reakcja:
(uwaga: nie klikać przy małoletnich)
http://memy.pl/show/big/uploads/Post/23098/14469874495846.jpg

Reakcja została przez kolegów oraz szefostwo przyjęta z pełnym zrozumieniem. Zapytany o dalsze kroki odrzekłem bez wahania, że należy poprosić firmę X o dane źródłowe, na podstawie których wygenerowano tego excelowego potwora. W przeciwnym bowiem razie będziemy musieli we własnym zakresie przeprowadzić de-piwotyzację, granulkowanie oraz odśmiecanie tego hipopotama tylko po to, żeby znaleźć się w jakimś sensownym punkcie startowym do raportowania.

Ktoś tam uznał, że gadam z sensem. Moja sugestia została w uprzejmy sposób przekazana firmie X, która jeszcze tego samego dnia odrzekła, że owszem, takie rozwiązanie byłoby najlepsze dla wszystkich, ale niestety oni nie są w stanie tych surowych danych nam przekazać, bo cośtam cośtam licencja, cośtam cośtam stary system, cośtam cośtam zamknięty kod, cośtam cośtam już tu nie pracuje, cośtam cośtam...

Czyli klasyka. Zapłaciliście za dane, dostaliście dane, a że dane są w formacie kojarzącym się nieco ze schematem systemu telefonii naziemnej Bangladeszu, to już wasz problem, nie nasz.

Z lekkim niepokojem oraz niewiele od niego mniejszym obrzydzeniem zabrałem się za rozbieranie padliny z hipopotama na czynniki pierwsze.

Minęły dwie godziny...

... i oto miałem działający kawałek kodu VBA, który całe to draństwo rozbiera na atomy, po czym zapisuje te atomy do osobnego arkusza w ładnej, standardowej postaci tabelki z pięcioma kolumnami oraz...

... dwa błędy później...

docelowo sześcioma milionami wierszy, tylko że cholera Excel ma tylko milion z niedużym hakiem. Sześciu milionów nie zmieszczę, choćbym po nich skakał i próbował ugnieść jak jakąś kapustę w beczce. No nie da się i już.

Co robić?

Walę do szefostwa z zapytaniem ile właściwie mam na to czasu. Dostaję odpowiedź, że tyle, ile potrzebuję, bo nikt inny nie chciał się za to zabrać nawet pod groźbą tygodniowej służby przy niszczarce. W zasadzie dobrze by było, gdybym się wyrobił w tydzień, ale no pressure.

... trzy godziny później...

Mam działający, nieco większy kawałek kodu VBA, który robi dokładnie to samo, co przedtem, tylko zamiast zapisywać wyniki do osobnego arkusza XLSX, pisze bezpośrednio do tabeli w warstwie stage hurtowni danych.

Jedyny problem to prędkość. Sto rekordów na sekundę. Zapisanie sześciu milionów rekordów zajmie mu {..liczu, liczu...} jakieś siedemnaście godzin, pi x oko. Jeżeli wszystko pójdzie bez błędów rzecz jasna.

Walę raz jeszcze do szefostwa i pytam, ilu takich hipopotamów się spodziewamy w jednostce czasu. Okazuje się, że nie wiadomo. W najlepszym przypadku jednego na miesiąc. W najgorszym - jednego dziennie.

Ponieważ prawa Murphy'ego nie śpią, nastawiam się od razu na dwa hipopotamy dziennie i siadam do dalszej optymalizacji.

Pół godziny i szklanicę kawy później mam jeszcze ciut większy kawałek kodu VBA, który "obrabia" hipopotama w tempie mniej więcej dziesięciu tysięcy komórek na sekundę. Przy sześciu milionach daje to jakieś dziesięć minut na całość.

Not bad - klepię się po plecach - not bad at all - dodaję po cichutku.

Z dumą prezentuję swoje rozwiązanie swemu bezpośredniemu przełożonemu, a ten na to, żebym cicho siedział i broń boże się nie chwalił, że mamy gotowca. Bo oni na to zabudżetowali minimum tydzień, a tak naprawdę są przygotowani na dwa. Więc wreszcie będzie trochę luzu.

Dziwny świat.

https://xpil.eu/cmk 📋