Oracle Business Intelligence

Postanowiłem że opiszę jak stosować daty w szczególności w aplikacji Answers.
Być może temat ten nie zainteresuje zaawansowanych użytkowników dobrze obeznanych z funkcjami w BI jednak pomyślałem że blog ten może być świetną formą szkolenia dla pracowników naszej firmy używających BI do bieżącej pracy – tym bardziej że brak jest jakichkolwiek podręczników a i szkoleń firmowych też nie za dużo.
Postaram się więc wyjaśnić w prosty sposób i na przykładach to co jest sednem rożnych systemó BI – czyli grupowanie i agregowanie “w locie” a zrobię to na przykładzie dat.
Często wynika potrzeba grupowania czegoś po częściach dat (roku, miesiącu ). Dla podstawowej daty (będącej wymiarem) takie rozbicie jest wykonane w systemie – stworzona jest też do takiej miary hierarchia (np taka: Rok->Kwartał->Miesiąc->Dekada-Dzień lub w skromniejszej formie: Rok -> Miesiąc -> Dzień – komplet takich atrybutów stanowi tzw: Wymiar czasu i można tworzyć raporty po dowolnym elemencie z tego wymiaru agregując fakty do poziomu np miesiąca,qwartału czy roku. W systemie występje jednak więcej niż jedna data. Np może wystąpić data sprzedaży (która będzie wymiarem czasu) ale może także wystąpić np data odbioru, data płatnośći która jest atrybutem i która występuje w systeme tylko i wyłącznie w postaci YYYY-MM-DD (np. 2009-05-20). Oczywiśćie grupowanie po częściach składowych tej daty jest w aplikacji Answers jak najbardziej możliwe. Przejdźmy jednak do przykładów
Weźmy sobie dla przykładu atrybut daty jakim jest data faktury oraz ilość takich faktur (UWAGA: Dane są fikcyjne )

Wynik dla takiego zapytanie (ograniczonego do dwóch lat) jest mało satysfakcjonujący:


A gdybyśmy chcieli sprawdzić ile było takich faktur w poszczególnym roku (2007,2008)
No dobrze ale przecież nie mamy wyciągniętych przez IT pól “Rok daty faktur” ani innych jak “Miesiąc daty faktury”. Stwórzmy je sobie więc sami stosując funkcję

(dla przypomnienia funkcję wpisujemy stosując ten przycisk na kolumnie:)

A funkcja pobierająca rok z pola daty wygląda następująco:

YEAR(data)

Nie zapomnijmy kliknąć w pole nagłówek niestandardowy i zmienić nazwę na krótką i treściwą (Kółka na czerwono) – gdy tego nie zrobimy to nagłowkiem kolumny będzie nieczytelna cała funkcja.
Zgrupowany po roku wynik
jest całkiem czytelny i co najwazniejsze miesci sie na jednej stronie – o czym fani excela nie chcą nawet słyszeć
Co jednak gdy chcielibyśmy pogrupować dodatkowo po miesiącach. Oczywiscie zastosujemy analogiczną funkcję tylko wyciągającą numer miesiąca z pola daty

MONTH(data)

UWAGA. nie możemy zamienić funkcji YEAR z poprzedniego przykładu na MONTH gdyż tym sposobem poagregujemy na poziomie miesięcy ale wszystkich lat razem
czyli będziemy mieli w wyniki ilość ze stycznia lat 2007 i 2008 w następnym wierszu ilośc z lutego 07 i 08 itd (no chyba żę tak chcemy). Musimy dodać dodatkową kolumnę aby pozostawić rok i rozróżnić miesiąca w obrębie roku. W tym celu

• klikamy na dowolną kolumnę (proponuję tę samą którą będziemy obrabiać czyli datę faktury)
• zmieniamy funkję tę kolumny na MONTH(Przedmiot.”Data faktury”)
• zmieniamy nagłówek kolumny tak jak poprzednio

Należy pamiętać o poprawnej (naturalnej kolejności kolumn tj Rok, miesiąć, iloś). Oczywiście można sobie dowolnie inaczej poprzestawiać kolumny wynik oczywiści będzie prawdziwy  ale sens wątpliwy:

W prosty sposób za pomoca tabeli przestawnej możemy sobie taki widok przedstawić w inny sposób:

Prawda że łatwo szybko i przyjemnie

Co jeszcze możemy wyciągnąć z dat? Proponuję nacisnąć przycisk “funkcje” w polu edycji funkcji/formuły kolumny

znajdziemy tamm.in:

• funkcje zwracającą aktualną dzisiejszą datę CURRENT_DATE
• funkcję odejmującą dwie daty (np aby zbadać ile dni minęło od jakiejś daty do dziś lub pomiędzy datami TIMESTAMP_DIFF
• funkcję dodającą/odejmującą zadany okres (dni, miesiące, lata) do daty zwracającą kolejną datę: TIMESTAMP_ADD
• i wiele innych

O arytmetyce na datach zrobięosobny wpis.

Kolejną sprawą którą chcielibyśmy usprawnić w naszym raporcie jest połączenie roku i miesiąca w jedną kolumnę aby otrzymać ciągi YYYY-MM

wydawało by się żę można połączyć w funkcji te dwie poprzednie funkcje. Ciągi łączy się znakami ‘||’ czyli zastosować coś o takiej składni:

YEAR(Przedmiot.”Data faktury”)||’-'||MONTH(Przedmiot.”Data faktury”)

niestety nie zadziała – zobaczymy na czerwono bład mówiący nam o tym: “że cyferek (jkaim są rok i miesiac) nie można łączyć w łańcuchy tekstowe znakiem ‘||’ ”
należy więc przed połączeniem zamienić cyferkę roku i miesiąca na format tekstowy stosujemy tutaj funkcję CAST która konwertuje nam formaty na zadany

CAST(wartość AS VARCHAR(n))

mamy więc

CAST(YEAR(Przedmiot.”Data faktury”) AS VARCHAR(4))||’-'||CAST(MONTH(Przedmiot.”Data faktury”) AS VARCHAR(2))

i wynik:

Wszystko fajnie tylko coś jest nie tak – sortowanie – a to za sprawą tego że zamiast miesięcy 01,02,03 mamy cyfry 1,2,3.
Jak to obejść. Jest kilka sosobów jednym z nich jest użycie funkcji

CASE WHEN….THEN…ELSE…END

o czym kiedy indziej a drugim łatwiejszym w tym przypadku jest całkowicie odmienna funkcja niz stosowane do tej pory MONTH i YEAR albowiem możemy użyć funkcji “wycinającej pierwsze 7 znaków daty otrzymując w ten sposób z formatu YYYY-MM-DD format YYYY.
Zastosujemy tu funkcję SUBSTRING która wycina ciąg tekstowy o zadanej długośći począwszyod zadanej pozycji z innego ciągu tekstowego (w naszym przypadku daty zamienionej na tekst)

SUBSTRING(text FROM pozycja FOR długość)

w naszym przypadku musimy pamiętać że data to nie format tekstowy i przed użyciem funkcji SUBSTRING musimy ją zamienić na tekst czyli:

CAST(Przedmiot.”Data faktury” AS VARCHAR(10))

to zwróci nam datę w postaci YY/MM/DD (tak jest ustawione na serwerze format daty)
teraz musimy “wykroic” z tego tekstu ciąg YY/MM

SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5)

i wynik:

możemy użyć jeszcze funkcji REPLACE zamieniając znak ‘/’ na ‘-’

REPLACE(SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5),’/',’-')

otrzymujemy:

Oraz dokleic z przodu “20″

‘20′||REPLACE(SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5),’/',’-')

by otrzymać w pełni poprawni i dobrze posortowany wynik pogrupowany dla miesięcy w latach

W kolejnych wpisach spróbuję przybliżyć arytmetykę na datach i funkcę CASE WHEN… THEN… ELSE… END