Лабораторна версия на Excel olap cubes. OLAP-CUBE (динамично отчитане на управлението)

Като цяло всеки специалист знае какво е OLAP днес. Поне понятията „OLAP“ и „многоизмерни данни“ са здраво свързани в съзнанието ни. Независимо от това, фактът, че тази тема се повдига отново, надявам се, ще бъде одобрен от мнозинството читатели, защото за да не остарее идеята за нещо с времето, трябва периодично да общувате с умни хора или прочетете статии в добра публикация ...

Складове за данни (място на OLAP в информационната структура на предприятието)

Терминът "OLAP" е неразривно свързан с термина "склад за данни" (Data Warehouse).

Ето дефиницията, формулирана от „бащата-основател“ на складирането на данни, Бил Инмон: „Складът на данни е специфичен за домейн, ограничен във времето, неизменен сбор от данни в подкрепа на вземането на управленски решения.“

Данните влизат в хранилището от операционни системи(OLTP системи), които са предназначени за автоматизиране на бизнес процеси. В допълнение, хранилището може да се попълва от външни източници, като например статистически отчети.

Защо да изграждаме складове за данни - в крайна сметка те съдържат очевидно излишна информация, която вече „живее“ в бази данни или файлове на операционната система? Отговорът може да бъде кратък: невъзможно е или много трудно да се анализират директно данни от операционни системи. Това се дължи на различни причини, включително фрагментацията на данните, тяхното съхранение в различни СУБД формати и в различни „ъгли“ корпоративна мрежа. Но дори ако едно предприятие съхранява всичките си данни на централен сървър на база данни (което е изключително рядко), анализаторът почти сигурно няма да разбере техните сложни, понякога объркващи структури. Авторът има доста тъжен опит да се опитва да „нахрани“ гладни анализатори със „сурови“ данни от операционни системи - това се оказа „твърде много за тях“.

По този начин целта на хранилището е да предостави „суровините“ за анализ на едно място и в проста, разбираема структура. Ралф Кимбъл, в предговора към книгата си "The Data Warehouse Toolkit", пише, че ако след като прочете цялата книга, читателят разбере само едно нещо - а именно, че структурата на склада трябва да бъде проста - авторът ще вземе предвид задачата е изпълнена.

Има още една причина, която оправдава появата на отделно хранилище - сложните аналитични заявки за оперативна информация забавят текущата работа на компанията, блокират таблици за дълго време и изземват сървърни ресурси.

Според мен хранилището не означава непременно гигантско натрупване на данни - основното е да е удобно за анализ. Най-общо казано, има отделен термин за малки съоръжения за съхранение - Data Marts (киоски за данни), но в нашата руска практика не го чувате често.

OLAP - удобен инструмент за анализ

Централизацията и удобното структуриране не са всичко, от което се нуждае един анализатор. Той все още се нуждае от инструмент за разглеждане и визуализиране на информация. Традиционните отчети, дори тези, изградени върху едно хранилище, нямат едно нещо - гъвкавост. Те не могат да бъдат "усукани", "разширени" или "свити", за да се получи желаният изглед на данните. Разбира се, можете да се обадите на програмист (ако иска да дойде) и той (ако не е зает) ще направи нов отчет достатъчно бързо - да речем в рамките на час (пиша това и не вярвам сам - това не става толкова бързо в живота, нека му дадем три часа) . Оказва се, че един анализатор може да тества не повече от две идеи на ден. И на него (ако той добър анализатор) няколко такива идеи могат да ви хрумнат на час. И колкото повече „срезове“ и „секции“ от данни вижда анализаторът, толкова повече идеи има, които от своя страна изискват все повече и повече „срезове“ за проверка. Само ако имаше инструмент, който би му позволил да разширява и свива данни просто и удобно! OLAP действа като такъв инструмент.

Въпреки че OLAP не е необходим атрибут на хранилище за данни, той все повече се използва за анализ на информацията, натрупана в склада.

Компонентите, включени в типично хранилище, са показани на фиг. 1.

ориз. 1. Структура на хранилището на данни

Оперативните данни се събират от различни източници, почистват се, интегрират се и се съхраняват в релационно хранилище. Нещо повече, те вече са достъпни за анализ различни средствастроителни отчети. След това данните (изцяло или частично) се подготвят за OLAP анализ. Те могат да бъдат заредени в специална OLAP база данни или съхранени в релационно хранилище. Неговият най-важен елемент са метаданните, т.е. информацията за структурата, разположението и трансформацията на данните. Благодарение на тях се осигурява ефективно взаимодействие на различни компоненти за съхранение.

За да обобщим, можем да дефинираме OLAP като набор от инструменти за многомерен анализ на данни, натрупани в склад. Теоретично OLAP инструментите могат да се прилагат директно към оперативни данни или към тях точни копия(за да не пречат на оперативните потребители). Но по този начин рискуваме да стъпим на вече описаното по-горе, т.е. да започнем да анализираме оперативни данни, които не са директно подходящи за анализ.

Дефиниция и основни понятия на OLAP

Първо, нека дешифрираме: OLAP е онлайн аналитична обработка, т.е. оперативен анализ на данни. 12-те дефиниращи принципа на OLAP са формулирани през 1993 г. от E. F. Codd, „изобретателят“ на релационните бази данни. По-късно неговата дефиниция беше преработена в така наречения FASMI тест, който изисква OLAP приложението да предоставя възможност за бърз анализ на споделена многоизмерна информация ().

FASMI тест

бързо(Бърз) - анализът трябва да се извършва еднакво бързо по всички аспекти на информацията. Приемливото време за отговор е 5 секунди или по-малко.

Анализ(Анализ) - трябва да е възможно да се извършват основни видове числени и статистически анализи, предварително дефинирани от разработчика на приложението или свободно дефинирани от потребителя.

Споделено(Споделено) - много потребители трябва да имат достъп до данни, докато е необходимо да се контролира достъпът до поверителна информация.

Многоизмерен(Multidimensional) е основната, най-съществена характеристика на OLAP.

Информация(Информация) - приложението трябва да има достъп до всякакви необходимата информация, независимо от неговия обем и място на съхранение.

OLAP = Многоизмерен изглед = Куб

OLAP предоставя удобни и бързи средства за достъп, преглед и анализ на бизнес информация. Потребителят получава естествен, интуитивен модел на данни, организирайки ги под формата на многомерни кубове (Cubes). Осите на многомерната координатна система са основните атрибути на анализирания бизнес процес. Например, за продажби може да бъде продукт, регион, тип купувач. Като едно от измеренията се използва времето. В пресечните точки на осите - измерения (Dimensions) - има данни, които количествено характеризират процеса - мерки (Measures). Това могат да бъдат обеми на продажби в бройки или в парично изражение, салда на запаси, разходи и т.н. Потребителят, анализиращ информацията, може да „нареже“ куба в различни посоки, да получи обобщение (например по година) или, обратно, подробно ( по седмица ) информация и извършва други манипулации, които му хрумнат по време на процеса на анализ.

Като мерки в триизмерния куб, показан на фиг. 2 се използват суми на продажбите, а като измерения се използват време, продукт и магазин. Измерванията са представени на специфични нива на групиране: продуктите са групирани по категория, магазините по държава, а данните за времето на транзакция по месец. Малко по-късно ще разгледаме по-подробно нивата на групиране (йерархия).

ориз. 2. Пример за куб

"Нарязване" на кубче

Дори триизмерен куб е трудно да се покаже на компютърен екран, така че стойностите на интересуващите ни мерки да са видими. Какво можем да кажем за кубовете с повече от три измерения? За визуализиране на данни, съхранявани в куб, като правило се използват познати двуизмерни, т.е. таблични изгледи със сложни йерархични заглавки на редове и колони.

Двуизмерно представяне на куб може да се получи чрез „разрязване“ на една или повече оси (измерения): фиксираме стойностите на всички измерения с изключение на две и получаваме обикновена двуизмерна таблица. Хоризонталната ос на таблицата (заглавки на колони) представлява едно измерение, вертикалната ос (заглавки на редове) представлява друго, а клетките на таблицата представляват стойностите на мерките. В този случай набор от мерки всъщност се счита за едно от измеренията - ние или избираме една мярка за показване (и след това можем да поставим две измерения в заглавията на реда и колоната), или показваме няколко измервания (и след това една от осите на таблицата ще бъдат заети от имената на мерките, а другата - стойностите на единственото „неизрязано“ измерение).

Разгледайте фиг. 3 - тук е двуизмерен срез на куба за една мярка - Unit Sales (продадени бройки) и две "неразрязани" измерения - Store (Магазин) и Време (Time).

ориз. 3. 2D кубичен срез за една мярка

На фиг. Фигура 4 показва само едно „неразрязано“ измерение - Store, но показва стойностите на няколко мерки - Unit Sales (продадени единици), Store Sales (разход на продажба) и Store Cost (разходи в магазина).

ориз. 4. 2D кубичен срез за множество мерки

Двуизмерно представяне на куб също е възможно, когато повече от две измерения остават „неизрязани“. В този случай две или повече измерения на „изрязания“ куб ще бъдат поставени върху осите на среза (редове и колони) - вижте фиг. 5.

ориз. 5. 2D кубичен срез с множество измерения на една ос

Етикети

Стойностите, "положени" по размери, се наричат членове или етикети. Етикетите се използват както за „изрязване“ на куба, така и за ограничаване (филтриране) на избраните данни – когато в измерение, което остава „неизрязано“, ние не се интересуваме от всички стойности, а от подмножество от тях, например три градове от няколко десетки. Стойностите на етикетите се появяват в изгледа на 2D куб като заглавия на редове и колони.

Йерархии и нива

Етикетите могат да се комбинират в йерархии, състоящи се от едно или повече нива. Например, етикетите на измерението Store са естествено групирани в йерархия с нива:

държава

състояние

град

Магазин.

Агрегираните стойности се изчисляват според йерархичните нива, например обем на продажбите за САЩ (ниво „Държава“) или за Калифорния (ниво „Щат“). Възможно е да се приложат повече от една йерархия в едно измерение - да речем за време: (година, тримесечие, месец, ден) и (година, седмица, ден).

Архитектура на OLAP приложения

Всичко, което беше казано по-горе за OLAP, по същество се отнася до многомерното представяне на данни. Как се съхраняват данните, грубо казано, не засяга нито крайния потребител, нито разработчиците на инструмента, който клиентът използва.

Многоизмерността в OLAP приложенията може да бъде разделена на три нива:

Многомерно представяне на данни - инструменти за краен потребител, които осигуряват многомерна визуализация и манипулиране на данни; Слоят на многомерното представяне се абстрахира от физическата структура на данните и ги третира като многоизмерни.
Многомерна обработка - инструмент (език) за формулиране на многомерни заявки (традиционни релационни SQL езиксе оказва неподходящ тук) и процесор, способен да обработи и изпълни такава заявка.
Многомерното съхранение е средство за физическо организиране на данни, което гарантира ефективно изпълнение на многомерни заявки.

Първите две нива са задължителни във всички OLAP инструменти. Третото ниво, макар и широко разпространено, не е необходимо, тъй като данните за многомерно представяне могат да бъдат извлечени от обикновени релационни структури; Процесорът на многомерни заявки в този случай превежда многомерните заявки в SQL заявки, които се изпълняват от релационна СУБД.

Конкретни OLAP продукти, като правило, са или многоизмерен инструмент за представяне на данни, OLAP клиент (например Pivot Tables в Excel 2000 от Microsoft или ProClarity от Knosys), или многоизмерен сървър DBMS, OLAP сървър (например Oracle Express Server или Microsoft OLAP услуги).

Многоизмерният слой за обработка обикновено е вграден в OLAP клиента и/или OLAP сървъра, но може да бъде разделен на чиста форма, като например компонента Pivot Table Service на Microsoft.

Технически аспекти на многомерното съхранение на данни

Както бе споменато по-горе, инструментите за OLAP анализ също могат да извличат данни директно от релационни системи. Този подход беше по-привлекателен в онези дни, когато OLAP сървърите не бяха включени в ценовите листи на водещите производители на СУБД. Но днес Oracle, Informix и Microsoft предлагат пълноценни OLAP сървъри и дори онези ИТ мениджъри, които не обичат да създават „зоопарк“ от софтуер в своите мрежи различни производители, може да закупи (по-точно да направи съответна заявка до ръководството на компанията) OLAP сървър от същата марка като основния сървър на база данни.

OLAP сървърите или сървърите на многоизмерни бази данни могат да съхраняват своите многоизмерни данни по различни начини. Преди да разгледаме тези методи, трябва да поговорим за такъв важен аспект като съхраняването на единици. Факт е, че във всеки склад за данни - както обикновен, така и многоизмерен - заедно с подробни данни, извлечени от операционни системи, се съхраняват и обобщени показатели (обобщени показатели, агрегати), като сумата от обемите на продажбите по месеци, по категория стоки и т.н. . Агрегатите се съхраняват изрично с единствената цел да се ускори изпълнението на заявките. В крайна сметка, от една страна, като правило, в склада се натрупва много голямо количество данни, а от друга страна, анализаторите в повечето случаи се интересуват не от подробни, а от обобщени показатели. И ако всеки път трябваше да се сумират милиони отделни продажби, за да се изчислят общите продажби за годината, скоростта най-вероятно би била неприемлива. Следователно, при зареждане на данни в многомерна база данни, всички общи показатели или част от тях се изчисляват и съхраняват.

Но, както знаете, трябва да платите за всичко. А за скоростта на обработка на заявките за обобщени данни трябва да платите за увеличаване на обема на данните и времето за зареждането им. Освен това увеличаването на обема може да бъде буквално катастрофално - в един от публикуваните стандартни тестове пълното изчисляване на агрегати за 10 MB изходни данни изисква 2,4 GB, т.е. данните нарастват 240 пъти! Степента на „набъбване“ на данните при изчисляване на агрегатите зависи от броя на измеренията на куба и структурата на тези измерения, т.е. съотношението на броя на „бащите“ и „децата“ на различни ниваизмервания. За да се реши проблемът със съхранението на единици, те понякога се използват сложни вериги, които правят възможно постигането на значително увеличение на производителността на заявките при изчисляване на не всички възможни агрегати.

Сега за различните опции за съхранение на информация. Както гранулираните данни, така и агрегатите могат да се съхраняват в релационни или многомерни структури. Многомерното съхранение ви позволява да третирате данните като многомерен масив, което гарантира еднакво бързи изчисления на общите показатели и различни многомерни трансформации по всяко от измеренията. Преди известно време OLAP продуктите поддържаха или релационно, или многоизмерно съхранение. Днес, като правило, един и същ продукт осигурява и двата вида съхранение, както и трети тип - смесен. Важат следните условия:

МОЛАП(Multidimensional OLAP) - както подробните данни, така и агрегатите се съхраняват в многомерна база данни. В този случай се получава най-големият излишък, тъй като многомерните данни съдържат изцяло релационни данни.
ROLAP(Relational OLAP) - подробните данни остават там, където първоначално са "живели" - в релационната база данни; агрегатите се съхраняват в същата база данни в специално създадени сервизни таблици.
ХОЛАП(Hybrid OLAP) - подробните данни остават на място (в релационна база данни), а агрегатите се съхраняват в многоизмерна база данни.

Всеки от тези методи има своите предимства и недостатъци и трябва да се използва в зависимост от условията - обем на данните, мощност на релационната СУБД и др.

Когато съхранявате данни в многомерни структури, има потенциален проблем от "раздуване" поради съхраняване на празни стойности. В края на краищата, ако в многоизмерен масив пространството е запазено за всички възможни комбинации от етикети за размери, но само малка част е действително запълнена (например редица продукти се продават само в малък брой региони), тогава повечето от кубът ще бъде празен, въпреки че мястото ще бъде заето. Съвременните OLAP продукти могат да се справят с този проблем.

Следва продължение. В бъдеще ще говорим за конкретни OLAP продукти, произведени от водещи производители.

Анотация: Тази лекция обхваща основите на проектирането на кубове с данни за OLAP хранилища на данни. Примерът показва метода за конструиране на куб с данни с помощта на инструмента CASE.

Цел на лекцията

След като изучите материала в тази лекция, ще знаете:

в какво е кубът с данни OLAP хранилище за данни ;
как да проектираме куб с данни за OLAP хранилища за данни ;
какво е измерение на куб данни;
как даден факт е свързан с куб с данни;
какво представляват атрибутите на измерението;
какво е йерархия;
какво е метрика на куба данни;

и научи:

изграждане многомерни диаграми ;
дизайн прост многомерни диаграми.

Въведение

OLAP технологията не е единична програмен продукт, Не език за програмиране. Ако се опитаме да обхванем OLAP във всичките му проявления, тогава това е набор от концепции, принципи и изисквания, които са в основата софтуерни продукти, което улеснява достъпа на анализаторите до данни.

Анализаторите са основните потребители корпоративна информация. Работата на анализатора е да намира модели в големи количества данни. Затова анализаторът няма да обърне внимание на отделния факт, че в определен ден е продадена партида химикалки на купувача Иванов - той се нуждае от информация за стотици и хиляди подобни събития. Единични факти в хранилището на данни могат да представляват интерес например за счетоводител или ръководител на отдел продажби, чиято компетентност е поддръжката на определен договор. За един анализатор един запис не е достатъчен - той например може да се нуждае от информация за всички договори на точка за продажба за месец, тримесечие или година. Анализаторът може да не се интересува от TIN на купувача или неговия телефонен номер - той работи с конкретни цифрови данни, което е същността на неговата професионална дейност.

Централизацията и удобното структуриране не са всичко, от което се нуждае един анализатор. Той се нуждае от инструмент за разглеждане и визуализиране на информация. Традиционните отчети, дори тези, изградени на базата на едно хранилище на данни, обаче нямат известна гъвкавост. Те не могат да бъдат „усукани“, „разширени“ или „свити“, за да се получи желаният изглед на данните. Колкото повече „срезове“ и „секции“ от данни може да изследва анализаторът, толкова повече идеи има, които от своя страна изискват все повече и повече „срезове“ за проверка. OLAP служи като такъв инструмент за анализ на данни от анализатор.

Въпреки че OLAP не е необходим атрибут на хранилище за данни, той все повече се използва за анализ на информацията, натрупана в това хранилище за данни.

Оперативните данни се събират от различни източници, почистват се, интегрират се и се съхраняват в хранилище за данни. Освен това те вече са достъпни за анализ чрез различни инструменти за отчитане. След това данните (изцяло или частично) се подготвят за OLAP анализ. Те могат да бъдат заредени в специална OLAP база данни или оставени в релационна база данни. Най-важният елемент от използването на OLAP са метаданните, т.е. информацията за структурата, разположението и трансформация на данни. Благодарение на тях се осигурява ефективно взаимодействие на различни компоненти за съхранение.

по този начин OLAP може да се дефинира като набор от инструменти за многоизмерен анализ на данни, натрупани в хранилище за данни. Теоретично OLAP инструментите могат да се прилагат директно към оперативни данни или техни точни копия. Съществува обаче риск от подлагане на анализ на данни, които не са подходящи за този анализ.

OLAP на клиент и сървър

OLAP се основава на многомерен анализ на данни. Може да се създаде с помощта на различни инструменти, които могат да бъдат разделени на клиентски и сървърни OLAP инструменти.

OLAP клиентските инструменти са приложения, които изчисляват обобщени данни (суми, средни стойности, максимални или минимални стойности) и ги показват, докато самите обобщени данни се съдържат в кеш в адресното пространство на такъв OLAP инструмент.

Ако изходните данни се съдържат в десктоп СУБД, изчисляването на обобщените данни се извършва от самия OLAP инструмент. Ако източникът на оригиналните данни е сървърна СУБД, много от клиентските OLAP инструменти изпращат SQL заявки, съдържащи оператора GROUP BY към сървъра, и в резултат получават обобщени данни, изчислени на сървъра.

По правило OLAP функционалността се реализира в инструменти за обработка на статистически данни (от продукти от този клас до руски пазарпродуктите на Stat Soft и SPSS са широко използвани) и в някои електронни таблици. По-специално, той има добри инструменти за многоизмерен анализ Microsoft Excel 2000. Използвайки този продукт, можете да създадете и запишете като файл малък локален многоизмерен OLAP куб и да покажете неговите дву- или триизмерни секции.

много инструменти за разработкасъдържат библиотеки от класове или компоненти, които ви позволяват да създавате приложения, които реализират проста OLAP функционалност (като например компоненти Decision Cube в Borland Delphi и Borland C++Builder). Освен това много компании предлагат контроли ActiveX и други библиотеки, които реализират подобна функционалност.

Обърнете внимание, че клиентските OLAP инструменти се използват като правило с малък брой измерения (обикновено се препоръчват не повече от шест) и малко разнообразие от стойности за тези параметри - в крайна сметка получените обобщени данни трябва да се поберат в адресното пространство на такъв инструмент и техният брой нараства експоненциално с увеличаването на броя на измерванията Следователно дори най-примитивните клиентски OLAP инструменти, като правило, ви позволяват да направите предварително изчисление на необходимия обем RAM паметза да създадете многоизмерен куб в него.

Много (но не всички) OLAP клиентски инструменти ви позволяват да запазите съдържанието на кеша с обобщени данни като файл, което от своя страна ви позволява да избегнете преизчисляването им. Имайте предвид, че тази възможност често се използва за отчуждаване на обобщени данни с цел прехвърлянето им на други организации или за публикуване. Типичен пример за такива отчуждаеми обобщени данни е статистиката за заболеваемостта в различни региони и в различни възрастови групи, която е отворена информация, публикуван от министерствата на здравеопазването на различни страни и Световната здравна организация. В същото време самите оригинални данни, които са информация за конкретни случаи на заболявания, са поверителни данни лечебни заведенияи при никакви обстоятелства не трябва да попадат в ръцете на застрахователни компании, още по-малко да стават обществено достояние.

Идеята за съхраняване на кеш от сборни данни във файл беше доразвита в сървърни OLAP инструменти, в които запазването и промяната на сборни данни, както и поддържането на съхранението, което ги съдържа, се извършват от отделно приложение или процес, наречен OLAP сървър. Клиентските приложения могат да поискат такова многоизмерно съхранение и да получат определени данни в отговор. Някои клиентски приложения могат също да създават такива хранилища или да ги актуализират въз основа на променени изходни данни.

Предимствата на използването на сървърни OLAP инструменти в сравнение с клиентските OLAP инструменти са подобни на предимствата на използването на сървърни СУБД в сравнение с тези за настолни компютри: в случай на използване на сървърни инструменти, изчисляването и съхранението на обобщени данни се извършва на сървъра, а клиентското приложение получава само резултатите от заявки срещу тях, което позволява като цяло да намали мрежовия трафик, време за изпълнениезаявки и изисквания за ресурси, използвани от клиентското приложение. Обърнете внимание, че инструментите за анализ и обработка на данни в мащаб на предприятието обикновено се базират на OLAP сървърни инструменти, например Oracle Express Server, Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, продукти от Crystal Decisions, Business Objects, Cognos, SAS Institute.

Тъй като всички водещи производители на сървърни СУБД произвеждат (или са лицензирали от други компании) едни или други сървърни OLAP инструменти, изборът е доста широк и в почти всички случаи можете да закупите OLAP сървър от същия производител като самия сървър на база данни . Обърнете внимание, че много клиентски OLAP инструменти (по-специално Microsoft Excel 2003, Seagate Analysis и т.н.) ви позволяват достъп до сървърни OLAP хранилища, действащи в този случай като клиентски приложения, които изпълняват такива заявки. Освен това има много продукти, които са клиентски приложения за OLAP инструменти.

различни производители

Технически аспекти на многомерното съхранение на данни време за изпълнениеМногоизмерните хранилища за данни съдържат обобщени данни с различна степен на детайлност, например обеми на продажби по ден, месец, година, по продуктова категория и т.н. Целта на съхраняването на обобщени данни е да се намали

Имайте предвид, че запазването на всички обобщени данни не винаги е оправдано. Факт е, че когато се добавят нови измерения, обемът от данни, които съставляват куба, нараства експоненциално (понякога се говори за „експлозивен растеж“ на обема от данни). По-точно, степента на нарастване на обема на агрегираните данни зависи от броя на измеренията на куба и членовете на измеренията на различни нива на йерархиите на тези измерения. За решаване на проблема с „експлозивния растеж“ се използват различни схеми, които позволяват, когато се изчисляват не всички възможни агрегирани данни, да се постигне приемлива скорост на изпълнение на заявката.

Както необработените, така и обобщените данни могат да се съхраняват в релационни или многоизмерни структури. Следователно в момента се използват три метода за съхранение на данни.

МОЛАП(Multidimensional OLAP) - изходните и обобщените данни се съхраняват в многомерна база данни. Съхраняването на данни в многоизмерни структури ви позволява да манипулирате данните като многоизмерен масив, поради което скоростта на изчисляване на агрегатните стойности е еднаква за всяко от измеренията. В този случай обаче многоизмерната база данни е излишна, тъй като многоизмерните данни съдържат изцяло оригиналните релационни данни.
ROLAP(Relational OLAP) - оригиналните данни остават в същата релационна база данни, където са били първоначално разположени. Агрегираните данни се поставят в служебни таблици, специално създадени за съхраняването им в същата база данни.
ХОЛАП(Hybrid OLAP) - оригиналните данни остават в същата релационна база данни, където са били първоначално разположени, а обобщените данни се съхраняват в многомерна база данни.

Някои OLAP инструменти поддържат съхраняване на данни само в релационни структури, някои само в многоизмерни. Въпреки това повечето съвременни сървърни OLAP инструменти поддържат и трите метода за съхранение на данни. Изборът на метод за съхранение зависи от обема и структурата на изходните данни, изискванията за скоростта на изпълнение на заявката и честотата на актуализиране на OLAP кубовете.

Имайте предвид също, че по-голямата част от съвременните OLAP инструменти не съхраняват „празни“ стойности (пример за „празна“ стойност би била липсата на продажби на сезонен продукт извън сезона).

Основни OLAP концепции

FAMSI тест

Технологията за комплексен многоизмерен анализ на данни се нарича OLAP (On-Line Analytical Processing). OLAP е ключов компонент на организацията на хранилището на данни. Концепцията за OLAP е описана през 1993 г. от Едгар Код, известен изследовател на бази данни и автор на релационния модел на данни. През 1995 г. въз основа на изискванията, изложени от Codd, т.нар FASMI тест(Fast Analysis of Shared Multidimensional Information) - бърз анализ на споделена многомерна информация, включително следните изисквания за приложения за многомерен анализ:

бързо(Бързо) - предоставяне на потребителя на резултатите от анализа за приемливо време (обикновено не повече от 5 s), дори с цената на по-малко подробен анализ;
Анализ(Анализ) - способността да се извършва всеки логически и статистически анализ, характерен за това приложение, и запазването му във форма, достъпна за крайния потребител;
Споделено(Споделен) - многопотребителски достъп до данни с поддръжка на подходящи заключващи механизми и средства за оторизиран достъп;
Многоизмерен(Multidimensional) - многомерно концептуално представяне на данни, включително пълна поддръжка за йерархии и множество йерархии (това е ключово изискване на OLAP);
Информация(Информация) - приложението трябва да има достъп до всяка необходима информация, независимо от нейния обем и място за съхранение.

Трябва да се отбележи, че OLAP функционалността може да бъде внедрена по различни начини, започвайки с най-простите инструменти за анализ на данни в офис приложенияи завършвайки с разпределени аналитични системи, базирани на сървърни продукти.

Многомерно представяне на информация

кубчета

OLAP предоставя удобни и бързи средства за достъп, преглед и анализ на бизнес информация. Потребителят получава естествен, интуитивен модел на данни, организирайки ги под формата на многомерни кубове (Cubes). Осите на многомерната координатна система са основните атрибути на анализирания бизнес процес. Например, за продажби може да бъде продукт, регион, тип купувач. Като едно от измеренията се използва времето. В пресечните точки на осите на измерване (Dimensions) има данни, които количествено характеризират процеса - мерки (Measures). Това могат да бъдат обеми на продажби в бройки или в парично изражение, салда на склад, разходи и т.н. Потребителят, анализирайки информацията, може да „нареже“ куба в различни посоки, да получи обобщение (например по години) или, обратно, подробно ( по седмица) информация и извършва други манипулации, които му хрумват по време на процеса на анализ.

Като мерки в триизмерния куб, показан на фиг.

26.1 се използват суми на продажбите, а като измерения се използват време, продукт и магазин. Измерванията са представени на специфични нива на групиране: продуктите са групирани по категория, магазините по държава, а данните за времето на транзакция по месец. Малко по-късно ще разгледаме по-подробно нивата на групиране ( йерархия).

ориз. 26.1.

"Нарязване" на кубче

(нива). Например, етикетите, представени в, не се поддържат от всички OLAP инструменти. Например Microsoft Analysis Services 2000 поддържа и двата типа йерархия, но Microsoft OLAP Services 7.0 поддържа само балансирани. Броят на йерархичните нива, максимално допустимият брой членове на едно ниво и максималният възможен брой самите измерения могат да бъдат различни в различните OLAP инструменти.

Архитектура на OLAP приложения

Многоизмерността в OLAP приложенията може да бъде разделена на три нива.

Многомерно представяне на данни - инструменти за краен потребител, които осигуряват многомерна визуализация и манипулиране на данни; Слоят на многомерното представяне се абстрахира от физическата структура на данните и ги третира като многоизмерни.
Многомерната обработка е средство (език) за формулиране на многомерни заявки (традиционният релационен език SQL е неподходящ тук) и процесор, който може да обработва и изпълнява такава заявка.
Многомерното съхранение е средство за физическо организиране на данни, което гарантира ефективно изпълнение на многомерни заявки.

Конкретни OLAP продукти, като правило, са или многомерен инструмент за представяне на данни (OLAP клиент - например Pivot Tables в Excel 2000 от Microsoft или ProClarity от Knosys) или многомерен сървър DBMS (OLAP сървър - например Oracle Express Server или Microsoft OLAP услуги).

Многоизмерният слой за обработка обикновено е вграден в OLAP клиента и/или OLAP сървъра, но може да бъде изолиран в чистата си форма, като например компонента Pivot Table Service на Microsoft.

В стандартна обобщена таблица изходните данни се съхраняват на вашия локален твърд диск. По този начин винаги можете да ги управлявате и реорганизирате, дори и без достъп до мрежата. Но това по никакъв начин не се отнася за обобщените таблици на OLAP. В OLAP обобщените таблици кешът никога не се съхранява на локалния твърд диск. Ето защо, веднага след прекъсване на връзката от локална мрежавашата обобщена таблица вече няма да работи. Няма да можете да преместите нито едно поле в него.

Ако все още трябва да анализирате OLAP данни, след като сте офлайн, създайте офлайн куб с данни. Офлайн кубът с данни е отделен файл, който е кеш на обобщена таблица и съхранява OLAP данни, които се преглеждат след прекъсване на връзката с локалната мрежа. OLAP данните, копирани в обобщена таблица, могат да бъдат отпечатани подробно на уебсайта http://everest.ua.

За да създадете самостоятелен куб с данни, първо създайте OLAP обобщена таблица. Поставете курсора в обобщената таблица и щракнете върху бутона OLAP инструменти в контекстния раздел Инструменти, който е част от групата контекстни раздели Инструменти за обобщена таблица. Изберете отбор Офлайн режим OLAP (Офлайн OLAP) (фиг. 9.8).

На екрана се появява диалоговият прозорец Offline OLAP Data Cube Settings. Кликнете върху бутона Създаване офлайн файлданни (Създаване на офлайн файл с данни). Стартирали сте съветника за създаване на файл с куб данни. Щракнете върху бутона Напред, за да продължите процедурата.

Първо трябва да укажете размерите и нивата, които ще бъдат включени в куба с данни. В диалоговия прозорец трябва да изберете данните, които ще бъдат импортирани от OLAP базата данни. Идеята е да посочите само тези размери, които ще са необходими след изключване на компютъра от локалната мрежа. Колкото повече измерения посочите, толкова по-голям ще бъде автономният куб с данни.

Щракнете върху бутона Напред, за да преминете към следващия диалогов прозорецмайстори Той ви дава възможност да посочите членове или елементи от данни, които няма да бъдат включени в куба. По-специално, няма да имате нужда от мярката за разширена сума за интернет продажби, така че нейното квадратче за отметка ще бъде изчистено в списъка. Изчистеното квадратче за отметка показва, че посоченият елемент няма да бъде импортиран и да заема ненужно място на вашия локален твърд диск.

В последната стъпка посочете местоположението и името на куба с данни. В нашия случай файлът с куб ще се казва MyOfflineCube.cub и ще се намира в папката Work.

Файловете с кубчета данни имат разширение .куб

След известно време Време на Excelще запази офлайн куба с данни в указаната папка. За да го тествате, кликнете два пъти върху файла, което ще предизвика автоматично генериранеработещ Работни книги на Excel, който съдържа обобщена таблица, свързана с избрания куб с данни. Веднъж създаден, можете да разпространявате офлайн куба с данни на всички заинтересовани потребители, които работят в офлайн LAN режим.

След като се свържете с вашата локална мрежа, можете да отворите офлайн файла с куб с данни и да го актуализирате, както и свързаната таблица с данни. Основният принцип гласи, че офлайн кубът с данни се използва само за работа, когато локалната мрежа е прекъсната, но е необходимо да се актуализира след възстановяване на връзката. Опитът за актуализиране на офлайн куб с данни след неуспешна връзка ще доведе до неуспех.

Третата статия, относно обработката на големи количества данни с Excel, описва предимствата от използването на обобщени таблици. Като цяло тази статия трябваше да бъде първата от поредицата, ако говорим за ползите от този или онзи метод на работа. И наистина, интерфейсът на обобщената таблица е специално създаден за анализиране на големи количества данни, които могат да се съхраняват не само в диапазони на електронни таблици, но и във външни източници на данни. Разбиране на принципите на работа и практическа употребаПивот таблиците могат значително да оптимизират ежедневната работа на икономистите. Повишаването на нивото на анализ на данните от своя страна води до подобряване на управлението на компанията и приемане на правилни управленски решения от мениджърите на различни нива.

Общите теоретични въпроси относно работата с обобщени таблици и анализ на многомерни данни са описани в друга статия на нашия уебсайт.

Тук ще се спрем по-подробно на конкретни методи за обработка на данни с помощта на интерфейса на обобщената таблица. Използвайте файла като пример nwdata_pivot.xls.

Използване на обобщени таблици

Извадка от уникални стойности

Една от най-популярните задачи, решавани с помощта на обобщена таблица, е избирането на уникални стойности от списък или масив от данни. Използването на интерфейса PivotTable ви позволява да разрешите този проблем по най-„елегантния“ начин - без да използвате формули.

В примера на листа пробапоказва списък с държави и броя на споменаванията в масива от данни.

Полето за данни трябва да съдържа вида на операцията – „количество”. Тази опция ви позволява да обработвате нечислови полета с изходни данни в областта с данни на обобщената таблица. Алтернатива на операцията за броене е стандартната функция COUNTIF. По принцип също е възможно да се генерира набор от уникални стойности, като се използват само формули (вижте част 1), но това ще изисква много сложни формули с изчислено адресиране. Тоест използването на обобщена таблица в тази задача е най-оптималният начин за нейното решаване.

Сумирани стойности

Друга популярна задача за използване на интерфейса на обобщената таблица е получаването на общи суми за уникални записи в набор от данни.

В примера на листа Сумагенерирани са обобщени данни за поръчките за всяка държава:

Типът операция "Сума" в полето за данни позволява използването само на числови полета. Други видове агрегиране на изходни данни почти не се използват на практика.

За да разрешите проблем с помощта на стандартни формули, можете да използвате функцията SUMIF. Очевидно трудността възниква не при консолидирането на ценностите, а, както в предишния пример, при избора на уникален списък (в примера имената на страните).

2D анализ

Описаните по-рано примери демонстрират анализ на данни въз основа на един критерий. Електронни таблициви позволяват да визуализирате данни в две измерения: колони и редове. Обобщените таблици също имат тези области за показване на данни.

В примера на листа Таблицабеше генериран отчет по държава и дата, показващ промените в броя на поръчките във времето. Моля, обърнете внимание, че за полето тип дата се прилага допълнително групиране: по месеци и по години.

Чрез стандарт е възможно и сумиране по няколко критерия Функции на Excel SUMIFS, SUMPRODUCT и функции за обработка на масиви (вижте част 1). Тази опция обаче изисква предварително известни стойности на параметрите - клавишите за избор. Освен това изчисленията с помощта на формули изискват значително повече време, което може да доведе до големи загуби в производителността за големи обеми от данни.

Многовариантен анализ

В допълнение към визуалния анализ в областта по редове и колони, в осеви таблици можете да използвате глобален филтър по едно или повече полета на изходните данни. За тази цел има специална област от клетки, разположена над обобщената таблица.

Пример на лист Филтърдемонстрира способността да преглеждате данни за компании в една държава, като използвате филтърната област на обобщена таблица:

Филтърното поле може да се премести в областта на редовете или колоните, което ви позволява да видите по-голям масив от информация. В допълнение към описаната област за филтриране, може да се направи допълнително филтриране на данни чрез създаване на списъци с ключови полета в областите на реда или колоната.

Аналогът на използването на филтри за обобщена таблица, използващи формули за работен лист, в повечето случаи е формула за обработка на масиви.

Примери на листове опорна точка1и опорна точка2покажете опции за показване на същата информация с помощта различни настройкиРазмери на осевата таблица.

Работа с данни

Актуализация на данните

Една обобщена таблица може да се основава както на данни, разположени в произволна област от клетки, така и на външни източници на данни. Нека разгледаме първо първия вариант. Тези. данните за анализ се съхраняват в набор от работни клетки Excel лист.

Отчет под формата на обобщена таблица може да бъде изготвен както за еднократна употреба, така и за текуща употреба с променлив набор от изходни данни. Последната опция предоставя на потребителя по-големи възможности за интерактивна работа: Трябва да конфигурирате и форматирате формуляра за отчитане веднъж, след което, когато редактирате изходните данни, промените в окончателния формуляр ще бъдат направени автоматично. В този случай отчетът не само променя данните, но също така може да добавя и изтрива редове и колони, което е практически невъзможно да се реализира с формули на работен лист.

Съветникът за обобщена таблица ви позволява да посочите диапазон от клетки, които да използвате като източник на данни. Ако при актуализиране на информацията са добавени нови редове, те може да не бъдат включени в източника на данни на обобщената таблица и съответно няма да бъдат анализирани правилно. Тази функция е доста трудна за проследяване при обработка на големи количества данни.

Можете да промените обхвата на източника на данни за съществуваща обобщена таблица чрез специален диалогов прозорец в Excel 2007-2010. В предишни версии на Excel тази функция на интерфейса беше внедрена в съветника за обобщена таблица, когато беше стартирана от активна обобщена таблица. След като отворите съветника, трябва да се върнете една стъпка назад:

Могат да бъдат направени и корекции на източника на данни програмно. Например през прозореца за изчисление на VBA редактора (незабавно):

За да не мислите за правилността на размера на изходния диапазон на данните от обобщената таблица, можете първоначално да посочите диапазон от редове с голям марж по време на изграждането. Например, знаейки, че очакваният обем редове не надвишава 10 000, можете веднага да зададете тази стойност като размер на диапазона. На практика подобно резервиране няма да доведе до видимо забавяне на работата на интерфейса на PivotTable. Можете да скриете празни стойности в размерите на отчета. Недостатъкът на този метод се проявява преди всичко при работа с полета от типа „дата“. Стандартният интерфейс на обобщената таблица ви позволява да прилагате различни групировки, когато работите с типа „дата“ (по месец, по тримесечие), но ако има празни стойности, тези опции стават недостъпни, тъй като Excel дефинира такава колона като текст.

В допълнение към разгледаните методи за контрол на източника на данни, предлагаме да конфигурирате диапазона от редове на обобщената таблица на активния работен лист с помощта на програмни методи. Ако източникът на данни заема цялата работна област на листа, тогава можете да използвате следната команда:

ActiveSheet.PivotTables(1).SourceData = _ Left(ActiveSheet.PivotTables(1).SourceData, _ InStr(ActiveSheet.PivotTables(1).SourceData, "!")) & _ Range(Application.ConvertFormula(_ ActiveSheet.PivotTables (1).SourceData, xlR1C1, xlA1) _).Worksheet.UsedRange.Address(ReferenceStyle:=xlR1C1)

Най-надеждният, но най-бавният начин е да проверите последователно редовете на изходния лист и след това да попълните свойството Изходни данниактивна обобщена таблица. Имайте предвид, че това свойство се съхранява само в R1C1 адресиране.

Макросите могат да се извикват по събитие Работен лист_Активиране, или конфигурирайте горещ клавиш.

Работа с резултатите от анализа

Обобщената таблица се намира в диапазон от клетки в работен лист на Excel. Писането на формули в работен лист в границите на обобщена таблица не е разрешено, нито ръчно, нито с помощта на програмни методи. Теоретично е възможно да се работи с клетки, разположени в границите на обобщена таблица, като се използват връзки за външни формули. Често на практика функцията VLOOKUP се използва и за търсене в колона на обобщена таблица. Този метод трябва да се използва с голямо внимание - интерфейсът на обобщения отчет включва промяна на позицията на показаните данни спрямо правоъгълните координати на работния лист без никакво въздействие върху източника на тези данни. Това означава, че няма гаранция, че връзката, посочена във формулата в обобщената таблица, ще покаже правилната стойност, когато по-нататъшна работас файла. Въпреки това източникът на данни може да не се промени.

Наличен алтернативни начиниобработка на резултатите от обобщената таблица:

Копиране и поставяне на стойностите на обобщена таблица в друг лист (с помощта на функцията „Специално поставяне“) с по-нататъшно търсене на данни в този формиран диапазон от клетки. Много по-трудно е да се наруши целостта на данните в проста таблица, отколкото в обобщена таблица. Очевидно основният недостатък на този начин на работа е използването на ръчни операции след всяка актуализация на източника на данни.
Използвайте функцията GETPIVOTDATA (Excel 2002 и по-нови). Тази функциявключва достъп до данни не чрез координати на работен лист, а чрез размери на обобщена таблица. За източници на данни от типа OLAP куб има специални функциидостъп до данни и измервания: CUBEVALUE, CUBEMEMBER и други (Excel 2007-2010). Този методе неудобно и също така забавя значително работата, ако трябва да получите много различни стойности в обобщена таблица.
Откажете се от обобщена таблица, за да получите резултати. Вместо това използвайте формулите от работния лист (вижте част 1). Този метод, макар и труден за прилагане, може да бъде най-удобен, ако други изчисления се основават на резултатите и източникът на данни се актуализира често.

Версии на интерфейса на обобщената таблица

В нов формат xlsx файл(Excel 2007-2010) възможностите на интерфейса на обобщените таблици са значително променени. В предишните версии на интерфейса (97-2003) бяха направени само „козметични“ промени:

Excel 2000 (9.0) – основна версияИнтерфейс на обобщена таблица.
Excel XP (10.0) - нова функция GETPIVOTDATE
Excel 2003 (11.0) - Изглежда, че изобщо не са направени промени
Excel 2007 (12.0) – нова версияИнтерфейс на обобщена таблица с поддръжка за разширени диапазони. Подобрена производителност, променена външен видинтерфейс. Съвместимостта със стария формат се запазва.
Excel 2010 (14.0) – Поддръжка на добавки PowerPivot. Работа с актуализирани OLAP кубове.

Основни промени в новия файлов формат (2007-2010):

Една колона може да съдържа няколко полета от обобщена таблица, подчертани с вдлъбнатини (компресирана форма).
РезениПивотните таблици ви позволяват визуално да показвате текущия набор от стойности за филтриране.
Размерите в областта на филтъра поддържат многократен избор.
Елементите на размерите могат да бъдат скрити/показани чрез бутони, разположени в същата клетка като самата заглавка.
Има няколко нови параметъра в свойствата на полето и таблицата.
Налични са стилове на обобщена таблица, които ви позволяват да промените външния вид на вашите отчети по всяко време.

За да разберете по-добре разликите, изтеглете и отворете примерните файлове nwdata_pivot1.xlsxи nwdata_pivot2.xlsx(в архива nwdata_pivot.zip). Първият файл представя отчета в стария формат, вторият - в новия, изходните данни са същите.

Вътрешна организация на интерфейса на обобщената таблица

За да разберем по-добре принципите на работа на обобщената таблица, нека разгледаме вътрешната организация на интерфейса.

Кеш на осевата таблица

Когато създавате или актуализирате обобщена таблица, независимо от избрания тип източник, Excel прехвърля данните в междинно хранилище, наречено кеш на осевата таблица. Структурата на организиране на данни в кеша ви позволява значително да оптимизирате агрегирането на данни и изчисленията в обобщената таблица. Съхраняването на данни в собствен кеш ви позволява да използвате различни източници на данни, като същевременно поддържате сходна функционалност.

Данните в кеша се актуализират, когато щракнете върху бутона Обновяване на интерфейса на обобщената таблица (бутон на лентата или в контекстно меню), или на определен интервал от време, ако такава настройка е посочена в параметрите. Режимът на изчисление на Excel (автоматичен или ръчен) не засяга обобщената таблица по никакъв начин.

Множество обобщени таблици (или диаграми) могат да показват данни от един и същ кеш. Тази работна опция се използва за показване на множество формуляри за отчитане на едни и същи данни, без да се използва интерфейсът за настройка на измерването. В този случай, когато една от таблиците се актуализира, тази, базирана на същия кеш, автоматично се възстановява.

VBA обекти

Достъпът до данни чрез програмни методи е възможен на ниво обекти на обобщена таблица - обект обобщена таблица. Други обекти на PivotTable са отговорни за подреждането и визуалното показване на елементи и данни. Те включват колекции от полета: PivotFields, ColumnFields, RowFields, PageFields, DataFields. Опциите за стойност на полето са достъпни чрез колекции от обекти PivotItems.

Универсална възможност за достъп до данни директно в кеша (обект PivotCache) по някаква причина не се предоставя от разработчиците на Excel. Логиката не е съвсем ясна. Както вече беше отбелязано, кеш данните се съхраняват отделно и дори могат да се видят в xlsx файл, ако отворите този файл като zip архив. В зависимост от типа източник на данни може да опитате да използвате свойството Изходни данни(за осеви таблици, базирани на диапазони) или Набор от записи(за източници от типа „заявка към база данни“).

Изчисляеми полета и обекти на обобщена таблица ( Изчислени полета, Изчислени елементи) имат собствен механизъм за изчисление и дърво на зависимостта на формулата, което не е свързано с формулите на работния лист на Excel. На практика препоръчваме, ако е възможно, избягване на голям брой изчислими полета в обобщените таблици, тъй като това води до значително забавяне на изчисленията. За източници на данни за диапазон от клетки често можете просто да добавите колона с обикновена формула към изходните данни, а за заявки към база данни можете да добавите изчисления директно към тялото на SQL заявката.

Видове източници на данни

В световен мащаб източниците на данни могат да бъдат разделени на 3 вида:

Клетъчни диапазони
Заявки към бази данни
OLAP кубове и PowerPivot2010 като една от възможностите за внедряване на OLAP механизма.

Диапазони

Първият вариант е най-често срещаният в практиката; Предишните описания на примерите се отнасят конкретно за данни, съхранявани в диапазон от клетки.

Стандартният интерфейс на Excel не ви позволява да изградите обобщен отчет въз основа на множество диапазони от клетки. Причината за това ограничение не е много ясна. Има подозрение, че разработчиците просто не могат да предложат интуитивен потребителски интерфейс за решаване на този проблем. Техническото изпълнение на задачата не изглежда твърде сложно - просто трябва да попълните кеша с данни. В секцията ДобавкиНашият сайт представя собствено решение за изграждане на комплексни обобщени справки.

Заявки към бази данни

Заявките към базата данни могат да бъдат реализирани с помощта на различни технически механизми: Microsoft Query, ADO, ODBC. Независимо от интерфейса за достъп до данни, обединяващият фактор на това решение е попълването на кеша на обобщената таблица директно от външен източник. При по-нататъшна работа с обобщената таблица заявката може да бъде изпълнена отново, след което данните отново ще бъдат прехвърлени в кеша. Този метод ви позволява да анализирате данни от външни източници (счетоводни системи) в реално време. Ако връзката с източника на данни е изгубена, може да се извърши анализ на най-новите данни в кеша.

OLAP кубове

OLAP кубът осигурява междинно ниво на подготовка на информацията за многомерен анализ в осеви таблици. Кубът съхранява информация за наличните типове полета (измерение или данни), йерархични зависимости на полета, агрегирани стойности (междинни суми) и други изчислени елементи. Основното предимство на използването на кубове пред директните заявки към базата данни е висока производителност, тъй като данните се преместват и агрегират в междинното хранилище. Недостатъкът също е очевиден този метод– Данните от OLAP куб може да съдържат неуместна информация, която зависи от настройките за съхранение.

Преди Office 2007 можеше да се подготви прост OLAP куб с помощта на Помощ от MicrosoftЗапитване, но в най-новите версииТази функция е деактивирана по неизвестни причини. Разработчиците силно препоръчват използването SQL сървърУслуга за анализ за създаване и конфигуриране на OLAP кубове. Препоръката е полезна, но, първо, тази услуга е включена само в платени версии SQL Server и, второ, изисква сериозно проучване както на интерфейса, така и на езика за обработка на MDX заявки.

Примерът за тази статия показва архив nwdata_cube.zipс два файла nwdata_cube.cub, nwdata_cube.xls. Моля, обърнете внимание на промените в интерфейса на PivotTable, когато използвате OLAP куб като източник на данни:

Наличието на йерархични измерения, няма възможност за промяна на родителя и дъщерен елементна някои места.
Не е разрешено преместване на размери в областта с данни и обратно.
Междинните суми се показват за всички елементи, а не от текущия групов филтър.

PowerPivot

Специална добавка PowerPivot е налична за Excel 2010, която като цяло е алтернативен механизъм за внедряване на OLAP кубове. С PowerPivot можете да обработвате милиони записи от различни информационни файлове и бази данни с огромна производителност. В същото време потребителският интерфейс за окончателния анализ на данните е внедрен в Excel 2010.

Много е вероятно тази добавка да бъде включена в следващата версия на Excel като основна функционалност. Наистина се надяваме да посветим отделна статия или дори поредица от статии, за да опишем как работи PowerPivot. Днес PowerPivot + Excel са може би най-много мощен инструментза анализиране на големи обеми от данни.

Официален уебсайт на PowerPivot.

Самостоятелен кубичен файл (.cub) съхранява данни във формуляр в куб за онлайн аналитична обработка (OLAP). Тези данни може да представляват част от OLAP база данни от OLAP сървър или може да са създадени независимо от която и да е OLAP база данни. За да продължите да работите с отчети с обобщена таблица и обобщена диаграма, когато сървърът е недостъпен или когато сте офлайн, използвайте офлайн кубичен файл.

Повече информацияотносно автономните кубове

Когато работите с отчет с обобщена таблица или обобщена диаграма, който се основава на източник на данни от OLAP сървър, използвайте съветника за офлайн куб, за да копирате изходните данни в отделен офлайн файл с куб на вашия компютър. За да създадете тези офлайн файлове, трябва да имате OLAP доставчик на данни, който поддържа тези възможности, като MSOLAP от Microsoft SQL Server Analysis Services, инсталиран на вашия компютър.

Забележка:Създаване и използване на офлайн куб файлове от Microsoft SQL Server Analysis Services, предмет на условия и лицензиране Инсталации на Microsoft SQL сървър. Прегледайте подходящата информация за лицензиране за вашата версия на SQL Server.

Използване на офлайн съветника за куб

За да създадете офлайн файл с куб, използвайте съветника за офлайн куб, за да изберете поднабор от данни в OLAP базата данни и след това запазете този набор. Не е необходимо отчетът да включва всички полета, включени във файла, и можете да избирате от всяко от неговите измерения и полета с данни, налични в OLAP базата данни. За да намалите размера на файла, можете да включите само данните, които искате да можете да показвате в отчета. Можете да пропуснете всички измерения и, за повечето типове измерения, също така да пропуснете подробности от по-ниско ниво и функции от най-високо ниво, които не искате да показвате. За офлайн файл всички елементи, които могат да бъдат включени в полетата за свойства, които са налични в базата данни за тези елементи, също се записват.

Вземане на данни офлайн и след това връщане на данните обратно онлайн

За да направите това, първо трябва да създадете отчет с обобщена таблица или отчет с обобщена диаграма, който се основава на базата данни на сървъра, и след това да създадете самостоятелен кубичен файл от отчета. Впоследствие, когато работите с отчет, можете да превключвате между сървърната база данни и офлайн файла по всяко време (например, когато работите на лаптоп у дома или на път и след това свържете отново компютъра към мрежата).

По-долу са описани основните стъпки за прехвърляне на данни офлайн и връщането им обратно онлайн.

Забележка:

Щракнете върху отчета с обобщена таблица. Ако това е отчет с обобщена диаграма, изберете свързания отчет с обобщена таблица.

В раздела Анализ"в групата изчислениящракнете върху бутона OLAP услугаи натиснете бутона Офлайн OLAP.

Изберете елемент OLAP със свързаности след това щракнете върху бутона добре.

Ако бъдете подканени да намерите източник на данни, щракнете Намерете източники намерете OLAP сървър в мрежата.

Щракнете върху отчета с обобщена таблица, който се основава на офлайн файла с куб.

В Excel 2016: В раздела " данни"в групата заявки и връзки Актуализирайте всичкии натиснете бутона Актуализация.

В Excel 2013: В раздела " данни"в групата връзкищракнете върху стрелката до бутона Актуализирайте всичкии натиснете бутона Актуализация.

В раздела Анализ"в групата изчислениящракнете върху бутона OLAP услугаи натиснете бутона Офлайн OLAP.

Щракнете върху бутона Офлайн OLAP режим, а след това - .

Забележка: Спретев диалоговия прозорец.

Предупреждение:

Създаване на офлайн куб файл от база данни на OLAP сървър

Забележка:Ако OLAP базата данни е голяма и кубичният файл е необходим за осигуряване на достъп до голямо подмножество от данни, много свободно пространствона диск и запазването на файла може да отнеме много време. За да подобрите производителността, се препоръчва да създавате самостоятелни кубични файлове с помощта на MDX скрипт.

Проблем: Компютърът ми няма достатъчно дисково пространство при записване на куб.

OLAP базите данни са проектирани да управляват големи количества подробни данни, така че база данни, хоствана на сървър, може да заема значително повече място, отколкото е налично на вашия локален твърд диск. Ако изберете голямо количество данни за офлайн куб с данни, може да нямате достатъчно свободно дисково пространство. Следният подход ще помогне за намаляване на размера на офлайн кубичния файл.

Освободете място на диска или изберете друг дискПреди да запазите кубичния файл, премахнете го от диска. ненужни файловеили запишете файла на мрежово устройство.

Включване на по-малко данни в офлайн куб файлПомислете как можете да минимизирате количеството данни, включени във файла, така че файлът да съдържа всички данни, необходими за отчет с обобщена таблица или обобщена диаграма. Опитайте стъпките по-долу.

Свързване на офлайн файл с куб към база данни на OLAP сървър

Актуализиране и повторно създаване на офлайн куб файл

Актуализирането на офлайн кубичен файл, който е създаден от най-новите данни, получени от сървърен куб или от нов офлайн кубичен файл, може да отнеме значително време и да изисква голямо количество временно дисково пространство. Стартирайте този процес, когато не се нуждаете от незабавен достъп до други файлове, след като се уверите, че имате достатъчно място на вашия твърд диск.

Проблем: Новите данни не се показват в отчета, когато се обнови.

Проверка на наличността на изходната база данниОфлайн кубичният файл може да не може да се свърже с базата данни на сървъра източник, за да получи нови данни. Уверете се, че оригиналната база данни на сървъра, който е източник на данни за куба, не е преименувана или преместена на друго място. Уверете се, че сървърът е достъпен и може да се свърже с него.

Проверка за нови данниПроверете при администратора на вашата база данни дали данните, които трябва да бъдат включени в отчета, са актуализирани.

Проверка на неизменността на организацията на базата данниАко кубът на OLAP сървъра е бил модифициран, може да се наложи да реорганизирате отчета, да създадете офлайн файл с куб или да стартирате съветника за създаване на OLAP куб, за да получите достъп до променените данни. За да научите за промените в базата данни, свържете се с администратора на вашата база данни.

Включване на други данни в офлайн кубичния файл

Записването на модифициран офлайн кубичен файл може да отнеме много време и не можете да работите в Microsoft Excel, докато файлът се записва. Стартирайте този процес, когато не се нуждаете от незабавен достъп до други файлове, след като се уверите, че имате достатъчно място на вашия твърд диск.

Проверете дали има мрежова връзка и че изходната база данни на OLAP сървъра, от която офлайн кубичният файл е получил данните, е достъпна.

Щракнете върху отчет с обобщена таблица, създаден от самостоятелен кубичен файл, или свързан отчет с обобщена таблица за отчет с обобщена диаграма.

В раздела Опциив групата Обслужванещракнете върху бутона OLAP услугаи натиснете бутона Офлайн OLAP режим.

Щракнете върху бутона Офлайн OLAP режим, а след това - Редактиране на офлайн файл с данни.

Следвайте съветника за офлайн куб, за да изберете други данни, които да включите в този файл. В последната стъпка посочете името и пътя към файла, който искате да промените.

Забележка:За да отмените записването на файла, щракнете върху бутона Спретев диалоговия прозорец Създаване на куб файл - напредък.

Изтриване на офлайн куб файл

Предупреждение:Ако изтриете офлайн файл с куб за отчет, вече не можете да използвате този отчет офлайн и повече не можете да създадете офлайн файл с куб за този отчет.

Затворете всички работни книги, които съдържат отчети, които използват офлайн кубичния файл, или се уверете, че всички такива отчети са изтрити.

IN Microsoft WindowsНамерете и изтрийте офлайн кубичния файл (CUB файл).

Повече информация

Винаги можете да зададете въпрос на специалист от Excel Tech Community, да поискате помощ в общността на Answers и също да предложите нова функцияили подобрение на уебсайта