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 konfigurowane są następujące ustawienia: 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 pomiary będą następujące: „Region”, „Miasto”, „Kupujący”, „Towar”, „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”, 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 sprzedaż według miasta regionu Ural, po zapoznaniu się z danymi dotyczącymi Jekaterynburga będziemy mogli przeglądać dane dotyczące hurtowych nabywców 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.
Pierwszy interfejs tabeli przestawnej, zwany także raportami przestawnymi, został zawarty w programie Excel w 1993 r. (Wersja 5.0 programu Excel). Pomimo wielu przydatnych funkcji, prawie nigdy nie jest używana przez większość użytkowników Excela. Nawet doświadczeni użytkownicy często rozumieją termin „raport podsumowujący” coś zbudowanego przy użyciu złożonych formuł. Spróbujmy spopularyzować stosowanie tabel przestawnych w codziennej pracy ekonomistów. W artykule omówiono teoretyczne podstawy tworzenia raportów podsumowujących, podano praktyczne zalecenia dotyczące ich wykorzystania, a także podano przykład dostępu do danych na podstawie kilku tabel.
Warunki wielowymiarowej analizy danych
Większość ekonomistów słyszała pojęcia „dane wielowymiarowe”, „wirtualna kostka”, „technologie OLAP” itp. Ale przy szczegółowej rozmowie zwykle okazuje się, że prawie wszyscy tak naprawdę nie rozumieją, o czym mówią. Oznacza to, że ludzie mają na myśli coś złożonego i zwykle niezwiązanego z ich codziennymi czynnościami. W rzeczywistości tak nie jest.
Dane wielowymiarowe, pomiary
Można śmiało powiedzieć, że ekonomiści niemal stale mają do czynienia z wielowymiarowymi danymi, ale starają się prezentować je w z góry określonej formie za pomocą arkuszy kalkulacyjnych. Przez wielowymiarowość rozumie się możliwość wprowadzania, przeglądania lub analizowania tych samych informacji ze zmianą wyglądu, przy użyciu różnych grup i sortowania danych. Na przykład plan sprzedaży można analizować według następujących kryteriów:
- rodzaje lub grupy towarów;
- marki lub kategorie produktów;
- okresy (miesiąc, kwartał, rok);
- nabywcy lub grupy nabywców;
- regiony sprzedaży
- itd.
Każde z tych kryteriów w zakresie wielowymiarowej analizy danych nazywane jest „pomiarem”. Można powiedzieć, że pomiar charakteryzuje informacje dla określonego zestawu wartości. Specjalnym rodzajem pomiaru informacji wielowymiarowych są „dane”. W naszym przykładzie dane planu sprzedaży mogą być:
- wielkość sprzedaży;
- cena sprzedaży;
- indywidualna zniżka
- itd.
Teoretycznie dane mogą być również standardowym pomiarem informacji wielowymiarowych (na przykład można pogrupować dane według ceny sprzedaży), ale zwykle mimo wszystko dane są szczególnym rodzajem wartości.
Możemy zatem powiedzieć, że w praktyce ekonomiści używają dwóch rodzajów informacji: dane wielowymiarowe (rzeczywiste i planowane liczby z wieloma atrybutami) i odniesienia (charakterystyka lub pomiary danych).
OLAP
Skrót OLAP (przetwarzanie analityczne online) w dosłownym tłumaczeniu brzmi jak „przetwarzanie analityczne w czasie rzeczywistym”. Definicja nie jest bardzo szczegółowa; można w niej streścić prawie każdy raport dowolnego oprogramowania. Pod względem znaczenia OLAP oznacza technologię pracy ze specjalnymi raportami, w tym oprogramowaniem, do uzyskiwania i analizowania tylko wielowymiarowych danych strukturalnych. Jednym z popularnych programów, które implementują technologię OLAP, jest SQL Server Analysis Server. Niektórzy nawet błędnie uważają go za jedynego przedstawiciela implementacji programowej tej koncepcji.
Wirtualna kostka danych
„Wirtualna kostka” (kostka wielowymiarowa, kostka OLAP) to specjalny termin proponowany przez niektórych wyspecjalizowanych dostawców oprogramowania. Systemy OLAP zwykle przygotowują i przechowują dane we własnych strukturach, a specjalne interfejsy analityczne (na przykład raporty podsumowujące Excel) uzyskują dostęp do danych tych wirtualnych kostek. Ponadto wykorzystanie takiego dedykowanego miejsca do przechowywania nie jest konieczne do przetwarzania informacji wielowymiarowych. W ogólnym przypadku wirtualna kostka - Jest to tablica specjalnie zoptymalizowanych danych wielowymiarowych, które są używane do tworzenia raportów podsumowujących. Można go uzyskać zarówno przez wyspecjalizowane narzędzia programowe, jak i poprzez prosty dostęp do tabel bazy danych lub dowolnego innego źródła, na przykład do tabeli Excel.
Tabela podsumowująca
Tabela przestawna (tabela przestawna) to interfejs użytkownika do wyświetlania danych wielowymiarowych. Za pomocą tego interfejsu można grupować, sortować, filtrować i zmieniać lokalizację danych w celu uzyskania różnych próbek analitycznych. Raport jest aktualizowany za pomocą prostego interfejsu użytkownika, dane są automatycznie agregowane zgodnie z określonymi zasadami, a dodatkowe lub powtarzane wprowadzanie jakichkolwiek informacji nie jest wymagane. Interfejs tabeli przestawnej programu Excel jest prawdopodobnie najpopularniejszym wielowymiarowym produktem danych. Obsługuje zarówno zewnętrzne źródła danych (kostki OLAP i relacyjne bazy danych), jak i wewnętrzne zakresy arkuszy kalkulacyjnych jako źródło danych. Począwszy od wersji 2000 (9.0), Excel obsługuje również formę graficzną do wyświetlania danych wielowymiarowych - wykres przestawny.
Interfejs tabeli przestawnej zaimplementowany w programie Excel pozwala umieszczać wielowymiarowe wymiary danych w obszarze arkusza roboczego. Dla uproszczenia można sobie wyobrazić tabelę przestawną jako raport leżący na szczycie zakresu komórek (w rzeczywistości istnieje pewne powiązanie formatów komórek z polami tabeli przestawnej). Tabela przestawna programu Excel ma cztery obszary wyświetlania: filtr, kolumny, wiersze i dane. Wymiary danych są nazywane pola tabeli przestawnej. Te pola mają własne właściwości i format wyświetlania.
Jeszcze raz chcę zwrócić uwagę na fakt, że tabela przestawna programu Excel jest przeznaczona wyłącznie do analizy danych bez możliwości edytowania informacji. Bliższe znaczenie miałoby powszechne użycie terminu „raport podsumowujący” (Raport przestawny) i tak właśnie nazywano ten interfejs do 2000 roku. Ale z jakiegoś powodu twórcy porzucili to w kolejnych wersjach.
Edycja tabel przestawnych
Z definicji technologia OLAP w zasadzie nie implikuje możliwości zmiany danych źródłowych podczas pracy z raportami. Niemniej jednak na rynku powstała cała klasa systemów oprogramowania, które wdrażają zarówno analizę, jak i bezpośrednią edycję danych w tabelach wielowymiarowych. Zasadniczo takie systemy koncentrują się na zadaniach budżetowych.
Korzystając z wbudowanych narzędzi automatyzacji Excel, możesz rozwiązać wiele niestandardowych zadań. Przykład edycji tabel przestawnych Excela na podstawie danych arkusza roboczego można znaleźć na naszej stronie internetowej.
Wielowymiarowe przygotowanie danych
Podejdźmy do praktycznego zastosowania tabel przestawnych. Spróbujmy przeanalizować dane dotyczące sprzedaży w różnych kierunkach. Plik pivottableexample.xls składa się z kilku arkuszy. Liść Przykładzawiera podstawowe informacje o sprzedaży za określony okres. Dla uproszczenia przeanalizujemy jedyny wskaźnik liczbowy - sprzedaż w kg. Dostępne są następujące kluczowe wymiary danych: produkty, nabywca i przewoźnik (firma transportowa). Ponadto istnieje kilka dodatkowych wymiarów danych, które wskazują na produkt: typ, marka, kategoria, dostawca, a także nabywca: typ. Dane te są gromadzone w arkuszu referencyjnym. W praktyce takie pomiary mogą być znacznie większe.
Liść Przykładzawiera standardowe narzędzie do analizy danych - autofiltr. Patrząc na przykład wypełniania tabeli, oczywiste jest, że dane dotyczące sprzedaży według dat (ułożone w kolumnach) poddają się normalnej analizie. Ponadto, korzystając z autofiltru, możesz spróbować podsumować dane za pomocą kombinacji jednego lub więcej kluczowych kryteriów. Nie ma absolutnie żadnych informacji na temat marek, kategorii i typów. Nie ma możliwości grupowania danych z automatycznym sumowaniem według określonego klucza (na przykład według klientów). Ponadto zestaw dat jest stały i nie można wyświetlić informacji podsumowujących dla określonego okresu, na przykład 3 dni, automatycznie.
Ogólnie rzecz biorąc, obecność predefiniowanej lokalizacji daty w tym przykładzie jest główną wadą tabeli. Układając daty w kolumnach, niejako z góry ustaliliśmy wymiary tej tabeli, pozbawiając się w ten sposób możliwości korzystania z analizy przy użyciu tabel przestawnych.
Po pierwsze, musimy pozbyć się tej wady - tj. usuń predefiniowaną lokalizację jednego z wymiarów danych źródłowych. Przykład prawidłowej tabeli - arkusz Sprzedaż.
Tabela ma postać dziennika wprowadzania informacji. Tutaj data jest równym pomiarem danych. Należy również zauważyć, że do późniejszej analizy w tabelach przestawnych względne położenie wierszy względem siebie (innymi słowy sortowanie) jest całkowicie obojętne. Te właściwości mają rekordy w relacyjnych bazach danych. Analiza dużych ilości baz danych koncentruje się przede wszystkim na interfejsie tabel przestawnych. Dlatego konieczne jest przestrzeganie tych zasad podczas pracy ze źródłem danych w postaci zakresów komórek. Jednocześnie nikt nie zabrania używania narzędzi interfejsu Excel w pracy - tabele przestawne analizują tylko dane, a formatowanie, filtry, grupowanie i sortowanie komórek źródłowych mogą być dowolne.
Od autofiltru do raportu podsumowującego
Teoretycznie na danych z arkusza sprzedaży można już przeprowadzić analizę w trzech wymiarach: towary, klienci i przewoźnicy. Dane dotyczące właściwości produktów i klientów nie są dostępne w tym arkuszu, co w związku z tym nie pozwoli na ich pokazanie w tabeli podsumowującej. W normalnym trybie tworzenia tabeli przestawnej dla danych źródłowych program Excel nie pozwala na połączenie danych z kilku tabel w niektórych polach. Możesz obejść to ograniczenie za pomocą oprogramowania - zobacz przykładowy dodatek do tego artykułu na naszej stronie internetowej. Aby nie korzystać z programowych metod przetwarzania informacji (zwłaszcza, że \u200b\u200bnie są one uniwersalne), należy dodać dodatkowe cechy bezpośrednio do formularza zapisu do dziennika - patrz arkusz SalesAnalysis.
Korzystanie z funkcji WYSZUKAJ.PIONOWO ułatwia uzupełnienie danych źródłowych o brakujące cechy. Teraz za pomocą autofiltru możesz analizować dane w różnych wymiarach. Ale problem grupowania pozostaje nierozwiązany. Na przykład śledzenie kwoty tylko według marki dla określonych dat jest dość problematyczne. Jeśli jesteś ograniczony do formuł Excela, musisz zbudować dodatkowe próbki za pomocą funkcji SUMIF.
Zobaczmy teraz, jakie funkcje zapewnia interfejs tabeli przestawnej. Na kartce Analiza koduzbudował kilka raportów w oparciu o zakres komórek z danymi arkusza Analiza sprzedaży.
Pierwsza tabela analityczna jest budowana za pomocą interfejsu Excel 2007 Tape \\ Insert \\ Pivot Table (w menu Excel 2000-2003 Dane \\ Tabela przestawna).
Druga i trzecia tabela są tworzone przez kopiowanie i późniejsze dostosowywanie. Źródło danych dla wszystkich tabel jest takie samo. Możesz to sprawdzić, zmieniając dane źródłowe, a następnie musisz zaktualizować dane raportów podsumowujących.
Z naszego punktu widzenia zalety widoczności informacji są oczywiste. Możesz zamieniać filtry, kolumny i wiersze oraz ukrywać określone grupy wartości o dowolnych wymiarach, stosować ręczne przeciąganie i upuszczanie oraz automatyczne sortowanie.
Właściwości i formatowanie
Oprócz bezpośredniego wyświetlania danych istnieje duży zestaw opcji do wyświetlania wyglądu tabel przestawnych. Nadmiar danych można ukryć za pomocą filtrów. W przypadku pojedynczego elementu lub pola łatwiej jest użyć elementu menu kontekstowego Usuń(w wersji 2000-2003 Ukryj).
Pożądane jest również określenie wyświetlania innych elementów tabeli przestawnej nie poprzez formatowanie komórki, ale poprzez ustawienie pola lub elementu tabeli przestawnej. Aby to zrobić, przesuń wskaźnik myszy na żądany element, poczekaj na pojawienie się specjalnego kształtu kursora (w postaci strzałki), a następnie wybierz element jednym kliknięciem. Po zaznaczeniu możesz zmienić widok za pomocą wstążki, menu kontekstowego lub wywołać standardowe okno dialogowe formatu komórki:
Ponadto program Excel 2007 wprowadził wiele predefiniowanych stylów wyświetlania tabeli przestawnej:
Zauważ, że filtry kontrolne i obszary przeciągania są aktywne na wykresie.
Dostęp do danych zewnętrznych
Jak już wspomniano, być może największy efekt użycia tabel przestawnych można uzyskać, uzyskując dostęp do danych ze źródeł zewnętrznych - kostek OLAP i zapytań do bazy danych. Takie źródła zwykle przechowują duże ilości informacji, a także mają predefiniowaną strukturę relacyjną, co ułatwia określenie wymiarów danych wielowymiarowych (pól w tabeli przestawnej).
Excel obsługuje wiele rodzajów zewnętrznych źródeł danych:
Największy efekt wykorzystania zewnętrznych źródeł informacji można osiągnąć, stosując narzędzia automatyzacji (programy VBA) zarówno do uzyskiwania danych, jak i ich wstępnego przetwarzania w tabelach podsumowujących.
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 wykresu przestawnego można łatwo wyświetlać dane podsumowania 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 za pomocą 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 oraz 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 wysłać odpowiednie ikony, podobne do nowej ikony Excela, tak aby ustawiły się w linii powyżej lub poniżej poziomów statusu (na przykład ikony zatrzymania), a także przewijać w górę lub w dół (na przykład ikonę 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 wersja 7.0, Microsoft SQL Server olap wersja 2000 (8.0) i Microsoft SQL Server Analysis Services wersja 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. Na komputerze musi być zainstalowany MS Excel 2016/2013/2010 (możesz także korzystać z MS Excel 2007, ale nie jest to wygodne w nim pracować, a bardzo słaba funkcjonalność MS Excel 2003)
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 również zostanie zaktualizowana.
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:
