Vyladění výkonu multidimenzionálních databázových dotazů

Pokud v nástroji Oracle Analytics používáte multidimenzionální databázi jako datový zdroj, mohou se vyskytnout problémy s výkonem, které vedou k suboptimálnímu generování dotazů s multidimenzionálními výrazy (MDX).

Úpravou návrhu můžete vylepšit dotazy MDX, které Oracle Analytics generuje. To může mít značný dopad nejen na výkonnost sestav, ale také na objem zdrojů používaných v databázi. Způsob použití podporovaných nebo nepodporovaných funkcí výrazně ovlivňuje generované dotazy MDX, a tedy i výkon.

Protože je každý případ užití jedinečný, vývojový tým by měl prozkoumat volby, analyzoval protokoly dotazů služby Oracle Analytics a vybrat nejlepší řešení pro daný případ užití.

Toto téma se nezabývá problémy s výkonem způsobenými vaší infrastrukturou, jako jsou sítě, prohlížeče nebo prezentace sestav.

Metodologie

Pro zvýšení výkonu doporučuje Oracle provést následující úkoly. Je důležité, abyste rozuměli struktuře dotazů MDX a také protokolům dotazů, které Oracle Analytics generuje.

  • Zjednodušte generované dotazy MDX.
  • Snižte počet generovaných dotazů MDX.
  • Zajistěte, aby byly v dotazech MDX použity optimální filtry a výběry.
  • Vylaďte se správcem databáze (DBA) výkon multidimenzionální databáze a ověřte, proč má zdrojová databáze stále nízký výkon.
  • Upravte analýzu na základě zpětné vazby od správce databáze.

Optimalizace kroků výběru

Optimalizací kroků výběru můžete zjednodušit dotazy MDX, snížit počet generovaných dotazů MDX a zvýšit výkon.

Následující obrázek ukazuje příklad srovnání optimalizovaných a neoptimalizovaných kroků výběru.

Popis GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg následuje
.jpg''​

Příkazy CASE

Funkce příkazu CASE není v dotazech MDX podporována a musí být vždy použita ve službě Oracle Analytics. Logika týkající se příkazů CASE vysvětlená v tomto oddíle platí u většiny funkcí, které nejsou v dotazech MDX podporovány (pokud je hodnota null apod.).

Použití příkazů CASE má své výhody i nevýhody. Když do vzorců sestavy zahrnete příkazy CASE, nebudou zahrnuty do dotazu MDX. Tím lze zjednodušit dotaz MDX a zvýšit výkon. Nevýhodou však je, že nemůžete filtrovat tak efektivně, takže dotaz může vrátit více záznamů, než je nezbytné.

Níže jsou uvedena omezení pro použití funkce příkazu CASE:

  • Pokud příkaz CASE nekombinuje více členů, měl by být základní sloupec použitý v příkazu zahrnut do dotazu a zobrazení jako skrytý samostatný sloupec.
  • Pokud příkaz CASE kombinuje více členů, nelze do zobrazení zahrnout základní sloupec, aniž by to ovlivnilo úroveň agregace. Pokud tomu tak je:
    • Pokud agregačním pravidlem ukazatele není Externí agregace, musí být základní sloupec z dotazu vyloučen.
    • Pokud je agregačním pravidlem ukazatele Externí agregace, musí být základní sloupec v dotazu zahrnut a ze zobrazení vyloučen. Je třeba změnit agregační pravidlo ukazatele z výchozího na jednoduché interní agregační pravidlo (SUM, MAX, MIN). To funguje pouze v případě, že se ke kombinaci členů používá interní agregační pravidlo a poskytuje správné výsledky.

Funkce FILTER

Na rozdíl od funkce příkazu CASE lze funkci FILTER odeslat ke spuštění do databáze.

Hlavní výhoda použití funkce FILTER ve vzorcích sestavy spočívá v tom, že se výběr použije v dotazu MDX a sníží se objem dat vypočítaných a načtených z databáze.

Hlavní nevýhodou použití funkce FILTER je, že může zvýšit počet spouštěných dotazů MDX. Ve výchozím nastavení se pro každou použitou funkci FILTER spouští jeden dotaz.

Příklad porovnání CASE a FILTER

V tomto příkladu uživatel požaduje sestavu, která zobrazuje zisk podle čtvrtletí a vybraného SKU produktu. Kromě toho jsou SKU seskupeny do 12 kategorií. Kategorie „Ostatní cola“ má přiřazeny následující produkty LOB: Cola, Dietní Cola a Společná dietní Cola.

Popis GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg následuje
.jpg''​

Zde je logický dotaz příkazu CASE:

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

Na základě příkazu CASE neprobíhá žádné seskupování. Je vygenerován jednoduchý dotaz MDX s příkazem CASE zpracovaný nástrojem 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]

Příkaz CASE je spuštěn na serveru BI Server což je patrné z nastavení databáze na databáze 0:0,0:

 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]

Případně můžete použít filtr na metriku zisku, abyste získali pouze požadované členy LOB. V tomto scénáři vytvoříte tři metriky s příslušnými filtry.

Zde je logický dotaz příkazu FILTER:

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

V tomto scénáři jsou generovány tři dotazy, jeden pro každý filtr, a dochází k problémům s výkonem.

Dotaz 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]

]]

Dotaz 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]

]]

Dotaz 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]

Příklad s použitým filtrem produktu

Vhodnější je zahrnout sloupec produktu do sestavy s jedním sloupcem ukazatele bez filtru. Poté vytvořte filtr, který bude obsahovat požadované produkty. Pokud chcete produkty seskupit do různých kategorií, použijte příkaz CASE. V tomto scénáři je vygenerován jediný dotaz MDX s filtrovanými řádky, a přestože je příkaz CASE použit službou Oracle Analytics, používá podmnožinu dat, nikoli všechny záznamy.

Zde je další scénář, kdy příkazy CASE způsobují problémy s výkonem.

Vývojář použije příkaz CASE k přejmenování značek a výzva na panelu umožní uživatelům vybrat značku.

Popis GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg následuje
.jpg''​

Popis GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg následuje
.jpg''​

Protože příkaz CASE není v MDX podporován, nelze v dotazu MDX použít filtr na Brand2. Jsou vybrány všechny značky, což není optimalizovaný výběr.

Popis GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg následuje
.jpg''​

V tomto typu scénáře Oracle doporučuje odebrat příkaz CASE a přejmenovat členy v databázi nebo vytvořit aliasy.