Design SSAS cubes like a pro!

W poniedziałek 13 czerwca miałem okazję uczestniczyć w bardzo ciekawej sesji Sławka Drzymały z zaprzyjaźnionego bloga seequality.net w ramach programu BiteIT firmy JCommerce z Katowic. Temat sesji to Design SSAS cubes like a pro. Kilka myśli, które spisałem sobie po sesji to:

  • dane w katalogu OLAP/data to nie są żadne wielowymiarowe struktury danych, ale fizyczne pliki płaskie na dysku; kostka natomiast stanowi tylko logiczny model danych
  • agregacje domyślnie nie są tworzone w projekcie SSAS w VS!
  • w SSAS można korzystać z typu danych binary, ale tylko jako value
  • można utworzyć kostkę z templatki, co jest bezużyteczne 😉
  • Data Source View w kostce nie jest wymagane
  • zawsze należy korzystać z odpowiedniego sterownika przy odczycie źródła danych (dla SQL Server jest to Microsoft OLE DB Provider for SQL Server lub SQL Server Native Client), warto też zaznaczyć packet size 32767 (zwłaszcza dla odczytu i zapisu na różnych serwerach), warto korzystać z service account w ustawieniach impersonifikacji
  • procesowanie kostki to tak na prawdę nic innego jak odczytywane danych źródłowych i obliczanie agregacji
  • bzdurne wartości w zagregowanych miarach (np. ujemna suma dodatnich wartości) mogą występować w przypadku przekroczenia zakresu typu danych (np. int zamiast biginta) – najlepiej w takim przypadku albo zmienić typ danych w źródle, albo dokonać rzutowania typu int za pośrednictwem widoku
  • ustawienie wszędzie takiego samego Collation w projekcie SSAS uchroni nas przed niewydajnym rzutowaniem stringów
  • zmiana domyślnego ustawienie opcji ProcessingGroup ByTable może przyspieszyć procesowanie
  • ustawienie ProcessingMode na LazyAgreggations udostępnia wymiar jeszcze przed obliczeniem agregacji, odczyt oczywiście jest wolniejszy, bo agregacje jeszcze nie są policzone; ProcessingMode ustawione na Regular pozwala na dostęp dopiero po wykonaniu agregacji i jest to domyślne ustawienie
  • hierarchie typu Parent-Child najlepiej jest znaturalizować; można do tego wykorzystać dodatek BIDS Helper (https://bidshelper.codeplex.com)
    opcja StringStoresCompatibilityLevel pozwala na ustalenie maksymalnej powierzchni przeznaczonej na przechowywanie wszystkich stringów danego wymiaru (1050 – 4 GB, 1010 8 GB)
  • dostęp do źródeł danych innego typu gwarantują pliki XSL w lokalizacji OLAP/bin/cartridges, których zadaniem jest „tłumaczenie” kodu T-SQL
    SQL Server Analysis Services pozwala na automatyczne grupowanie atrybutów dzięki opcji DiscretizationMethod
  • bardzo ciekawą opcję stanowi CustomRollup, która pozwala na szczegółowe decydowanie o tym jak powinny zachowywać się agregacje w kostkach (przydatne zwłaszcza w finansach)
  • ustawienie opcji relacji Rigid daje minimalny wzrost wydajności, a nigdy nie możemy być pewni, że nie nastąpi jakaś zmiana w wymiarze (przydatne jedynie np. dla wymiaru czasu)

Nie mogę się doczekać kolejnej sesji! 🙂

8 Comments Add yours

  1. PitB napisał(a):

    Witaj!
    Tak trochę off topic:
    Stronka (seequality.net) jest bardzo ciekawa. Już po małym przeglądzie trafiłem na materiał, który na moim poziomie (rookie) jest interesujący 🙂 Dzięki, że masz rękę na pulsie i karmisz nas ciekawostkami i … linkami:)
    Pozdrawiam
    Piotr

  2. piotrziuzianski napisał(a):

    Bloga seequality prowadzą moi przyjaciele po fachu, którzy są na prawdę wysokiej klasy Specjalistami 🙂 Polecam koniecznie 🙂

  3. Gość napisał(a):

    Czy to znaczy że kostki OLAP są jedynie logicznymi tworami generowanymi w danej chwili (z tablicy faktów i tablic wymiarów) dla analityka biznesowego, a nie mogą służyć do trwałego, fizycznego przechowywania obrobionych danych, w hurtowni albo poza nią, w jakimś oprogramowaniu do analizy BI?

    1. piotrziuzianski napisał(a):

      Generalnie rzecz ujmując, kostki OLAP najczęściej faktycznie przechowują dane. W moim wpisie chodziło mi tylko o to, że nie są to jakieś nie wiadomo jakie wielowymiarowe struktury, ale w rzeczywistości po prostu pliki płaskie przechowywane na dysku, które zawierają w sobie kombinacje przecięć wymiarów.
      Warto mieć też jednak na uwadze, że zasugerowane przez Ciebie podejście także może wystąpić.
      Wszystko jest zależne od tego, w jaki sposób został zaimplementowany wielowymiarowy model danych. Na dwóch biegunach są: ROLAP oraz MOLAP. W przypadku ROLAP-a dane przechowywane są w relacyjnej strukturze hurtowni danych. MOLAP zakłada, że wszystkie dane przechowywane są w bazie analitycznej. HOLAP to tryb mieszany.

  4. Gość napisał(a):

    Trochę mnie zaskoczyłeś, bo myślałem że kostki są raczej strukturą logiczną. No to od razu sobie teraz pomyślałem (popraw mnie proszę jeżeli źle myślę), że każde z tych rozwiązań może mieć „plusy dodatnie” i „plusy ujemne”. Bo jak ciągle coś zmieniamy w zapytaniach do hurtowni danych, czyli operujemy na różnych danych, to pewnie lepiej jest gdy te są przechowywane w stanie bardziej surowym w bazie. A gdy posługujemy się jakimś standardowym zestawem zapytań analitycznych, to pewnie lepiej będzie wstępnie obrobić dane i zapisać je jako kostkę? Wtedy tracimy na pewnym „zaszufladkowaniu” i częściowym „skompilowaniu” tych danych, ale zyskujemy na przykład na wydajności zapytań?
    Inna rzecz o którą chciałem Cię zapytać, to czy w swojej pracy jako specjalista BI, spotkałeś się z jakimiś szczególnymi zapytaniami do baz transakcyjnych, mającymi cechy analizy danych pod kątem BI? Chodzi mi właśnie o bazy transakcyjne np. SQL Server, a nie hurtownie danych. Jeżeli chodzi o przykłady, to przychodzą mi na myśl dwa, takie wzięte z sufitu, być może że kompletnie nietrafione:
    1) tabele przestawne tworzone z poziomu języka SQL – czy one mają jakieś większe zastosowanie w analizie BI, czy te typy tabel pozostawia się raczej dla Excela i narzędzi typowo dedykowanych do BI?
    2) złączenia tabeli z nią samą w celu znalezienia jakiś zależności pomiędzy danymi. Można wyobrazić sobie taki problem biznesowy: z tabeli produktów należy pobrać zestawy towarów z konkretnych kategorii, w których jeden z nich będzie co najmniej 10x tańszy od drugiego (bo sklep będzie dorzucać je jako gratisy do tych droższych). Takie polecenie można by zrealizować właśnie za pomocą złączenia tabeli z samą sobą, np.:

    SELECT t1.KategoriaID, t1.Cena, t2.KategoriaID, t2.Cena
    FROM Towar AS t1
    JOIN towar AS t2
    ON t1.KategoriaID = t2.KategoriaID
    WHERE t1.Cena * 10 < t2.Cena;

    Przykład jest dydaktyczny, ale być może nieżyciowy. Albo po prostu nie stosuje się go w analizie BI, szczególnie w przypadku baz transakcyjnych. Ale to tylko dwa przykłady aby zobrazować to, o co chodziło mi w pytaniu. Może są jakieś inne typowe/ciekawe zapytania służące do analiz dużych zbiorów danych? Bo zdaję sobie sprawę z tego, że typ zapytań w analizie BI jest całkiem odmienny od tych wykorzystywanych przy zwykłym używaniu bazy.

    1. piotrziuzianski napisał(a):

      Generalnym pomysłem producentów oprogramowania związanego z przechowywaniem danych i ich analizy jest oddzielnie warstwy transakcyjnej (operacyjnej) od warstwy analitycznej. U podstaw tego pomysłu leży fakt, że dane do analizy powinny być zupełnie inaczej poukładane niż dane do zapisu. Jest to bezpośrednio związane np. z tworzeniem indeksów. Zapis w tabeli, która posiada wiele indeksów jest długi, a operacje w wielkich systemach muszą spływać ciągle, on-line. Jeśli dołoży się do tego obciążenie, które generuje tworzenie zestawień możemy uzyskać efekt w postaci ciągłych problemów z zasobami/lockami, generalnie jeden wielki koszmar.
      Aby temu wszystkiemu zapobiec przyjęto, że należy posiadać w swojej organizacji dwa modele danych: zoptymalizowany pod kątem transakcji model relacyjny oraz model hurtowni w postaci gwiazdy czy też płatka śniegu zoptymalizowany pod kątem tworzenia zestawień analitycznych. Jeśli te dwa modele spoczywają na dwóch różnych serwerach, a synchronizacja zachodzi np. raz na dobę, to uzyskujemy bardzo przyzwoite wydajnościowe rozwiązanie: operacje spływają do modelu relacyjnego on-line, a analitycy korzystają ze zoptymalizowanego środowiska na innym serwerze.
      Kostka analityczna przechowuje tzw. agregacje, czyli wstępnie przeliczone sumaryczne wartości na różnych poziomach hierarchii. Dzięki temu, serwer nie musi za każdym razem przeliczać tych wartości, ale liczy je raz i są one dostępne dla wielu analityków.

      Znajomość SQL jest wg mnie podstawowym wyznacznikiem, czy specjalista BI jest dobrym czy złym specjalistą. Niestety, ale narzędzia typu SQL Server Integration Services, które służą do zaprojektowania i wdrożenia procesu ETL (czyli procesu ładowania hurtowni danych) nieumiejętnie używane mogą strasznie wydłużać proces ETL. A przecież proces ETL to nic innego jak przekopiowanie danych z jednego miejsca w drugie oraz jakaś ich transformacja. Koniec końców warstwą dostępu do źródła i tak jest… T-SQL 🙂 Dlatego czasem warto w samym SSIS korzystać z T-SQL zamiast dostępnych niewydajnych narzędzi SSIS.
      Nie wiem czy dobrze rozumiem pytania, ale… stworzenie hurtowni danych to wg mnie podstawa działania systemu BI w organizacji. I szczerze mówiąc, na tym etapie czasami się poprzestaje. Opracowanie spójnej, centralnej bazy danych, zoptymalizowanej pod kątem przeglądania oddzielonej od środowiska operacyjnego pozwala na tworzenie analiz z wykorzystaniem T-SQL. Niekoniecznie niezbędnym będzie tworzenie bazy analitycznej. Baza analityczna udostępnia natomiast wygodny interfejs do przeglądania danych (tabela przestawna) i ma przeliczone agregacje. To jest główny atut wg mnie.
      A odpowiadając bardziej konkretnie na zadane pytania:
      1.) Kostki MOGĄ być jedynie strukturą logiczną w modelu ROLAP, ale najczęściej przechowująw sobie dane (model MOLAP). Zyskujemy dzięki temu na wydajności. Zmiany w strukturze hurtowni muszą pociągać za sobą zmiany w kostce (chyba, że jest to tylko np. dodawanie nowych pól, które nie będą używane w kostce).
      2.) To czy lepiej poprzestać na samej bazie operacyjnej/hurtowni danych/bazie analitycznej – to wszystko jest uzależnione od specyfiki projektu. Nie ma chyba żadnego złotego środka.
      3.) Nie wykorzystuje T-SQL do typowo analitycznych zestawień, ale T-SQL ma do zaoferowania wiele funkcji, które można wykorzystać. Do samego grupowania można wykorzystać np. ROLLUP, CUBE, PIVOT. A mamy także funkcje do obliczania sum, wariancji, odchylenia standardowego… Kombinacja różnych możliwości samego języka T-SQL pozwala na wygenerowanie pożądanych raportów.
      4.) Tabele przestawne tworzone z poziomu języka SQL – nie do końca wiem, czy dobrze rozumiem, ale warto mieć na uwadze, że wykorzystując narzędzia Microsoft, np. PowerPivot i mając jako źródło SQL Server możemy przeprowadzić sprawną analizę danych – wszystko jest zależne od wielkości projektu. Czasami wielkość projektu pozwala na wykorzystanie narzędzi self-service BI i jest to w zupełności wystarczające. Czasami dla projektu idealne wydaje się być rozwiązanie chmurowe, a czasami Excel jest wystarczający 🙂 Nie wiem czy ktokolwiek jest w stanie wyznaczyć sensowne granice.
      5.) W SQL Serverze do odpytywania baz analitycznych wykorzystuje się albo MDX (dla kostek analitycznych) albo DAX (dla modelu tabelarycznego). Tak jak wspominałem, czasami zachodzi też potrzeba wydobycia danych z hurtowni za pomocą T-SQL i to też bywa bardzo pomocne. I kolejny raz muszę powiedzieć: zastosowanie odpowiedniego podejścia uzależnione jest głównie od projektu. Do odszukiwania zależności, o których napisałeś można chyba wykorzystać Data mining, z którym niestety jeszcze nie miałem przyjemności pracować 🙁

      1. Gość napisał(a):

        Bardzo ładnie to opisałeś w swoim komentarzu, dziękuję za wyjaśnienia. Ale jednocześnie to jest na tyle ciekawe, i myślę że ważne dla osób zajmujących się transakcjami lub analityką, że warto abyś to rozszerzył w oddzielnym wpisie.

        1. piotrziuzianski napisał(a):

          Myślę, że faktycznie mogłoby to być ciekawe dla niektórych. W wolnej chwili przygotuję wpis 🙂 Dzięki za pomysł 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *