ABIS Infor - 2011-06

Enter Oracle spreadsheets

Kris Van Thillo (ABIS) - 31 mei 2011

Samenvatting

Nog steeds worden spreadsheets enorm veel gebruikt - hun flexibiliteit kent inderdaad nauwelijks grenzen. Eén van de belangrijkste uitdagingen eigen aan een spreadsheet blijft evenwel 'de data'. Die is veelal afkomstig van een derde bron, die dus in de spreadsheet moet worden gekopieerd. Als die derde bron wijzigt... juist! Ook de integratie van data en formules kan voor problemen zorgen. De oplossing: Oracle spreadsheets!

Het idee is even eenvoudig als spectaculair - want is het resultaat van een SQL select statement geen tabel, dus, een soort spreadsheet? We hebben dus enkel een SQL select instructie nodig die het 'standaard' select resultaat omvormt tot een spreadsheet resultaat, waarop we dan standaard spreadsheet berekeningen kunnen uitvoeren! En dat is nu juist de taak van de SQL select 'model' instructie.

De model-instructie

De 'model' instructie is niet éénvoudig; en vergt waarschijnlijk enig lees- en studiewerk. In hoofdlijnen - en sterk vereenvoudigd - komt het hierop neer.

Na het bepalen van de query resultset (voor select en order by instructies worden aangepakt) wordt de model instructie uitgevoerd. Deze instructie beslaat globaal 4 onderdelen (niet steeds allen verplicht):

• partition by column c1 - verdeeld de resultset in meerdere spreadsheet 'worksheets' op basis van een kolomwaarde in de resultset column c1;
• dimension by c2, C3 - benoemt binnen een worksheet de c2 (en/of c3) as(sen). De waarden van c2 en c3 zijn kolomwaarden uit de database opgehaald. Eén waarde van c2 en één waarde van c3 definieert één cel in de spreadsheet! Vergelijk met een normale spreadsheet: cel A24 komt overeen met c2 = A en c3 = 24. Bij een spreadsheet kies je in welke cel je een waarde opneemt; nu bepaalt de opgehaalde waarde uit de database voor c2 en C3 om welke cel het gaat;
• measures - de waarden in een cel, bepaald door de opgegeven dimensies c2 en c3, waarmee we kunnen gaan rekenen;
• rules - de berekeningen die met de waarden (measures) kunnen worden uitgevoerd.

Voorbeeld 1

Bekijk onderstaand voorbeeld (stel dat we beschikken over gegevens inkomen en cursustitel voor de jaren 2005 en 2006).

select * 
from modeltest
model 
partition by (ctitle)
dimension by (cyear) 
measures (ccount, cincome)
rules (cincome['2007']=cincome[2005]*1.25,
       ccount['2007']=sum(ccount)[ANY]
)
order by ctitle, cyear

Concreet:

• wordt een worksheet aangemaakt per ctitle in modeltest
• wordt voor een sheet - dus één ctitle - een ééndimensionele structuur aangemaakt (cyear dimensie) met waarden ccount en cincome (de measures)
• wordt de voorspelde omzet van 2007 bepaald als 1.25 maal de omzet van 2005, en het aantal deelnemers als de som van het aantal van de beide vorige jaren

In ons voorbeeld wordt door de model clause één extra rij aangemaakt: de rij met 'dimensie' 2007, en een cincome en ccount als bepaald door de 'rules'. Natuurlijk: een extra rij 'per sheet'!

Krachtig inderdaad, maar snel complex!

Voorbeeld 2

select * 
from modeltest
model 
dimension by (ctitle, cyear) 
measures (ccount, cincome, 0 as val1, 0 as val2, 0 as val3, 0 as val4, 0 as val5)
rules (
val1[ctitle, cyear]=sum(cincome)[ANY, ANY],
val2[ctitle, cyear]=sum(cincome)[cv(ctitle), ANY],
val3[ctitle, cyear]=cincome[cv(ctitle), cv(cyear)]/sum(cincome)[cv(ctitle), ANY] *100,       
val4[ctitle, cyear]=cincome[cv(ctitle), cv(cyear)]/sum(cincome)[ANY, ANY] *100,
val5[ctitle, 
cyear]=cincome[cv(ctitle),CV(cyear)]-cincome[cv(ctitle),CV(cyear)-1]/cincome[cv(ctitle),CV(cyear)]*100) 
order by ctitle, cyear

• we werken met één sheet
• val1 t/m val 5 bevatten dummy 0 waarden die in de rules worden bepaald
• val1 zal constant zijn: de sum van het inkomen overheen alle dimensies - ANY spreekt voor zich
• val2 aggregeert het inkomen overheen een identieke ctitle waarde (cv staat voor current value)
• val3 geeft weer het aandeel van het huidige cursustitel-inkomen in het totale inkomen per current value van die cursustitel
• val4 geeft weer het aandeel van het huidige cursustitel-inkomen in het totale inkomen
• val5 geeft de groei/krimp weer, in percentage, t.o.v. het vorige registratiejaar

Eénvoudig zeker niet, krachtig in elk geval. Afhankelijk van de te realiseren doelstellingen zijn er mogelijk alternatieve SQL instructies denkbaar. Vaak zullen deze niet minder complex zijn, in elk geval unions bevatten, en daardoor SQL-technisch minder efficiënt.

Tot slot

Tot slot: voor sudoku fanaten die absoluut prijzen willen winnen. Google op volgende strings 'SQL', 'Model' en 'Sudoku'! Maar om echt zeker te zijn voeg je natuurlijk nog één string toe... 'Oracle'!