В рамках данной работы будут рассмотрены следующие вопросы:
Главный постулат OLAP - многомерность в представлении данных. В терминологии OLAP для описания многомерного дискретного пространства данных используется понятие куба, или гиперкуба.
Куб представляет собой многомерную структуру данных, из которой пользователь-аналитик может запрашивать информацию. Кубы создаются из фактов и измерений.
Факты - это данные об объектах и событиях в компании, которые будут подлежать анализу. Факты одного типа образуют меры (measures). Мера есть тип значения в ячейке куба.
Измерения - это элементы данных, по которым производится анализ фактов. Коллекция таких элементов формирует атрибут измерения (например, дни недели могут образовать атрибут измерения "время"). В задачах бизнес-анализа коммерческих предприятий в качестве измерений часто выступают такие категории, как "время", "продажи", "товары", "клиенты", "сотрудники", "географическое местоположение". Измерения чаще всего являются иерархическими структурами, представляющими собой логические категории, по которым пользователь может анализировать фактические данные. Каждая иерархия может иметь один или несколько уровней. Так иерархия измерения "географическое местоположение" может включать уровни: "страна - область - город". В иерархии времени можно выделить, например, такую последовательность уровней: Измерение может иметь несколько иерархий (при этом каждая иерархия одного измерения должна иметь один и тот же ключевой атрибут таблицы измерений).
Куб может содержать фактические данные из одной или нескольких таблиц фактов и чаще всего содержит несколько измерений. Любой конкретный куб обычно имеет конкретный направленный предмет анализа.
На рисунке 1 показан пример куба, предназначенного для анализа продаж продуктов нефтепереработки некоторой компанией по регионам. Данный куб имеет три измерения (время, товар и регион) и одну меру (объем продаж, выраженный в денежном эквиваленте). Значения мер хранятся в соответствующих ячейках (cell) куба. Каждая ячейка уникально идентифицируется набором членов каждого из измерений, называемого кортежем. Например, ячейка, расположенная в нижнем левом углу куба (содержит значение $98399), задается кортежем [Июль 2005, Дальний Восток, Дизель]. Здесь значение $98399 показывают объем продаж (в денежном выражении) дизеля на Дальнем Востоке за июль 2005 года.
Стоит обратите также внимание на то, что некоторые ячейки не содержат никаких значений: эти ячейки пусты, потому что в таблице фактов не содержится данных для них.
Рис. 1. Куб с информацией о продажах нефтепродуктов в различных регионах
Конечной целью создания подобных кубов является минимизация времени обработки запросов, извлекающих требуемую информацию из фактических данных. Для реализации этой задачи кубы обычно содержат предварительно вычисленные итоговые данные, называемые агрегациями (aggregations). Т.е. куб охватывает пространство данных большее, чем фактическое - в нем существуют логические, вычисляемые точки. Вычислять значения точек в логическом пространстве на основе фактических значений позволяют функции агрегирования. Наиболее простыми функциями агрегирования являются SUM, MAX, MIN, COUNT. Так, например, используя функцию MAX, для приведенного в примере куба можно выявить, когда произошел пик продаж дизеля на Дальнем Востоке и т.д.
Еще одной специфической чертой многомерных кубов является сложность определения точки начала координат. Например, как задать точку 0 для измерения "Товар" или "Регионы"? Решением этой проблемы является внедрение специального атрибута, объединяющего все элементы измерения. Этот атрибут (создается автоматически) содержит всего один элемент - All ("Все"). Для простых функций агрегирования, например, суммы, элемент All эквивалентен сумме значений всех элементов фактического пространства данного измерения.
Важной концепцией многомерной модели данных является подпространство, или подкуб (sub cube). Подкуб представляет собой часть полного пространства куба в виде некоторой многомерной фигуры внутри куба. Так как многомерное пространство куба дискретно и ограничено, подкуб также дискретен и ограничен.
Операции над OLAP-кубами
Над OLAP-кубом могут выполняться следующие операции:
Рис. 2. Срез OLAP-куба
Вращение (рисунок 3) - операция изменения расположения измерений, представленных в отчете или на отображаемой странице. Например, операция вращения может заключаться в перестановке местами строк и столбцов таблицы. Кроме того, вращением куба данных является перемещение внетабличных измерений на место измерений, представленных на отображаемой странице, и наоборот.
/ В кубистической манере. Применение 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).
Несколько причин заставляют поступить именно так.
Можно еще добавить, что 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. При этом решаются три главные задачи:
В-третьих, 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 свое исчерпывающее математическое решение, и это решение «адекватно» (термин Б. Спинозы) практической задаче аналитики. «Адекватно» же означает у Спинозы, что и сам Бог не придумал бы лучше…
Вконтакте
В цикле статей «Введение в базы данных», публиковавшемся в последнее время (см. КомпьютерПресс №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). Он же сформулировал и основные требования к хранилищам данных:
Удовлетворять всем перечисленным требованиям в рамках одного и того же продукта зачастую не удается. Поэтому для реализации хранилищ данных обычно используется несколько продуктов, одни их которых представляют собой собственно средства хранения данных, другие - средства их извлечения и просмотра, третьи - средства их пополнения и т.д.
Типичное хранилище данных, как правило, отличается от обычной реляционной базы данных. Во-первых, обычные базы данных предназначены для того, чтобы помочь пользователям выполнять повседневную работу, тогда как хранилища данных предназначены для принятия решений. Например, продажа товара и выписка счета производятся с использованием базы данных, предназначенной для обработки транзакций, а анализ динамики продаж за несколько лет, позволяющий спланировать работу с поставщиками, - с помощью хранилища данных.
Во-вторых, обычные базы данных подвержены постоянным изменениям в процессе работы пользователей, а хранилище данных относительно стабильно: данные в нем обычно обновляются согласно расписанию (например, еженедельно, ежедневно или ежечасно - в зависимости от потребностей). В идеале процесс пополнения представляет собой просто добавление новых данных за определенный период времени без изменения прежней информации, уже находящейся в хранилище.
И в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.
Системы поддержки принятия решений обычно обладают средствами предоставления пользователю агрегатных данных для различных выборок из исходного набора в удобном для восприятия и анализа виде. Как правило, такие агрегатные функции образуют многомерный (и, следовательно, нереляционный) набор данных (нередко называемый гиперкубом или метакубом), оси которого содержат параметры, а ячейки - зависящие от них агрегатные данные . Вдоль каждой оси данные могут быть организованы в виде иерархии, представляющей различные уровни их детализации. Благодаря такой модели данных пользователи могут формулировать сложные запросы, генерировать отчеты, получать подмножества данных.
Технология комплексного многомерного анализа данных получила название 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 - быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:
Следует отметить, что 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 .
Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:
Переведем эти вопросы в запросы на языке SQL (табл. 1).
Результатом любого из перечисленных выше запросов является число. Если в первом из запросов заменить параметр ‘France’ на ‘Austria’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных (ниже показан фрагмент):
| Country | SUM (ExtendedPrice) |
| Argentina | 7327.3 |
| Austria | 110788.4 |
| Belgium | 28491.65 |
| Brazil | 97407.74 |
| Canada | 46190.1 |
| Denmark | 28392.32 |
| Finland | 15296.35 |
| France | 69185.48 |
| Germany | 209373.6 |
| … | … |
Полученный набор агрегатных значений (в данном случае - сумм) может быть интерпретирован как одномерный набор данных. Этот же набор данных можно получить и в результате запроса с предложением GROUP BY следующего вида:
SELECT Country, SUM (ExtendedPrice) FROM invoices1 GROUP BY Country
Теперь обратимся ко второму из приведенных выше запросов, который содержит два условия в предложении WHERE. Если выполнять этот запрос, подставляя в него все возможные значения параметров Country и ShipperName, мы получим двухмерный набор данных следующего вида (ниже показан фрагмент):
| ShipperName | |||
| Country | Federal Shipping | Speedy Express | United Package |
| Argentina | 1 210.30 | 1 816.20 | 5 092.60 |
| Austria | 40 870.77 | 41 004.13 | 46 128.93 |
| Belgium | 11 393.30 | 4 717.56 | 17 713.99 |
| Brazil | 16 514.56 | 35 398.14 | 55 013.08 |
| Canada | 19 598.78 | 5 440.42 | 25 157.08 |
| Denmark | 18 295.30 | 6 573.97 | 7 791.74 |
| Finland | 4 889.84 | 5 966.21 | 7 954.00 |
| France | 28 737.23 | 21 140.18 | 31 480.90 |
| Germany | 53 474.88 | 94 847.12 | 81 962.58 |
| … | … | … | … |
Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей (cross table, crosstab). Создавать подобные таблицы позволяют многие электронные таблицы и настольные СУБД - от Paradox для DOS до Microsoft Excel 2000. Вот так, например, выглядит подобный запрос в Microsoft Access 2000:
TRANSFORM Sum(Invoices1.ExtendedPrice) AS SumOfExtendedPrice SELECT Invoices1.Country FROM Invoices1 GROUP BY Invoices1.Country PIVOT Invoices1.ShipperName;
Агрегатные данные для подобной сводной таблицы можно получить и с помощью обычного запроса GROUP BY:
SELECT Country,ShipperName, SUM (ExtendedPrice) FROM invoices1 GROUP BY COUNTRY,ShipperName Отметим, однако, что результатом этого запроса будет не сама сводная таблица, а лишь набор агрегатных данных для ее построения (ниже показан фрагмент):
| Country | ShipperName | SUM (ExtendedPrice) |
| Argentina | Federal Shipping | 845.5 |
| Austria | Federal Shipping | 35696.78 |
| Belgium | Federal Shipping | 8747.3 |
| Brazil | Federal Shipping | 13998.26 |
| … | … | … |
Третий из рассмотренных выше запросов имеет уже три параметра в условии WHERE. Варьируя их, мы получим трехмерный набор данных (рис. 2).
Ячейки куба, показанного на рис. 2 , содержат агрегатные данные, соответствующие находящимся на осях куба значениям параметров запроса в предложении WHERE.
Можно получить набор двухмерных таблиц с помощью сечения куба плоскостями, параллельными его граням (для их обозначения используют термины cross-sections и slices).
Очевидно, что данные, содержащиеся в ячейках куба, можно получить и с помощью соответствующего запроса с предложением GROUP BY. Кроме того, некоторые электронные таблицы (в частности, Microsoft Excel 2000) также позволяют построить трехмерный набор данных и просматривать различные сечения куба, параллельные его грани, изображенной на листе рабочей книги (workbook).
Если в предложении WHERE содержится четыре или более параметров, результирующий набор значений (также называемый OLAP-кубом) может быть 4-мерным, 5-мерным и т.д.
Рассмотрев, что представляют собой многомерные OLAP-кубы, перейдем к некоторым ключевым терминам и понятиям, используемым при многомерном анализе данных.
Наряду с суммами в ячейках OLAP-куба могут содержаться результаты выполнения иных агрегатных функций языка SQL, таких как MIN, MAX, AVG, COUNT, а в некоторых случаях - и других (дисперсии, среднеквадратичного отклонения и т.д.). Для описания значений данных в ячейках используется термин summary (в общем случае в одном кубе их может быть несколько), для обозначения исходных данных, на основе которых они вычисляются, - термин measure, а для обозначения параметров запросов - термин dimension (переводимый на русский язык обычно как «измерение», когда речь идет об OLAP-кубах, и как «размерность», когда речь идет о хранилищах данных). Значения, откладываемые на осях, называются членами измерений (members).
Говоря об измерениях, следует упомянуть о том, что значения, наносимые на оси, могут иметь различные уровни детализации. Например, нас может интересовать суммарная стоимость заказов, сделанных клиентами в разных странах, либо суммарная стоимость заказов, сделанных иногородними клиентами или даже отдельными клиентами. Естественно, результирующий набор агрегатных данных во втором и третьем случаях будет более детальным, чем в первом. Заметим, что возможность получения агрегатных данных с различной степенью детализации соответствует одному из требований, предъявляемых к хранилищам данных, - требованию доступности различных срезов данных для сравнения и анализа.
Поскольку в рассмотренном примере в общем случае в каждой стране может быть несколько городов, а в городе - несколько клиентов, можно говорить об иерархиях значений в измерениях. В этом случае на первом уровне иерархии располагаются страны, на втором - города, а на третьем - клиенты (рис. 3).
Отметим, что иерархии могут быть сбалансированными (balanced), как, например, иерархия, представленная на рис. 3 , а также иерархии, основанные на данных типа «дата-время», и несбалансированными (unbalanced). Типичный пример несбалансированной иерархии - иерархия типа «начальник-подчиненный» (ее можно построить, например, используя значения поля Salesperson исходного набора данных из рассмотренного выше примера), представлен на рис. 4 .
Иногда для таких иерархий используется термин Parent-child hierarchy.
Существуют также иерархии, занимающие промежуточное положение между сбалансированными и несбалансированными (они обозначаются термином ragged - «неровный»). Обычно они содержат такие члены, логические «родители» которых находятся не на непосредственно вышестоящем уровне (например, в географической иерархии есть уровни Country, City и State, но при этом в наборе данных имеются страны, не имеющие штатов или регионов между уровнями Country и City; рис. 5).
Отметим, что несбалансированные и «неровные» иерархии поддерживаются далеко не всеми OLAP-средствами. Например, в Microsoft Analysis Services 2000 поддерживаются оба типа иерархии, а в Microsoft OLAP Services 7.0 - только сбалансированные. Различным в разных OLAP-средствах может быть и число уровней иерархии, и максимально допустимое число членов одного уровня, и максимально возможное число самих измерений.
В данной статье мы ознакомились с основами OLAP. Мы узнали следующее:
Кроме того, мы рассмотрели основные принципы логической организации OLAP-кубов, а также узнали основные термины и понятия, применяемые при многомерном анализе. И наконец, мы выяснили, что представляют собой различные типы иерархий в измерениях OLAP-кубов.
В следующей статье данного цикла мы рассмотрим типичную структуру хранилищ данных, поговорим о том, что представляет собой клиентский и серверный OLAP, а также остановимся на некоторых технических аспектах многомерного хранения данных.
КомпьютерПресс 4"2001
Кубы данных OLAP (Online Analytical Processing - оперативный анализ данных) позволяют эффективно извлекать и анализировать многомерные данные. В отличие от других типов баз данных, базы данных OLAP разработаны специально для аналитической обработки и быстрого извлечения из них всевозможных наборов данных. На самом деле существует несколько ключевых различий между стандартными реляционными базами данных, такими как Access или SQL Server, и базами данных OLAP.
Рис. 1. Для подключения куба OLAP к книге Excel воспользуйтесь командой Из служб аналитики
Скачать заметку в формате или
В реляционных базах данных информация представляется в виде записей, которые добавляются, удаляются и обновляются последовательно. В базах данных OLAP хранится только моментальный снимок данных. В базе данных OLAP информация заархивирована в виде единого блока данных и предназначается только для вывода по запросу. Хотя в базу данных OLAP и можно добавлять новую информацию, существующие данные редко редактируются и тем более удаляются.
Реляционные базы данных и базы данных OLAP различаются структурно. Реляционные базы данных обычно состоят из набора таблиц, которые связаны между собой. В отдельных случаях реляционная база данных содержит так много таблиц, что очень сложно определить, как же они все-таки связаны. В базах данных OLAP связь между отдельными блоками данных определяется заранее и сохраняется в структуре, известной под названием кубы OLAP. В кубах данных хранятся полные сведения об иерархической структуре и связях базы данных, которые значительно упрощают навигацию по ней. К тому же создавать отчеты намного проще, если заранее известно, где располагаются извлекаемые данные и какие еще данные с ними связаны.
Основная же разница между реляционными базами данных и базами данных OLAP заключается в способе хранения информации. Данные в кубе OLAP редко представлены в общем виде. Кубы данных OLAP обычно содержат информацию, представленную в заранее разработанном формате. Таким образом, операции группировки, фильтрации, сортировки и объединения данных в кубах выполняются перед заполнением их информацией. Это делает извлечение и вывод запрашиваемых данных максимально упрощенной процедурой. В отличие от реляционных баз данных, нет необходимости в упорядочении информации должным образом перед выводом ее на экран.
Базы данных OLAP обычно создаются и поддерживаются администраторами IT-отдела. Если в вашей организации нет структуры, которая отвечает за управление базами данных OLAP, то можете обратиться к администратору реляционной базы данных с просьбой реализовать в корпоративной сети хотя бы отдельные OLAP-решения.
Подключение к кубу данных OLAP
Чтобы получить доступ к базе данных OLAP, сначала нужно установить подключение к кубу OLAP. Начните с перехода на вкладку ленты Данные . Щелкните на кнопке Из других источников и выберите в раскрывающемся меню команду Из служб аналитики (рис. 1).
При выборе указанной команды мастера подключения к данным (рис. 2). Основная его задача - это помочь вам установить соединение с сервером, который будет использован программой Excel при управлении данными.
1. Сначала нужно предоставить Excel регистрационную информацию. Введите в полях диалогового окна имя сервера, регистрационное имя и пароль доступа к данным, как показано на рис. 2. Щелкните на кнопке Далее . Если вы подключаетесь с помощью учетной записи Windows, то установите переключатель Использовать проверку подлинности Windows .
2. Выберите в раскрывающемся списке базу данных, с которой будете работать (рис. 3). В текущем примере используется база данных Analysis Services Tutorial. После выбора этой базы данных в расположенном ниже списке предлагается импортировать все доступные в ней кубы OLAP. Выберите необходимый куб данных и щелкните на кнопке Далее .
Рис. 3. Выберите рабочую базу данных и куб OLAP, который планируете применять для анализа данных
3. В следующем диалоговом окне мастера, показанном на рис. 4, вам требуется ввести описательную информацию о создаваемом подключении. Все поля диалогового окна, показанного на рис. 4, не обязательны для заполнения. Вы всегда можете проигнорировать текущее диалоговое окно, не заполняя его, и это никак не скажется на подключении.
Рис. 4. Измените описательную информацию о соединении
4. Щелкните на кнопке Готово , чтобы завершить создание подключения. На экране появится диалоговое окно Импорт данных (рис. 5). Установите переключатель Отчет сводной таблицы и щелкните на кнопке ОК, чтобы начать создание сводной таблицы.
Структура куба OLAP
В процессе создания сводной таблицы на основе базы данных OLAP вы заметите, что окно области задач Поля сводной таблицы будет отличаться от такового для обычной сводной таблицы. Причина кроется в упорядочении сводной таблицы так, чтобы максимально близко отобразить структуру куба OLAP, присоединенного к ней. Чтобы максимально быстро перемещаться по кубу OLAP, необходимо детально ознакомиться с его компонентами и способами их взаимодействия. На рис. 6 показана базовая структура типичного куба OLAP.
Как видите, основные компоненты куба OLAP – это размерности, иерархии, уровни, члены и меры:
Теперь, когда вы ознакомились со структурой кубов OLAP, давайте по-новому взглянем на список полей сводной таблицы. Организация доступных полей становится понятной и не вызывает нареканий. На рис. 7 показано, как в списке полей представляются элементы сводной таблицы OLAP.
В списке полей сводной таблицы OLAP меры выводятся первыми и обозначаются значком суммирования (сигма). Это единственные элементы данных, которые могут находиться в области ЗНАЧЕНИЯ. После них в списке указываются размерности, обозначенные значком с изображением таблицы. В нашем примере используется размерность Customer. В эту размерность вложен ряд иерархий. После развертывания иерархии можно ознакомиться с отдельными уровнями данных. Для просмотра структуры данных куба OLAP достаточно перемещаться по списку полей сводной таблицы.
Ограничения, накладываемые на сводные таблицы OLAP
Работая со сводными таблицами OLAP, следует помнить, что взаимодействие с источником данных сводной таблицы осуществляется в среде Analysis Services OLAP. Это означает, что каждый поведенческий аспект куба данных, начиная с размерностей и заканчивая мерами, которые включены в куб, также контролируется аналитическими службами OLAP. В свою очередь, это приводит к ограничениям, накладываемым на операции, которые можно выполнять в сводных таблицах OLAP:
Создание автономных кубов данных
В стандартной сводной таблице исходные данные хранятся на локальном жестком диске. Таким образом, вы всегда можете управлять ими, а также изменять структуру, даже не имея доступа к сети. Но это ни в коей мере не касается сводных таблиц OLAP. В сводных таблицах OLAP кеш не находится на локальном жестком диске. Поэтому сразу же после отключения от локальной сети ваша сводная таблица OLAP утратит работоспособность. Вы не сможете переместить ни одного поля в такой таблице.
Если все же нужно анализировать OLAP-данные при отсутствии подключения к сети, создайте автономный куб данных. Это отдельный файл, который представляет собой кеш сводной таблицы. В этом файле хранятся OLAP-данные, просматриваемые после отключения от локальной сети. Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в сводную таблицу и щелкните на кнопке Средства OLAP контекстной вкладки Анализ, входящей в набор контекстных вкладок Работа со сводными таблицами . Выберите команду Автономный режим OLAP (рис. 8).
На экране появится диалоговое окно Настройка автономной работы OLAP (рис. 9). Щелкните на кнопке Создать автономный файл данных . На экране появится первое окно мастера создания файла куба данных. Щелкните на кнопке Далее , чтобы продолжить процедуру.
На втором шаге (рис. 10), укажите размерности и уровни, которые будут включаться в куб данных. В диалоговом окне необходимо выбрать данные, импортируемые из базы данных OLAP. Нужно выделить только те размерности, которые понадобятся после отключения компьютера от локальной сети. Чем больше размерностей укажете, тем больший размер будет иметь автономный куб данных.
Щелкните на кнопке Далее для перехода к третьему шагу (рис. 11). В этом окне нужно выбрать члены или элементы данных, которые не будут включаться в куб. Если флажок не установлен, указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.
Укажите расположение и имя куба данных (рис. 12). Файлы кубов данных имеют расширение.cub.
Спустя некоторое время Excel сохранит автономный куб данных в указанной папке. Чтобы протестировать его, дважды щелкните на файле, что приведет к автоматической генерации рабочей книги Excel, которая содержит сводную таблицу, связанную с выбранным кубом данных. После создания вы можете распространить автономный куб данных среди всех заинтересованных пользователей, которые работают в режиме отключенной локальной сети.
После подключения к локальной сети можно открыть файл автономного куба данных и обновить его, а также соответствующую таблицу данных. Учтите, что хотя автономный куб данных применяется при отсутствии доступа к сети, он в обязательном порядке обновляется после восстановления подключения к сети. Попытка обновления автономного куба данных после разрыва соединения с сетью приведет к сбою.
Применение функций куба данных в сводных таблицах
Функции куба данных, которые применяются в базах данных OLAP, могут запускаться и из сводной таблицы. В устаревших версиях Excel вы получали доступ к функциям кубов данных только после установки надстройки Пакет анализа. В Excel 2013 данные функции встроены в программу, а потому доступны для применения. Чтобы в полной мере ознакомиться с их возможностями, рассмотрим конкретный пример.
Один из самых простых способов изучения функций куба данных заключается в преобразовании сводной таблицы OLAP в формулы куба данных. Эта процедура очень простая и позволяет быстро получить формулы куба данных, не создавая их «с нуля». Ключевой принцип - заменить все ячейки в сводной таблице формулами, которые связаны с базой данных OLAP. На рис. 13 показана сводная таблица, связанная с базой данных OLAP.
Поместите курсор в любом месте сводной таблицы, щелкните на кнопке Средства OLAP контекстной вкладки ленты Анализ и выберите команду Преобразовать в формулы (рис. 14).
Если ваша сводная таблица содержит поле фильтра отчета, то на экране появится диалоговое окно, показанное на рис. 15. В этом окне следует указать, нужно ли преобразовывать в формулы раскрывающиеся списки фильтров данных. При положительном ответе раскрывающиеся списки будут удалены, а вместо них будут отображены статические формулы. Если же вы в дальнейшем планируете использовать раскрывающиеся списки для изменения содержимого сводной таблицы, то сбросьте единственный флажок диалогового окна. Если вы работаете над сводной таблицей в режиме совместимости, то фильтры данных будут преобразовываться в формулы автоматически, без предварительного предупреждения.
Спустя несколько секунд вместо сводной таблицы отобразятся формулы, которые выполняются в кубах данных и обеспечивают вывод в окне Excel необходимой информации. Обратите внимание на то, что при этом удаляются ранее примененные стили (рис. 16).
Рис. 16. Взгляните на строку формул: в ячейках содержатся формулы куба данных
Учитывая то, что просматриваемые вами значения теперь не являются частью объекта сводной таблицы, можно добавлять столбцы, строки и вычисляемые элементы, комбинировать их с другими внешними источниками, а также изменять отчет самыми разными способами, в том числе и перетаскивая формулы.
Добавление вычислений в сводные таблицы OLAP
В предыдущих версиях Excel в сводных таблицах OLAP не допускались пользовательские вычисления. Это означает, что в сводные таблицы OLAP было невозможно добавить дополнительный уровень анализа подобно тому, как это делается в обычных сводных таблицах, допускающих добавление вычисляемых полей и элементов (подробнее см. ; прежде чем продолжить чтение, убедитесь, что вы хорошо знакомы с этим материалом).
В Excel 2013 появились новые инструменты OLAP - вычисляемые меры и вычисляемые элементы многомерных выражений. Теперь вы не ограничены использованием мер и элементов в кубе OLAP, предоставленных администратором базы данных. Вы получаете дополнительные возможности анализа путем создания пользовательских вычислений.
Знакомство с MDX. При использовании сводной таблицы вместе с кубом OLAP вы отсылаете базе данных запросы MDX (Multidimensional Expressions - многомерные выражения). MDX - это язык запросов, применяемый для получения данных из многомерных источников (например, из кубов OLAP). В случае изменения или обновления сводной таблицы OLAP соответствующие запросы MDX передаются базе данных OLAP. Результаты выполнения запроса возвращаются обратно в Excel и отображаются в области сводной таблицы. Таким образом обеспечивается возможность работы с данными OLAP без локальной копии кеша сводных таблиц.
При создании вычисляемых мер и элементов многомерных выражений применяется синтаксис языка MDX. С помощью этого синтаксиса сводная таблица обеспечивает взаимодействие вычислений с серверной частью базы данных OLAP. Примеры, рассматриваемые в книге, основаны на базовых конструкциях MDX, демонстрирующих новые функции Excel 2013. Если необходимо создавать сложные вычисляемые меры и элементы многомерных выражений, придется потратить время на более глубокое изучение возможностей MDX.
Создание вычисляемых мер. Вычисляемая мера представляет собой OLAP-версию вычисляемого поля. Идея заключается в создании нового поля данных на основе некоторых математических операций, выполняемых по отношению к существующим полям OLAP. В примере, показанном на рис. 17, используется сводная таблица OLAP, которая включает перечень и количество товаров, а также доход от продажи каждого из них. Нужно добавить новую меру, которая будет вычислять среднюю цену за единицу товара.
Анализ Работа со сводными таблицами . В раскрывающемся меню Средства OLAP выберите пункт (рис. 18).
Рис. 18. Выберите пункт меню Вычисляемая мера многомерного выражения
На экране появится диалоговое окно Создание вычисляемой меры (рис. 19).
Выполните следующие действия:
2. Выберите группу мер, в которой будет находиться новая вычисляемая мера. Если этого не сделать, Excel автоматически поместит новую меру в первую доступную группу мер.
3. В поле Многомерное выражение (MDX) введите код, задающий новую меру. Чтобы ускорить процесс ввода, воспользуйтесь находящимся слева списком для выбора существующих мер, которые будут использованы в вычислениях. Дважды щелкните на нужной мере, чтобы добавить ее в поле Многомерное выражение. Для вычисления средней цены продажи единицы товара используется следующее многомерное выражение:
4. Кликните ОК.
Обратите внимание на кнопку Проверить MDX , которая находится в правой нижней части окна. Щелкните на этой кнопке, чтобы проверить корректность синтаксиса многомерного выражения. Если синтаксис содержит ошибки, отобразится соответствующее сообщение.
После завершения создания новой вычисляемой меры перейдите в список Поля сводной таблицы и выберите ее (рис. 20).
Область действия вычисляемой меры распространяется только на текущую книгу. Другими словами, вычисляемые меры не создаются непосредственно в кубе OLAP сервера. Это означает, что никто не сможет получить доступ к вычисляемой мере, если только вы не откроете общий доступ к рабочей книге либо не опубликуете ее в Интернете.
Создание вычисляемых элементов многомерных выражений. Вычисляемый элемент многомерного выражения представляет собой OLAP-версию обычного вычисляемого элемента. Идея заключается в создании нового элемента данных, основанного на некоторых математических операциях, выполняемых по отношению к существующим элементам OLAP. В примере, показанном на рис. 22, используется сводная таблица OLAP, включающая сведения о продажах за 2005–2008 годы (с поквартальной разбивкой). Предположим, нужно выполнить агрегирование данных, относящихся к первому и второму кварталам, создав новый элемент First Half of Year (Первая половина года). Также объединим данные, относящиеся к третьему и четвертому кварталам, сформировав новый элемент Second Half of Year (Вторая половина года).
Рис. 22. Мы собираемся добавить новые вычисляемые элементы многомерных выражений, First Half of Year и Second Half of Year
Поместите курсор в любом месте сводной таблицы и выберите контекстную вкладку Анализ из набора контекстных вкладок Работа со сводными таблицами . В раскрывающемся меню Средства OLAP выберите пункт Вычисляемый элемент многомерного выражения (рис. 23).
На экране появится диалоговое окно (рис. 24).
Рис. 24. Окно Создание вычисляемого элемента
Выполните следующие действия:
1. Присвойте вычисляемой мере имя.
2. Выберите родительскую иерархию, для которой создаются новые вычисляемые элементы. Настройке Родительский элемент присвойте значение Все . Благодаря этой настройке Excel получает доступ ко всем элементам родительской иерархии при вычислении выражения.
3. В окне Многомерное выражение введите синтаксис многомерного выражения. Чтобы немного сэкономить время, воспользуйтесь отображенным слева списком для выбора существующих элементов, используемых в многомерном выражении. Дважды щелкните на выбранном элементе, и Excel добавит его в окно Многомерное выражение . В примере, показанном на рис. 24, вычисляется сумма первого и второго кварталов:
..&& +
.. && +
.. && + …
4. Щелкните ОК. Excel отобразит только что созданный вычисляемый элемент многомерного выражения в сводной таблице. Как показано на рис. 25, новый вычисляемый элемент отображается вместе с другими вычисляемыми элементами сводной таблицы.
На рис. 26 иллюстрируется аналогичный процесс, применяемый для создания вычисляемого элемента Second Half of Year.
Обратите внимание: Excel даже не пытается удалить исходные элементы многомерного выражения (рис. 27). В сводной таблице по-прежнему отображаются записи, соответствующие 2005–2008 годам с поквартальной разбивкой. В рассматриваемом случае это не страшно, но в большинстве сценариев следует скрывать «лишние» элементы во избежание появления конфликтов.
Рис. 27. Excel отображает созданный вычисляемый элемент многомерного выражения наравне с исходными элементами. Но все же лучше удалять исходные элементы во избежание конфликтов
Помните: вычисляемые элементы находятся только в текущей рабочей книге. Другими словами, вычисляемые меры не создаются непосредственно в кубе OLAP сервера. Это означает, что никто не сможет получить доступ к вычисляемой мере либо вычисляемому элементу, если только вы не откроете общий доступ к рабочей книге либо не опубликуете ее в Интернете.
Следует отметить, что в случае изменения родительской иерархии или родительского элемента в кубе OLAP вычисляемый элемент многомерного выражения перестает выполнять свои функции. Потребуется повторно создать этот элемент.
Управление вычислениями OLAP. В Excel поддерживается интерфейс, позволяющий управлять вычисляемыми мерами и элементами многомерных выражений в сводных таблицах OLAP. Поместите курсор в любом месте сводной таблицы и выберите контекстную вкладку Анализ из набора контекстных вкладок Работа со сводными таблицами . В раскрывающемся меню Средства OLAP выберите пункт Управление вычислениями . В окне Управления вычислениями доступны три кнопки (рис. 28):
Рис. 28. Диалоговое окне Управление вычислениями
Выполнение анализа «что, если» по данным OLAP. В Excel 2013 можно выполнять анализ «что, если» для данных, находящихся в сводных таблицах OLAP. Благодаря этой новой возможности можно изменять значения в сводной таблице и повторно вычислять меры и элементы на основании внесенных изменений. Можно также распространить изменения обратно на куб OLAP. Чтобы воспользоваться возможностями анализа «что, если», создайте сводную таблицу OLAP и выберите контекстную вкладку Анализ Работа со сводными таблицами . В раскрывающемся меню Средства OLAP выберите команду Анализ «что, если» –> Включить анализ «что, если» (рис. 29).
Начиная с этого момента можно изменять значения сводной таблицы. Чтобы изменить выбранное значение в сводной таблице, щелкните на нем правой кнопкой мыши и в контекстном меню выберите пункт (рис. 30). Excel повторно выполнит все вычисления в сводной таблице с учетом внесенных правок, включая вычисляемые меры и вычисляемые элементы многомерных выражений.
Рис. 30. Выберите пункт Учесть изменение при расчете сводной таблицы , чтобы внести изменения в сводную таблицу
По умолчанию правки, внесенные в сводную таблицу в режиме анализа «что, если», являются локальными. Если же вы хотите распространить изменения на сервер OLAP, выберите команду для публикации изменений. Выберите контекстную вкладку Анализ , находящуюся в наборе контекстных вкладок Работа со сводными таблицами . В раскрывающемся меню Средства OLAP выберите пункты Анализ «что, если» – > Опубликовать изменения (рис. 31). В результате выполнения этой команды включится «обратная запись» на сервере OLAP, что означает возможность распространения изменений на исходный куб OLAP. (Чтобы распространять изменения на сервер OLAP, нужно обладать соответствующими разрешениями на доступ к серверу. Обратитесь к администратору баз данных, который поможет вам получить разрешения на доступ в режиме записи к базе данных OLAP.)
Заметка написана на основе книги Джелен, Александер. . Глава 9.
OLAP (On-Line Analytical Processing) – это способ электронной аналитической обработки данных, представляющий организацию данных в иерархические категории с использованием предварительно рассчитанных итоговых значений. Данные OLAP упорядочены иерархически и хранятся не в таблицах, а в кубах. Кубы OLAP представляют собой многомерный набор данных с осями, на которых отложены параметры, и ячейками, содержащими зависящие от параметров агрегатные данные. Кубы предназначены для комплексного многомерного анализа больших объемов данных, поскольку они предоставляют для отчетов только сводные результаты, вместо большого числа отдельных записей.
Концепция OLAP была описана в 1993 г. известным исследователем баз данных и автором реляционной модели данных Э. Ф. Коддом. В настоящее время поддержка OLAP реализована во многих СУБД и иных инструментах.
Куб OLAP содержит два типа данных:
· итоговые значения, значения, для которых требуется подвести итог, представляющие вычисляемые поля данных ;
· описательные сведения, представляющие измерения или размерности . Описательные сведения обычно распределяются по уровням детализации. Например: «Год», «Квартал», «Месяц» и «День» в размерности «Время». Распределение полей по уровням детализации позволяет пользователям, работающим с отчетами, выбирать требуемый уровень детализации для просмотра, начиная с итоговых данных высокого уровня и затем переходя к более подробному представлению, и наоборот.
Средства Microsoft Query также позволяют создавать кубы OLAP из запроса, который загружает данные реляционной базы данных, например Microsoft Access, при этом происходит преобразование линейной таблицы в структурную иерархию (куб).
Мастер создания куба OLAP является встроенным средством Microsoft Query. Для создания куба OLAP на основе реляционной базы данных перед запуском мастера необходимо выполнить следующие действия.
1. Определить источник данных (см. рис. 6.1).
2. С помощью Microsoft Query создать запрос, включая в него только те поля, которые будут являться либо полями данных, либо полями размерностей куба OLAP, если поле в кубе используется больше одного раза, то его необходимо включить в запрос нужное число раз.
3. На последнем шаге мастера создания запросов установить переключатель на пункте Создание куба OLAP из данного запроса (см. рис. 6.2) или после того как запрос создан средствами непосредственно Query в меню Файл выбрать команду Создать куб OLAP , после чего мастер создания куба OLAP будет запущен.
Работа мастера создания куба OLAP состоит из трех шагов.
На первом шаге мастера (см. рис. 6.6) определяются поля данных –вычисляемые поля, для которых необходимо определить итоговые значения.
Рис. 6.6. Определение полей данных
Предполагаемые вычисляемые поля (обычно это числовые поля) мастер размещает в верхнюю часть списка, отмечает флажками и определяет итоговую функцию этих полей, обычно – Сумма . При выборе полей данных хотя бы одно поле должно быть выбрано в качестве вычисляемого поля и хотя бы одно поле должно остаться неотмеченным для определения размерности.
При создании OLAP куба можно использовать четыре итоговых функции – Сумма , Число (количество значений), Минимум , Максимум для числовых полей и одну функцию Число для всех остальных полей. Если нужно использовать несколько различных итоговых функций одного и того же поля, это поле должно быть включено в запрос нужное количество раз.
Имя вычисляемого поля можно изменить в столбце Имя поля данных .
На втором шаге мастера определяются описательные данные и их размерности (см. рис. 6.7). Для выбора поля измерения необходимо из списка Исходные поля перетащить нужное поле размерности верхнего уровня в список Измерения в область, помеченную как Перетащите сюда поля для создания размерности . Для создания куба OLAP необходимо определить хотя бы одну размерность. На этом же шаге мастера с помощью контекстного меню можно изменить имя поля размерности или уровня.
Рис. 6.7. Определение полей измерений
Поля, содержащие изолированные или обособленные данные и не принадлежащие к иерархии, можно определить как размерности с одним уровнем. Однако использование куба будет более эффективным, если некоторые из полей организованы в уровни. Чтобы создать уровень в составе размерности, перетащите поле из списка Исходные поля на поле, являющееся размерностью или уровнем. Поля, содержащие более подробные сведения, должны располагаться на более низких уровнях. Например, на рисунке 6.7 поле Должность является уровнем поля НазваниеОтдела .
Чтобы переместить поле на более низкий или более высокий уровень, нужно перетащить его на более низкое или более высокое поле в составе размерности. Для отображения или скрытия уровней используются соответственно кнопки или .
Если в качестве размерности верхнего уровня используются поля даты или времени, мастер создания куба OLAP автоматически создает уровни для этих размерностей. Пользователь может затем выбрать, какие уровни должны присутствовать в отчетах. Например, можно выбрать недели, кварталы и годы, или же месяцы (см. рис. 6.7).
Следует помнить, что мастер создает автоматически уровни для полей даты и времени только при создании размерности верхнего уровня; при добавлении этих полей в качестве подуровней имеющей размерности, автоматические уровни не создаются.
На третьем шаге мастера определяется типа куба, создаваемого мастером, при этом возможны три варианта (см. рис. 6.8).
Рис. 6.8. Выбор типа создаваемого куба на третьем шаге мастера
· Первые два варианта предполагают создание куба при каждом открытии отчета (если куб просматривается из Excel, то речь идет о сводной таблице). В этом случае сохраняется файл запроса и файл определения куба *.oqy , содержащий инструкции по созданию куба. Файл *.oqy может быть открыт в программе Excel для создания отчетов на основе куба, а при необходимости внесения изменений в куб, можно его открыть Query для повторного запуска мастера создания куба.
По умолчанию файлы определения куба так же, как и файлы запросов, хранятся в папке профиля пользователя в Application Data\Microsoft\Que-ries. При сохранении файла *.oqy в стандартной папке, имя файла определения куба выводится на вкладке Кубы OLAP при открытии нового запроса в Microsoft Query или при выборе команды Создать запрос (меню Данные , подменю Импорт внешних данных ) в Microsoft Excel.
· В случае выбора третьего варианта типа куба Сохранение файла куба, содержащего все данные для куба , извлекаются все данные для куба и в указанном пользователем месте создается файл куба с расширением *.cub , в котором эти данные хранятся. Создание данного файла не происходит немедленно при нажатии кнопки Готово ; файл создается либо при сохранении определения куба в файле, либо при создании отчета на основе куба.
Выбор типа куба определяется несколькими факторами: объемом данных, которые содержит куб; типом и сложностью отчетов, которые будут создаваться на основе куба; ресурсами системы (память и дисковое пространство) и т. п.
Отдельный файл куба *.cub следует создавать в следующих случаях:
1) для часто изменяемых интерактивных отчетов при наличии достаточного дискового пространства;
2) когда требуется сохранить куб на сетевом сервере для обеспечения доступа к нему других пользователей при создании отчетов. Файл куба может предоставить определенные данные из исходной базы данных, опуская при этом секретные или конфиденциальные данные, доступ к которым для других пользователей требуется закрыть.
