Funkcjonalno ść języka MDX w implementacji HYPERION

Paweł Kędziora, Marek Lewandowski

Politechnika Poznańska

pawel.kedziora@gmail.com, lewandowski.marek@gmail.com

2

Spis Treści:

1 Standard MDX ............................................................................................................... 2 2 Postać zapytania w języku MDX Hyperion ................................................................... 3 3 Funkcje w języku MDX Hyperion ................................................................................. 3

3.1 Predefiniowane funkcje języka MDX implementacji Hyperion ............................ 3 3.2 Funkcje definiowane przez użytkownika ............................................................. 10

4 Narzędzia wydawania zapytań do bazy danych ........................................................... 11 4.1 Konsola MaxL Script Editor oraz konsola MDX Script Editor ........................... 11 4.2 Hyperion Visual Explorer .................................................................................... 11 4.3 Hyperion Essbase Query Designer (Microsoft Excel) ......................................... 12

5 Przeprowadzony eksperyment...................................................................................... 13 5.1 Cel i zakres eksperymentu.................................................................................... 13 5.2 Schemat hurtowni danych. ................................................................................... 14 5.3 Dostępne dane ...................................................................................................... 16 5.4 Zapytania .............................................................................................................. 17

5.4.1 Wycięcie oraz obrót...................................................................................... 17 5.4.1.1 Wycięcie jednej warstwy kostki danych (zapytania 1, 2) ........................ 17 5.4.1.2 Wycięcie więcej niż jednej warstwy kostki danych (zapytanie 3) ........... 18 5.4.1.3 Wycięcie z kostki za pomocą zakresu wymiarów (zapytania 4, 5).......... 19

5.4.2 Drill up i Drill down..................................................................................... 20 5.4.2.1 Drill up i Drill down z wykorzystaniem standardowych funkcji (zapytania 6, 7, 8) 20 5.4.2.2 Operacje Drill up i Drill down z wykorzystaniem własności drzewa hierarchii elementów (zapytania 10, 11, 12, 13) ...................................................... 21

5.4.3 Funkcje standardowe języka MDX w implementacji Hyperion ..................22 5.4.3.1 Distinct (zapytanie 14) ............................................................................. 22 5.4.3.2 Except (zapytanie 15)............................................................................... 23 5.4.3.3 Head, Tail (zapytania 16, 17) ................................................................... 23 5.4.3.4 Union (zapytanie 18) ................................................................................ 24 5.4.3.5 Subset (zapytanie 19) ............................................................................... 24 5.4.3.6 Intersect (zapytanie 20) ............................................................................ 24

5.4.4 Elementy obliczone (calculated members) (zapytanie 21)........................... 25 5.4.5 Nazwane zbiory (named sets) (zapytanie 22)............................................... 25 5.4.6 Filtrowanie wyników zapytań (zapytanie 23) .............................................. 26 5.4.7 Predykcja wartości (zapytanie 24) ............................................................... 26 5.4.8 Sortowanie wyników zapytań (zapytanie 25) .............................................. 28

6 Podsumowanie.............................................................................................................. 29 7 Literatura ...................................................................................................................... 29

1 Standard MDX

MDX został wprowadzony w roku 1997 przez firmę Microsoft, jako część OLEDB dla specyfikacji OLAP. Specyfikacja ta została rozwinięta w 1998 roku, przez komercyjną wersję Microsoft OLAP Services 7.0 oraz później przez Microsoft Analysis Services. Mimo, iż MDX nie był otwartym standardem, szybko został zaadaptowany przez innych twórców systemów hurtowni danych, m.in.: Hyperion, SAS, SAP.

3

2 Postać zapytania w j ęzyku MDX Hyperion Ogólna postać zapytania w języku MDX wygląda następująco: SELECT {set} ON COLUMNS [, {set} ON ROWS] FROM aplikacja.baza_danych [WHERE {set}]

gdzie set to jedna bądź wiele krotek, a krotka rozumiana jest jako odwołanie do elementu (member) lub kombinacji elementów z różnych wymiarów. Elementy podawane są w nawiasach kwadratowych ( [ ] ) i oddzielane przecinkiem (‘ , ‘). Słowa kluczowe ON

COLUMNS, ON ROWS określają format wyświetlania wierszy i kolumn wyniku zapytania. Przykład: SELECT { [Sales] } ON COLUMNS, { [Shops] } ON ROWS FROM app_shops.shops_db WHERE { [2005]};

Powyższe zapytanie zwróci w wartość sprzedaży (Sales ) dla poszczególnych sklepów (Shops ) w roku 2005 obliczoną na podstawie danych zawartych w bazie danych shops_db znajdującej się w aplikacji app_shops . Wynik zapytania przyjmie postać tabeli: Shops Sales Shops Sales_1 + Sales_2 + … + Sales_n Shop_1 Sales_1 Shop_2 Sales_2 … … Shop_n Sales_n W pierwszym rzędzie tabeli znajduje się wynik zapytania o atrybut Sales dla wszystkich sklepów. Jeśli atrybut Sales jest atrybutem addytywnym, wielkość ta będzie sumą wartości Sales dla wszystkich krotek spełniających warunki zapytania.

3 Funkcje w j ęzyku MDX Hyperion

3.1 Predefiniowane funkcje języka MDX implementacji Hyperion Abs (liczba) – przyjmuje wartość bezwzględną podanego parametru. Ancestor (element | warstwa) – przyjmuje wartość bezpośredniego poprzednika podanego elementu w hierarchii wymiarów.

4

Ancestors (element, warstwa | indeks) – zwraca zbiór poprzedników wskazanego elementu, występujących w hierarchii wymiarów do wysokości określonej przez indeks. Avg (zbiór [,liczba [, InculdeEmpty]]) – zwraca średnią wartość krotek znalezionych we wskazanym zbiorze. W przypadku wyszczególnienia elementu liczba obliczana jest średnia dla zbioru krotek i drugiego parametru. Klauzula InculdeEmpty pozwala na uwzględnienie pustych wartości (#MISSING) podczas obliczania średniej. BottomCount (zbiór, indeks [,liczba]) – funkcja zwraca podzbiór o liczebności indeks

elementów zbioru set uporządkowanych od wartości najmniejszej do największej. Liczba wskazuje kryteria selekcji. BottomPercent (zbiór, procentowo ść, liczba) – zwraca najmniejszy istniejący podzbiór zbioru zbiór , dla którego całkowity wynik przeliczeń jest nie mniejszy niż zadeklarowana procentowo ść przeliczeń całego zbioru zbiór . Procentowo ść jest liczbą z zakresu [0; 100]. Liczba wskazuje kryteria selekcji. Elementy zwróconego podzbioru uporządkowane są od najmniejszego do największego. BottomSum (zbiór, suma, liczba) – zwraca najmniejszy istniejący podzbiór zbioru zbiór , dla którego całkowity wynik przeliczeń jest co najmniej równy podanemu wyrażeniu suma. Elementy zwróconego podzbioru uporządkowane są od najmniejszego do największego. Case – słowo kluczowe rozpoczynające instrukcję warunkową case . Children (element) – funkcja zwraca zbiór elementów potomnych wyszczególnionego elementu element . ClosingPeriod( [warstwa [, element]]) – zwraca ostatni element wymiaru typu Time dla podanych parametrów. CoalesceEmpty ( element, warto ść1 ) – funkcja zwraca wartość warto ść1 jeśli wartość elementu element jest pusta (#MISSING ). W przeciwnym przypadku zwracana jest wartość elementu element . Count (zbiór [, IncludeEmpty]) – funkcja zwraca liczebność zbioru zbiór . Parametr IncludeEmpty określa czy wartości puste (#MISSING) mają być ignorowane. Cousin (element1, element2) – zwraca potomka innego elementu element2 , znajdującego się na tym samym poziomie w hierarchii wymiarów co element element1 . CrossJoin (zbiór1, zbiór2) – zwraca iloczyn kartezjański zbioru zbiór1 i zbioru zbiór2 . Zbiór2 nie może zawierać żadnych wymiarów wykorzystywanych w zbiorze zbiór1 . CurrentMember (wymiar) – zwraca aktualnie przetwarzany element dla wskazanego wymiaru wymiar . Funkcja wykorzystywana m.in. w funkcjach iteracyjnych. CurrentTupple (zbiór) – zwraca aktualnie przetwarzaną krotkę wskazanego zbioru zbiór . Funkcja wykorzystywana w m.in. w funkcjach iteracyjnych.

5

DefaultMember (wymiar) – zwraca domyślny element wyszczególnionego wymiaru wymiar . W Analytic Services domyślnym elementem jest element znajdujący się na pierwszym miejscu wśród elementów z najwyższego poziomu hierarchii wymiaru wymiar . Descendants (element [{ warstwa | indeks}[, flaga]] ) - zwraca elementy podrzędne znajdujące się w warstwie warstwa , lub na głębokości indeks w hierarchii wymiarów elementu element . Flaga wskazuje, które elementy mają być zwracane / pomijane. Wartości, jakie może przyjmować element flaga to: SELF – zwraca tylko elementy z warstwy warstwa oraz element element , jeśli należy on do warstwy warstwa . AFTER – zwraca elementy leżące poniżej warstwy warstwa . BEFORE – zawraca element element oraz elementy znajdujące się w hierarchii wymiarów powyżej warstwy warstwa . BEFORE_AND_AFTER – zwraca element element oraz wszystkie elementy znajdujące się poniżej niego w hierarchii wymiarów, ale nie należące do warstwy warstwa . SELF_AND_AFTER – zwraca wszystkie elementy leżące w warstwie warstwa , oraz w warstwach leżących poniżej niej w hierarchii wymiarów. SELF_BEFORE_AFTER – zwraca element element oraz wszystkie elementy leżące poniżej niego w hierarchii wymiarów. LEAVES – zwraca elementy podrzędne elementu element , znajdujące na najniższym poziomie hierarchii wymiarów (liście drzewa wymiarów). Distinct (zbiór) – funkcja eliminująca zduplikowane krotki ze zbioru zbiór . Dimension (element | warstwa) – funkcja zwracająca wymiar, do którego należy element lub warstwa . DrilldownByLayer ( zbiór [, warstwa | indeks]) - funkcja wykonująca operację DrillDown na zbiorze zbiór , w ramach elementów należących do warstwy warstwa lub występujących na poziomie drzewa hierarchii określonego przez indeks . DrilldownMember (zbiór1, zbiór2 [, RECURSIVE]) – funkcja wykonująca operację DrillDown na elementach należących do zbioru zbiór1 , które również należą do zbioru zbiór2 . RECURSIVE – słowo kluczowe, którego użycie umożliwia wykonywanie powtarzających się porównań na elementach zbiorów. DrillupByLayer (zbiór, [, warstwa]) – funkcja przeprowadzająca operację DrillUp wszystkich elementów zbioru zbiór leżących poniżej warstwy warstwa . DrillupMember (zbiór1, zbiór2) – funkcja sprawdza, czy w zbiorze elementów zbiór2 znajdują się przodkowie zbioru elementów zbiór1 i wykonująca operację DrillUp do poziomu elementów – przodków w zbiorze zbiór2 . Except (zbiór1, zbiór2 [, ALL]) – funkcja zwracająca różnicę zbiorów zbiór1 i zbiór2 . Flaga ALL pozwala na pozostawienie duplikatów. Exp (pot ęga) – funkcja matematyczna zwracająca liczbę e (podstawę logarytmu naturalnego) podniesioną do potęgi pot ęga.

6

Extract (zbiór [, wymiar]) – funkcja zwracająca zbiór krotek należących do zbioru elementów zbiór , z podanego wymiaru wymiar . Factorial (liczba) – funkcja matematyczna zwracająca silnię podanej liczby liczba . Filter (zbiór, warunek) – funkcja zwracająca krotki należące do zbioru elementów zbiór , spełniające kryteria selekcji określone przez warunek . FirstChild (element) – funkcja zwracająca pierwszego potomka (zależnie od kolejności definiowania) elementu element . FirstSibling (element) – funkcja zwracająca pierwszy element (zależnie od kolejności definiowania) znajdujący się na tym samym poziomie hierarchii co element i mający taki sam jak element element nadrzędny. Generate (zbiór1, zbiór2 [, ALL]) – funkcja zwracająca zbiór powstały w wyniku wykonania operacji: „dla każdego elementu ze zbioru zbiór1 zwróć zbiór2 ”. Klauzula ALL pozwala na pozostawienie duplikujących się elementów w zbiorze wynikowym. Generation (element) – funkcja zwracająca elementy pokolenia, do którego należy element element . Generations (wymiar, indeks) – funkcja zwracająca elementy pokolenia wymiaru wymiar do zadanej głębokości w hierarchii wymiarów określonej przez indeks. Head (zbiór [, liczba]) – funkcja zwracająca liczba pierwszych krotek lub elementów należących do zbioru zbiór . Hierarchize (zbiór [, POST]) – funkcja zwracająca elementy należące do zbioru zbiór w takim porządku, w jakim zostały one zdefiniowane w hierarchii wymiarów. Użycie klauzuli POST powoduje elementów podrzędnych przed nadrzędnymi. IIF (warunek, prawda, fałsz) – funkcja weryfikująca prawdziwość warunku warunek i zwracająca wartość prawda , jeśli warunek jest prawdziwy, lub fałsz w przeciwnym przypadku. Prawda i fałsz mogą być zbiorami elementów! Int (liczba) – funkcja matematyczna zwracająca najmniejszą liczbę całkowitą większą niż liczba . Intersection (zbiór1, zbiór2 [, ALL]) – funkcja zwracająca iloczyn zbiorów zbiór1 i zbiór2 . ALL pozwala na pozostawienie duplikujących się elementów w zbiorze wynikowym. Is (element1, element2) – funkcja porównująca dwa elementy. Przyjmuje wartość TRUE, jeśli elemen1 i element2 są identyczne.

7

IsAccType (element, AccTag) – funkcja sprawdzająca, czy element element należy do wymiaru zadeklarowanego jako Accounts i posiadającego odpowiednią etykietę AccTag . Etykieta może przyjąć jedną z następujących wartości: First, Last, Average, Expense,

Two-pass . IsAncestor (element1, element2) – funkcja przyjmująca wartość TRUE, jeśli element element1 jest przodkiem elementu element2 . IsChild (element1, element2) – funkcja przyjmująca wartość TRUE, jeśli element element1 jest potomkiem elementu element2 . IsEmpty (element) – funkcja przyjmująca wartość TRUE w przypadku, gdy element jest pusty (#MISSING). IsGeneration (element, indeks) – funkcja przyjmująca wartość TRUE, jeśli element element należy do pokolenia wskazanego przez indeks . IsLeaf (element) – funkcja przyjmująca wartość TRUE, jeśli element element znajduje się na najniższym poziomie hierarchii wymiarów (jest liściem drzewa wymiarów). IsLevel (element, poziom) - funkcja przyjmująca wartość TRUE, jeśli element element znajduje się w hierarchii wymiarów na poziomie określonym przez poziom. IsSibling (element1, element2) - funkcja przyjmująca wartość TRUE, jeśli element element1 znajduje się na tym samym poziomie w hierarchii wymiarów i ma ten sam element nadrzędny co element element2 . IsUDA (element, nazwa) - funkcja przyjmująca wartość TRUE, jeśli element element jest powiązany z etykietą UDA (user defined attribute ) o nazwie nazwa. IsValid (element | krotka | zbiór | warstwa | własn ość) - funkcja przyjmująca wartość TRUE, jeśli wyszczególniony parametr jest poprawny. Item (krotka, indeks) – ekstrahuje element o numerze indeks z krotki krotka . Lag (element, indeks) – zwraca element, który znajduje się indeks kroków wstecz od elementu element w hierarchii wymiarów. LastChild (element) – funkcja zwracająca ostatniego potomka (zależnie od kolejności definiowania) elementu element . LastPeriods (liczba [, element]) – funkcja zwracająca ciąg elementów o liczebności liczba , który zakończony jest elementem element . LastSibling (element) – funkcja zwracająca ostatni element (zależnie od kolejności definiowania) znajdujący się na tym samym poziomie hierarchii co element i mający taki sam jak element element nadrzędny.

8

Lead (element, indeks) – funkcja zwracająca element, który znajduje się indeks kroków w przód od elementu element w ramach tej samej warstwy w hierarchii wymiarów. Level (element) – funkcja zwracająca poziom w hierarchii wymiarów, na którym znajduje się element element . Levels (wymiar, indeks) – funkcja zwracająca poziom wymiaru wymiar określony przez głębokość indeks . Ln (liczba) – funkcja matematyczna zwracająca logarytm naturalny z liczby liczba . Log (liczba [, podstawa]) - funkcja matematyczna zwracająca logarytm o podstawie podstawa z liczby liczba . Domyślną wartością parametru podstawa jest 10. Log10 (liczba) - funkcja matematyczna zwracająca logarytm dziesiętny z liczby liczba . Max (zbiór [, liczba]) - funkcja matematyczna zwracająca element o największej wartości spośród elementów zbioru zbiór . Liczba jest parametrem opcjonalnym. MemberRange (element1, element2) – zwraca wszystkie elementy występujące pomiędzy element1 i element2 w hierarchii wymiarów. Members (wymiar | warstwa) – funkcja zwraca wszystkie elementy należące do wymiaru wymiar bądź warstwy warstwa . Min (zbiór [, liczba]) - funkcja matematyczna zwracająca element o najmniejszej wartości spośród elementów zbioru zbiór . Liczba jest parametrem opcjonalnym. Mod (liczba1, liczba2) – funkcja matematyczna zwracająca resztę z dzielenia liczby liczba1 przez liczbę liczba2 . NextMember (element [, GENERATION | LEVEL]) – funkcja zwracająca kolejny element (zależnie od kolejności definiowania) w stosunku do element , w ramach pokolenia (GENERATION) lub poziomu (LEVEL). NonEmptyCount (zbiór [, liczba]) – funkcja zwracająca liczbę krotek posiadających niepuste wartości. OpeningPeriod ( [warstwa [, element]]) - zwraca pierwszy element wymiaru typu Time dla podanych parametrów. Order (zbiór, wyra żenie | liczba [, BASC | BDESC] ) – funkcja porządkująca elementy zbioru zbiór zgodnie z regułami określonymi przez wyra żenie . BASC – rosnąco; BDESC – malejąco. Ordinal (warstwa) – funkcja zwracająca numer pokolenia lub poziomu w hierarchii wymiarów dla warstwy warstwa .

9

ParallelPeriod ( [warstwa [, indeks [, element]]]) – funkcja zwraca element z wcześniejszego okresu czasu dla podanych parametrów. Parent ( element ) – funkcja zwracająca element nadrzędny w hierarchii wymiarów w stosunku do elementu element . PeriodsToDate ( [warstwa [, element]]) – funkcja zwracająca elementy, które występują wcześniej niż aktualny element z wymiaru Time dla podanych parametrów. Power (liczba, pot ęga) – funkcja matematyczna przyjmująca wartość liczby liczba podniesionej do potęgi pot ęga. PrevMember (element [, GENERATION | LEVEL]) – funkcja zwracająca poprzedni element (zależnie od kolejności definiowania) w stosunku do element , w ramach pokolenia (GENERATION) lub poziomu (LEVEL). RelMemberRange (element, poprzednie, nast ępne [, GENERATION | LEVEL]) – funkcja zwracająca poprzednie poprzednich elementów elementu element , nast ępne następnych elementów elementu element w ramach danego pokolenia (GENERATION) lub poziomu (LEVEL). Remainder (liczba) – funkcja matematyczna zwracająca część ułamkową z liczby liczba . Round (liczba, dokładno ść) - funkcja matematyczna zaokrąglająca liczbę liczba zgodnie z precyzją wskazaną parametrem dokładno ść. Siblings (element) – funkcja zwracająca zbiór elementów znajdujących się na tym samym poziomie w hierarchii co element i mających ten sam element poprzedni. Subset (zbiór, indeks1 [, indeks2]) – funkcja zwracająca podzbiór (wyspecyfikowany zakresem krotek: od indeks1 do indeks2 ) zbioru zbiór . Sum (zbiór [, liczba]) – funkcja zwracająca sumę elementów wchodzących w skład zbioru zbiór . Tail (zbiór [, liczba]) – funkcja zwracająca liczba ostatnich krotek lub elementów należących do zbioru zbiór . ToDate (ci ąg1, ci ąg2) – funkcja zmieniająca datę na liczbę. Ci ąg1 określa format daty (‘mm-dd-yyyy’ lub ‘dd-mm-yyyy’), a ci ąg2 to data (np. ’21-06-2006’). TopCount (zbiór, indeks [, liczba]) – funkcja zwracająca indeks elementów zbioru zbiór uporządkowanych od największego do najmniejszego. TopPercent (zbiór, procentowo ść, liczba) – zwraca najmniejszy istniejący podzbiór zbioru zbiór , dla którego całkowity wynik przeliczeń jest nie mniejszy niż zadeklarowana procentowo ść przeliczeń całego zbioru zbiór . Procentowo ść jest liczbą z zakresu [0; 100]. Liczba wskazuje kryteria selekcji. Elementy zwróconego podzbioru uporządkowane są od największego do najmniejszego.

10

TopSum (zbiór, suma, liczba) – zwraca najmniejszy istniejący podzbiór zbioru zbiór , dla którego całkowity wynik przeliczeń jest co najmniej równy podanemu wyrażeniu suma. Elementy zwróconego podzbioru uporządkowane są od największego do najmniejszego. Truncate (liczba) – funkcja matematyczna zwracająca całkowitą część liczby liczba . Uda (wymiar | element, nazwa) – funkcja zwraca wszystkie elementy wyszczególnione etykietą UDA (user defined attribute ) o nazwie nazwa związane z wymiarem wymiar lub poddrzewem wymiarów o korzeniu w elemencie element ). Union (zbiór1, zbiór2 [, ALL]) – funkcja zwracająca sumę zbiorów zbiór1 i zbiór2 . ALL pozwala na pozostawienie duplikujących się elementów w zbiorze wynikowym. Value (element) – funkcja zwracająca wartość elementu element . WithAttr (element, LITERAŁ [, literał_numeryczny | , literał_znakowy] ) – funkcja zwracająca wszystkie elementy, które są związane z elementem element w wskazany sposób. Wartości, które mogą być przyjmowane przez LITERAŁ: >, >=, <, <=, = =, <> | ! =, In.

xTD ([element]) – funkcja równoważna PeriodsToDate .

3.2 Funkcje definiowane przez użytkownika System Hyperion umożliwia użytkownikowi tworzenie własnych funkcji. Funkcje te definiowane są w języku JAVA, kompilowane przez zewnętrzny kompilator języka JAVA, a następnie dołączane do schematu bazy danych. Język MDX służy jedynie do zadeklarowania funkcji poprzednio zdefiniowanych. Przykład definicji funkcji w języku JAVA: public class CalcFunc { public static double sum (double[] data) { int i, n = data.length; double sum = 0.0d; for (i=0; i<n; i++) { double d = data [i]; sum = sum + d; } return sum; } }

Przykład deklaracji zdefiniowanej funkcji: create function Sample.'@JSUM' as 'CalcFunc.sum' spec '@JSUM(memberRange)' comment 'adds list of input members';

Wszystkie nazwy funkcji w języku MDX w implementacji Hyperion muszą zaczynać się od znaku „@”.

11

4 Narzędzia wydawania zapyta ń do bazy danych

4.1 Konsola MaxL Script Editor oraz konsola MDX Script Editor Do bezpośredniego wpisywania zapytań do bazy danych w języku MDX służą dwie konsole: MaxL Script Editor oraz MDX Script Editor. MDX Script Editor posiada opcję podpowiadania składni języka MDX, natomiast konsola MaxL Script Editor jest zauważalnie szybsza i z tego powodu została wybrana do przeprowadzenia eksperymentu. Wysłanie zapytania do bazy danych następuje poprzez wciśnięcie przycisku F5. Wynik zapytania przedstawiany jest w postaci tabeli.

Rys. 4.1 Widok okna konsoli MaxL Script Editor wraz z wynikiem zapytania.

4.2 Hyperion Visual Explorer Hyperion Visual Explorer włączony zostaje poprzez uruchomienie pliku runvss.exe z katalogu \essbase\bin znajdującego się w katalogu głównym systemu Hyperion. Hyperion Visual Explorer jest narzędziem do graficznego przedstawiania wyników zapytań do bazy danych, którego użytkowanie nie wymaga znajomości języka MDX. Zapytania wydawane są poprzez umieszczanie żądanych elementów (metodą „przeciągnij i upuść”) w odpowiednich polach (oznaczających osie rzędnych i odciętych na wykresach). Ograniczanie wyników zapytań realizowane jest przez zaznaczanie / odznaczanie pól typu check – box przyporządkowanych do każdego elementu.

12

Wynik zapytania przedstawiany jest domyślnie w postaci wykresu słupkowego. Typ wykresu może być modyfikowany.

Rys. 4.2 Hyperion Visual Explorer i wynik zapytania o [Obrót] w pierwszych trzech miesiącach lat [2005] i [2006]

Rys. 4.3 Ograniczanie zakresu elementu w zapytaniu w Hyperion Visual Explorer.

4.3 Hyperion Essbase Query Designer (Microsoft Excel) Po zainstalowaniu systemu Hyperion, w głównym menu programu Microsoft Excel pojawia się dodatkowa opcja Essbase, umożliwiająca m.in. podłączanie do bazy danych oraz otwarcie narzędzia Hyperion Query Designer. Wybór opcji New Query powoduje przejście do kreatora zapytania, którego poszczególne składniki wybierane są przez użytkownika za pomocą kliknięć myszką na żądanych elementach odpowiednich wymiarów, co podobnie jak w przypadku Hyperion Visual Explorer, nie wymaga od użytkownika znajomości języka MDX. Wysłanie zapytania do bazy danych dokonane

13

zostaje poprzez wybór opcji Apply Query z menu kontekstowego przygotowywanego zapytania.

Rys. 4.4 Widok okna kreatora zapytania w Hyperion Essbase Query Designer.

Rys. 4.5 Wynik zapytania w Microsoft Excel

5 Przeprowadzony eksperyment.

5.1 Cel i zakres eksperymentu. Celem eksperymentu było zbudowanie hurtowni danych oraz zbadanie funkcjonalności języka MDX w implementacji Hyperion. Hurtownia została zbudowana dla rzeczywistych danych następujących spółek giełdowych: PKO BP, INGBSG, BZWBK, Prokom,

14

Comarch, Comp, KGHM, STALEXP, ATM, TPSA, Orbis, Amrest. Dla każdej spółki i dla każdego jej notowania dostępne były następujące dane: wartość podczas otwarcia oraz zamknięcia notowania, wartość minimalna i maksymalna w danym dniu, zmiana notowania, dzienny obrót.

5.2 Schemat hurtowni danych. Zaproponowany schemat przewiduje istnienie jednej tabeli faktów (Notowanie ), oraz czterech wymiarów: Czas, Rok, Sektor, Index. Hyperion proponuje stosowanie określenia „element” (member) zarówno jako terminu opisującego wystąpienie wymiaru, wymiaru nadrzędnego, bądź też konkretnej wartości wymiaru. W zastosowanym schemacie element Czas jest więc nadrzędny w stosunku do elementów grupujących wystąpienia wymiaru (poszczególne dni) w miesiące, a Sektor posiada elementy podrzędne określające typ działalności spółek do niego należących (banki,

informatyka, p_metalowy, telekomunikacja, usługi ). Utworzono bazę danych spolkiDB w aplikacji SPOLKI.

Rys. 4.1 Tabela faktów (Notowanie) oraz wymiar Index

15

Rys. 4.2 Elementy: Rok, Czas, Sektor wraz z elementami podrzędnymi.

Rys. 4.3 Pojedyncze wystąpienia dni w elemencie agregującym [Jan] (styczeń).

16

5.3 Dostępne dane Udostępnione do analizy dane znajdowały się w plikach .csv o następującym formacie: Data;Otw.;Min;Maks.;Zamkn.;Zmiana;Obrót 2006.03.31;147.00;146.00;148.50;147.00;+1.03;85944 2006.03.30;145.00;145.00;147.00;145.50;+1.39;51777 2006.03.29;144.00;141.00;144.00;143.50;-1.03;28302 2006.03.28;149.00;145.00;149.00;145.00;-2.36;20590 (…)

W celu dostosowania danych do wymaganego przez Hyperion formatu i zgodności z skonstruowanym schematem napisany został skrypt w języku AWK: { gsub (";", "\t"); if (NR == 1) {print;} if (NR == 2) {print;} if(NR > 2) { gsub("+", ""); if($0 ~ /^2005\.*/) { gsub("2005.01.", "\"Jan "); gsub("2005.02.", "\"Feb "); gsub("2005.03.", "\"Mar "); gsub("2005.04.", "\"Apr "); gsub("2005.05.", "\"May "); gsub("2005.06.", "\"Jun "); gsub("2005.07.", "\"Jul "); gsub("2005.08.", "\"Aug "); gsub("2005.09.", "\"Sept "); gsub("2005.10.", "\"Oct "); gsub("2005.11.", "\"Nov "); gsub("2005.12.", "\"Dec "); print "\"2005\"\t" $1 " " $2 "\"\t" $3"\t" $4"\t" $ 5"\t" $6"\t" $7"\t" $8; } else if($0 ~ /^2006\.*/) { gsub("2006.01.", "\"Jan "); gsub("2006.02.", "\"Feb "); gsub("2006.03.", "\"Mar "); gsub("2006.04.", "\"Apr "); gsub("2006.05.", "\"May "); gsub("2006.06.", "\"Jun "); gsub("2006.07.", "\"Jul "); gsub("2006.08.", "\"Aug "); gsub("2006.09.", "\"Sept "); gsub("2006.10.", "\"Oct "); gsub("2006.11.", "\"Nov "); gsub("2006.12.", "\"Dec "); print "\"2006\"\t" $1 " " $2 "\"\t" $3"\t" $4"\t" $ 5"\t" $6"\t" $7"\t" $8; } } }

17

Wynikiem działania skryptu jest plik tekstowy o następującym formacie: PROKOM WIG20

Otw. Min Maks. Zamkn. Zmiana Obrót "2006" "Mar 31" 147.00 146.00 148.50 147.00 1.03 85 944 "2006" "Mar 30" 145.00 145.00 147.00 145.50 1.39 51 777 "2006" "Mar 29" 144.00 141.00 144.00 143.50 -1.03 2 8302

Dane wczytano do systemu wybierając opcję Load Data ( � Load Data from File ) z menu kontekstowego bazy danych spolkiDB .

5.4 Zapytania

5.4.1 Wycięcie oraz obrót

5.4.1.1 Wycięcie jednej warstwy kostki danych (zapytania 1, 2) Zapytanie o obrót z dnia 09.05.2005 dla spółek sektora bankowego wchodzących w skład indeksu WIG20: select

{[Obrót]} on rows, { ([May 09], [2005], [banki], [wig20])} on columns

from SPOLKI.spolkiDB;

Równoznaczne zapytanie z zastosowanie prefiksów w postaci nazw wymiarów (dla czytelności zapytania): select

{[Obrót]} on rows, {

( [czas].[May 09], [rok].[2005], [sektor].[banki], [index].[wig20]

) } on columns

from SPOLKI.spolkiDB;

„Columns” i „rows” mogą zostać zastąpione przez „axis(1)” i „axis(0)”: select {[Obrót]} on axis(1), { ([May 09], [2005], [banki], [wig20])} on axis(0) from SPOLKI.spolkiDB;

18

Wynik zapytania:

Zapytanie o obrót wszystkich spółek wchodzących w skład indeksu WIG20 (dla wszystkich notowań w bazie danych): select

{[Obrót]} on rows, {[wig20]} on columns

from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.1.2 Wycięcie więcej niż jednej warstwy kostki danych (zapytanie 3) Zapytanie o obroty (w roku 2005) spółki Techwig oraz wszystkich spółek wchodzących w skład indeksu WIG20: select {[Obrót]} on rows, { ([techwig], [2005]), ([wig20], [2005])} on colum ns from SPOLKI.spolkiDB;

Zastosowanie nawiasów okrągłych ‘()’ oznacza rozpoczęcie nowej kolumny w wyniku zapytania. Równoważne zapytanie może być sformułowane z wykorzystaniem funkcji CrossJoin, lub za pomocą klauzuli ograniczającej: select {[Obrót]} on rows, crossjoin({[techwig], [wig20]}, {[2005]}) on colum ns from SPOLKI.spolkiDB; select {[Obrót]} on rows, {[techwig], [wig20]} on columns from SPOLKI.spolkiDB where ([2005]);

19

Wynik zapytania:

5.4.1.3 Wycięcie z kostki za pomocą zakresu wymiarów (zapytania 4, 5) Zapytanie o obrót spółek z notowań od stycznia do marca, z podziałem na lata: select { ( [Obrót], [2005] ), ( [Obrót], [2006] ) } on ro ws, {[Jan]:[Mar]} on columns from SPOLKI.spolkiDB;

Równoważne zapytanie może zostać sformułowane z użyciem funkcji MemberRange: select { ( [Obrót], [2005] ), ( [Obrót], [2006] ) } on ro ws, {MemberRange([Jan],[Mar])} on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obrót spółek wchodzących w skład indeksu WIG20 w dniach od 12.01.2005 do 14.01.2005: select crossjoin ({[Obrót]}, {[wig20]}) on rows, crossjoin ({Memberrange([Jan 12], [Jan 14])}, {[20 05]}) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

20

5.4.2 Drill up i Drill down

5.4.2.1 Drill up i Drill down z wykorzystaniem standardowych funkcji (zapytania 6, 7, 8)

Zapytanie o obroty w roku 2005 wszystkich spółek. Wynik przedstawiony z podziałem na sektory. select drilldownbylayer ({ [sektor]} ) on columns, {([Obrót], [2005])} on rows from spolki.spolkidb;

Wynik zapytania:

Zapytanie o obroty wszystkich elementów podrzędnych w stosunku do elementu Sektor (aż do pierwszego poziomu w pokoleniu) z notowań z roku 2005: select drillupbylayer ( {sektor, [sektor].children }, ge nerations (sektor, 1) ) on columns, {([Obrót], [2005])} on rows from spolki.spolkidb;

Wynik zapytania:

Zapytanie o obroty wszystkich elementów podlegających pod spółkę PKOBP. select drillupmember({[PKOBP]}, {[sektor]}) on columns, {Obrót} on rows from spolki.spolkidb;

Na wyniku przeprowadzana jest operacja DrillUp aż do elementu sektor, jeśli tylko sektor jest elementem nadrzędnym w stosunku do spółki PKOBP. Wynik zapytania:

21

5.4.2.2 Operacje Drill up i Drill down z wykorzystaniem własności drzewa hierarchii elementów (zapytania 10, 11, 12, 13)

Zapytanie o obroty (w roku 2005) wszystkich sektorów spółek znajdujących się na tym samym poziomie w drzewie hierarchii elementów co sektor informatyka: select {([Obrót], [2005])} on rows, { [informatyka].siblings } on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obroty (w roku 2005) wszystkich spółek elementu nadrzędnego w stosunku do elementu informatyka: select {([Obrót], [2005])} on rows, { [informatyka].parent } on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obroty (w roku 2005) wszystkich spółek podrzędnych w stosunku do elementu informatyka: select {([Obrót], [2005])} on rows, { [informatyka].children } on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obroty (w roku 2005) spółki, która została zdefiniowana jako pierwszy element podrzędny w stosunku do elementu informatyka:

22

select {([Obrót], [2005])} on rows, { [informatyka].firstchild } on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obroty (w roku 2005) wszystkich elementów leżących o jeden poziom niżej w hierarchii niż element sektor wraz z agregacją do poziomu sektor: select crossjoin ({[Obrót]},{ members(sektor.levels(1)), members(sektor.levels(0))}) on rows, {[2005]} on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3 Funkcje standardowe j ęzyka MDX w implementacji Hyperion

5.4.3.1 Distinct (zapytanie 14) Zapytanie o obroty (w roku 2005) wszystkich spółek z sektora informatyka, z eliminacją duplikujących się krotek:

23

select {([Obrót], [2005])} on rows, distinct ({ [informatyka].children}) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3.2 Except (zapytanie 15) Zapytanie o obroty (w roku 2005) wszystkich spółek z sektora informatyka, które równocześnie nie należą do sektora bankowego. select {([Obrót], [2005])} on rows, except ({ [informatyka].children}, { [banki].chil dren}) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3.3 Head, Tail (zapytania 16, 17) Zapytanie o obroty (w roku 2005) pierwszego elementu (znajdującego się w drugim pokoleniu elementu sektor). „Pierwszy” wskazuje w tym przypadku kolejność definiowania elementów podczas tworzenia bazy danych. select {([Obrót], [2005])} on rows, head( members([sektor].generations(2))) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

Zapytanie o obroty (w roku 2005) ostatniego elementu (znajdującego się w drugim pokoleniu elementu sektor). „Ostatni” wskazuje w tym przypadku kolejność definiowania elementów podczas tworzenia bazy danych.

24

select {([Obrót], [2005])} on rows, tail( members([sektor].generations(2))) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3.4 Union (zapytanie 18) Zapytanie o obroty (w roku 2005) spółek, które należą do sektora informatyka, lub banki: select {([Obrót], [2005])} on rows, union ({ [informatyka].children}, { [banki].childr en}) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3.5 Subset (zapytanie 19) Zapytanie o obroty (w roku 2005) wszystkich spółek z sektora informatyka, z pominięciem pierwszej spółki. „Pierwsza” wskazuje w tym przypadku na kolejność definiowania elementów podczas tworzenia bazy danych. select {([Obrót], [2005])} on rows, subset ({ [informatyka].children}, 1) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.3.6 Intersect (zapytanie 20) Zapytanie o obroty (w roku 2005) wszystkich spółek, które są podrzędne w stosunku do elementu informatyka, oraz należą do jakiegoś sektora:

25

select {([Obrót], [2005])} on rows, intersect ({ members([sektor])}, {[informatyka].ch ildren} ) on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.4 Elementy obliczone (calculated members) (zapy tanie 21) Elementy obliczone mogą być traktowane jako funkcje tworzone na czas trwania zapytania. Zdefiniowany w na początku zapytania element obliczony może być wykorzystywany w dalszej części zapytania, poprzez wyszczególnienie jego nazwy. Unika się w ten sposób wielokrotnych obliczeń i zwiększa przejrzystość zapytania. Zapytanie o średnią wartość otwarcia, w roku 2005 dla wszystkich elementów z sektora bankowego: with member [notowanie].[srednia] as 'avg ( {[Otw.]}, [2005] )' select {([banki].children)} on rows, {[notowanie]. [srednia]} on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.5 Nazwane zbiory (named sets) (zapytanie 22) Nazwane zbiory mogą być traktowane jako podzapytania tworzone na czas trwania zapytania głównego. Nazwany zbiór posiada nazwę, przez którą może on być wywoływany z zapytania głównego. Zapytanie o obroty trzech elementów z najniższego poziomu hierarchii elementów, których elementem nadrzędnym jest sektor, które to elementy miały najwyższe obroty w styczniu 2005 roku: with set [najlepsi] as 'topcount (members([sektor].levels(0)), 3, ([Obrót] , [Jan], [2005]))' select {[najlepsi]} on rows, {[Obrót]} on columns

26

from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.6 Filtrowanie wyników zapyta ń (zapytanie 23) Nakładany na zapytanie filtr ma na celu ograniczenie wyników zapytania do żądanego podzbioru. Funkcja filter jest funkcją iteracyjną, co oznacza, że pewien fragment polecenia wykonywany jest dla każdej zwróconej w zapytaniu krotki. Nawigację po iteracji zapewnia funkcja currentMember. Zapytanie o obroty (w roku 2005) wszystkich elementów podrzędnych w stosunku do elementu sektor, których element nadrzędny to element banki: select {([Obrót], [2005])} on rows, {filter([sektor].members, ischild ([sektor].curren tmember, [banki]))} on columns from SPOLKI.spolkiDB;

Wynik zapytania:

5.4.7 Predykcja warto ści (zapytanie 24) Przeprowadzony eksperyment polegał na zbudowaniu modelu regresji liniowej i oszacowaniu, na jego podstawie, wartości elementów bazy danych. Hyperion nie udostępnia standardowej funkcji do wyznaczania regresji liniowej. Rozwiązaniem jest zdefiniowanie skryptu przeliczającego bazę danych (calculation script) i dokonanie przeliczenia wartości przechowywanych w bazie. Skrypt przeliczający został zdefiniowany w następujący sposób: Obrót (@TREND(@LIST(Jan, Feb, Mar),@LIST(1,2,3),,, @LIST(4, 5, 6, 7, 8, 9, 10, 11,12), Apr: Dec, LR););

Parametr LR wskazuje na model regresji (linear regression). System Hyperion udostępnia również inne modele:

27

Nazwa parametru metoda

SES Single exponential smoothing DES Double exponential smoothing TES Triple exponential smoothing Na podstawie danych dotyczących obrotów spółek w pierwszych trzech miesiącach roku, wyznaczamy model regresji liniowej, który dokona predykcji wartości pozostałych miesięcy w roku. Zapytanie: select {([Jan] : [Dec])} on rows, {([Obrót], [2005]), ([Obrót], [2006])} on columns from spolki.spolkiDB;

Wynik zapytania przed wykonaniem skryptu przeliczającego:

Wynik zapytania po wykonaniu skryptu przeliczającego:

28

Niestety nie można bezpośrednio wyznaczyć równania regresji liniowej. Rozwiązaniem jest wykonanie powyższego zapytania w Hyperion Visual Explorer (lub Hyperion Essbase Query Designer), eksport wyniku zapytania do pliku Microsoft Excel, narysowanie wykresu, a następnie dodanie linii trendu (z opcją dodania równania R do wykresu). Jak widać, model regresji napisał istniejące dane (zbiorcze dane dotyczące obrotów spółek w poszczególnych miesiącach były już w bazie danych). Powtórne wykonanie domyślnego skryptu kalkulacyjnego (default calculation script) umożliwi powrót do pierwotnych wielkości istniejących wcześniej agregatów (niestety z błędami zaokrągleń):

5.4.8 Sortowanie wyników zapyta ń (zapytanie 25) Do sortowania wyników zapytań służy standardowa funkcja Order. Parametr BDESC oznacza sortowanie od wyniku największego do najmniejszego, a parametr BASC od wyniku najmniejszego do największego. Zapytanie o obroty (z 2005 roku) wszystkich spółek. Wynik przedstawiony od spółki o najwyższych obrotach do spółki o najniższych obrotach. select order (crossjoin ({[Obrót]},{ members(sektor.level s(0))}), Obrót, bdesc) on rows, {[2005]} on columns from SPOLKI.spolkiDB;

Wynik zapytania:

29

6 Podsumowanie Implementacja Hyperion języka MDX jest bardzo rozbudowana, co nie zmienia faktu, iż zestaw dostępnych funkcji standardowych powinien być bogatszy. Brakuje przede wszystkim funkcji operujących na ciągach znaków i datach (np. brak możliwości automatycznej konwersji, czy też wyekstrahowania poszczególnych pól daty). Utrudnieniem jest również brak standardowej funkcji wyznaczania regresji liniowej (mimo, iż standard MDX firmy Microsoft ją przewiduje). Definiowany skrypt kalkulacyjny pełni swoją rolę tylko połowicznie. Przeliczenie bazy danych z wykorzystaniem skryptu definiującego regresję liniową może powodować nadpisanie istniejących danych (jeśli istniały dane, których wartości mają zostać przez model regresji przewidziane), co pokazano w punkcie 5.4.7. Zawiodło narzędzie Hyperion Visual Explorer. Nie oferuje ono niestety wysokiej funkcjonalności i sprawia wrażenie aplikacji udostępniającej tylko bardziej przyjazny interfejs do programu Microsoft Excel. Lepiej pod tym względem prezentuje się Hyperion Essbase Query Designer. Raz zbudowawszy zapytanie i wyświetliwszy jego wyniki w komórkach programu Microsoft Excel, można dokonywać operacji DrillUp i DrillDown nie definiując nowego zapytania. Nawigacja po danych ukrytych w drzewie hierarchii elementów odbywa się za pomocą kliknięć myszką na poszczególnych komórkach arkusza kalkulacyjnego. Zaletą systemu jest szybkość jego działania. Zarówno serwer bazy danych, jak i konsola administracyjna pracowały na jednym komputerze (system operacyjny: Windows 2000 Professional, pamięć RAM: 256MB, procesor: Athlon 1700XP), a szybkość działania systemu oraz wykonywania zapytań była zadziwiająca.

7 Literatura Dokumentacja techniczna systemu Hyperion.