Moniulotteisten tietokantakyselyjen suorituskyvyn säätäminen

Kun Oracle Analyticsin tietolähteenä käytetään moniulotteista tietokantaa, palvelussa saattaa ilmetä suorituskykyongelmia, joiden seurauksena luotavista MDX (Multidimensional Expression) -kyselyistä tulee tehottomia.

Rakennetta muokkaamalla voit parantaa Oracle Analytics -palvelun luomia MDX-kyselyjä. Tällä voi olla valtava vaikutus niin raporttien suorituskykyyn kuin käytettyjen tietokantaresurssien määräänkin. Tuettujen ja ei-tuettujen funktioiden käyttämisellä on suuri vaikutus luotuihin MDX-kyselyihin ja siten myös suorituskykyyn.

Koska jokainen käyttötapaus on yksilöllinen, kehitystiimin on arvioitava eri vaihtoehtoja, analysoitava Oracle Analytics -palvelun kyselylokeja ja valittava kulloiseenkin tilanteeseen parhaiten sopiva ratkaisu.

Ohjeessa ei käsitellä esimerkiksi verkosta, selaimesta tai raporttien esityksestä aiheutuvia suorituskykyongelmia.

Menetelmät

Oracle suosittelee tekemään seuraavat toimet suorituskyvyn parantamiseksi. On tärkeää ymmärtää MDX-kyselyjen rakennetta ja osata tulkita Oracle Analyticsin generoimia kyselylokeja.

  • Tee MDX-kyselyistä yksinkertaisempia.
  • Pienennä luotujen MDX-kyselyjen määrää.
  • Varmista, että MDX-kyselyssä käytetään parhaita mahdollisia suodattimia ja valintoja.
  • Hienosäädä moniulotteisen tietokannan suorituskykyä tietokannan järjestelmänvalvojan (DBA) kanssa selvittämällä syy lähdetietokannan huonoon suorituskykyyn.
  • Päivitä suorituskykyanalyysia tietokannan järjestelmänvalvojan palautteen perusteella.

Valintavaiheiden optimointi

Valintavaiheita optimoimalla voit tehdä MDX-kyselyistä yksinkertaisempia, pienentää luotujen MDX-kyselyjen määrää ja parantaa suorituskykyä.

Seuraavassa kuvassa on vertaileva esimerkki optimoiduista ja optimoimattomista valintavaiheista.

Kohteen GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg kuvaus seuraa
.jpg kuvaus​

CASE-lauseet

CASE-lauseita ei tueta MDX-kyselyissä, joten jos niitä halutaan käyttää, se on tehtävä Oracle Analytics -palvelussa. Seuraavassa kuvatut CASE-lauseita koskevat asiat pätevät myös useimpiin muihin funktioihin, joita ei tueta MDX-kyselyissä (if null jne.).

CASE-lauseiden käyttämiseen liittyy sekä hyötyjä että haittoja. Kun lisäät CASE-lauseita raportin kaavoihin, niitä ei sisällytetä MDX-kyselyyn. Se voi parantaa yksinkertaistaa MDX-kyselyä ja parantaa suorituskykyä. Haittapuoli on se, että suodatus ei toimi yhtä tehokkaasti, joten kysely saattaa palauttaa tarpeettoman paljon tietueita.

CASE-lauseiden käyttämiseen liittyy seuraavia rajoituksia:

  • Jos CASE-lauseeseen ei liity useita jäseniä, lauseen perussarake on sisällytettävä kyselyyn ja näkymiin erillisenä piilotettuna sarakkeena.
  • Jos CASE-lauseeseen liittyy useita jäseniä, perussaraketta ei voi sisällyttää näkymään, koska se vaikuttaisi koontitasoon. Tässä tapauksessa:
    • Jos mittarin ryhmäkoostesääntö ei ole Ulkoinen kooste, perussarake on suljettava pois kyselystä.
    • Jos mittarin ryhmäkoostesääntö on Ulkoinen kooste, perussarake on sisällytettävä kyselyyn, mutta se on suljettava pois näkymästä. Mittarin ryhmäkoostesääntö on muutettava oletusarvoisesta yksinkertaiseksi sisäiseksi ryhmäkoostesäännöksi (SUM, MAX, MIN). Tämä toimii vain, jos sisäistä ryhmäkoostesääntöä käytetään jäsenten yhdistämiseen ja se tuottaa oikeat tulokset.

FILTER-funktio

Toisin kuin CASE-lause, FILTER-funktio voidaan lähettää tietokantaan suoritettavaksi.

Tärkein etu, joka liittyy FILTER-toiminnon käyttöön raporttien kaavoissa, on, että valinta on käytössä MDX-kyselyssä ja että käsiteltyjen ja tietokannasta haettujen tietojen määrä on pienempi.

Suurin haitta, joka liittyy FILTER-toiminnon käyttöön, on, että suoritettujen MDX-kyselyjen määrä voi olla suurempi. Oletusarvoisesti kutakin käytettyä FILTER-toimintoa kohden suoritetaan yksi kysely.

Esimerkki: CASE vs. FILTER

Tässä esimerkissä käyttäjä pyytää raportin, jossa näytetään voitto vuosineljänneksen ja valitun tuotteen varastoyksikön mukaan. Varastoyksiköt on ryhmitelty 12 eri luokkaan. Other Cola -luokassa on seuraavat LOB-tuotteet: Cola, Diet Cola ja Shared Diet Cola.

Kohteen GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg kuvaus seuraa
.jpg kuvaus​

CASE-lauseen looginen kysely on seuraava:

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

CASE-lauseeseen perustuvaa ryhmittelyä ei ole tehty. Toiminto luo yksinkertaisen MDX-kyselyn, ja CASE-lause käsitellään Oracle Analytics -palvelussa:

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]

CASE-lause suoritetaan BI-palvelimella, ja tietokanta-asetuksena on 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]

Vaihtoehtoisesti voiton mittarissa voidaan käyttää suodatinta, joka palauttaa vain tarvitut LOB-jäsenet. Tässä tapauksessa luodaan kolme mittaria, joissa sovelletaan vastaavia suodattimia.

FILTER-lauseen looginen kysely on seuraava:

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

Tässä tapauksessa luodaan kolme kyselyä eli yksi kullekin suodattimelle, mikä heikentää suorituskykyä.

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

]]

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

]]

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

Esimerkki tuotesuodattimen käytöstä

Parempi vaihtoehto on sisällyttää tuotesarake raporttiin siten, että mukana on yksi mittarisarake ilman suodatinta. Sen jälkeen luodaan suodatin, jossa ovat mukana tarvitut tuotteet. Jos tuotteet halutaan ryhmitellä eri luokkiin, siihen voidaan käyttää CASE-lausetta. Tässä tapauksessa suodatetuista riveistä luodaan yksi MDX-kysely, ja vaikka CASE-lause suoritetaan Oracle Analytics -palvelussa, se käyttää tietojen alijoukkoa eikä kaikkia tietueita.

Katsotaan toista tapausta, jossa CASE-lauseet heikentävät suorituskykyä.

Kehittäjä käyttää CASE-lausetta tuotemerkkien uudelleennimeämiseen, ja koontinäyttökehotteen avulla käyttäjille annetaan mahdollisuus valita tuotemerkki.

Kohteen GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg kuvaus seuraa
.jpg kuvaus​

Kohteen GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg kuvaus seuraa
.jpg kuvaus​

Koska MDX ei tue CASE-lausetta, MDX-kyselyssä ei voida soveltaa Brand2-suodatinta. Kaikki tuotemerkit otetaan mukaan kyselyyn, mikä ei tue optimaalista suorituskykyä.

Kohteen GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg kuvaus seuraa
.jpg kuvaus​

Tällaisissa tilanteissa Oracle suosittelee poistamaan CASE-lauseen ja nimeämään jäsenet uudelleen tietokannassa tai luomaan aliaksia.