Wprowadzenie

Informacja stała się jednym z największych atutów każdej organizacji. Jednakże wykorzystanie wszystkich tych cennych danych może być trudne. Odpowiedź na proste pytanie, na przykład: „Wymień dziesięć najlepszych produktów sprzedawanych przez pięć ostatnich lat przez pięciu naszych największych konkurentów”, może wymagać zadania złożonego zapytania pobierającego dane z wielu baz danych, a którego wykonanie może zająć wiele godzin albo nawet dni. Posortowanie danych – mające na celu odwzorowanie regionów geograficznych oraz kwartałów – może zwiększyć wymaganą moc obliczeniową oraz potrzebny czas o rząd wielkości.

Rozwiązaniem tego problemu (istniejącego od ponad dekady) jest OLAP –analityczne przetwarzanie na bieżąco (ang. online analytical processing). Ale aż do początku 1999 roku rozwiązania typu OLAP były bardzo drogie, przeważnie oparte na komputerach typu mainframe i skierowane głównie do największych światowych korporacji. Mniejsze firmy uzyskały dostęp do tych wydajnych narzędzi dopiero dzięki pojawieniu się Microsoft® SQL Serwer™ 7.0 oraz Microsoft Office 2000. Sytuacja uległa dalszej poprawie dzięki Usługom analitycznym Microsoft SQL Serwer 2000 oraz pakietowi Microsoft Office XP.

Niniejszy artykuł ma na celu nauczenie zasad działania oraz funkcji OLAP, dzięki czemu czytelnik będzie mógł rozpocząć wykorzystywanie danych swojej organizacji na nowe, wydajne i zaawansowane sposoby. Docelowo: czytelnik, jako programista Microsoft Visual Basic® dla Aplikacji (VBA), powinien zrozumieć podstawy narzędzi Microsoft OLAP i mógł je efektywnie wykorzystywać w swoich aplikacjach. Usługi Analityczne ze SQL Serwer 2000 zawierają także narzędzia wychodzące poza OLAP, ale niniejszy artykuł skupia się na funkcjach OLAP, które można wykorzystać z poziomu Microsoft Excel 2002.

Wprowadzenie do OLAP

Na najbardziej podstawowym poziomie OLAP jest preprocesorem dla danych, potrafiącym wstępnie przepakować i przeanalizować ogromne magazyny danych, ale także i małe bazy danych do jednorazowych zastosowań. Aby uzyskać odpowiedź na zapytanie o informacje biznesowe (typowe zapytanie, dotyczące relacyjnej bazy danych), przetworzenie danych wymaga wykonania setek lub nawet milionów operacji – zależy to od liczby tabel, rekordów oraz typów koniecznych agregacji. Z tego powodu zrealizowanie niektórych zapytań trwa tak długo, że stają się one całkowicie bezużyteczne. Zanim wygenerowana zostanie żądana odpowiedź, dane stają się nieaktualne, a okazja na zyskowne wykorzystanie odpowiedzi dawno już minęła.

OLAP jest narzędziem dla użytkowników końcowych, co stanowi dla wielu ludzi prawdziwe zaskoczenie. Pomaga użytkownikom zrozumieć i zanalizować ogromne ilości danych. Bez OLAP użytkownik – chcący efektywnie analizować dane – musi rozumieć wewnętrzną strukturę relacyjnych baz danych. Istnieje kilka aplikacji zawierających narzędzia do tworzenia zapytań przez użytkownika, pozwalających na uzyskanie odpowiedzi na pytania dotyczące danych w małych bazach danych, zawierających zaledwie megabajty danych. Niektóre takie narzędzia wręcz ujawniają wewnętrzne nazwy tabel oraz pól. Tak więc użytkownik musi wówczas rozumieć zależności między tabelami oraz polami. A niełatwo wyjaśnić użytkownikowi końcowemu takie pojęcia relacyjne, jak klucze zewnętrzne czy wewnętrzne połączenia. I takie właśnie aplikacje powodują, że komputery i ich oprogramowanie mają opinię trudnych w użyciu.

Po co jednak wstawiać jeszcze jedną warstwę złożoności (nawet jeśli jej celem jest uproszczenie dostępu użytkownika do danych) między użytkownika i nie przetworzone dane? Przede wszystkim taka informacja jest wstępnie przepakowywana i przetwarzana, a więc dostęp do niej jest bardzo szybki. Zamiast tworzyć zapytania korzystające z terabajtów nie przetworzonych danych ułożonych w struktury niezrozumiałe dla użytkownika, mechanizm wstępnego przetwarzania OLAP opracowuje wiele z relacji pomiędzy danymi i w rezultacie streszcza użytkownikowi te dane. Istnieje problem z tak zwanym eksplodowaniem danych w niektórych implementacjach OLAP, ale dzięki odrobinie wstępnego planowania administrator bazy danych może ten problem zminimalizować. Można utrzymać równowagę pomiędzy rozmiarem danych a szybkością dostępu.

Najpopularniejszym sposobem udostępniania użytkownikowi końcowemu danych OLAP są raporty Microsoft PivotTable® (tabela przestawna) w Microsoft Excel 2000 oraz Microsoft Excel 2002. Ogromna różnica między „tradycyjną” tabelą przestawną a tymi opartymi na danych OLAP polega na tym, że tradycyjne tabele, aby pokazać raport, wymagają, by pobrane były wszystkie wewnętrzne, nie przetworzone dane, na których wyliczana jest tabela (a mogą to być potencjalnie ilości danych mogące zablokować sieć), podczas gdy tabele OLAP pobierają tylko te dane, które są w danej tabeli używane.

Czy to oznacza, że administratorzy baz danych oraz menedżerowie IT nie odgrywają już żadnej roli? Oczywiście nie. Pierwszym krokiem w udostępnianiu użytkownikom końcowym danych jest utworzenie kostki (ang. cube) OLAP. Jest to bardzo techniczne zadanie, które powinno być wykonywane w firmie przez osobę mająca w dziedzinie baz danych największy talent. Tworząc kostki OLAP i umożliwiając użytkownikom końcowym ich kreatywne wykorzystanie, menedżerowie IT oraz administratorzy baz danych nadal utrzymują kontrolę nad cennymi danymi, ale uwalniają się od nużącej pracy polegającej na ciągłym odpowiadaniu na błahe pytania użytkowników.

Podstawy OLAP

Kostka OLAP asymiluje miliardy fragmentów danych, gromadzi je, co powoduje, że tracą one tożsamość. Jednakże w wyniku tego procesu kostka OLAP staje się bardziej efektywna − dzięki wykorzystywaniu kolektywnej informacji zawartej w danych.

Istnieją cztery podstawowe elementy OLAP: kostki, wymiary, poziomy (czasami określane mianem hierarchii) oraz miary. Zrozumienie koncepcji elementów OLAP jest bardzo istotne dla administratora bazy danych, ale jest także pomocne dla użytkownika końcowego oraz programisty VBA. W zrozumieniu tych czterech elementów może pomóc przyjrzenie się oknu Analysis Manager, pokazanego na rysunku 1.

(Jeśli na komputerze użytkownika jest już zainstalowany pakiet usług analitycznych Microsoft SQL Serwer 2000 (Analysis Services), w celu otworzenia okna Menedżera Analiz należy kliknąć Start, wskazać pozycję Programs, następnie pozycje Microsoft SQL Server i Analysis Services, a na koniec kliknąć Analysis Manager).

Rysunek 1. Okno Analysis Manager

Kostka

Kostka to cały wszechświat danych, motyw przewodni (lejtmotyw) danych, jak kto woli. Najlepiej skonstruowane kostki zwykle związane są z funkcjonalnymi elementami działalności danej firmy – i tak powstają kostki personalne, kostki magazynowe czy kostki marketingowe. Taka funkcjonalna zależność jest zasadniczo przypadkowa, ale użytkownik najprawdopodobniej będzie chciał wykorzystać logikę tworzenia takich kostek tematycznych. Dane zgromadzone w kostce mogą częściowo się powtarzać – chociażby w celu zwiększenia wydajności zapytań analitycznych. Jednakże nie powinno być to uznane za nadmiarowość, gdyż użytkownicy w każdej części danej organizacji najprawdopodobniej uznają takie informacje za bardziej użyteczne.

Wymiary

Wymiary to kategorie opisowe o różnym poziomie szczegółowości. Przykładowo można skategoryzować dane w kostce marketingowej według geografii, czasu, produktów oraz usług, tzn. obszarów zainteresowań użytkowników kostki.

Wymiary są podzielone na poziomy (hierarchie) − są to fragmenty wymiarów z poziomem szczegółowości rosnącym, w miarę jak użytkownicy zagłębiają się coraz bardziej w dane i z każdym krokiem zbliżają do „surowych” danych podzielonych na kategorie względem wymiarów oraz poziomów. Przykładowo, dla wymiaru „czas” do poziomów mogą należeć lata fiskalne, kwartały, miesiące, tygodnie oraz dni.

Miary

Miary są aktualnymi jakościowymi wartościami danych, gromadzonymi zgodnie ze specyfikacją ustaloną przez twórcę kostki. O ile wymiary i poziomy definiują wiersze oraz kolumny typowej tabeli przestawnej Excel 2002, o tyle miary są danymi wewnątrz samej tabeli, końcowymi informacjami interesującymi użytkownika. Do miar mogą należeć płacone pensje, sprzedane jednostki, dochód oraz wydatki – wszelkie dane interesujące użytkownika, pogrupowane według wymiarów oraz poziomów.

Tworzenie kostki OLAP

Jak można się na podstawie powyższego domyśleć, tworzenie kostki to zarówno zadanie techniczne, jak i forma sztuki. Jeśli się ono powiedzie – użytkownicy będą znajdowali dokładnie te informacje, których potrzebują. W przypadku niepowodzenia zarówno twórca kostki, jak i jej użytkownicy będą sfrustrowani długim czasem oczekiwania; istnieje też potencjalnie możliwość, że użytkownicy będą podejmowali błędne decyzje na podstawie nieprawidłowych danych.

W nauce o OLAP istnieją cztery modele danych, związane głównie ze sposobem przechowywania wynikającej z nich kostki. Oryginalną formą było wielowymiarowe OLAP (MOLAP). Ponieważ taka forma kostki wstępnie oblicza każdą możliwą kombinację danych, czyli proces agregacji, z reguły ma też najlepszą wydajność. A ponieważ tak powstała kostka w danych nie ma już formy relacyjnej, produkty OLAP − w celu zoptymalizowania dostępu − przechowują kostki MOLAP we własnych formatach danych, dzięki czemu formularze OLAP mają najlepszą wydajność, jeśli kostka jest dobrze zaprojektowana. Jednak wówczas powstaje konieczność przechowania danych, które mogą być nawet o rząd wielkości większe niż dane oryginalne. Z tego powodu nie jest to rozwiązanie, które dobrze się skaluje wraz ze wzrostem ilości informacji.

Kiedy relacyjne bazy danych zafascynowały informatyków i stały się standardem dla danych, stworzono relacyjne OLAP (ROLAP). Ta forma kostki przechowuje dane w relacyjnej bazie danych. Wydajność z reguły jest znacznie gorsza niż w MOLAP, ale kostka zajmuje mniej miejsca, co daje administratorom jasny wybór oparty na ich priorytetach oraz potrzebach użytkownika.

Na szczęście istnieje także coś pośredniego pomiędzy MOLAP oraz ROLAP: hybrydowe OLAP (HOLAP). To rozwiązanie łączy najlepsze cechy obu poprzednich, przechowując większość danych kostki w formie relacyjnej – co ułatwia skalowalność – ale przechowując też kluczowe dane w bardziej efektywnej formie. Użytkownik zapewne z zadowoleniem przyjmie fakt, że właśnie w taki sposób Microsoft implementuje swoje usługi OLAP (można także wykorzystywać również inne formy kostki).

Innowacją firmy Microsoft jest DOLAP (desktop OLAP), która prawdopodobnie często przyda się w aplikacjach VBA. DOLAP obsługuje korzystanie z danych w kostce także po odłączeniu od serwera. Można dzielić i kroić całą kostkę na interesujące użytkowników sekcje, pozwalając im pracować na pojedynczym wymiarze, podzbiorze pełnej wielowymiarowej kostki.

Problemy

Jednym z problemów, z którym trzeba będzie sobie poradzić, jest eksplozja danych. Pełne omówienie tej kwestii wykracza poza ramy niniejszego dokumentu, ale w skrócie − dotyczy to projektowania kostki oraz niedoboru danych, co powoduje powstanie kostki znacznie większej od oryginalnego zbioru danych. W którymś momencie powiększający się rozmiar kostki niweluje wszelkie zalety korzystania z OLAP, wymuszając najpierw zakup większego dysku. Wydajność, jak można się spodziewać, zmniejsza się wraz z rosnącym rozmiarem kostki.

Kolejnym problemem jest agresywność wstępnej agregacji wbudowanej w kostkę. Podstawowy proces tworzenia kostki agreguje dane w taki sposób, by obsłużyć wymiary oraz poziomy przydatne dla użytkowników końcowych. Im więcej wstępnych agregacji utworzy się w kostce, tym mniej obliczeń wymaga wykonywanie zapytań użytkownika. Ale z drugiej strony „rozdmuchana” wstępna agregacja powoduje powstanie znacznie większej kostki niż jest potrzebna, oraz wydłuża czas tworzenia i aktualizacji kostki.

Usługi OLAP Microsoft SQL 7.0 oraz 2000 Server

Microsoft Excel 2002 zawiera obiekty PivotTable (tabela przestawna) oraz Microsoft PivotChart® (wykres przestawny) rozbudowane na potrzeby źródeł danych OLAP – jak to pokazano na rysunku 2 (architektura usług Microsoft OLAP). Kreator kostek odłączonych (ang. Offline Cube Wizard) tworzy „plasterek” danych z istniejącej kostki, z którego następnie sporządza kostkę DOLAP, która może być używana po odłączeniu od serwera. Kreatorzy tabel przestawnych oraz Wykresów Przestawnych rozbudowano tak, aby mogły wykorzystywać zewnętrzne źródła danych OLAP.

Rysunek 2. Architektura usług Microsoft OLAP

Środkowa warstwa oznaczona na rysunku 2 kolorem żółtym, która w celu odróżnienia od n‑warstwowych koncepcji programistycznych będzie określana mianem usług warstwy klienta, stanowi interfejs pomiędzy klientem oraz danymi kostki. Są to usługi które programistę VBA będą interesować najbardziej – usługi te dają tworzonym aplikacjom dostęp dodanych OLAP. Usługa tabel przestawnych (PivotTable Service), która jest usługą wspólną dla Excela oraz SQL Server, udostępnia usługi bufora od strony klienta oraz mechanizmu obliczeniowego czyniące pozyskiwanie danych bardziej efektywnym.

OLE DB for OLAP to komponent służący do uzyskiwania dostępu do danych, który stanowi rozszerzenie standardu OLE DB, który jest interfejsem umożliwiającym wszystkim aplikacjom uzyskanie dostępu do danych OLAP. ADO – Microsoft ActiveX® Data Objects – także rozbudowano tak, aby mogły obsługiwać dane OLAP. ADO MD (MD oznacza wielowymiarowy – ang. multidimensional) jest obiektowym interfejsem danych przeznaczonym dla aplikacji znających te części kostki.

W SQL Server także pojawiło się kilka nowych usług. Z reguły leżą one w gestii administratora bazy danych tworzącego kostkę OLAP, ale programista VBA używający usług OLAP także skorzysta na znajomości tych funkcji.

Serwer analityczny (Analysis Server) zawiera rdzeń funkcji obliczeniowych OLAP Services (usług OLAP), zaimplementowanych jako usługa Microsoft Windows NT®. Decision Support Objects (obiekty wspomagania decyzji) zapewniają programistyczny dostęp do funkcji administracyjnych, mniej‑więcej tak, jak obiekty DMO SQL Server. Oba te zbiory funkcji działają głównie w tle, ale program do zarządzania OLAP (OLAP Manager) udostępnia administracyjny interfejs użytkownika do zarządzania usługami OLAP, zaimplementowany jako zatrzask do MMC (Microsoft Management Console).

Tworzenie kostki OLAP

Tworzenie kostki OLAP to złożony proces tworzenia wstępnie wyliczonych wartości określanych mianem agregacji. Istnieją trzy sposoby tworzenia kostek OLAP. Pierwszy to zastosowanie programu Microsoft Query z użyciem źródeł danych ODBC. Po utworzeniu zapytania opartego na OLAP, w menu plików pojawia się opcja Create OLAP Cube (utwórz kostkę OLAP) uruchamiająca kreatora kostki OLAP. Wynik końcowy pozwala na zapisanie pliku z rozszerzeniem .oqy, który można potem wykorzystać w aplikacji Excel 2002.

Drugi sposób utworzenia kostki to użycie opcji Create Local Cube (utwórz lokalną kostkę) w aplikacji Excel 2002, co pozwala na utworzenie płata kostki z kostki znajdującej się na serwerze. Aby rozpocząć proces tworzenia nowej kostki w Excel 2002, należy kliknąć Data (dane), wybrać Import External Data (importowanie zewnętrznych danych) a następnie kliknąć New Database Query (nowe zapytanie bazy danych). Podobnie jak w przypadku użycia Microsoft Query, najpierw trzeba będzie utworzyć tabelę przestawną, a następnie zapisać kostkę typu DOLAP wraz z danymi wynikowymi.

Ostatni sposób na utworzenie kostki OLAP to użycie SQL Server OLAP Services, co umożliwia utworzenie po stronie serwera rozbudowanej kostki z nieprzetworzonych danych w SQL Serwer 2000. Poza tym używając usług DTS (Data Transformation Services, usługi transformacji danych) potencjalnie można utworzyć kostkę z niemalże każdego źródła danych.

Decyzję, którą trzeba podjąć wcześnie w procesie projektowania kostki, jest określenie najlepszego zlokalizowania kostki – na serwerze czy u klienta. Lokalny, oparty na plikach "plik kostki odłączonej" pozwala użytkownikom na kontynuowanie pracy z danymi nawet kiedy są odłączeni od serwera. Oparta na serwerze kostka wymaga połączenia z serwerem, ale za to pozwala na pełne wykorzystanie wszystkich usług OLAP Serwera SQL, co czyni zapytania bardziej wydajnymi.

Funkcje OLAP w Excel-u

Nie będzie to kurs tworzenia tabel przestawnych w Excel-u, ale rzut oka na funkcje, z którymi będzie się pracować w tworzonych aplikacjach VBA. Rysunek 3 pokazuje strukturę tabeli przestawnej w aplikacji Excel z jaką można się spotkać podczas tworzenia tego typu zestawienia. Aby rozpocząć proces generowania nowej tabeli przestawnej w Excel 2002, należy kliknąć Data (Dane) a następnie wybrać PivotTable and PivotChart Report (Raporty – tabela przestawna oraz wykres przestawny). To uruchomi kreatora tabel przestawnych oraz wykresów przestawnych.

Rysunek 3. Struktura projektu tabeli przestawnej aplikacji Excel, do której zostaną dodane pola danych OLAP

Rysunek 4 pokazuje, w jak te elementy odnoszą się do rzeczywistego utworzonego tabeli przestawnej. Po uruchomieniu kreatora trzeba przeciągnąć i upuścić pola danych na różne obszary tabeli. Pole strony (Page Fields) jest opcjonalne i działa jako swoisty filtr pozwalający na wyświetlanie podzbiorów danych (raport tabeli przestawnej odwzorowuje n-wymiarowy obiekt w postaci dwuwymiarowej tabeli).

Rysunek 4. Związek pomiędzy elementy projektu tabeli przestawnej aplikacji

Odświeżanie danych w kostce wykracza poza temat niniejszego artykułu. Należy jednak wiedzieć, jak Excel reaguje na zmianę danych na serwerze, czym jest dynamiczne odświeżanie tabeli przestawnej. Excel nie określa, czy na serwerze wystąpiła rekalkulacja (kostka może być automatycznie przeliczana w celu odzwierciedlenia zmian na serwerze). Zamiast tego Excel tylko stale podczas sesji wysyła zapytania w formie wielowymiarowych wyrażeń (MDX) – czyli wielowymiarowych zapytań OLAP. Jeśli kostka się zmieni i w kostce pojawią się nowe dane, nie pojawia się żaden odpowiednik zgłoszenia zdarzenia. Oznacza to, że nowe elementy pojawią się „automatycznie” po wygenerowaniu nowego MDX. Jeśli dane się zmieniły, tabela przestawna odzwierciedli nowy stan danych.

Ciekawiej jest, kiedy elementy znikają z kostki na serwerze (np. poziomy, wymiary, kostki itd.). Kiedy coś takiego się zdarzy, Excel ponownie rysuje tabelę przestawną np. dobierając nowe elementy.

Nowości w tabelach przestawnych w Microsoft Excel 2002

W Excel-u 2002 funkcja tabel przestawnych została rozszerzona zarówno dla danych OLAP, jak i dla zwykłych (nie-OLAP) danych. Oto niektóre z rozszerzeń tabel przestawnych w Excel-u 2002:

Ulepszenia interfejsu użytkownika tabeli przestawnej

Wiele ze zmian interfejsu użytkownika w funkcji tabeli przestawnej spowodowało większą zgodność pomiędzy tabelą przestawną realizowaną za pomocą komponentu WWW Office XP oraz tabelą przestawną wbudowaną w Excel 2002. Oto lista głównych ulepszeń:

• Nowa „studnia” do wrzucania pól w Excel-u 2002 jest łatwiejsza do obsługi, niż ta doczepiona do spodu paska narzędzi tabeli przestawnej w Excel-u 2000. W przypadku źródeł danych OLAP umożliwia to zagłębienie się w celu obejrzenia poziomów w każdej hierarchii.
• W aplikacji Excel 2002, po upuszczeniu pola w obszar wiersza lub kolumny tabeli przestawnej, elementy tego pola będą widoczne natychmiast – nie trzeba będzie czekać na pole danych. To ułatwia wyobrażenie sobie, jaki wygląd będzie miał tworzony, krok po kroku. Na pasku narzędzi tabeli przestawnej jest przycisk umożliwiający użytkownikom, który sobie tego życzą, wyłączenie tej funkcji. W przypadku źródeł danych zaawansowani użytkownicy mogą chcieć wyłączyć tę funkcję, ponieważ wtedy każde upuszczenie powoduje wysłanie zapytania do serwera OLAP.
• Rozwijane menu widoku drzewa pól zostało ulepszone dzięki dodaniu pola wyboru Show All/Hide All (Pokaż wszystkie/Ukryj wszystkie). To bardzo ułatwia dobieranie wielu filtrów w polach tabeli przestawnej, chociaż może to sprawić, że przestrzeń robocza będzie zawierała zbyt wiele informacji na raz. To nowe pole można przeciągnąć i doczepić do lewej lub prawej krawędzi okna arkusza roboczego – jeśli taka pozycja będzie dla kogoś wygodniejsza.
• Pojawił się nowy sposób tworzenia tabeli przestawnej – nie za pomocą kreatora tabeli przestawnej. Klikając Data (Dane), wybierając Import External Data (Importuj Zewnętrzne Dane) i klikając Import Data (Importuj Dane), użytkownicy mogą tworzyć tabele przestawne w oparciu o nowe pliki źródeł danych pakietu Office bez konieczności użycia Microsoft Query (Zapytania Microsoft) czy też kreatora zapytań. Dla niektórych użytkowników jest to łatwiejsze, niż użycie kreatora Tabel Przestawnych.
• Polecenia tabeli przestawnej zostały skonsolidowane w jedno menu, co miało na celu ułatwienie to użytkownikom odkrycie wszystkich funkcji. W wyniku tej konsolidacji, pojawiły się pewne zmiany w poleceniach dostępnych za pomocą menu kontekstowego tabeli przestawnej oraz przycisków widocznych na pasku narzędzi. Menu kontekstowe zawiera teraz najprzydatniejsze i najczęściej potrzebne dla użytkowników polecenia.

Dynamiczne filtrowanie w tabelach przestawnych opartych na OLAP

Tradycyjnie w tabelach przestawnych używających źródła danych inne niż serwer OLAP, w momencie ukrycia elementu w polu, element ten zostaje całkowicie wyłączony z sum pośrednich dla tego pola oraz z sumy całkowitej tabeli przestawnej. W Excel-u 2000 w przypadku raportów opartych na OLAP tak się nie dzieje. Całkowita suma źródeł OLAP zawsze była całkowitą sumą wszystkich elementów potomnych istniejących w kostce, niezależnie od jakiegokolwiek filtrowania zastosowanego przez użytkownika w stosunku do danego widoku.

W Excel-u 2002 uległo to zmianie, i teraz zachowanie źródeł OLAP jest takie samo, jak innych źródeł danych. Kiedy użytkownik odfiltrowuje jakiś element w widoku, jego wartość zostaje usunięta z sum pośrednich dla tego pola oraz z sumy całkowitej tabeli przestawnej.

Obecne domyślne ustawienia dla sumy całkowitej tabel przestawnych to:

• W przypadku tabeli przestawnej OLAP utworzonego w Excel-u 2000, domyślnym ustawieniem nadal będzie brak wpływu filtrowania na sumy pośrednie oraz sumy całkowite. Jednakże jeśli pożądane było by nowe zachowanie sum, należy nacisnąć nowy przycisk na pasku narzędzi opisany jako Include hidden items in totals (Uwzględnij ukryte elementy w sumach).
• W przypadku wszystkich nowoutworzonych tabel przestawnych aplikacji Excel 2002, domyślne ustawienie w przypadku korzystania ze źródeł OLAP jest takie samo, jak w przypadku korzystanie ze źródeł tradycyjnych: filtrowanie elementów w widoku zmieni sumy pośrednie i sumę główną. Jeśli pożądana jest zmiana zachowania na takie, jak zachowanie Excel-a 2000 wobec źródeł OLAP, można w tym celu przełączyć stosowny przycisk na pasku narzędzi.

Warto zauważyć, że w tabeli przestawnej to, czy sumy obejmują elementy ukryte, jest sygnalizowane. Jeśli elementy ukryte są zawarte w sumach, wszystkie sumy pośrednie i sumy całkowite w tabeli przestawnej są oznaczone gwiazdką. Jest to zachowanie domyślnie. Użytkownik może w dialogu opcji tabeli przestawnej wyłączyć to oznaczenie wyłączając opcję Mark totals with * (Oznacz sumy za pomocą *). Tam, gdzie elementy ukryte nie są uwzględnianie w sumach, sumy nie będą nigdy oznaczone gwiazdką.

Indywidualnie dostosowywane grupowania w tabelach przestawnych opartych na OLAP

Tradycyjnie tabele przestawne które nie korzystały z serwera OLAP pozwalały użytkownikom na wybieranie elementów w tabelach przestawnych i dowolne ich grupowanie. W Excel-u 2000 ta funkcja nie była dostępna dla opartych na raportów bazujących na OLAP. Inaczej jest w Excel-u 2002 - dane można grupować bez względu na to z jakich źródeł korzysta raport. Stosowne polecenie zostało dodane do menu kontekstowego tabeli przestawnej. W celu zgrupowania zbioru elementów należy zaznaczyć wybrane elementy, kliknąć prawym klawiszem myszy, wybrać Group and Show detail (Szczegóły Grupowania i Wyświetlania) a następnie kliknąć Group (Grupuj).

W przypadku źródeł danych OLAP, grupowanie jest możliwe tylko w ramach pojedynczego przodka w wymiarze OLAP. Przykładowo pod 1999 można zgrupować "Kwartał 1" oraz "Kwartał 2". Ale nie można zgrupować "1999 Kwartał 4" z "2000 Kwartał 1", ponieważ spowodowało by to przekroczenie granicy pomiędzy dwoma elementami-przodkami w hierarchii OLAP – 1999 oraz 2000.

Można zaobserwować kilka różnic w zachowaniu:

• Nazwa pola w przypadku zgrupowanego pola nie jest formowana tak samo, jak w przypadku źródeł danych nie związanych z OLAP. Będzie wychodzić z lewej zamiast z prawej.
• Przodkowie, pod którymi nie ma grupowania, są przenoszeni w dół do poziomu grupy, zamiast przenoszenia elementów z pola z prawej do góry.
• Jeśli pod danym przodkiem jest grupowanie, elementy spod tego przodka, które nie zostały zgrupowane, są automatycznie łączone w Inną (Other) grupę. Nazwa tej grupy może zostać zmieniona.

Pola strony wielokrotnego wyboru w tabelach przestawnych opartych na OLAP

Często użytkownicy chcą utworzyć własne zgrupowanie wielu elementów w obszarze strony tabeli przestawnej a następnie wyświetlić tabelę opartą na tym zbiorze elementów. W Excel-u 2000 nie było to możliwe w przypadku źródeł danych OLAP. Było to możliwe w przypadku źródeł danych nie związanych z OLAP, ale interfejs użytkownika służący do tego celu nie był łatwy do używania. W Excel-u 2002 w przypadku źródeł danych opartych na OLAP można obecnie definiować indywidualnie dobrane zgrupowania wielu elementów w polu strony, a ponadto został do tego celu utworzony nowy interfejs użytkownika. Ten nowy interfejs dotyczy wyłącznie źródeł OLAP; w przypadku źródeł nie związanych z OLAP poprzednia metoda, polegająca na ukrywaniu elementów w menu rozwijanym strony a następnie wybieraniu opcji All (Wszystkie) nadal działa tak, jak działa.

Po rozwinięciu menu rozwijanego pola strony, jeśli pole wyboru Select multiple items (Wybierz wiele elementów) – tuż nad przyciskami OK/Anuluj – jest zaznaczone, okaże się, że każdy element w polu strony ma teraz obok siebie pole wyboru. Jeśli pole wyboru jest zaznaczone, można wybrać dowolną grupę elementów. Wszystkie wartości w tabeli przestawnej będą agregacją wybranych wartości.

Ulepszone komórki referencyjne tabel przestawnych w połączeniu z formułami w arkuszu roboczym Excel

Funkcja Excel-a GETPIVOTDATA to najlepszy sposób na odwoływanie się do danych z tabeli przestawnej w komórce w innym miejscu arkusza. Jednakże argumenty dla funkcji GETPIVOTDATA okazały się być zbyt skomplikowane. Obecnie, jeśli podczas tworzenia formuły w arkuszu roboczym kliknie się na komórce raportu-tabeli przestawnej, Excel automatycznie wygeneruje formułę GETPIVOTDATA.

Obsługiwane są pola właściwości elementu OLAP

Bazy danych OLAP obsługują funkcję określaną jako Member Property Fields (Pola właściwości elementu). Pola właściwości elementu są dodatkowymi polami z informacjami o danym elemencie w bazie danych OLAP, ale informacja ta nie jest częścią wymiaru informacji. Przykładowo wymiar może się składać z poziomów: Województwo Klienta, Miasto Klienta oraz Nazwa Klienta a w polu Nazwisko Klienta może być kilka różnych informacji o kliencie, nie będących częścią poziomów wymiaru, takich jak wiek, płeć, zajęcie, dochód i tak dalej. Te dodatkowe związane z klientem informacje są określane mianem Member properties (Właściwości elementu) w bazie danych OLAP, a do uzyskania do nich dostępu można użyć opcji Property Fields (Pola właściwości) z rozwijanego menu tabeli przestawnej na pasku menu tabeli przestawnej.

Znaczne ulepszenia modelu obiektowego tabeli przestawnej

Pojawiło się dużo nowych funkcji programistycznych w modelu obiektu aplikacji Excel, użytecznych podczas tworzenia rozbudowanych aplikacji z tabelami przestawnymi – opartymi na OLAP oraz tymi, które korzystają z innych źródeł danych.

Programowanie OLAP oraz tabel przestawnych za pomocą VBA

Wreszcie dotarliśmy do najciekawszej części: używanie VBA do programowania nowych funkcji OLAP Excel-a 2002 oraz SQL Server. Najważniejszą rzeczą, której trzeba się nauczyć, to nowe obiekty w Office XP obsługujące tabele przestawne OLAP. Najczęściej wykorzystywane są pola które już istnieją w tabeli przestawnej, dodając i usuwając je w celu zmodyfikowania wyniku, widzianego przez użytkownika końcowego. Oto główne kolekcje obiektów udostępniane przez Excel-a:

• PivotFields (Pola Przestawne) — Wszystkie pola dostępne w źródle danych
• ColumnFields (Pola Kolumn)— Zwracają kolekcję pól w obszarze kolumny
• DataFields (Pola Danych) — Zwracają kolekcję pól w obszarze danych
• HiddenFields (Pola Ukryte) — Zwracają kolekcję pól nie wyświetlaną w tabeli przestawnej
• PageFields (Pola Strony) — Zwracają kolekcję pól w obszarze strony
• RowFields (Pola Wierszy)— Zwracają kolekcję pól w obszarze wierszy
• VisibleFields (Pola Widoczne) — Zwracają kolekcję pól wyświetlaną w tabeli przestawnej

Najlepsze z tego wszystkiego jest to, że tabela przestawna wyświetla się na podstawie użytych pól. Należy jednak zwrócić uwagę na jedną sprawę. W oparciu o projekt kostki, konkretne pole z kostki OLAP, czyli miara, może zostać użyte wyłącznie w pewnych obszarach tabeli.

Innym dużym obszarem, do którego trzeba będzie się przyzwyczaić, są rozszerzenia ADO. ADO miało funkcje hierarchiczne od pierwszej wersji, ale obecnie ma cały odłączony model obiektowy służący do obsługi danych OLAP. Model obiektowy ADO MD jest związany z modelem obiektowym ADO, ale jest zupełnie oddzielnym elementem. Obiekty w modelu ADO MD odzwierciedlają obiekty w kostce OLAP – i właśnie dlatego należy się dobrze orientować w koncepcjach OLAP przed rozpoczęciem programowania OLAP w ADO MD.

ADO MD jest oparty na leżącej głębiej specyfikacji OLE DB for OLAP, tak więc konieczne jest posiadanie usługodawca (serwer) OLE DB obsługujący te funkcje. W celu użycia ADO MD w aplikacjach należy ustawić referencję do Microsoft ActiveX Data Objects (Multi-dimensional) 1.0 Library w wersji 1.0 lub dowolnej późniejszej. W tworzonych aplikacjach jej programistyczne ID to ADOMD – należy go używać zamiast ADODB.

Rysunek 5 pokazuje obiektowy model ADO MD. Należy pamiętać, że model ten jest odrębny od zwykłego modelu obiektowego ADO. Kolekcje Pozycje, Elementy oraz Właściwości są częścią kilku obiektów – co sygnalizują numerowane indeksy.

Rysunek 5. Model obiektowy ADO MD

Teraz zaprezentowane zostanie nieco kodu. Istnieje kilka powszednich zadań, które trzeba wykonać programistycznie. Pierwsze to dodawanie obiektu-tabeli przestawnej do roboczego arkusza.

Oto przykładowy kod, który wykonuje tą operację:

...
With ActiveWorkbook.PivotCaches _ 
 .Add(SourceType:=xlExternal)
 .Connection = _
 "OLEDB;Provider=MSOLAP.1;Data Source=adatum;" & _
 "Connect Timeout=60;Initial Catalog=FoodMart;" & _
 "Client Cache Size=25;Auto Synch Period=10000"
 .CommandType = xlCmdCube
 .CommandText = Array("Sales")
 .MaintainConnection = True
 .CreatePivotTable _
 TableDestination:= Range("A3"), _
 TableName:= "PivotTable1"
End With
...

Istnieje wiele kreatorów udostępnionych jako metody, które można użyć we własnych aplikacjach. Jednym z takich kreatorów jest kreator tabeli przestawnej, udostępniony w VBA jako metoda PivotTableWizard. Jak w przypadku większości kreatorów, metoda ta ma sporo parametrów, ale używając nazwanych parametrów można podać tylko te, które różnią się od domyślnych.

...
'Zwraca bieżący zakres dat.

Set wksData = ThisWorkbook.Worksheets _
 (ORDER_DETAILS_EXTENDED)
Set rngData = wksData.UsedRange

'Tworzy raport PivotTable. 

Set pvtTable = wksData _
 .PivotTableWizard( _
 SourceType:=xlDatabase, _
 SourceData:=rngData, _
 TableDestination:=wksPivot.Range("B5"), _
 TableName:=SALES_BY_CATEGORY, _ 
 RowGrand:=True, ColumnGrand:=True)
...

Jak wspomniano powyżej, znaczna część programowania wymaga dodawania oraz zmieniania pól używanych w różnych częściach tabeli. Oto kilka przykładów pokazujących, jak dodawać te pola. Najpierw dodawanie pola strony:

...
With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Customers]")
 .Orientation = xlPageField
 .Position = 1
End With
...

Następnie pokażemy dodawanie pól wierszy. Warto zauważyć, że wystarczy podać tylko względną pozycję pól w tabeli, używając właściwości Position (Pozycja).

...
With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Store]")
 .Orientation = xlRowField
 .Position = 1
End With

With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Store Size in SQFT]")
 .Orientation = xlRowField
 .Position = 2
End With
...

Teraz dodamy do tabeli pola kolumn, używając poniższego kodu:

...
With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Time]")
 .Orientation = xlColumnField
 .Position = 1
End With

With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Yearly Income]")
 .Orientation = xlColumnField
 .Position = 1
End With
...

Na koniec – dodawanie jednego lub więcej pól w obszarze danych tabeli:

...
With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Measures].[Profit]")
 .Orientation = xlDataField
 .Position = 1
End With

With ActiveSheet _
 .PivotTables("PivotTable1") _
 .CubeFields("[Measures].[Store Cost]")
 .Orientation = xlDataField
 .Position = 2
End With
...

Jednym z atrybutów aplikacji Excel jest jej zdolność do tworzenia wykresu przestawnego na podstawie danych z tabeli przestawnej. To daje użytkownikowi możliwość zbadania danych w ciekawych postaciach wizualnych, a co więcej wykres przestawny jest dynamicznie uaktualniany, kiedy dane w tabeli przestawnej ulegają zmianie. Wykres przestawny można dodać do arkusza roboczego za pomocą VBA – jak to pokazano w przykładowym fragmencie kodu poniżej:

...
Charts.Add
ActiveChart.SetSourceData _
 Source:=Sheets("Sheet2").Range("A3")
ActiveChart.Location _
 Where:=xlLocationAsNewSheet
With ActiveChart.PivotLayout _
 .CubeFields("[Customers]")
 .Orientation = xlPageField
 .Position = 1
End With
...

Po tym kodzie można dodawać pola w taki sam sposób, jak dodawało się je do tabeli przestawnej.

Tabela przestawna jako komponent strony WWW

Arkusze kalkulacyjne Excel-a są, ze swojej natury, elementami dynamicznymi, i z pewnością przydałby się taki dynamizm na stronach WWW. I do tego właśnie przydają się lomponenty Stron WWW Microsoft Office (OWC - Microsoft Office Web Components). Komponenty pakietu Office oraz kontrolki ActiveX, które można dołączyć do stron WWW, umożliwiają interaktywność. Istnieją dwa komponenty, które głównie interesują programistów OLAP VBA: PivotTable List (lista tabeli przestawnej) oraz Chart (wykres). Model obiektu listy tabeli przestawnej pokazany jest na Rysunku 6.

Rysunek 6. Model obiektu-kontrolki Lista tabeli przestawnej

Szczegółowa dyskusja o Komponentach Stron WWW pakietu Office XP wykracza poza temat niniejszego artykułu. Więcej na ten temat można znaleźć w artykułach Microsoft Office 2000 Web Components Basics (Podstawy komponentów stron WWW pakietu Microsoft Office 2000) oraz Introducing the Office Web Component (Wprowadzenie do komponentów stron WWW pakietu Office). Ze stron MSDN można ściągnąć także Microsoft Office XP Web Component Toolpack (Zestaw narzędzi dla komponentów stron WWW pakietu Microsoft Office XP). Ten zestaw narzędzi zawiera obszerne przykłady kodu i jest na prawdę bardzo przydatny.

Podsumowanie

OLAP jest narzędziem dla użytkownika końcowego, które można wykorzystywać wraz z Excel-em 2002 na wiele zawansowanych sposobów. Używając kostki OLAP jako źródła danych dla raportów Tabela Przestawna oraz Wykres Przestawny w Excel-u można szybko utworzyć wizualnie atrakcyjne oraz łatwe to zanalizowania i utworzenia raporty Tabela Przestawna oraz Wykres Przestawny. Można także użyć VBA do zaprogramowania Excel-a za pomocą narzędzia Usługi Analityczne Sewera SQL 2000.

Aby dowiedzieć się więcej na temat używania Excel-a 2002 z Usługami Analitycznymi Sewera SQL 2000, warto zacząć od zapoznania się z poniższymi pozycjami:

• About OLAP data sources for PivotTable and PivotChart reports (O źródłach danych OLAP dla raportów i wykresów przestawnych)
• Creating a PivotTable Report from an Excel Range or Another PivotTable Report (Tworzenie raportu przestawnego na podstawie obiektu Range - zakresu na arkuszu - lub innej tabeli przestawnej)
• PivotTable Report Structure (Struktura obiektu PivotTable)
• Query-Building Tools (Narzędzia do budowy zapytań)
• Analysis Manager Concepts and Tutorial (Koncepcje oraz przewodnik po Analysis Manager) dostępne z okna startowego Analysis Manager jest także znakomitym źródłem. Jeśli zainstalowano Microsoft SQL Serwer 2000 oraz usługi analityczne, w celu otworzenia okna Analysis Manager należy kliknąć Start, wybrać Programs, wybrać Microsoft SQL Serwer, wybrać Analysis Services a następnie kliknąć Analysis Manager.

Don Kiely jest autorem oraz współautorem kilku książek o programowaniu, włącznie z Visual Basic Programmer's Guide to the Windows Registry (Przewodnik programisty języka Visual Basic po rejestrze systemu Windows) wydanej przez Mabry Software, oraz autorem w kilku czasopismach branżowych włącznie z InformationWeek, EarthWeb oraz VBPJ. Jest instruktorem (Visual Basic, SQL Server, XML, oraz ASP) w Application Developers Training Company. Podsumowując jego dzień – jest Technologiem Programowym dla Trzeciego Sektora Technologii w Fairbanks, Alaska.

• Wprowadzenie
• Wprowadzenie do OLAP
• Podstawy OLAP
• Usługi OLAP Microsoft SQL 7.0 oraz 2000 Server
• Tworzenie kostki OLAP
• Funkcje OLAP w Excel-u
• Nowości w tabelach przestawnych w Microsoft Excel 2002
• Programowanie OLAP oraz tabel przestawnych za pomocą VBA
• Tabela przestawna jako komponent strony WWW
• Podsumowanie

Autor: Don Kiely