Projektowanie agregacji w Microsoft SQL Server Analysis Services 2005, cz. I
Zawartość strony
 | Wstęp |
| Zrozumieć agregacje |
| Ile kosztuje brak agregacji? |
| Jak fizycznie wyrażona jest agregacja? |
| Projektowanie i budowa agregacji |
| Sposoby przechowywania agregacji |
| Przeczytaj pozostałe części tego artykułu |
Wstęp
Podstawowym celem rozwiązań typu Online Analytical Processing (OLAP) jest szybkie dostarczanie zagregowanych danych. W tym aspekcie Microsoft SQL Server 2005 Analysis Services (SSAS) na dobre zagościło na rynku, nie mniej wielokrotnie pojawiają się pytania, co to są agregacje w ujęciu hurtownianym, do czego służą oraz jak wpływają na proces tworzenia oraz administrowania rozwiązaniami OLAP.
Zrozumieć agregacje
Wyobraźmy sobie typową sytuację: „chcemy uzyskać informacje o sprzedaży produktów kategorii X w roku 2006”. Gdy dana informacja jest składowana w postaci agregacji, zasoby zużywane są jedynie na jej odczyt, w innym przypadku potrzebne jest skanowanie tabeli faktów oraz jej grupowanie przy użyciu odpowiednich atrybutów z wymiarów produkt oraz rok. Tym samym wprost nasuwa się definicja: agregacja to prekalkulowane podsumowanie danych o określonych współrzędnych wyrażonych przez wymiary oraz ich atrybuty.
Aby zgłębić definicję należy najpierw zastanowić sie jak wygląda żądanie danych oraz wykorzystanie agregacji przez Storage Engine w SSAS:
| Tabela 1.1. Budowa SSAS Storage Engine | ||
| Storage Engine | ||
1) Storage Engine Cache |
|
|
2) Aggregations | Partycja 1 | Partycja n |
3) Fact Data |
|
|
1. | Najpierw odpytwany jest Storage Engine Cache – poszukiwany jest wynik do zapytania o takiej samej definicji jak nasza. |
2. | W przypadku braku gotowego wyniku sprawdzane są agregacje. |
3. | W przypadku braku poszukiwanej agregacji budowane jest ad-hoc żądane podsumowanie i zwracane jako wynik zapytania. |
Aby potwierdzić powyższy algorytm spójrzmy na poniższe zapytanie MDX oraz odczyty z SQL Server Profiler.
SELECT [Product].[Category].[Category] ON ROWS, [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] |
Rys. 1.2. Profiler – Brak agregacji, dane pobierane są z Fact Data.
Rys. 1.3. Profiler – Brak agregacji, dane pobierane są ze Storage Engine Cache.
Rys. 1.4. Profiler – Utworzona agregacja, dane pobierane są z Aggregation.
Rys. 1.5. Profiler – Utworzona agregacja, dane pobierane są ze Storage Engine Cache.
Śledząc wykonanie zapytań zauważymy regułę: jeśli zapytanie jest wykonywane pierwszy raz, wynik jest zwracany odpowiednio z Aggregations lub z Fact Data. W innym przypadku brany jest bezpośrednio ze Storage Engine Cache kilkuktrotnie zmniejszając czas trwania zapytania.
Ile kosztuje brak agregacji?
W przypadku gdy nie ma agregacji odpowiadającej określonemu zapytaniu wynik jest czerpany wprost ze skanowania partycji „Internet_Sales” z Fact Data (Rysunek 1.2). W innym przypadku wynik jest pobierany z konkretnej agregacji – w naszym przypadku „Aggregation a7” (Rysunek 1.4). Dla porównania czas trwania to odpowiednio 361 ms i 250 ms. Oznacza to w tym przypadku zapytanie wykorzystujące agregację jest aż o 44% szybsze (Wykres 1.6).
Wykres 1.6 Porównanie czasu trwania zapytań.
Jak fizycznie wyrażona jest agregacja?
Nawiązując do definicji, agregacja podsumowuje miary poprzez określone współrzędne atrybutów. Dla każdego atrybutu standardowo mamy do dyspozycji dwa poziomy: All oraz Attribute.
Podczas tworzenia agregacji SSAS oznacza je przy użyciu wektorów. Każdy wektor z kolei reprezentowany jest za pomocą 0 oraz 1 odpowiadających konkretnym atrybutom.
| Tabela 1.7 Przykładowe wymiary | ||
| DimProduct | DimCustomer | DimDate |
ProductKey *1000 members Subcategory *200 members Category *50 members | CustomerKey *100 members Gender *2 members City *15 members | DateKey *300 members Month *24 members Year *2 members |
Przykłady wektorów dla wymiaru DimProduct:
Agregacja utworzona na podstawie:
| • | ProductKey: 100 |
| • | Subcategory: 010 |
| • | Category: 001 |
W powyższych wektorach 0 odpowiada poziomowi All, natomiast 1 poziomowi Attribute.
Kontynuujemy przykład zapytania o sprzedaży produktów kategorii X w roku 2006. Potrzebujemy utworzyć agregację, która przyspieszy takie zapytanie.
Dla DimProduct będzie to 001, a dla DimDate również 001. Tym samym zostanie utworzony tzw. subcube – agregacja o współrzędnych (001,001). Liczba możliwych kombinacji dla tej agregacji to 50 kategorii x 2 lata = 100 rekordów.
Dla porównania, teoretyczna liczba rekordów na najniższym poziomie szczegółowości (ziarna) to iloczyn kartezjański wszystkich atrybutów:
1000 x 200 x 50 x 100 x 2 x 15 x 300 x 24 x 2 = 4,32 x (10^14)
Tak więc biorąc pod uwagę powyższe porównanie, wykorzystywanie agregacji, okazuje się bardzo uzasadnione.
Projektowanie i budowa agregacji
Skoro wiemy, że agregacje wpływają na polepszenie czasu oraz wydajności zapytań, dlaczego nie stworzyć wszystkich możliwych? Bazując na Tabeli 1.2 mamy trzy wymiary po trzy atrybuty każdy. Daje to nam 2 (liczba poziomów per atrybut – All oraz Attribute) ^ 9 (liczba dostępnych atrybutów) = 512 możliwych agregacji, z czego niektóre mogą być nieefektywne oraz przybierać zbyt duże rozmiary. Dzisiejsze rozwiązania OLAP to co najmniej kilkadziesiąt atrybutów, miliony rekordów – procesowanie oparte na wszystkich możliwych agregacjach mogłoby trwać setki godzin!
Microsoft proponuje dwa sposoby projektowania oraz tworzenia agregacji:
| • | Aggregations Design Wizard – narzędzie daje możliwość wyboru sposobu przechowywania i odświeżania agregacji (MOLAP, ROLAP, HOLAP) oraz wyboru ewentualnych kandydatów na podstawie algorytmu Cost/Benefit. |
| • | Usage Based Optimization Wizard – narzędzie do optymalizacji budowy agregacji na podstawie zebranych zapytań. Aby rozpocząć przechwytywanie (w tym przypadku do tabeli) należy odpowiednio skonfigurować właściwości SSAS – patrz Rysunek 1.3. |
Rys. 2.1. QueryLog – konfiguracja SSAS.
Obydwa w/w narzędzia są dostępne w SQL Server Management Studio (SSMS) oraz Business Intelligence Development Studio (BIDS).
Projektowanie agregacji musi wiązać się ze zdrowym rozsądkiem oraz rachunkiem kosztu vs zysku. Tak właśnie dzieje się w SSAS gdzie jest specjalny algorytm odpowiedzialny za selekcję agregacji. Opiera on się na dwóch filarach:
| • | Aggregation Cost – wskaźnik wyliczany na podstawie statystyk kostki tj. liczba rekordów w tabeli faktów oraz liczba unikalnych wartości (members) w wymiarach przypisanych do tabeli faktów. |
| • | Aggregation Benefit – wskaźnik ten jest wyliczany na podstawie stosunku liczby wierszy w agregacji do liczby wierszy, na podstawie których agregacja została stworzona – patrz przykład dla DimProduct. Ponadto ten wskaźnik służy odrzuceniu agregacji redundantnych lub nieefektywnych. Jednym z najbardziej znanych warunków uznania agregacji za nieefektywną jest agregacja zawierająca więcej niż 1/3 rekordów na poziomie ziarna. |
W przypadku, gdy chcemy przejąć w 100% kontrolę nad tworzeniem agregacji, należy użyć Aggregation Manager (więcej w kolejnym rozdziale Aggregation Manager). Manualne projektowanie agregacji może okazać się pracochłonne, lecz da nam pełną kontrolę nad ich strukturą oraz umożliwi stworzenie agregacji w pełni dostosowanych do używanych zapytań MDX. Co za tym idzie, wpłyniemy na czas potrzebny na procesowanie, redukując tym samym używane zasoby.
Sposoby przechowywania agregacji
Temat przechowywania agregacji jest bardzo obszerny i może stanowić podstawę napisania kilku innych artykułów. Nie mniej jednak ważne jest aby poznać dostępne w SSAS modele przechowywania partycji zawierających agregacje:
| • | Multidimensional OLAP (MOLAP) – zarówno dane źródłowe jak i agregacje są optymalizowane (kompresja rzędu 30% względem tradycyjnej bazy relacyjnej) i przechowywane w SSAS co powoduje, że zapytania są bardzo szybkie. W przypadku gdy agregacje nie są dostępne dane są pobierane wprost z Fact Data w SSAS. W czasie procesowania partycja nie jest dostępna do odczytu. |
| • | Relational OLAP (ROLAP) – agregacje oraz dane są przechowywane w bazie relacyjnej. Standardowo partycje ROLAP przechowują wymiary w SSAS natomiast same agregacje w bazie OLTP. ROLAP jest stosowany w rozwiązaniach OLAP czasu rzeczywistego. Wiąże się to jednak ze stratami wydajności zapytań (duża liczba joinów na Online Transaction Processing (OLTP)) oraz obciążeniem serwera. Zaleca się aby rozwiązania ROLAP opierać na aktualnej/nych partycji/ach (podlegającej zmianom danych) ROLAP, natomiast historyczne dane trzymać już na partycjach opartych o MOLAP. Należy pamiętać, iż ROLAP wiąże się z wieloma ograniczeniami – bardzo dobrze opisane w Books Online do którego lektury bardzo zapraszamy. |
| • | Hybrid OLAP (HOLAP) – agregacje są przechowywane w SSAS, natomiast Fact Data oparte są na OLTP. Rozwiązanie to powoduje, iż zapytania oparte na agregacjach są bardzo szybkie. W przypadku ich braku odpytywany jest Fact Data, po czym wynik umieszczany jest w Storage Engine Cache. Rozwiązanie stosowane w systemach OLAP czasu „prawie rzeczywistego” gdzie dane na poziomie ziarna rezydują na OLTP. Aby odświeżyć agregacje należy wykonać Process Index na partycji i mamy aktualne dane. Ze względu na wydajność bardzo ważne jest aby pamiętać o utworzeniu odpowiednich agregacji oraz indeksów na tabelach relacyjnych (fakty oraz wymiary) aby umożliwić SSAS szybki dostęp do danych. |
Przeczytaj pozostałe części tego artykułu
| • | Projektowanie agregacji w Microsoft SQL Server Analysis Services 2005, cz. II |
 | Daniel Arak (InspireTech) |
 | Łukasz Kieloch (InspireTech) |