OLAP. Что нам стоит куб построить
Автор: В. Кулибаба, www.axolap.ru
Intro
В ходе внедрения пакета аналитической отчетности AxOlap мы постоянно сталкиваемся с необходимостью обучения как технических специалистов заказчика так и бизнес-пользователей основам построения и использования OLAP кубов. Для того чтобы как то облегчить прежде всего самим себе этот процесс мы решили подготовить ряд статей. Эта статья рассчитана как на технических специалистов начинающих работу с OLAP кубами так и на бизнес-аналитиков которые хотят расширить свой кругозор. В качестве аналитической системы позволяющей строить кубы мы будем использовать Microsoft Analysis Server, а в качестве базы данных, которую мы будем анализировать - возьмем структуру популярной ERP системы Microsoft Dynamics Ax (далее Axapta). С первоначальными понятиями Olap кубов, для чего они нужны и что такое меры и измерения Вы можете ознакомиться в статье "ОЛАП по-русски" или в специализированной литературе.
С чего начнем?
Давайте построим несложный куб о состоянии текущих товарных запасов, остатков попросту говоря. Для незнакомых с системой Axapta придется привести небольшое описание структуры используемых таблиц, которое можно пропустить если Вы уже сталкивались с данной системой. Итак идеология системы в двух словах основана на следующем принципе, любое изменение состояния товарных запасов при помощи разноски складской проводки (операции) немедленно находит отражение в виде увеличения или уменьшения остатков в соответствующем статусе в таблице остатков. Т.е. если Вы оприходуете 2 единицы товара, запасы (остатки) увеличиваются на 2 единицы, если например резервируете под реализацию - увеличивается зарезервированное количество и соответственно уменьшается доступное для последующего резервирования количество. Это чем то напоминает механизм триггеров СУБД, только в роли событий "возбуждающих" триггер являются складские проводки. Важной особенностью данного механизма является то, что остатки всегда показывают состояние запасов не на некую дату (точку актуальности в терминах некоторых не будем говорить каких программ), а именно текущее состояние запасов с учетом всех завершенных операций, которое Вы можете проверить например с помощью инвентаризации.
Структура данных
Структура интересующих нас таблиц собственно достаточно тривиальна. Мы не будем приводить описания всех полей, а опишем только интересующие нас в данном примере. Итак, собственно таблица в которой содержатся остатки (inventsum) содержит поля:
- itemid – код товара, таблица inventtable
- inventdimid – код аналитики, таблица inventdim
- dataareaid – код компании (фирмы)
- postedqty – разнесенный остаток (штук на полке)
- postedvalue – стоимость запасов в первичной валюте (себестоимость)
Наверно стоит дополнительно остановиться только на таблице аналитик (inventdim). Разработчики Axapta не пошли по пути денормализации данных и хранят такие параметры как код склада, номер партии и т.п. не в таблице остатков, а в промежуточной таблице складских аналитик. Т.е. каждый набор аналитик, таких как склад, цвет, размер или номер партии в совокупности составляет некое уникальное сочетание аналитик, которое и хранится в таблице аналитик.
Ну-с приступим...
Наконец то можно перейти собственно к построению куба. Дальше постараемся действовать по принципу «лучше один раз увидеть».
Запустите Analysis Manager и создайте новую базу данных.
Показать полноразмерное изображение
Введите наименование создаваемой базы данных.
Показать полноразмерное изображение
Далее необходимо настроить источник данных. Таких источников может быть несколько.
Показать полноразмерное изображение
У нас в качестве источника будет выступать SQL сервер на котором расположена база данных.
Показать полноразмерное изображение
Строить куб начнем с того, что определим используемые измерения. Измерения могут быть как общими, доступными для всех кубов в рамках данной базы данных, так и частными(локальными), определенными для конкретного куба. Подход с использованием общих измерений более правильный и позволит в дальнейшем строить виртуальные кубы объединяющие разнородные данные.
Показать полноразмерное изображение
Для измерения "Товар" выберем соответствующую таблицу (inventtable) и определим код товара(itemid) как идентификатор измерения.
Показать полноразмерное изображение
Но помимо кода товара существует и его наименование. Для того чтобы предоставить возможность его отображения - определим свойство товара, member properties.
Показать полноразмерное изображение
В качестве поля свойства укажем наименование товара (itemname).
Показать полноразмерное изображение
Должно получиться вот так.
Показать полноразмерное изображение
Поля лучше называть по-русски и по возможности кратко. Не стоит использовать длинные предложения, так как длина наименования влияет на ширину колонки по умолчанию в отчете.
Показать полноразмерное изображение
Сохраним созданное измерение.
Показать полноразмерное изображение
И отпроцессим (рассчитаем) его.
Показать полноразмерное изображение
Аналогично создаем следующее измерение.
Показать полноразмерное изображение
В таблице dataarea хранятся идентификаторы компаний и в каждой таблице есть ссылка на компанию. Используя средства ОЛАП мы сможем дать возможность просматривать консолидированные произвольным образом данные в разрезе компаний.
Показать полноразмерное изображение
Ну и последнее "физическое" измерение на сегодня.
Показать полноразмерное изображение
Это складская аналитика, inventdim. Из вступления мы помним что с ним всё не так просто (это не справочник, а комбинация ссылок на справочники) и как это обойти мы рассмотрим далее.
Показать полноразмерное изображение
В данной таблице в частности хранится код склада, определим его как свойство измерения.
Показать полноразмерное изображение
Указав соответствующее поле.
Показать полноразмерное изображение
Должно получиться вот так.
Показать полноразмерное изображение
Затем мы опять идем создавать измерение но воспользуемся уже не редактором, а волшебником(мастером) создания измерений.
Показать полноразмерное изображение
И в качестве типа измерения укажем "виртуальное" измерение являющееся свойством другого измерения (помните как мы определили поле код склада в качестве свойства измерения аналитика?).
Показать полноразмерное изображение
Вот его теперь и выберем.
Показать полноразмерное изображение
Выбираем поле.
Показать полноразмерное изображение
И называем новое измерение.
Показать полноразмерное изображение
Почему мы сделали именно так? Если идти классическим путем - нам надо было либо определить связи меду таблицами аналитики и складов средствами Analysis Server (например так называемая снежинка), либо пойти альтернативным путем и создать хранилище данных, в котором создать денормализованную структуру средствами SQL сервер.
Показать полноразмерное изображение
Однако практика показывает что чем больше связей между сущностями или чем больше промежуточных слоев денормализации - тем медленнее и с большим количеством ошибок работает система в целом. Стройте простые и понятные кубы и у Вас все будет работать быстро и надежно как пулемет. Но для этого надо сначала подумать.
Показать полноразмерное изображение
Теперь мы готовы к созданию собственно куба.
Показать полноразмерное изображение
В качестве таблицы фактов укажем таблицу остатков (inventsum).
Показать полноразмерное изображение
И добавим существующие измерения в куб.
Показать полноразмерное изображение
Выберем их.
Показать полноразмерное изображение
И протянем связи между соответствующими полями и таблицами, так как это показано на рисунке.
Показать полноразмерное изображение
Теперь вставим необходимые меры простым щелчком по ним правой кнопкой мыши. Количество товара на полке.
Показать полноразмерное изображение
И его стоимость(себестоимость).
Показать полноразмерное изображение
Должно получиться примерно вот так.
Показать полноразмерное изображение
Сохраним куб.
Показать полноразмерное изображение
И отпроцессим его.
Показать полноразмерное изображение
Показать полноразмерное изображение
Здесь система предложит Вам создать дизайн агрегаций. Ответьте нет (не создавайте или создайте нулевой процент). Здесь надо сделать небольшое лирическое отступление. Никогда никому не верьте. Никогда. Никому. Если в очень умной книге написано что надо создавать максимальный процент агрегации для наибольшего быстродействия - попробуйте сделать наоборот. Что Вы потеряете в худшем случае? Убедитесь в правильности утверждения и поймете почему надо делать именно так, как написано на собственном опыте. Что Вы приобретете в лучшем случае? Возможно Вы убедитесь что при минимальном проценте агрегации куб процессится не час, а минуту, а отчеты открываются не 2 секунды, а 2,5 секунды. В худшем случае Вы лучше разберетесь, в лучшем через пару лет напишите свою книгу. Конечно если захотите.
Показать полноразмерное изображение
Ну и если мы все сделали правильно - то мы получим успешно отпроцессенный готовый куб. (Если у Вас выдаст предупреждение-ошибку о лимите на 64000 членов измерения - смотрите ниже).
Показать полноразмерное изображение
Так выглядят данные в кубе.
Показать полноразмерное изображение
А так в Excel. (Забыл об одном немаловажном моменте, чтобы получить доступ к кубу извне, необходимо настроить доступ (роли). Для этого надо запустить управление ролями и выбрать «Все» для простого случая, либо настроить доступ в разрезе пользователей-измерений-мер).
Показать полноразмерное изображение
Другой взгляд на наш куб через Microsoft Excel.
Показать полноразмерное изображение
О том как максимально использовать возможности Excel при работе с кубами - читайте нашу следующую статью на портале axolap.
Ограничения и фьючерсы. Скачать данный пример.
На "боевой" аксапте таблица аналитик будет иметь значительно больше 64000 элементов. В Analysis Server же есть данное ограничение. Чтобы его обойти достаточно создать виртуальный невидимый уровень. Скачать пример рассмотренного куба (включая пример обхода ограничения) и куб демонстрирующий использование развитых возможностей внутреннего языка Analysis Server (MDX) Вы можете на портале AxOlap.ru. Пакет называется «AxOlap: Старт» содержит инструкцию по работе с ним, настройке и разворачиванию, и, что немаловажно - абсолютно бесплатен.
Итого.
Конечно мы не рассмотрели и сотой доли возможностей этого отличного, на наш взгляд, сервера и десятой доли всех фьючерсов позволяющих максимально оптимально использовать его возможности, но впрочем целью статьи и не ставилось раскрыть какие то ноу-хау. Надеемся она поможет глубже понять аналитикам чего можно ожидать от инструмента, а техническим специалистам поможет начать с ним работать.
