W poprzednim artykule opisywałem Analysis Services – było to takie teoretycznie wprowadzenie, w którym opowiedziałem o podstawach wielowymiarowej analizy danych z wykorzystaniem Tabulara. Dzisiaj chciałbym przejść do praktyki i zaprezentować ważne z punktu widzenia dewelopera funkcjonalności tej usługi, które pomogą zbudować jeszcze wydajniejsze rozwiązanie. Warto wspomnieć, że funkcjonalności, które będę opisywał, działają zarówno w Azure Analysis Services, jak i w SQL Server Analysis Services (w trybie tablicowym). Zaczynajmy!
Miary i kolumny kalkulowane
Miara kalkulowana jest jednym z podstawowych elementów modelu Analysis Services i można ją zdefiniować w języku DAX – definicja określa w jaki sposób wartości w konkretnej kolumnie mają być agregowane. Miary są niezbędne do analizy danych. Oprócz takich operacji jak sumowanie, mnożenie i dzielenie wartości, możliwe jest tworzenie rankingów czy wybieranie pierwszych n wartości. Co najważniejsze, dodanie czy usunięcie nowej miary nie wymusza konieczności procesowania modelu, a w przypadku dużych rozwiązań jest to bardzo istotne. Miary są liczone „w locie”, co oznacza, że w chwili użycia silnik bierze definicję i według niej agreguje wartości w kolumnie.
Analysis Services oferuje także możliwość tworzenia kolumn kalkulowanych. Jest to nic innego jak dodanie do modelu nowej kolumny, która jest wyliczana na podstawie definicji języka DAX. Czym różni się to od miary kalkulowanej? Otóż kolumna jest materializowana w chwili procesowania, co oznacza, że silnik wykonuje działanie i dodaje wartość do modelu. Na ogół lepiej jest dodawać kolumny na poziomie bazy danych i tylko pobierać wartości podczas procesowania, ale są pewne sytuacje, w których jest to niemożliwe.
Zatem kiedy korzystać z miar, a kiedy z kolumn kalkulowanych? Spójrzmy na przykład:
W tabeli znajdują się dane sprzedażowe. Mamy tu takie pola jak nazwa przedmiotu, marka, cena jednostkowa i liczba kupionych sztuk. Wszystkie pola zaznaczone na niebiesko, są pobrane z bazy danych. Załóżmy, że zachodzi potrzeba grupowania towaru po złączeniu nazwy i marki. W takiej sytuacji, jeżeli nie możemy nic dodawać do naszej bazy danych, możemy posłużyć się kolumną kalkulowaną. Zaznaczona na zielono kolumna jest kalkulowana, co oznacza, że w trakcie procesowania modelu generowane są w niej wartości będące połączeniem marki i nazwy przedmiotu, z myślnikiem pomiędzy.
A co w sytuacji gdy chcemy podliczyć wartość sprzedaży? Musimy utworzyć miarę kalkulowaną – zaznaczyłem ją na fioletowo. Z punktu widzenia modelu jest to po prostu mnożenie ceny jednostkowej przez liczbę kupionych sztuk. Istotne w tym jest to, że te wartości nie są fizycznie składowane w modelu, ale wyliczane „w locie”, dzięki czemu, w przypadku hierarchii, na każdym jej poziomie otrzymamy poprawną wartość. Jak pokazuje przykład – jeżeli chcemy grupować po jakiejś wartości i nie możemy jej dodać na poziomie widoku danych w bazie, dodajemy ją jako kolumnę kalkulowaną. Jeżeli potrzebujemy agregacji wartości, wtedy należy napisać miarę.
Relacje
Jest to swego rodzaju oddziaływanie między tabelami. Warto wiedzieć, że mamy dwa typy relacji: jeden do wielu lub jeden do jednego.
Oprócz tego w relacji możemy określić także kierunek filtrowania – jednostronny lub obustronny.
Jak relacje działają w praktyce? Spójrzmy na przykład. Między tabelami DimCurrency a FctCurrencyRate mamy relację jeden do wielu, z filtrowaniem jednostronnym. Oznacza to, że jeśli wybierzemy konkretną walutę to silnik „wytnie” wszystkie inne waluty i nie będą one widoczne ani uwzględniane w obliczeniach (chyba, że zastosujemy pewne funkcje języka DAX). Z drugiej strony mamy relację między tabelami FctCurrencyRate i DimDate, ale w tym wypadku filtrowanie zostało ustawione jako dwukierunkowe, co oznacza, że jeśli odfiltrujemy jakieś konkretne wartości w atrybucie CurrencyRate to i odpowiadające im wartości w DimDate zostaną wyfiltrowane. Tak samo działa to w drugą stronę. Funkcjonalność dwukierunkowego filtrowania jest bardzo przydatna, ale czasami możemy napotkać problemy z wydajnością. Dlatego warto używać jej świadomie i tylko wtedy, gdy jest to konieczne.
Typy procesowania modelu tablicowego
Warto zacząć od tego czym jest procesowanie. Otóż jest to załadowanie danych do bazy analitycznej i wykonanie niezbędnych obliczeń. Aby być bardziej precyzyjnym, na tę czynność składają się takie elementy, jak:
- Pobranie danych ze źródła
- Kompresja wartości
- Umieszczenie wartości w pamięci
- Przeliczenie relacji
Silnik oferuje nam kilka rodzajów procesowania bazy, tabeli i partycji:
- Process Default (b,t,p) – tryb domyślny, który sam powinien określić co ma przeprocesować;
- Process Full (b,t,p) – procesowanie całościowe. W trakcie procesowania baza/tabela/partycja jest dostępna online, dane są pobierane, a relacje przeliczane. Wadą tego trybu jest znaczny wzrost zapotrzebowania pamięci. Zużycie wzrasta nawet trzykrotnie. Dzieje się tak, ponieważ tworzony jest jakby drugi obiekt na czas procesowania, który po zakończeniu jest podmieniany, przez co baza dostępna jest przez cały czas procesowania;
- Process Clear (b,t,p) – wyczyszczenie obiektu, wszystkie dane są usuwane;
- Process Recalc (b) – przeliczane są tylko relacje;
- Process Defrag (b,t) – ponowne procesowanie słowników w celu ich optymalizacji;
- Process Add (p) – procesowanie przyrostowe, pobrane zostaną nowo dodane wiersze;
- Process Data (t,p) – ładowane są tylko dane. Nie kalkulowane są ani hierarchie, ani kolumny kalkulowane, ani relacje.
To, kiedy wybrać jaki tryb procesowania pokazuje poniższa tabela:
Model mały (<100k wierszy) | Model średni (<10M wierszy) | Model duży (>10M wierszy) | |
Inicjalne Procesowanie Modelu | Process Full | Process Full, Process Data + Process Recalc | Process Data + Process Recalc (najlepiej na poziomie partycji/ procesowanie równoległe n partycji) |
Procesowanie Przyrostowe Modelu | Process Full | Process Full, Process Data + Process Recalc | Process Full, Process Data + Process Recalc (najlepiej na poziomie partycji/ procesowanie równoległe n ostatnich partycji) |
Dodano nową tabelę | Process Full | Process Full, Process Data + Process Recalc | Process Data + Process Recalc (najlepiej na poziomie partycji/ procesowanie równoległe n partycji) |
Dodano nową kolumnę | Process Full | Process Full, Process Data + Process Recalc | Process Data + Process Recalc (najlepiej na poziomie partycji/ procesowanie równoległe n partycji) |
Dodano nową miarę | Procesowanie nie jest konieczne | Procesowanie nie jest konieczne | Procesowanie nie jest konieczne |
Dodano nową hierarchię | Process Full | Process Full | Process Recalc/ Process Full jeśli tabela jest mała (na przykład wymiar) |
Dodano nową relację | Process Recalc | Process Recalc | Process Recalc |
Ukryto miarę | Procesowanie nie jest konieczne | Procesowanie nie jest konieczne | Procesowanie nie jest konieczne |
UWAGA: Jeśli model może być niedostępny na czas procesowania, można korzystać z opcji Process Data + Process Recalc. Jeśli nie mamy takiej możliwości, musimy wykorzystać opcję Process Full.
Partycjonowanie
Ta funkcjonalność jest szczególnie przydatna przy dużych bazach, gdzie ładowanie danych trwa długo i nie można sobie pozwolić na codzienne procesowanie całego modelu. W takich sytuacja można podzielić tabelę na partycje. W zależności od potrzeb mogą one być dzienne, miesięczne, roczne, dotyczyć zupełnie innego okresu lub w ogóle nie być związane z czasem. Główna idea takiego rozwiązania to utrzymanie modelu. Chodzi o to, że dzięki partycjom nie musimy procesować całej tabeli, a na przykład tylko ostatnie dwa tygodnie. Co więcej, jeżeli w bazie, z której pobieramy dane, mamy także partycje, proces ładowania może być szybszy – dzięki optymalnym planom zapytań.
Jeśli chodzi o wydajność zapytań do bazy analitycznej to teoretycznie liczba partycji nie powinna mieć znaczenia. Ważne, aby partycjonowanie dobrać optymalnie – im większa partycja, tym lepsza kompresja, a model zajmuje mniej miejsca. Z drugiej strony im większa partycja, tym ładowanie danych trwa dłużej (ze względu na większą liczbę wierszy do pobrania).
Row Level Security
Ta funkcjonalność jest szczególnie przydatna jeżeli w naszej organizacji mamy różne departamenty bądź marki, które nie powinny widzieć wzajemnie informacji o swojej sprzedaży. Co istotne, dodanie takiego filtra jest bardzo proste. Jedyne co musimy zrobić, to utworzyć role dla każdego departamentu czy marki, i na poziomie tabeli dodać wyrażenie w języku DAX będące filtrem. Dzięki temu bardzo łatwo można zarządzać uprawnieniami na poziomie dostępu do danych. W przypadku gdyby była potrzeba ukrycia konkretnych tabel w zależności od departamentu, konieczne będzie utworzenie perspektyw.
To by było na tyle 🙂 Mam nadzieję, że ten artykuł pomoże Ci budować jeszcze lepsze modele z wykorzystaniem Analysis Services. Jeżeli chciałbyś więcej tego typu postów – zachęcam do zostawienia komentarza.