OLAP w praktyce Szymon Supik dyrektor ds technologii

OLAP w praktyce Szymon Słupik dyrektor ds. technologii CDN S. A. grupa Comarch

CDN XL – nasz flagowy produkt

CDN XL – nasz flagowy produkt l Podsystem „Controlling” l l l Rozwiązanie klasy Business Intelligence 100% implementacji w oparciu o Microsoft Analysis Services 2000 Ponad 70 wymiarów współdzielonych Kilkanaście kostek dziedzinowych Pełna implementacja zapisu do kostek (writeback) Dział aplikacji Business Intelligence l Hurtownie danych <pod klucz>, powiązane z systemami analitycznymi opartymi o Microsoft Analysis Services

Plan l Optymalizacja OLAP konstrukcji kostek l Wymiary, fakty i schemat gwiazdy l Wymiar czasu l Optymalizacja l Zarządzanie pracy serwera OLAP pamięcią l Agregacje l Bezpieczeństwo danych

Wymiary: prywatne i współdzielone l Wymiary – prywatne czy współdzielone? l l Prywatne – są zawsze przetwarzane z kostką Współdzielone – procesowane wtedy, gdy jest to konieczne Hierarchie – czy warto? l Zalecenie: wymiary współdzielone z hierarchiami l l Nie da się zmienić prywatnego na współdzielony Decydując się na wymiar prywatny trudno przewidzieć czy kiedyś nie będzie potrzeby wykorzystania go w kilku kostkach Zmiana nazwy wymiaru (wprowadzenie hierarchii) jest kosztowna (przeróbki istniejących raportów)

Wymiary - konstrukcja l Gdzie nas obciążają? l l l Jak optymalizować? l l l Pamięć Etap procesowania kostek (JOINy do faktów) Unikalne klucze (member keys) Klucze numeryczne, nie znakowe Wymiary wielopoziomowe / hierarchie l l l Pełny płatek śniegu Osobne tabele dla każdego poziomu Więzy integralności – programowanie defensywne

Wymiar czas – podejście tradycyjne l Kolumna <datetime> w tabeli faktów l l Zalety l l l Wizard automatycznie buduje wymiar czasu Prostota rozwiązania, wsparcie w narzędziach Metoda podawana na większości szkoleń dla początkujących Wady l l l Budowa wymaga skanowania całej tabeli faktów Brak osi czasu dla prognoz / budżetowania Problemy związane z funkcją Parallel. Period l Gdy rok nie zaczyna się od 1 go stycznia

Wymiar czas – podejście profesjonalne Osobna tabela np. WYM_Czas l Generowana procedurą składowaną l l sp_generuj_czas <od> <do> Relacja FKT. Dzien_ID<<->WYM_Czas. Dzien_ID l Zalety l l l Możemy wygenerować oś czasu za dowolny okres Unikamy problemów z Parallel. Period Mamy podstawę czasu do prognozowania i budżetowania Wady l Większa komplikacja rozwiązania

Wymiar czas: po polsku

Optymalizacja schematu l W hurtowni danych l l Indeks na każdym PK i FK W serwerze OLAP l “Optimize Schema” w celu minimalizacji JOINów SELECT Dim 1. dim 1 key, SELECT Fact. Table. dim 1 key, Dim 2. dim 2 key, Fact. Table. dim 2 key, Dim 3. dim 3 key, Fact. Table. meas 1, Fact. Table. meas 2, FROM Fact. Table, Fact. Table Dim 1, Dim 2, Dim 3 WHERE (Fact. Table. dim 1 key=Dim 1. dim 1 key) AND (Fact. Table. dim 2 key=Dim 2. dim 2 key) AND (Fact. Table. dim 3 key=Dim 3. dim 3 key)

Optymalizacja schematu kostki l Jest możliwa gdy… l l Wymiar jest współdzielony Kolumna „Member Key” na najniższym poziomie wymiaru jest zgodna z kluczem zastosowanym w tabeli faktów Kolumna „Member Key” na najniższym poziomie jest unikalna Najniższy poziom wymiaru w kostce nie jest wyłączony (disabled)

Optymalizacja agregacji l Wymiary - Depends on Dimension property l l l W edytorze wymiarów Wpływa na optymalizację agregatów wymiarów zależnych Kostki - usage-based optimization l l l Serwer zapisuje dziennik zapytań (query log) Usage-based optimization kreuje najefektywniejszy zestaw agregatów używanych dla większości zapytań Błąd konstrukcyjny – w przypadku >64 wymiarów

Co mamy w pamięci? l Wymiary… l l l …są ładowane do pamięci przy starcie serwera …wszystkie …ze wszystkich baz na serwerze …razem z właściwościami (member properties) Jak sobie z tym radzić? l l l Uważać na member properties - np. URL: Char(256) Odpinać nieużywane bazy (np. kopie testowe itp. ) Wymiary wirtualne nie obciążają pamięci l Są agregowane w locie, niezła wydajność przy niskiej kardynalności (niewiele elementów)

Procesowanie kostek l Faza bazowa l l Faza indeksowania l l Odczytuje dane z serwera SQL Sortuje dane w pamięci Zapisuje dane w 64 KB segmentach na dysku Odczytuje segmenty danych i buduje indeksy Faza agregacji l l Budowa agregatów w pamięci W przypadku braku pamięci wykorzystanie pliku TMP

Co mamy w pamięci? l Bufor procesowania l l Zwiększa wydajność wszystkich faz procesowania W fazie bazowej akumuluje rekordy l l l Faza indeksacji i agregacji l l l Zwiększa ilość wierszy w segmencie Pozwala na lepszą kompresję danych Więcej pamięci do wyliczania agregatów Zmniejsza wykorzystanie plików tymczasowych Ustawiany we właściwościach serwera

Co mamy w pamięci? l O ile zwiększać bufor procesowania l Za duży powoduje paging pamięci (OS) l Za mały generuje pliki tymczasowe (AS) Pamięć serwera Analysis Services Wymiary Bufor Procesowania Bufor Read Ahead

Miara <Distinct Count> l Distinct Count jest miarą nieaddytywną l l Podstawowa zasada – nie mieszać l l l Na każdym poziomie musi być wyliczana na podstawie faktów bazowych i tam jest zapamiętywana Dodanie miary DC do kostki mającej inne miary powoduje eksplozję agregatów Dla miar DC robimy oddzielne, dedykowane kostki, spinane z kostką główną poprzez mechanizm kostek wirtualnych Ograniczenie l W kostce może być tylko 1 miara DC

Metoda przetwarzania kostek l Process database l l Przetwarza całą bazę danych, w jednej transakcji Zalecana metoda Wymaga 2 x. RAM, 2 x. Dysk Process dimension + process cube l l Przetworzenie wymiaru może skutkować inwalidacją kostek i brakiem dostępności danych analitycznych Dobra metoda do diagnostyki problemów

Hurtownia i kostki (teoria) l Na jednej maszynie l l l + Brak konieczności transferu danych przez sieć + Hurtownia nie obciąża serwera OLTP - Rywalizacja o zasoby między SQL i OLAP Należy ręcznie ustawić obszar pamięci SQL Na osobnych maszynach l l l - Transfer danych przez sieć (ale sieci są szybkie ) - Hurtownia obciąża serwer OLTP (ale w nocy ) +Brak rywalizacji o zasoby między SQL i OLAP

Hurtownia i kostki (praktyka) l Doświadczenia z instalacji produkcyjnych l l OLAP bardzo obciąża serwer, zwłaszcza w procesie przetwarzania Dokładnie w tym samym czasie dociążany jest serwer SQL (bardzo złożone zapytania, generujące długie resultsety) Silna rywalizacja o pamięć – często kończy się nadmiernym pagingiem Warto rozważyć jeden wspólny serwer dla OLTP i hurtowni l W typowej firmie każda z tych baz jest dociążana w innym okresie (OLTP w ciągu dnia, hurtownia w nocy).

Windows 2003 Server l Wstępne wyniki testów (w laboratoriach Comarch-CDN) l l Wyraźny wzrost wydajności serwera SQL 2000 pracującego na platformie Windows 2003 Server Duży wzrost wydajności aplikacji przetwarzających dane, pracujących na Windows 2003 Server W pewnych scenariuszach obserwujemy wypadkowy wzrost wydajności (serwer SQL + aplikacja) rzędu 200% i więcej Oficjalne dokumenty Microsoft l l Usprawnione mechanizmy schedulera, podsystemu I/O, adresowania AWE http: //www. microsoft. com/sql/techinfo/planning/winsvr 2003 benefit s. asp

Bezpieczeństwo danych l l l Model bezpieczeństwa oparty o Active Directory Koncepcja ról rzutowanych na użytkowników / grupy Role definiowane na poziomie bazy danych Przypisywanie ról do kostek Mechanizmy uprawnień l l Przypisanie roli do poszczególnych kostek Definiowanie wycinka wymiaru dostępnego dla roli Cell security – dla wymagających Testowanie ról

Pamięć a dimension security l W pamięci umieszczane są cieniowe kopie wymiarów l l l Po jednej kopii dla każdej roli zawierającej <dimension security> Kopia zawiera elementy dozwolone oraz ich poprzedników (ancestors) Pojawia się w pamieci w momencie podłączenia się pierwszego użytkownika korzystającego z roli Zostaje w pamięci do momentu procesowania kostki (lub restartu serwera) Scenariusz kostek połączonych (Linked Cubes)

Podsumowanie l Dobre przygotowanie aplikacji opartej o Microsoft Analysis Services wymaga l Znajomości praw fizyki l l Dobrego zaplanowania fundamentów l l l Schemat gwiazdy Schemat nazewnictwa wymiarów Defensywnego podejścia do projektu l l l Np. czym się różni pamięć RAM od dysku Więzy integralności Diagnostyka błędów Iteracyjnego rozwoju l Monitorowanie Zmiany Testy Monitorowanie …

Na koniec: ciekawostka l sp_addlinkedserver @server = ‘Linked. OLAP', @provider = 'MSOLAP‘, @provstr= 'Data Source=My. Server; Initial Catalog=My. Olap. Db; ', @srvproduct = '' l SELECT * FROM OPENQUERY(Linked. OLAP, 'SELECT [Kontrahent. Akwizytor: Akronim], Sum ([Przychód]) FROM [Analiza Sprzedaży] WHERE [Czas. Kalendarzowy: Rok]=2003 GROUP BY [Kontrahent. Akwizytor: Akronim]')

Bibliografia l The l Data Warehouse Lifecycle Toolkit Ralph Kimball l microsoft. public. sqlserver. olap l msnews. microsoft. com l SQL l Server 2000 Resource Kit Rozdział 26 l White l papers (http: //msdn. microsoft. com/) Analysis Services: Optimizing Cube Performance Using Microsoft SQL Server 2000 Analysis Services l MSDN