Olap в узком смысле слова трактуется как: олап кубы

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 1. Подмножество витрины данных Adventure Works Internet Sales

Учебную базу данных 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 Information (информация о настройке заимствования прав), которая, как и связь с данными, зависит от того, как устроена среда 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

Синие стрелки - пути, которыми информация попадает в систему, зеленными – как информация в дальнейшем используется.

Информация о заказах заносится в систему 1с – dbf версия.
Загрузка данных «автообмен». Вообще – то это лишний шаг. Данные можно получать напрямую из dbf базы. Но программисты 1с решили что стандартный (для 1с) механизм выгрузки данных, принесет меньше вреда.
Раз в сутки изменения за прошедший день выгружаются в специально подготовленную базу MsSql – хранилище. Выгружается не вся информация, а только то, что нужно для кубов.
В принципе необязательно строить «хранилище». Данные для куба можно получать напрямую из базы 1с (MsSQL или dbf). Но в моем случае из 1с данные прошлых периодов периодически удаляются и очищаются справочники. Кроме того перед загрузкой в хранилище данные немного «чистятся».
Происходит пересчет куба – данные попадают в куб.

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

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

Любой пользователь может получить информацию разными путями:

Построить отчет самостоятельно на web-странице или в excel
Сначала использовался только excel, но возникало много проблем с тем, что екселевские файлы «разбредались», нужно было получить одну «точку входа» для выбора информации.
Поэтому был создан локальный сайт, на котором опубликованы страницы с PivotTable. Сотрудник, который хочет получить пару цифр «здесь и сейчас» заходит на этот сайт и строит отчет в нужной ему форме. Если человеку нужно использовать этот отчет в дальнейшем – он может написать заявку, чтобы его отчет опубликовали в SSRS или сам сохраняет его в excel.
Посмотреть стандартный отчет, опубликованный в SQL Server Reporting Services (SSRS)
Получить локальный куб – и вне офиса «вращать» данные с помощью excel
Подписаться на рассылку и получать стандартные отчеты из SSRS на e-mail
Отдел маркетинга кроме того использует программу CubeSlice. В ней можно создавать локальные кубы самостоятельно и гораздо удобнее, чем в excel

Локальные кубы

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

Как правило, поставщику тоже неудобно работать с большими отчетами. Поэтому большая часть, попробовав работать с локальными кубами, согласилась получать отчетность в таком виде. Список отчетов, которые формировал отдел маркетинга, значительно сократился. Оставшиеся тяжелые отчеты были реализованы в SSRS, созданы подписки (отчеты формируются автоматически и рассылаются поставщикам по расписанию)

Основные параметры системы

Конфигурация сервера:

процессор: 2xAMD Opteron 280
память: 4Gb
дисковые массивы:
операционная система: RAID 1 (зеркало) 2xSCSI 15k
данные: RAID 0+1 4xSCSI 10k

Согласитесь, такую машинку сложно назвать «мощным» сервером

Объем данных:

хранилище 10Гб, данные с 2002 года
агрегация 30%
Размер многомерной базы 350М
кол-во членов «больших измерений»: товары 25 тыс., адреса – 20 тыс.
кол-во документов в день - 400. среднее кол-во строк в документе - 30

Что в итоге получила компания:

Плюсы

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

Минусы:

Стоимость внедрения. Необходимо дополнительное оборудование и программное обеспечение.
Нехватка подготовленных специалистов. Расходы на обучение сотрудников it-отдела.

Аннотация: В настоящей лекции рассматриваются основы проектирования кубов данных для OLAP-хранилищ данных. На примере показана методика построения куба данных с помощью CASE-инструмента.

Цель лекции

Изучив материал настоящей лекции, вы будете знать:

что такое куб данных в OLAP-хранилище данных ;
как проектировать куб данных для OLAP-хранилищ данных ;
что такое измерение куба данных ;
как факт связан с кубом данных ;
что такое атрибуты измерения ;
что такое иерархия ;
что такое метрика куба данных ;

и научитесь:

строить многомерные диаграммы ;
проектировать простые многомерные диаграммы .

Введение

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

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

Централизация и удобное структурирование - это далеко не все, что нужно аналитику. Ему требуется инструмент для просмотра, визуализации информации. Традиционные отчеты, даже построенные на основе единого ХД, лишены, однако, определенной гибкости. Их нельзя "покрутить", "развернуть" или "свернуть", чтобы получить необходимое представление данных. Чем больше "срезов" и "разрезов" данных аналитик может исследовать, тем больше у него идей, которые, в свою очередь , для проверки требуют все новых и новых "срезов". В качестве такого инструмента для исследования данных аналитиком выступает OLAP .

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

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

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

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

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

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

Если исходные данные содержатся в настольной СУБД , вычисление агрегатных данных производится самим OLAP -средством. Если же источник исходных данных - серверная СУБД , многие из клиентских OLAP -средств посылают на сервер SQL -запросы, содержащие оператор GROUP BY , и в результате получают агрегатные данные, вычисленные на сервере.

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

Многие средства разработки содержат библиотеки классов или компонентов, позволяющие создавать приложения, реализующие простейшую OLAP -функциональность (такие, например, как компоненты Decision Cube в Borland Delphi и Borland C++Builder). Помимо этого многие компании предлагают элементы управления ActiveX и другие библиотеки, реализующие подобную функциональность.

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

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

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

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

Отметим, что многие клиентские OLAP -средства (в частности, Microsoft Excel 2003, Seagate Analysis и др.) позволяют обращаться к серверным OLAP-хранилищам , выступая в этом случае в роли клиентских приложений, выполняющих подобные запросы. Помимо этого имеется немало продуктов, представляющих собой клиентские приложения к OLAP -средствам различных производителей.

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

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

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

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

MOLAP ( Multidimensional OLAP) - исходные и агрегатные данные хранятся в многомерной базе данных. Хранение данных в многомерных структурах позволяет манипулировать данными как многомерным массивом, благодаря чему скорость вычисления агрегатных значений одинакова для любого из измерений . Однако в этом случае многомерная база данных оказывается избыточной, так как многомерные данные полностью содержат исходные реляционные данные.
ROLAP (Relational OLAP) - исходные данные остаются в той же реляционной базе данных, где они изначально и находились. Агрегатные же данные помещают в специально созданные для их хранения служебные таблицы в той же базе данных.
HOLAP ( Hybrid OLAP) - исходные данные остаются в той же реляционной базе данных, где они изначально находились, а агрегатные данные хранятся в многомерной базе данных.

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

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

Основные понятия OLAP

Тест FAMSI

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP - это ключевой компонент организации ХД. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information) - быстрый анализ разделяемой многомерной информации, включающий следующие требования к приложениям для многомерного анализа :

Fast (Быстрый) - предоставление пользователю результатов анализа за приемлемое время (обычно не более 5 с), пусть даже ценой менее детального анализа;
Analysis (Анализ) - возможность осуществления любого логического и статистического анализа, характерного для данного приложения, и его сохранения в доступном для конечного пользователя виде;
Shared (Разделяемый) - многопользовательский доступ к данным с поддержкой соответствующих механизмов блокировок и средств авторизованного доступа;
Multidimensional (Многомерный) - многомерное концептуальное представление данных, включая полную поддержку для иерархий и множественных иерархий (это ключевое требование OLAP);
Information (Информация) - приложение должно иметь возможность обращаться к любой нужной информации, независимо от ее объема и места хранения.

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

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

Кубы

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

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

Рис. 26.1.

"Разрезание" куба

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

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

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

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

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

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

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

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

Конкретные OLAP-продукты, как правило, представляют собой либо средство многомерного представления данных (OLAP-клиент - например, Pivot Tables в Excel 2000 фирмы Microsoft или ProClarity фирмы Knosys), либо многомерную серверную СУБД (OLAP-сервер - например, Oracle Express Server или Microsoft OLAP Services).

Слой многомерной обработки обычно бывает встроен в OLAP-клиент и/или в OLAP-сервер, но может быть выделен в чистом виде, как, например, компонент Pivot Table Service фирмы Microsoft.

/ В кубистической манере. Применение 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 свое исчерпывающее математическое решение, и это решение «адекватно» (термин Б. Спинозы) практической задаче аналитики. «Адекватно» же означает у Спинозы, что и сам Бог не придумал бы лучше…

Ларсон Б. Разработка бизнес-аналитики в Microsoft SQL Server 2005. – СПб.: «Питер», 2008.
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.

Вконтакте

Механизм OLAP является на сегодня одним из популярных методов анализа данных. Есть два основных подхода к решению этой задачи. Первый из них называется Multidimensional OLAP (MOLAP) – реализация механизма при помощи многомерной базы данных на стороне сервера, а второй Relational OLAP (ROLAP) – построение кубов "на лету" на основе SQL запросов к реляционной СУБД. Каждый из этих подходов имеет свои плюсы и минусы. Их сравнительный анализ выходит за рамки этой статьи. Мы же опишем нашу реализацию ядра настольного ROLAP модуля.

Такая задача возникла после применения ROLAP системы, построенной на основе компонентов Decision Cube, входящих в состав Borland Delphi. К сожалению, использование этого набора компонент показало низкую производительность на больших объемах данных. Остроту этой проблемы можно снизить, стараясь отсечь как можно больше данных перед подачей их для построения кубов. Но этого не всегда бывает достаточно.

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

Схема работы

Общую схему работы настольной OLAP системы можно представить следующим образом:

Алгоритм работы следующий:

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

Рассмотрим как подобная система может быть устроена внутри. Начнем мы это с той стороны, которую можно посмотреть и пощупать, то есть с отображений.

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

Кросс-таблица

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

Таким образом, таблицу можно разделить на следующие элементы, с которыми мы и будем работать в дальнейшем:

Заполняя матрицу с фактами, мы должны действовать следующим образом:

На основании данных об измерениях определить координаты добавляемого элемента в матрице.
Определить координаты столбцов и строк итогов, на которые влияет добавляемый элемент.
Добавить элемент в матрицу и соответствующие столбцы и строки итогов.

При этом нужно отметить то, что полученная матрица будет сильно разреженной, почему ее организация в виде двумерного массива (вариант, лежащий на поверхности) не только нерациональна, но, скорее всего, и невозможна в связи с большой размерностью этой матрицы, для хранения которой не хватит никакого объема оперативной памяти. Например, если наш куб содержит информацию о продажах за один год, и если в нем будет всего 3 измерения – Клиенты (250), Продукты (500) и Дата (365), то мы получим матрицу фактов следующих размеров:

Кол-во элементов = 250 х 500 х 365 = 45 625 000

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

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

Рассмотрим теперь, как можно определить координаты факта, зная соответствующие ему измерения. Для этого рассмотрим подробнее структуру заголовка:

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

Подготовка данных

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

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

Библиотека компонентов CubeBase

Описанные выше идеи были положены в основу при создании библиотеки компонентов CubeBase.

TСubeSource осуществляет кэширование и преобразование данных во внутренний формат, а также предварительное агрегирование данных. Компонент TСubeEngine осуществляет вычисление гиперкуба и операции с ним. Фактически, он является OLAP-машиной, осуществляющей преобразование плоской таблицы в многомерный набор данных. Компонент TCubeGrid выполняет вывод на экран кросс-таблицы и управление отображением гиперкуба. TСubeChart позволяет увидеть гиперкуб в виде графиков, а компонент TСubePivote управляет работой ядра куба.

Сравнение производительности

Данный набор компонент показал намного более высокое быстродействие, чем Decision Cube. Так на наборе из 45 тыс. записей компоненты Decision Cube потребовали 8 мин. на построение сводной таблицы. CubeBase осуществил загрузку данных за 7сек. и построение сводной таблицы за 4 сек. При тестировании на 700 тыс. записей Decision Cube мы не дождались отклика в течение 30 минут, после чего сняли задачу. CubeBase осуществил загрузку данных за 45 сек. и построение куба за 15 сек.

На объемах данных в тысячи записей CubeBase отрабатывал в десятки раз быстрее Decision Cube. На таблицах в сотни тысяч записей – в сотни раз быстрее. А высокая производительность – один из самых важных показателей OLAP систем.