Создание куба ОLAP средствами Microsoft Query. Введение в OLAP

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

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

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

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

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

См. также

Wikimedia Foundation . 2010 .

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

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

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

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

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

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

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

/ В кубистической манере. Применение OLAP-кубов в практике управления крупных компаний

Вконтакте

Одноклассники

Константин Токмачев , системный архитектор

В кубистической манере.
Применение OLAP-кубов в практике управления крупных компаний

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

О пользе бизнес-аналитики

В контуре управления корпорацией между «сырыми» данными и «рычагами» воздействия на управляемый объект располагаются «показатели работы» – KPI. Они образуют как бы «приборное табло», отражающее состояние различных подсистем управляемого объекта. Оснастить фирму информативными показателями работы и контролировать их расчет и полученные значения – труд бизнес-аналитика. Существенную помощь в организации аналитической работы корпорации способны оказать автоматизированные службы анализа, такие как утилита MS SQL Server Analysis Services (SSAS) и ее главный диспозитив – OLAP-куб.

Прямо здесь нужно сделать еще одно замечание. Скажем, в американской традиции специальность, ориентированная на работу с OLAP-кубами, называется BI (Business Intelligence) . Не должно быть никаких иллюзий, будто бы американское BI соответствует русскому «бизнес-аналитик». Без обид, но нередко наш бизнес-аналитик – это «недобухгалтер» и «недопрограммист», специалист с нечеткими знаниями и с небольшим окладом, реально не обладающий никаким собственным инструментарием и методологией.

Специалист же BI – это, по сути, прикладной математик, высококлассный специалист, ставящий на вооружение фирмы современные математические методы (то, что называлось Operations Researh – методы исследования операций). BI больше соответствует бывшей когда-то в СССР специальности «системный аналитик», выпускавшейся факультетом ВМК МГУ им. М.В. Ломоносова. OLAP-куб и службы анализа могут стать перспективной основой рабочего места русского бизнес-аналитика, возможно, после некоторого повышения его квалификации в сторону американского BI.

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

Бухгалтер занят отчетностью, его суммы и по смыслу и по динамике не имеют прямого отношения к бизнес-процессу фирмы.

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

Наконец, программист, бывший когда-то (благодаря образованию) проводником передовых технических идей из сферы науки в сферы бизнеса, превратился в пассивного исполнителя фантазий бухгалтера и менеджера, так что уже не редкость, когда ИТ-отделами корпораций подруливают бухгалтеры и вообще все, кому не лень. Безынициативный, малограмотный, но относительно высокооплачиваемый программист 1С – настоящий бич российских корпораций. (Почти как отечественный футболист.) О так называемых «экономистов и юристов» я уже не говорю, о них давно все сказано.

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

Преимущества OLAP-кубов

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

Некоторое представление об OLAP-
кубе может дать «сводная таблица» MS Excel. У этих объектов схожая логика и похожие интерфейсы. Но, как будет видно из статьи, функциональность OLAP несравненно богаче, а производительность несравненно выше, так что «сводная таблица» остается локальным настольным продуктом, тогда как OLAP – продукт корпоративного уровня.

Почему OLAP-куб так хорошо подходит для решения аналитических задач? OLAP-куб устроен так, что все показатели во всех возможных разрезах заранее вычислены (полностью или частично), и пользователю остается только «вытянуть» мышью требуемые показатели (измерения measures) и разрезы (размерности dimensions), а программе – перерисовать таблички.

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

Формулировка аналитики возможна в трех вариантах: это либо поле базы данных (вернее, поле warehouse), либо расчетное поле calculation, определяемое на уровне дизайна куба, либо выражение языка MDX при интерактивной работе с кубом.

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

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

Приведем пример. Допустим, руководитель контролирует дебиторскую задолженность. Пока KPI просроченной дебиторской задолженности «горит зеленым светом», значит, все в норме, никаких управленческих действий не требуется. Если цвет изменился на желтый или красный – что-то не так: разрезаем KPI по отделам продаж и сразу видим подразделения «в красном». Следующий разрез по менеджерам – и продавец, чьи клиенты просрочили платежи, определен. (Далее сумму просрочки можно разрезать по покупателям, по срокам и т.п.) Руководитель корпорации может прямо обратиться к нарушителям на любом уровне. Но вообще-то тот же KPI (на своих уровнях иерархии) видят и начальники отделов, и менеджеры по продажам. Поэтому, чтобы исправить ситуацию, им даже не нужно ждать «вызова на ковер»… Разумеется, сам KPI по смыслу не обязательно должен быть суммой просрочки – он может быть средневзвешенным сроком просрочки или вообще скоростью оборота дебиторской задолженности.

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

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

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

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

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

OLTP + OLAP: контур обратной связи в цепи управления фирмой

Теперь рассмотрим общую идею OLAP-кубов и их точку приложения в управленческой цепи корпорации. Термин OLAP (OnLine Analytical Processing) был введен британским математиком Едгаром Коддом в дополнение к им же ранее введенному термину OLTP (OnLine Transactions Processing). Об этом еще будет сказано, но Е. Кодд, разумеется, предложил не только термины, но и математические теории OLTP и OLAP. Не вдаваясь в детали, в современной интерпретации OLTP – это реляционная база данных, рассмотренная как механизм регистрации, хранения и выборки информации .

Методология решения

Такие ERP-системы (Enterprice Resource Planning), как 1С7, 1С8, MS Dynamics AX, имеют программные интерфейсы, ориентированные на пользователя (ввод и корректировка документов и т.п.), и реляционную базу данных (DB) для хранения и выборки информации, представленную сегодня программными продуктами типа MS SQL Server (SS).

Отметим, что информация, зарегистрированная в базе данных ERP-системы, и в самом деле представляет весьма ценный ресурс. Дело не только в том, что зарегистрированная информация обеспечивает текущий документооборот корпорации (выписку документов, их корректировку, возможность распечатки и сверки и т.п.) и не только в возможности расчета бухгалтерской отчетности (налоги, аудит и т.п.). С точки зрения управления намного важнее, что OLTP-система (реляционная база данных) – это, по сути, актуальная цифровая модель деятельности корпорации в натуральную величину.

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

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

Кроме привычной системы регистрации информации методом OLTP, нужна еще одна система – система анализа собранной информации. Эта надстройка, которая в контуре управления играет роль обратной связи между руководством и объектом управления, и есть система OLAP или, короче говоря, OLAP-куб.

В качестве программной реализации OLAP мы будем рассматривать утилиту MS Analysis Services, входящую в состав стандартной поставки MS SQL Server, сокращенно SSAS. Отметим, что по замыслу Е. Кодда OLAP-куб в аналитике должен дать ту же исчерпывающую свободу действий, которую система OLTP и реляционная база данных (SQL Server) дают в хранении и выборке информации.

Материально-техническое обеспечение OLAP

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

Будем считать, что корпорация использует ERP-систему, например, 1С7 или 1С8, в рамках которой в обычном порядке идет регистрация информации. База данных этой ERP-системы располагается на некоем сервере и поддерживается программой MS SQL Server.

Будем считать также, что на другом сервере установлено матобеспечение, включающее MS SQL Server с утилитой MS Analysis Services (SSAS), а также программы MS SQL Server Managment Studio, MS C#, MS Excel и MS Visual Studio. Эти программы в совокупности образуют требуемый контекст: инструментарий и необходимые интерфейсы разработчика OLAP-кубов.

На сервере SSAS установлена свободно распространяемая программа blat, вызываемая (с параметрами) из командной строки и обеспечивающая почтовый сервис.

На рабочих станциях сотрудников, в рамках локальной сети, среди прочего установлены программы MS Excel (версии не менее 2003), а также, возможно, специальный драйвер для обеспечения работы MS Excel с MS Analysis Services (если только соответствующий драйвер уже не включен в MS Excel).

Для определенности будем считать, что на рабочих станциях сотрудников установлена операционная система Windows XP, а на серверах – Windows Server 2008. Кроме того, пусть в качестве SQL Server используется MS SQL Server 2005, причем на сервере с OLAP-кубом установлены Enterprise Edition (EE) или Developer Edition (DE). В этих редакциях возможно использовать т.н. «полуаддитивные меры», т.е. дополнительные агрегатные функции (статистики), отличные от обычных сумм (например, экстремум или среднее значение).

Дизайн OLAP-куба (OLAP-кубизм)

Скажем несколько слов о дизайне самого OLAP-куба. На языке статистики OLAP-куб – это множество показателей работы, рассчитанных во всех необходимых разрезах, например, показатель отгрузки в разрезах по покупателям, по товарам, по датам и т.п. Из-за прямого перевода с английского в русской литературе по OLAP-кубам показатели называются «мерами», а разрезы – «размерностями». Это математически корректный, но синтаксически и семантически не очень удачный перевод. Русские слова «мера», «измерение», «размерность» почти не отличаются по смыслу и написанию, в то время как английские «measure» и «dimension» отличны и по написанию и по смыслу. Поэтому мы отдаем предпочтение аналогичным по смыслу традиционным русским статистическим терминам «показатель» и «разрез».

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

В этой схеме OLAP и OLTP не имеют общих таблиц, и аналитики OLAP рассчитываются максимально детально на стадии обновления куба (Process), предшествующей стадии использования. Эта схема называется MOLAP (Multidimensional OLAP). Ее минусы – асинхронность с ERP и большие затраты памяти.

Хотя формально OLAP-куб можно построить с использованием в качестве источника данных всех (тысяч) таблиц реляционной базы данных ERP-системы и всех (сотен) их полей в качестве показателей или разрезов, реально этого делать не стоит. Наоборот. Для загрузки в куб правильнее подготовить отдельную базу данных, называемую «витрина» или «хранилище» (warehouse).

Несколько причин заставляют поступить именно так.

• Во-первых, привязка OLAP-куба к таблицам реальной базы данных наверняка создаст технические проблемы. Изменение данных в таблице может инициировать обновление куба, а обновление куба – не обязательно быстрый процесс, так что куб будет в состоянии перманентной перестройки; при этом еще процедура обновления куба может блокировать (при чтении) данные таблиц базы, тормозя работу пользователей по регистрации данных в ERP-системе.
• Во-вторых , наличие слишком большого количества показателей и разрезов резко увеличит область хранения куба на сервере. Не забудем, что в OLAP-кубе хранятся не только исходные данные, как в OLTP-системе, а еще и все показатели, просуммированные по всем возможным разрезам (и даже по всем сочетаниям всех разрезов). Кроме того, соответственно, замедлятся скорость обновления куба и в конце концов скорость построения и обновления аналитик и основанных на них пользовательских отчетов.
• В-третьих , слишком большое количество полей (показателей и разрезов) создаст проблемы в интерфейсе разработчика OLAP, т.к. списки элементов станут необозримы.
• В-четвертых, OLAP-куб весьма чувствителен к нарушениям целостности данных. Куб не может быть построен, если ключевые данные не находятся по ссылке, прописанной в структуре связей полей куба. Временное или постоянное нарушение целостности, незаполненные поля – обычное дело в базе данных ERP-системы, но это категорически не годится для OLAP.

Можно еще добавить, что ERP-систему и OLAP-куб следует располагать на разных серверах, чтобы разделить нагрузку. Но тогда при наличии общих таблиц для OLAP и OLTP возникает еще и проблема сетевого трафика. Практически неразрешимые -проблемы появляются в этом случае при необходимости консолидации в один OLAP-куб нескольких разнородных ERP-систем (1С7, 1С8, MS Dynamics AX).

Наверное, можно и дальше громоздить технические проблемы. Но самое главное, вспомним, что, в отличие от OLTP, OLAP – не средство регистрации и хранения данных, а средство аналитики. Это означает, что не нужно «на всякий случай» грузить и грузить «грязные» данные из ERP в OLAP. Наоборот, нужно сначала выработать концепцию управления фирмой, хотя бы на уровне системы KPI, и далее сконструировать прикладное хранилище данных (warehouse), расположенное на том же сервере, что и OLAP-куб, и содержащее небольшое рафинированное количество данных из ERP, необходимых для управления.

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

Итак, мы получили, что источник данных для OLAP – это специальная база данных (warehouse), расположенная на том же сервере, что и OLAP. Вообще это означает две вещи. Во-первых, должны существовать особые процедуры, которые будут создавать warehouse из баз данных ERP. Во-вторых, OLAP-куб асинхронен со своими ERP-системами.

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

Архитектура решения

Пусть на разных серверах располагается множество ERP-систем некой корпорации (холдинга), аналитические данные по которым мы хотели бы консолидировано видеть в пределах одного OLAP-куба. Подчеркнем, что в описываемой технологии мы объединяем данные ERP-систем на уровне warehouse, оставляя неизменным дизайн OLAP-куба.

На сервере OLAP мы создаем образы (пустые копии) баз данных всех этих ERP-систем. На эти пустые копии мы периодически (еженощно) выполняем частичную репликацию баз данных соответствующих активно работающих ERP.

Далее запускаются SP (stored procedure), которые на том же сервере OLAP без сетевого трафика на основе частичных реплик баз данных ERP-систем создают (или пополняют) хранилище (warehouse) – источник данных OLAP-куба.

Потом запускается стандартная процедура обновления/построения куба по данным warehouse (операция Process в интерфейсе SSAS).

Прокомментируем отдельные моменты технологии. Какую работу выполняют SP?

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

Во-первых, из всех таблиц базы данных ERP-системы в ходе частичной репликации копируются лишь те, что нужны для построения warehouse. Это управляется фиксированным списком имен таблиц.

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

Конечно, возможно не копировать строки таблиц целиком, но только добавлять новые записи. Однако это создает серьезные неудобства при учете редакций ERP «задним числом», что часто встречается в реально работающих системах. Так что проще, не мудрствуя лукаво, копировать все записи (или обновлять «хвост» начиная с некоторой даты).

Далее, главная задача SP – преобразовать данные ERP-систем к формату warehouse. Если имеется только одна ERP-система, то задача преобразования в основном сводится к выкопировке и, возможно, переформатированию нужных данных. Но если в одном и том же OLAP-кубе необходимо консолидировать несколько ERP-систем разной структуры, то преобразования усложняются.

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

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

Уделим некоторое внимание архитектуре хранилища warehouse. Обычно схему OLAP-куба представляют в виде «звезды», т.е. как таблицу данных, окруженную «лучами» справочников – таблицами значений вторичных ключей. Таблица – это блок «показателей», справочники – это их разрезы. При этом справочник, в свою очередь, может быть произвольным несбалансированным деревом или сбалансированной иерархией, например, многоуровневой классификацией товаров или контрагентов. В OLAP-кубе числовые поля таблицы данных из warehouse автоматически становятся «показателями» (или измерениями measures), а посредством таблиц вторичных ключей могут быть определены разрезы (или размерности dimensions).

Это наглядное «педагогическое» описание. На самом деле архитектура OLAP-куба может быть значительно сложнее.

Во-первых, warehouse может состоять из нескольких «звездочек», возможно, связанных через общие справочники. В этом случае OLAP-куб будет объединением нескольких кубов (нескольких блоков данных).

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

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

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

MS Excel как интерфейс с OLAP

Отдельный интерес представляет интерфейс пользователя с OLAP-кубами. Естественно наиболее полный интерфейс предоставляет сама утилита SSAS. Это и инструментарий разработчика OLAP-кубов, и интерактивный конструктор отчетов, и окно интерактивной работы с OLAP-кубом посредством запросов на языке MDX.

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

Интерфейс с MS Excel обеспечивает специальный драйвер, отдельно загружаемый или включенный в поставку Excel. Он не охватывает всей функциональности OLAP, но с ростом номеров версий MS Excel этот охват становится все шире (скажем, в MS Excel 2007 появляется графическое изображение KPI, чего не было в MS Excel 2003 и т.п.).

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

Большим преимуществом MS Excel как интерфейса с OLAP является возможность дальнейшей самостоятельной обработки данных, полученных в отчете OLAP (т.е. продолжение исследования данных, полученных из OLAP на других листах того же Excel, уже не средствами OLAP, но обычными средствами Excel).

Еженощный цикл обработки facubi

Теперь опишем ежедневный (еженощный) вычислительный цикл эксплуатации OLAP. Расчет ведется под контролем программы facubi, написанной на C# 2005 и запускаемой посредством Task Scheduler на сервере с warehouse и SSAS. В начале facubi обращается к интернету и считывает текущие курсы валют (используются для представления ряда показателей в валюте). Далее выполняются следующие действия.

Во-первых, facubi запускает SP, выполняющие частичную репликацию баз данных различных ERP-систем (элементов холдинга), доступных в локальной сети. Репликация выполняется, как мы говорили, на заранее подготовленные «подворья» – образы удаленных ERP-систем, расположенные на сервере SSAS.

Во-вторых, посредством SP выполняется отображение из реплик ERP на хранилище warehouse – особую DB, являющуюся источником данных OLAP-куба и расположенную на сервере SSAS. При этом решаются три главные задачи:

• данные ERP подводятся под требуемые форматы куба; речь идет и о таблицах, и о полях таблиц. (Иногда требуемую таблицу нужно «вылепить», скажем, из нескольких листов MS Excel.) Аналогичные данные могут иметь разный формат в разных ERP, например, ключевые поля ID в справочниках 1С7 имеют 36-значный символьный код длиной 8, а поля _idrref в справочниках 1С8 – шестнадцатеричные числа длиной 32;
• по ходу обработки ведется логический контроль данных (в том числе прописывание «умолчаний» default на месте пропущенных данных, где это возможно) и контроль целостности, т.е. проверка наличия первичных и вторичных ключей в соответствующих классификаторах;
• консолидация кодов объектов, имеющих один и тот же смысл в разных ERP. Например, соответствующие элементы справочников разных ERP могут иметь один и тот же смысл, скажем, это один и тот же контрагент. Задача консолидации кодов решается посредством построения таблиц мэппинга, где различные коды одних и тех же объектов приводятся к единству.

В-третьих, facubi запускает стандартную процедуру обновления данных куба Process (из состава процедур утилиты SSAS).

Согласно контрольным спискам программа facubi рассылает почтовые сообщения о ходе выполнения этапов обработки.

Выполнив facubi, Task Scheduler запускает по очереди несколько файлов excel, в которых заранее созданы отчеты на базе показателей OLAP-куба. Как мы говорили, MS Excel имеет специальный программный интерфейс (отдельно загружаемый или встроенный драйвер) для работы с OLAP-кубами (с SSAS). При запуске MS Excel включаются программы на MS VBA (типа макросов), которые обеспечивают обновление данных в отчетах; отчеты при необходимости модифицируются и рассылаются по почте (программа blat) пользователям согласно контрольным спискам.

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

Оценка результатов

Мы говорили выше об асинхронности OLTP и OLAP. В рассматриваемом варианте технологии цикл обновления OLAP-куба выполняется ночью (скажем, запускается в 1 час ночи). Это означает, что в текущем рабочем дне пользователи работают со вчерашними данными. Поскольку OLAP – это не средство регистрации (посмотреть последнюю редакцию документа), а средство управления (понять тенденцию процесса), такое отставание обычно не критично. Впрочем, при необходимости даже в описанном варианте архитектуры куба (MOLAP) обновление возможно проводить несколько раз в сутки.

Время выполнения процедур обновления зависит от особенностей конструкции OLAP-куба (большей или меньшей комплексности, более или менее удачных определений показателей и разрезов) и от объема баз данных внешних OLTP-систем. По опыту процедуры построения warehouse занимают от нескольких минут до двух часов, процедура обновления куба (Process) – от 1 до 20 минут. Речь идет о комплексных OLAP-кубах, объединяющих десятки структур типа «звездочка», о десятках общих «лучей» (справочников-разрезов) для них, о сотнях показателей. Оценивая объемы баз данных внешних ERP-систем по документам отгрузки, мы говорим о сотнях тысяч документов и, соответственно, миллионах товарных строк в год. Историческая глубина обработки, интересующая пользователя, составляла три – пять лет.

Описанная технология эксплуатируется в ряде крупных корпораций: с 2008 года в «Русской рыбной компании» (РРК) и компании «Русское море» (РМ), с 2012 года в компании «Санта-Бремор» (СБ). Часть корпораций является по преимуществу торгово-закупочными фирмами (РРК), другие – производственными (заводы по переработке рыбы и морепродуктов РМ и СБ). Все корпорации являются крупными холдингами, объединяющими по несколько фирм с независимыми и различными системами компьютерного учета – начиная от стандартных ERP-систем типа 1C7 и 1C8 и заканчивая «реликтовыми» учетными системами на базе DBF и Excel. Добавлю, что описанная технология эксплуатации OLAP-кубов (без учета этапа разработки) либо вообще не требует специальных сотрудников, либо входит в круг обязанностей одного штатного бизнес-аналитика. Задача годами крутится в автоматическом режиме, ежедневно снабжая различные категории сотрудников корпораций актуальной отчетностью.

Плюсы и минусы решения

Как показывает опыт, вариант предложенного решения достаточно надежен и прост в эксплуатации. Он легко модифицируется (подключение/отключение новых ERP, создание новых показателей и разрезов, создание и модификация Excel-отчетов и списков их почтовой рассылки) при инвариантности управляющей программы facubi.

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

«Рафинированная» БД warehouse, в которой консолидировано (в ходе построения куба) несколько разнородных ERP-систем, даже без всякого OLAP позволяет решать (на сервере SSAS, методом запросов на языке Transact SQL или методом SP и др.) множество прикладных задач управления. Напомним, структура БД warehouse унифицирована и существенно проще (в плане количества таблиц и числа полей таблиц), чем структуры БД исходных ERP.

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

Мы использовали модель куба MOLAP. Плюсы этой модели – надежность в эксплуатации и высокая скорость обработки запросов пользователя. Минусы – асинхронность OLAP и OLTP, а также большие объемы памяти для хранения OLAP.

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

Технология OLTP, основанная на теории реляционных БД, была первой идеей Е. Кодда. По сути, концепция OLAP-кубов – это вторая его идея, высказанная им в начале 90-х годов. Даже не будучи математиком, вполне можно ожидать, что вторая идея окажется столь же эффективной, как первая. То есть в плане компьютерной аналитики идеи OLAP скоро захватят мир и вытеснят все другие. Просто потому, что тема аналитики находит в OLAP свое исчерпывающее математическое решение, и это решение «адекватно» (термин Б. Спинозы) практической задаче аналитики. «Адекватно» же означает у Спинозы, что и сам Бог не придумал бы лучше…

1. Ларсон Б. Разработка бизнес-аналитики в Microsoft SQL Server 2005. – СПб.: «Питер», 2008.
2. Codd E. Relational Completeness of Data Base Sublanguages, Data Base Systems, Courant Computer Science Sumposia Series 1972, v. 6, Englwood cliffs, N.Y., Prentice – Hall.

Вконтакте

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

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

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

www.bud-tech.ru

Возможно, для кого-то использование 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 ). Естественно, в отчет выводится столько кнопок со справочниками, сколько есть в строке бюджета, содержащей максимальное количество справочников. Если ни в одной строке бюджета нет ни одного справочника, то в отчете не будет ни одной кнопки со справочниками.

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

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

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

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

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

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

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

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

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

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

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

Рис. 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

В цикле статей «Введение в базы данных», публиковавшемся в последнее время (см. КомпьютерПресс №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’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных (ниже показан фрагмент):

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

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

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

Такой набор данных называется сводной таблицей (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 Отметим, однако, что результатом этого запроса будет не сама сводная таблица, а лишь набор агрегатных данных для ее построения (ниже показан фрагмент):

Третий из рассмотренных выше запросов имеет уже три параметра в условии 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

07.04.2011 Дерек Комингор

Если вам приходилось иметь дело с какой-либо областью, связанной с технологией, вы слышали, вероятно, термин «куб»; однако большинство обычных администраторов и разработчиков баз данных с этими объектами не работали. Кубы представляют собой действенную архитектуру данных для быстрого агрегирования многомерной информации. Если вашей организации требуется выполнить анализ больших объемов данных, то идеальным решением будет именно куб

Что такое куб?

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

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

Куб - центральная конструкция данных в оперативной системе анализа данных OLAP аналитических служб SQL Server (SSAS). Кубы обычно строятся из основной реляционной базы данных, называемой моделью размерностей, но представляют собой отдельные технические сущности. Логически куб является складом данных, который составлен из размерностей (dimensions) и измерений (measures). Размерности содержат описательные признаки и иерархии, в то время как измерения - это факты, которые вы описываете в размерностях. Измерения объединены в логические сочетания, которые называются группами измерений. Вы привязываете размерности к группам измерений на основе признака - степени детализации.

В файловой системе куб реализован как последовательность связанных бинарных файлов. Бинарная архитектура куба облегчает быстрое извлечение больших объемов многомерных данных.

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

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

Требования к программному обеспечению

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

Мой пример куба «Продажи через Интернет» будет построен на основе тестовой базы данных AdventureWorksDW 2005. Я буду строить тестовый куб из подмножества таблиц, найденных в тестовой базе данных, которые будут полезны для анализа данных о сбыте через Интернет. На рисунке 1 представлена основная схема таблиц базы данных. Поскольку я использую версию 2005, вы можете следовать моим указаниям, применяя либо SQL Server 2005, либо SQL Server 2008.

Учебную базу данных Adventure WorksDW 2005 можно найти на сайте CodePlex: msftdbprodsamples.codeplex.com. Найдите ссылку «SQL Server 2005 product sample databases are still available» (http://codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004). Учебная база данных содержится в файле AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId=11755).

Как уже упоминалось, необходимо иметь доступ к экземпляру SQL Server 2008 или 2005, в том числе SSAS и к компонентам Business Intelligence Development Studio (BIDS). Я буду использовать SQL Server 2008, так что вы можете увидеть некоторые тонкие различия, если используете SQL Server 2005.

Создание проекта SSAS

Первое, что вы должны сделать, - это создать проект SSAS, используя BIDS. Найдите BIDS в меню Start и далее в меню Microsoft SQL Server 2008/2005 подпункт SQL Server Business Intelligence Development Studio. При нажатии на эту кнопку запустится BIDS c экраном заставки по умолчанию. Создайте новый проект SSAS, выбрав File, New, Project. Вы увидите диалоговое окно New Project (новый проект), которое показано на экране 1. Выберите папку проекта Analysis Services Project и задайте описание этому проекту «SQLMAG_MyFirstCube». Нажмите кнопку ОК.

Когда проект будет создан, щелкните по нему правой кнопкой мыши в Solution Explorer и выберите в контекстном меню пункт свойств Properties. Теперь выберите раздел Deployment в левой части диалогового окна SQLMAG_MyFirstCube: Property Pages и проверьте установки значений для параметров Target Server и Database settings, как показано на экране 2. Если вы работаете в распределенной среде SQL Server, вам необходимо уточнить значение свойства Target Server именем сервера, на который вы собираетесь производить развертывание. Щелкните OK, когда вас устроят установленные значения параметров развертывания для данного проекта SSAS.

Определение источника данных

Первый объект, который нужно создать, - это источник данных. Объект источника данных обеспечивает схему и данные, используемые при построении связанных с кубом и расположенных в его основании объектов. Чтобы создать объект источника данных в BIDS, задействуйте мастер источников данных Data Source Wizard.

Начните работу мастера источника данных щелчком правой кнопкой мыши по папке Data Source на панели Solution Explorer, с выбора пункта New Data Source. Вы обнаружите, что создание объектов SSAS в BIDS имеет характер разработки. Сначала мастер проводит вас через процесс создания объекта и общие настройки. А затем вы открываете полученный объект SSAS в проектировщике и детально подстраиваете его, если нужно. Как только вы проходите экран приглашения, определите новое соединение с данными, нажимая кнопку New. Выберите и создайте новое соединение на основе Native OLEDB\SQL Server Native Client 10, указывающее на желательный для вас сервер SQL Server, который владеет нужным экземпляром базы данных. Вы можете использовать либо аутентификацию Windows, либо SQL Server, в зависимости от настроек окружающей среды SQL Server. Нажмите кнопку Test Connection, чтобы удостовериться, что вы правильно определили соединение с базой данных, а затем кнопку OK.

Далее следует Impersonation Infor­mation (информация о настрой­ке заимствования прав), которая, как и связь с данными, зависит от того, как устроена среда SQL Server. Заимствование прав - это контекст безопасности, на который полагается SSAS, обрабатывая свои объекты. Если вы управляете развертыванием на основном, единственном сервере (или ноутбуке), как, я полагаю, большинство читателей, вы можете просто выбрать вариант использования учетной записи службы Use the service account. Нажмите Next для завершения работы мастера источника данных и задайте AWDW2005 в качестве имени источника данных. Весьма удобно, что можно задействовать этот метод для целей тестирования, но в реальной производственной среде это не самая лучшая практика - использовать учетную запись службы. Лучше указать доменные учетные записи для заимствования прав подключения SSAS к источнику данных.

Представление источника данных

Для определенного вами источника данных на следующем шаге в процессе построения куба SSAS следует создать представление Data Source View (DSV). DSV обеспечивает возможность разделения схемы, которую ожидает ваш куб, от подобной схемы основной базы данных. В результате DSV можно использовать для того, чтобы расширить основную реляционную схему при построении куба. Некоторые из ключевых возможностей DSV для расширения схем источников данных включают именованные запросы, логические отношения между таблицами и именованные вычисляемые столбцы.

Пойдем дальше, щелкнем правой кнопкой мыши по папке DSV и выберем пункт New Data Source View, чтобы запустить мастер создания новых представлений DSV. В диалоговом окне, на шаге Select a Data Source, выберите соединение с реляционной базой данных и нажмите кнопку Next. Выберите таблицы FactInternetSales, DimProduct, DimTime, DimCustomer и щелкните кнопку с одиночной стрелкой направо, чтобы перенести эти таблицы в колонку Included. Наконец, кликните Next и завершите работу мастера, принимая имя по умолчанию и нажимая кнопку Finish.

На данном этапе у вас должно быть представление DSV, которое расположено под папкой Data Source Views в Solution Explorer. Выполните двойной щелчок по новому DSV, чтобы запустить конструктор DSV. Вы должны увидеть все четыре таблицы для данного DSV, как показано на рисунке 2.

Создание размерностей базы данных

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

Размерности базы данных и куба обеспечивают изящное решение для концепции, известной как «ролевые размерности». Ролевые размерности применяются, когда вам необходимо использовать единственную размерность в кубе многократно. Дата - прекрасный пример в данном экземпляре куба: вы будете строить единственную размерность даты и ссылаться на нее один раз для каждой даты, для которой хотите анализировать продажи через Интернет. Календарная дата будет первой размерностью, которую вы создадите. Щелкните правой кнопкой мышки по папке Dimensions в Solution Explorer и выберите пункт New Dimension, чтобы запустить мастер размерностей Dimension Wizard. Выберите пункт Use an existing table и щелкните Next на шаге выбора метода создания Select Creation Method. На шаге определения источника информации Specify Source Information укажите таблицу DimTime в раскрывающемся списке Main table и нажмите кнопку Next. Теперь, на шаге выбора признака размерности Select Dimension Attributes, вам необходимо отобрать атрибуты размерности времени. Выберите каждый атрибут, как показано на экране 3.

Нажмите Next. На завершающем шаге введите Dim Date в поле Name и нажмите кнопку Finish для завершения работы мастера размерности. Теперь вы должны увидеть новую размерность даты Dim Date, расположенную под папкой Dimensions в Solution Explorer.

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

Создание куба продаж по Интернету

Теперь, подготовив размерности базы данных, вы можете приступить к строительству куба. В Solution Explorer щелкните правой кнопкой мыши на папке Cubes и выберите New Cube для запуска мастера создания кубов Cube Wizard. В окне Select Creation Method выберите вариант использования существующих таблиц Use existing tables. Выберите таблицу FactInternetSales для Measure Group на шаге выбора таблицы групп измерения Select Measure Group Tables. Удалите флажок рядом с измерениями Promotion Key, Currency Key, Sales Territory Key и Revision Number на шаге Select Measures и нажмите Next.

На экране Select Existing Dimensions убедитесь, что все существующие размерности базы данных выбраны, чтобы использовать их далее как размерности куба. Поскольку мне хотелось бы сделать данный куб настолько простым, насколько это возможно, отмените выбор размерности FactInternetSales на шаге Select New Dimensions. Оставляя размерность FactInternetSales выбранной, вы создали бы то, что называется размерностью факта или вырожденной размерностью. Размерности факта - это размерности, которые были созданы с использованием основной таблицы фактов в противоположность традиционной таблице размерностей.

Нажмите кнопку Next, чтобы перей­ти к шагу Completing the Wizard, и введите «Мой первый куб» в поле имени куба. Нажмите кнопку Finish, чтобы завершить процесс работы мастера создания куба.

Развертывание и обработка куба

Теперь все готово к развертыванию и обработке первого куба. Щелкните правой кнопкой мыши по значку нового куба в Solution Explorer и выберите пункт Process. Вы увидите окно с сообщением о том, что содержание представляется устаревшим. Щелкните Yes для развертывания нового куба на целевом сервере SSAS. При развертывании куба вы посылаете файл XML for Analisis (XMLA) на целевой сервер SSAS, который создает куб на самом сервере. Как уже упоминалось, обработка куба заполняет его двоичные файлы на диске данными из основного источника, а также дополнительными метаданными, которые вы добавили (размерности, измерения и настройки куба).

Как только процесс развертывания будет завершен, появляется новое диалоговое окно Process Cube. Нажмите кнопку Run, чтобы начать процесс обработки куба, который открывается окном Process Progress. При завершении обработки нажмите кнопку Close (два раза, чтобы закрыть оба диалоговых окна) для завершения процессов развертывания и обработки куба.

Теперь вы построили, развернули и обработали свой первый куб. Вы можете просматривать этот новый куб, щелкая по нему правой кнопкой мыши в окне Solution Explorer и выбирая пункт Browse. Перетащите измерения в центр сводной таблицы, а атрибуты размерностей на строки и столбцы, чтобы исследовать свой новый куб. Обратите внимание, как быстро куб отрабатывает различные запросы с агрегированием. Теперь вы можете оценить неограниченную мощь и, значит, ценность для бизнеса, куба OLAP.

Дерек Комингор ([email protected]) - старший архитектор в компании B. I. Voyage, имеющей статус Microsoft Partner в области бизнес-аналитики. Имеет звание SQL Server MVP и несколько сертификатов Microsoft