Usługi analizy: poprawa wydajności kostki przy użyciu usług analitycznych Microsoft SQL Server 2000

Na podstawie powyższych pytań definiujemy odpowiednie źródło danych w istniejącym diagramie ER (diagram relacji jednostka-obiekt, diagram relacji-obiekt) bardzo dużej bazy danych (VLDB, Very Large DataBase). Tabele można zdefiniować w następujący sposób:

• Tabela produktów zawiera informacje o kontach bankowych (na przykład konto bieżące lub pilne).
• Tabela segmentu klientów daje możliwość podziału klientów banku na kategorie (na przykład deponenci lub dłużnicy).
• Tabela Okres zawiera dane okresów do obliczania wskaźników zysku. Przedmiotowa baza danych zawiera informacje przez 2 lata (1996 i 1997).
• Tabela Region zawiera informacje o położeniu geograficznym każdego banku.
• W tabeli Household wprowadzono dane dotyczące klientów, którzy mogą mieć wiele kont.

Aby uzyskać odpowiedzi na pytania dotyczące skuteczności produktu, opracowano diagram gwiazdkowy (dla kostki OLAP). Tabela Account_Prof_Fact została zbudowana z tabel dotyczących zwrotu na kontach dla wszystkich okresów (patrz rys. 2). Zawiera dane dotyczące rentowności danej usługi bankowej, na przykład miesięczne przychody i wydatki na różne produkty; innymi słowy tabela zawiera sumy wszystkich miar dla każdego miesiąca. Zidentyfikowano pięć wymiarów: produkt , okres , region , gospodarstwo domowe i segment klientów .

Rys.2. Schemat gwiazdy z faktami i pomiarami

Tworzenie i zapełnianie bazy danych SQL Server

Usługi transformacji danych (DTS) zostały użyte do wypełnienia tabel faktów i pomiarów w zbiorczej bazie danych. Tabele przechowujące dane dla każdego z okresów zostały połączone w jedną tabelę faktów, nazywaną fact_prof_fact i zawierającą informacje dla wszystkich 24 miesięcy. Tabela faktów zawiera około 13 milionów rekordów.

Baza danychLiczba wierszyRozmiar

Konto_Prof_Fakt 13,036,152 5188.00 MB CustSegmentDim 7 0,03 MB DomowyDim 200,001 38,56 MB ProductDim 14 0,03 MB RegionDim 51 0,04 MB TimeDim 24 0,03 MB
Budowanie kostek OLAP

Następnie utworzono wielowymiarową bazę danych OLAP, zwaną bazą danych AccountProfitabilityOLAPDatabase , która zawiera 12 kostek o tej samej strukturze, ale z różnymi trybami przechowywania i poziomami agregacji. Rysunek 3 przedstawia strukturę jednej z tych kostek.

Poniższa tabela zawiera krótki opis każdej z 12 kostek. Jak już wspomniano, kostki mają tę samą strukturę, ale ich tryby przechowywania i poziomy agregacji są różne.

Tytułowy kostkipoziom agregacjisposób przechowywania(w procentach)

AccountProfitabilityCubeM0 MOLAP 0 AccountProfitabilityCubeM30 MOLAP 30 AccountProfitabilityCubeM60 MOLAP 60 AccountProfitabilityCubeM90 MOLAP 90 AccountProfitabilityCubeH0 HOLAP 0 AccountProfitabilityCubeH30 HOLAP 30 AccountProfitabilityCubeH60 HOLAP 60 AccountProfitabilityCubeH90 HOLAP 90 AccountProfitabilityCubeR0 ROLAP 0 AccountProfitabilityCubeR30 ROLAP AccountProfitabilityCubeR60 ROLAP 30 60 90 AccountProfitabilityCubeR90 ROLAP

Wybraliśmy osiem miar z tych pokazanych na rys. 3. Podstawowa tabela faktów zawiera 13 milionów wierszy. Poniższa tabela zawiera opisy miar zawartych w kostkach.

który bank otrzymuje od klientów indywidualnych przez pewien okres. Dochód ekonomiczny oznacza zysk lub stratę netto z działalności operacyjnej pomniejszonej o podatki od zysków kapitałowych za określony okres czasu. Dochód z różnic stóp procentowych Dochód, który bank otrzymuje od klientów indywidualnych przez określony czas. Reprezentuje różnicę między poziomem stóp procentowych, przy których bank udziela pożyczki i akceptuje depozyty. Dochód w formie prowizji Dochód, który bank otrzymuje w formie prowizji od klientów indywidualnych przez określony czas. Rezerwy w przypadku braku zwrotu kredytów Rezerwa na przypadki braku zwrotu kredytów przez klientów indywidualnych. Koszty tego produktu Koszty związane z danym produktem przez określony czas. Koszty Koszty poniesione przez bank na określony czas. Zysk netto Zysk netto oznacza zysk / stratę netto z działalności operacyjnej pomniejszonej o podatki, koszty odsetek i dywidendy za określony okres czasu. Koszty transakcji Koszty transakcji ponoszone przez bank podczas interakcji z indywidualnymi klientami przez określony czas.

Poniższa tabela zawiera opisy pięciu wymiarów, które zostały uwzględnione w kostkach - w tym celu tabele wymiarów zostały użyte w zbiorach danych zorganizowanych w schemacie gwiazdy.

domoweDim 200001 2 19128 KB ProductDim 14 1 3 KB RegionDim 51 2 8 KB TimeDim 24 3 5 KB CustSegmentDim 7 1 2 KB

Rysunek 4 przedstawia dane kostki po przetworzeniu.

Przetwarzanie wyników dla każdego trybu przechowywania danych

Wszystkie kolejne wykresy są wykreślane dla poziomów agregacji 0%, 30%, 60% i 90%, chociaż w większości przypadków stosowane są tylko wartości od 30% do 60% (dla porównania uwzględniono 0% i 90%). Przypomnijmy, że poziom agregacji charakteryzuje wzrost wydajności przetwarzania zapytań w porównaniu z brakiem wstępnie obliczonych agregatów danych.

Czas przetwarzania dla każdego trybu przechowywania

Następujące wyniki uzyskano w wyniku przetwarzania kostek o identycznej strukturze z różnymi trybami przechowywania i poziomami agregacji.

Rys.5. Czas przetwarzania dla każdego trybu przechowywania

Zatem możemy wyciągnąć następujące wnioski:

• Przy poziomie agregacji 0% ROLAP przetworzenie kostki zajęło najmniej czasu. W tym przypadku dane tabeli faktów i pomiarów nie są dodawane do kostki, a agregaty nie są obliczane.
• Wraz ze wzrostem poziomu agregacji ROLAP - w porównaniu z MOLAP lub HOLAP - spędza więcej czasu na przetwarzaniu kostki.
• Różnica między MOLAP a HOLAP w przedziale 30–60% jest nieznaczna.
• Czas przetwarzania MOLAP i HOLAP wzrasta między 60% a 90%, ale tylko nieznacznie.
• Czas przetwarzania ROLAP rośnie wykładniczo w zakresie 60 - 90%.

Poniższy wykres pokazuje zmianę wymaganego miejsca na dysku w zależności od poziomu agregacji dla każdego trybu przechowywania.

Rys. 6. Wymagane miejsce na dysku
Patrząc na wykres, możemy stwierdzić, że:

• Tryb przechowywania MOLAP wymaga więcej miejsca niż HOLAP lub ROLAP. (Kostki MOLAP zawierają kopie faktów źródłowych i pomiarów).
• Różnica w ilości zużytego miejsca na dysku w trybach MOLAP i HOLAP jest nieznaczna w zakresie 0–60% i wzrasta, gdy zbliża się do poziomu 90%.
• Tryb przechowywania HOLAP wykorzystuje najmniejszą ilość miejsca na dysku. Dzieje się tak, ponieważ kopie oryginalnych faktów i pomiarów nie znajdują się w bazie danych OLAP, a agregaty są przechowywane w zoptymalizowanym formacie wielowymiarowym.
• Tryb przechowywania ROLAP wymaga dodatkowej przestrzeni dyskowej, gdy poziom agregacji przekracza 30%, a gdy osiągnie 90%. (Wykres uwzględnia ilość miejsca wymaganą do przechowywania agregatów danych w relacyjnej bazie danych).

Ta tabela pokazuje ilość miejsca na dysku wymaganą dla kostki MOLAP w porównaniu z oryginalnym schematem gwiazdy (tabela faktów i tabela pomiarów) w RDBMS.

Poziom agregacji (%) Miejsce nadysku dla kostki MOLAPRozmiar schematu gwiazdy (tabele faktów i pomiarów z indeksami)Współczynnik kompresji danych podczas budowania kostek MOLAP

60 335,75 5188 93,53 90 353,11 5188 93,19

Oczywiście przestrzeń zajmowana przez kostkę OLAP stanowi około 7% objętości wymaganej dla schematu gwiazdy. Nawet przy 90% poziomie agregacji możliwe jest uzyskanie prawie takiego samego stopnia kompresji. Dodatkowa przestrzeń wymagana do zbudowania kostki MOLAP zależy od liczby poziomów w wymiarze, liczby miar i typu danych.

Poniższa tabela przedstawia czas przetwarzania zapytań MDX i SQL (��Jaki jest dochód ekonomiczny za pierwsze kwartały 1996 i 1997 oraz różnica między nimi dla każdego segmentu?”).

Typprzetwarzania

MDX czas przetwarzania 4 sek. SQL 88 sek.

Oczywiście żądanie MDX jest prostsze i działa znacznie szybciej.

Można również porównać szybkość przetwarzania zapytań w różnych środowiskach: kwerendy SQL wykonywane na serwerze SQL i kwerendy MDX na serwerze OLAP, który przechowuje kostkę MOLAP. (Każde zapytanie zostało wykonane po ponownym uruchomieniu serwera, aby upewnić się, że w zapytaniu brakuje wyników zapytania).

Numer żądania (patrz tab. )Czas poświęcony na przetwarzanie zapytania MDX na serwerze OLAP (przy użyciu trybu pamięci MOLAP i poziomie agregacji 60%)Czas poświęcony na przetwarzanie zapytania SQL na serwerze SQLOk.liczba zapisów

1 4 sekundy 88 sekund 13 milionów 6 10 sekund 36 sekund 13 milionów 7 4 sekund 89 sekund 13 milionów

Chociaż takie porównanie może nie wydawać się całkowicie poprawne, jego wyniki sugerują, że użycie zapytań MDX i usług Analysis Services może znacznie poprawić wydajność. Ponieważ kostki OLAP przechowują wstępnie obliczone agregaty, wydajność w środowisku OLAP jest znacznie wyższa niż w przypadku korzystania z relacyjnych baz danych.

Czas wykonania zapytania MDX dla różnych trybów przechowywania danych

Rozważ czas przetwarzania najczęściej używanych problemów biznesowych przy użyciu MOLAP, ROLAP lub HOLAP, różnych poziomów agregacji, a także „ciepłej” lub „zimnej” pamięci podręcznej. Zimna pamięć podręczna oznacza, że ​​serwer został zrestartowany przed wykonaniem zapytania, a pamięć podręczna nie zawierała wyników poprzednich zapytań; w przypadku ciepłej pamięci podręcznej wyniki zapytania były przechowywane w pamięci podręcznej. Czas został zapisany w sekundach (nie w milisekundach). Produktywność w ciepłej pamięci podręcznej jest średnio znacznie wyższa niż w przypadku zimnej.

Średni czas przetwarzania (zimna pamięć podręczna)

Rysunek 7 przedstawia zależność średniego czasu przetwarzania zapytań dla MOLAP, HOLAP i ROLAP na poziomie agregacji w zimnej pamięci podręcznej.

Rys.7. Porównanie wartości czasu przetwarzania

Poniższy rysunek jest powiększoną wersją poprzedniego i ilustruje bardziej szczegółowo zmianę wydajności w zakresie 30–90%.

Rys.8. Porównanie czasu przetwarzania najczęściej używanych zapytań.

Informacje na wykresie pokazują, że:

• MOLAP pozwala osiągnąć najwyższą prędkość podczas przetwarzania żądań, a wydajność znacznie wzrasta wraz ze wzrostem poziomu agregacji z 0 do 60% (jak również dla ROLAP i HOLAP).
• Wraz ze wzrostem poziomu agregacji z 60 do 90%, wzrost wydajności jest nieistotny dla wszystkich trybów.

Poniższy wykres pokazuje zależność średniego czasu przetwarzania dla MOLAP, HOLAP i ROLAP na poziomie agregacji w przypadku ciepłej pamięci podręcznej.

Rys.9. Czas przetwarzania wniosku

Tak więc:

• Jeśli wynik zapytania znajduje się w pamięci podręcznej, jest przetwarzany niemal natychmiast (mniej niż 1 sekunda) dla wszystkich zapytań, niezależnie od trybu przechowywania lub poziomu agregacji. Na wykresie wartość wynosi 1, ponieważ na poprzednich rysunkach czas był również zaokrąglany do sekund.

Średni czas przetwarzania z zimną pamięcią podręczną w porównaniu z ciepłą pamięcią podręczną

Rys. 10. Porównanie czasu przetwarzania ciepłej i zimnej pamięci podręcznej
Ten wykres pokazuje, że gdy pamięć podręczna jest ciepła, wynik jest zwracany w mniej niż 1 sekundę. Najczęstsze zapytania można wykonywać jako zadanie wsadowe natychmiast po przetworzeniu danych kostki. W tym przypadku spędzony czas będzie mniejszy.

Poniższy wykres ilustruje środowisko wykonawcze procesora podczas przetwarzania żądań kostek MOLAP, ROLAP i HOLAP. Czerwony kolor oznacza czas działania serwera SQL, natomiast niebieski kolor oznacza serwer OLAP.

Rys.11. Czas ładowania procesora dla różnych trybów przechowywania

Ta liczba sugeruje, że:

• MOLAP wymaga tylko CPU do pracy na serwerze OLAP.
• ROLAP i HOLAP w większym stopniu korzystają z pracy procesora. Nawet HOLAP intensywnie korzysta z serwera RDBMS. Może to wynikać z faktu, że zapytania wybrane do tego eksperymentu nie zawierają wielu agregatów, które już istnieją w usługach Analysis Services (muszą być obliczane w czasie wykonywania).

Niektóre zalecenia dotyczące optymalizacji wydajności

Istnieją dwie kategorie najskuteczniejszych sposobów optymalizacji kostek OLAP: skrócenie czasu przetwarzania kostki OLAP i skrócenie czasu wykonania zapytania.

Zalecenia dotyczące ograniczenia czasu przetwarzania danych kostki

Udało nam się skrócić czas przetwarzania z kilku godzin do kilku minut, stosując następujące techniki:

• Wykorzystanie schematu przestrzennego dla zestawów danych. Schematy gwiazd są dobrze gotowe do kostek OLAP. Oprócz tego, że jest z nią związany, wymuszam na niej fakturę i opisaliśmy relacje z obcego między faktami i tabelami wymiarów. Stworzyliśmy indeks złożony dla wszystkich kluczy obcych w tabeli faktów. Więcej informacji na ten temat znajduje się w podręczniku.
• U edytuj edytora kostek, aby zoptymalizować projekt i zminimalizować połączenia za pomocą danych podczas pobierania danych. Zwiększyliśmy rozmiar bufora dla procesu (okno dialogowe Właściwości dla serwera OLAP) do 1 GB na komputer z 4 GB pamięci. Ważne jest, aby zwrócić uwagę na wybór poziomu agregacji. Po ustawieniu poziomu agregacji na 90% z danych danych kostki zajęło 8–9 godzin. Optymalna wartość do około 25%. Następnie możesz nieznacznie zwiększyć poziom agregacji i eksperymentalnie określić, czy będzie to miało pozytywny wpływ na szybkość wykonywania najczęściej używanych zapytań.

Udało nam się zoptymalizować czas przetwarzania żądań mdx przez:

• Efektywne wykorzystanie pamięci dla usług Analysis Services;
• Wdrażanie usług analizy (kostki OLAP) i serwera SQL (magazyny danych) na różnych komputerach;
• Użyj trybu przechowywania MOLAP.

Zaleca się również użycie Kreatora optymalizacji wykorzystania w celu obliczenia dodatkowych agregatów danych potrzebnych do poprawy wydajności przetwarzania zapytań.

Wniosek

W tym artykule przeprowadziliśmy analizę wydajności usług SQL Server 2000 Analysis Services przy użyciu różnych trybów przechowywania (ROLAP, MOLAP i HOLAP) oraz różnych poziomów agregacji przy użyciu dużej ilości informacji.

Najważniejsze ustalenia:

• Wraz ze wzrostem poziomu agregacji potrzeba więcej czasu na przetwarzanie kostek ROLAP w porównaniu z kostkami MOLAP i HOLAP.
• MOLAP wymaga więcej miejsca na dysku niż HOLAP i ROLAP; HOLAP wymaga najmniejszej ilości miejsca na dysku.
• Gdy kostki OLAP korzystają z trybu przechowywania danych MOLAP, zajmowana przez nie przestrzeń wynosi tylko 7% w porównaniu z rozmiarem oryginalnego schematu gwiazdy.
• Korzystanie z zapytań MDX może znacznie przyspieszyć system, ponieważ kostki OLAP zawierają wstępnie obliczone agregaty danych.
• Maksymalna szybkość przetwarzania zapytań jest osiągana w trybie przechowywania danych MOLAP.
• MOLAP używa procesora tylko na serwerze OLAP.

Jedną z kluczowych zalet korzystania z hurtowni danych jest możliwość przeprowadzania analiz. Jedną z głównych zalet interaktywnej analizy (inaczej - FASMI, szybka analiza współdzielonych informacji wielowymiarowych, szybka analiza współdzielonych informacji wielowymiarowych) jest możliwość korzystania z usług analizy. W tym artykule przedstawiono eksperckie potwierdzenie wysokiej wydajności modułów zbudowanych przy użyciu usług Analysis Services.