Optimizarea performanţei interogărilor bazei de date multidimensionale

Atunci când utilizaţi o bază de date multidimensională ca sursă de date în Oracle Analytics, puteţi întâmpina probleme de performanţă, în urma cărora sunt generate interogări de expresie multidimensională (MDX) suboptime.

Prin modificarea designului, puteţi îmbunătăţi interogările MDX pe care le generează Oracle Analytics. Acest lucru poate avea un impact semnificativ nu doar asupra performanţei rapoartelor dvs., ci şi asupra volumului de resurse utilizate în baza de date. Modul în care utilizaţi funcţiile acceptate sau neacceptate are un impact semnificativ asupra interogărilor MDX generate şi, prin urmare, asupra performanţei.

Având în vedere că fiecare caz de utilizare este unic, echipa de dezvoltare ar trebui să examineze opţiunile, să analizeze jurnalele de interogări Oracle Analytics şi să selecteze cea mai bună soluţie pentru cazul dvs. de utilizare.

Acest subiect nu se referă la problemele de performanţă cauzate de infrastructura dvs., cum ar fi problemele de reţea, browser sau prezentare a raportului.

Metodologie

Oracle recomandă să efectuaţi următoarele sarcini pentru a îmbunătăţi performanţa. Este important să înţelegeţi că structura de interogări MDX, precum şi jurnalele de interogări pe care le generează Oracle Analytics.

  • Simplificaţi interogările MDX generate.
  • Reduceţi numărul de interogări MDX generate.
  • Verificaţi dacă sunt aplicate filtre şi selecţii optime în interogarea MDX.
  • Optimizaţi performanţa cu ajutorul administratorului bazei de date (DBA) la nivelul bazei de date multidimensionale şi verificaţi motivele pentru care baza de date sursă are în continuare performanţe nesatisfăcătoare.
  • Modificaţi analiza în funcţie de feedbackul de la DBA.

Optimizarea paşilor de selecţie

Atunci când optimizaţi paşii de selecţie, puteţi simplifica interogările MDX, reduce numărul de interogări MDX generate şi îmbunătăţi performanţa.

Următoarea figură prezintă un exemplu de comparaţie a paşilor de selecţie optimizaţi şi neoptimizaţi.

Urmează descrierea GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg
.jpg​

Instrucţiuni CASE

Funcţionalitatea instrucţiunii CASE nu este acceptată în interogările MDX şi trebuie aplicată întotdeauna în Oracle Analytics. Logica explicată în această secţiune cu privire la instrucţiunile CASE este valabilă pentru majoritatea funcţiilor care nu sunt acceptate în interogările MDX (if null etc.).

Există avantaje şi dezavantaje ale utilizării instrucţiunilor CASE. Atunci când includeţi instrucţiuni CASE în formulele raportului, acestea nu sunt incluse în interogarea MDX. Acest lucru poate simplifica interogarea MDX şi poate îmbunătăţi performanţa. Cu toate acestea, dezavantajul este că nu puteţi filtra la fel de eficient, ceea ce înseamnă că interogarea ar putea returna mai multe înregistrări decât este necesar.

Următoarele sunt restricţii pentru utilizarea funcţionalităţii instrucţiunii CASE:

  • Dacă instrucţiunea CASE nu combină mai mulţi membri, coloana de bază utilizată în instrucţiune ar trebui inclusă în interogare şi vizualizări ca o coloană ascunsă separată.
  • Dacă instrucţiunea CASE combină mai mulţi membri, coloana de bază nu poate fi inclusă în vizualizare fără a afecta nivelul de agregare. În acest caz:
    • Dacă regula de agregare a subindicatorului nu este Agregare externă, coloana de bază trebuie exclusă din interogare.
    • Dacă regula de agregare a subindicatorului este Agregare externă, coloana de bază trebuie inclusă în interogare şi exclusă din vizualizare. Trebuie să modificaţi regula de agregare a subindicatorului din cea prestabilită într-o regulă de agregare internă simplă (SUM, MAX, MIN). Acest lucru este posibil numai dacă regula de agregare internă este utilizată pentru a combina membri şi produce rezultate corecte.

Funcţia FILTER

Spre deosebire de funcţionalitatea instrucţiunii CASE, funcţia FILTER poate fi expediată către baza de date pentru execuţie.

Principalul avantaj al utilizării funcţiei FILTER în formulele raportului este că selecţia se aplică în interogarea MDX şi volumul de date calculate şi preluate din baza de date este redus.

Principalul dezavantaj al utilizării funcţiei FILTER este că poate mări numărul de interogări MDX executate. În mod prestabilit, se execută o singură interogare pentru fiecare funcţie FILTER utilizată.

Exemplu CASE vs. FILTER

În acest exemplu, un utilizator solicită un raport care prezintă profitul în funcţie de semestru şi codul unic de stocare al produsului selectat. În plus, codurile unice de stocare sunt grupate împreună în 12 categorii. Categoria "Alte tipuri de Cola" are următoarele produse din domeniul de activitate asignate: Cola, Diet Cola şi Shared Diet Cola.

Urmează descrierea GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg
.jpg​

Iată interogarea logică pentru instrucţiunea 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

Nu există grupare pe baza instrucţiunii CASE. Este generată o interogare MDX simplă, cu instrucţiunea CASE procesată de 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]

Instrucţiunea CASE este executată pe serverul BI şi este văzută de setarea bazei de date setată la 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]

Alternativ, puteţi utiliza un filtru pentru subindicatorul de profit, pentru a prelua doar membrii necesari din domeniul de activitate. În acest scenariu, creaţi trei metrici cu filtrele corespunzătoare aplicate.

Iată interogarea logică pentru instrucţiunea 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

În acest scenariu, sunt generate trei interogări, câte una pentru fiecare filtru, şi puteţi întâmpina probleme de performanţă.

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

]]

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

]]

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

Exemplu de filtru aplicat pentru produs

O abordare mai eficientă presupune includerea în raport a coloanei de produse, cu o singură coloană de subindicatori, fără filtru. Apoi, creaţi un filtru care include produsele necesare. Dacă doriţi să grupaţi produsele în categorii diferite, utilizaţi o instrucţiune CASE. În acest scenariu, se generează o singură interogare MDX cu rândurile filtrate şi chiar dacă este aplicată instrucţiunea CASE de către Oracle Analytics, utilizează subsetul de date, nu toate înregistrările.

Iată un alt scenariu, în care instrucţiunea CASE provoacă probleme de performanţă.

Un dezvoltator aplică o instrucţiune CASE pentru a redenumi mărcile, iar un prompt de dashboard permite utilizatorilor să selecteze marca.

Urmează descrierea GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg
.jpg​

Urmează descrierea GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg
.jpg​

Deoarece instrucţiunea CASE nu este acceptată în MDX, filtrul pentru Brand2 nu poate fi aplicat în interogarea MDX. Toate mărcile sunt selectate, fără optimizare.

Urmează descrierea GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg
.jpg​

În acest tip de scenariu, Oracle vă recomandă să eliminaţi instrucţiunea CASE şi să redenumiţi membrii din baza de date sau să creaţi aliasuri.