http://www.afin.net

 

Artykuł AFIN.NET:

 AFIN.NET – Moja własna kostka OLAP © AFIN 1995-2010 

Wojciech Gardziński

 

 

Tworzymy własną kostkę OLAP – krok po kroku

 

Summary in English:

OLAP Cube technology in Excel is ready to use for almost ten years, but

  1. nobody (most of corporate information workers) knows that
  2. the standard Excel’s OLAP Cube Wizard is removed from Excel 2007 and Excel 2010.

But despite this we can still make the cubes in AFIN.NET and it’s valuable to use it.

It’s totally free in AFIN.NET in unregistered copy, too

 

 

W AFIN.NET kostki OLAP można tworzyć, poprawiać i odświeżać całkowicie darmowo, również w wersji niezarejestrowanej (darmowej).

Używanie ich w Excelu jest standardem.

 

Spis treści:

 

Wstęp. 1

Po co kostki?. 1

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

Budujemy kostkę. 3

Przygotowujemy dane dla kostki 3

Kwerenda. 3

Tworzymy nowe pola danych dla wymiarów lub miar kostki 4

Definiujemy kostkę. 6

Konstrukcja skoroszytu definicji kostki 6

Tworzymy wymiar „Geografia”. 6

Tworzymy kolejne wymiary i miary kostki 8

Uruchamiamy kreatora kostek AFIN.NET.CubeBuilder 10

Sposób uruchomienia oraz wynik działania. 10

Poprawiamy (do skutku) definicję kostki 13

Automatyzacja i parametryzacja odświeżania kostek. 13

Jak używać danych z kostek OLAP?. 15

 

 

Wstęp

Po co kostki?

Dane transakcyjne, w każdej praktycznie organizacji, mają jedną, wspólną cechę – są duże (albo bardzo duże).

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:

 

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:

 

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:

oraz

 

 

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:

  1. 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.oqyMojaKostka.cub
  2. Posiada definicję źródła danych kostki: arkusz o nazwie Query, zawierający jedną kwerendę
  3. 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
  4. 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

  1. „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
  2. 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

  1. 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.
  2. 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ć.
  3. 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

  1. Z różnych źródeł danych, czyli np. z różnych kostek OLAP
  2. Różne miary
  3. Z różnych wymiarów
  4. 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