Każdy, kto choć w niewielkim stopniu miał styczność z analizą danych wie, że jest to bardzo rozległy temat. W zależności od potrzeb wykorzystywane są do niej różne narzędzia, takie jak Excel, PowerBI i Azure Analysis Services, które – wbrew pozorom – łączy bardzo wiele. Wszystkie te technologie wykorzystują silnik Vertipaq i kolumnowe składowanie danych, dzięki którym możliwa jest wydajna analiza z wykorzystaniem mechanizmów In-Memory.
Różne podejścia do analizy danych?
Jak już wiemy, analiza danych jest tematem bardzo rozległym, szczególnie w obecnej epoce Big Data. Trzeba jednak pamiętać, że nie każda firma ma do dyspozycji wielkie zbiory danych, a co więcej – wcale nie są one niezbędne. Do prostych analiz często wystarczają arkusze Excela – i właśnie takie rozwiązanie jest powszechnie stosowane.
Kiedy jednak zbieramy dane z większej liczby systemów, ich przechowywanie w arkuszach może okazać się bardzo problematyczne. Często w takiej sytuacji dobrym rozwiązaniem okazuje się wykorzystanie bazy danych (na przykład hurtowni) i podłączenie się do niej za pomocą Excela, aby np. analizować dane sprzedażowe.
Jeżeli w naszej firmie mamy bardzo rozbudowane działy, które analizują duże ilości danych, możemy pokusić się o zbudowanie modelu tablicowego, a następnie wdrożenie go do usługi Azure Analysis Services. Zaletą takiego rozwiązania jest bardzo wydajny model z predefioniowanymi miarami. Użytkownik, łącząc się przez narzędzie takie jak np. Excel czy Power BI, dostaje zestaw miar i atrybutów – nie musi nic budować, wystarczy analizować 🙂 .
Te trzy podejścia łączy jedno – silnik Vertipaq (xVelocity). Wiem, że nawet zaawansowanym użytkownikom Excela ta nazwa może nic nie mówić, dlatego chciałbym o nim opowiedzieć i zaprezentować, w jaki sposób można go wykorzystać w stu procentach!
Czym jest silnik Vertipaq (xVelocity)?
Jest to silnik zaimplemetowany w Excelu (a konkretniej w PowerPivot) i Analysis Services (Tabular), umożliwiający kolumnowe składowanie danych, a także przetwarzanie In-memory. Dzięki niemu możliwe jest wydajne przetwarzanie danych na potrzeby analityczne. Silnik opiera się na kilku istotnych elementach, o których opowiem poniżej.
Kolumnowe składowanie danych
Patrząc na arkusze Excela możemy zauważyć, że dane są tam składowane w sposób wierszowy. To znaczy, że każdy wiersz ma swój unikatowy numer, a w każdej kolumnie przechowywane są dane powiązane tylko z nim. Takie podejście jest też często wykorzystywane w silnikach bazodanowych. W sytuacji, gdy chcemy pobrać wszystkie kolumny jest to zupełnie w porządku. Co jednak, jeśli każdy wiersz ma bardzo wiele kolumn, a my potrzebujemy wartości tylko z trzech lub czterech z nich? Otóż w takim przypadku silnik pobierający dane i tak musi wyciągnąć wszystkie wiersze i kolumny, aby zwrócić pożądane przez nas wyniki.
Lepszym rozwiązaniem w tym przypadku będzie składowanie danych w postaci kolumnowej. Oznacza to, że w pamięci komputera dane będą poukładane kolumnowo. Mówiąc prościej – jeżeli potrzebujemy tylko dwóch kolumn, silnik nie musi przeglądać n wierszy i z nich wszystkich ekstrachować wybranych przez nas dwóch kolumn – wyciąga po prostu dwie wybrane kolumny.
Wróćmy jeszcze na chwilę do teorii hurtowni danych. Tabele faktów zwykle są szerokie, czyli zawierają dużo kolumn. Bardzo rzadko istnieje potrzeba wyciągania ich wszystkich, przez co kolumnowe składowanie sprawdza się wtedy idealnie. Excel, PowerBI, Azure Anlysis Services (Tabular), SQL Server, a przede wszystkim Azure SQL Data Warehouse wspierają właśnie kolumnowe składowanie danych. W dwóch pierwszych wymienionych przeze mnie narzędziach odpowiada za to silnik Vertipaq, natomiast w dwóch kolejnych – kolumnowy indeks klastrowany i nieklastrowany.
Jaki wpływ na wydajność ma ilość danych?
Mówiąc wprost – ogromny. To co jest wąskim gardłem w przypadku operacji na danych, to czas ich odczytu z pamięci. Aby go zmniejszyć możemy zainstalować szybszą pamięć albo skorzystać z łatwiejszego rozwiązania – przechowywać mniej danych. Do tego celu wykorzystuje się kompresję. Dzięki niej przechowujemy mniej danych, ale taką samą liczbę informacji, co sprawia, że przy jednym odczycie możemy pobrać znacznie więcej informacji. Minusem kompresji jest zwiększone obciążenie procesora (dane trzeba rozkompresować), ale przy optymalnym wykorzystaniu nie będzie to problemem.
Kompresja, czyli w jaki sposób zmniejszyć ilość danych
Wyobraźmy sobie sytuację, że nasze dane składowane są w sposób kolumnowy, a w jednej z tych kolumn zapisany jest rok urodzenia klienta. Załóżmy, że mamy 1 000 000 klientów, a 10 000 z nich urodziło się w 1999 roku. Czyli w naszej kolumnie przechowujemy 10 000 identycznych wartości – dla uproszenia, niech będzie to łańcuch znaków (wiem, że jest to niewydajnie, ale chcę zobrazować, o co chodzi 🙂 ). W takim przypadku w pamięci przechowujemy 10 000 x 4 znaków = 40 000 znaków. Sporo, jak na identyczne wartości. Na szczęście istnieje coś takiego jak kompresja, czyli taki zapis tej samej informacji, aby zminimalizować powtarzające się wzorce. To znaczy, że nie musimy zapisywać roku 1999 10 000 razy. Możemy go zapisać raz.
Algorytmy kompresji
Silnik Vertipaq ma zaimplentowane dwa podstawowe algorytmy kompresji (a przynajmniej te zostały udokumentowane). Pierwszym z nich jest RLE (Run Lenght Encoding). Jego zasada działania polega na ustaleniu, gdzie zaczyna się dana wartość i ile kolejnych rekordów taką samą wartość posiada. Spójrzmy na ilustrację: wartość 1999 występuje 3 razy pod rząd, ale po kompresji silnik wie, że ta wartość występuje między pierwszym a trzecim rekordem i nie musi już czytać każdego wiersza. Wyobraźmy sobie, że chcemy zliczyć wszystkich klientów, którzy urodzili się w 1999 roku, po kompresji nie musimy odczytywać wszystkich rekordów. Odczytujemy wartości z tabelki pomocniczej i gotowe!
Drugim sposobem kompresji jest algorytm słownikowy. Zasada działania jest taka, że długie wartości, zastępujemy krótszą i dodajemy mapowanie w tabelce słownikowej. W naszym przypadku nie będziemy przechowywać wartości 1999, która ma 4 znaki, a jedynie wartość 1, która ma tylko 1 znak. Jeśli weźmiemy pod uwagę, że mamy klika milionów takich rekordów, zysk staje się znaczny.
Jak w pełni wykorzystywać silnik Vertipaq?
Generalnie jest to temat dosyć złożony, ale chciałbym w skrócie opisać, w jaki sposób pracować z silnikiem Vertipaq – w kontekście zarówno Excela, jak i Analysis Services. Oto 3 zasady, do których zawsze warto się stosować, aby nasze rozwiązanie działało wydajnie:
- Wykorzystuj zawsze najbardziej optymalny typ danych – np. liczby przechowuj jako liczby, a nie jako ciągi znaków;
- Staraj się unikać unikatowych wartości w kolumnach. W kontekście Vertipaq im więcej powtarzalnych wartości, tym lepsza kompresja, a co za tym idzie – wydajniejsze działanie.
- Pobierając dane (czy to w tabeli przestawnej, czy w zapytaniu do Analysis Services), korzystaj tylko z niezbędnych kolumn.
Ciekawostka na zakończenie - prototypowanie w Power Pivot
Jeżeli chcielibyśmy przenieść model zbudowany w Power Pivot do Analysis Services, to jest to jak najbardziej możliwe – więcej informacji tutaj. Takie rozwiązanie wykorzystywane jest do szybkiego prototypowania. Dzięki temu możemy podzielić się naszym rozwiązaniem z innymi współpracownikami 🙂