Blog Oracle BI - Waar blijft de tijd? Blog Oracle BI - Waar blijft de tijd?
business-intelligence-oracle-bi

Oracle BI – Waar blijft de tijd?

Logo candran analytics

Alles te weten komen over BI & Analytics?

Cadran-analytics.nl is hier dé plek voor. Ontdek de mogelijkheden van Business Intelligence & Analytics tools om waardevolle inzichten te krijgen in uw data.

Oracle BI (cloud of on-premise) kent krachtige functies voor het weergeven van data in de tijd. Voorbeelden hiervan zijn:

  • Vergelijk vorige periodes (Ago)
  • Middel afgelopen periodes (PeriodRolling)
  • Vergelijk huidige maand met het huidige jaar (ToDate)

De onderliggende motor is een goed opgezette dimensie Tijd met bijbehorende hiërarchie of structuur.

Dat hierin de nodige valkuilen kunnen zitten, is deels al duidelijk geworden in mijn vorige blog: Even iets in de week leggen. Hierin is reeds kort aangestipt dat het gebruiken van de gebruikelijke structuur en hiërarchie van een kalender bijzonder goed zal functioneren. Kijken we naar een datum in de kalender dan is die uitstekend op te rollen naar de bijbehorende maand, het kwartaal en het jaar. De datum- en tijdsfuncties, die Oracle BI biedt, kunnen prima worden gebruikt.

Fiscale Periodes

Financiële administraties van bedrijven werken echter niet per definitie via de kalenderstructuur, maar gebruiken (mogelijk niet op de kalender aansluitende) fiscale periodes. Hierin kunnen we drie varianten herkennen:

  1. De fiscale periodes zijn één op één synchroon met de kalendermaanden: Er worden twaalf fiscale periodes gebruikt die beginnen op de eerste dag van de kalendermaand en eindigen op de laatste dag van de kalendermaand.
  2. De fiscale periodes kennen een gebroken boekjaar. Bijvoorbeeld Japanse bedrijven beginnen hun fiscaal jaar op 1 april. Worden hierbij wel hele kalendermaanden gebruikt, dan is het leed wel te overzien. We dienen alleen in de gaten te houden dat fiscale periode 3 overeenkomt met kalendermaand 6. De einddata van de periodes komen echter nog steeds overeen met einddata van kalendermaanden en de tijdsstructuur staat ons nog steeds ten dienst.
  3. De fiscale periodes lopen dwars door de kalendermaanden heen. Hanteert een bedrijf bijvoorbeeld 13 fiscale periodes van 4 weken dan zal het einde en begin van elke fiscale periode dwars door de kalender lopen. Wordt als einde van een fiscale periode niet einddatum van die maand, maar einddatum in de laatste werkweek gebruikt, dan … afijn… hier beginnen de echte uitdagingen.

In situatie 1 en 2 is vrijwel er geen zorg: De fiscale tijdsstructuur loopt gelijk met de natuurlijke kalenderstructuur. Wel kunnen schrikkeljaren voor ongewenste effecten zorgen. Zie hiervoor de volgende paragraaf.

Het laatste scenario is direct in strijd met de standaardfunctionaliteit van Oracle BI. De tijdsstructuur wordt onder water namelijk altijd afgerold naar een periode van datums en vergelijking met voorgaande jaren vindt via die hiërarchie plaats. Of wel: de functie Ago zal een bepaalde gekozen periode uitklappen naar onderliggende datums om dezelfde reeks van datums in bijvoorbeeld het voorgaande jaar te selecteren. Indien fiscale periode 2017/04 eindigt op 27-04-2017 en fiscale periode 2016/04 eindigt op 29-04-2017 dan levert dit voor Oracle BI geen vergelijkbare data op en gaat de functie YearAgo (voor vergelijking vorig jaar) mank.

Ik kwam dit voorval tegen bij een recente implementatie van Oracle BI. De klant in kwestie wilde uiteraard toch echt een goed werkend oplossing. Uiteraard is die er:

  1. Details: Zorg voor transacties op dagniveau. Dit zal voor journaalposten en logistieke transacties prima opgaan. Elke transactie is terug te voeren op een datum en die datum is te vergelijken met vorig jaar. De fiscale tijdsstructuur kan oprollen naar de bijbehorende fiscale periode en -jaar. Echter: Balanstabellen (bv JD Edwards General Balance F0902 en JD Edwards Fixed Asset Balance F1202) aggregeren data op fiscale periode en kennen dus alleen een einddatum fiscale periode (via JD Edwards Fiscal Date Patterns F0008), die dus per jaar anders kan zijn.
  2. Laat de datum/tijd-functies van Oracle BI los: Auw. Dit doet even zeer. Die prachtige geavanceerde kalenderfuncties van Oracle BI zomaar even vergeten? Ja. Het is niet anders. Functioneel valt hier nooit goed uit te komen. Ik heb voorbeelden langs zien komen, waarin de oplossing werd gezocht in de middelste dag van de fiscale periode (dus bijvoorbeeld dag 15 van elke maand). Dit lost een aantal zaken mogelijk wel op (zoals Schrikkeljaren hieronder, en ongelijke einddata van fiscale periodes), maar met 13 fiscale periodes van 4 weken gaat dit ook niet goed.
    Hoe dan wel? Ik heb zelf tot dusver de beste resultaten weten te boeken door de periodetabel FQ09PER™ (Cadran AddOn) te voorzien van een kolom Fiscal Year en Fiscal Period. Een SQL-script zet hierin bij elke kalenderdatum de juiste waardes, gebaseerd op de opzet van de Fiscal Date Patterns in JD Edwards (F0008). Nu kan heel goed met zogenoemde Column Filters worden geselecteerd op huidige jaar, huidige periode en daarnaast huidige periode vorig jaar. De Ago-functie wordt niet gebruikt. Voor Verlies- en Winstrekeningen en allerlei balansoverzichten werkt dit vlekkeloos. De performance hiervan blijkt boven verwachting goed, omdat deze functies in de database worden uitgevoerd door het fysieke SQL-statement, terwijl de kalenderfuncties in de Oracle BI Server zelf plaats vinden.

Schrikkeljaar

Nog een klein duiveltje uit een doosje in de wereld van BI is het schrikkeljaar. Daarin bestaat opeens de datum 29 februari en die laat zich lastig met vorig jaar vergelijken. De functie YearAgo zal op datum 29 februari niets laten zien. Het voorgaande jaar is immers geen schrikkeljaar. Het hele jaar heeft opeens 366 dagen en feitelijk wordt er dus een dag meer gewerkt/geproduceerd/omgezet. Om cijfers te vergelijken op jaarbasis, zou de data van een schrikkeljaar dus eigenlijk moeten worden gecorrigeerd (met de factor 365/366) om in lijn te zijn met normale jaren. De data van een februari uit een schrikkeljaar zal dan mogelijk met 28/29 moeten worden gecorrigeerd.

Tabellen in JD Edwards, die werken met een einddatum (fiscale) periode, zoals Sales Forecasting (F3460), of die gekoppeld zijn aan de Fiscal Date Patterns (F0008) zullen mogelijk van schrikkeljaren ontdaan moeten worden. Door overal 29 februari te vervangen door 28 februari zal er met alle jaren goede vergelijkingen te maken zijn.

Een solide aanpak is om de tijdsfuncties, die Oracle BI biedt, wederom los te laten (ja, snif, echt waar). Traditioneel zou een filter (zoals YearNr = YEAR(CURRENT_DATE) prima werken. Een kolom Revenue en daarnaast een kolom RevenuePY (met een Ago-functie op het niveau Jaar) zou de conventionele aanpak zijn. Het is echter ook zeer goed mogelijk om te werken met Column Filters op de kolom Revenue en de kolom RevenuePY. Daarmee is zelf af te dwingen dat de eerste kolom het huidige jaar selecteert en de tweede kolom het vorige jaar. Verdiep dit Column Filter met de periode  (bv MonthNr = 2) en een maand februari wordt probleemloos vergeleken, schrikkeljaar of niet.

Een pragmatische aanpak zou kunnen zijn om ook niet de functie YearAgo te gebruiken, maar om de tabel met alle datum kolommen te voorzien van een kolom vorig jaar. Die kolom bevat dan de Julian Date van vorig jaar. Bij 29 februari 2016 (Julian Date 116060) staat in de kolom Vorig Jaar de waarde 115059 (28 februari 2015). Bij 28 februari 2016 staat daar dan hetzelfde. Dit vertekent de boel wel: Immers 1 dag uit 2015 wordt vergeleken met 2 dagen uit 2016 en om zuiver te zijn zou de hoeveelheid van 2016 dan gehalveerd moeten worden. De YearAgo-functie kan dan niet langer worden gebruikt, maar zal via die kolom JulianDatePY zelf in het BI-model of in de Analytics moeten worden gehaald. Dit zijn schijnt overigens performance-technisch wel een punt van winst te kunnen zijn, al hoewel hieraan geen uitsluitend onderzoek is gewijd. Voorzie de Periode Tabel (FQ09PER) van een Julian Date Previous Year. Dit wordt dan de datumsleutel waarmee vorig jaar kan worden vergeleken. Per schrikkeljaar zal dan heel goed gekeken moeten worden welke julian-dates-previous-year worden neergezet bij elke julian-date-this-year. Een leuke uiteenzetting hiervan is te vinden in een blogje over Oracle BI 10g, maar de versie van de software (of zelfs de leverancier) doet niets af aan dit fenomeen.

Een ‘vieze’ aanpak is om de datum 29 februari uit te sluiten door er een filter op te zetten. Met wat slimmigheid is dit generiek te maken. Dit is echter doen alsof deze datum niet bestaat en daar zal zowel de accountant als de fiscus wel wat van vinden. Voor generieke vergelijkingen tussen jaren op hoger niveau (zoals omzet per maand per productgroep) kan dit wel een zuiverder beeld geven. Een kolom Vorig Jaar zal uiteraard nooit goed kunnen vergelijken met vorig jaar als we op 29 februari kijken. Die datum is er natuurlijk niet vorig jaar.

De conclusie is dat er geen goede oplossing bestaat. Er is altijd een appelen-peren-vergelijking. De meeste zuivere methode zou zijn om periodes (zoals maand) helemaal los te laten en te werken met een YearToDate (1 januari t/m vandaag huidig jaar en dezelfde periode vorig jaar). Kijken we dan bijvoorbeeld naar 1 maart 2017 dan kijken we naar een periode van 60 dagen. Vergelijken we die periode met vorig jaar dan kijken we eveneens naar een periode van 60 dagen, al loopt die dan (toevallig) tot en met 29 februari. Pas aan het einde van het jaar op 31 december gaat het een beetje scheef. Maar over een heel jaar gezien hebben met wel meer correctieslagen te maken, zoals feestdagen, die het ene jaar toevallig in het weekend vallen en het andere jaar midden in de week.

Oracle BI - Waar blijft de tijd?

Profit & Loss van februari 2017 in Oracle BI met data uit JD Edwards. AmountPY slaat dus op februari 2016 (zijnde de schrikkelmaand).

 

Rick-Brobbel

Author:  Rick Brobbel
BI Consultant bij Cadran Consultancy