Histogram (Klokgrafiek) maken in OBIEE 11G

Blog: Histogram (Klokgrafiek) maken in OBIEE 11G Auteur: Jeroen Riethorst (Oracle BI Consultant)

 

Onlangs kwam de vraag om bepaalde rapportages om te zetten van Excel naar OBIEE 11G. Het betrof hier met name Boxplot en Histogram grafieken. Nu was de Boxplot niet zo’n probleem, want ondanks dat het grafiektype niet bestaat in OBIEE, is deze prima te simuleren via een Lijn-staaf diagram.

Het probleem zat hem in het Histogram grafiek type. Hoewel het eigenlijk een gewone lijn grafiek is, was hij niet eenvoudig te maken in OBIEE. In Excel bestaat er een standaard functie voor het berekenen van de normaal verdeling nl. NORMDIST (NORM.VERD). Hierbij geef je de waarde, het gemiddelde en de standaard deviatie op, en of je het resultaat cumulatief wilt of niet. Maar een soort gelijke functie bestaat niet in OBIEE. Maar ik heb toch een oplossing gevonden.

De formule

Na wat speurwerk op het internet kwam ik de volgende formule tegen voor een normaal verdeling waarmee je een Histogram kunt maken.

Omgezet naar SQL, levert dat het volgende op :

ROUND((1 / (SQRT(ASIN(1) * 4) * STDDEV))* EXP(-((POWER((WAARDE – GEMIDDELDE),2)) / (2 * POWER(STDDEV,2)))),14) 

De Histogram tabel

In OBIEE is het eenvoudig om het Gemiddelde en de Standaard Deviatie te bepalen van een getallen reeks. Dit leg je vast in de Repository. Maar om de grafiek te kunnen maken, ook met meerdere groeperingen, heb je een aantal gelijk verdeelde punten nodig. Dit worden de “Buckets” die in de formule de ’WAARDE’ zijn. Deze “Buckets” worden dan afgezet tegen de x-as.

Van alle getallen die ik had voor deze Histogram, 52.345 metingen, heb ik het minimum en het maximum bepaald, zodat ik een gelijkmatig verdeelde schaal kan maken. Hiermee heb ik een tabel gecreëerd met 200 rijen, en die gevuld met waarden tussen de 10 (min) en de 70 (max), en stappen van 0.3.

Voor elk van deze rijen moet de berekening, zoals voorheen beschreven, worden uitgevoerd. Alleen is er geen relatie tussen deze tabel en de metingen. Om dit voor elkaar te krijgen moet er een cartesisch product worden gemaakt, zodat de formule voor elke rij wordt uitgevoerd. Je maakt hiervoor in het fysieke model van OBIEE, een join tussen de twee tabellen met de expressie 1=1.

Uiteindelijk wil je met het gemiddelde en standaard deviatie van je getallen reeks, voor elke waarde “Bucket”, een resultaat berekenen dat uitgezet kan worden in de grafiek.

Het Rapport

In de grafiek wil ik per maand de verdeling van de metingen zien. Ik selecteer nu de maand, de standaard deviatie en het gemiddelde. Daarnaast kies ik de kolom uit de Histogram tabel voor de X-waarde, en pas ik de formule toe om voor elke X-waarde een Y-waarde te bepalen.

Y-waarde = ROUND((1 / (SQRT(ASIN(1) * 4) * StdDev))* EXP(-((POWER((X-waarde – Gemiddelde),2)) / (2 * POWER(StdDev,2)))),14).

Als je alle 200 punten berekent, komen er hele kleine Y-waarden uit. Omdat je niet alle Y-waarden nodig hebt, beperken we de data door alleen die waarden te selecteren die groter zijn dan 0.0001. Als je alle waarden selecteert, zou dat resulteren in een plat gedrukte grafiek.

De grafiek

Nu is de grafiek eenvoudig te maken. Je plaatst de Y-waarde bij de Measures, en de X-waarde bij de Curve. In dit geval worden de maanden gebruikt om meerdere lijnen weer te geven.

Zet je lijn type op “Curved Line”. Het uiteindelijk rapport ziet er als volgt uit.