W poprzednim wpisie (czyli tutaj) opisałem zmienne jakie stosuję w naszym systemie BI celem ułatwienia życia sobie i użytkownikom. Teraz może króciutki wpis jak zrobić zestaw takich zmiennych.
Wystarczy krótki SQL zapiąć do bloku inicjalizującego.
SQL wygląda tak (dla DB ORACLE):

SELECT
TRUNC (ADD_MONTHS (CURRENT_DATE, -12)) data12mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first12mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -12))) LAST12mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’) RM12mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -6)) data6mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -6)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first6mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -6))) LAST6mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -6)), ‘YYYY-MM’) RM6mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -3)) data3mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -3)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first3mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -3))) LAST3mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -3)), ‘YYYY-MM’) RM3mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -2)) data2mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first2mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -2))) LAST2mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -2)), ‘YYYY-MM’) RM2mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -1)) data1mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first1mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -1))) LAST1mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -1)), ‘YYYY-MM’) RM1mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -0)) data0mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -0)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first0mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -0))) LAST0mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -0)), ‘YYYY-MM’) RM0mago,
TO_DATE(TO_CHAR(CURRENT_DATE,’YYYY’)||’-01-01′,’YYYY-MM-DD’) FirstOfYear,
TRUNC (CURRENT_DATE-1) data1dago
FROM DUAL

Jak widać ten SQL zwraca jeden wiersz z preparowanymi na podstawie dzisiejszej daty różnymi wariacjami dat.

Teraz wystarczy tylko poszczególnym kolumnom zwróconym przez blok inicjalizacyjny podpiąć odpowiednie nazwy zmiennych jak we wpisie poprzednim (oczywiście można użyć dowolnej nazwy )

Nie zapomnijmy ustaiwić odświeżania tego bloku przynajmniej 1 raz dziennie

Wpis ten potraktuję jako pomoc użytkownikom w mojej firmie ale można go też potraktować jako good practice.

So, go on….

Bardzo często filtrujemy po datach i bardzo dobrze po to są. Więc tworzymy mozolnie raport, robimy skomplikowane funkcje, przestawiamy to wszystko w tabelach przestawnych, robimy super kolorowe wykresy i… dodajemy filtr na daty tak aby pokazać wynik za poprzedni miesiąc czyli np filtrujemy jakies pole aby zawierało się w datach 2009-11-01 do 2009-11-30. Zapisujemy ten raport na pulpicie i już mamy  gotowca którego możemy oglądać codziennie i cieszyć się dobrymi wynikami ale…. tylko przez miesiąc za miesiąc musimy wejść i przestawić datę i tak co miesiąc. Jeszcze gorzej jest gdy mamy raport filtrowany datą dzienną czyli załóżmy zawsze na wczoraj. Jakie jest rozwiązanie

1. Filtrować funkcją opartą na dacie (nie o tym ten topik ale pokażę) czyli np zamiast filtrować na sztywno w raporcie  Data=’2009-11-16′ robimy filtr na funkcji która wygląda następująco:

Data=TIMESTAMPDIFF(SQL_TSI_DAY,Current_Date,1)

O funkcjach na datach zrobię osobny wpis

2. Użyć zmiennych przedefiniowanych przez administratora (czyli w VB Leasing przeze mnie )

Otóż
Zrobiłem na potrzeby użytkowników szereg zmiennych tzw repozytorium które zwracają różne daty ustalane na podstawie bieżącej daty.
Oto lista tych zmiennych wraz z komentarzem i przykładem co zwróci taka zmienna zakładając że wykonana jest w dniu zrobienia tego wpisu tj w dniu: 2009-12-16

Tak więc użycie tak więc użycie takiej zmiennej i dynamiiczne filtrowanie danych będzie polegało na zastąpieniu statycznie wprowadzonej daty zmienną repozytorium.

Przykład:

Tworzymy raport z polem daty do filtrowania i filtrujemy

Wybieramy zmienną serwera

Wpisujemy zmienną z tabeli wyzej (na przykłądzie chce pokazac raport z dnia wczorajszego więc użyję zmiennej varData1dago)

i cieszymy się wynikiem

Dodano 2010-01-05

Dodałem jeszcze parę zmiennych do tego zestawu tj
tj zestaw zwracający daty od 13 do 24 miesięcy wstecz
varData13mago

varData14mago

varData15mago

varData16mago

varData17mago

varData18mago

varData19mago

varData20mago

varData20mago

varData20mago

varData20mago

Używając jako serwer systemu Oracle Business Intelligence systemu Windows Server niejednokrotnie natrafialiśmy na problemy związane z OC4J. Czasami program ten  wyłączał się samoistnie nie dając żadnego znaku czemu tak się działo. Zauważyliśmy że wylogowanie powodowało również zamknięcie aplikacji uruchamianej przy wejściu z autostartu usera. Również restarcie serwera trzeba było się zalogować użytkownikiem na którym instalowany był BI celem uruchomienia OC4J. Było to niezwykle uciążliwe i deprymujące zwłaszcza dlatego że Oracle Business Intelligence nosi dumny  sub-tytuł Enterprise Edition  i funkcjonuje jako serwer ba czasami zdarza się czytać o korporacjach gdzie traktowany jest jako oprogramowanie tzw”high avability”- wysokiej dostęności z minimalnymi czasami przestoju. Dlaczego więc Oracle nie stworzył z tego programu usługi? Ano pewnie dlatego żeby promować Oracle Aplication Server – chcesz wysoką dostępność dokup do BI jeszcze OAS  – masz tutaj Oc4J które jest stabilne ale moze się czasami “wywrócić”. Od czegóż jednak mamy opensourcowe rozwiązania

Projekt javaservice który pozwala
zainstalowac oprogramowanie oparte na Javie jako serwis systemów Windows NT/2000/XP
Sposób użycia:

(zakładając żę BI zainstalowany jest na dysku dajmy na to E:)

javaservice -install “Oracle BI OC4J Service” “[pełna ścieżka do pliku - jvm.dll]” -XX:MaxPermSize=128m -Xmx512m “-Djava.class.path=E:\OracleBI\oc4j_bi\j2ee\home\oc4j.jar” -start oracle.oc4j.loader.boot.BootStrap -description “Oracle BI Oc4J Service”

Oczywiście nazwę oraz opis możemy dostosować do swoich potrzeb.
z koleji pełna ścieżka do pliku – jvm.dll najczęściej tam gdzie zainsalowaliśmy JDK czyli np “c:\Program Files (x86)\Java\jdk1.6.0_10\jre\bin\server\jvm.dll”

po zainstalowaniu i restarcie możemy cieszyć się serwisem OC4J ze wszystkimi konsekwencjami tegoż czynu.

Czyli możemy np ustawić sobie opcje co ma sie stać jeśli serwis się zatrzyma.

Z praktyki – od czasu przejścia na serwis ten komponent nie sprawia już kłopotu