Analytical Analytical Processing (OLAP) to technologia używana do organizowania dużych baz danych biznesowych i wspierania analizy biznesowej. Bazy danych OLAP są podzielone na jedną lub więcej kostek, a każda z nich jest zorganizowana przez administratora kostki, dzięki czemu odpowiada metodzie uzyskiwania i analizowania danych w celu ułatwienia tworzenia i używania raportów tabeli przestawnej i raportów wykresu przestawnego, których potrzebujesz.
W tym artykule
Czym jest inteligencja biznesowa?
Analityk biznesowy często chce uzyskać pełny obraz firmy, aby zobaczyć szersze trendy na podstawie zagregowanych danych, a także zobaczyć trendy według dowolnej liczby zmiennych. Business Intelligence to proces wydobywania danych z bazy danych OLAP i analizowania tych danych w celu uzyskania informacji, które mogą być wykorzystane do podejmowania świadomych decyzji biznesowych i wykonywania działań. Na przykład dzięki OLAP i analizie biznesowej możesz odpowiedzieć na następujące pytania dotyczące danych biznesowych.
Jaka jest łączna sprzedaż wszystkich produktów w 2007 r. Ze sprzedażą od 2006 r.?
Jak to się ma do daty i godziny w opłacalnym okresie ostatnich pięciu lat?
Ile pieniędzy wydali klienci w zeszłym roku na 35 i jak zmieniło się to zachowanie z czasem?
Ile produktów zostało sprzedanych w dwóch określonych krajach / regionach w tym miesiącu, w przeciwieństwie do tego samego miesiąca ubiegłego roku?
Dla każdej grupy wiekowej klientów Jaki jest podział rentowności (zarówno procent marży, jak i całości) według kategorii produktu?
Szukaj najlepszych i najtańszych sprzedawców, dystrybutorów, dostawców, klientów, partnerów i klientów.
Co to jest przetwarzanie analityczne online (OLAP)?
Bazy danych Online Analytical Processing (OLAP) upraszczają zapytania analityki biznesowej. OLAP to technologia baz danych zoptymalizowana pod kątem zapytań i raportów, a nie przetwarzania transakcji. Źródłem danych dla OLAP są internetowe bazy danych przetwarzania transakcji (OLTP), które zwykle są przechowywane w hurtowniach danych. Dane OLAP są pobierane z tych danych historycznych i łączone w struktury umożliwiające złożoną analizę. Dane OLAP są również hierarchicznie uporządkowane i przechowywane w kostkach, a nie w tabelach. Jest to wyrafinowana technologia wykorzystująca struktury wielowymiarowe w celu zapewnienia szybkiego dostępu do danych do analizy. W tej organizacji w przypadku raportu tabeli przestawnej lub raportu tabeli przestawnej można łatwo wyświetlać dane podsumowujące wysokiego poziomu, takie jak wyniki sprzedaży dla kraju lub regionu, a także wyświetlać informacje o witrynach, w których sprzedaż jest szczególnie wysoka lub słaba.
Bazy danych OLAP mają na celu przyspieszenie ładowania danych. Ponieważ serwer OLAP, a nie Microsoft Office Excel, oblicza wartości zagregowane, podczas tworzenia lub modyfikowania raportu należy wysłać do Excela mniej danych. To podejście umożliwia pracę z większą ilością danych źródłowych niż w przypadku, gdyby dane były zorganizowane w tradycyjnej bazie danych, w której program Excel pobiera wszystkie poszczególne rekordy i oblicza wartości uogólnione.
Bazy danych OLAP zawierają dwa główne typy danych: miary, które są danymi liczbowymi, ilościami i średnimi, które są wykorzystywane do podejmowania świadomych decyzji biznesowych, oraz wymiary, które są kategoriami stosowanymi do usprawnienia tych miar. Bazy danych OLAP pomagają organizować dane przy użyciu wielu poziomów szczegółowości, przy użyciu tych samych kategorii, które znasz do analizy danych.
W poniższych sekcjach każdy element jest szczegółowo opisany poniżej.
Cubic Struktura danych, która łączy miary według poziomu i hierarchii każdego wymiaru, który chcesz analizować. Kostki łączą kilka wymiarów, takich jak czas, położenie geograficzne i linie produktów, z sumami, takimi jak sprzedaż i zapasy. Kostki nie są „sześcianami” w ścisłym sensie matematycznym, ponieważ niekoniecznie mają te same strony. Jednak reprezentują one metaforę Apt dla złożonej koncepcji.
Pomiary Zestaw wartości w module oparty na kolumnie w tabeli faktów kostki, który zwykle jest wartością liczbową. Miary to centralne wartości na Kubie, które są wstępnie przetwarzane, przetwarzane i analizowane. Najczęstszymi przykładami są sprzedaż, przychody, przychody i wydatki.
Członek Element w hierarchii, który reprezentuje jedno lub więcej wystąpień danych. Element może być unikalny lub nieunikalny. Na przykład 2007 i 2008 reprezentują unikalne elementy na poziomie roku wymiaru czasu, podczas gdy styczeń reprezentuje nieunikalne elementy na poziomie miesiąca, ponieważ wymiar czasu ma więcej niż jeden styczeń, ponieważ zawiera dane z więcej niż jednego roku.
Obliczony element Element wymiaru, którego wartość jest obliczana w czasie wykonywania przy użyciu wyrażenia. Wartości obliczonych elementów można wyprowadzić z wartości innych elementów. Na przykład obliczoną pozycję zysku można ustalić, odejmując wartość przedmiotu, a także koszty od wartości przedmiotu, sprzedaży.
pomiar Zestaw co najmniej jednej uporządkowanej hierarchii poziomów kostki, którą użytkownik rozumie i wykorzystuje jako podstawę analizy danych. Na przykład wymiar geograficzny może obejmować poziomy dla kraju / regionu, stanu / dzielnicy i miasta. Ponadto wymiar czasowy może obejmować hierarchię z poziomami „rok”, „kwartał”, „miesiąc” i „dzień”. W raporcie tabeli przestawnej lub raporcie tabeli przestawnej każda hierarchia staje się zestawem pól, które można rozwinąć i zwinąć w celu wyświetlenia niższych lub wyższych poziomów.
Hierarchia Logiczna struktura drzewa, która organizuje elementy wymiarów, dzięki czemu każdy element ma jednego rodzica i zero lub więcej dzieci. Członek podrzędny jest członkiem wcześniejszej grupy w hierarchii, która jest bezpośrednio powiązana z bieżącym członkiem. Na przykład, w hierarchii czasu zawierającej poziomy kwartału, miesiąca i dnia, styczeń jest dzieckiem Qtr1. Element nadrzędny jest elementem niższego poziomu w hierarchii, który jest bezpośrednio powiązany z bieżącym elementem. Wartość nadrzędna jest zwykle konsolidacją wartości wszystkich elementów potomnych. Na przykład w hierarchii czasu zawierającej poziomy „kwartał”, „miesiąc” i „dzień” Qtr1 jest rodzicem dla stycznia.
Poziom W hierarchii dane można porządkować na niższych i wyższych poziomach szczegółowości, takich jak lata, kwartały, miesiące i poziomy dzienne w hierarchii czasu.
Funkcje Excel OLAP
Pobieranie danych OLAP Możesz połączyć się ze źródłami danych OLAP w taki sam sposób, jak możesz połączyć się z innymi zewnętrznymi źródłami danych. Możesz pracować z bazami danych utworzonymi przy użyciu Microsoft SQL Server OLAP w wersji 7.0, Microsoft SQL Server Analysis Services w wersji 2000 i Microsoft SQL Server Analysis Services w wersji 2005, produktów serwerowych Microsoft OLAP. Excel może także współpracować z produktami OLAP innych firm, które są kompatybilne z OLE-DB dla OLAP.
Dane OLAP mogą być wyświetlane tylko jako raport tabeli przestawnej lub raport tabeli przestawnej lub jako funkcja arkusza przekształcona z raportu tabeli przestawnej, ale nie jako zakres danych zewnętrznych. Możesz zapisywać raporty i tabele przestawne OLAP w szablonach raportów, a także tworzyć pliki Office Data Connection (ODC), aby łączyć się z bazami danych OLAP w celu wysyłania zapytań OLAP. Po otwarciu pliku .odc w programie Excel wyświetlany jest pusty raport tabeli przestawnej, który jest gotowy do umieszczenia.
Tworzenie plików kostek do użytku offline Możesz utworzyć autonomiczny plik kostki (. Cub) z podzbiorem danych z bazy danych serwera OLAP. Pliki kostek offline są używane do pracy z danymi OLAP, jeśli nie masz połączenia z siecią. Korzystając z kostki, możesz pracować z większą liczbą danych w raporcie tabeli przestawnej lub raporcie tabeli przestawnej niż w innym przypadku i przyspieszyć pobieranie danych. Pliki kostek można tworzyć tylko, jeśli korzystasz z dostawcy OLAP, takiego jak Microsoft SQL Analysis Services wersja 2005, który obsługuje tę funkcję.
Działanie serwera Działanie serwera to opcjonalna funkcja, którą administrator kostki OLAP może zdefiniować na serwerze, który używa elementu kostki lub miary jako parametru w żądaniu pobrania informacji na Kubie lub w celu uruchomienia innej aplikacji, takiej jak przeglądarka. Excel obsługuje adresy URL, raporty, zestawy wierszy, drążenie w dół i wdrażanie szczegółowych działań po stronie serwera, ale nie obsługuje własnej natywnej instrukcji i zestawu danych.
KPI Kluczowy wskaźnik wydajności to specjalna miara obliczeniowa zdefiniowana na serwerze, która umożliwia śledzenie „kluczowych wskaźników wydajności”, w tym statusu (bieżąca wartość odpowiada określonej liczbie). i trend (wartości w czasie). Gdy są wyświetlane, serwer może wysyłać odpowiednie ikony, podobne do nowej ikony Excela, tak aby ustawiały się powyżej lub poniżej poziomów statusu (na przykład dla ikony stop), a także przewijać w górę lub w dół (na przykład ikona strzałki kierunku).
Formatowanie na serwerze Administratorzy modułów mogą tworzyć miary i elementy obliczeniowe za pomocą formatowania kolorów, formatowania czcionek i reguł formatowania warunkowego, które można przypisać jako korporacyjną standardową regułę biznesową. Na przykład format serwera dla przychodów może być formatem liczbowym waluty, kolorem komórki jest zielony, jeśli wartość jest większa lub równa 30 000, i czerwonym, jeśli wartość jest mniejsza niż 30 000, a styl czcionki jest pogrubiony, jeśli wartość jest mniejsza niż 30 000, a wartość jest dodatnia - zwykły. większa lub równa 30 000. Więcej informacji można znaleźć.
Język interfejsu biurowego Administrator kostki może zdefiniować tłumaczenia danych i błędów na serwerze dla użytkowników, którzy muszą przeglądać informacje o tabeli przestawnej w innym języku. Ta funkcja jest zdefiniowana jako właściwość połączenia z plikiem, a ustawienia regionalne komputera użytkownika i kraju muszą odpowiadać językowi interfejsu.
Komponenty oprogramowania wymagane do uzyskania dostępu do źródeł danych OLAP
Dostawca OLAP Aby skonfigurować źródła danych OLAP dla programu Excel, potrzebujesz jednego z następujących dostawców OLAP.
Microsoft OLAP Provider Excel zawiera sterownik źródła danych i oprogramowanie klienckie do uzyskiwania dostępu do baz danych utworzonych za pomocą Microsoft SQL Server olap w wersji 7.0, Microsoft SQL Server olap w wersji 2000 (8.0) i Microsoft SQL Server Analysis Services w wersji 2005 (9 , 0).
Zewnętrzni dostawcy OLAP W przypadku innych produktów OLAP należy zainstalować dodatkowe sterowniki i oprogramowanie klienckie. Aby używać programu Excel do pracy z danymi OLAP, produkt innej firmy musi być zgodny ze standardem OLE-DB dla OLAP i musi być zgodny z pakietem Microsoft Office. Aby uzyskać informacje na temat instalowania i używania zewnętrznego dostawcy OLAP, skontaktuj się z administratorem systemu lub dostawcą produktu OLAP.
Serwerowe bazy danych i pliki Kuba Oprogramowanie klienckie Excel OLAP obsługuje połączenia z dwoma typami baz danych OLAP. Jeśli baza danych na serwerze OLAP jest dostępna w sieci, możesz bezpośrednio z niej uzyskać surowe dane. Jeśli masz samodzielny plik kostki zawierający dane OLAP lub plik definicji kostki, możesz połączyć się z tym plikiem i pobrać z niego dane źródłowe.
Źródła danych Źródło danych zapewnia dostęp do wszystkich danych w bazie danych OLAP lub pliku kostki offline. Tworząc źródło danych OLAP, możesz na nim opierać raporty i zwracać dane OLAP do Excela jako raport tabeli przestawnej lub raport wykresu przestawnego, a także funkcję arkusza przekształconą z raportu tabeli przestawnej.
Zapytanie Microsoft Za pomocą zapytania można pobierać dane z zewnętrznej bazy danych, takiej jak Microsoft SQL lub Microsoft Access. Nie trzeba używać zapytania do pobierania danych z tabeli przestawnej OLAP powiązanej z plikiem kostki. Dodatkowe informacje
Różnice w funkcjach danych źródłowych OLAP i innych niż OLAP
Jeśli pracujesz z raportami tabeli przestawnej i wykresami przestawnymi z danych źródłowych OLAP i innych typów danych źródłowych, zobaczysz pewne różnice w funkcjach.
Ekstrakcja danych Serwer OLAP zwraca nowe dane do programu Excel przy każdej zmianie układu raportu. Korzystając z innych typów zewnętrznych źródeł danych, żądasz jednocześnie wszystkich danych źródłowych lub możesz ustawić parametry zapytania tylko wtedy, gdy wyświetlane są różne elementy pól filtru raportu. Ponadto masz kilka dodatkowych opcji aktualizacji raportu.
W raportach opartych na źródłowych danych OLAP parametry pola filtru raportu są niedostępne, zapytanie w tle nie jest dostępne, a parametr optymalizacji pamięci nie jest dostępny.
Uwaga: Opcja optymalizacji pamięci nie jest również dostępna dla źródeł danych OLEDB i raportów tabeli przestawnej opartych na szeregu komórek.
Typy pól Dane źródłowe OLAP. pól wymiarowych można używać tylko jako wierszy (wierszy), kolumn (kategorii) lub pól strony. Pola miary mogą być używane tylko jako pola wartości. W przypadku innych typów danych źródłowych wszystkie pola mogą być używane w dowolnej części raportu.
Dostęp do szczegółów W przypadku danych źródłowych OLAP serwer określa dostępne poziomy szczegółowości i oblicza wartości podsumowań, więc rekordy ze szczegółowymi informacjami, które składają się na wartości podsumowania, mogą nie być dostępne. Serwer może jednak udostępnić pola właściwości, które można wyświetlić. Inne typy danych źródłowych nie mają pól właściwości, ale można wyświetlić podstawowe informacje o wartościach pól danych i elementów, a także elementy bez danych.
Pola filtru raportu OLAP mogą nie mieć wszystko elementy raczej zespół Pokaż strony filtrów raportów niedostępne.
Początkowa kolejność sortowania W przypadku danych źródłowych OLAP elementy są najpierw wyświetlane w kolejności, w jakiej są zwracane przez serwer OLAP. Możesz sortować lub ręcznie zmieniać kolejność elementów. W przypadku innych typów danych źródłowych elementy nowego raportu są najpierw sortowane według nazwy elementu w porządku rosnącym.
Nimi Serwery OLAP dostarczają zagregowane wartości bezpośrednio dla raportu, więc nie można zmienić funkcji podsumowania dla pól wartości. W przypadku innych typów danych wejściowych można zmienić funkcję agregującą dla pola wartości i użyć kilku funkcji podsumowania dla tego samego pola wartości. Nie można tworzyć pól obliczeniowych i elementów obliczeniowych w raportach z danymi źródłowymi OLAP.
Suma częściowa W raportach z danymi źródłowymi OLAP nie można zmienić funkcji sumy dla sum częściowych. W przypadku innych typów danych źródłowych możesz zmienić sumy dla sum częściowych i pokazać lub ukryć sumy częściowe dla wszystkich pól wierszy i kolumn.
W przypadku danych źródłowych OLAP można uwzględnić lub wykluczyć ukryte elementy podczas obliczania sum częściowych i sum całkowitych. W przypadku innych typów danych źródłowych możesz dołączyć ukryte elementy pól filtru raportu do sum częściowych, ale ukryte elementy w innych polach będą domyślnie wykluczone.
Praca z kostką OLAP w MS Excel
1. Uzyskanie pozwolenia na dostęp do kostki OLAP SQL Server Analysis Services (SSAS)
2. MS Excel 2016/2013/2010 musi być zainstalowany na twoim komputerze (MS Excel 2007 jest również możliwy, ale nie jest wygodny w nim pracować, a funkcjonalność MS Excel 2003 jest bardzo słaba)
3. Otwórz MS Excel, uruchom kreatora, aby skonfigurować połączenie z usługą analityczną:
3.1 Podaj nazwę lub adres IP bieżącego serwera OLAP (czasem trzeba podać numer otwartego portu, na przykład 192.25.25.102:80); używane jest uwierzytelnianie domeny:
3.2 Wybieramy wielowymiarową bazę danych i kostkę analityczną (jeśli istnieją prawa dostępu do kostki):
3.3 Ustawienia połączenia z usługą analityczną zostaną zapisane w pliku odc na twoim komputerze:
3.4 Wybierz typ raportu (tabela przestawna / wykres) i wskaż lokalizację jego umieszczenia:
Jeśli połączenie zostało już utworzone w skoroszycie programu Excel, możesz użyć go ponownie: menu główne „Dane” -\u003e „Istniejące połączenia” -\u003e wybierz połączenie w tym skoroszycie -\u003e wstaw tabelę przestawną do określonej komórki.
4. Po pomyślnym połączeniu z kostką możesz rozpocząć interaktywną analizę danych:
Rozpoczynając interaktywną analizę danych, należy ustalić, które z pól będą uczestniczyć w tworzeniu wierszy, kolumn i filtrów (stron) tabeli przestawnej. W ogólnym przypadku tabela przestawna jest trójwymiarowa i możemy założyć, że trzeci wymiar jest prostopadły do \u200b\u200bekranu, i obserwujemy sekcje równoległe do płaszczyzny ekranu i określamy, która „strona” zostanie wybrana do wyświetlenia. Filtrowanie można wykonać, przeciągając i upuszczając odpowiednie atrybuty pomiaru w obszarze filtru raportu. Filtrowanie ogranicza przestrzeń kostki, zmniejszając w ten sposób obciążenie serwera OLAP najlepiej najpierw zainstalować niezbędne filtry. Następnie należy umieścić atrybuty wymiaru w obszarach wiersza, kolumny i miary w obszarze danych tabeli przestawnej.
Za każdym razem, gdy zmienia się tabela przestawna, instrukcja MDX jest automatycznie wysyłana do serwera OLAP, po czym zwracane są dane. Im większa i bardziej złożona ilość przetwarzanych danych, obliczone wskaźniki, tym dłuższy czas wykonania zapytania. Możesz anulować żądanie, naciskając klawisz Uciec. Ostatnie wykonane operacje można anulować (Ctrl + Z) lub zwrócić (Ctrl + Y).
Z reguły w przypadku najczęściej używanych kombinacji atrybutów wymiaru kostka przechowuje wstępnie obliczone dane zagregowane, więc czas odpowiedzi na takie zapytanie wynosi kilka sekund. Nie można jednak obliczyć wszystkich możliwych kombinacji agregacji, ponieważ może to wymagać dużo czasu i miejsca do przechowywania. Znaczne zasoby obliczeniowe serwera mogą być wymagane do wykonywania ogromnych żądań danych na poziomie szczegółowości, więc ich czas wykonania może być długi. Po odczytaniu danych z napędów dyskowych serwer umieszcza je w pamięci podręcznej pamięci RAM, co umożliwia natychmiastowe wykonanie kolejnych takich żądań, ponieważ dane zostaną wyodrębnione z pamięci podręcznej.
Jeśli uważasz, że Twoje żądanie będzie często używane, a jego czas realizacji jest niezadowalający, możesz skontaktować się z usługą analitycznej pomocy technicznej w celu zoptymalizowania wykonania żądania.
Po umieszczeniu hierarchii w obszarze wiersza / kolumny można ukryć poszczególne poziomy:
Kluczowe atrybuty (rzadziej - dla atrybutów znajdujących się wyżej w hierarchii) wymiarów mogą istnieć właściwości - cechy opisowe, które mogą być wyświetlane zarówno w etykietkach narzędzi, jak iw postaci pól:
Jeśli chcesz wyświetlić kilka właściwości pola jednocześnie, możesz użyć odpowiedniej listy okien dialogowych:
Zestawy zdefiniowane przez użytkownika
Excel 2010 wprowadził możliwość interaktywnego tworzenia własnych (zdefiniowanych przez użytkownika) zestawów elementów wymiarowych:
W przeciwieństwie do zestawów utworzonych i przechowywanych centralnie po stronie kostki, zestawy niestandardowe są zapisywane lokalnie w skoroszycie programu Excel i mogą być używane w przyszłości:
Zaawansowani użytkownicy mogą tworzyć zestawy przy użyciu konstrukcji MDX:
Ustawianie właściwości tabeli przestawnej
Za pomocą elementu „Parametry tabeli przestawnej ...” w menu kontekstowym (kliknięcie prawym przyciskiem myszy w tabeli przestawnej) można skonfigurować tabelę przestawną, na przykład:
- zakładka „Wyjście”, opcja „Klasyczny układ tabeli przestawnej” - tabela przestawna staje się interaktywna, możesz przeciągać i upuszczać pola (Drag & Drop);
- zakładka „Dane wyjściowe”, opcja „Pokaż elementy bez danych w wierszach” - w tabeli przestawnej zostaną wyświetlone puste wiersze, które nie zawierają żadnych wartości wskaźników dla odpowiednich elementów wymiarowych;
- zakładka „Układ i format”, opcja „Zachowaj formatowanie komórek podczas aktualizacji” - w tabeli przestawnej możesz zastąpić i zapisać format komórek podczas aktualizacji danych;
Utwórz wykresy przestawne
W przypadku istniejącej tabeli przestawnej OLAP możesz utworzyć wykres przestawny - wykres kołowy, słupkowy, słupkowy, wykresowy, rozproszony i inne typy diagramów:
W takim przypadku wykres przestawny zostanie zsynchronizowany z tabelą przestawną - po zmianie składu wskaźników, filtrów, pomiarów w tabeli przestawnej tabela przestawna jest również aktualizowana.
Tworzenie pulpitów nawigacyjnych
Wybierz oryginalną tabelę przestawną, skopiuj ją do schowka (Ctrl + C) i wklej jej kopię (Ctrl + V), w której zmieniamy skład wskaźników:
W celu równoczesnego zarządzania kilkoma tabelami przestawnymi wstawimy wycinek (nowa funkcjonalność, dostępna od MS Excel 2010). Podłączymy naszą krajalnicę do tabel przestawnych - kliknij prawym przyciskiem myszy w plasterku, wybierz „Połączenia z tabelą przestawną ...” w menu kontekstowym. Należy zauważyć, że może istnieć kilka paneli przekroju, które mogą jednocześnie obsługiwać tabele przestawne na różnych arkuszach, co pozwala tworzyć skoordynowane panele informacyjne (pulpit nawigacyjny).
Panele krajalnicy można dostosować: należy wybrać panel, a następnie wyświetlić elementy „Rozmiar i właściwości ...”, „Ustawienia krajalnicy”, „Przypisz makra” w menu kontekstowym aktywowanym przez kliknięcie prawym przyciskiem myszy lub element „Parametry” w menu głównym. Możliwe jest więc ustawienie liczby kolumn dla elementów plasterka (przycisków), rozmiarów przycisków plasterka i panelu, zdefiniowanie schematu kolorów i stylu cięcia z istniejącego zestawu (lub stworzenie własnego stylu), zdefiniowanie własnego tytułu panelu, przypisanie makra programu, poprzez które można rozwinąć funkcjonalność panelu.
Wykonywanie zapytania MDX z Excela
- Przede wszystkim musisz wykonać operację DRILLTHROUGH na jakimś wskaźniku, tj. Zejdź do szczegółowych danych (szczegółowe dane są wyświetlane na osobnym arkuszu) i otwórz listę połączeń;
- Otwórz właściwości połączenia, przejdź do zakładki „Definicja”;
- Wybierz domyślny typ polecenia i umieść przygotowane Mdx wniosek;
- Po naciśnięciu przycisku po sprawdzeniu poprawnej składni żądania i dostępności odpowiednich praw dostępu, żądanie zostanie wykonane na serwerze, a wynik zostanie przedstawiony w bieżącym arkuszu jako zwykły płaski stół.
Możesz wyświetlić tekst zapytania MDX wygenerowanego przez program Excel, instalując bezpłatny dodatek, który zapewnia również inne dodatkowe funkcje.
Tłumaczenie na inne języki
Kostka analityczna obsługuje lokalizację na język rosyjski i angielski (w razie potrzeby możliwa jest lokalizacja na inne języki). Tłumaczenia dotyczą nazw wymiarów, hierarchii, atrybutów, folderów, miar, a także elementów poszczególnych hierarchii, jeśli istnieją tłumaczenia dla nich po stronie systemów księgowych / hurtowni danych. Aby zmienić język, musisz otworzyć właściwości połączenia i dodać następującą opcję w ciągu połączenia:
Rozszerzone właściwości \u003d „Ustawienia regionalne \u003d 1033”
gdzie 1033 - lokalizacja w języku angielskim
1049 - lokalizacja na język rosyjski
Dodatkowe rozszerzenia Excel dla Microsoft OLAP
Możliwości pracy z kostkami Microsoft OLAP wzrosną, jeśli użyjesz dodatkowych rozszerzeń, na przykład Rozszerzeń tabeli przestawnej OLAP, dzięki czemu możesz skorzystać z szybkiego wyszukiwania według pomiaru:
W standardowej tabeli przestawnej dane źródłowe są przechowywane na lokalnym dysku twardym. Dlatego zawsze możesz nimi zarządzać i reorganizować je, nawet bez dostępu do sieci. Ale w żaden sposób nie dotyczy to tabel przestawnych OLAP. W tabelach przestawnych OLAP pamięć podręczna nigdy nie jest przechowywana na lokalnym dysku twardym. Dlatego natychmiast po odłączeniu od sieci lokalnej tabela przestawna straci działanie. Nie możesz w nim przenosić żadnych pól.
Jeśli nadal musisz analizować dane OLAP po odłączeniu od sieci, utwórz autonomiczną kostkę danych. Kostka danych offline to osobny plik, który jest pamięcią podręczną tabeli przestawnej i przechowuje dane OLAP przeglądane po odłączeniu od sieci lokalnej. Dane OLAP skopiowane do tabeli przestawnej można wydrukować, strona internetowa http://everest.ua jest szczegółowo opisana na ten temat.
Aby utworzyć autonomiczną kostkę danych, najpierw utwórz tabelę przestawną OLAP. Umieść kursor w tabeli przestawnej i kliknij przycisk Narzędzia OLAP na karcie kontekstowej Narzędzia, która jest częścią grupy kart kontekstowych Narzędzia tabeli przestawnej. Wybierz polecenie Offline OLAP (Offline OLAP) (rys. 9.8).
Ryc. 9,8 Utwórz niezależną kostkę danych
Zostanie wyświetlone okno dialogowe ustawień autonomicznej kostki danych OLAP. Kliknij na przycisk Utwórz plik danych offline. Uruchomiłeś Kreatora tworzenia pliku kostki danych. Kliknij przycisk Dalej, aby kontynuować procedurę.
Najpierw musisz określić wymiary i poziomy, które będą zawarte w kostce danych. W oknie dialogowym musisz wybrać dane, które zostaną zaimportowane z bazy danych OLAP. Chodzi o to, aby określić tylko te wymiary, które będą potrzebne po odłączeniu komputera od sieci lokalnej. Im więcej wymiarów określisz, tym większa będzie autonomiczna kostka danych.
Kliknij przycisk Dalej, aby przejść do następnego okna dialogowego kreatora. W nim masz możliwość określenia członków lub elementów danych, które nie zostaną uwzględnione w kostce. W szczególności nie będziesz potrzebować miary Zwiększona sprzedaż internetowa, więc pole wyboru dla niej nie będzie zaznaczone na liście. Niezaznaczone pole wskazuje, że określony element nie zostanie zaimportowany i zajmie dodatkowe miejsce na lokalnym dysku twardym.
W ostatnim kroku określ lokalizację i nazwę kostki danych. W naszym przypadku plik kostki będzie miał nazwę MyOfflineCube.cub i będzie znajdować się w folderze roboczym.
Pliki kostek danych mają rozszerzenie .cub
Po pewnym czasie program Excel zapisze kostkę danych offline w określonym folderze. Aby go przetestować, kliknij dwukrotnie plik, który automatycznie wygeneruje skoroszyt programu Excel zawierający tabelę przestawną powiązaną z wybraną kostką danych. Po utworzeniu możesz rozpowszechniać autonomiczną kostkę danych wśród wszystkich zainteresowanych użytkowników, którzy pracują w trybie odłączonej sieci LAN.
Po połączeniu z siecią lokalną możesz otworzyć plik kostki danych offline i zaktualizować go, a także odpowiednią tabelę danych. Główna zasada mówi, że autonomiczna kostka danych jest używana tylko do działania, gdy sieć lokalna jest odłączona, ale aktualizacja jest obowiązkowa po przywróceniu połączenia. Próba aktualizacji autonomicznej kostki danych po rozłączeniu spowoduje błąd.
Praca z plikami kostek offline
plik kostki offline (. Cub) przechowuje dane w postaci kostki Online Analytical Processing (OLAP). Te dane mogą stanowić część bazy danych OLAP na serwerze OLAP lub mogą być tworzone niezależnie od bazy danych OLAP. Użyj pliku kostki offline, aby kontynuować pracę z tabelami przestawnymi i raportami tabeli przestawnej, jeśli serwer jest niedostępny lub gdy jesteś offline.
Uwaga bezpieczeństwa: Zachowaj ostrożność podczas używania lub dystrybucji pliku kostki offline zawierającego poufne lub osobiste dane. Zamiast pliku kostki zaleca się zapisanie danych w książce, aby można było kontrolować dostęp do danych za pomocą funkcji zarządzania prawami. Aby uzyskać więcej informacji, zobacz Zarządzanie prawami do informacji biurowych.
Podczas pracy z raportem tabeli przestawnej lub wykresu przestawnego opartym na danych źródłowych serwera OLAP można użyć Kreatora autonomicznej kostki, aby skopiować dane źródłowe do osobnego pliku kostki autonomicznej na komputerze. Aby utworzyć te pliki offline, dostawca danych OLAP musi obsługiwać tę funkcję, na przykład MSOLAP z Microsoft SQL Server Analysis Services zainstalowanych na komputerze.
Uwaga: Tworzenie i używanie autonomicznych plików kostek z Microsoft SQL Server Analysis Services jest regulowane przez termin i licencję instalacji Microsoft SQL Server. Przejrzyj odpowiednie informacje licencyjne dla wersji SQL Server.
Pracuj z Autonomous Cube Wizard
Aby utworzyć autonomiczny plik kostki, możesz wybrać podzbiór danych w bazie danych OLAP za pomocą kreatora autonomicznych kostek, a następnie zapisać podzestaw. Raport nie musi zawierać wszystkich pól zawartych w pliku, a także wybierać z nich dowolne z nich i pola danych dostępne w bazie danych OLAP. Aby zapisać plik jako minimum, możesz dołączyć tylko dane, które powinny być wyświetlane w raporcie. Możesz pominąć wszystkie wymiary, a dla większości typów wymiarów możesz również wykluczyć informacje niższego poziomu i elementy najwyższego poziomu, które nie muszą być wyświetlane. Dla wszystkich uwzględnionych elementów pola właściwości dostępne w bazie danych dla tych elementów są również przechowywane w osobnym pliku.
Przesyłanie danych offline i połączenie zwrotne
Aby to zrobić, musisz najpierw utworzyć tabelę przestawną lub raport w formie tabeli przestawnej na podstawie bazy danych serwera, a następnie utworzyć z raportu niezależny plik kostki. Następnie możesz w dowolnym momencie przełączać raport między bazą danych serwera a plikiem offline. Na przykład jeśli używasz laptopa do podróży domowych i wideo, ponownie podłącz komputer do sieci.
Poniżej przedstawiono podstawowe kroki, które należy podjąć, aby pracować z danymi w trybie offline, a następnie przenieść dane z powrotem do Internetu.
Utwórz lub otwórz tabelę przestawną lub raport wykresu przestawnego na podstawie danych OLAP, do których chcesz uzyskać dostęp offline.
Utwórz plik kostki offline na komputerze. W dziale Utwórz autonomiczny plik kostki z bazy danych serwera OLAP (w dalszej części tego artykułu).
Odłącz się od sieci i pracuj z plikiem kostki offline.
Połącz się z siecią i ponownie podłącz plik kostki offline. Sprawdź sekcję Ponowne połączenie pliku modułu offline z bazą danych serwera OLAP (w dalszej części tego artykułu).
Aktualizowanie pliku kostki offline o nowe dane i ponowne utworzenie pliku kostki offline. Sprawdź sekcję zaktualizuj i ponownie utwórz plik kostki offline (w dalszej części tego artykułu).
BLOG
Tylko wysokiej jakości posty
Co to są tabele przestawne programu Excel i kostki OLAP
Obejrzyj wideo do artykułu:
OLAP - to jest angielski. przetwarzanie analityczne online, technologia przetwarzania danych analitycznych w czasie rzeczywistym. W prostym języku - repozytorium z danymi wielowymiarowymi (moduł), jeszcze prostszym - tylko baza danych, z której można pobierać dane w programie Excel i analizować je za pomocą narzędzia Excel - tabele przestawne.
Tabele podsumowujące - Jest to interfejs użytkownika do wyświetlania danych wielowymiarowych. Innymi słowy, specjalny rodzaj tabel, za pomocą których można wykonać prawie każdy raport.
Aby to wyjaśnić, porównajmy „tabelę normalną” z „tabelą przestawną”
Zwykły stół:
Tabela podsumowująca:
Główna różnica Tabele przestawne - to jest obecność okna ” Lista pól tabeli przestawnej", Z którego możesz wybrać niezbędne pola i automatycznie uzyskać dowolną tabelę!
Jak korzystać
Otwórz plik Excel podłączony do kostki OLAP, na przykład „BIWEB”:
Co to oznacza i jak z niego korzystać?
Przeciągnij wymagane pola, aby na przykład uzyskać tabelę podobną do tej:
« Plusy»Umożliwiają drążenie raportu. W tym przykładzie „Marka” jest szczegółowo opisana jako „Skrócone nazwy”, a „Kwartał” do „Miesiąc”, tj. jak to:
Funkcje analityczne w programie Excel (funkcje kostki)
Microsoft stale dodaje nowe funkcje do Excela dotyczące analizy i wizualizacji danych. Praca z informacjami w programie Excel może być reprezentowana w postaci względnie niezależnych trzech warstw:
- „Właściwie” zorganizowane dane źródłowe
- matematyka (logika) przetwarzania danych
- prezentacja danych
Ryc. 1. Analiza danych w programie Excel: a) dane źródłowe, b) pomiar w Power Pivot, c) pulpit nawigacyjny; aby powiększyć obraz, kliknij go prawym przyciskiem myszy i wybierz Otwórz zdjęcie w nowej karcie
Pobierz notatkę w formacie Word lub pdf, przykłady w formacie Excel
Funkcje kostki i tabele przestawne
Najprostszym i jednocześnie bardzo skutecznym sposobem prezentacji danych są tabele przestawne. Można je budować na podstawie danych zawartych: a) w arkuszu programu Excel, b) kostce OLAP lub c) modelu danych Power Pivot. W dwóch ostatnich przypadkach oprócz tabeli przestawnej można użyć funkcji analitycznych (funkcji kostki) do wygenerowania raportu w arkuszu programu Excel. Tabele przestawne są łatwiejsze. Funkcje kostek są bardziej skomplikowane, ale zapewniają większą elastyczność, szczególnie w raportowaniu, dlatego są szeroko stosowane w pulpitach nawigacyjnych.
Dalsza dyskusja dotyczy wzorów kostek i tabel przestawnych opartych na modelu Power Pivot, aw niektórych przypadkach opartych na kostkach OLAP.
Łatwy sposób na uzyskanie funkcji kostki
Kiedy (jeśli) zacząłeś uczyć się kodu VBA, wtedy dowiedziałeś się, że najłatwiej jest uzyskać kod za pomocą rekordu makra. Ponadto kod można edytować, dodawać pętle, kontrole itp. Podobnie najłatwiej jest uzyskać zestaw funkcji kostki, przekształcając tabelę przestawną (ryc. 2). Stań na dowolnej komórce tabeli przestawnej, przejdź do zakładki Analizakliknij przycisk Znaczy OLAPi kliknij Konwertuj na formuły.
Ryc. 2. Przekształć tabelę przestawną w zestaw funkcji kostki
Liczby zostaną zapisane i nie będą to wartości, ale formuły, które wyodrębnią dane z modelu danych Power Pivot (ryc. 3). Możesz sformatować wynikową tabelę. W tym możesz usuwać i wstawiać wiersze i kolumny do tabeli. Plasterek pozostaje i wpływa na dane w tabeli. Podczas aktualizacji danych źródłowych numery w tabeli również zostaną zaktualizowane.
Ryc. 3. Tabela oparta na formułach sześciennych
Funkcja CURVAL ()
Jest to być może główna funkcja kostek. Jest odpowiednikiem obszaru Wartości stół przestawny. KUZVALUETS pobiera dane z modelu kostki lub modelu Power Pivot i odzwierciedla je poza tabelą przestawną. Oznacza to, że nie jesteś ograniczony limitami tabeli przestawnej i możesz tworzyć raporty z niezliczonymi możliwościami.
Pisanie formuły od zera
Nie musisz konwertować gotowej tabeli przestawnej. Możesz napisać dowolną formułę kostki od zera. Na przykład następująca formuła jest wprowadzana do komórki C10 (ryc. 4):
Ryc. 4. Funkcja wartości CUB () w komórce C10 zwraca sprzedaż rowerów za wszystkie lata, jak w tabeli podsumowującej
Mała sztuczka. Aby ułatwić czytanie wzorów kostek, pożądane jest umieszczenie tylko jednego argumentu w każdym wierszu. Możesz zmniejszyć okno Excela. Aby to zrobić, kliknij ikonę Minimalizuj do oknaznajduje się w prawym górnym rogu ekranu. A następnie dostosuj rozmiar okna w poziomie. Alternatywą jest wymuszenie tekstu formuły w nowym wierszu. Aby to zrobić, umieść kursor na pasku formuły w miejscu, w którym chcesz wykonać transfer, i naciśnij klawisze Alt + Enter.
Ryc. 5. Zminimalizuj okno
Składnia funkcji CURRENT VALUE ()
Pomoc programu Excel jest absolutnie dokładna i całkowicie bezużyteczna dla początkujących:
KUZVALUE (połączenie; [wyrażenie_elementu1]; [wyrażenie_elementu2]; ...)
Połączenie - wymagany argument; Ciąg tekstowy reprezentujący nazwę połączenia z kostką.
Wyrażenie elementu - opcjonalny argument; Ciąg tekstowy reprezentujący wielowymiarowe wyrażenie, które zwraca element lub krotkę w kostce. Ponadto „wyrażenie_elementu” może być zbiorem zdefiniowanym za pomocą funkcji CUBESET. Użyj element_expression jako wycinka, aby określić część kostki, dla której chcesz zwrócić zagregowaną wartość. Jeśli nie zostanie podana żadna miara w argumencie element_expression, zostanie użyta domyślna miara dla tej kostki.
Zanim przejdziemy do wyjaśnienia składni funkcji CURRENT, kilka słów o kostkach, modelach danych i zagadkach motorcade.
Niektóre informacje na temat modułów OLAP i modeli danych Power Pivot
Kostki danych OLAP ( On line Analytical P.przetwarzanie (analiza danych operacyjnych) zostało opracowane specjalnie do przetwarzania analitycznego i szybkiego wydobywania z nich danych. Wyobraź sobie trójwymiarową przestrzeń, w której okresy, miasta i towary są kreślone wzdłuż osi (ryc. 5a). Węzłami takiej siatki są wartości różnych miar: wielkość sprzedaży, zysk, koszty, liczba sprzedanych jednostek itp. Teraz wyobraź sobie, że istnieją dziesiątki, a nawet setki pomiarów ... i jest też wiele miar. Będzie to wielowymiarowa kostka OLAP. Tworzenie, konfigurowanie i aktualizowanie modułów OLAP jest zadaniem specjalistów IT.
Ryc. 5a. Kostka OLAP 3D
Formuły analityczne Excel (formuły kostki) pobierają nazwy osi (np. Czas), nazwy elementów na tych osiach (sierpień, wrzesień), wartości miar na przecięciu współrzędnych. Ta struktura pozwala tabelom przestawnym i formułom kostek być elastycznym i dostosowywać się do potrzeb użytkowników. Arkusze kalkulacyjne Excel oparte na arkuszach Excel nie używają miar, więc nie są tak elastyczne do analizy danych.
Power Pivot to stosunkowo nowa funkcja firmy Microsoft. Jest to wbudowane Excel i częściowo niezależne środowisko ze znanym interfejsem. Power Pivot znacznie przewyższa standardowe tabele przestawne. Jednocześnie tworzenie kostek w dodatku Power Pivot jest stosunkowo proste, a co najważniejsze, nie wymaga udziału specjalisty IT. Microsoft wdraża hasło: „Business intelligence - masowo!”. Chociaż modele Power Pivot nie są w 100% kostkami, możesz również mówić o nich jako kostkach (więcej informacji można znaleźć w kursie wprowadzającym Moore. Power Pivot i większej objętości Rob Collie. Formuły DAX dla Power Pivot).
Głównymi składnikami kostki są wymiary, hierarchie, poziomy, elementy (lub elementy; elementy w języku angielskim) i miary. Pomiar - główna cecha analizowanych danych. Na przykład kategoria produktu, okres, geografia sprzedaży. Wymiar jest tym, co możemy umieścić na jednej z osi tabeli przestawnej. Oprócz unikalnych wartości każdy wymiar zawiera element, który agreguje wszystkie elementy tego wymiaru.
Pomiary są oparte na hierarchie. Na przykład kategorię produktu można podzielić na podkategorie, następnie na modele, a na koniec na nazwy produktów (ryc. 5b). Hierarchia umożliwia tworzenie danych podsumowujących i analizowanie ich na różnych poziomach struktury. W naszym przykładzie hierarchia Kategoria obejmuje 4 Poziom.
Przedmioty (poszczególni członkowie) są obecni na wszystkich poziomach. Na przykład na poziomie kategorii są cztery elementy: akcesoria, rowery, odzież, komponenty. Inne poziomy mają swoje własne elementy.
Środki Są to wartości obliczone, na przykład sprzedaż. Miary w kostkach są przechowywane we własnym wymiarze, zwanym (patrz rysunek poniżej 9). Miary nie mają hierarchii. Każda miara oblicza i przechowuje wartość dla wszystkich wymiarów i wszystkich elementów i jest cięta w zależności od tego, które elementy wymiaru umieszczamy na osi. Mówią także, które współrzędne ustawiliśmy lub jaki kontekst filtru ustawiliśmy. Na przykład na ryc. 5a, w każdej małej kostce obliczana jest ta sama miara - Zysk. Wartość zwracana przez miarę zależy od współrzędnych. Po prawej na rysunku 5a pokazano, że zysk (w trzech współrzędnych) dla Moskwy w październiku na jabłkach \u003d 63 000 p. Miarę można interpretować jako jeden z wymiarów. Na przykład na ryc. 5a zamiast osi Produktyoś osi Środki z elementami Wielkość sprzedaży, Zysk, Sprzedane jednostki. Wtedy każda komórka będzie miała jakąś wartość, na przykład sprzedaż w Moskwie, wrzesień.
Tuple - kilka elementów o różnych wymiarach, które określają współrzędne wzdłuż osi sześcianu, w którym obliczamy miarę. Na przykład na ryc. 5a Tuple \u003d Moskwa, październik, jabłka. Dopuszczalna krotka - Perm, jabłka. Kolejny to jabłka, sierpień. Wymiary nie zawarte w krotce są w niej zawarte i są reprezentowane przez domyślny element członkowski. Tak więc komórka przestrzeni wielowymiarowej jest zawsze określana przez pełny zestaw współrzędnych, nawet jeśli niektóre z nich zostaną pominięte w krotce. W krotce nie można dołączyć dwóch elementów tego samego wymiaru; składnia na to nie zezwala. Na przykład niepoprawna motocykla Moskwa i Perm, jabłka. Aby zrealizować takie wielowymiarowe wyrażenie, wymagany jest zestaw dwóch krotek: Moskwa i jabłka + Perm i jabłka.
Zestaw elementów - kilka elementów jednego wymiaru. Na przykład jabłka i gruszki. Zestaw krotek - kilka krotek, z których każda składa się z tych samych pomiarów w tej samej sekwencji. Na przykład zestaw dwóch krotek: Moskwa, jabłka i Perm, banany.
Pomoc w ukończeniu
Wróćmy do składni funkcji wartości CUB. Użyjemy autouzupełniania. Zacznij wpisywać formułę w komórce:
Excel zaoferuje wszystkie połączenia dostępne w skoroszycie programu Excel:
Ryc. 6. Połączenie z modelem danych Power Pivot jest zawsze nazywane ThisWorkbookDataModel.
Ryc. 7. Połączenia kostek
Kontynuujemy wprowadzanie formuły (w naszym przypadku dla modelu danych):
Zakończenie zapewni wszystkie dostępne tabele i miary modelu danych:
Ryc. 8. Dostępne elementy pierwszego poziomu - nazwy tabel i zestaw miar (wyróżnione)
Wybierz ikonę Środki. Postaw punkt:
KUZVALUE („ThisWorkbookDataModel”; ”.
Zakończenie zapewni wszystkie dostępne środki:
Ryc. 9. Dostępne elementy drugiego poziomu w zestawie miar
Wybierz miarę. Dodaj cudzysłowy, aby zamknąć nawias, naciśnij klawisz Enter.
KUZVALUE („ThisWorkbookDataModel”; „.”)
Ryc. 10. Formuła wartości CUB w komórce Excel
Podobnie możesz dodać trzeci argument do formuły:
VBA w Excelu Obiekt Excel.PivotTable i praca z tabelami przestawnymi i kostkami OLAP w Excelu
10.8 Praca z tabelami przestawnymi (obiekt tabeli przestawnej)
Obiekt Excel.PivotTable, praca programu z tabelami przestawnymi i kostkami OLAP w programie Excel za pomocą VBA, obiekt PivotCache, tworzenie układu tabeli przestawnej
W trakcie pracy większości przedsiębiorstw gromadzone są tzw. Surowe dane o działalności. Na przykład w przypadku firmy handlowej dane dotyczące sprzedaży towarów mogą być gromadzone - dla każdego zakupu osobno, dla przedsiębiorstw mobilnych - statystyki dotyczące obciążenia stacji bazowych itp. Bardzo często zarząd przedsiębiorstwa potrzebuje informacji analitycznych, które są generowane na podstawie surowych informacji - na przykład, aby obliczyć wkład każdego rodzaju produktu w dochody firmy lub jakość usług na obszarze danej stacji. Bardzo trudno jest wydobyć takie informacje z surowych informacji: musisz wykonywać bardzo złożone zapytania SQL, które zajmują dużo czasu i często zakłócają twoją bieżącą pracę. Dlatego coraz częściej surowe dane są najpierw redukowane do hurtowni danych archiwalnych - hurtowni danych, a następnie do kostek OLAP, które są bardzo wygodne do interaktywnej analizy. Najłatwiejszym sposobem wyobrażenia sobie kostek OLAP są tabele wielowymiarowe, w których zamiast standardowych dwóch wymiarów (kolumn i wierszy, jak w zwykłych tabelach), może być wiele wymiarów. Zazwyczaj termin „przekrój” jest używany do opisania wymiarów w sześcianie. Na przykład dział marketingu może potrzebować informacji na czas, w kontekście regionalnym, w kontekście rodzajów produktów, w kontekście kanałów sprzedaży itp. Używając kostek (w przeciwieństwie do standardowych zapytań SQL), bardzo łatwo jest uzyskać odpowiedzi na pytania takie jak: „ile produktów tego typu zostało sprzedanych w czwartym kwartale ubiegłego roku w regionie północno-zachodnim za pośrednictwem regionalnych dystrybutorów.
Oczywiście w zwykłych bazach danych takich kostek nie można utworzyć. Do pracy z kostkami OLAP wymagane są specjalistyczne oprogramowanie. Microsoft SQL zawiera SQL Server o nazwie Analysis Services. Istnieją rozwiązania OLAP Oracle, IBM, Sybase itp.
Aby pracować z takimi kostkami, w programie Excel jest wbudowany specjalny klient. W języku rosyjskim nazywa się to Tabela podsumowująca (na ekranie graficznym jest dostępny poprzez menu Dane -> Tabela podsumowująca) oraz w języku angielskim - Tabela przestawna. Odpowiednio obiekt reprezentowany przez tego klienta nazywa się Tabela przestawna. Należy zauważyć, że jest on w stanie pracować nie tylko z kostkami OLAP, ale także ze zwykłymi danymi w tabelach lub bazach danych Excel, ale wiele możliwości jest utraconych.
PivotTable i PivotTable to produkty Panorama Software, które zostały nabyte przez Microsoft i zintegrowane z Excelem. Dlatego praca z obiektem tabeli przestawnej różni się nieco od pracy z innymi obiektami programu Excel. Zgadywanie, co należy zrobić, jest często trudne. Dlatego zaleca się aktywne korzystanie z rejestratora makr, aby uzyskać wskazówki. Jednocześnie podczas pracy z tabelami przestawnymi użytkownicy często muszą wykonywać te same powtarzalne operacje, dlatego automatyzacja jest konieczna w wielu sytuacjach.
Jak wygląda program z tabelą przestawną?
Pierwszą rzeczą, którą musimy zrobić, to stworzyć obiekt PivotCache, który będzie reprezentował zestaw rekordów otrzymanych ze źródła OLAP. Bardzo warunkowo ten obiekt PivotCache można porównać do QueryTable. Dla każdej tabeli przestawnej można użyć tylko jednego obiektu PivotCache. Tworzenie obiektu PivotCache odbywa się za pomocą metody Add () kolekcji PivotCache:
Dim PC1 As PivotCache
Ustaw PC1 \u003d ActiveWorkbook.PivotCaches.Add (xlExternal)
PivotCaches to standardowa kolekcja, a spośród metod, które zasługują na szczegółowe rozważenie, można w niej wspomnieć tylko metodę Add (). Ta metoda przyjmuje dwa parametry:
- Rodzaj źródła - wymagany, określa typ źródła danych dla tabeli przestawnej. Możesz określić tworzenie tabeli przestawnej na podstawie zakresu w programie Excel, danych z bazy danych, zewnętrznego źródła danych, innej tabeli przestawnej itp. W praktyce zwykle ma sens używanie OLAP tylko wtedy, gdy jest dużo danych - w związku z tym potrzebujesz specjalistycznej pamięci zewnętrznej (na przykład Microsoft Analysis Services). W tej sytuacji wybierane jest xlExternal.
- Sourcedata - obowiązkowe we wszystkich przypadkach, z wyjątkiem tych, gdy wartość pierwszego parametru to xlExternal. W rzeczywistości określa zakres danych, na podstawie którego zostanie utworzona tabela przestawna. Zwykle przyjmuje obiekt Range.
Następnym zadaniem jest skonfigurowanie parametrów obiektu PivotCache. Jak już wspomniano, obiekt ten jest bardzo podobny do QueryTable, a jego zestaw właściwości i metod jest bardzo podobny. Niektóre z najważniejszych właściwości i metod:
- Adoconnection - możliwość zwrócenia obiektu połączenia ADO, który jest tworzony automatycznie w celu połączenia z zewnętrznym źródłem danych. Służy do dalszej konfiguracji właściwości połączenia.
- Połączenie - działa dokładnie tak samo, jak właściwość o tej samej nazwie obiekt QueryTable. Może akceptować parametry połączenia, gotowy zestaw rekordów, plik tekstowy, żądanie sieciowe. Plik zapytania Microsoft. Najczęściej podczas pracy z OLAP ciąg połączenia jest rejestrowany bezpośrednio (ponieważ nie ma potrzeby uzyskiwania obiektu Recordset, na przykład w celu zmiany danych - źródła danych OLAP są prawie zawsze tylko do odczytu). Na przykład ustawienie tej właściwości na połączenie z bazą danych Foodmart (szkoleniowa baza danych usług Analysis Services) na serwerze LONDON może wyglądać następująco:
PC1.Connection \u003d "OLEDB; Dostawca \u003d MSOLAP.2; Źródło danych \u003d LONDON1; Początkowy katalog \u003d FoodMart 2000"
- właściwości Typ dowodzenia i CommandText dokładnie opisz typ polecenia przesyłanego do serwera bazy danych oraz tekst samego polecenia. Na przykład, aby uzyskać dostęp do kostki Sprzedaż i uzyskać ją całkowicie w pamięci podręcznej klienta, można użyć kodu formularza
- własność Połączenie lokalne pozwala połączyć się z lokalną kostką (plik * .cub) utworzoną przez program Excel. Oczywiście nie zaleca się używania takich plików do pracy z woluminami danych „produkcyjnych” - tylko w celu tworzenia układów itp.
- własność Wykorzystano pamięć zwraca ilość pamięci RAM używanej przez PivotCache. Jeśli tabela przestawna oparta na tej tabeli przestawnej nie została jeszcze utworzona i nie jest otwarta, zwraca wartość 0. Można jej użyć do sprawdzenia, czy aplikacja będzie działać na słabych klientach.
- własność OLAP zwraca True, jeśli PivotCache jest podłączony do serwera OLAP.
- OptimizeCache - zdolność do optymalizacji struktury pamięci podręcznej. Wstępne ładowanie danych potrwa dłużej, ale wtedy prędkość pracy może wzrosnąć. W przypadku źródeł OLE DB nie działa.
Pozostałe właściwości obiektu PivotCache są takie same jak właściwości QueryTable, dlatego też nie będą tutaj brane pod uwagę.
Główną metodą obiektu PivotCache jest metoda CreatePivotTable (). Za pomocą tej metody wykonywany jest następny krok - utworzenie tabeli przestawnej (obiekt tabeli przestawnej). Ta metoda przyjmuje cztery parametry:
- Tabledestination jest jedynym wymaganym parametrem. Akceptuje obiekt Range, w lewym górnym rogu, w którym zostanie umieszczona tabela przestawna.
- Tablename to nazwa tabeli przestawnej. Jeśli nie zostanie określony, nazwa formularza „SummaryTable1” zostanie wygenerowana automatycznie.
- Readdata - jeśli ustawione na True, cała zawartość kostki zostanie automatycznie buforowana. Musisz być bardzo ostrożny z tym parametrem, ponieważ jego nieprawidłowe użycie może znacznie zwiększyć obciążenie klienta.
- DefaultVersion - ta właściwość zwykle nie jest określona. Umożliwia określenie wersji utworzonej tabeli przestawnej. Domyślnie używana jest najnowsza wersja.
Tworzenie tabeli przestawnej w pierwszej komórce pierwszego arkusza książki może wyglądać następująco:
PC1.CreatePivotTable Range („A1”)
Tabela przestawna została utworzona, ale natychmiast po utworzeniu jest pusta. Ma cztery obszary, w których możesz umieszczać pola ze źródła (na ekranie graficznym wszystko to można skonfigurować za pomocą okna Lista pól tabeli przestawnej - otwiera się automatycznie lub za pomocą przycisku Układ na ostatnim ekranie kreatora tworzenia tabeli przestawnej):
- obszar kolumny - umieszczane są w nim pomiary („sekcja”, w której dane będą analizowane), których członków jest mniej;
- powierzchnia rzędu - wymiary, których członkowie są większe;
- obszar strony - pomiary, za pomocą których konieczne jest jedynie filtrowanie (na przykład pokazują dane tylko dla takiego regionu lub tylko dla takiego roku);
- obszar danych - w rzeczywistości środkowa część stołu. Dane liczbowe (na przykład wielkość sprzedaży), które analizujemy.
Trudno polegać na tym, że użytkownik prawidłowo umieści elementy we wszystkich czterech obszarach. Ponadto może to zająć trochę czasu. Dlatego często wymagane jest programowe porządkowanie danych w tabeli przestawnej. Ta operacja jest wykonywana przy użyciu obiektu CubeField. Główną właściwością tego obiektu jest Orientacja, która określa, gdzie będzie znajdować się to lub inne pole. Na przykład umieszczamy wymiar Klienci w obszarze kolumny:
PT1.CubeFields („”) .Orientation \u003d xlColumnField
Następnie - wymiar Czas w obszarze wiersza:
PT1.CubeFields („”) .Orientation \u003d xlRowField
Dalej jest wymiar Produkt w obszarze strony:
PT1.CubeFields („”) .Orientation \u003d xlPageField
I na koniec wskaźnik (dane liczbowe do analizy) Sprzedaż jednostkowa:
PT1.CubeFields („.”). Orientacja \u003d xlDataField
Problemy analityki, OLAP, hurtowni danych cieszą się coraz większym zainteresowaniem rosyjskich specjalistów IT. Do tej pory wiele dobrych materiałów akademickich na ten temat, w tym wstępny, zostało opublikowanych w naszym druku komputerowym oraz w Internecie. Zwracamy uwagę na artykuł, w którym świadomie próbujemy wyjaśnić OLAP „na palcach”, używając konkretnego przykładu. Praktyka pokazuje, że takie wyjaśnienie jest konieczne dla niektórych specjalistów IT, a zwłaszcza użytkowników końcowych.
Tak więc OLAP * 1 jako pierwsze przybliżenie „na palcach” można zdefiniować jako specjalny sposób analizy danych i otrzymywania raportów. Jego istotą jest dostarczenie użytkownikowi wielowymiarowej tabeli, która automatycznie podsumowuje dane w różnych sekcjach i umożliwia interaktywne zarządzanie obliczeniami i formą raportu. W tym artykule omówimy technologię i podstawowe operacje OLAP na przykładzie analizy faktur przedsiębiorstwa prowadzącego hurtowy handel produktami spożywczymi.
* 1. OLAP - przetwarzanie analityczne on-line, analiza danych online.
System OLAP najprostszej i najtańszej klasy - klient OLAP * 1 będzie uważany za narzędzie. Na przykład wybrano najprostszy produkt z listy klientów OLAP - „Kontur Standard” firmy Intersoft Lab. (Dla większej przejrzystości w dalszej części artykułu ogólnie przyjęte terminy OLAP zostaną wytłuszczone i będą towarzyszyć im odpowiedniki w języku angielskim).
* 1. Więcej szczegółów na temat klasyfikacji systemów OLAP opisano w artykule „OLAP, Made in Russia” w PC Week / RE, nr 3/2001.
Zacznijmy od systemu. Najpierw musisz opisać źródło danych - ścieżkę do tabeli i jej pól. Jest to zadanie użytkownika znającego fizyczną implementację bazy danych. Dla użytkowników końcowych tłumaczy nazwę tabeli i jej pól na warunki obszaru tematycznego. Za „źródłem danych” stoi lokalna tabela, tabela lub widok serwera SQL lub procedury składowanej.
Najprawdopodobniej w konkretnej bazie danych faktury są przechowywane nie w jednej, ale w kilku tabelach. Ponadto niektóre pola lub rekordy mogą nie być wykorzystywane do analizy. W związku z tym tworzony jest Próbka (zestaw wyników lub zapytanie), w której skonfigurowane są: algorytm łączenia tabel według pól kluczowych, warunki filtrowania i zestaw zwracanych pól. Nazwiemy naszą próbkę „Fakturą” i umieścimy w niej wszystkie pola źródła danych „Faktura”. Dlatego informatyk, tworząc warstwę semantyczną, ukrywa fizyczną implementację bazy danych przed użytkownikiem końcowym.
Następnie konfigurowany jest raport OLAP. Może to zrobić specjalista w danej dziedzinie. Po pierwsze, pola płaskiej próbki danych są podzielone na dwie grupy - fakty (fakty lub miary) i wymiary (wymiary). Fakty to liczby, a wymiary to „cięcia”, w których fakty zostaną podsumowane. W naszym przykładzie pomiarami będą: „Region”, „Miasto”, „Kupujący”, „Produkt”, „Data”, i będzie jeden fakt - pole „Kwota” faktury. Aby to zrobić, musisz wybrać jeden lub więcej algorytmów agregacji. OLAP jest w stanie nie tylko podsumowywać wyniki, ale także wykonywać bardziej złożone obliczenia, włącznie z analizą statystyczną. Jeśli wybierzesz wiele algorytmów agregacji, zostaną utworzone wirtualne obliczone fakty. W tym przykładzie wybrano jeden algorytm agregacji - „Suma”.
Szczególną cechą systemów OLAP jest generowanie pomiarów i danych dla najbardziej uprzywilejowanych okresów od daty oraz automatyczne obliczanie sum dla tych okresów. Wybieramy okresy „Rok”, „Kwartał” i „Miesiąc” i w raporcie nie będzie danych dla każdego dnia, ale pojawią się wygenerowane pomiary „Rok”, „Kwartał” i „Miesiąc”. Nazwij raport „Analiza sprzedaży” i zapisz go. Prace nad tworzeniem interfejsu aplikacji analitycznej zostały zakończone.
Teraz, uruchamiając ten interfejs codziennie lub co miesiąc, użytkownik zobaczy tabelę i wykres, na którym zestawione są faktury za towary, klientów i okresy.
Aby manipulowanie danymi było intuicyjne, narzędziami do zarządzania dynamiczną tabelą są elementy samej tabeli - jej kolumn i wierszy. Użytkownik może je przenosić, usuwać, filtrować i wykonywać inne operacje OLAP. W takim przypadku tabela automatycznie oblicza nowe sumy pośrednie i końcowe.
Na przykład przeciągając („przenieś”) kolumnę „Produkt” na pierwsze miejsce, otrzymamy raport porównawczy - „Porównanie wielkości sprzedaży towarów w ciągu roku”. Aby agregować dane za rok, po prostu przeciągnij kolumny „Kwartał” i „Miesiąc” na górę tabeli - „nieaktywny obszar pomiaru”. Pomiary „kwartału” i „miesiąca” przeniesione do tego obszaru zostaną zamknięte (operacja „bliskiego wymiaru”), to znaczy wykluczona z raportu; podczas gdy fakty są podsumowywane w ciągu roku. Pomimo faktu, że pomiary są zamknięte, można ustawić określone lata, kwartały i miesiące dla filtrowania danych (operacja „filtrowania”).
Dla większej przejrzystości zmieniamy rodzaj wykresu ilustrującego tabelę OLAP i jej położenie na ekranie.
Pogłębianie danych (operacja „drążenia w dół”) pozwala uzyskać bardziej szczegółowe informacje na temat sprzedaży interesującego nas produktu. Klikając znak „+” obok produktu „Kawa”, zobaczymy jego wielkość sprzedaży według regionów. Po otwarciu regionu Ural otrzymamy wolumeny sprzedaży według miast regionu Ural, po wgłębieniu się w dane dotyczące Jekaterynburga będziemy mogli przeglądać dane dotyczące nabywców hurtowych tego miasta.
Możesz użyć otwartych pomiarów, aby ustawić filtry. Aby porównać dynamikę sprzedaży słodyczy w Moskwie i Jekaterynburgu, ustawimy filtry do pomiarów „towarów” i „miasta”.
Zamknij niepotrzebne pomiary i wybierz typ wykresu „Linia”. Na wynikowym wykresie można śledzić dynamikę sprzedaży, oceniać sezonowe wahania oraz związek spadków i wzrostu sprzedaży towarów w różnych miastach.
Byliśmy zatem przekonani, że technologia OLAP pozwala użytkownikowi wystawiać dziesiątki różnego rodzaju raportów z jednego interfejsu, kontrolując dynamiczną tabelę OLAP za pomocą myszy. Zadaniem programisty, który jest właścicielem takiego narzędzia, nie jest rutynowe kodowanie formularzy raportowania, ale konfigurowanie klienta OLAP dla baz danych. Jednocześnie metody zarządzania raportem są intuicyjnie zrozumiałe dla użytkownika końcowego.
Rzeczywiście OLAP jest naturalną kontynuacją i rozwinięciem idei arkuszy kalkulacyjnych. W rzeczywistości interfejs wizualny OLAP jest również arkuszem kalkulacyjnym, ale jest wyposażony w potężną maszynę obliczeniową i specjalny standard prezentacji i zarządzania danymi. Ponadto, niektórzy klienci OLAP są implementowani jako dodatek do MS Excel. Dlatego milionowa armia „białych kołnierzyków”, pewnie posiadająca arkusze kalkulacyjne, bardzo szybko opanowuje narzędzia OLAP. Dla nich jest to „aksamitna rewolucja”, która daje nowe możliwości, ale nie wiąże się z koniecznością ponownej nauki.
Jeśli czytelnik po przeczytaniu tego artykułu nie stracił zainteresowania OLAP, może odnieść się do materiałów wymienionych na początku. Kolekcje takich materiałów są dostępne na wielu stronach internetowych, w tym na stronie laboratorium Intersoft - www.iso.ru. Możesz także pobrać wersję demonstracyjną systemu „Kontur Standard” z przykładu opisanego w artykule.
