OLAP w praktyce

OLAP w praktyce
Szymon Słupik
dyrektor ds. technologii
CDN S.A. grupa Comarch
CDN XL – nasz flagowy produkt
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
Plan
 Optymalizacja
konstrukcji kostek
OLAP
 Wymiary,
fakty i schemat gwiazdy
 Wymiar czasu
 Optymalizacja
 Zarządzanie
pracy serwera OLAP
pamięcią
 Agregacje
 Bezpieczeństwo
danych
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)
Wymiary - konstrukcja

Gdzie nas obciążają?



Jak optymalizować?



Pamięć
Etap procesowania kostek (JOINy do faktów)
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
Wymiar czas – podejście tradycyjne

Kolumna <datetime> w tabeli faktów


Zalety



Wizard automatycznie buduje wymiar czasu
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
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
Wymiar czas: po polsku
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,
SELECT FactTable.dim1key,
Dim2.dim2key,
FactTable.dim2key,
Dim3.dim3key,
FactTable.dim3key,
FactTable.meas1,
FactTable.meas1,
FactTable.meas2,
FactTable.meas2, FROM
FROM
FactTable,
FactTable
Dim1,
Dim2,
Dim3
WHERE (FactTable.dim1key=Dim1.dim1key) AND
(FactTable.dim2key=Dim2.dim2key) AND
(FactTable.dim3key=Dim3.dim3key)
Optymalizacja schematu kostki

Jest możliwa gdy…




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

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
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)
Procesowanie kostek

Faza bazowa




Faza indeksowania


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


Budowa agregatów w pamięci
W przypadku braku pamięci wykorzystanie pliku TMP
Co mamy w pamięci?

Bufor procesowania


Zwiększa wydajność wszystkich faz procesowania
W fazie bazowej akumuluje rekordy



Faza indeksacji i agregacji



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?

O ile zwiększać bufor procesowania
 Za duży powoduje paging pamięci (OS)
 Za mały generuje pliki tymczasowe (AS)
Pamięć serwera Analysis Services
Wymiary
Bufor Procesowania
Bufor
Read Ahead
Miara <Distinct Count>

Distinct Count jest miarą nieaddytywną


Podstawowa zasada – nie mieszać



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

W kostce może być tylko 1 miara DC
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
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
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).
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/winsvr2003benefit
s.asp
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
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)
Podsumowanie

Dobre przygotowanie aplikacji opartej o
Microsoft Analysis Services wymaga

Znajomości praw fizyki


Dobrego zaplanowania fundamentów



Schemat gwiazdy
Schemat nazewnictwa wymiarów
Defensywnego podejścia do projektu



Np. czym się różni pamięć RAM od dysku
Więzy integralności
Diagnostyka błędów
Iteracyjnego rozwoju

Monitorowanie  Zmiany  Testy  Monitorowanie  …
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]=2003
GROUP BY
[Kontrahent.Akwizytor:Akronim]')
Bibliografia

The Data Warehouse Lifecycle Toolkit


microsoft.public.sqlserver.olap


Rozdział 26
White papers (http://msdn.microsoft.com/)


msnews.microsoft.com
SQL Server 2000 Resource Kit


Ralph Kimball
Analysis Services: Optimizing Cube Performance Using Microsoft
SQL Server 2000 Analysis Services
MSDN