Wyliczanie miar kalkulowanych bezpośrednio po stronie bazy danych

Okazuje się że niezależnie od tego czy programista pracuje dla klienta z branży energetycznej, farmaceutycznej czy bankowej, analizy i raporty które tworzy są z reguły bardzo podobne. Większość analiz posiada miary kalkulowane, takie jak:
   -sumy narastające na przykład: Year-To-Date (YTD), Quarter-To-Date (QTD)
   -wartości z poprzedniego okresu: Previous Year(PY), Previous Month(PM)
i tym podobne.

Są one bardzo popularne ponieważ pozwalają odnieść się do wyników uzyskanych w przeszłości (PY,PQ,...) lub pokazać stopień realizacji planu (YTD,QTD,...). Z biznesowego punktu widzenia to niezwykle cenne informacje.

I tu przejdę do sedna strony technicznej. Są to miary bardzo często wyliczane przez narzędzia warstwy prezentacji (przez aplikacje w których budujemy analizy i raporty). Co za tym idzie? Wyliczanie skomplikowanych miar po stronie warstwy prezentacji nie zawsze jest najwydajniejszą z dostępnych metod. Jeżeli zauważymy że nasz raport ma problem z wydajnością i posiada tego typu miary warto sprawdzić czy da się go usprawnić.

Przykład1. Mamy raport pokazujących wydatki na rozwój aplikacji używanych w banku. Oprócz kosztów miesięcznych, raport zawiera pola typu:
-YTD - suma narastająca kosztów z bieżącego roku, ta miara pokazuje jak wiele środków pochłonęły aplikacje w bieżącym roku
-PY(YTD) - suma narastająca kosztów z zeszłego roku.
-Budżet – ta miara została dodana do raportu ponieważ pokazuje jak bardzo YTD zbliżyło się do limitu wydatków ujętych w budżecie.

Co musi 'zrobić' warstwa prezentacyjna raportu aby obliczyć powyższe pola? W każdym przypadku coś innego:
I) Zacznijmy od YTD. Jeżeli na raporcie wyświetlamy wartości od początku roku to aby obliczyć tę miarę wystarczy skorzystać z danych jakie zostały dostarczone do tej pory. Nie trzeba sięgać do bazy danych po dodatkowe pola, a to już bardzo komfortowa sytuacja. Silnik sumuje wszystkie wartości występujące od początku roku do miesiąca bieżącego i miara gotowa.
II) Z wartością YTD z poprzedniego roku PY(YTD) jest już nieco gorzej. W tym wypadku aby uzyskać rekordy z roku minionego, musimy dodatkowo odpytać bazę danych. Trzeba to zrobić, ponieważ na chwilę obecną nie posiadamy rekordów pozwalających na wyliczenie wspomnianej miary. Jeżeli raport nawet bez wartości PY jest wolny, odpytywanie bazy danych o dodatkowe rekordy, z poprzedniego roku może sprawić że spowolni na tyle że czas wykonywania raportu przestanie być na poziomie akceptowalnym przez użytkownika.

III) Najtrudniejszym zadaniem w przytoczonym przykładzie wydaje się być dodanie kolumny zawierającej informacje o budżecie. Dlaczego? Ponieważ najczęściej ponoszone koszty oraz budżety przechowuje się w odrębnych tabelach faktów. Co za tym idzie warstwa prezentacyjna musi odpytać kolejną tabelę, żeby móc wyświetlić dodatkową kolumnę. Jak by nie patrzeć jest to kolejna potencjalnie czasochłonna czynność.

Mamy trzy różne zadania. Wśród nich dwa w których potrzebujemy dodatkowego odpytania bazy danych.

Czy da się to usprawnić? Często tak. Możemy rozszerzyć tabelę faktów i przechowywać w niej miary kalkulowane razem z miarami podstawowymi. Jaki będzie wpływ dodatkowych miar na prezentowane przypadki jeżeli do tabeli faktów dodamy nowe kolumny z gotowymi wartościami miar? Różny:

I) YTD - ta miara nie miała potrzeby sięgania do bazy danych. Można ją było obliczyć na podstawie dostępnych danych. W tym wypadku narzędzie warstwy prezentacji nie będzie musiało sumować poszczególnych miesięcy w celu uzyskania ostatecznej miary.

II) PY(YTD), miara jest gotowa, warstwa prezentacji tak jak w przykładzie powyżej nie musi wykonywać żadnych sumowań, co bez wątpienia jest korzystne. Jednak zdecydowanie większą zaletą jest uniknięcie ponownego odpytywania bazy o rekordy z poprzedniego roku. Tak jak to miało miejsce kiedy miara kalkulowana po stronie bazy danych była niedostępna.

III) Budżet - kolumna z zupełnie innej tabeli faktów. Czy może znaleźć się wśród miar wyliczanych po stronie bazy danych? Tak, pod warunkiem że obie tabele da się sprowadzić do tego samego poziomu granuralności. Często dane o budżetach oraz dane o wydatkach posiadają takie same wymiary, więc ten warunek może zostać spełniony. W tym wypadku dodanie kolumny do miar wyliczanych po stronie bazy danych pozwoliło uniknąć dodatkowego zapytania do odrębnej tabeli faktów.

Dodatkową korzyścią jest porządek jaki otrzymujemy po implementacji tego typu miar po stronie bazy danych. Dla programisty budującego analizę jest to bardzo komfortowe rozwiązanie, wszystkie miary ma 'pod ręką' i nie musi budować skomplikowanych formuł obliczających dla każdej analizy z osobna.

Czy istnieją niebezpieczeństwa związane z dodawaniem miar kalkulowanych bezpośrednio do tabeli faktów? Niestety tak. Każde dodatkowe pole przetrzymywane po stronie bazy danych potrzebuje pamięci, przekłada się to bezpośrednio na ilość potrzebnej przestrzeni dyskowej. W dzisiejszych czasach sama przestrzeń nie jest problemem ale odczytanie wszystkich wierszy z danej tabeli wiąże się z pobraniem wszystkich bloków danych. W tym momencie warto nadmienić że często to z jak dużymi tabelami faktów ma się do czynienia ocenia się po ilości wierszy. Jest to bardzo zgubna metoda! Ponieważ to od ilości megabajtów jaką zajmuje tabela a nie od ilości wierszy będzie zależał czas jej pełnego przeszukiwania. Spójrzmy na poniższy rysunek.

 

Pomarańczowa ramka pokazuje jak wiele pamięci na dysku zajmują bloki tabeli zawierającej wyłącznie miary podstawowe. Czerwona ramka obrazuje ile dodatkowej pamięci będą potrzebowały nowe miary kalkulowane, oraz dodatkowe wiersze związane z miarami kalkulowanymi. Dlaczego ilustracja przedstawia dodatkowe wiersze, skoro dodaliśmy kolumny? Miary kalkulowane często powodują że tabela nie tylko posiada więcej kolumn, ale i wierszy. Widać to na przykładzie z tabeli prezentującej wydatki na aplikacje. Aplikacja 2 nie posiada wydatków w miesiącach: luty i marzec. W takim wypadku pierwotna tabela nie posiada wierszy z nimi związanych. Jednak jeżeli zdecydujemy się na rozszerzenie tabeli o miary typu YTD, będziemy musieli dodać dwa wiersze dla lutego i marca, przechowuje wartości 0 dla wydatków miesięcznych oraz 20000 dla miary YTD.
Brałem kiedyś udział w optymalizacji raportów opartych o tabele wydatków i budżetów. Podjęliśmy decyzje o zastosowaniu miar kalkulowanych po stronie bazy danych. Okazało się że dane o wydatkach nie były danymi gęstymi, wpisy występowały co kilka miesięcy. Jednak dodanie miary YTD dla każdego miesiąca spowodowało że tabela nie dość że urosła znacznie 'w szerz' – przybyły nowe kolumny, to i urosła kilkukrotnie 'wzdłuż' przybyły wiersze które zapełniły danymi YTD miesiące w których nie było kosztów. Efekt? Tabela rozrosła się kilkukrotnie a raporty które do tej pory działały bardzo wolno zaczęły działać 'dramatycznie wolno'. Pokazuje to jak ostrożnie trzeba podchodzić do tego typu zmian i że stosowanie tej samej metody optymalizacji może przynieść różne skutki w różnych przypadkach.

Czy istnieją jeszcze jakieś przeciwwskazania? Tak, opisany powyżej sposób działania dotyczy analiz opartych o ROLAP i HOLAP. W przypadkach w których korzystamy z modelu danych opartym o kostki OLAP, które nie sięgają po dane z bazy w czasie pracy raportów i analiz (robią to tylko w czasie procesu ładowania kostki), nie ma sensu kalkulowanie miar po stronie bazy danych. Silniki OLAP nie odpytują bazy o żadne rekordy ponieważ przechowują je w kostce. Wyliczenie ich po stronie bazy danych może przyspieszyć proces ładowania kostki ale nie będzie miało wpływu na działanie analiz i raportów (więcej na temat kostek OLAP).