Многомерные кубы в olap. Проектирование кубов данных

08.04.2019

Возможно, для кого-то использование OLAP-технологии (On-line Analytic Processing) при построении отчетности покажется какой-то экзотикой, поэтому применение OLAP-КУБа для них вовсе не является одним из важнейших требований при автоматизации бюджетирования и управленческого учета .

На самом деле очень удобно пользоваться многомерным КУБом при работе с управленческой отчетностью. При разработке форматов бюджетов можно столкнуться с проблемой многовариантности форм (подробнее об этом можно прочитать в Книге 8 "Технология постановки бюджетирования в компании" и в книге "Постановка и автоматизация управленческого учета").

Это связано с тем, что для эффективного управления компанией требуется все более детализированная управленческая отчетность. То есть в системе используется все больше различных аналитических срезов (в информационных системах аналитики определяются набором справочников).

Естественно, это приводит к тому, что руководители хотят получать отчетность во всех интересующих их аналитических срезах. А это значит, что отчеты нужно как-то заставить «дышать». Иными словами можно сказать, что в данном случае речь идет о том, что по смыслу один и тот же отчет должен предоставлять информацию в различных аналитических разрезах. Поэтому статичные отчеты уже не устраивают многих современных руководителей. Им нужна динамика, которую может дать многомерный КУБ.

Таким образом, OLAP-технология уже сейчас стала обязательным элементом в современных и перспективных информационных системах. Поэтому при выборе программного продукта нужно обращать внимание на то, используется ли в нем OLAP-технология.

Причем нужно уметь отличать настоящие КУБы от имитации. Одной из таких имитаций являются сводные таблицы в MS Excel. Да, этот инструмент похож на КУБ, но на самом деле таковым не является, поскольку это статические, а не динамические таблицы. Кроме того, в них гораздо хуже реализована возможность построения отчетов, использующих элементы из иерархических справочников.

Для подтверждения актуальности использования КУБа при построении управленческой отчетности можно привести простейший пример с бюджетом продаж. В рассматриваемом примере для компании актуальными являются следующие аналитические срезы: продукты, филиалы и каналы сбыта. Если для компании важны эти три аналитики, то бюджет (или отчет) продаж можно выводить в нескольких вариантах.

Следует отметить, что если создавать строки бюджетов на основе трех аналитических срезов (как в рассматриваемом примере), это позволяет создавать достаточно сложные бюджетные модели и составлять детализированные отчеты с использованием КУБа.

Например, бюджет продаж можно составлять с использованием только одной аналитики (справочника). Пример бюджета продаж, построенного на основе одной аналитики "Продукты" представлен на рисунке 1 .

Рис. 1. Пример бюджета продаж, построенного на основе одной аналитики "Продукты" в OLAP-КУБе

Этот же бюджет продаж можно составлять с использованием двух аналитик (справочников). Пример бюджета продаж, построенного на основе двух аналитик "Продукты" и "Филиалы" представлен на рисунке 2 .

Рис. 2. Пример бюджета продаж, построенного на основе двух аналитик "Продукты" и "Филиалы" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

Если есть необходимость строить более детальные отчеты, то можно тот же бюджет продаж составлять с использованием трех аналитик (справочников). Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Филиалы" и "Каналы сбыта" представлен на рисунке 3 .

Рис. 3. Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Филиалы" и "Каналы сбыта" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

Нужно напомнить о том, что КУБ, используемый для формирования отчетов, позволяет выводить данные в различной последовательности. На рисунке 3 бюджет продаж сначала "разворачивается" по продуктам, затем по филиалам, а потом по каналам сбыта.

Те же самые данные можно представить в другой последовательности. На рисунке 4 тот же самый бюджет продаж "разворачивается" сначала по продуктам, затем по каналам сбыта, а потом по филиалам.

Рис. 4. Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Каналы сбыта" и "Филиалы" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

На рисунке 5 тот же самый бюджет продаж "разворачивается" сначала по филиалам, затем по продуктам, а потом по каналам сбыта.

Рис. 5. Пример бюджета продаж, построенного на основе трех аналитик "Филиалы", "Продукты" и "Каналы сбыта" в OLAP-КУБепрограммного комплекса "ИНТЕГРАЛ"

На самом деле это не все возможные варианты вывода бюджета продаж.

Кроме того, нужно обратить внимание на то, что КУБ позволяет работать с иерархической структурой справочников. В представленных примерах иерархическими справочниками являются "Продукты" и "Каналы сбыта".

С точки зрения пользователя он в данном примере получает несколько управленческих отчетов (см. Рис. 1-5 ), а с точки зрения настроек в программном продукте – это один отчет. Просто с помощью КУБа его можно просматривать несколькими способами.

Естественно, что на практике возможно очень большое количество вариантов вывода различных управленческих отчетов, если их статьи строятся на одной или нескольких аналитиках. А уж сам набор аналитик зависит от потребности пользователей в детализации. Правда, при этом не следует забывать, что, с одной стороны, чем больше аналитик, тем более детализированные отчеты можно строить. Но, с другой стороны, значит, и финансовая модель бюджетирования будет более сложной. В любом случае при наличии КУБа компания будет иметь возможность просмотра необходимой отчетности в различных вариантах, в соответствии с интересующими аналитическими разрезами.

Необходимо упомянуть еще о нескольких возможностях OLAP-КУБа.

В многомерном иерархическом OLAP-КУБе есть несколько измерений: тип строки, дата, строки, справочник 1, справочник 2 и справочник 3 (см. Рис. 6 ). Естественно, в отчет выводится столько кнопок со справочниками, сколько есть в строке бюджета, содержащей максимальное количество справочников. Если ни в одной строке бюджета нет ни одного справочника, то в отчете не будет ни одной кнопки со справочниками.

Изначально OLAP-КУБ строится по всем измерениям. По умолчанию при первоначальном построении отчета измерения расположены именно в тех областях, как показано на рисунке 6 . То есть такое измерение, как «Дата», располагается в области вертикальных измерений (измерения в области столбцов), измерения «Строки», «Справочник 1», «Справочник 2» и «Справочник 3» – в области горизонтальных измерений (измерения в области строк), а измерение «Тип строки» – в области «нераскрываемых» измерений (измерения в страничной области). Если измерение находится в последней области, то данные в отчете не будут «раскрываться» по этому измерению.

Каждое из этих измерений можно поместить в любую из трех областей. После переноса измерений отчет мгновенно перестраивается в соответствии с новой конфигурацией измерений. Например, можно поменять местами дату и строки со справочниками. Или можно в вертикальную область измерений перенести один из справочников (см. Рис. 7 ). Иными словами, отчет в OLAP-КУБе можно «крутить» и выбирать тот вариант вывода отчета, который является наиболее удобным для пользователя.

Рис. 7. Пример перестройки отчета после изменения конфигурации измерений программного комплекса "ИНТЕГРАЛ"

Конфигурацию измерений можно менять либо в основной форме КУБа, либо в редакторе карты изменений (см. Рис. 8 ). В этом редакторе также можно мышкой перетаскивать измерения из одной области в другую. Помимо этого, можно менять местами измерения в одной области.

Кроме того, в этой же форме можно настраивать некоторые параметры измерений. По каждому измерению можно настраивать расположение итогов, порядок сортировки элементов и названия элементов (см. Рис. 8 ). Также можно задавать, какое название элементов выводить в отчет: сокращенное (Name) или полное (FullName).

Рис. 8. Редактор карты измерений программного комплекса "ИНТЕГРАЛ"

Редактировать параметры измерений можно непосредственно в каждом из них (см. Рис. 9 ). Для этого нужно нажать на пиктограмму, расположенную на кнопке рядом с названием измерения.

Рис. 9. Пример редактирования справочника 1 Продукты и услуги в

С помощью этого редактора можно выбирать элементы, которые нужно показывать в отчете. По умолчанию в отчет выводятся все элементы, но при необходимости часть элементов или папок можно не показывать. Например, если нужно выводить в отчет только одну продуктовую группу, то у всех остальных необходимо убрать галочки в редакторе измерений. После чего в отчете будет только одна продуктовая группа (см. Рис. 10 ).

Также в этом редакторе можно сортировать элементы. Кроме того, элементы можно перегруппировывать различными способами. После такой перегруппировки отчет мгновенно перестраивается.

Рис. 10. Пример вывода в отчете только одной продуктовой группы (папки) в программном комплексе "ИНТЕГРАЛ"

В редакторе измерения можно оперативно создавать свои группы, перетаскивать туда элементы из справочников и т.д. По умолчанию автоматически создается только группа «Прочие», но можно создавать и другие группы. Таким образом, с помощью редактора измерений можно настраивать, какие элементы справочников и в каком порядке нужно выводить в отчет.

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

На самом деле все такие изменения можно было сделать изначально при настройке строк.

Например, с помощью ограничений также можно задавать, какие элементы или группы справочников нужно выводить в отчет, а какие – нет.

Примечание : более подробно тема данной статьи рассматривается на семинарах-практикумах "Бюджетное управление предприятием" и "Постановка и автоматизация управленческого учета" , которые проводит автор данной статьи - Александр Карпов .

Если пользователю практически регулярно нужно выводить в отчет только определенные элементы или папки справочников, то подобные настройки лучше заранее сделать при создании строк отчетов. Если же для пользователя важны различные комбинации элементов справочников в отчетах, тогда при настройке методики никакие ограничения ставить не нужно. Все такие ограничения можно будет оперативно настраивать с помощью редактора измерения.

Данных, как правило, разрежённый и долговременно хранимый. Может быть реализован на основе универсальных реляционных СУБД или специализированным программным обеспечением (см. также OLAP). В программных продуктах компании SAP используется термин «инфокуб».

Индексам массива соответствуют измерения (dimensions) или оси куба, а значениям элементов массива - меры (measures) куба.

w : (x ,y ,z ) → w xyz ,

где x , y , z - измерения, w - мера.

В отличие от обычного массива в языке программирования, доступ к элементам- OLAP-куба может осуществляться как по полному набору индексов-измерений, так и по их подмножеству, и тогда результатом будет не один элемент, а их множество.

W : (x ,y ) → W = {w z1 , w z2 , …, w zn }

Также известно описание OLAP-куба с использованием терминологии реляционной алгебры, как проекции отношений .

См. также

Wikimedia Foundation . 2010 .

Схема звезды
Наш дом - Россия (фракция)

Смотреть что такое "OLAP-куб" в других словарях:

OLAP куб - … Википедия

OLAP - (англ. online analytical processing, аналитическая обработка в реальном времени) технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по… … Википедия

Куб (значения) - Куб многозначный термин: В математике В стереометрии куб шестигранный правильный многогранник В алгебре третья степень числа Фильм Серия фантастических фильмов: «Куб» «Куб 2: Гиперкуб» «Куб Ноль» Сленг и жаргон медицинское… … Википедия

Куб - У этого термина существуют и другие значения, см. Куб (значения). Куб Тип Правильный многогранник Грань квадрат … Википедия

Mondrian - OLAP Server Тип OLAP сервер Разработчик Pentaho Операционная система кроссплатформенное программное обеспечение Последняя версия 3.4.1 (2012 05 07) Лицензия свободное программное обеспечение … Википедия - Информационно аналитическая система автоматизированная система позволяющая экспертам быстро анализировать большие объемы данных, как правило является одним из элементов ситуационных центров. Так же, иногда в состав ИАС включают систему сбора… … Википедия

Довольно давно являюсь обитателем Хабра, но так и не доводилось читать статьи на тему многомерных кубов, OLAP и MDX, хотя тема очень интересная и с каждым днем становится все более актуальной.
Не секрет, что за тот небольшой промежуток времени развития баз данных, электронного учета и онлайн систем, самих данных накопилось очень много. Теперь же интерес также представляет полноценный анализ архивов, а возможно и попытка прогнозирования ситуаций для подобных моделей в будущем.
С другой стороны, большие компании даже за несколько лет, месяцев или даже недель могут накапливать настолько большие массивы данных, что даже их элементарный анализ требует неординарных подходов и жестких аппаратных требований. Такими могут быть системы обработки банковских транзакций, биржевые агенты, телефонные операторы и т.д.
Думаю, всем хорошо известны 2 разных подхода построения дизайна баз данных: OLTP и OLAP. Первый подход (Online Transaction Processing - обработка транзакций в реальном времени) рассчитан на эффективный сбор данных в реальном времени, второй же (Online Analytical Processing – аналитическая обработка в реальном времени) нацелен именно на выборку и обработку данных максимально эффективным способом.

Давайте рассмотрим основные возможности современных OLAP кубов, и какие задачи они решают (за основу взяты Analysis Services 2005/2008):

быстрый доступ к данным
преагрегация
иерархии
работа с временем
язык доступа к многомерным данным
KPI (Key Performance Indicators)
дата майнинг
многоуровневое кэширование
поддержка мультиязычности

Итак, рассмотрим возможности OLAP кубов немного подробнее.

Немного подробнее о возможностях

Быстрый доступ к данным
Собственно быстрый доступ к данным, независимо от размеров массива, и является основой OLAP систем. Так как основной упор именно на этом, хранилище данных обычно строится по принципам, отличным от принципов реляционных баз данных.
Здесь, время на выборку простых данных измеряется в долях секунды, а запрос, превышающий несколько секунд, скорее всего, требует оптимизации.

Преагрегация
Кроме быстрой выборки существующих данных, также предоставляется возможность преагрегировать «наиболее вероятно-используемые» значения. Например, если мы имеем ежедневные записи о продажах какого-то товара, система может преагрегировать нам также месячные и квартальные суммы продаж, а значит, если мы запросим данные помесячно или поквартально, система нам мгновенно выдаст результат. Почему же преагрегация происходит не всегда – потому, что теоретически возможных комбинаций товаров/времени/и т.д. может быть огромное количество, а значит, нужно иметь четкие правила для каких элементов агрегация будет построена, а для каких нет. Вообще тема учета этих правил и собственно непосредственного дизайна агрегаций довольно обширна и сама по себе заслуживает отдельную статью.

Иерархии
Закономерно, что анализируя данные и строя конечные отчеты, возникает потребность учитывать то, что месяцы состоят из дней, а сами образуют кварталы, а города входят в области, которые в свою очередь являются частью регионов или стран. Хорошая новость то, что OLAP кубы изначально рассматривают данные с точки зрения иерархий и взаимоотношений с другими параметрам одной и той же сущности, так что построение и использования иерархией в кубах – дело очень простое.

Работа с временем
Так как в основном анализ данных происходит на временных участках, именно времени в OLAP системах выделено особое значение, а значит, просто определив для системы, где у нас тут время, в дальнейшем можно с легкостью пользоваться функциями типа Year To Date, Month To Date (период от начала года/месяца и до текущей даты), Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.

Язык доступа к многомерным данным
MDX (Multidimensional Expressions) - язык запросов для простого и эффективного доступа к многомерным структурам данных. И этим все сказано – внизу будет несколько примеров.

Key Performance Indicators (KPI)
Ключевые показатели эффективности - это финансовая и нефинансовая система оценки, которая помогает организации определить достижение стратегических целей. Ключевые показатели эффективности могут быть достаточно просто определены в OLAP системах и использоваться в отчетах.

Дата майнинг
Интеллектуальный анализ данных (Data Mining) - по сути, выявление скрытых закономерностей или взаимосвязей между переменными в больших массивах данных.
Английский термин «Data Mining» не имеет однозначного перевода на русский язык (добыча данных, вскрытие данных, информационная проходка, извлечение данных/информации) поэтому в большинстве случаев используется в оригинале. Наиболее удачным непрямым переводом считается термин «интеллектуальный анализ данных» (ИАД). Впрочем, это отдельная, не менее интересная тема для рассмотрения.

Многоуровневое кэширование
Собственно для обеспечения наиболее высокой скорости доступа к данным, кроме хитрых структур данных и преагрегаций, OLAP системы поддерживают многоуровневое кэширование. Кроме кэширования простых запросов, также кэшируются части вычитанных из хранилища данных, агрегированные значения, вычисленные значения. Таким образом, чем дольше работаешь с OLAP кубом, тем быстрее он, по сути, начинает работать. Также существует понятие «разогрев кэша» - операция, подготавливающая OLAP систему к работе с конкретными отчетами, запросами или всем вместе взятым.

Поддержка мультиязычности
Да-да-да. Как минимум Analysis Services 2005/2008 (правда, Enterprise Edition) нативно поддерживают мультиязычность. Достаточно привести перевод строковых параметров ваших данных, и клиенту, указавшему свой язык, будут приходить локализированные данные.

Многомерные кубы

Так что же все-таки эти многомерные кубы?
Представим себе 3-х мерное пространство, у которого по осям Время, Товары и Покупатели.
Точка в таком пространстве будет задавать факт того, что кто-то из покупателей в каком-то месяце купил какой-то конкретный товар.

Фактически, плоскость (или множество всех таких точек) и будет являться кубом, а, соответственно, Время, Товары и Покупатели – его измерениями.
Представить (и нарисовать) четырехмерный и более куб немного сложнее, но суть от этого не меняется, а главное, для OLAP систем совершенно неважно в скольких измерениях вы будете работать (в разумных пределах, конечно).

Немного MDX

Итак, в чем же прелесть MDX – скорее всего в том, что описывать нужно не то как мы хотим выбрать данные, а что именно мы хотим.
Например,

SELECT
{ . } ON COLUMNS,
{ ., . } ON ROWS
FROM
WHERE (., .)

Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?

А вот чуть посложнее:

WITH MEMBER AverageSpend AS
. / .
SELECT
{ AverageSpend } ON COLUMNS,
{ .., .. } ON ROWS
FROM
WHERE (.)
* This source code was highlighted with Source Code Highlighter .

Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.

Сам язык чрезвычайно интересен и для изучения и для использования, и, пожалуй, заслуживает немало обсуждений.

Заключение

На самом деле, данная статья очень мало покрывает даже базовых понятий, я бы назвал ее «appetizer» - возможность заинтересовать хабра-сообщество данной тематикой и развивать ее дальше. Что же касается развития – тут огромное непаханое поле, а я буду рад ответить на все интересующие вопросы.

P.S. Это мой первый пост об OLAP и первая публикацию на Хабре - буду очень признателен за конструктивный фидбек.
Update: Перенес в SQL, перенесу в OLAP как только разрешат создавать новые блоги.

Теги: Добавить метки

В цикле статей «Введение в базы данных», публиковавшемся в последнее время (см. КомпьютерПресс №3’2000 - 3’2001), мы обсуждали различные технологии и программные средства, применяемые при создании информационных систем - настольные и серверные СУБД, средства проектирования данных, средства разработки приложений, а также Business Intelligence - средства анализа и обработки данных масштаба предприятия, которые в настоящее время становятся все более популярными в мире, в том числе и в нашей стране. Отметим, однако, что вопросы применения средств Business Intelligence и технологии, используемые при создании приложений такого класса, в отечественной литературе пока еще освещены недостаточно. В новом цикле статей мы попробуем восполнить этот пробел и рассказать о том, что представляют собой технологии, лежащие в основе подобных приложений. В качестве примеров реализации мы будем использовать в основном OLAP-технологии фирмы Microsoft (главным образом Analysis Services в Microsoft SQL Server 2000), но надеемся, что основная часть материала будет полезна и пользователям других средств.

Первая статья в данном цикле посвящена основам OLAP (On-Line Analytical Processing) - технологии многомерного анализа данных. В ней мы рассмотрим концепции хранилищ данных и OLAP, требования к хранилищам данных и OLAP-средствам, логическую организацию OLAP-данных, а также основные термины и понятия, применяемые при обсуждении многомерного анализа.

Что такое хранилище данных

Информационные системы масштаба предприятия, как правило, содержат приложения, предназначенные для комплексного многомерного анализа данных, их динамики, тенденций и т.п. Такой анализ в конечном итоге призван содействовать принятию решений. Нередко эти системы так и называются - системы поддержки принятия решений.

Принять любое управленческое решение невозможно не обладая необходимой для этого информацией, обычно количественной. Для этого необходимо создание хранилищ данных (Data warehouses), то есть процесс сбора, отсеивания и предварительной обработки данных с целью предоставления результирующей информации пользователям для статистического анализа (а нередко и создания аналитических отчетов).

Ральф Кимбалл (Ralph Kimball), один из авторов концепции хранилищ данных, описывал хранилище данных как «место, где люди могут получить доступ к своим данным» (см., например, Ralph Kimball, «The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses», John Wiley & Sons, 1996 и «The Data Webhouse Toolkit: Building the Web-Enabled Data Warehouse», John Wiley & Sons, 2000). Он же сформулировал и основные требования к хранилищам данных:

поддержка высокой скорости получения данных из хранилища;
поддержка внутренней непротиворечивости данных;
возможность получения и сравнения так называемых срезов данных (slice and dice);
наличие удобных утилит просмотра данных в хранилище;
полнота и достоверность хранимых данных;
поддержка качественного процесса пополнения данных.

Удовлетворять всем перечисленным требованиям в рамках одного и того же продукта зачастую не удается. Поэтому для реализации хранилищ данных обычно используется несколько продуктов, одни их которых представляют собой собственно средства хранения данных, другие - средства их извлечения и просмотра, третьи - средства их пополнения и т.д.

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

Во-вторых, обычные базы данных подвержены постоянным изменениям в процессе работы пользователей, а хранилище данных относительно стабильно: данные в нем обычно обновляются согласно расписанию (например, еженедельно, ежедневно или ежечасно - в зависимости от потребностей). В идеале процесс пополнения представляет собой просто добавление новых данных за определенный период времени без изменения прежней информации, уже находящейся в хранилище.

И в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.

Что такое OLAP

Системы поддержки принятия решений обычно обладают средствами предоставления пользователю агрегатных данных для различных выборок из исходного набора в удобном для восприятия и анализа виде. Как правило, такие агрегатные функции образуют многомерный (и, следовательно, нереляционный) набор данных (нередко называемый гиперкубом или метакубом), оси которого содержат параметры, а ячейки - зависящие от них агрегатные данные . Вдоль каждой оси данные могут быть организованы в виде иерархии, представляющей различные уровни их детализации. Благодаря такой модели данных пользователи могут формулировать сложные запросы, генерировать отчеты, получать подмножества данных.

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP - это ключевой компонент организации хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных (см. E.F. Codd, S.B. Codd, and C.T.Salley, Providing OLAP (on-line analytical processing) to user-analysts: An IT mandate. Technical report, 1993). В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information - быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

предоставление пользователю результатов анализа за приемлемое время (обычно не более 5 с), пусть даже ценой менее детального анализа;
возможность осуществления любого логического и статистического анализа, характерного для данного приложения, и его сохранения в доступном для конечного пользователя виде;
многопользовательский доступ к данным с поддержкой соответствующих механизмов блокировок и средств авторизованного доступа;
многомерное концептуальное представление данных, включая полную поддержку для иерархий и множественных иерархий (это - ключевое требование OLAP);
возможность обращаться к любой нужной информации независимо от ее объема и места хранения.

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

Многомерные кубы

В данном разделе мы более подробно рассмотрим концепцию OLAP и многомерных кубов. В качестве примера реляционной базы данных, который мы будем использовать для иллюстрации принципов OLAP, воспользуемся базой данных Northwind, входящей в комплекты поставки Microsoft SQL Server или Microsoft Access и представляющей собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, компаниях, осуществляющих доставку, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании. Подробное описание базы данных Northwind можно найти в справочных системах Microsoft SQL Server или Microsoft Access - здесь за недостатком места мы его не приводим.

Для рассмотрения концепции OLAP воспользуемся представлением Invoices и таблицами Products и Categories из базы данных Northwind, создав запрос, в результате которого получим подробные сведения о всех заказанных товарах и выписанных счетах:

SELECT dbo.Invoices.Country, dbo.Invoices.City, dbo.Invoices.CustomerName, dbo.Invoices.Salesperson, dbo.Invoices.OrderDate, dbo.Categories.CategoryName, dbo.Invoices.ProductName, dbo.Invoices.ShipperName, dbo.Invoices.ExtendedPrice FROM dbo.Products INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN dbo.Invoices ON dbo.Products.ProductID = dbo.Invoices.ProductID

В Access 2000 аналогичный запрос имеет вид:

SELECT Invoices.Country, Invoices.City, Invoices.Customers.CompanyName AS CustomerName, Invoices.Salesperson, Invoices.OrderDate, Categories.CategoryName, Invoices.ProductName, Invoices.Shippers.CompanyName AS ShipperName, Invoices.ExtendedPrice FROM Categories INNER JOIN (Invoices INNER JOIN Products ON Invoices.ProductID = Products.ProductID) ON Categories.CategoryID = Products.CategoryID;

Этот запрос обращается к представлению Invoices, содержащему сведения обо всех выписанных счетах, а также к таблицам Categories и Products, содержащим сведения о категориях продуктов, которые заказывались, и о самих продуктах соответственно. В результате этого запроса мы получим набор данных о заказах, включающий категорию и наименование заказанного товара, дату размещения заказа, имя сотрудника, выписавшего счет, город, страну и название компании-заказчика, а также наименование компании, отвечающей за доставку.

Для удобства сохраним этот запрос в виде представления, назвав его Invoices1. Результат обращения к этому представлению приведен на рис. 1 .

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

Какова суммарная стоимость заказов, сделанных клиентами из Франции?
Какова суммарная стоимость заказов, сделанных клиентами из Франции и доставленных компанией Speedy Express?
Какова суммарная стоимость заказов, сделанных клиентами из Франции в 1997 году и доставленных компанией Speedy Express?

Переведем эти вопросы в запросы на языке SQL (табл. 1).

Результатом любого из перечисленных выше запросов является число. Если в первом из запросов заменить параметр ‘France’ на ‘Austria’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных (ниже показан фрагмент):

Country	SUM (ExtendedPrice)
Argentina	7327.3
Austria	110788.4
Belgium	28491.65
Brazil	97407.74
Canada	46190.1
Denmark	28392.32
Finland	15296.35
France	69185.48
Germany	209373.6
…	…

Полученный набор агрегатных значений (в данном случае - сумм) может быть интерпретирован как одномерный набор данных. Этот же набор данных можно получить и в результате запроса с предложением GROUP BY следующего вида:

SELECT Country, SUM (ExtendedPrice) FROM invoices1 GROUP BY Country

Теперь обратимся ко второму из приведенных выше запросов, который содержит два условия в предложении WHERE. Если выполнять этот запрос, подставляя в него все возможные значения параметров Country и ShipperName, мы получим двухмерный набор данных следующего вида (ниже показан фрагмент):

	ShipperName
Country	Federal Shipping	Speedy Express	United Package
Argentina	1 210.30	1 816.20	5 092.60
Austria	40 870.77	41 004.13	46 128.93
Belgium	11 393.30	4 717.56	17 713.99
Brazil	16 514.56	35 398.14	55 013.08
Canada	19 598.78	5 440.42	25 157.08
Denmark	18 295.30	6 573.97	7 791.74
Finland	4 889.84	5 966.21	7 954.00
France	28 737.23	21 140.18	31 480.90
Germany	53 474.88	94 847.12	81 962.58
…	…	…	…

Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей (cross table, crosstab). Создавать подобные таблицы позволяют многие электронные таблицы и настольные СУБД - от Paradox для DOS до Microsoft Excel 2000. Вот так, например, выглядит подобный запрос в Microsoft Access 2000:

TRANSFORM Sum(Invoices1.ExtendedPrice) AS SumOfExtendedPrice SELECT Invoices1.Country FROM Invoices1 GROUP BY Invoices1.Country PIVOT Invoices1.ShipperName;

Агрегатные данные для подобной сводной таблицы можно получить и с помощью обычного запроса GROUP BY:

SELECT Country,ShipperName, SUM (ExtendedPrice) FROM invoices1 GROUP BY COUNTRY,ShipperName Отметим, однако, что результатом этого запроса будет не сама сводная таблица, а лишь набор агрегатных данных для ее построения (ниже показан фрагмент):

Country	ShipperName	SUM (ExtendedPrice)
Argentina	Federal Shipping	845.5
Austria	Federal Shipping	35696.78
Belgium	Federal Shipping	8747.3
Brazil	Federal Shipping	13998.26
…	…	…

Третий из рассмотренных выше запросов имеет уже три параметра в условии WHERE. Варьируя их, мы получим трехмерный набор данных (рис. 2).

Ячейки куба, показанного на рис. 2 , содержат агрегатные данные, соответствующие находящимся на осях куба значениям параметров запроса в предложении WHERE.

Можно получить набор двухмерных таблиц с помощью сечения куба плоскостями, параллельными его граням (для их обозначения используют термины cross-sections и slices).

Очевидно, что данные, содержащиеся в ячейках куба, можно получить и с помощью соответствующего запроса с предложением GROUP BY. Кроме того, некоторые электронные таблицы (в частности, Microsoft Excel 2000) также позволяют построить трехмерный набор данных и просматривать различные сечения куба, параллельные его грани, изображенной на листе рабочей книги (workbook).

Если в предложении WHERE содержится четыре или более параметров, результирующий набор значений (также называемый OLAP-кубом) может быть 4-мерным, 5-мерным и т.д.

Рассмотрев, что представляют собой многомерные OLAP-кубы, перейдем к некоторым ключевым терминам и понятиям, используемым при многомерном анализе данных.

Некоторые термины и понятия

Наряду с суммами в ячейках OLAP-куба могут содержаться результаты выполнения иных агрегатных функций языка SQL, таких как MIN, MAX, AVG, COUNT, а в некоторых случаях - и других (дисперсии, среднеквадратичного отклонения и т.д.). Для описания значений данных в ячейках используется термин summary (в общем случае в одном кубе их может быть несколько), для обозначения исходных данных, на основе которых они вычисляются, - термин measure, а для обозначения параметров запросов - термин dimension (переводимый на русский язык обычно как «измерение», когда речь идет об OLAP-кубах, и как «размерность», когда речь идет о хранилищах данных). Значения, откладываемые на осях, называются членами измерений (members).

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

Поскольку в рассмотренном примере в общем случае в каждой стране может быть несколько городов, а в городе - несколько клиентов, можно говорить об иерархиях значений в измерениях. В этом случае на первом уровне иерархии располагаются страны, на втором - города, а на третьем - клиенты (рис. 3).

Отметим, что иерархии могут быть сбалансированными (balanced), как, например, иерархия, представленная на рис. 3 , а также иерархии, основанные на данных типа «дата-время», и несбалансированными (unbalanced). Типичный пример несбалансированной иерархии - иерархия типа «начальник-подчиненный» (ее можно построить, например, используя значения поля Salesperson исходного набора данных из рассмотренного выше примера), представлен на рис. 4 .

Иногда для таких иерархий используется термин Parent-child hierarchy.

Существуют также иерархии, занимающие промежуточное положение между сбалансированными и несбалансированными (они обозначаются термином ragged - «неровный»). Обычно они содержат такие члены, логические «родители» которых находятся не на непосредственно вышестоящем уровне (например, в географической иерархии есть уровни Country, City и State, но при этом в наборе данных имеются страны, не имеющие штатов или регионов между уровнями Country и City; рис. 5).

Отметим, что несбалансированные и «неровные» иерархии поддерживаются далеко не всеми OLAP-средствами. Например, в Microsoft Analysis Services 2000 поддерживаются оба типа иерархии, а в Microsoft OLAP Services 7.0 - только сбалансированные. Различным в разных OLAP-средствах может быть и число уровней иерархии, и максимально допустимое число членов одного уровня, и максимально возможное число самих измерений.

Заключение

В данной статье мы ознакомились с основами OLAP. Мы узнали следующее:

Назначение хранилищ данных - предоставление пользователям информации для статистического анализа и принятия управленческих решений.
Хранилища данных должны обеспечивать высокую скорость получения данных, возможность получения и сравнения так называемых срезов данных, а также непротиворечивость, полноту и достоверность данных.
OLAP (On-Line Analytical Processing) является ключевым компонентом построения и применения хранилищ данных. Эта технология основана на построении многомерных наборов данных - OLAP-кубов, оси которого содержат параметры, а ячейки - зависящие от них агрегатные данные.
Приложения с OLAP-функциональностью должны предоставлять пользователю результаты анализа за приемлемое время, осуществлять логический и статистический анализ, поддерживать многопользовательский доступ к данным, осуществлять многомерное концептуальное представление данных и иметь возможность обращаться к любой нужной информации.

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

В следующей статье данного цикла мы рассмотрим типичную структуру хранилищ данных, поговорим о том, что представляет собой клиентский и серверный OLAP, а также остановимся на некоторых технических аспектах многомерного хранения данных.

КомпьютерПресс 4"2001

Главная Термины Статьи Курсы Опыт компаний Блог Советы Скачать Партнерам Контакты Акции

Статьи > Автоматизация бюджетирования и управленческого учета >

Александр Карпов, руководитель проекта bud-tech.ru, автор серии книг «100% практического бюджетирования» и книги «Постановка и автоматизация управленческого учета»

www.bud-tech.ru

На самом деле очень удобно пользоваться многомерным КУБом при работе с управленческой отчетностью. При разработке форматов бюджетов можно столкнуться с проблемой многовариантности форм (подробнее об этом можно прочитать в Книге 8 «Технология постановки бюджетирования в компании» и в книге «Постановка и автоматизация управленческого учета»).

Например, бюджет продаж можно составлять с использованием только одной аналитики (справочника). Пример бюджета продаж, построенного на основе одной аналитики «Продукты» представлен на рисунке 1 .

Рис. 1. Пример бюджета продаж, построенного на основе одной аналитики «Продукты» в OLAP-КУБе программного комплекса «ИНТЕГРАЛ»

Этот же бюджет продаж можно составлять с использованием двух аналитик (справочников). Пример бюджета продаж, построенного на основе двух аналитик «Продукты» и «Филиалы» представлен на рисунке 2 .

Рис. 2. Пример бюджета продаж, построенного на основе двух аналитик «Продукты» и «Филиалы» в OLAP-КУБе программного комплекса «ИНТЕГРАЛ»

Если есть необходимость строить более детальные отчеты, то можно тот же бюджет продаж составлять с использованием трех аналитик (справочников). Пример бюджета продаж, построенного на основе трех аналитик «Продукты», «Филиалы» и «Каналы сбыта» представлен на рисунке 3 .

Рис. 3. Пример бюджета продаж, построенного на основе трех аналитик «Продукты», «Филиалы» и «Каналы сбыта» в OLAP-КУБе программного комплекса «ИНТЕГРАЛ»

Нужно напомнить о том, что КУБ, используемый для формирования отчетов, позволяет выводить данные в различной последовательности. На рисунке 3 бюджет продаж сначала «разворачивается» по продуктам, затем по филиалам, а потом по каналам сбыта.

Те же самые данные можно представить в другой последовательности. На рисунке 4 тот же самый бюджет продаж «разворачивается» сначала по продуктам, затем по каналам сбыта, а потом по филиалам.

Рис. 4. Пример бюджета продаж, построенного на основе трех аналитик «Продукты», «Каналы сбыта» и «Филиалы» в OLAP-КУБе программного комплекса «ИНТЕГРАЛ»

На рисунке 5 тот же самый бюджет продаж «разворачивается» сначала по филиалам, затем по продуктам, а потом по каналам сбыта.

Рис. 5. Пример бюджета продаж, построенного на основе трех аналитик «Филиалы», «Продукты» и «Каналы сбыта» в OLAP-КУБепрограммного комплекса «ИНТЕГРАЛ»

На самом деле это не все возможные варианты вывода бюджета продаж.

Необходимо упомянуть еще о нескольких возможностях OLAP-КУБа.

Рис. 6. Измерения OLAP-КУБа программного комплекса «ИНТЕГРАЛ»

Рис. 7. Пример перестройки отчета после изменения конфигурации измерений программного комплекса «ИНТЕГРАЛ»

Рис. 8. Редактор карты измерений программного комплекса «ИНТЕГРАЛ»

Рис. 9. Пример редактирования справочника 1 Продукты и услуги в программном комплексе «ИНТЕГРАЛ»

Рис. 10. Пример вывода в отчете только одной продуктовой группы (папки) в программном комплексе «ИНТЕГРАЛ»

На самом деле все такие изменения можно было сделать изначально при настройке строк.

Примечание : более подробно тема данной статьи рассматривается на семинарах-практикумах «Бюджетное управление предприятием» и «Постановка и автоматизация управленческого учета» , которые проводит автор данной статьи — Александр Карпов.

Общие сведения

Microsoft Excel позволяет создать отчеты сводных таблиц, основанных на исходных данных интерактивной аналитической обработки (OLAP). При работе с отчетами сводных таблиц, основанных на исходных данных OLAP и отчетов, основанных на не OLAP исходных данных, можно заметить различия в возможностях и в работе средства. В этой статье рассматриваются некоторые из основных различий между отчетами сводных таблиц, основанных на исходных данных OLAP и отчеты сводных таблиц, основанных на не OLAP исходных данных.

Получение данных и обновить различия

Базы данных OLAP организованы для облегчения извлечения и анализа больших объемов данных. Прежде чем Excel отображает обобщенные данные в сводной таблице, сервер OLAP выполняет вычисления для обобщения данных. Только требуемые обобщенные данные возвращаются в Excel, по мере необходимости.

С внешними базами данных не OLAP возвращаются все отдельные записи, а Excel выполняет обобщение. Следовательно базы данных OLAP дают Excel возможность анализировать значительно большие объемы внешних данных.

Сервер OLAP передает новые данные в Excel при каждом изменении макета отчета сводной таблицы или сводная диаграмма или представления. При использовании не OLAP исходных данных, обновляются данные по-разному и различные параметры обновления доступны в диалоговом окне Параметры сводной таблицы.

Не-OLAP данные могут быть возвращены в Microsoft Excel как диапазон внешних данных или отчет сводной таблицы или сводная диаграмма. Данные OLAP могут быть возвращены в Excel только в виде отчета сводной таблицы или сводная диаграмма.

Фоновый запрос

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

Запросы с параметрами

Отчеты сводных таблиц, основанные на источнике данных OLAP не поддерживают использование запросов с параметрами.

Оптимизация памяти

Флажок « оптимизировать память » в диалоговом окне Параметры сводной таблицы недоступна, когда отчет сводной таблицы основан на источнике данных OLAP.

Параметры поля страницы

В отчетах сводных таблиц, основанных на не OLAP исходных данных можно использовать параметры поля страницы для извлечения данных для каждого элемента по отдельности или для всех элементов одновременно. Эти параметры поля страницы недоступны в отчетах, основанных на исходных данных OLAP. Исходные данные OLAP всегда извлекаются для каждого элемента по мере необходимости что позволяет отчеты для отображения информации из больших баз данных OLAP.

Различия в расчет

Параметры поля страницы

Невозможно изменить функцию, для суммирования поля данных в отчете сводной таблицы, основанный на исходных данных OLAP. Это ограничение возникает из-за того, что итоговые значения вычисляются на сервере OLAP. Итоговые функции

Не удается создать вычисляемое поле или вычисляемого элемента в сводной таблице на основе источника данных OLAP.

Вычисляемые поля и вычисляемые элементы

При работе с промежуточных итогов в отчете сводной таблицы, основанный на исходных данных OLAP, применяются следующие ограничения.

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

OLAP-КУБ (динамическая управленческая отчетность)

Не удается отобразить промежуточные итоги для внутренних или внутренними полями столбцов в отчете сводной таблицы.

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

Промежуточные итоги

Помечать Итого * параметр в диалоговом окне Параметры сводной таблицы можно использовать только в отчетах сводных таблиц, основанных на исходных данных OLAP. Этот параметр помечает все промежуточные и общие итоги звездочкой (*) для указания того, что эти значения содержат скрытые, а также отображаемых элементов.

Макет и Дизайн различия

Измерения и меры

При работе с отчетом сводной таблицы, основанный на исходных данных OLAP, аналитик может использоваться только как поля строк, столбцов или страницу. Меры могут использоваться только как поля данных. При перетаскивании измерение в область данных поля или измерения в строку, столбец или область полей страниц, появляется следующее сообщение об ошибке:

Поле, которое нужно переместить не может быть помещен в эту область сводной таблицы.

При активном отчете сводной таблицы, основанный на исходных данных OLAP панели инструментов Сводная таблица отображает значок рядом с каждой строкой поля. Значок показывает, где Excel позволит поместить поле в отчете сводной таблицы. Если значок в левом верхнем углу, поле является измерением, которое можно перетащить в строку, столбец или поле страницы областей. Если значок в правом нижнем углу, поле является мер, которые можно перетаскивать в область полей данных.

Измерения и меры

Microsoft Excel позволяет переименовывать поля, добавляемые в сводную таблицу. Когда отчет сводной таблицы основан на исходных данных OLAP, ваше пользовательское имя будут потеряны при удалении поля из сводной таблицы.

Группирование и разгруппирование элементов

В Excel 2000 нельзя группировать элементы в отчете сводной таблицы, основанный на исходных данных OLAP;

Переименование полей

Отчеты сводных таблиц, основанных на исходных данных OLAP позволяют отобразить самый нижний уровень данных, доступных на сервере OLAP.

Группировка и разгруппирование элементов

Для не OLAP исходных данных элементы в новом отчете сводной таблицы сначала появляются отсортированный в порядке возрастания по имени элемента.

Подробные данные

Команда Показать страницы не доступны в отчетах сводных таблиц, основанных на исходных данных OLAP.

Show Items With No Data

Параметр Показывать элементы без данных в диалоговом окне Поле сводной таблицы не доступны в отчетах сводных таблиц, основанных на исходных данных OLAP.

Ниже представлен список вопросов по предмету Информационные технологии в менеджменте МФПУ/МФПА «Синергия»

… – интерактивная автоматизированная система, которая помогает по…

OLAP в узком смысле слова трактуется как …

OLAP-системы (online analytical processing) – это …

OLTP-системы оказались мало пригодны потому что …

Автоматизированная система управления (автоматизированная информа…

В программе MS Project …

В системе OLTP обновления данных происходит…

Диаграмма, предназначенная для анализа плана работ с помощью мето…

Информационная система – это множество взаимосвязанных элементов …

Информационная технология – это …

Информационное обеспечение – это …

Информационные технологии на развитие общества влияют следующим о…

Информационный обмен в структуре органов управления организации о…

Исполнительские информационные системы (Executive Information Sys…

К признакам «малых» информационных систем относится …

К признакам информационных систем «среднего» масштаба относят …

Методы обработки информации представляют собой …

Модульный принцип построения бухгалтерских информационных систем …

На рисунке приведен фрагмент диаграммы типа …, выполненной в про…

На сетевом графике в программе MS Project задачу из внешнего прое…

На сетевом графике в программе MS Project задачу, не относящуюся …

На сетевом графике в программе MS Project задачу, являющуюся заве…

На сетевом графике в программе MS Project сводную задачу, объедин

На состав и количество автоматизированных рабочих мест, входящих …

Наука об информационной деятельности, ин¬формационных процессах и…

Организация информационной системы, при которой на удаленном серв…

Основное назначение системы OLAP заключается в …

Основным назначением ERP-систем является автоматизация …

Основным назначением методологии MPS является …

Основными характеристиками OLAP-систем является …

Подсистема технического обеспечения включает в себя …

Последовательность технологических этапов по модификации первично…

При сетевом объединении персональных компьютеров в виде внутрипро…

Прикладное программное обеспечение ЭВМ предназначено для …

Примером предметной информационной технологии является технология…

Процесс поддержки принятия решения подразумевает под собой …

Сеть Масштаба Предприятия или Корпоративная Сеть – это информацио…

Система искусственного интеллекта представляет собой …

Системы обработки трансакций – это системы предназначенные для …

Системы обработки трансакций соответствуют …

Системы поддержки принятия решений (Decision Support Systems – DS…

Современные методы и средства анализа и планирования процессов пр…

Создание интегрированной автоматизированной информационной систем…

Созданные информационные системы становятся не пригодными для исп…

Специфика информационной системы поддержки руководства проявляетс…

Средствами традиционных OLTP-систем можно …

Структура корпоративных информационных систем является …

Ускорить и упростить работу менеджеров по персоналу на фирме позв..

Ускорить и упростить работу менеджеров по персоналу на фирме позв…

Фиксируемые воспринимаемые факты окружающего мира представляют со…

Цепочка действий, наиболее точно отражающую процесс управления пр…

Экономические задачи, решаемые в диалоговом режиме, характеризуют…

Экспертные системы предназначены для обработки …

Является нарушением безопасности или относится к сфере безопаснос…

OLAP — это просто

Удивительное — рядом …

По ходу работы мне часто требовалось делать сложные отчеты, я все время пытался найти в них что-то общее, чтобы составлять их более просто и универсально, даже написал и опубликовал по этому поводу статью «Дерево Осипова». Однако мою статью раскритиковали и сказали, что все те проблемы, которые я поднял, давно уже решены в OLAP (www.molap.rgtu.ru) и порекомендовали посмотреть сводные таблицы в EXCEL.
Это оказалось настолько простым, что приложив к этому свои гениальные ручонки, у меня получилась очень простая схема для выгрузки данных из 1С7 или любой другой базы данных (в дальнейшем под 1С подразумевается любая база данных) и анализа в OLAP.
Я думаю, многие схемы выгрузки в OLAP слишком усложнены, я выбираю простоту.

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

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

Что такое OLAP : (www.molap.rgtu.ru)

Предположим у вас есть торговая сеть. Пусть данные о торговых операциях выгружены в текстовый файл или таблицу вида:

Дата — дата операции
Месяц — месяц операции
Неделя — неделя операции
Вид — закуп, продажа, возврат, списание
Контрагент — внешняя организация, учавствующая в операции
Автор — человек, выписавший накладную

В 1С, например, одна строка этой таблицы будет соответствовать одной строке накладной, некоторые поля (Контрагент, Дата) при этом берутся из шапки накладной.

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

Эта таблица является исходной для OLAP-анализа.

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

Как использовать у себя :

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

Сначала вы должны написать обработку, которая выгружает данные из 1С в текстовый файл (таблицу). Вам нужно определить состав полей, которые будут выгружаться.
Например, уже готовая универсальная обработка, которая работает в любой конфигурации и выгружает для OLAP-анализа проводки за период, выгружает для анализа следующие поля:

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

Для торговой системы поля могут быть такие:

Для анализа данных используются таблицы "Анализ движений.xls" ("Анализ бухгалтерии.xls"). Открывая их, не отключайте макросы, иначе вы не сможете обновлять отчеты (они запускаются макросами на языке VBA). Исходные данные эти файлы берут из файлов C:\fixin\motions.txt (C:\fixin\buh.txt), в остальном они одинаковые.

Основы OLAP

Поэтому возможно, вам придется скопировать ваши данные в один из этих файлов.
Чтобы в EXCEL загрузились ваши данные, выберите или напишите свой фильтр и нажмите кнопку "Сформировать" на листе "Условия".
Листы отчетов начинаются префиксом "Отч". Перейдите на лист отчета, нажмите "Обновить" и данные отчета изменятся в соотсветсвии с последними загруженными данными.
Если вас не устраивают стандартные отчеты, есть лист ОтчШаблон. Скопируйте его в новый лист и настройте вид отчета, работая со сводной таблицей на этом листе (о работе со сводными таблицами — в любой книге по EXEL 2000). Рекомендую настраивать отчеты на небольшом наборе данных, а затем уже запускать их на большом массиве, т.к. нет никакой возможности отключить перерисовку таблиц при каждом изменении макета отчета.

Технические комментарии :

При выгрузке данных из 1С пользователь выбирает папку, куда ему выгружать файл. Я сделал это потому, что вполне вероятно в ближайшем будующем будут выгружаться несколько файлов (остатки и движения). Затем по нажатию в Проводнике кнопки "Отправить" —> "На OLAP-анализ в EXCEL 2000" данные копируются из выбранной папки в папку C:\fixin. (чтобы эта команда появилась в списке команды "Отправить" и нужно скопировать файл "На OLAP-анализ в EXCEL 2000.bat" в каталог C:\Windows\SendTo) Поэтому выгружайте данные сразу давая имена файлам motions.txt или buh.txt.

Формат текстового файла:
Первая строка текстового файла — заголовки колонок разделенные "|", остальные строки содержат значения этих колонок, разделенные "|".

Для импорта текстовых файлов в Excel используется Microsoft Query (составная часть EXCEL) для его работы необходимо наличие в каталоге импорта (C:\fixin) файла shema.ini, содержащего следующую информацию:

ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Пояснение: motions.txt и buh.txt — это название раздела, соответствует имени импортируемого файла, описывает, как импортировать текстовый файл в Эксель. Остальные параметры означают, что первая строка содержит названия колонок, разделителем колонок является "|", набор символов — Windows ANSI (для ДОС — OEM).
Тип полей определяется автоматически исходя из содержащихся в колонке данных (дата, число, строка).
Перечень полей не нужно нигде описывать — EXCEL и OLAP сами определят, какие поля содержатся в файле по заголовкам в первой строке.

Внимание, проверьте ваши региональные настройки "Панель управления" —> "Региональные настройки" . В моих обработках числа выгружаются с разделителем запятая, а даты в формате "ДД.ММ.ГГГГ".

Данные при нажатии кнопки "Сформировать" загружаются в сводную таблицу на листе "База", а из этой сводной таблицы и берут данные все отчеты на листах "Отч".

Я понимаю, что любители MS SQL Server и мощных баз данных начнут ворчать, что у меня слишком все упрощено, что моя обработка загнется на годичной выборке, но в первую очередь я хочу дать преимущества OLAP-анализа для средних организаций. Я бы позиционировал этот продукт как инструмент годичного анализа для оптовых компаний, квартального анализа для розничной торговли и оперативного анализа для любой организации.

Мне пришлось повозиться с VBA, чтобы данные брались из файла с любым списком полей и можно было заранее готовить бланки отчетов.

Описание работы в EXCEL (для пользователей):

Инструкция по использованию отчетов:
1. Отправьте на анализ выгруженные данные (уточните у администратора). Для этого нажмите правой кнопкой на папке, в которую у вас выгрузились данные из 1С и выберите команду "Отправить", затем "На OLAP-анализ в EXCEL 2000".
2. Откройте файл "Анализ движений.xls"
3. Выберите Значение фильтра, нужные вам фильтры можно дописать на закладке "Значения".
4. Нажмите кнопку "Сформировать", при этом выгруженные данные будут загружены в EXCEL.
5. После загрузки данных в EXCEL, можно смотреть различные отчеты. Для этого достаточно нажать кнопку "Обновить" в выбранном отчете. Листы с отчетами начинаются на Отч.
Внимание! После того как вы поменяете значение фильтра, нужно еще раз нажать кнопку "Сформировать", чтобы данные в EXCEL перезагрузились из файла выгрузки в соответствие с фильтрами.

Обработки из демо-примера:

Обработка motionsbuh2011.ert – последняя версия выгрузки проводок из Бухгалтерии 7.7 для анализа в Excel. В ней есть галочка «Присоединить в файл», которая позволяет выгружать данные частями по периодам, присоединяя их в один и тот же файл, а не выгружая в один и тот же файл заново:

Обработка motionswork.ert выгружает данные о продажах для анализа в Excel.

Примеры отчетов :

Шахматка по проводкам:

Загруженность операторов по видам накладных:

P.S. :

Понятно, что по аналогичной схеме можно организовать выгрузку данных из 1С8.
В 2011 году ко мне обращался пользователь, которому нужно было доработать эту обработку в 1С7, чтобы она выгружала большие объемы данных, я нашел аутсорсера и выполнил эту работу. Так что разработка вполне актуальна.

Обработка motionsbuh2011.ert доработана, чтобы справляться с выгрузкой большого объема данных.

Первое четкое определение OLAP (On-line Analytical Processing) предложено в 1993 году Е.Ф.Коддом (E.F.Codd) в статье, опубликованной при поддержке Arbor Software (теперь — Hyperion Software). Статья включала 12 правил, которые сейчас уже стали широко известными и описаны на сайте любого поставщика OLAP приложений. Позже, в 1995 году, к ним были добавлены еще шесть менее известных правил, все они были разделены на четыре группы и названы «характеристиками» (features). Вот эти правила, дающие определение OLAP приложения с комментариями Найджела Пендса (Nigel Pendse), одного из создателей сайта OLAP Report.

Основные характеристики OLAP включают:

1. Многомерность модели данных . С этим утверждением мало кто спорит, и оно считается основной характеристикой OLAP. Частью этого требования считается возможность построения различных проекций и разрезов модели.

2. Интуитивные механизмы манипулирования данными . Кодд считает, что манипулирование данными должно производится с помощью действий непосредственно в ячейке таблиц, без применения меню или сложных. Можно предположить, что это подразумевает использование операций с мышью, но Кодд этого не утверждает. Многие продукты не выполняют этого правила. С нашей точки зрения, эта характеристика незначительно влияет на качество процесса анализа данных. Мы считаем, что программа должна предлагать возможность выбора модели работы, т.к. не всем пользователям нравится одно и то же.

3. Доступность . OLAP это Посредник. Кодд особенно подчеркивает, что ядро OLAP является программой промежуточного уровня между гетерогенными источниками данных и пользовательским интерфейсом. Большинство продуктов обеспечивают эти функции, но удобство доступа к данным часто оказывается ниже чем это хотелось бы другим поставщикам программ.

4. Пакетное извлечение данных . Это правило требует, чтобы продукты предлагали как собственные базы для хранения анализируемых данных, так и динамический (live) доступ к внешним данным. Мы согласны с Коддом в этом пункте и сожалеем, что лишь немногие OLAP продукты соответствуют ему. Даже те программы, которые предлагают такие функции, редко делают их легкими и достаточно автоматизированными. В результате, Кодд поддерживает многомерное представление данных плюс частичный предварительный обсчет больших многомерных баз данных с прозрачным сквозным доступом к детальной информации. Сегодня это рассматривается как определение гибридного OLAP, которая становится наиболее популярной архитектурой, так что Кодд очень точно увидел основные тенденции в этой области.

5. Архитектура «клиент-сервер» . Кодд считает, что не только каждый продукт должен быть клиент-серверным, но и что каждая серверная компонента OLAP продуктов должна быть достаточно интеллектуальной для того, чтобы разные клиенты могли быть подключены с минимальными усилиями и программированием. Это намного более сложный тест, чем простая клиент-серверная архитектура и относительно мало продуктов проходит его. Мы могли бы возразить, что этот тест, возможно, сложнее, чем надо и не стоит диктовать разработчикам архитектуру системы.

6. Прозрачность . Этот тест также сложен, но необходим. Полное соответствие означает, что пользователь, скажем, электронной таблицы может получить полный доступ к средствам, предоставляемым ядром OLAP и может при этом даже не знать о том, откуда получены эти данные. Для того чтобы достичь этого, продукты должны предоставлять динамический доступ к гетерогенным источникам данных и полнофункциональный модуль, встраиваемый в электронную таблицу. Между электронной таблицей и хранилищем данных при этом размещается OLAP сервер.

7. Многопользовательская работа . Кодд определяет, что для того, чтобы считаться стратегическим OLAP инструментом, приложения должны работать не только на чтение и интерпретацию данных, и, соответственно, они должны обеспечивать одновременный доступ (включая и извлечение, и обновление данных), целостность и безопасность.

Специальные характеристики

8. Обработка ненормализованных данных . Это означает возможность интеграции между ядром OLAP и ненормализованным источником данных. Кодд выделяет то, что при обновлении данных, выполненном в среде OLAP, должна быть возможность изменять ненормализованные данные во внешних системах.

9. Хранение OLAP результатов отдельно от исходных данных . В действительности, это имеет отношение к реализации продукта, а не к его возможностям, но мало кто будет спорить с этим утверждением. По сути, Кобб поддерживает широко принятую систему, в соответствии с которой OLAP приложения должны строить анализ непосредственно на основе данных транзакции и изменения в данных OLAP должны храниться отдельно от данных транзакции.

10. Выделение отсутствующих данных . Это означает, что отсутствующие данные должны отличаться от нулевого значения. Как правило, все современные OLAP системы поддерживают эту характеристику.

11. Обработка отсутствующих значений . Все отсутствующие значения должны быть проигнорированы при анализе, вне зависимости от их источника.

Характеристики построения отчетов

12. Гибкое построение отчетов . Различные измерения должны выстраиваться любым способом в соответствии с потребностями пользователя. Большинство продуктов соответствует этому требованию в рамках специальных редакторов отчетов. Хотелось бы, чтобы такие же возможности были доступны и в интерактивных средствах просмотра, но это встречается значительно реже. Это — одна из причин, по которой мы предпочитаем, чтобы функционал анализа и построения отчетов был объединен в одном модуле.

1. Понятие куба olap

13. Стабильная производительность при построении отчетов . Это означает, что производительность системы при построении отчетов не должна существенно падать при увеличении размерности или величины базы данных.

14. Автоматическое регулирование физического уровня . OLAP система должна автоматически регулировать физическую структуру для адаптации ее к типу и структуре модели.

Управление размерностью

15. Общая функциональность . Все измерения должны иметь одинаковые возможности в структуре и функциональности.

16. Неограниченное число измерений и уровней агрегирования . Фактически, под неограниченным числом Кодд подразумевает 15-20, т.е. число, заведомо превышающее максимальные потребности аналитика.

17. Неограниченные операции между данными различных измерений . Кодд полагает, что для того, чтобы приложение называлось многомерным, оно должно поддерживать любые вычисления с использованием данных всех измерений.

Подробности о продуктах Hyperion — на сайте www.hyperion.ru

Версия для печати

Назад

10.8 Работа со сводными таблицами (объект PivotTable)

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

В процессе работы большинства предприятий накапливаются так называемые необработанные данные (raw data) о деятельности. Например, для торгового предприятия могут накапливаться данные о продажах товаров - по каждой покупке отдельно, для предприятий сотовой связи - статистика нагрузки на базовые станции и т.п. Очень часто менеджменту предприятия необходима аналитическая информация, которая генерируется на основе необработанной - например, посчитать вклад каждого вида товара в доходы предприятия или качество обслуживания в зоне данной станции. Из необработанной информации такие сведения извлечь очень тяжело: нужно выполнять очень сложные SQL-запросы, которые выполняются долго и часто мешают текущей работе. Поэтому все чаще в настоящее время необработанные данные сводятся вначале в хранилище архивных данных - Data Warehouse, а затем - в кубы OLAP, которые очень удобны для интерактивного анализа. Проще всего представить себе кубы OLAP как многомерные таблицы, в которых вместо стандартных двух измерений (столбцы и строки, как в обычных таблицах), измерений может быть очень много. Обычно для описания измерений в кубе используется термин «в разрезе». Например, отделу маркетинга может быть нужна информация во временном разрезе, в региональном разрезе, в разрезе типов продукта, в разрезе каналов продаж и т.п. При помощи кубов (в отличие от стандартных SQL-запросов) очень просто получать ответы на вопросы типа «сколько товаров такого-то типа было продано в четвертом квартале прошлого года в Северо-Западном регионе через региональных дистрибьюторов.

Конечно же, в обычных базах данных такие кубы не создать. Для работы с кубами OLAP требуются специализированные программные продукты. Вместе с SQL Server поставляется база данных OLAP от Microsoft, которая называется Analysis Services. Есть OLAP-решения от Oracle, IBM, Sybase и т.п.

Для работы с такими кубами в Excel встроен специальный клиент.

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

Сводная таблица и объект PivotTable - это программные продукты фирмы Panorama Software, которые были приобретены Microsoft и интегрированы в Excel.

Поэтому работа с объектом PivotTable несколько отличается от работы с другими объектами Excel. Догадаться, что нужно сделать, часто бывает непросто. Поэтому рекомендуется для получения подсказок активно использовать макрорекордер. В то же время при работе со сводными таблицами пользователям часто приходится выполнять одни и те же повторяющиеся операции, поэтому автоматизация во многих ситуациях необходима.

Как выглядит программная работа со сводной таблицей?

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

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches - стандартная коллекция, и из методов, которые заслуживают подробного рассмотрения, в ней можно назвать только метод Add(). Этот метод принимает два параметра:

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

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

ADOConnection - возможность возвратить объект ADO Connection, который автоматически создается для подключения к внешнему источнику данных. Используется для дополнительной настройки свойств подключения.
Connection - работает точно так же, как и одноименное свойство объекта QueryTable. Может принимать строку подключения, готовый объект Recordset, текстовый файл, Web-запрос. файл Microsoft Query. Чаще всего при работе с OLAP прописывается строка подключения напрямую (поскольку получать объект Recordset, например для изменения данных, большого смысла нет - источники данных OLAP практически всегда доступны только на чтение). Например, настройка этого свойства для подключения к базе данных Foodmart (учебная база данных Analysis Services) на сервере LONDON может выглядеть так:

PC1.Connection = «OLEDB;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000»

свойства CommandType и CommandText точно так же описывают тип команды, которая передается на сервер баз данных, и текст самой команды. Например, чтобы обратиться на куб Sales и получить его целиком в кэш на клиенте, можно использовать код вида

PC1.CommandType = xlCmdCube

PC1.CommandText = Array(«Sales»)

свойство LocalConnection позволяет подключиться к локальному кубу (файлу *.cub), созданному средствами Excel. Конечно, такие файлы для работы с «производственными» объемами данных использовать очень не рекомендуется - только для целей создания макетов и т.п.
свойство MemoryUsed возвращает количество оперативной памяти, используемой PivotCache. Если PivotTable на основе этого PivotCache еще не создана и не открыта, возвращает 0. Можно использовать для проверок, если ваше приложение будет работать на слабых клиентах.
свойство OLAP возвращает True, если PivotCache подключен к серверу OLAP.
OptimizeCache - возможность оптимизировать структуру кэша. Изначальная загрузка данных будет производиться дольше, но потом скорость работы может возрасти. Для источников OLE DB не работает.

Остальные свойства объекта PivotCache совпадают с аналогичными свойствами объекта QueryTable, и поэтому здесь рассматриваться не будут.

Главный метод объекта PivotCache - это метод CreatePivotTable(). При помощи этого метода и производится следующий этап - создание сводной таблицы (объекта PivotTable). Этот метод принимает четыре параметра:

TableDestination - единственный обязательный параметр.
Принимает объект Range, в верхний левый угол которого будет помещена сводная таблица.
TableName - имя сводной таблицы. Если не указано, то автоматически сгенерируется имя вида «СводнаяТаблица1».
ReadData - если установить в True, то все содержимое куба будет автоматически помещено в кэш. С этим параметром нужно быть очень осторожным, поскольку неправильное его применение может резко увеличить нагрузку на клиента.
DefaultVersion - это свойство обычно не указывается. Позволяет определить версию создаваемой сводной таблицы. По умолчанию используется наиболее свежая версия.

Создание сводной таблицы в первой ячейке первого листа книги может выглядеть так:

PC1.CreatePivotTable Range («A1»)

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

область столбцов - в нее помещаются те измерения («разрез», в котором будут анализироваться данные), членов которых меньше;
область строк - те измерения, членов которых больше;
область страницы - те измерения, по которым нужно только проводить фильтрацию (например, показать данные только по такому-то региону или только за такой-то год);
область данных - собственно говоря, центральная часть таблицы. Те числовые данные (например, сумма продаж), которые мы и анализируем.

Полагаться на пользователя в том, что он правильно разместит элементы во всех четырех областях, трудно.

Кроме того, это может занять определенное время. Поэтому часто требуется расположить данные в сводной таблице программным образом. Эта операция производится при помощи объекта CubeField. Главное свойство этого объекта - Orientation, оно определяет, где будет находиться то или иное поле. Например, помещаем измерение Customers в область столбцов:

PT1.CubeFields («»).Orientation = xlColumnField

Затем - измерение Time в область строк:

PT1.CubeFields («»).Orientation = xlRowField

Затем - измерение Product в область страницы:

PT1.CubeFields («»).Orientation = xlPageField

И наконец, показатель (числовые данные для анализа) Unit Sales:

PT1.CubeFields(«.»).Orientation = xlDataField

Теперь сводная таблица создана и с ней вполне можно работать. Однако часто необходимо выполнить еще одну операцию - раскрыть нужный уровень иерархии измерения. Например, если нас интересует поквартальный анализ, то нужно раскрыть уровень Quarter измерения Time (по умолчанию показывается только самый верхний уровень). Конечно, пользователь может сделать это самостоятельно, но не всегда можно рассчитывать, что он догадается, куда щелкнуть мышью. Программным образом раскрыть, например, иерархию измерения Time на уровень кварталов для 1997 года можно при помощи объектов PivotField и PivotItem:

PT1.PivotFields(«.»).PivotItems(«.»).DrilledDown = True

Многомерные кубы в olap. Проектирование кубов данных

См. также

Смотреть что такое "OLAP-куб" в других словарях:

Немного подробнее о возможностях

Многомерные кубы

Немного MDX

Заключение

Что такое хранилище данных

Что такое OLAP

Многомерные кубы

Некоторые термины и понятия

Заключение

OLAP-КУБ (динамическая управленческая отчетность)

Ниже представлен список вопросов по предмету Информационные технологии в менеджменте МФПУ/МФПА «Синергия»

Основы OLAP

1. Понятие куба olap

10.8 Работа со сводными таблицами (объект PivotTable)

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Ваш комментарий (необязательно):