Dostrajanie wydajności zapytań w wielowymiarowych bazach danych

W przypadku używania w Oracle Analytics wielowymiarowej bazy danych jako źródła danych mogą wystąpić problemy z wydajnością skutkujące generowaniem nieoptymalnych zapytań z wyrażeniami wielowymiarowymi (MDX).

Modyfikując projekt, można poprawić zapytania MDX generowane przez Oracle Analytics. Może to mieć ogromny wpływ nie tylko na wydajność raportów, ale również na ilość zasobów używanych w bazie danych. Sposób korzystania z obsługiwanych lub nieobsługiwanych funkcji znacząco wpływa na generowane zapytania MDX, a tym samym na wydajność.

Ponieważ każdy przypadek użycia jest niepowtarzalny, zespół programistów powinien przejrzeć opcje, przeanalizować dzienniki zapytań Oracle Analytics i wybrać najlepsze rozwiązanie dla danego przypadku.

Ten temat nie obejmuje problemów z wydajnością wynikających z używanej infrastruktury, czyli np. sieci, przeglądarek lub sposobów prezentowania raportów.

Metodologia

Oracle w celu zwiększenia wydajności zaleca wykonanie wymienionych poniżej działań. Ważne jest, aby rozumieć strukturę zapytań MDX, a także dzienniki zapytań generowane przez Oracle Analytics.

  • Uproszczenie generowanych zapytań MDX.
  • Ograniczenie liczby generowanych zapytań MDX.
  • Upewnienie się, że w zapytaniach MDX są stosowane optymalne filtry i wybory.
  • Dostrojenie wydajności we współpracy z administratorem bazy danych (DBA) po stronie wielowymiarowej bazy danych i sprawdzenie, dlaczego źródłowa baza danych nadal ma niską wydajność.
  • Zmodyfikowanie analizy na podstawie informacji zwrotnych od DBA.

Optymalizacja etapów wyboru

Zoptymalizowanie etapów wyboru pozwala uprościć zapytania MDX, ograniczyć liczbę generowanych zapytań MDX i poprawić wydajność.

Na poniższym rysunku pokazano przykładowe porównanie zoptymalizowanych i niezoptymalizowanych etapów wyboru.

Opis "GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg" znajduje się poniżej
.jpg"​

Instrukcje CASE

Instrukcje CASE nie są obsługiwane w zapytaniach MDX i muszą być zawsze stosowane w Oracle Analytics. Wyjaśniona w tej sekcji logika dotycząca instrukcji CASE jest również prawdziwa dla większości funkcji, które nie są obsługiwane w zapytaniach MDX (takich jak if null itp.).

Używanie instrukcji CASE niesie ze sobą zarówno korzyści, jak i ograniczenia. Gdy instrukcje CASE są zawarte w formułach raportów, instrukcje te nie są uwzględniane w zapytaniu MDX. Może to uprościć zapytanie MDX i poprawić wydajność wykonywania. Wiąże się to jednak z ograniczeniem skuteczności filtrowania, co oznacza, że zapytanie może zwrócić więcej rekordów niż jest to potrzebne.

Korzystanie z funkcjonalności instrukcji CASE wiąże się z następującymi ograniczeniami:

  • Jeśli instrukcja CASE nie łączy wielu elementów, użyta w tej instrukcji kolumna podstawowa powinna być uwzględniona w zapytaniu i widokach jako oddzielna ukryta kolumna.
  • Jeśli instrukcja CASE łączy wiele elementów, nie można uwzględnić kolumny podstawowej w widoku, nie wpływając przy tym na poziom agregacji. W takim przypadku:
    • Jeśli regułą agregacji miary nie jest Agregacja zewnętrzna, kolumna podstawowa musi być wykluczona z zapytania.
    • Jeśli regułą agregacji miary jest Agregacja zewnętrzna, kolumna podstawowa musi być uwzględniona w zapytaniu i wykluczona z widoku. Konieczna jest zmiana reguły agregacji miary z reguły domyślnej na prostą wewnętrzną regułę agregacji (SUM, MAX, MIN). Działa to tylko wtedy, gdy wewnętrzna reguła agregacji jest używana do łączenia elementów i dostarcza prawidłowe wyniki.

Funkcja FILTER

W przeciwieństwie do instrukcji CASE funkcja FILTER może być dostarczana do bazy danych w celu wykonania.

Główną korzyścią płynącą z używania funkcji FILTER w formułach raportów jest to, że wybór jest stosowany w zapytaniu MDX, a objętość danych obliczanych i pobieranych z bazy danych zostaje ograniczona.

Główną wadą używania funkcji FILTER jest to, że może ona spowodować zwiększenie liczby wykonywanych zapytań MDX. Domyślnie dla każdej użytej funkcji FILTER jest wykonywane jedno zapytanie.

Przykładowe porównanie użycia instrukcji CASE i funkcji FILTER

W tym przykładzie użytkownik wnioskuje o raport przedstawiający zysk według kwartału i wybranego numeru SKU produktu. Dodatkowo numery SKU są pogrupowane w 12 kategorii. Do kategorii "Other Cola" są przypisane następujące produkty LOB: "Cola", "Diet Cola" i "Shared Diet Cola".

Opis "GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg" znajduje się poniżej
.jpg"​

Zapytanie logiczne instrukcji CASE jest następujące:

SELECT

   0 s_0,

   CASE when XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola') THEN 'Other Cola' ELSE XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" END s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_3,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_6,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_8,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_9,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_10,

   XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" s_11

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 8 ASC NULLS LAST, 11 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

Nie występuje grupowanie na podstawie instrukcji CASE. Generowane jest proste zapytanie MDX, przy czym instrukcja CASE jest przetwarzana przez Oracle Analytics:

With 
  set [_Product3]  as 'Descendants([Product], [Product].Generations(3), leaves)'
  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'
select 
  { [Measures].[Profit]
  } on columns,
  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties GEN_NUMBER, [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows 
from [Sample.Basic]

Instrukcja CASE jest wykonywana na serwerze BI i jest to odzwierciedlone w ustawieniu database 0:0,0 bazy danych:

 RqList <<11777451>> [for database 0:0,0]
                            D1.c6 as c6 [for database 0:0,0],
                            D1.c4 as c4 [for database 0:0,0],
                            case  when D1.c7 in ([ 'Cola', 'Diet Cola', 'Shared Diet Cola'] ) then 'Other Cola' else D1.c7 end  as c2 [for database 0:0,0],
                            D1.c5 as c5 [for database 0:0,0],
                            D1.c3 as c3 [for database 0:0,0],
                            D1.c1 as c1 [for database 0:0,0],
                            D1.c7 as c7 [for database 0:0,0],
                            D1.c8 as c8 [for database 0:0,0]

Alternatywnie można użyć filtra w odniesieniu do miary zysku, aby pobierać tylko wymagane elementy LOB. W tym scenariuszu tworzone są trzy miary z zastosowanymi odpowiadającymi im filtrami.

Zapytanie logiczne instrukcji FILTER jest następujące:

SELECT

   0 s_0,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_3,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_6,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_8,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_9,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola')) s_10,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Sasprilla','Birch Beer','Dark Cream')) s_11,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('xxxxx')) s_12

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 8 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

W tym scenariuszu generowane są trzy zapytania, po jednym dla każdego filtra, i występują problemy z wydajnością.

Zapytanie 1:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "xxxxx")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Zapytanie 2:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Birch Beer") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Dark Cream") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Sasprilla")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Zapytanie 3:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Diet Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Shared Diet Cola")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

Przykład zastosowanego filtra produktu

Lepszym podejściem jest uwzględnienie w raporcie kolumny produktu z pojedynczą kolumną miary bez filtra. Następnie należy utworzyć filtr uwzględniający wymagane produkty. Aby pogrupować produkty w różne kategorie, należy użyć instrukcji CASE. W tym scenariuszu jest generowane pojedyncze zapytanie MDX z odfiltrowanymi wierszami i nawet mimo tego, że w Oracle Analytics zastosowano instrukcję CASE, zapytanie używa podzbioru danych, a nie wszystkich rekordów.

Oto inny scenariusz, w którym instrukcje CASE powodują problemy z wydajnością.

Programista stosuje instrukcję CASE w celu zmiany nazw marek, a filtr monitujący pulpitu informacyjnego umożliwia użytkownikom wybór marki.

Opis "GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg" znajduje się poniżej
.jpg"​

Opis "GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg" znajduje się poniżej
.jpg"​

Ponieważ instrukcja CASE nie jest obsługiwana w MDX, nie można zastosować filtra Brand2 w zapytaniu MDX. Wybierane są wszystkie marki i nie jest to podejście zoptymalizowane.

Opis "GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg" znajduje się poniżej
.jpg"​

W scenariuszach tego typu Oracle zaleca usunięcie instrukcji CASE i zmianę nazw elementów w bazie danych lub utworzenie aliasów.