Problemy analityków, OLAP, magazyny danych są coraz bardziej zainteresowani rosyjskich profesjonalistów IT. Do tej pory istnieje wiele dobrych, akademickich w sposobie prezentacji materiałów na ten temat, w tym wkład w naszym druku komputerowym i Internecie. Wnioskujemy na twoją uwagę artykuł, w którym świadomie staramy się wyjaśnić OLAP "na palcach", na konkretnym przykładzie. Praktyka pokazuje, że takie wyjaśnienie jest konieczne dla niektórych specjalistów IT, a zwłaszcza użytkowników końcowych.
Tak, OLAP * 1 W pierwszym przybliżeniu, "na palcach" można zdefiniować jako specjalny sposób analizy danych i uzyskać raporty. Jego istotą jest zapewnienie użytkownikowi tabelę wielowymiarową, która automatycznie podsumowuje dane w różnych ciętach i pozwala interaktywnie zarządzać obliczeniami i formularzem raportu. Ten artykuł opowie o technologii i głównej operacji OLAP na przykładzie przykładowej analizy faktury przedsiębiorstwa zatrudnionego hurtowego handlu spożywczym.
*jeden. OLAP - przetwarzanie analityczne on-line, analiza danych operacyjnych.
Jako narzędzie, system OLAP najprostszej i niedrogiej klasy - OLAP Client * 1 zostanie rozważone. Na przykład wybrany jest najprostszy produkt klientów OLAP - Firma "Standard konturowy" InterSoft Lab. (W celu dalszej jasności w artykule, ogólnie przyjęte warunki OLAP zostaną oznaczone pogrubioną i towarzyszy analogi angielskojęzyczne).
*jeden. Więcej informacji na temat klasyfikacji systemów OLAP jest opisany w artykule "OLAP, MADE IN Rosja" w PC tygodnia / RE, №3 / 2001.
Więc przejdź do pracy z systemem. Aby rozpocząć, musisz opisać źródło danych (źródło danych) - ścieżka do tabeli i jej pól. To jest zadanie użytkownika, który wie fizyczna realizacja Baza danych. Dla użytkowników końcowych tłumaczy nazwę tabeli i jej pól pod względem obszaru przedmiotowego. W przypadku "Źródło danych" znajduje się tabela lokalna, tabela lub prezentacja (widok) serwera SQL lub procedury przechowywanej.
Najprawdopodobniej w określonej bazie danych rachunkowości nie jest przechowywany w jednym, ale w kilku tabelach. Ponadto niektóre pola lub rekordy nie mogą być używane do analizy. Dlatego powstaje próbka (zestaw wyników lub zapytania), - w którym skonfigurowany: algorytm do łączenia tabel na polach kluczy, warunkach filtrowania i zestawu powróconych pól. Nazywamy naszą próbkę "faktury" i umieścimy wszystkie pola źródła "faktury". W ten sposób specjalista IT, tworząc warstwę semantyczną, ukrywa fizyczną implementację bazy danych od użytkownika końcowego.
Następnie raport OLAP jest skonfigurowany. Może to obejmować specjalistę w obszarze tematycznym. Po pierwsze, pola płaskiego pobierania próbek danych są podzielone na dwie grupy - fakty (fakty lub środki) oraz pomiary (wymiary). Fakty są liczbami, a pomiary są "cięcia", w których fakty zostaną podsumowane. W naszym przykładzie pomiary będą: "region", "miasto", "kupujący", "produkt", "data", a fakt będzie jeden - "kwotę" pola faktury. Dla faktu, musisz wybrać jedną lub więcej algorytmów agregacji. OLAP jest w stanie nie tylko podsumować wyniki, ale także do wykonywania bardziej złożonych obliczeń, do analizy statystycznej. Wybierając wiele algorytmów agregacji, wirtualne, obliczone fakty (obliczone fakty) zostanie utworzone. W przykładzie wybrana jest pojedynczy algorytm agregacji - "Ilość".
Specjalna właściwość systemów OLAP jest generacja pomiarów i danych w starszych okresach od daty i automatycznego obliczania wyników dla tych okresów. Wybieramy okresy roku, kwartał i miesiąc, podczas gdy dane dla każdego dnia nie będą w raporcie, ale pojawią się wygenerowane pomiary roku, kwartał i miesiąc pojawią się. Zadzwońmy do raportu analizy sprzedaży i zapisz go. Zakończono prace nad tworzeniem interfejsu aplikacji analitycznej.
Teraz, codziennie lub miesięczny początek ten interfejs, użytkownik zobaczy tabelę i harmonogram, w którym faktury są podsumowane przez towary, kupujący i okresy.
W celu manipulacji z danymi intuicyjnie zrozumiałymi narzędzia do zarządzania tabelami są elementy samej tabeli - jego kolumny i łańcuchy. Użytkownik może je przesunąć, usunąć, filtrować i wykonywać inne operacje OLAP. W takim przypadku tabela automatycznie oblicza nowe wyniki pośrednie i końcowe.
Na przykład przeciąganie (operacja "Przenieś") "produkt" na pierwsze miejsce, otrzymamy raport porównawczy - "Porównanie sprzedaży towarów na rok". Aby zagregować dane na rok, wystarczy przeciągnąć kolumny "ćwierć" i "miesiąc" w górnej części tabeli - "obszar nieaktywnych pomiarów". Pomiary "Dzielnica" i "miesiąc" przeniesiony do tego obszaru zostaną zamknięte (obsługa "Zamknij wymiar"), tj. Wyłączony z raportu; Jednocześnie fakty są podsumowane na rok. Pomimo faktu, że pomiary są zamknięte, można ustawić określone lata, kwartały i miesiące do filtrowania danych (operacja "filtr").
Dla większej jasności zmienisz typ wykresu ilustrujący tabelę OLAP i jej lokalizację na ekranie.
Pogłębianie danych (operacja wiertła) Umożliwia uzyskanie bardziej szczegółowych informacji o sprzedaży zainteresowanych towarów. Klikając na znak "+" naprzeciw towarów "Kawa", zobaczymy wolumen sprzedaży w kontekście regionów. Przegapiamy region Ural, otrzymamy sprzedaż w kontekście miast regionu Uralu, przechodząc do danych dotyczących "Yekaterinburg", będziemy mogli przeglądać dane dotyczące hurtowych nabywców tego miasta.
Możesz użyć otwartych pomiarów, aby zainstalować filtry. Aby porównać dynamikę sprzedaży cukierków w Moskwie i Jekaterynburgu, zainstaluj filtry do filtrów pomiarowych "Produkt" i "City".
Zamknij niepotrzebne pomiary i wybierz typ grafiki linii. W wynikowym harmonogramie można śledzić dynamikę sprzedaży, ocenić wahania sezonowe i połączenie kropli i wzrost towarów w różnych miastach.
Dlatego upewniliśmy się, że technologia OLAP umożliwia użytkownikowi z jednego interfejsu, aby uwolnić dziesiątki gatunków szerokiej gamy raportów, prowadząc dynamiczną tabelę OLAP z myszą. Zadaniem programisty posiadającego takie narzędzie nie staje się rutynowym kodowaniem formularzy raportowania, ale konfigurowanie klienta OLAP do baz danych. Jednocześnie metody raportowania intuicyjnie zrozumiały dla użytkownika końcowego.
Rzeczywiście, OLAP jest naturalną kontynuacją i rozwojem idei arkuszy kalkulacyjnych. W rzeczywistości interfejs wizualny OLAP jest również arkuszem kalkulacyjnym (arkusz kalkulacyjny), ale wyposażony w potężna maszyna Obliczenia i specjalny standard reprezentacji danych i zarządzania. Ponadto niektórzy klienci OLAP są realizowane jako dodatek do MS Excel. Dlatego wspaniała armia "White Collar", pewnie posiadająca arkusze kalkulacyjne, bardzo szybko mistrzów i narzędzi OLAP. Dla nich jest to "aksamitna rewolucja", zapewniająca nowe możliwości, ale nie koniugatu z konieczności przekwalifikowania się.
Jeśli czytelnik, po przeczytaniu tego artykułu, nie stracił zainteresowania OLAP, może odnosić się do wymienionych materiałów na początku. Kolekcje takich materiałów są publikowane na wielu witrynach w Internecie, w tym Witryna Intersoft Lab - www.iso.ru. Możesz także pobrać wersję demonstracyjną systemu "Standardowy kontur" z przykładem opisanym w artykule.
Pierwszy interfejs tabel podsumowujących, zwany także skonsolidowanym raportami, został zawarty w programie Excel z powrotem w 1993 r. (Wersje Excel 5.0). Pomimo wielu przydatnych funkcjonalnośćJest praktycznie nieużywany w pracy większości użytkowników Excel. Nawet doświadczeni użytkownicy są często implikowane przez termin "Raport podsumowujący" coś zbudowanego z pomocą złożonych formuł. Spróbujmy popularyzować stosowanie tabel podsumowujących w codziennej pracy ekonomistów. W artykule omawia teoretyczne podstawy tworzenia skonsolidowanych sprawozdań, dostarczają praktyczne zalecenia dotyczące ich użycia, a także zapewnia przykład dostępu do danych na podstawie kilku tabel.
Warunki analizy danych wielowymiarowej
Większość ekonomistów usłyszała terminy "wielowymiarowe dane", "wirtualna kostka", "OLAP-Technologies" itp. Ale ze szczegółową rozmową zazwyczaj dowiaduje się, że prawie każdy naprawdę nie wyobraża sobie tego, co to jest mowa. Oznacza to, że ludzie oznaczają coś złożonego i zwykle nie związane z ich codziennymi czynnościami. W rzeczywistości nie jest.
Dane wielowymiarowe, pomiary
Bezpieczne jest argumentowanie, że ekonomiści są prawie stale skonfrontowane z wielowymiarowymi danymi, ale próbują przedstawić je w określonej formie przy użyciu arkuszy kalkulacyjnych. W obszarze Multidimensial jest możliwość wejścia, przeglądania lub analizowania tych samych informacji o zmianie wyglądu, wykorzystanie różnych grup i sortowania danych. Na przykład plan sprzedaży może być analizowany według następujących kryteriów:
- gatunki lub grupy towarów;
- marki lub kategorie towarów;
- okresy (miesiąc, kwartał, rok);
- kupujący lub grupy kupujących;
- sprzedaż regionów.
- itp.
Każde z powyższych kryteriów pod względem wielowymiarowej analizy danych nazywa się "pomiarem". Można powiedzieć, że pomiar charakteryzuje informacje na określonym zestawie wartości. Specjalny rodzaj pomiaru informacji wielowymiarowych to "dane". W naszym przykładzie plan sprzedaży może być:
- wielkość sprzedaży;
- cena sprzedaży;
- indywidualny rabat
- itp.
Teoretycznie dane mogą być również standardowym wymiarem informacji wielowymiarowych (na przykład, możliwe jest grupowanie ceny ceny sprzedaży), ale zazwyczaj dane są specjalnym typem wartości.
W ten sposób możemy to powiedzieć praktyczna praca Ekonomiści używają dwóch rodzajów informacji: dane wielowymiarowe (rzeczywiste i planowane liczby mające wiele funkcji) i książek referencyjnych (cechy lub pomiary danych).
Olej.
Skrót OLAP (przetwarzanie analityczne online) w literalnym tłumaczeniu brzmi jak "przetwarzanie analityczne w czasie rzeczywistym". Definicja nie jest zbyt specyficzna, prawie każdy raport o dowolnym oprogramowaniu można podsumować. Według OLAP oznacza to technologię pracy ze specjalnymi raportami, w tym oprogramowaniem do uzyskania i analizowania tylko wielowymiarowych danych strukturalnych. Jednym z popularnych produktów oprogramowania wdrażających technologię OLAP jest SQL Server. Serwer analityczny. Niektórzy nawet myliła go jedynym przedstawicielem realizacji programu tej koncepcji.
Wirtualna kostka danych.
"Wirtualny sześcian" (wielokrotny kostka, OLAP Cube) jest specjalnym terminem zaproponowanym przez niektórych wyspecjalizowanych dostawców oprogramowania. Systemy OLAP są zwykle przygotowywane i przechowywane dane w swoich strukturach i specjalnych interfejsach analizy (na przykład raporty podsumowujące Excel) odnoszą się do tych wirtualnych kostek. W tym przypadku zastosowanie takiej dedykowanej pamięci nie jest wcale niezbędne do przetwarzania informacji wielowymiarowych. Ogólnie, wirtualny sześcienny - Jest to szereg specjalnie zoptymalizowanych danych wielowymiarowych, która służy do tworzenia raportów podsumowujących. Można go uzyskać zarówno poprzez wyspecjalizowane oprogramowanie, jak i prostym dostęp do tabel bazy danych lub dowolnego innego źródła, takich jak tabela Excel.
Tablica zbiorcza
"Raport podsumowujący" (stół podsumowujący, tabela obrotowa) jest interfejsem użytkownika do wyświetlania danych wielowymiarowych. Dzięki temu interfejsie możesz grupować, sortować, filtrować i zmienić lokalizację danych w celu uzyskania różnych próbek analitycznych. Aktualizacja raportu jest wykonana przez proste środki interfejs użytkownikaDane są automatycznie agregowane zgodnie z określonymi zasadami, nie wymaga dodatkowych lub ponownych wprowadzania żadnych informacji. Interfejs komunikacyjny tabele Excel. Jest to prawdopodobnie najbardziej popularny produkt oprogramowania do pracy z wielowymiarowymi danymi. Obsługuje źródło danych jako zewnętrzne źródła danych (kostki OLAP i relacyjne bazy danych) oraz wewnętrzne zakresy arkuszy kalkulacyjnych. Począwszy od wersji 2000 (9.0), Excel obsługuje również formę graficzną wielowymiarowej wyświetlania danych - diagram podsumowania (wykres obrotowy).
Skonsolidowany interfejs tabeli zaimplementowany w programie Excel umożliwia zorganizowanie pomiaru danych wielowymiarowych w obszarze arkusza roboczego. Dla prostoty można sobie wyobrazić tabelę podsumowującą, jako raport leżący na górze komórek (w rzeczywistości istnieje pewne wiązanie formatów komórkowych do pól tabeli obrotowej). Tabela podsumowania Excel ma cztery obszary wyświetlania informacji: filtr, kolumny, ciągi i dane. Pomiary danych są nazywane pola tabeli obrotu. Pola te mają własne właściwości i format wyświetlania.
Po raz kolejny chciałbym zwrócić uwagę na fakt, że tabela podsumowania Excel jest przeznaczona wyłącznie do analizy danych bez możliwości edycji informacji. Bliżej w znaczeniu byłoby powszechne wykorzystanie terminu "Raport podsumowujący" (Raport Pivot), a w ten sposób ten interfejs został wywołany do 2000 lat. Ale z jakiegoś powodu, w kolejnych wersjach deweloperzy odmówili go.
Edycja skonsolidowanych tabel
Zgodnie z jego definicją, OLAP Technology, w zasadzie, nie oznacza sugeruje możliwości zmiany danych źródłowych podczas pracy z raportami. Jednak cała klasa utworzona na rynku systemy oprogramowaniaWdrażanie możliwości zarówno danych analizy, jak i bezpośredniego edycji danych w tabelach wielowymiarowych. Zasadniczo takie systemy koncentrują się na rozwiązywaniu zadań budżetowych.
Korzystając z wbudowanej automatyki Excel, można rozwiązać wiele niestandardowych zadań. Przykład przykładu edycji tabel podsumowujących Excel na podstawie danych arkusza roboczego można znaleźć na naszej stronie internetowej.
Przygotowanie danych wielowymiarowych
Garnitur C. praktyczne zastosowanie Stoły podsumowujące. Spróbujmy analizować dane sprzedaży w różnych kierunkach. Plik pivottableExample.xls. Składa się z kilku arkuszy. Arkusz Przykładzawiera podstawowe informacje o sprzedaży przez określony czas. Dla prostoty z przykładu przeanalizujemy jedyny wskaźnik numeryczny - wielkość sprzedaży w kg. Dostępne są następujące kluczowe pomiary danych: produkty, kupujący i przewoźnik (firma transportowa). Ponadto istnieje kilka dodatkowych wymiarów danych, które są wyposażone w produkt: typ, marka, kategoria, dostawca i kupujący: typ. Dane te są gromadzone w arkuszu prowadzącego. W praktyce takie pomiary mogą być znacznie więcej.
Arkusz Przykładzawiera standardowy środek zaradczy Analiza danych - AutoFilter. Patrząc na przykład wypełnienia tabeli, jest oczywiste, że dane sprzedaży danych w datach można znormalizować (znajdują się na kolumnach). Ponadto, korzystając z autofiltera, możesz spróbować podsumować dane dotyczące kombinacji jednego lub więcej kluczowych kryteriów. Nie ma absolutnie żadnych informacji o marek, kategoriach i typach. Nie ma sposobu na grupę danych z automatycznym podsumowaniem w określonym kluczu (na przykład przez kupujących). Ponadto zestaw dat jest ustalany i wyświetlać łączne informacje przez określony czas, na przykład przez 3 dni, automatyczne środki nie będą możliwe.
Ogólnie rzecz biorąc, obecność z góry określonej lokalizacji daty ten przykład - główna wadą tabeli. Umieszczając daty na kolumnach, jak to było, z góry określony pomiar tej tabeli, tym samym pozbawiony siebie, aby wykorzystać analizę za pomocą tabel podsumowujących.
Po pierwsze, konieczne jest pozbycie się tej wady - tj. Usuń określoną lokalizację jednego z pomiarów danych źródłowych. Przykład właściwej tabeli - arkusz Sprzedaż.
Tabela ma formę dziennika wpisu informacji. Tutaj data jest równym wymiarem danych. Należy również zauważyć, że w przypadku kolejnej analizy w skonsolidowanych tabelach, względna pozycja wierszy w stosunku do siebie (innymi słowy, sortowanie) jest całkowicie obojętny. Właściwości te mają wpisy w relacyjnych bazach danych. Jest on na analizie dużych ilości baz danych, który jest przede wszystkim zorientowany głównie interfejsem skonsolidowanych tabel. Dlatego konieczne jest przestrzeganie tych zasad i podczas pracy z źródłem danych w postaci zakresów komórek. Jednocześnie nikt nie może używać narzędzi interfejsu Excel w działaniu - tabele podsumowujące analizują tylko dane, oraz formatowanie, filtry, grupowania i komórek źródłowych sortowania mogą być dowolne arbitralne.
Od autofiltera do skonsolidowanego raportu
Teoretycznie dane arkusze danych mogą być już analizowane w trzech wymiarach: towarów, nabywców i przewoźników. Dane dotyczące właściwości produktów i nabywców na tym arkuszu nie są dostępne, które odpowiednio nie pozwolą im pokazać je i w skonsolidowanej tabeli. W normalnym trybie tworzenia tabeli obrotowej dla źródła dane Excel. Nie wiąże wielu tabel na określonych dziedzinach. Pomijanie tego ograniczenia może być oprogramowanie - Zobacz przykładowy suplement do tego artykułu na naszej stronie internetowej. Aby nie uzpracować metodami oprogramowania do przetwarzania informacji (tym bardziej, że nie są one uniwersalne), należy dodać dodatkowe cechy, należy dodać bezpośrednio do formy wejścia do dziennika - patrz listę sprzedaży.
Korzystanie z funkcji VLookup ułatwia uzupełnienie wstępnych danych z brakującymi właściwościami. Teraz używając autofiltera, możesz przeanalizować dane różne pomiary. Ale problem grup pozostaje nierozwiązany. Na przykład do śledzenia kwoty tylko marki dla niektórych dat jest dość problematyczne. Jeśli jesteś ograniczony do formuł Excel, musisz zbudować dodatkowe próbki za pomocą funkcji SUMIF.
Teraz zobaczmy, co zawiera skonsolidowany interfejs tabeli. Na arkuszu Svodarnis.kilka raportów opartych na zakresie arkuszy z danymi arkuszowymi Szansa.
Pierwsza tabela analizy jest zbudowana przez interfejs Excel 2007 Stół podsumowany wstążka (w menu Excel 2000-2003 Tabela podsumowania danych).
Drugie i trzecie tabele są tworzone poprzez kopiowanie i kolejne ustawienie. Źródło danych dla wszystkich tabel jest taki sam. Możesz to sprawdzić, zmieniając dane źródłowe, musisz zaktualizować dane raportu podsumowującego.
Z naszego punktu widzenia zalety jasności informacji są oczywiste. Możesz zmienić filtry, kolumny i ciągi, a także ukryć pewne grupy wartości dowolnych pomiarów, stosować ręczne przeciąganie i automatyczne sortowanie.
Właściwości i formatowanie
Oprócz wyświetlania bezpośrednich danych znajduje się duży zestaw funkcji wyświetlania wyglądu tabel podsumowujących. Nadmiar danych można ukryć za pomocą filtrów. Dla pojedynczego elementu lub pola łatwiejsze w użyciu elementu menu kontekstowego Kasować(w wersji 2000-2003 Ukryć).
Ustaw Wyświetlanie innych elementów skonsolidowanej tabeli jest również korzystnie nie poprzez formatowanie komórki, ale przez ustawienie pola lub elementem skonsolidowanej tabeli. Aby to zrobić, konieczne jest podsumowanie wskaźnika myszy do pożądanego elementu, poczekaj na pojawienie się specjalnego kształtu kursora (jako strzałki), a następnie wybierz wybrany element za pomocą jednego kliknięcia. Po wybraniu można zmienić widok przez taśmę, menu kontekstowe lub wywołaj standardowy okno dialogowe formatu komórkowego:
Ponadto w programie Excel 2007 pojawił się wiele predefiniowanych stylów wyświetlanych stylów:
Należy pamiętać, że na diagramie są aktywne filtry sterujące i obszary przeciągania i upuszczania.
Dostęp do danych zewnętrznych
Jak już zauważył, być może największy wpływ stosowania tabel podsumowujących można uzyskać podczas dostępu przez zewnętrzne źródła, są kostki OLAP i baz danych. Takie źródła zazwyczaj przechowują duże ilości informacji, a także mają predefiniowany struktura relacyjnaUłatwia to określenie pomiarów danych wielowymiarowych (skonsolidowanych pól tabeli).
Excel obsługuje wiele typów zewnętrznych źródeł danych:
Największy efekt z wykorzystaniem zewnętrznych źródeł informacji można osiągnąć za pomocą narzędzi automatyzacji (programy VBA), zarówno w celu uzyskania danych, jak i do ich wstępnego przetwarzania w skonsolidowanych tabelach.
Analityczne przetwarzanie analityczne (OLAP) to technologia, która służy do usprawnienia dużych baz danych biznesowych i wsparcia inteligencji biznesowej. Bazy danych OLAP są podzielone na jedną lub więcej kostek, a każdy z nich jest organizowany przez administratora Kuby, aby odpowiada sposobowi uzyskania i analizy danych w celu ułatwienia tworzenia i wykorzystania raportów tabeli podsumowujących i raportów wykresu podsumowania że potrzebujesz.
W tym artykule
Jaki jest analityk biznesowy?
Analityk biznesowy często chce uzyskać duży obraz firmy, aby zobaczyć szersze trendy oparte na zagregowanych danych, a także widoku trendów z podziałem przez dowolną liczbę zmiennych. Analityka biznesowa jest procesem wyodrębniania danych z bazy danych OLAP i analizowanie tych danych do informacji, które można wykorzystać do podjęcia informacji biznesowych i działań. Na przykład, korzystając z Analityków OLAP i Business, możesz odpowiedzieć na następujące pytania dotyczące danych biznesowych.
W jaki sposób całkowita kwota sprzedaży wszystkich produktów w 2007 r. W porównaniu ze sprzedażą od 2006 roku?
W jaki sposób porównywa się z datą i godziną w korzystnym okresie w ciągu ostatnich pięciu lat?
Ile klientów pieniężnych spędził 35 w zeszłym roku i jak to zmieniło się z czasem?
Ile produktów zostało sprzedanych w dwóch konkretnych krajach / regionach w tym miesiącu, w przeciwieństwie do tego samego miesiąca ostatniego roku?
Dla każdej grupy wieków klientów, jakie jest oddzielenie rentowności (jako odsetek marży i wyniku) według kategorii towarów?
Wyszukaj najlepszych i niższych dostawców, dystrybutorów, dostawców, klientów, partnerów i klientów.
Co to jest przetwarzanie analityczne w Internecie (OLAP)?
Baza danych OLAP (przetwarzanie analityczne online) Uprość żądania analityki biznesowej. OLAP to technologia bazy danych zoptymalizowana dla zapytań i raportów, a nie do przetwarzania transakcji. Źródłem danych dla OLAP jest bazami danych przetwarzania transakcji operacyjnych (OLTP), które są zwykle przechowywane w magazynach danych. Dane OLAP ekstrahuje się z tych danych historycznych i łączy się w struktury, które tworzą złożoną analizę. Dane OLAP są również zamówione hierarchicznie i przechowywane w kostkach, a nie w tabelach. Jest to kompleksowa technologia, która wykorzystuje wielowymiarowe struktury, aby zapewnić szybki dostęp do danych do analizy. W tej organizacji podsumowanie tabeli podsumowrażowej lub raportu ze schematu podsumowującego można łatwo wyświetlać dane podsumowujące wysokiego szczebla, takie jak wyniki sprzedaży w całym kraju lub regionie, a także wyświetlanie informacji o witrynach, w których sprzedaż jest szczególnie wysoka lub słaba .
Bazy danych OLAP są zaprojektowane tak, aby przyspieszyć ładowanie danych. Od serwera OLAP, a nie Microsoft Office Excel, oblicza ogólne wartości, podczas tworzenia lub zmiany raportu musisz wysłać mniejsze dane do programu Excel. Takie podejście pozwala pracować z dużą ilością danych źródłowych niż gdyby dane zostały zorganizowane w tradycyjnej bazie danych, gdzie Excel ekstrahuje wszystko oddzielne wpisy i oblicza ogólne wartości.
Bazy danych OLAP zawierają dwa podstawowe typy danych: środki, które są dane liczbowe, ilości i średnie wartości, które służą do podjęcia poinformowanych rozwiązań biznesowych oraz pomiary, które są kategoriami używanymi do usprawnienia tych środków. Bazy danych OLAP pomagają usprawnić dane za pomocą wielu poziomów szczegółów przy użyciu tych samych kategorii, które znasz, aby analizować dane.
W poniższych sekcjach każdy składnik opisano szczegółowo poniżej.
Sześcienny Struktura danych, która łączy środki na poziomy i hierarchie każdego pomiaru, który chcesz przeanalizować. Kostki łączą kilka pomiarów, takich jak czas, geografia i linie produktów, z ostatecznymi danymi, takimi jak sprzedaż i zapasy. Kostki nie są "Kuba" w surowym znaczeniu matematycznym, ponieważ niekoniecznie mają te same boki. Niemniej jednak są metaforem apt dla złożonej koncepcji.
Pomiary Zestaw wartości w kostce opartej na kolumnie w stole fabrycznym Cube i która jest zwykle wartością numeryczną. Środki są wartościami centralnymi na Kubie, które są wstępnie przetworzone, przetwarzane i analizowane. Najczęstszymi przykładami są sprzedaż, dochód, dochody i koszty.
Członek Element w hierarchii reprezentujących jeden lub więcej wpisów danych. Element może być zarówno unikalny, jak i nienarodowy. Na przykład 2007 i 2008 r. Reprezentują unikalne elementy na poziomie roku pomiaru czasu, podczas gdy styczeń reprezentuje nieodległych elementów na poziomie miesiąca, ponieważ istnieje więcej niż jeden w pomiarze czasu, ponieważ zawiera dane dla więcej niż rok.
Obliczony przedmiot Element pomiarowy, którego wartość jest obliczana podczas wykonania przy użyciu wyrażenia. Wartości obliczonych elementów można uzyskać z wartości innych członków. Na przykład obliczony element "zysku" można określić przez odjęcie wartości elementu, a także kosztów wartości elementu, sprzedaż.
pomiar Zestaw jednego lub więcej zamówionych hierarchii poziomów kostki, które użytkownik rozumie i wykorzystuje jako podstawa do analizy danych. Na przykład pomiar geograficzny może obejmować poziomy dla kraju / regionu, stanu / dzielnicy i miasta. Ponadto pomiar czasu może obejmować hierarchię z poziomem "roku", "kwartału", "miesiąca" i "dnia". W podsumowaniu tabeli lub raporcie raportu każda hierarchia staje się zestawem pól, które można wdrożyć i składać, aby pokazać niższe lub wyższe poziomy.
Hierarchia Logiczna struktura drzewa strumieniowego elementów pomiarowych, dzięki czemu każdy element ma jeden element macierzysty i zero lub więcej elementów dziecka. Element dziecka jest członkiem wcześniejszej grupy w hierarchii bezpośrednio związanej z obecnym członkiem. Na przykład w hierarchii czasu zawierającej poziomy kwartału, miesiąca i dnia styczeń jest spółką zależną QTR1. Element macierzysty jest członkiem niższego poziomu w hierarchii bezpośrednio związanej z bieżącym członkiem. Wartość rodzicielska zazwyczaj konsoliduje wartości wszystkich elementów dziecka. Na przykład, w hierarchii czasowej zawierającej poziomy "kwartału", "miesiąca" i "dnia", QTR1 jest rodzicem dla stycznia.
Poziom W hierarchii dane można zorganizować na niższy i wyższy poziom szczegółowości, takich jak lata, kwartały, miesiące i poziomy na dzień w hierarchii czasowej.
Funkcje OLAP w Excelu
Dostanie danych OLAP. Możesz połączyć się ze źródłami danych OLAP w taki sam sposób, jak inne zewnętrzne źródła danych. Możesz pracować z bazami danych utworzonymi za pomocą usług. Microsoft SQL. Server OLAP Wersja 7.0, Microsoft SQL Server Analysis Services Server Analysis Analysis Service Services Analysis Services, Produkty serwerowe Microsoft OLAP. Excel może również współpracować z produktami OLAP firmy OLAP, które są kompatybilne z OLE-DB dla OLAP.
Dane OLAP mogą być wyświetlane tylko jako podsumowanie tabeli podsumowującą lub raportu schematu podsumowania lub w funkcji arkusza, przekształcone z skonsolidowanego raportu tabeli, ale nie w postaci zewnętrznego zakresu danych. Możesz zapisać skonsolidowane tabele OLAP i wykresy podsumowujące w szablonach raportów, a także tworzyć pliki połączeń danych biurowych (ODC), aby połączyć się z bazami danych OLAP dla żądań OLAP. Podczas otwierania pliku ODC w programie Excel wyświetlany jest pusty raport z skonsolidowanej tabeli, który jest gotowy do zakwaterowania.
Tworzenie plików kostek do autonomicznego użytkowania Możesz utworzyć autonomiczny plik kostki (Cub) z podzbiorem danych z bazy danych OLAP Server. Pliki Offline Cube są używane do pracy z danymi OLAP, jeśli nie jesteś podłączony do sieci. Za pomocą kostki można pracować z dużą ilością danych w raporcie tabeli obrotowej lub raportu skonsolidowanego wykresu niż w inny sposób i przyspieszyć uzyskiwanie danych. Możesz tworzyć pliki Cube tylko wtedy, gdy używasz dostawcy OLAP, takich jak Microsoft SQL Analysis Services w wersji 2005, która obsługuje tę funkcję.
Akcje serwera. Działanie serwera jest opcjonalną funkcją, którą administrator OLAPA Cuba może zdefiniować na serwerze, który używa elementu Cube lub umiarkowany jako parametr w zapytaniu, aby uzyskać informacje na Kubie lub rozpocząć inną aplikację, na przykład przeglądarkę. Excel obsługuje adresy URL, raporty, zestawy wierszy, detalowanie i wdrażanie szczegółowych działań serwera, ale nie obsługuje własnego własnego operatora i zestawu danych.
Kpe. Wskaźnik klucza jest specjalnym obliczonym środkiem zdefiniowanym na serwerze, który umożliwia śledzenie " kluczowe wskaźniki Wydajność ", w tym stan (wartość bieżąca odpowiada konkretna liczba). i trend (wartości w czasie). Gdy są wyświetlane, serwer może wysłać odpowiednie ikony podobne do nowej ikony Excel, dzięki czemu są one zbudowane powyżej lub poniżej poziomów stanu (na przykład, dla ikony STOP), a także awans w górę lub w dół (na przykład, Ikona strzałki kierunku).
Formatowanie na serwerze Administratorzy Cube mogą tworzyć środki i obliczone elementy za pomocą formatowania kolorów, formatowania czcionek i reguł formatowanie warunkowektóry można powołujący jako standardowa reguła biznesowa korporacyjna. Na przykład format serwera dla dochodu może być formatem walutowym numerycznym, kolorowym kolorem komórek, jeśli wartość jest większa niż lub równa 30 000, a czerwone, jeśli mniej niż 30 000, a styl czcionki jest odważny, jeśli mniej niż 30 000 i z wartością dodatnią - normalnie. Więcej lub równa 30 000. Dodatkowe informacje Może być znaleziony.
Język interfejsu biurowego Administrator Kuby może zdefiniować transfery dla danych i błędów na serwerze dla użytkowników, którzy muszą wyświetlać skonsolidowane informacje o tabeli w innym języku. Ta funkcja jest zdefiniowana jako właściwość połączenia z plikiem i parametry regionalne. Użytkownik komputera i kraj muszą pasować do języka interfejsu.
Komponenty oprogramowania wymagane do uzyskania dostępu do źródeł danych OLAP
OLAP Dostawca Aby skonfigurować źródła danych OLAP dla programu Excel, wymagane jest jeden z powyższych dostawców OLAP.
Dostawca Microsoft Olap. Excel zawiera oprogramowanie sterownika źródła danych i oprogramowanie do dostępu do baz danych utworzonych przy użyciu usług Microsoft SQL Server Olap Server Olap, Microsoft SQL Server OLAP Wersja 2000 (8.0) i Microsoft SQL Server Analysis Services w wersji 2005 (9 0).
Dostawcy osób trzecich OLAP W przypadku innych produktów OLAP należy zainstalować dodatkowe sterowniki i oprogramowanie klienckie. Aby korzystać z funkcji Excel do pracy z danymi OLAP, produkt osób trzecich musi być zgodny z normą OLE-DB dla OLAP i być kompatybilne z Microsoft Office. Aby uzyskać informacje na temat instalacji i korzystania z dostawcę OLAP firmy Thery-Party administrator systemu lub dostawca produktu OLAP.
Bazy danych serwerów i pliki kostki Oprogramowanie klienta 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 odbierać dane źródłowe bezpośrednio z niego bezpośrednio. Jeśli masz samodzielny plik sześcianu, który zawiera dane OLAP lub definicję plików kostek, możesz połączyć się z tym plikiem i uzyskać dane źródłowe z niego.
Źródła danych Źródło danych zapewnia dostęp do wszystkich danych w bazie danych OLAP lub autonomiczny plik sześcianu. Tworząc źródło danych OLAP, możesz opierać raporty na nim i zwrócić dane OLAP do programu Excel w formie tabeli podsumowania lub raportu raportu podsumowującego, a także funkcję arkusza, która została przekonwertowana z skonsolidowanego raportu tabeli.
Zapytanie Microsoft. Dzięki zapytaniu można odbierać dane z zewnętrznej bazy danych, takich jak Microsoft SQL lub Dostęp Microsoft.. Aby uzyskać dane z tabeli Podsumowanie OLAP związane z plikiem Kuba, użyj zapytania nie jest wymagane. Dodatkowe informacje .
Różnice w funkcjach danych źródłowych OLAP i NONAP
Jeśli pracujesz z raportami skonsolidowanych tabel i diagramów podsumowujących z danych źródłowych OLAP i innych typów danych źródłowych, zobaczysz niektóre funkcje.
Wydobywanie danych. Serwer OLAP zwraca nowe dane do programu Excel za każdym razem, gdy układ raportu został zmieniony. Korzystając z innych typów zewnętrznych źródeł danych, żądasz wszystkich danych źródłowych jednocześnie lub można określić parametry żądania tylko wtedy, gdy wyświetlane są różne elementy pól filtrowania raportu. Ponadto masz więcej opcji aktualizacji raportu.
W raportach opartych na danych źródłowych OLAP, parametry pola filtra raportu nie są dostępne, zapytanie tła jest niedostępne, a parametr optymalizacji pamięci nie jest dostępny.
Uwaga: Parametr optymalizacji pamięci jest również niedostępny dla źródeł danych OLEDB i podsumowanie raportów stołowych na podstawie zakresu komórek.
Rodzaje pól Dane źródłowe OLAP. Pomiar pola mogą być używane tylko jako wiersze (wiersze), kolumny (kategoria) lub pole strony. Pola 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ółowych danych W przypadku danych źródłowych OLAP serwer określa dostępne poziomy szczegółów i oblicza wartości podsumowujące, więc rekordy ze szczegółowymi informacjami, które tworzą całkowite wartości mogą nie być dostępne. Jednak serwer może zapewnić właściwości polowe, które możesz wyświetlać. Inne typy danych źródłowych nie mają pól właściwości, ale można wyświetlić podstawowe informacje dla pól danych i elementów oraz wyświetlanie elementów bez danych.
Pola filtrowania raportu OLAP mogą nie być wszystko Elementy i zespół Pokaż raport Strony filtra Niedostępne.
Sortowanie zamówienia początkowym W przypadku danych źródłowych OLAP elementy są najpierw wyświetlane w kolejności, w którym są zwracane przez serwer OLAP. Możesz sortować lub ręcznie zmienić kolejność elementów. W przypadku innych typów danych źródłowych elementy nowego raportu są po raz pierwszy posortowane według nazwy elementu w kolejności rosnącej.
Nimi. Serwery OLAP zapewniają uogólnione wartości bezpośrednio do raportu, więc niemożliwe jest zmianę funkcji końcowych dla pól wartości. W przypadku innych typów danych źródłowych można zmienić funkcję statystyczną dla pola wartości i użyć wielu końcowych funkcji dla tej samej wartości. Nie można tworzyć obliczonych pól i obliczonych elementów w raportach z danymi źródłowymi OLAP.
Wyniki pośrednie W raportach z danymi źródłowymi OLAP nie można zmienić ostatecznej funkcji dla wyników pośrednich. Dzięki innym typom danych źródłowych można zmienić funkcje końcowe dla wyników pośrednich i wyświetlania lub ukrywania wyników pośrednich dla wszystkich pól wierszy i kolumn.
W przypadku danych źródłowych OLAP można włączyć lub wykluczyć ukryte przedmioty przy obliczaniu wyników średnio- i ogólnych. W przypadku innych typów danych źródłowych można dołączyć ukryte elementy pól filtrowanych raportów do wyników pośrednich, ale ukryte elementy w innych dziedzinach zostaną domyślnie wykluczone.
Praca z kostką OLAP w MS Excel
1. Uzyskaj pozwolenie na dostęp do OLAP CUBA SQL Server Analysis Services (SSAS)
2. Na komputerze MS Excel 2016/2013/2010 musi być zainstalowany (możesz i MS Excel 2007, ale nie jest to wygodne do pracy w nim i całkowicie słabej funkcjonalności MS Excel 2003)
3. Otwórz MS Excel, uruchom kreatora konfiguracji połączenia z usługą analityczną:
3.1 Podaj nazwę lub adres IP serwer działający OLAP (czasami chcesz określić numer otwarty port., na przykład 192.25.25.102:80); Uwierzytelnianie domeny jest używane:
3.2 Wybierz wielowymiarową bazę danych i sześcian analityczny (w przypadku dostępu do Kuby):
3.3 Ustawienia połączenia z usługą analityczną zostaną zapisane w pliku ODC na komputerze:
3.4 Wybierz typ raportu (tabela podsumowanie / wykres) i wskazuj miejsce, aby go pomieścić:
Jeśli połączenie zostało już utworzone w książce Excel, możesz użyć ponownie: Menu główne "Data" -\u003e "Istniejące połączenia" -\u003e Wybierz połączenie w tej książce -\u003e Włóż skonsolidowaną tabelę do określonej komórki.
4. Pomyślnie podłączony do Kuby, możesz przejść do interaktywnej analizy danych:
Rozpoczęcie interaktywnej analizy danych, konieczne jest określenie, które z pól będzie zaangażowany w tworzenie wierszy, kolumn i filtrów (stron) skonsolidowanej tabeli. W ogólnym przypadku tabela podsumowująca jest trójwymiarowa, a można założyć, że trzeci wymiar jest prostopadłym do ekranu, i obserwujemy sekcje, płaszczyznę równoległej ekranu i zdefiniowane przez "stronę" jest wybrany do wyświetlania . Filtrowanie można wykonać, przeciągając odpowiednie atrybuty pomiarowe do obszaru filtrów raportu. Filtracja ogranicza przestrzeń sześcianu, zmniejszając ładunek na serwerze OLAP, tak najlepiej zainstalować niezbędne filtry. Następnie umieść atrybuty pomiarowe w obszarach rzędów, kolumnach i wskaźników do obszaru danych skonsolidowanej tabeli.
Za każdym razem zmienia się tabelę podsumowującą, instrukcja MDX jest automatycznie wysyłana na serwer OLAP, który jest zwracany do danych. Im większa i bardziej złożona ilość przetwarzanych danych, obliczonych wskaźników, tym dłuższy czas wykonania czasu. Możesz anulować wykonanie zapytania, naciskając klawisz Ucieczka.. Ostatnia operacja przeprowadzona może zostać anulowana (Ctrl + Z) lub powrót (Ctrl + Y).
Z reguły, dla najczęściej stosowanych kombinacji atrybutów pomiarowych na Kubie, przechowywane są wstępnie obliczone dane zagregowane, więc przechowywane jest czas odpowiedzi takich żądań przez kilka sekund. Jednak niemożliwe jest obliczenie całej możliwej kombinacji agregacji, ponieważ może to zająć dużo czasu i miejsca do przechowywania. Aby wykonać ogromne prośby o dane na poziomie szczegółowości, mogą być wymagane znaczące zasoby obliczeniowe serwera, więc czas ich wykonania może być długi. Po przeczytaniu danych z dysków dysk serwer umieszcza je w pamięci podręcznej losowy pamięć dostępuTo pozwala na natychmiastowe śledzenie tych żądań, ponieważ dane zostaną wykonane już z pamięci podręcznej.
Jeśli uważasz, że Twoja prośba będzie często używana, a czas jej wykonania jest niezadowalający, możesz skontaktować się z usługą rozwój analitycznych, aby zoptymalizować wykonanie zapytania.
Po umieszczeniu hierarchii w obszarze wierszy / kolumn możliwe jest ukrywanie indywidualnych poziomów:
Kluczowe cechy (rzadziej - dla atrybutów powyżej hierarchii) pomiarów może być właściwościami - charakterystyką opisową, które mogą być wyświetlane zarówno w wyskakujących wskazówkach, jak iw postaci pól:
Jeśli chcesz wyświetlić kilka właściwości pola jednocześnie, możesz użyć odpowiedniego listy okna dialogowych:
Zestawy zdefiniowane przez użytkownika
W programie Excel 2010 możliwe było interaktywność tworzenia własnych zestawów (zdefiniowanych przez użytkownika) z elementów pomiarowych:
W przeciwieństwie do zestawów utworzonych i przechowywanych centralnie po stronie sześcianu, niestandardowe zestawy są zapisywane lokalnie w książce Excel i mogą być używane poniżej:
Zaawansowani użytkownicy mogą tworzyć zestawy za pomocą projektów MDX:
Ustawianie właściwości tabeli obrotu
Za pomocą "Skonsolidowanych ustawień tabeli ..." menu kontekstowego (kliknienie prawym przyciskiem myszy tabeli podsumowania), możliwe jest skonfigurowanie skonsolidowanego tabeli, na przykład:
- Karta wniosku, parametr "Klasyczny klasyczny układ tabeli" - Skonsolidowany tabelę staje się interaktywny, możesz przeciągnąć pola (przeciągnij i upuść);
- Zakładka "Wniosek", "Pokaż elementy bez danych w strunach" Parametr - Puste ciągi, które nie zawierają żadnych wartości wskaźnika na odpowiednich elementach pomiarowych będą wyświetlane w tabeli obrotów;
- Zakładka "Znakowanie i format", "Zapisz format formatu komórki podczas aktualizacji" - możesz zastąpić i zapisać format komórki podczas aktualizacji danych;
Tworzenie skonsolidowanych diagramów.
W przypadku istniejącego podsumowania tabeli OLAP można utworzyć diagram podsumowujący - okrągły, linię, histogram, harmonogram, punkt i inne rodzaje diagramów:
W tym przypadku wykres podsumowujący zostanie zsynchronizowany ze skonsolidowaną tabelą - podczas wymiany składu wskaźników, filtrów, pomiarów w tabeli podsumowania jest również aktualizowany stół podsumowujący.
Tworzenie paneli informacyjnych.
Podświetlamy źródło skonsolidowany tabelę, skopiuj go do schowka (Ctrl + C) i włóż go za pomocą kopii (Ctrl + V), w którym skład się skład wskaźników:
Aby jednocześnie zarządzać wkładaniem wielu skonsolidowanych tabel (nowe funkcjonalność, dostępne, począwszy od MS Excel 2010). Podłącz nasz koralnik do skonsolidowanych tabel - kliknij prawym przyciskiem myszy nacięcie, wybierz W menu kontekstowe Połączenia "Połączenia z skonsolidowaną tabelą ...". Należy zauważyć, że istnieje kilka paneli sekcji, które mogą służyć jednocześnie skonsolidowanych tabel na różnych arkuszach, co pozwala tworzyć skoordynowane panele informacyjne (pulpit nawigacyjny).
Panele cięte można skonfigurować: musisz podświetlić panel, a następnie zobacz "Rozmiar i właściwości ...", "Ustawienia obwodu", "Przypisz makro" w menu kontekstowym aktywowanym przez kliknięcie prawym przyciskiem myszy lub "Parametry" głównego menu. Możliwe jest ustanowienie bluzy kolumn dla elementów (przycisków) cięcia, rozmiar przycisków ciętych i przycisków panelu, określają gamę kolorów i projektowanie projektu z dostępnego pokrętła (lub stwórz własny styl ), Zdefiniuj pozycję panelu, przypisz makro oprogramowania, dzięki którym można rozwinąć funkcjonalny panel.
Wykonanie Excel Query MDX
- Przede wszystkim musisz wykonać działanie wiertniczej na niektórych wskaźnikach, tj. Zejdź do szczegółowych danych (szczegółowe dane są wyświetlane na oddzielnym 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 oraz w polu tekstowym, dodaj wstępnie przygotowany MDX. zapytanie ofertowe;
- Po naciśnięciu przycisku po sprawdzeniu poprawności składni zapytania i dostępność odpowiednich praw dostępu, żądanie zostanie włączone na serwerze, a wynik zostanie przedstawiony w bieżącym arkuszu jako konwencjonalny stół płaski.
Wyświetl tekst zapytania MDX Wygenerowany przez Excel jest możliwy, 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 rosyjski i język angielskioraz (jeśli to konieczne, możliwe jest lokalizacja na inne języki). Transfery są dystrybuowane do nazw pomiarów, hierarchii, atrybutów, folderów, środków, a także elementów indywidualnych hierarchii w przypadku, gdy mają tłumaczenia z boku systemów księgowych / magazynów danych. Aby zmienić język, należy otworzyć właściwości połączenia i w wierszu połączenia Dodaj następującą opcję:
Właściwości rozszerzone \u003d "Locale \u003d 1033"
Gdzie 1033 - Lokalizacja na angielski
1049 - Lokalizacja na rosyjski
Dodatkowe rozszerzenia Excel dla Microsoft OLAP
Microsoft Olap Cubes wzrośnie, jeśli używasz dodatkowych rozszerzeń, takich jak rozszerzenia OLAP Pivottable, dzięki którym można użyć szybkie wyszukiwanie Według pomiaru:
