Олап кубчета в ексел. Публикации Практическа работа по olap технология в excel

Първият интерфейс за осеви таблици, наричани още обобщени отчети, беше включен в Excel през 1993 г. (Excel версия 5.0). Въпреки многото полезни функционалност, практически не се използва в работата от повечето потребители на Excel. Дори опитни потребители често имат предвид под термина „обобщен отчет“ нещо, създадено с помощта на сложни формули. Нека се опитаме да популяризираме използването на обобщени таблици в ежедневната работа на икономистите. Статията обсъжда теоретична основаДадени са създаване на обобщени отчети практически препоръкиотносно тяхното използване, а също така дава пример за достъп до данни въз основа на няколко таблици.

Условия за многовариантен анализ на данни

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

Многомерни данни, измервания

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

видове или групи стоки;
марки или продуктови категории;
периоди (месец, тримесечие, година);
купувачи или групи от купувачи;
региони за продажба
и така нататък.

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

обем на продажбите;
Продажна цена;
индивидуална отстъпка
и така нататък.

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

По този начин можем да кажем, че в практическата си работа икономистите използват два вида информация: многоизмерни данни (действителни и планирани числа, които имат много характеристики) и справочници (характеристики или измервания на данни).

OLAP

Съкращението OLAP (online analytical processing) буквално означава „аналитична обработка в реално време“. Определението не е много конкретно, под него може да се обобщи почти всеки отчет за всеки софтуерен продукт. По смисъла си OLAP означава технология за работа със специални отчети, включително софтуер, за получаване и анализ на многомерни структурирани данни. Един от популярните софтуерни продукти, които прилагат OLAP технологиите, е SQL сървърСървър за анализ. Някои дори погрешно го смятат за единствения представител на софтуерната реализация на тази концепция.

Виртуален куб с данни

„Виртуален куб“ (многоизмерен куб, OLAP куб) е специален термин, предложен от някои специализирани доставчици софтуер. OLAP системите обикновено подготвят и съхраняват данни в свои собствени структури и специални интерфейси за анализ (например резюме Ексел отчети) достъп до данните на тези виртуални кубове. Освен това използването на такова специално хранилище изобщо не е необходимо за обработка на многоизмерна информация. Общо взето, виртуален куб– това е масив от специално оптимизирани многоизмерни данни, които се използват за създаване на обобщени отчети. Тя може да бъде получена или чрез специализиран софтуер, или чрез лесен достъп до таблици на база данни или друг източник, като например електронна таблица в Excel.

Пивотна таблица

„Обобщен отчет“ (обобщаваща таблица, обобщена таблица) е потребителски интерфейсза показване на многоизмерни данни. Използвайки този интерфейс, можете да групирате, сортирате, филтрирате и пренареждате данни, за да получите различни аналитични проби. Отчетът се актуализира с помощта на прости инструменти за потребителски интерфейс, данните се агрегират автоматично според определени правила и не е необходимо допълнително или повторно въвеждане на каквато и да е информация. Интерфейсът на обобщената таблица на Excel е може би най-популярният софтуерен продукт за работа с многоизмерни данни. Той поддържа както външни източници на данни (OLAP кубове и релационни бази данни), така и вътрешни диапазони на електронни таблици като източник на данни. Започвайки с версия 2000 (9.0), Excel поддържа и графична форма за показване на многомерни данни - Pivot Chart.

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

Още веднъж бих искал да обърна внимание на факта, че обобщената таблица на Excel е предназначена единствено за анализ на данни без възможност за редактиране на информацията. По-близко значение би било широкото използване на термина „обобщен отчет“ и точно това е името на този интерфейс до 2000 г. Но по някаква причина в следващите версии разработчиците го изоставиха.

Редактиране на осеви таблици

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

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

Подготовка на многомерни данни

Да дойдем на практическо приложениеПивотни таблици. Нека се опитаме да анализираме данните за продажбите в различни посоки. Файл pittableexample.xlsсе състои от няколко листа. Лист Примерсъдържа основна информация за продажбите за определен период. За да опростим примера, ще анализираме един цифров показател – обем на продажбите в кг. Ключовите измерения на данните са: продукт, купувач и превозвач (транспортна компания). Освен това има няколко допълнителни измерения на данните, които са характеристики на продукта: тип, марка, категория, доставчик и клиент: тип. Тези данни се събират в листа с указатели. На практика може да има много повече такива измервания.

Лист Примерсъдържа стандартно средство за защитаанализ на данни – автофилтър. Разглеждайки примера за попълване на таблицата, очевидно е, че данните за продажбите по дата (те са подредени в колони) се поддават на нормален анализ. Освен това, като използвате автоматичен филтър, можете да опитате да обобщите данни въз основа на комбинации от един или повече ключови критерии. Няма абсолютно никаква информация за марки, категории и видове. Няма начин за групиране на данни с автоматично сумиране по определен ключ (например по клиенти). Освен това наборът от дати е фиксиран и няма да е възможно да видите обобщена информация за определен период, например 3 дни, с помощта на автоматични средства.

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

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

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

От автофилтър до обобщен отчет

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

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

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

Първата таблица за анализ е изградена чрез интерфейса на Excel 2007 Лента\Вмъкване\Обобщена таблица(в менюто на Excel 2000-2003 Data\PivotTable).

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

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

Свойства и форматиране

В допълнение към директното показване на данни, има широк набор от опции за показване на външния вид на осеви таблици. Можете да скриете ненужните данни с помощта на филтри. За отделен елемент или поле е по-лесно да използвате елемента от контекстното меню Изтрий(във версия 2000-2003 Крия).

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

В допълнение, Excel 2007 въведе много предварително дефинирани стилове на показване на обобщена таблица:

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

Достъп до външни данни

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

Excel поддържа много видове външни източници на данни:

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

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

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

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

Ориз. 9.8. Създайте офлайн куб с данни

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

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

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

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

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

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

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

Работа с офлайн куб файлове

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

Бележка за безопасност:Бъдете внимателни, когато използвате или разпространявате офлайн кубичен файл, който съдържа чувствителни или лични данни. Препоръчително е да запишете данните в работна книга вместо в кубичен файл, за да можете да контролирате достъпа до данните чрез управление на правата. Допълнителна информацияможете да намерите в Управление на правата за информация в Office.

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

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

Работа с Offline Cube Wizard

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

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

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

По-долу са основните стъпки, които трябва да следвате живот на батериятас данните и след това прехвърлете данните обратно в интернет.

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

Създайте офлайн файл с куб на вашия компютър. В глава Създаване на офлайн куб файл от база данни на OLAP сървър(по-долу в тази статия).

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

Свържете се с мрежата и свържете отново кубичния файл офлайн. Разгледайте секцията Повторно свързване на офлайн кубичния файл към базата данни на OLAP сървъра(по-долу в тази статия).

Актуализирайте офлайн файла с куб с нови данни и създайте отново офлайн файла с куб. Разгледайте секцията актуализиране и повторно създаване на офлайн файла с куб(по-долу в тази статия).

БЛОГ

Само качествени публикации

Какво представляват обобщените таблици на Excel и OLAP кубовете

Вижте видеото към статията:

OLAP- това е английски. онлайн аналитична обработка, аналитична технологияобработка на данни в реално време. С прости думи– склад с многомерни данни (Cube), още по-просто – просто база данни, от която можете да получите данни в Excel и да ги анализирате с помощта на инструмента на Excel – Pivot Tables.

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

За да стане ясно, нека сравним „Обикновена таблица“ с „Осева таблица“

Редовна маса:

Обобщена таблица:

Основна разлика Пивотни таблици– това е наличието на прозорец “ Списък с полета на обобщена таблица“, от който можете да изберете необходимите полета и да получите всяка таблица автоматично!

Как да използвам

Отворете Excel файл, който е свързан към OLAP куб, например „BIWEB“:

Сега, какво означава това и как да го използвате?

Плъзнете и пуснете задължителните полета, за да получите например таблица като тази:

« Плюсове» позволяват да се задълбочите в отчета. В този пример „Марка“ е подробно описана в „Съкратени имена“, а „Тримесечие“ в „Месец“, т.е. Така:

Аналитични функции в Excel (кубични функции)

Microsoft непрекъснато добавя нови възможности към Excel по отношение на анализ на данни и визуализация. Работата с информация в Excel може да бъде представена като относително независими три слоя:

“правилно” организирани изходни данни
математика (логика) на обработка на данни
представяне на данни

Ориз. 1. Анализ на данни в Excel: a) изходни данни, b) мярка c Power Pivot, в) арматурно табло; За да увеличите изображението, щракнете върху него Кликнете с десния бутонмишката и изберете Отворете изображението в нов раздел

Изтеглете бележката в Word или pdf формат, примери в Excel формат

Кубични функции и осеви таблици

Най-простото и в същото време много мощно средство за представяне на данни са обобщените таблици. Те могат да бъдат изградени от данни, съдържащи се в: a) работен лист на Excel, b) OLAP куб или c) модел на данни Power Pivot. В последните два случая, в допълнение към обобщената таблица, можете да използвате аналитични функции (кубни функции), за да генерирате отчет на Excel лист. Пивотните таблици са по-прости. Функциите на Cube са по-сложни, но осигуряват повече гъвкавост, особено при дизайна на отчети, поради което се използват широко в таблата за управление.

Следващата дискусия се отнася за формули за кубове и обобщени таблици, базирани на модела Power Pivot и, в няколко случая, базирани на OLAP кубове.

Лесен начин за получаване на кубични функции

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

Ориз. 2. Преобразувайте обобщена таблица в набор от функции на куб

Числата ще бъдат запазени и това няма да са стойности, а формули, които извличат данни от модела на данни на Power Pivot (Фигура 3). Можете да форматирате получената таблица. Включително можете да изтривате и вмъквате редове и колони в таблицата. Срезът остава и засяга данните в таблицата. Когато изходните данни се актуализират, числата в таблицата също ще се актуализират.

Ориз. 3. Таблица на базата на кубични формули

Функция CUBEVALUE().

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

Писане на формула от нулата

Не е необходимо да конвертирате предварително изградена обобщена таблица. Можете да напишете всяка формула за куб от нулата. Например следната формула се въвежда в клетка C10 (фиг. 4):

Ориз. 4. Функцията CUBEVALUE() в клетка C10 връща продажбите на велосипеди за всички години, както в обобщената таблица

Малък трик. За да улесните четенето на формулите на куба, препоръчително е да поставите само един аргумент на всеки ред. Можете да направите прозореца на Excel по-малък. За да направите това, щракнете върху иконата Пуснете прозореца, разположен в горния десен ъгъл на екрана. След това коригирайте хоризонтално размера на прозореца. Алтернативен вариант– принудително прехвърляне на текста на формулата нова линия. За да направите това, в лентата с формули поставете курсора на мястото, където искате да направите прехвърлянето, и натиснете Alt+Enter.

Ориз. 5. Намалете прозореца

Синтаксис на функцията CUBEVALUE().

Помощта за Excel е напълно точна и напълно безполезна за начинаещи:

CUBEVALUE(връзка; [елемент_израз1]; [елемент_израз2]; ...)

Връзка– необходим аргумент; текстов низ A, което представлява името на връзката към куба.

Елемент_израз– незадължителен аргумент; текстов низ, представляващ MDX, който връща елемент или кортеж в куб. Освен това „element_expression“ може да бъде набор, дефиниран с помощта на функцията CUBESET. Използвайте „element_expression“ като срез, за да определите частта от куба, за която искате да върнете обобщена стойност. Ако в element_expression не е указана мярка, ще се използва мярката по подразбиране за този куб.

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

Малко информация за OLAP кубове и модели на данни Power Pivot

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

Ориз. 5а. 3D OLAP куб

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

Power Pivot е сравнително нова функция на Microsoft. Това е вградена в Excel и частично независима среда с познат интерфейс. Power Pivot е много по-мощен от стандартните обобщени таблици. В същото време разработването на кубове в Power Pivot е сравнително просто и най-важното е, че не изисква участието на ИТ специалист. Microsoft прилага своя слоган: „Бизнес анализи за масите!“ Въпреки че моделите на Power Pivot не са 100% кубове, те също могат да се считат за кубове (за повече информация вижте уводния курс на Mark Moore, Power Pivot, и по-дългия курс на Rob Colley, DAX Formulas for Power Pivot).

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

Измерванията се основават на йерархия. Например, продуктова категория може да бъде разделена на подкатегории, след това на модели и накрая на имена на продукти (фиг. 5b).Йерархията ви позволява да създавате обобщени данни и да ги анализирате на различни нива на структурата. В нашия пример йерархията Категориявключва 4 Ниво.

Елементи(индивидуални членове) присъстват на всички нива. Например, на ниво категория има четири елемента: аксесоари, велосипеди, дрехи, компоненти. Другите нива имат свои собствени елементи.

Мерки– това са изчислени стойности, например обем на продажбите. Мерките в кубчетата се съхраняват в тяхното собствено измерение, наречено (вижте Фигура 9 по-долу). Мерките нямат йерархии. Всяка мярка изчислява и съхранява стойност за всички измерения и всички елементи и се нарязва в зависимост от това кои елементи на измерението поставяме върху оста. Те също казват какви координати ще зададем или какъв филтърен контекст ще зададем. Например на фиг. 5а, във всяко малко кубче се изчислява една и съща мярка - Печалба. И стойността, върната от мярката, зависи от координатите. Вдясно Фигура 5а показва, че печалбата (в три координати) в Москва през октомври от ябълки = 63 000 рубли. Мярката може също да се тълкува като едно от измеренията. Например на фиг. 5а вместо ос Стоки, поставете оста Меркис елементи Обем на продажбите, печалба, Продадени единици. Тогава всяка клетка ще бъде някаква стойност, например Москва, септември, обем на продажбите.

Кортеж– няколко елемента с различни размери, които задават координати по осите на куба, в които изчисляваме мярката. Например на фиг. 5а Кортеж= Москва, октомври, ябълки. Също така приемлив кортеж е Perm, ябълки. Друг – ябълки, август. Измеренията, които не са включени в кортежа, присъстват в него имплицитно и са представени от члена по подразбиране. По този начин клетка от многомерно пространство винаги се определя от пълен набор от координати, дори ако някои от тях са пропуснати от кортежа. Не можете да включите два елемента от едно и също измерение в кортеж, синтаксисът няма да го позволи. Например невалиден кортеж Москва и Перм, ябълки. За да приложите такъв многоизмерен израз, ще ви е необходим набор от два кортежа: Москва и ябълки + Perm и ябълки.

Набор от елементи– няколко елемента от едно измерение. Например ябълки и круши. Набор от кортежи– няколко кортежа, всеки от които се състои от едни и същи измерения в една и съща последователност. Например набор от два кортежа: Москва, ябълки и Перм, банани.

Автоматично довършване в помощ

Нека се върнем към синтаксиса на функцията CUBEVALUE. Нека използваме автоматичното довършване. Започнете да въвеждате формула в клетка:

Excel ще предложи всички връзки, налични в работната книга на Excel:

Ориз. 6. Връзката към модела на данни на Power Pivot винаги се нарича ThisWorkbookDataModel

Ориз. 7. Връзки с кубчета

Нека продължим с въвеждането на формулата (в нашия случай за модела на данни):

Автоматичното довършване ще предложи всички налични таблици и мерки в модела на данни:

Ориз. 8. Налични елементи от първо ниво - имена на таблици и набор от мерки (маркирани)

Изберете икона Мерки. Направи точка:

CUBEVALUE(" ThisWorkbookDataModel " ; " .

Автоматичното довършване ще предложи всички налични мерки:

Ориз. 9. Налични елементи от второ ниво в мярката

Изберете мярка. Добавете кавички, затваряща скоба и натиснете Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . " )

Ориз. 10. Формула CUBE VALUE в клетка на Excel

По същия начин можете да добавите трети аргумент към формулата:

VBA в Excel Обект на Excel.PivotTable и работа с обобщени таблици и OLAP кубове в Excel

10.8 Работа с обобщени таблици (обект PivotTable)

Обект Excel.PivotTable, програмна работас обобщени таблици и OLAP кубове в Excel с помощта на VBA, обект PivotCache, създаване на оформление на обобщена таблица

По време на работата на повечето предприятия се натрупват така наречените необработени данни за дейностите. Например за търговско предприятие могат да се натрупват данни за продажби на стоки - за всяка покупка поотделно, за предприятия клетъчни комуникации- статистика на натоварването базови станциии така нататък. Много често ръководството на предприятието се нуждае от аналитична информация, която се генерира на базата на необработена информация - например, за да изчисли приноса на всеки вид продукт към приходите на предприятието или качеството на услугата в областта на дадена станция. Много е трудно да се извлече такава информация от необработена информация: трябва да стартирате много сложни SQL заявки, които отнемат много време за изпълнение и често пречат на текущата работа. Ето защо все по-често необработените данни сега се консолидират първо в хранилище за архивни данни - Data Warehouse, а след това в OLAP кубове, които са много удобни за интерактивен анализ. Най-лесният начин да мислите за OLAP кубовете е като за многоизмерни таблици, в които вместо стандартните две измерения (колони и редове, както в обикновените таблици), може да има много измерения. Терминът "разрез" обикновено се използва за описание на измервания в куб. Например маркетинговият отдел може да се нуждае от информация по време, по регион, по вид продукт, по канал за продажба и т.н. С помощта на кубове (за разлика от стандартните SQL заявки) е много лесно да получите отговори на въпроси като „колко продукта от този тип са продадени през четвъртото тримесечие на миналата година в Северозападния регион чрез регионални дистрибутори.

Разбира се, такива кубове не могат да бъдат създадени в конвенционалните бази данни. Работата с OLAP кубове изисква специализиран софтуерни продукти. SQL Server идва с OLAP база данни от Microsoft, наречена Analysis Services. Има OLAP решения от Oracle, IBM, Sybase и др.

За да работи с такива кубове, Excel има вграден клиент. На руски се казва Пивотна таблица(На графичен екрандостъпна е през менюто Данни -> Пивотна таблица), а на английски - Пивотна таблица. Съответно обектът, който този клиент представлява, се нарича PivotTable. Трябва да се отбележи, че може да работи не само с OLAP кубове, но и с обикновени данни в таблици или бази данни на Excel, но много възможности се губят.

PivotTable и PivotTable са софтуерни продукти от Panorama Software, които са придобити от Microsoft и интегрирани в Excel. Следователно работата с обекта PivotTable е малко по-различна от работата с други обекти на Excel. Често е трудно да разберете какво трябва да се направи. Затова се препоръчва активно да използвате записващото устройство за макроси, за да получавате подсказки. В същото време, когато работят с обобщени таблици, потребителите често трябва да извършват едни и същи повтарящи се операции, така че автоматизацията е необходима в много ситуации.

Как изглежда програмно работата с обобщена таблица?

Първото нещо, което трябва да направим, е да създадем PivotCache обект, който ще представлява набор от записи, извлечени от OLAP източника. Много грубо този обект PivotCache може да се сравни с QueryTable. Можете да използвате само един обект PivotCache на обект PivotTable. Обект PivotCache се създава с помощта на метода Add() на колекцията PivotCaches:

Dim PC1 като PivotCache

Задайте PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches е стандартна колекция и сред методите, които заслужават подробно разглеждане, само методът Add() може да бъде посочен в нея. Този метод приема два параметъра:

Тип на източника- задължително, дефинира типа източник на данни за обобщената таблица. Можете да посочите създаването на обобщена таблица въз основа на диапазон в Excel, данни от база данни, външен източник на данни, друга обобщена таблица и т.н. На практика обикновено има смисъл да използвате OLAP само когато има много данни - съответно имате нужда от специализирано външно хранилище (например Microsoft Analysis Services). В тази ситуация е избрана стойността xlExternal.
Изходни данни- изисква се във всички случаи, освен когато стойността на първия параметър е xlExternal. Всъщност той определя диапазона от данни, на базата на който ще бъде създадена PivotTable. Обикновено приема обект Range.

Следващата задача е да конфигурирате настройките на обекта PivotCache. Както вече споменахме, този обект е много подобен на QueryTable и неговият набор от свойства и методи е много подобен. Някои от най-важните свойства и методи:

ADOConnection- възможност за връщане на обект ADO Connection, който се създава автоматично за свързване към външен източник на данни. Използва се за допълнителни настройкисвойства на връзката.
Връзка- работи точно по същия начин като свойството на обект QueryTable със същото име. Може да приеме низ за свързване, готов обект Recordset, текстов файл,Уеб заявка. Microsoft файлЗапитване. Най-често, когато работите с OLAP, низът за свързване се записва директно (тъй като получаването на обект Recordset, например, за промяна на данни, няма много смисъл - източниците на OLAP данни почти винаги са само за четене). Например, настройването на това свойство за свързване към базата данни Foodmart (примерна база данни на Analysis Services) на сървъра LONDON може да изглежда така:

PC1.Connection = "OLEDB; Доставчик = MSOLAP.2; Източник на данни = LONDON1; Първоначален каталог = FoodMart 2000"

Имоти CommandTypeИ CommandTextте също така описват вида на командата, която се изпраща към сървъра на базата данни и текста на самата команда. Например, за да получите достъп до куба Sales и да го получите изцяло в кеша на клиента, можете да използвате код като

Имот LocalConnectionви позволява да се свържете с локален куб (*.cub файл), създаден с помощта на Excel. Разбира се, не се препоръчва използването на такива файлове за работа с „производствени“ обеми от данни - само за целите на създаването на оформления и т.н.
Имот Използвана паметвръща количество оперативна памет, използван от PivotCache. Ако PivotTable, базирана на този PivotCache, все още не е създадена и отворена, тя връща 0. Може да се използва за проверки дали вашето приложение ще работи на слаби клиенти.
Имот OLAPвръща True, ако PivotCache е свързан към OLAP сървъра.
OptimizeCache- възможност за оптимизиране на структурата на кеша. Първоначалното изтегляне на данни ще отнеме повече време, но след това скоростта може да се увеличи. Не работи за източници на OLE DB.

Останалите свойства на обекта PivotCache са същите като тези на обекта QueryTable и следователно няма да бъдат обсъждани тук.

Основният метод на обекта PivotCache е методът CreatePivotTable(). С помощта на този метод се изпълнява следващият етап - създаване на обобщена таблица (обект PivotTable). Този метод приема четири параметъра:

TableDestination- единственият задължителен параметър. Приема обект Range, в горния ляв ъгъл на който ще бъде поставена обобщената таблица.
TableName- име на обобщената таблица. Ако не е посочено, автоматично ще се генерира името на изгледа „PivotTable1“.
ReadData- ако е зададено True, тогава цялото съдържание на куба ще бъде автоматично поставено в кеша. Трябва да бъдете много внимателни с този параметър, тъй като неправилното му използване може драстично да увеличи натоварването на клиента.
Версия по подразбиране- това свойство обикновено не е посочено. Позволява ви да определите версията на създаваната обобщена таблица. По подразбиране се използва най-новата версия.

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

PC1.CreatePivotTable Range("A1")

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

площ на колоната- съдържа онези измерения („секция“, в която ще се анализират данните), чиито членове са по-малки;
зона на линията- тези измерения, които имат повече членове;
област на страницата- тези измервания, за които трябва само да филтрирате (например да показвате данни само за такъв и такъв регион или само за такава и такава година);
област за данни- всъщност централната част на масата. Тези числени данни (например количеството продажби), които анализираме.

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

PT1.CubeFields("").Ориентация = xlColumnField

След това - измерването на времето в областта на линията:

PT1.CubeFields("").Ориентация = xlRowField

След това - измерението на продукта в областта на страницата:

PT1.CubeFields("").Ориентация = xlPageField

И накрая, индикаторът (числови данни за анализ) Единични продажби:

PT1.CubeFields(“.”).Ориентация = xlDataField

Невероятно - близо...

В хода на работата ми често се налагаше да правя сложни доклади, винаги се опитвах да намеря нещо общо в тях, за да ги компилирам по-просто и универсално, дори написах и публикувах статия на тази тема „Дървото на Осипов. ” Статията ми обаче беше критикувана и те казаха, че всички проблеми, които повдигнах, отдавна са решени в MOLAP.RU v.2.4 (www.molap.rgtu.ru) и те препоръчаха да се видят обобщените таблици в EXCEL.
Оказа се толкова просто, че като приложих гениалните си малки ръчички към него, получих много проста схемаза изтегляне на данни от 1C7 или всяка друга база данни (по-нататък 1C означава всяка база данни) и анализ в OLAP.
Мисля, че много схеми за качване на OLAP са твърде сложни, аз избирам простотата.

Характеристики :

1. За работа е необходим само EXCEL 2000.
2. Потребителят може сам да проектира отчети без програмиране.
3. Качване от 1C7 в прост текстов формат.
4. За счетоводните записи вече има универсална обработка за разтоварване, която работи във всяка конфигурация. Налична е обработка на проби за изтегляне на други данни.
5. Можете да проектирате формуляри за отчети предварително и след това да ги прилагате към различни данни, без да ги препроектирате.
6. Доста добро представяне. На първия дълъг етап данните първо се импортират в EXCEL от текстов файл и се изгражда OLAP куб, след което доста бързо може да се изгради всеки отчет на базата на този куб. Например данните за продажбите на продукти за магазин за 3 месеца с асортимент от 6000 продукта се зареждат в EXCEL за 8 минути на Cel600-128M, рейтингът по продукт и група (OLAP отчет) се преизчислява за 1 минута.
7. Данните се изтеглят от 1C7 изцяло за посочения период (всички движения, във всички складове, фирми, сметки). При импортиране в EXCEL е възможно да се използват филтри, които зареждат само необходимите данни за анализ (например от всички движения, само продажби).
8. Понастоящем са разработени методи за анализиране на движения или остатъци, но не и на движения и остатъци заедно, въпреки че това е възможно по принцип.

Какво е OLAP : (www.molap.rgtu.ru)

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

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

В 1C, например, един ред от тази таблица ще съответства на един ред от фактурата; някои полета (контрагент, дата) се вземат от заглавката на фактурата.

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

Тази таблица е източникът за OLAP анализ.

Докладвай	Измервания	Данни	Филтър
Колко продукта и за каква сума се продават на ден?	Дата, продукт	Количество, сума	Преглед="разпродажба"
Кои контрагенти кои стоки са доставяли за каква сума на месец?	Месец, Изпълнител, Продукт	Сума	Преглед="покупка"
Каква сума са написали операторите за какъв тип фактури за целия отчетен период?		Сума

Потребителят сам определя кои от полетата на таблицата ще бъдат Размери, кои Данни и кои Филтри да приложи. Системата сама изгражда отчет във визуален табличен вид. Измеренията могат да бъдат поставени в заглавията на редове или колони на таблица на отчет.
Както можете да видите, от една проста таблица можете да получите много данни под формата на различни отчети.

Как да го използвате сами :

Разопаковайте данните от дистрибуцията точно в директорията c:\fixin (за система за търговия е възможно в c:\reports). Прочетете readme.txt и следвайте всички инструкции в него.

Първо трябва да напишете обработка, която качва данни от 1C в текстов файл (таблица). Трябва да определите състава на полетата, които ще бъдат разтоварени.
Например готова универсална обработка, която работи във всяка конфигурация и изтегля транзакции за период за OLAP анализ, изтегля следните полета за анализ:

Когато под префиксите Dt(Kt) има подсметки на Дебит (Кредит), Група е групата на тази подсметка (ако има), Раздел е групата на групата, Клас е групата на раздела.

За система за търговия полетата могат да бъдат както следва:

За анализ на данните се използват таблиците "Анализ на движението.xls" ("Анализ на счетоводството.xls"). Когато ги отваряте, не деактивирайте макросите, в противен случай няма да можете да актуализирате отчетите (те се изпълняват от VBA макроси). Тези файлове вземат своите изходни данни от файловете C:\fixin\motions.txt (C:\fixin\buh.txt), в противен случай са еднакви. Следователно може да се наложи да копирате данните си в един от тези файлове.
За да заредите вашите данни в EXCEL, изберете или напишете своя филтър и щракнете върху бутона „Генериране“ на листа „Условия“.
Отчетните листове започват с префикса "Отчет". Отидете до листа с отчети, щракнете върху „Опресни“ и данните в отчета ще се променят според последните заредени данни.
Ако не сте доволни от стандартните отчети, има лист с шаблон на отчет. Копирайте го в нов лист и персонализирайте типа отчет, като работите с обобщена таблица на този лист (за повече информация относно работата с обобщени таблици вижте която и да е книга за EXCEL 2000). Препоръчвам да настроите отчети за малък набор от данни и след това да ги стартирате върху голям масив, защото... Няма начин да деактивирате преначертаването на таблици при всяка промяна на оформлението на отчета.

Технически бележки :

Когато качва данни от 1C, потребителят избира папката, в която да качи файла. Направих това, защото е вероятно в близко бъдеще да има множество качени файлове (остатъци и движения). След това, като щракнете върху бутона „Изпращане“ в Explorer --> „Към OLAP анализ в EXCEL 2000“, данните се копират от избраната папка в папката C:\fixin. (за да се появи тази команда в списъка на командата „Изпращане“, трябва да копирате файла „За OLAP анализ в EXCEL 2000.bat“ в директорията C:\Windows\SendTo) Следователно, качете данните незабавно, като наименувате файловете motions.txt или buh.txt.

Формат на текстов файл:
Първият ред на текстовия файл е заглавките на колоните, разделени с "|", останалите редове съдържат стойностите на тези колони, разделени с "|".

За да импортирате текстови файлове в Excel, се използва Microsoft Query (компонент на EXCEL); за да работи, трябва да имате файл shema.ini, съдържащ следната информация в директорията за импортиране (C:\fixin):

ColNameHeader=Вярно
Формат=Разграничени(|)
MaxScanRows=3
Набор от символи=ANSI
ColNameHeader=Вярно
Формат=Разграничени(|)
MaxScanRows=3
Набор от символи=ANSI

Обяснение: motions.txt и buh.txt са името на раздела, съответства на името на импортирания файл, описва как да импортирате текстов файл в Excel. Останалите параметри означават, че първият ред съдържа имената на колоните, разделителят на колоните е "|", наборът от знаци е Windows ANSI (за DOS - OEM).
Типът на полето се определя автоматично въз основа на данните, съдържащи се в колоната (дата, номер, низ).
Списъкът с полета не е необходимо да се описва никъде - EXCEL и OLAP сами ще определят кои полета се съдържат във файла чрез заглавията в първия ред.

Внимание, проверете вашите регионални настройки "Контролен панел" --> "Регионални настройки". При моята обработка числата се качват със запетая, а датите са във формат "ДД.ММ.ГГГГ".

Когато щракнете върху бутона „Генериране“, данните се зареждат в обобщената таблица на листа „Основен“ и всички отчети в листовете „Отчет“ вземат данни от тази обобщена таблица.

Разбирам, че феновете на MS SQL Server и мощните бази данни ще започнат да мърморят, че всичко е твърде опростено, че обработката ми ще бъде изчерпана от едногодишна извадка, но преди всичко искам да дам предимствата на OLAP анализа на средни големи организации. Бих позиционирал този продукт като инструмент за годишен анализ за компании на едро, тримесечен анализ за търговци на дребно и оперативен анализ за всяка организация.

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

Описание на работата в EXCEL (за потребители):

Инструкции за използване на отчети:
1. Изпратете изтеглените данни за анализ (проверете при администратора). За да направите това, щракнете с десния бутон върху папката, в която сте изтеглили данни от 1C, и изберете командата „Изпращане“, след това „Към OLAP анализ в EXCEL 2000“.
2. Отворете файла "Motion Analysis.xls"
3. Изберете Стойност на филтъра; филтрите, от които се нуждаете, могат да бъдат добавени в раздела „Стойности“.
4. Щракнете върху бутона "Генериране" и изтеглените данни ще бъдат заредени в EXCEL.
5. След като заредите данните в EXCEL, можете да видите различни отчети. За да направите това, просто щракнете върху бутона "Обнови" в избрания отчет. Докладните листове започват с Доклад.
внимание! След като промените стойността на филтъра, трябва да щракнете отново върху бутона „Генериране“, така че данните в EXCEL да се презаредят от файла за качване в съответствие с филтрите.

Обработка от демонстрационния пример:

Обработка на motionsbuh2011.ert - най-новата версия за качване на операции от Счетоводство 7.7 за анализ в Excel. Има квадратче за отметка „Прикачване към файл“, което ви позволява да качвате данни на части по период, като ги добавяте към същия файл, вместо да ги качвате отново в същия файл:

Обработката на motionswork.ert качва данни за продажби за анализ в Excel.

Примери за отчети:

Окабеляване на шах:

Натовареност на оператора по видове фактури:

P.S. :

Ясно е, че подобна схема може да се използва за организиране на изтеглянето на данни от 1C8.
През 2011 г. потребител се свърза с мен, който трябваше да подобри тази обработка в 1C7, така че да качва големи количества данни, намерих аутсорсинг и свърших работата. Така че развитието е доста уместно.

Обработката на motionsbuh2011.ert е подобрена, за да се справи с разтоварването на големи количества данни.

Работа с OLAP куб в MS Excel
1. Получете разрешение за достъп до OLAP куба на SQL Server Analysis Services (SSAS).
2. MS Excel 2016 / 2013 / 2010 трябва да е инсталиран на вашия компютър (MS Excel 2007 също е възможен, но не е удобен за работа, а функционалността на MS Excel 2003 е много лоша)
3. Отворете MS Excel и стартирайте съветника за настройка на връзка с аналитичната услуга:

3.1 Посочете името или IP адреса на текущия OLAP сървър (понякога трябва да посочите номера отворен порт, например 192.25.25.102:80); използва се удостоверяване на домейна:

3.2 Изберете многомерна база данни и аналитичен куб (ако имате права за достъп до куба):

3.3 Настройките за свързване към аналитичната услуга ще бъдат запазени в .odc файл на вашия компютър:

3.4 Изберете типа отчет (осева таблица/графика) и посочете мястото за поставянето му:

Ако вече е създадена връзка в работна книга на Excel, можете да я използвате отново: главно меню „Данни“ -> „Съществуващи връзки“ -> изберете връзка в тази работна книга -> вмъкнете обобщена таблица в указаната клетка.
4. След като се свържете успешно с куба, можете да започнете интерактивен анализ на данни:

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

Всеки път, когато обобщената таблица се промени, MDX оператор се изпраща автоматично към OLAP сървъра и когато се изпълни, данните се връщат. Колкото по-голям и сложен е обемът на обработените данни и изчислените показатели, толкова по-дълго е времето за изпълнение на заявката. Можете да отмените изпълнението на заявка, като натиснете клавиша бягство. Последните извършени операции могат да бъдат отменени (Ctrl+Z) или върнати (Ctrl+Y).

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

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

След като йерархията е поставена в областта на редовете/колоните, е възможно да се скрият отделни нива:

При ключови атрибути(по-рядко - за атрибути по-високо в йерархията) размерите могат да имат свойства - описателни характеристики, които могат да се показват както в подсказки, така и като полета:

Ако трябва да покажете няколко свойства на полето наведнъж, можете да използвате съответния диалогов списък:

Дефинирани от потребителя комплекти

Excel 2010 въведе възможността за интерактивно създаване на ваши собствени (дефинирани от потребителя) набори от членове на измерение:

За разлика от наборите, създадени и съхранени централно от страната на куба, персонализираните набори се записват локално в работната книга на Excel и могат да се използват по-късно:

Напредналите потребители могат да създават набори с помощта на MDX конструкции:

Задаване на свойства на обобщена таблица

Като използвате елемента "Опции на обобщената таблица..." в контекстното меню (щракнете с десния бутон в рамките на обобщената таблица), можете да конфигурирате обобщената таблица, например:
- раздел "Изход", опция "Класическо оформление на обобщена таблица" - обобщената таблица става интерактивна, можете да плъзгате полета (Drag&Drop);
- раздел "Изход", опция "Показване на елементи без данни в редове" - осевата таблица ще показва празни редове, които не съдържат нито една индикаторна стойност за съответните елементи на размерността;
- раздел "Оформление и формат", опция "Запазване на форматирането на клетка при актуализиране" - в обобщената таблица можете да замените и запазите формата на клетката при актуализиране на данни;

Създайте PivotCharts

За съществуваща OLAP обобщена таблица можете да създадете обобщена диаграма - кръгова, лентова, хистограма, графика, точкова диаграма и други видове диаграми:

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

Създаване на табла за управление

Изберете оригиналната обобщена таблица, копирайте я в клипборда (Ctrl+C) и поставете нейно копие (Ctrl+V), в което променяме състава на индикаторите:

За едновременно управление на няколко обобщени таблици ще вмъкнем слайсер (нова функционалност, налична от MS Excel 2010). Нека свържем нашия Slicer с осеви таблици - щракнете с десния бутон върху среза, изберете контекстно менюЕлемент „Връзки към обобщена таблица...“. Трябва да се отбележи, че може да има няколко панела за нарязване, които могат да обслужват едновременно осеви таблици различни листове, което ви позволява да създавате координирани информационни панели (Dashboard).

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

Изпълнение на MDX заявка от Excel

На първо място, трябва да извършите операцията DRILLTHROUGH на някакъв индикатор, т.е. отидете до подробните данни (подробните данни се показват на отделен лист) и отворете списъка с връзки;
Отворете свойствата на връзката, отидете на раздела „Дефиниция“;
Изберете типа команда по подразбиране и поставете предварително подготвена команда в текстовото поле на командата MDXискане;
Когато щракнете върху бутона, след проверка на правилността на синтаксиса на заявката и наличието на подходящи права за достъп, заявката ще бъде изпълнена на сървъра и резултатът ще бъде представен в текущия лист под формата на обикновена плоска таблица.
Можете да видите текста на MDX заявката, генерирана от Excel, като използвате инсталацията безплатна добавка, което предоставя и други допълнителни функции.

Превод на други езици

Аналитичният куб поддържа локализация на руски и английски езики (ако е необходимо, е възможна локализация на други езици). Преводите се прилагат за имената на измерения, йерархии, атрибути, папки, мерки, както и елементи от отделни йерархии, ако преводите са налични за тях от страна на счетоводните системи/склада на данни. За да промените езика, трябва да отворите свойствата на връзката и да добавите следната опция в реда за връзка:
Разширени свойства="Locale=1033"
където 1033 е локализация на английски
1049 - локализация на руски език

Допълнителни разширения на Excel за Microsoft OLAP

Възможността за работа с Microsoft OLAP кубове ще се увеличи, ако използвате допълнителни разширения, например OLAP PivotTable Extensions, благодарение на които можете да използвате бързо търсенепо измерване:

уебсайт 2011-01-11 16:57:00Z Последна промяна: 2017-10-15 16:33:59Z Възрастова аудитория: 14-70