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

Pokud Oracle Analytics používá jako zdroj dat multidimenzionální databázi, je třeba zohlednit další aspekty návrhu, které mohou mít velký vliv na výkon.

Je důležité si uvědomit, že řešení návrhu pro zlepšení výkonu se liší v závislosti na případu použití. Toto téma vám neposkytne osvědčené postupy ani univerzální řešení, které by se mělo vždy použít. Místo toho nabízíme metody a techniky ladění, které vám pomohou zvýšit výkon vašich analýz a generovaného kódu.

Je na vývojovém týmu, aby prozkoumal volby, analyzoval protokoly dotazů služby Oracle Analytics a vybral nejlepší řešení pro daný případ použ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čujeme provést následující úkoly. Předpokladem těchto úkolů je pochopení struktury dotazů multidimenzionálního výrazu (MDX) a také protokolů dotazů, které generuje Oracle Analytics. Toto jsou hlavní úkoly:

  • Zjednodušte generovaný MDX.
  • Snižte počet generovaných dotazů MDX.
  • Zajistěte, aby byly v MDX použity optimální filtry a výběry.
  • Vylaďte výkon se správcem databáze (DBA) na straně 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.

Zde je uveden příklad:

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

Příkazy CASE

Funkce příkazu CASE není v MDX podporována a musí být vždy použita ve službě Oracle Analytics. Níže vysvětlená logika týkající se příkazů CASE platí pro většinu funkcí, které nejsou v MDX podporovány (if null apod.).

Použití příkazů CASE má svá pro a proti. Když do vzorců sestavy zahrnete příkazy CASE, nebudou zahrnuty do 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é.

Jak vidíte, každý případ použití je jedinečný. Klíčovým cílem je zjednodušit dotazy MDX a zároveň použít optimální filtry a výběry.

Použití funkce příkazu CASE je omezeno:

  • 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 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.

Nezapomeňte, že každý případ použití je jedinečný. Cílem je zjednodušit dotazy MDX a zároveň použít optimální filtry a výběry.

Scénář příkazu CASE versus FILTER

Podívejme se dále na stejný scénář s výsledky použití funkce CASE versus FILTER.

Uživatel požaduje sestavu, která zobrazuje zisk podle čtvrtletí a vybraného SKU produktu. Kromě toho jsou SKU seskupeny do dvanácti 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 nedochází k žádnému seskupování, je generován jednoduchý MDX, přičemž příkaz CASE zpracovává Oracle Analytics. Zde je vygenerovaný MDX:

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 „database 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]

Scénář příkazu Filter

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

Scénář 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í, můžete použít příkaz CASE. V tomto scénáři bude vygenerován jediný dotaz MDX s filtrovanými řádky, a přestože příkaz CASE použije služba Oracle Analytics, použije podmnožinu dat, nikoli všechny záznamy.

Podívejme se na 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 případě doporučujeme odebrat příkaz CASE a přejmenovat členy v databázi nebo vytvořit aliasy.

Pokud jako datový zdroj používáte multidimenzionální databázi, mohou se ve službě Oracle Analytics vyskytnout problémy s výkonem, které vedou k suboptimálním dotazům generovaným pomocí 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. Dávejte pozor na to, jak v MDX používáte podporované nebo nepodporované funkce, protože to výrazně ovlivňuje generované dotazy MDX a jejich výkon.