Nowoczesne narzędzia analizy danych w Excelu - OLAP

Wojciech Gardziński, Krzysztof Rumiński

Dostawcy systemów informatycznych, dzisiaj można to już powiedzieć jasno i otwarcie, przegrali pewną bitwę. Od wielu lat bowiem usiłowali stworzyć w swoich systemach narzędzia, które pozwalałyby łatwo analizować gromadzone w tychże systemach dane.

Powstawały dziesiątki specjalizowanych „edytorów raportów”, a i tak, ku wielkiemu niekiedy zdziwieniu wdrożeniowców, użytkownicy pieczołowicie przepisywali dane do Excela i tu dopiero powstawały raporty i analizy.

W chwili obecnej standard analizy danych jest już właściwie ustalony. Arkusz kalkulacyjny króluje tu niepodzielnie, z małymi wyjątkami specjalizowanych systemów analitycznych klasy Business Inteligence, ale i one komunikują się z arkuszem na wiele sposobów, czy to wysyłając dane, czy też tworząc kwerendę MS Query lub, bezpośrednio, tabelę przestawną, ze źródłem danych np. w hurtowni danych.

Dlaczego arkusz jest tak popularny w dziedzinie analizy danych?

Wiemy, że jest łatwy, elastyczny, bogaty w funkcje, formaty i szablony. I, pomimo, że często, na tzw. popularnym poziomie jego użytkowania, nie jesteśmy tego świadomi, jest bogaty również w profesjonalne narzędzia analizy danych.

Narzędzia OLAP

To chyba najmniej znana funkcjonalność Excela. OLAP (On-Line Analytical Processing) OLAP jest definiowany jako (definicja z Pomocy MS Excel):

Technologia bazy danych, która została zoptymalizowana ze względu na tworzenie kwerend i raportów, a nie na przetwarzanie transakcji. Dane OLAP są zorganizowane hierarchicznie i przechowywane w modułach, a nie w tabelach. Moduł: Struktura danych OLAP. Moduł zawiera wymiary, na przykład Kraj/Region/Miasto, i pola danych, na przykład Wielkość sprzedaży. Wymiary organizują typy danych w hierarchie z poziomami szczegółów, a pola danych mierzą ilości.

W procesie przetwarzania danych dla celów analitycznych, OLAP ma swoje jasno wytyczone miejsce, niekiedy niewidoczne dla użytkownika końcowego, gdyż oprogramowane w systemach hurtowni danych. Ten jednak, kto takowej hurtowni danych nie posiada, powinien wiedzieć, jak i gdzie technologii OLAP używać.

Rys.1. OLAP w procesie dostarczania informacji biznesowej

źródło: materiały Value Based Advisors Sp. z o.o.

Rys.2. „Wzorcowa” kostka OLAP

Zawarte w standardzie Excela (od wersji 2000), podstawowe funkcjonalności OLAP, takie jak tworzenie modułów OLAP (zwanych popularnie „kostkami”) ze źródeł danych ODBC, dostęp do kostek OLAP, oferowanych przez dostawców aplikacji analitycznych, wystarczają, by sprawny analityk wykorzystywał tę zaawansowaną technologię w swojej codziennej pracy, bez konieczności programowania czegokolwiek. Oczywiście, programując, można uzyskać dużo więcej funkcjonalności.

Skupmy się na funkcjonalności podstawowej – utwórzmy swój własny, niezależny moduł OLAP (tzw. moduł trybu offline, niezależny od bazy danych).

Dysponując dowolną bazą danych, w plikach DBF, arkuszach Excela, Accessa lub innych, dostępnych przez ODBC poprzez MS Query, potrafimy szybko stworzyć kwerendę. Tworząc ją jednak dla potrzeb zbudowania modułu OLAP powinniśmy poświęcić znacznie więcej uwagi na jej właściwe zaprojektowanie. Moduł taki bowiem, będzie się odświeżał potem już bez naszego udziału, a jego przeprojektowywanie zawsze już będzie się wiązać z kosztem uaktualnienia raportów na nim bazujących.

Podstawą każdego zbioru informacji jest jej właściwy wybór ze źródła danych

Rys.3. Kwerenda dla potrzeb modułu OLAP – przykładowe dane handlowe

Po stworzeniu kwerendy w MS Query, wybieramy z menu MS Query opcję Plik / Utwórz moduł OLAP

W pierwszym kroku kreatora, jako pola agregowane, podajemy pola numeryczne. Będą one agregowane, tj. sumowane, do wartości zbiorczych, na każdym etapie analizy kostki.

Rys.4. Definicja pól obliczanych

Tu wybieramy WYŁĄCZNIE pola, które merytorycznie jest sens sumować. Kreator proponuje sumowanie wszystkich pól numerycznych, ale suma np. cen jest bezwartościowa. Lepiej, gdyby cena została użyta jako podwymiar pola towar, umożliwiałoby to analizę, jaki towar PO JAKICH CENACH się sprzedawał.

Drugi krok polega na stworzeniu właściwej struktury danych.

Rys.5. Tworzenie struktury danych modułu OLAP:

Tu z kolei ustawiamy główne wymiary i strukturę tych wymiarów w kostce. Proszę zwrócić uwagę, że chcąc danego pola użyć w dwóch, niezależnych wymiarach (np. nazwy kontrahenta), należało UPRZEDNIO pole to zduplikować.

W trzecim kroku kreatora definiujemy miejsce zapisu kostki OLAP, możemy go zapisać na serwerze OLAP (Serwer bazy danych SQL Server posiada taką funkcjonalność) lub do pliku modułu off-line (*.CUB). Wybieramy opcję pliku, wskazując miejsce jego zapisu. To ważny krok – od razu wskazujemy DOCELOWE miejsce przechowywania kostki, najlepiej w sieci.

Rys.6. Zapisanie modułu off-line

Czwarty krok to wskazanie miejsca zapisu definicji kwerendy, która nam kostkę na żądanie odświeży i… gotowe.

Rys.7. Zapisanie kwerendy

Dalej następuje już standardowa seria pytań, czy otworzyć pod Excelem, gdzie wkleić, itp.

Gdy potwierdzimy, że chcemy przenieść dane do Excela, dane zostają wklejone w postaci… tabeli przestawnej. Modułu OLAP nie można inaczej analizować, jest to więc modelowo odporne na niewłaściwe użycie.

Rys.8. Wymiary kostki OLAP

Pomimo, że nasza kwerenda początkowo zawierała kilkadziesiąt pól, do modułu zostały zapisane dane, wyłącznie wartościowe dla tego modułu, czyli te pola, które wchodziły w skład zdefiniowanych wymiarów kostki. Wszystkich tych pól nie widać – dopiero umieszczenie wymiaru czasu (tu: DF_ROK) powoduje dostęp do podstruktury czasowej: miesięcy i dni.

Rys.9. Struktura kostki OLAP

Plik z rozszerzeniem .OQY (definicja kwerendy modułu OLAP) możemy otwierać również bezpośrednio z pliku – otwiera nam się Excel z, gotową do definicji, tabelą przestawną, na odświeżonych (ponownie obliczonych) danych.

Tak więc, tworzenie modułu OLAP nie jest ani trudne, ani skomplikowane, ani tym bardziej technologicznie niedostępne. Kwerenda odświeża moduł automatycznie, nadaje się on również do transportu danych, np. wysłania pocztą elektroniczną – traci jednak wtedy możliwość odświeżania danych.

Modułów w ten sposób utworzonych, można również używać jako ELEMENTÓW większych całości, np. firmowej hurtowni danych. Znamy przypadek, gdy tworzona Excelem kostka wielowymiarowych kosztów (i dodatkowo poatrybutownych zewnętrznymi słownikami), stawała się pełnoprawnym elementem systemu informacyjnego dużej korporacji międzynarodowej. Wszystko to z prozaicznego powodu – polski oddział był jedynym (i takim pozostał, ponieważ… działało), w którym, nie we wszystkich jego oddziałach, został wdrożony bardzo drogi system ERP z towarzyszącą mu hurtownią danych. Excel więc, z powodzeniem, nadrabiał braki oprogramowania.

Producent systemu ERP przegrał tu może pewną bitwę – wygrał jednak jego klient, niewątpliwie zadowolony użytkownik, zarówno arkusza kalkulacyjnego, jak i powyższego systemu ERP – umożliwiał on bowiem podłączanie „obcych” kostek do systemu informacji biznesowej w swojej hurtowni danych.