OLAP w praktyce

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

2. CDN XL – nasz flagowy produkt

3. CDN XL – nasz flagowy produkt • Podsystem „Controlling” • 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 • Hurtownie danych <pod klucz>, powiązane z systemami analitycznymi opartymi o Microsoft Analysis Services

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

5. Wymiary: prywatne i współdzielone • Wymiary – prywatne czy współdzielone? • Prywatne – są zawsze przetwarzane z kostką • Współdzielone – procesowane wtedy, gdy jest to konieczne • Hierarchie – czy warto? • Zalecenie: wymiary współdzielone z hierarchiami • 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)

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

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

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

9. Wymiar czas: po polsku

10. Optymalizacja schematu • W hurtowni danych • Indeks na każdym PK i FK • W serwerze OLAP • “Optimize Schema” w celu minimalizacji JOINów SELECT Dim1.dim1key, Dim2.dim2key, Dim3.dim3key, FactTable.meas1, FactTable.meas2, FROM FactTable, Dim1, Dim2, Dim3 WHERE (FactTable.dim1key=Dim1.dim1key) AND (FactTable.dim2key=Dim2.dim2key) AND (FactTable.dim3key=Dim3.dim3key) SELECT FactTable.dim1key, FactTable.dim2key, FactTable.dim3key, FactTable.meas1, FactTable.meas2, FROM FactTable

11. Optymalizacja schematu kostki • Jest możliwa gdy… • Wymiar jest współdzielony • Kolumna „Member Key”na najniższym poziomiewymiarujest 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)

12. Optymalizacja agregacji • Wymiary - Depends on Dimension property • W edytorze wymiarów • Wpływa na optymalizację agregatów wymiarów zależnych • Kostki - usage-based optimization • 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

13. Co mamy w pamięci? • Wymiary… • …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ć? • Uważać na member properties - np. URL:Char(256) • Odpinać nieużywane bazy (np. kopie testowe itp.) • Wymiary wirtualne nie obciążają pamięci • Są agregowane w locie, niezła wydajność przy niskiej kardynalności (niewiele elementów)

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

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

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

17. Miara <Distinct Count> • Distinct Count jest miarą nieaddytywną • Na każdym poziomie musi być wyliczana na podstawie faktów bazowych i tam jest zapamiętywana • Podstawowa zasada – nie mieszać • 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 • W kostce może być tylko 1 miara DC

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

19. Hurtownia i kostki (teoria) • Na jednej maszynie • + 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 • - 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

20. Hurtownia i kostki (praktyka) • Doświadczenia z instalacji produkcyjnych • 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 • W typowej firmie każda z tych baz jest dociążana w innym okresie (OLTP w ciągu dnia, hurtownia w nocy).

21. Windows 2003 Server • Wstępne wyniki testów (w laboratoriach Comarch-CDN) • 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 • Usprawnione mechanizmy schedulera, podsystemu I/O, adresowania AWE • http://www.microsoft.com/sql/techinfo/planning/winsvr2003benefits.asp

22. Bezpieczeństwo danych • 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ń • Przypisanie roli do poszczególnych kostek • Definiowanie wycinka wymiaru dostępnego dla roli • Cell security – dla wymagających • Testowanie ról

23. Pamięć a dimension security • W pamięci umieszczane są cieniowe kopie wymiarów • 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)

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

25. Na koniec: ciekawostka • sp_addlinkedserver @server = ‘LinkedOLAP', @provider = 'MSOLAP‘, @provstr= 'Data Source=MyServer; Initial Catalog=MyOlapDb;', @srvproduct = '' • SELECT * FROM OPENQUERY(LinkedOLAP,'SELECT [Kontrahent.Akwizytor:Akronim], Sum ([Przychód])FROM [Analiza Sprzedaży]WHERE [Czas.Kalendarzowy:Rok]=2003GROUP BY [Kontrahent.Akwizytor:Akronim]')

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