Język MDX i obliczenia w kostkach analitycznych
Zawartość strony
 | Wstęp |
| Zapytanie MDX |
| Przykładowa kostka analityczna |
| Mechanizmy obliczeniowe |
Wstęp
Mechanizmy obliczeń wykorzystywane w kostce analitycznej zakładają realizacje agregacji dla poszczególnych poziomów w hierarchii wymiarów. W większości zastosowań realizacja tylko mechanizmów sumowania na poziomie hierarchii jest niewystarczająca. Z reguły w procesach analitycznych pojawia się konieczność obliczeń związanych z porównywaniem wartości z dwóch okresów (lat, miesięcy), czy też porównywanie wartości sprzedaży jednostkowej do całości wyrażonej w procentach. Przykłady tego typu obliczeń można mnożyć w nieskończoność. Pojawia się pytanie - w jaki sposób można rozbudować mechanizmy obliczeń w kostce analitycznej. Wykorzystamy w tym celu mechanizmy oferowane przez język MDX (Multidimensional Expression).
Podobnie jak SQL, język MDX jest językiem zapytań. Język SQL jest potężnym narzędziem wykorzystywanym w środowisku baz relacyjnych, jednak w nie sprawdza się w środowiskach wielowymiarowych struktur bazodanowych – bazy OLAP. MDX jest językiem zapytań w strukturach baz OLAP, umożliwiając zadawanie zapytań on-line w kostkach analitycznych. Pod pojęciem zapytań, podobnie jak w przypadku SQL, rozumiemy również możliwość modyfikowania danych – a więc w przypadku kostek analitycznych – możliwość realizacji specyficznych obliczeń. Obecnie język MDX staje się standardem przemysłowym, całkowicie niezależnym od języka SQL.
Zapytanie MDX
Zapytanie MDX zawiera słowo kluczowe SELECT (definiuje dane wynikowe), słowo kluczowe FROM (definiuje kostkę wejściową) oraz słowo kluczowe WHERE (określa filtrowanie danych). W zakresie języka dostępnych jest szereg funkcji, zarówno matematycznych, jak i statystycznych oraz operowania na ciągach znakowych. Dodatkowo możliwe jest definiowanie obliczanych wartości pomiarowych oraz przypisywanie nazwy zbiorom wymiarów (name set). Służy do tego klauzula WITH. Pełną składnię zapytania MDX możemy przedstawić:
[WITH <specyfikacja formuły> [,<specyfikacja formuły> …]] SELECT [<specyfikacja osi> [,<specyfikacja osi> …]] FROM [<specyfikacja kostki>] [WHERE [<specyfikacja warunków]]
Fraza SELECT określa wynikowy zbiór komórek oraz sposób prezentacji. Prezentacja zbioru jest określana przez przyporządkowanie osiom ( axis) punktów przestrzeni wielowymiarowej. Istnieje 128 osi, przy czym pięć pierwszych ma przypisane nazwy:
0 – COLUMNS 1 – ROWS 2 – PAGES 3 – SECTIONS 4 – CHAPTERS
a do pozostałych odwołujemy się poprzez kolejne liczby wyrażeniem AXIS(index). Innymi słowy, specyfikację osi możemy zapisać:
<specyfikacja osi>:: <zbiór punktów> ON <nazwa osi>
a z kolei nazwa osi:
<nazwa osi>:: COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS | AXIS (index)
Przykładowe zapytanie może wyglądać następująco:
SELECT {[ALL YEAR], 2004, 2005, 2006 } ON COLUMNS,
{[ALL Miasta], Poznań, Gdańsk, Warszawa} ON ROWS
W efekcie możemy uzyskać następujące struktury danych:
| ALL YEAR | 2004 | 2005 | 2006 | |
ALL MIASTA | 620 | 120 | 200 | 300 |
Poznań | 250 | 50 | 100 | 100 |
Gdańsk | 200 | 50 | 50 | 100 |
Warszawa | 170 | 20 | 50 | 100 |
Do zdefiniowania zbioru punktów możemy również wykorzystać funkcję Members, która zwraca wszystkie człony z wymiaru, hierarchii lub poziomu, do których jest stosowana:
SELECT {[ALL YEAR], 2004, 2005, 2006 } ON COLUMNS,
Miasta.Members ON ROWS
Dodając do tego odpowiednie warunki, możemy z naszej kostki wycinać „plastry” określonych zbiorów danych:
SELECT {[ALL YEAR], 2004, 2005, 2006 } ON COLUMNS,
Miasta.Members ON ROWS
FROM SalesCube
WHERE ([Kowalski], LiczbaKlientów)
Powyższe zapytanie spowoduje wyświetlenie odpowiednio zagregowanych wartości dotyczących liczby klientów dla sprzedawcy o nazwisku Kowalski. Pozostaje uzupełnić nasze zapytanie o klauzule WITH, definiującą określone mechanizmy obliczeniowe:
WITH MEMBER Measures.Zysk AS ‘(Przychod –Koszt)/Koszt*100’
SELECT {[ALL YEAR], 2004, 2005, 2006 } ON COLUMNS,
Miasta.Members ON ROWS
FROM SalesCube
Jak wcześniej pisałem, klauzula WITH umożliwia również definiowanie nazw zbiorów. Poniższy przykład ilustruje ten mechanizm:
WITH SET RegionZachod AS ‘(Gniezno, Poznan, Leszno)’
SELECT {Przychod, LiczbaKlientow) ON COLUMNS,
RegionZachod ON ROWS
FROM SalesCube
Przykładowa kostka analityczna
Opisane elementy języka MDX są obecnie wykorzystywane praktycznie we wszystkich środowiskach baz OLAP. W środowisku MS SQL 2005 wprowadzono znaczące rozszerzenia tego języka, zarówno w procesach obliczeniowych jak i agregowania danych. Przy pomocy języka MDX możemy teraz zarówno definiować strukturę kostki, wypełniać danymi oraz wykonywać określone obliczenia.
Definicja przykładowej kostki analitycznej:
CREATE CUBE SalesCube
(DIMENSION Time TYPE TIME,
HIERARCHY [Fiscal],
LEVEL [Fiscal Year] TYPE YEAR,
LEVEL [Fiscal Qtr] TYPE QUARTER,
LEVEL [Fiscal Month] TYPE MONTH,
HIERARCHY [Calendar],
LEVEL [Calendar Year] TYPE YEAR,
LEVEL [Calendar Month] TYPE MONTH,
.
.
.
MEASURE [$ Sales]
FUNCTION SUM
FORMAT 'Currency'
MEASURE [Units Sold]
FUNCTION SUM)
Wypełnianie danymi:
INSERT INTO SalesCube
(Time.Year,
Time.Quarter,
Time.Month,
Product.[Product Name],
Measures.Quantity)
SELECT Sales.[time:Year],
Sales.[time:Quarter],
Sales.[time.Month],
Sales.[product:product name],
Sales.[measures:line item quantity]
FROM Sales
Rezultat obliczeń wyrażenia MDX w kostce analitycznej jest zależny od bieżącego kontekstu kostki. Jeśli przy pomocy filtrowania wytniemy fragment kostki (sprzedaż danego produktu w danym kraju dla danego sprzedawcy), wyrażenie MDX wyliczy wartości dla wybranej części kostki.
Mechanizmy obliczeniowe
Tworzenie mechanizmów obliczeniowych przy pomocy języka MDX w środowisku SQL 2005 zostało znacząco uproszczone. Są one elementem budowy kostki analitycznej dostępnym w zakładce Calculations, gdzie składnia zapytania, umieszczona w określonych polach, gwarantuje poprawność wykonywania obliczeń. Wartości obliczeniowe (Calculated members) są elementami budowy kostki analitycznej, umożliwiającej realizacje obliczeń w momencie wyświetlania kostki. Calculated members wykorzystuje się, gdy obliczenia jakie wykonujemy w kostce są nie addytywne (sumowanie, średnia itp.).
W naszych przykładach posłużymy się hurtownią danych utworzoną na podstawie przykładowej bazy AdventureWorks. Struktura hurtowni danych przedstawiona jest poniżej.
Hurtownia danych składa się z trzech tabel faktów. Zawierają one dane o sprzedaży rowerów przez resellerów, bezpośrednią sprzedaż internetową oraz trzecia tabela zawiera założenia sprzedaży dla poszczególnych handlowców. Jednocześnie wartości pomiarowe w każdej tabeli tworzą odpowiednie grupy pomiarowe. Z tabelami faktów powiązane są odpowiednie tabele wymiarów, na podstawie których utworzone są wymiary: Produkt, Internet Customer, Reseller, Sales Person, Promotion oraz Time.
Rys. 1. Struktura hurtowni danych.
Rys. 2. Struktura kostki analitycznej.
W naszym przykładzie przy pomocy calculate members wyliczymy całkowitą sprzedaż z kanału reselerskiego i sprzedaży internetowej oraz udział procentowy sprzedaży poszczególnych produktów w stosunku do całości sprzedaży. Ponieważ w obliczeniach udziału procentowego sprzedaży, będziemy wykorzystywali sprzedaż całkowitą, musimy zachować odpowiednią kolejność obliczeń. W tym celu przechodzimy na zakładkę Calculations i z menu Cube, bądź z paska skrótów, wybieramy opcje New Calculated members. Pojawi się formatka pozwalająca zdefiniować zaplanowane obliczenia oraz inne parametry obliczeń.
Pierwsza wartość obliczeniowa to sprzedaż całkowita – tak też nazwiemy ten obiekt. W tym przykładzie zesumujemy dwie wartości pomiarowe. Możemy w tym celu wykorzystać Calculation Tool i przy pomocy myszki przenieść odpowiednie wartości pomiarowe do pola Expression. Poza tym możemy zdefiniować dodatkowe właściwości, takie jak kolor fontów, tła czy też format wyświetlanej liczby. Możemy także określić, czy obliczenia będą widoczne w kostce. Tę właściwości wykorzystuje się w sytuacji, gdy dokonujemy obliczeń pośrednich, a nie chcemy aby można było te obliczenia pokazywać w przeglądarce kostki analitycznej. Ostatnim elementem, jaki możemy określić, jest parametr: Non-empty Behavior pozwalający na podstawie wskazanej wartości pomiarowej określić zapytania nie zwracające wartości NULL. Jeśli to pole jest puste, zapytania zwracające wartość NULL będą analizowane w trakcie wyliczania calculate members.
Rys. 3. Obliczenie całkowitej sprzedaży.
Przystąpimy następnie do zdefiniowania calculate memebers udziału procentowej sprzedaży poszczególnych produktów w stosunku do całości sprzedaży, wykorzystując wcześniej zdefiniowaną wartość. Poniżej przedstawiono zdefiniowaną wartość obliczeniową.
Rys. 4. Wartość procentowa sprzedaży poszczególnych produktów.
W tym wypadku wyrażenie zawiera wskazanie [Product].[Product Category].[(ALL)].[ALL], pozwalające porównywać całość sprzedaży w wymiarze produkt do poszczególnych produktów i kategorii. Ponadto, w definicji formatu czcionki, użyto funkcji warunkowej, powodującej, że udział procentowy sprzedaży danego produktu w stosunku do całości większy niż 30% jest wyświetlany w kolorze czerwonym. Format wyświetlanej wartości zdefiniowano na procentowy.
Efektem wykonanych obliczeń jest zaprezentowany poniżej ekran przeglądarki kostki analitycznej.
Rys. 5. Efekt wykonanych obliczeń.
W przypadku wykonywania skomplikowanych obliczeń może zaistnieć konieczność skorzystania z debuggera. Procedurę uruchomienia debuggera realizujemy w zakładce calculations wybierając z menu debug opcję start debugging. W tym przypadku w tej zakładce zostaje uruchomiony browser kostki oraz możliwości związane z analizą obliczeń w trakcie jej przeglądania.
Rys. 6. Debuggowanie obliczeń.
Dostępne wartości obliczeniowe są tutaj przedstawione jako pełna definicja języka MDX. Operacja Create powoduje utworzenie danej wartości obliczeniowej. Prezentowany ekran pokazuje wykonanie pierwszego kroku w obliczeniach – tworząc Sprzedaż Całkowitą – ale jeszcze przed utworzeniem Udziału Procentowego. Wykonanie kolejnego kroku, spowoduje utworzenie tej wartości obliczeniowej i pojawienie się jej na liście Metadata. Oprócz tego istnieje możliwość zadawania zapytań MDX w locie (zakładki MDX1, 2,..) i wykonywania obliczeń.
 | Tomasz Skurniak |