Ich obróbka trwa długo, niezależnie, w jakim są formacie – czy dostępne w firmowej bazie danych, czy, już po eksporcie i imporcie do Excela, w Excelu.

Nie jest celem tego artykułu wyjaśniać korzyści, płynące z użycia danych zagregowanych oraz z użycia technologii OLAP, jako takiej – odsyłamy do powszechnie dostępnych opracowań.

Dla nas, analityków, jest ważne, że Excel potrafi, i to chyba najlepiej, z nich korzystać. To taka tabela przestawna, zawarta w stosunkowo małym i bardzo szybkim w odczycie, pliku .CUB.

Czy technologia OLAP to wielkie serwery, konsultanci i ogromne koszty?

Nie. I to jest właśnie najważniejsze!

Kostkę (tzw. Moduł OLAP) można zdefiniować, tworzyć, poprawiać, odczytywać (wieloma sposobami) z poziomu Excela. Nie jest wymagana żadna wiedza dodatkowa – jeśli wiesz, co to jest tabela przestawna, możesz startować od razu.

W Excelu 2007 funkcjonalność ta została definitywnie usunięta – już nie można tworzyć nowych kostek. Ale ich odczyt działa i to działa coraz lepiej – pojawiły się dodatkowe funkcje Excela, odczytujące kostki OLAP – funkcje te nie są, co prawda, zbyt funkcjonalne, ale są!

Plik .CUB – jego konstrukcja jest znana od 10-ciu lat. W Excelu (do wersji 2003) zaimplementowano kreatora, który pozostawiał wiele do życzenia – jego największymi wadami były:

Konieczność budowania kostki ZAWSZE OD NOWA
To poważna wada – przy skomplikowanych kostkach, proces ich definicji był bardzo żmudny: kilka wymiarów po kilka poziomów każdy, wiele miar. Każda z nazw wymiarów oraz każdego ich poziomu, jak również i miar, są zwykle dostosowywane ręcznie w terminologii biznesowej. Poza tym, gdy tworzy się kostkę, często brakuje informacji – trzeba się więc „wycofać” do definicji samej kwerendy – nie było to możliwe.
Co prawda, istniała możliwość częściowej przebudowy kostki poprzez edycję pliku .OQY, ale tylko hipotetycznie – nikt z tego nie korzystał, bo działało tylko w bardzo ograniczonym zakresie (nie można było edytować kwerendy - źródła danych), a czasami w ogóle nie działało.
Niemożność wykorzystania dwa (wiele) razy tego samego pola w kostce
Problem w tym, że potrzeba użycia takiego pola występowała zawsze, gdy definicja kostki była już bardzo zaawansowana, czyli, gdy potrzeba się pojawiła, zawsze trzeba było robić wszystko od nowa.
Niemożność zapisu kostki w innym miejscu, niż zapisał ją kreator (parametryzacja zapisu) – miejsce zapisu kostki (plik .CUB) było zapisane w pliku definicji kostki (.OQY)
W technologii OLAP funkcjonuje pojęcie ‘Data Mart’-u, czyli kostki, będącej wydzieloną częścią innej, większej kostki, według określonych wartości jednego lub więcej wymiarów, np. kostka budżetowa (wyłącznie) Wydziału A dla (wyłącznie) 2010 roku. Jest wiele powodów dzielenia kostek, oto niektóre z nich:

Tajemnica danych (odbiorca kostki nie powinien wiedzieć o danych innego, np. szef centrum kosztowego nie powinien mieć wglądu w koszty innego CK)
Zmniejszenie rozmiaru kostki dla jej szybszego działania lub, w przypadku kostek .CUB, możliwej wysyłki mejlem.

Niemożność własnej definicji wymiaru ‘Czas’ – gdy w kreatorze używano pola w formacie daty, kreator rozpoznawał go i od razu dzielił na hierarchię czasu. Gdy nam to odpowiadało, było to bardzo efektywne (i efektowne) – gdy jednak chcieliśmy wymiar czasu celowo podzielić a poszczególne jego poziomy uczynić wymiarami niezależnymi, było to niemożliwe, gdyż… wymagało powtórnego użycia tego samego pola drugi (i kolejne) raz, a to, jak wspomniano wyżej, było niemożliwe.

W związku z powyższym, AFIN.NET posiada własnego kreatora kostek – AFIN.NET.CubeBuilder, działającego bez problemu zarówno w Excelu 2003, jak i 2007 oraz 2010.

Kreator ten umożliwia ponadto:

Wielokrotną zmianę definicji kostki
Powrót do kwerendy i dowolną zmianę źródła danych w każdym momencie, zarówno w trakcie tworzenia kostki, jak i potem, gdy kostka jest już gotowa
Dodawanie wielokrotnie tego samego pola do wielu wymiarów, również pod jednakowymi nazwami
Odświeżanie kostki „czystym” Excelem, czyli Excelem bez programu AFIN.NET – w przeciwieństwie do innych kreatorów OLAP, które wymagają każdorazowego uruchomienia programu, w którym kostka została zdefiniowana.

Nie umożliwia parametryzowania miejsca zapisu, ale temat ten jest rozwiązany przez AFIN.NET.InformationServices i zostanie wyjaśniony w dalszej części.

Budujemy kostkę

Przygotowujemy dane dla kostki

Kwerenda

Podobnie jak w standardowym kreatorze OLAP Excela, bazą stworzenia kostki jest kwerenda MS Query. Im lepiej znasz to narzędzie, tym lepsze, czyli bardziej wydajne oraz bardziej skomplikowane, kostki utworzysz.

W naszym, podstawowym, przykładzie, posłużymy się trzema tabelami, połączonymi relacjami – na ich podstawie utworzymy kwerendę – szeroką” tabelę, którą następnie wykorzystamy przy budowie kostki. Nasza kostka będzie zawierała następujące wymiary:

trzy wymiary podstawowe „Geografia” ([region].[miasto]), „Grupa klientów” ([gr_odb]), „Klient” ([nazwa].[nr_fakt])
dwa wymiary pomocnicze, będące, w gruncie rzeczy, jednym, podstawowym wymiarem czasu, ale – celowo – wykonanym w formie 2 wymiarów niezależnych: „Rok” (rok z [data]) i „Miesiąc” (miesiąc z [data])

oraz

jedną miarę „Sprzedaż” (Suma z [wart_net])

Tworzymy kwerendę MS Query

Kwerenda zawiera trzy tabele: [region], [odbiorca] i [faktura]. Są one połączone relacjami.

Trzy tabele są nam potrzebne do uzyskania w kwerendzie wszystkich, niezbędnych nam, pól danych.

(nie ma tu widoków wszystkich kroków – odsyłamy do opracowań nt. MS Query)

Tworzymy nowe pola danych dla wymiarów lub miar kostki

Tworzymy od razu nowe pola, [Rok] i [Mc]

Pole Rok=Format(faktura.data;’yyyy’)

Pole Mc=Format(faktura.data;’mm’)

UWAGA!

Kreator Kostek AFIN.NET wymaga, by użyte w formułach dodatkowych pól nazwy pól były tzw. „nazwami kwalifikowanymi” w rozumieniu ODBC, tj. zawierały, oprócz nazwy samego pola danych, również nazwę tabeli, z której pole pochodzi (rozdzielone kropką), czyli nie „Format(data;’yyyy’)” tylko „Format(faktura.data;’yyyy’)”. Dotyczy to również sytuacji, gdy formuła budowana jest na pojedynczej tabeli w kwerendzie.

Po zwróceniu danych do arkusza, nadajemy temu arkuszowi nazwę Query. Będzie on, a właściwie kwerenda w nim zawarta, przechowywać informacje o źródle danych naszej kostki.

W tej chwili jesteśmy już gotowi do rozpoczęcia definicji kostki

Definiujemy kostkę

Konstrukcja skoroszytu definicji kostki

Skoroszyt spełnia warunki, konieczne do uruchomienia AFIN.NET.CubeBuilder, gdy:

Jest zapisany. Jego nazwa będzie jednocześnie nazwą pliku .OQY (definicja kostki) oraz pliku .CUB (kostka), czyli np. MojaKostka.xls (albo MojaKostka.xlsx) – MojaKostka.oqy – MojaKostka.cub
Posiada definicję źródła danych kostki: arkusz o nazwie Query, zawierający jedną kwerendę
Posiada definicję wymiaru (może być wiele): Arkusz o nazwie wymiaru - zawiera jedną tabelę przestawną, połączoną z kwerendą (wykonaną na bazie kwerendy w arkuszu Query), definiującą wymiar kostki wyłącznie w polu wierszy tabeli przestawnej – hierarchia pól w polu wierszy tabeli przestawnej definiuje hierarchię danego wymiaru kostki
Posiada definicję miary (może być wiele): Arkusz o nazwie miary - zawiera jedną tabelę przestawną, połączoną z kwerendą (wykonaną na bazie kwerendy w arkuszu Query), definiującą miarę kostki wyłącznie w polu wartości tabeli

Uwagi:

Wszystkie tabele przestawne mogą i powinny być swoimi „klonami”, czyli być wykonane „z jednej tabeli przestawnej”, bazującej na CAŁEJ kwerendzie z arkusza Query. Gdy jest inaczej, każda tabela osobno odwołuje się do tych danych – również nie stanowi to problemu, lecz działa wolniej.

Tworzymy wymiar „Geografia”

Uruchamiamy kreatora tabeli przestawnej. Warto powiązać tabelę z nazwą kwerendy, tak jak widać to na widoku poniżej. W Excelu 2007 nazwa kwerendy pojawia się sama, w Excelu 2003 należy nacisnąć [F3] (lista nazw w skoroszycie) i z listy wybrać nazwę kwerendy.

Ważne też jest, ale to opcja domyślna, żeby utworzyć tabelę przestawną w nowym arkuszu)

Arkusz nazywamy tak, jak chcemy, żeby brzmiała nazwa wymiaru, tu: „Geografia”.

Gdy tabela przestawna jest gotowa, warto dokonać w niej kilku ustawień, aby proces tworzenia wymiaru był czytelniejszy

„Klasyczny układ tabeli przestawnej”, standardowy w Excelu 2003, w Excelu 2007 i późniejszych, musi być ustawiony ręcznie: Prawoklik / Opcje tabeli przestawnej / Karta Widok: Standardowy układ tabeli przestawnej
Brak sum pośrednich: Prawoklik / AFIN.NET … / Ukryj sumy pośrednie

Na widoku poniżej, opcje te są już ustawione, a arkusz odpowiednio nazwany.

Wybieramy pola wymiaru, tu: Region (z tabeli region) oraz Miasto (z tabeli odbiorca) i umieszczamy je kolejno w obszarze pola wierszy tabeli przestawnej. Gdy umieścimy tu więcej, niż jedno pole, wymiar będzie wymiarem hierarchicznym.

Tworzymy kolejne wymiary i miary kostki

Aby sprawnie przejść do definicji kolejnego wymiaru – kopiujemy arkusz Geografia i nazywamy go nazwą kolejnego wymiaru.

Na kolejnym widoku, przedstawiono widok kolejnego wymiaru – „Grupa klientów”. Polecamy jednak stosować powszechną w informatyce zasadę „bez polskich znaków i spacji w nazwach” – nawet, jeśli nie jest to zabronione. Tu nazwano ten wymiar GrupaKlientow

Podobnie postępujemy z definicjami kolejnych wymiarów: Klient oraz Rok i Miesiąc (nazwa: Mc) oraz z definicją miary „Sprzedaż”, tu nazwanej Sprzedaz – pamiętamy jednak, że miarę umieszczamy w polu wartości tabeli przestawnej.

Gdybyśmy zechcieli dodać więcej wymiarów lub więcej miar – postępujemy podobnie.

Uruchamiamy kreatora kostek AFIN.NET.CubeBuilder

Kreator Kostek (AFIN.NET.CubeBuilder) wymaga, aby plik, będący definicją kostki OLAP, był zapisany, czyli posiadał nazwę – zapisujemy go na dysku z nazwą MyCube.xlsx.

Sposób uruchomienia oraz wynik działania

Uruchamiamy Kreatora…

Kreator nie buduje kostki, jako takiej – robi więcej – buduje plik definicji kostki. Czyli nie daje efektu końcowego – umożliwia natomiast stworzenie i odświeżanie kostki przez Excela, nawet bez dodatku AFIN.NET w dowolnym momencie, a nawet na innym komputerze.

Plik .OQY powstaje na podstawie arkusza Query (źródło danych) oraz tabel przestawnych, które dostarczają informacji o wymiarach i miarach kostki.

W trakcie jego działania może pojawić się następujący komunikat.

Po co on się pojawia? Gdy ktoś robi kostki zawodowo (np. konsultant BI), nie może sobie pozwolić na stratę czasu, wynikającą z konieczności każdorazowej odbudowy kostki w całości – może to trwać nawet kilka minut. AFIN.NET.CubeBuilder umożliwia więc umyślne działanie na pewnych, ograniczonych zbiorach danych (czyli kwerendach z klauzulą WHERE), które pozwalają procesować (Informatycy używają takiego sformułowania) kostkę szybciej, bo na mniejszych danych. Ale czas testów zawsze kiedyś się kończy i trzeba kostkę odbudować w całości – wystarczy więc wybrać Tak, czyli usunąć klauzulę WHERE z definicji SQL – nie trzeba zmieniać, za każdym razem, zapytania testowego na produkcyjne.

Proste potwierdzenie spowoduje pozostawienie zapytania w stanie nienaruszonym.

W wyniku działania Kreatora powstaje nowy arkusz o nazwie OQY, ze wszystkimi szczegółami pliku .OQY oraz informacją, gdzie został on zapisany.

W kolejnym kroku – pytanie:

Czy kostka ma być zrobiona i wyświetlona w Excelu od razu, teraz, natychmiast?

Więc jest.

Natychmiast otwiera się nowa instancja Excela (Proszę zwrócić uwagę – bez dodatku AFIN.NET), a w niej nasza, przed chwilą zdefiniowana, kostka OLAP, jako nowa tabela przestawna. Plik z tabelą można zapisać jako plik Excela i używać jak normalny plik Excela, z tą jednak różnicą, że odświeżenie tabeli przestawnej automatycznie (sic!) spowoduje odbudowę kostki i wyświetlenie nowych danych.

Pozostaje mi jedynie wytłumaczyć, dlaczego użyłem, zamiast jednego wymiaru zależnego, dwóch niezależnych wymiarów czasu: Rok i Mc, pomimo, że 1) mogłem, gdyż kreator na to pozwala, 2) byłoby to zachowaniem standardowym – kreator modułów OLAP Excela 2003 domyślnie robił hierarchiczny wymiar czasu, 3) byłoby to efektowniejsze, bo szybsze?

Otóż, gdy wymiar czasu jest hierarchiczny, jest też „sztywny”, czyli miesiąc jest zawsze częścią roku – nigdy np. odwrotnie. A nie zawsze tak chcemy. Wystarczy – gdy są to wymiary niezależne – umieścić rok i miesiąc na dwóch osiach wykresu przestawnego, aby uzyskać taki efekt:

Czas, pomimo pozoru swojej ścisłej hierarchii, w analizie danych powinien być rozbity na wymiary niezależne, bo to umożliwia m.in. analizę sezonowości.

Poprawiamy (do skutku) definicję kostki

AFIN.NET.CubeBuilder umożliwia poprawę kostki!

Efektem działania kreatora jest – owszem – plik .OQY, ale jego rzeczywista definicja jest zapisana w pliku Excela – tu: „MyCube.xlsx”, który możemy otwierać, poprawiać i projektować kostkę, aż spełni nasze oczekiwania. Co więcej – możemy również zmieniać definicję kwerendy, czyli np. dodawać nowe pola obliczane, dołączać do kwerendy nowe tabele, itp. Odświeżenie tabel przestawnych definicji wymiarów spowoduje od razu wyświetlenie nowo dodanych pól, których można też od razu użyć.

Kreator uwzględni je przy kolejnym uruchomieniu.

Automatyzacja i parametryzacja odświeżania kostek

Nie zawsze jednak życzymy sobie, żeby

Kostka zawsze pozostawała w lokalizacji, w której została wykonana
Gdy jesteśmy analitykiem – twórcą raportów lub np. konsultantem zewnętrznym, nie możemy tworzyć kostek, przywiązanych na sztywno do lokalizacji na naszym komputerze. Plik .OQY jednak wymaga jednak, żeby nazwa kostki została w nim zapisana, czyli ją pamięta (ma zapisaną na stałe). Stanowi to pewien problem, gdyż plik .OQY, przesłany analitykowi-użytkownikowi, będzie odbudowywał kostkę nie tam, gdzie analityk sobie tego życzy, tylko w zapisanej lokalizacji, której często (inny komputer) po prostu nie ma.
Zawsze była budowana na podstawie tego samego źródła danych
Zabrzmi to może nieco dziwnie – po co podpinać różne źródła danych pod jedną definicję kostki?
Jest to wręcz konieczne! Definicja kostki to informacja, jak kostka ma być zbudowana, a nie, co ma zawierać. A kostki dla różnych centrów kosztowych muszą być takie same w swej definicji, a różne w swej zawartości – jedna zawiera dane jednego centrum, druga – drugiego i nie może się tu nic pomieszać.
Była każdorazowo odświeżana przez jej użytkownika
Kolejny paradoks – budujemy kostkę dla użytkownika i nie pozwolimy mu jej odświeżać? Gdy kostka jest duża (zawiera dużo danych), jej odświeżanie trwa dłuższy czas (kilka minut maksymalnie), ale jest to wystarczająco długo, by inni nie mogli w tym czasie z niej korzystać. Nie za każdym razem też istnieje konieczność odbudowy kostki – wszak technologia OLAP to technologia odczytu danych, przetworzonych do celów analiz, a nie ciągłego ich przetwarzania. Proces odbudowy kostki powinien być więc administrowany przez upoważnionego analityka i realizowany nie automatycznie (inicjacja przez tabelę przestawną), ale programowo.

Wszystkie powyższe postulaty uwzględnia AFIN.NET.InformationServices – procedura AFIN.NET, umożliwiająca „programowe” wykonywanie operacji bazodanowych – w tym: budowę kostek OLAP na podstawie wskazanego pliku .OQY.

W AFIN.NET otwieramy szablon AFIN.NET.IS_Template_DataWarehouseInMOLAP.xlt.

Wystarczy, że w zaznaczone miejsce wpiszemy właściwą ścieżkę do pliku .OQY (może być dowolna, niekoniecznie związana z miejscem jego utworzenia) oraz żądaną nazwę docelowego pliku .CUB, uruchomienie programu spowoduje automatyczną odbudowę kostki.

Program taki umożliwia również budowanie kostki na różnych zbiorach danych – pełnych, dzielonych według różnych kryteriów, testowych – wszystko zależy, co umieścimy w tabeli, na podstawie której kostka się tworzy.

Możemy użyć jednej definicji kostki do budowy wielu kostek – wystarczy raz wpisać do tabeli-źródła dane pełne, potem dane, ograniczone według jednego kryterium, potem drugiego, itd.

Wszystko można łatwo sparametryzować normalnymi formułami łączenia tekstu w Excelu.

Budowa kostek to przyjemność. Ich używanie w Excelu to, niesamowite wręcz, efekty – przyspieszenie pracy, małe pliki z ogromną ilością danych, bezproblemowy wielodostęp.

Jak używać danych z kostek OLAP do budowy własnych raportów?

Używanie kostek OLAP w Excelu, choć bardzo efektywne, ogranicza się jednak właściwie tylko do jednego sposobu – analizy danych tabelą przestawną. Zalet tabeli przestawnej trudno przecenić, są jednak zadania, których tabela przestawna rozwiązać nie pomaga.

Przykładem może być własny raport w arkuszu Excela, czyli raport, w którym, obok siebie, musza być zestawione dane

Z różnych źródeł danych, czyli np. z różnych kostek OLAP
Różne miary
Z różnych wymiarów
Według różnych kryteriów dodatkowych

W takim przypadku, znowu korzystamy z funkcjonalności AFIN.NET.

Gdy kostkę otworzymy pod Excelem, mamy możliwość korzystania wyłącznie z tabeli przestawnej, ale, gdy otworzymy ją w AFIN.NET, pod prawym przyciskiem myszy pojawia się dodatkowa funkcjonalność pobrania danych z tabeli do własnego raportu jako funkcji.

Wystarczy wskazać miejsce, gdzie chcemy umieścić funkcję – tu wstępnie przygotowany raport w innym, nowym, skoroszycie.

Natychmiast uzyskujemy żądany efekt – funkcja AFIN.NET o nazwie GETDATAOLAP() pobiera dane – uwaga! – bezpośrednio z kostki OLAP.

Funkcja ta, w swoim działaniu, jest bardzo podobna do, standardowej w Excelu, funkcji WEŹDANETABELI() – nie ma jednak jej podstawowej wady-ograniczenia, a mianowicie nie wymaga ona już do swojego działania otwartej tabeli przestawnej.

Tym sposobem nie dość, że stworzyliśmy nowy moduł (kostkę) OLAP, to jeszcze znacznie możemy poszerzyć zakres jej wykorzystania.

Można tak tworzyć dowolne raporty.

Życzymy przyjemnej i efektywnej pracy.

Załoga AFINA