Кубы olap
Содержание
- О чем эта статья
- Применимость
- Работа с OLAP-системой Microsoft SQL Server Analysis Services при помощи внешних источников данных в «1С:Предприятии 8.3.5»
- PDF-версия статьи для участников группы ВКонтакте
- Введение
- OLAP на клиенте и на сервере
- Технические аспекты многомерного хранения данных
- Основные понятия OLAP
- Средства чтения OLAP-данных в Microsoft Office
- Манипуляция OLAP-данными в Microsoft Excel
- Создание сводной таблицы с данными OLAP-кубов
- Манипуляция отображением данных в сводной таблице
- Создание сводных диаграмм с данными OLAP-кубов
- Создание локальных OLAP-кубов
- Заключение
О чем эта статья
В статье рассмотрена технология OLAP в части использовании ее как внешний источник данных для платформы «1С:Предприятие» редакции 8.3.5. Прочитав статью вы узнаете:
- Что такое технология OLAP и какие средства есть в платформе для работе с ней?
- Как опубликовать куб OLAP SQL Server с помощью Internet Information Service (IIS) и обращаться к нему из Excel?
- Как обратиться к кубу OLAP из системы «1С:Предприятие»?
Применимость
В статье используется Microsoft SQL Server 2008 R2, работающий под управлением Windows Server 2008 R2 и платформа «1С:Предприятие» редакции 8.3.5. Материал актуален и для текущих релизов платформы.
Работа с OLAP-системой Microsoft SQL Server Analysis Services при помощи внешних источников данных в «1С:Предприятии 8.3.5»
В предыдущей статье ( Запись во внешние источники данных в «1С:Предприятие 8» ) мы познакомились с функционалом записи во внешние источники данных при помощи платформы 8.3.5.823.
Сегодня мы остановимся на еще одной очень интересной возможности работы с внешними источниками данных – взаимодействие с OLAP.
OLAP (от англ. online analytical processing – аналитическая обработка в реальном времени) – технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу.
Данные в OLAP-системах формируются на основании данных OLTP-систем. OLAP-системы предназначены для быстрой выборки сложных многомерных данных, которые в OLTP-системах из-за сложной табличной структуры базы данных будут выполняться медленно.
Для обеспечения скорости получения данных OLAP-системы используют специальную структуру хранения данных, называемую кубом.
Куб (cube) можно представить в виде пространства, оси которого представляют собой измерения (dimensions), а в узлах этого пространства располагаются некоторые меры (measures). Каждое измерение куба характеризуется определенными членами (members) измерения.
Можно провести аналогию между OLAP-кубом и регистром накопления. Измерение регистра схоже с измерением куба, значения измерения регистра соответствует членам измерения куба, а ресурс регистра представляет меру куба.
Рассмотрим, как устроен куб OLAP-системы в «1С:Предприятии». Куб состоит из таблиц измерений, измерений и ресурсов.
Таблицы измерений описывают набор членов измерений куба. Измерения объекта метаданных соответствуют измерениям куба в OLAP-системе.
Меры куба в платформе реализованы ресурсами, которые могут принимать значения типа Число и Строка.
Для работы с многомерными внешними источниками данных используется механизм XMLA (XML for Analysis). Платформа получает доступ к данным с помощью HTTP-запросов к веб-серверу.
Рассмотрим пример. Подключимся из информационной базы «1С:Предприятие» к Microsoft Analysis Services. Все действия выполняются на СУБД Microsoft SQL Server 2008 R2 под управлением операционной системы Windows Server 2008 R2.
Для начала убедимся, что служба Microsoft SQL Server Analysis Services запущена. Проверяем это в Диспетчере конфигурации SQL Server:
Для экспериментов загрузим тестовую базу данных AdventureWorks и подготовленный куб с сервера http://msftdbprodsamples.codeplex.com/releases/view/59211.
Присоединим загруженные базы формата MDF при помощи SQL Management Studio:
Далее подключившись к серверу Analysis Services мы восстанавливаем базу данных из файла Adventure Works DW 2008R2.abf:
После окончания загрузки проверяем работоспособность куба Adventure Works. Щелкаем по нему правой кнопкой мыши и выбираем Обзор.
В открывшемся конструкторе мышью перетаскиваем поля в строки и столбцы сводной таблицы. Данные выбираются из базы, следовательно, загруженные базы функционируют.
Analysis Services для работы по HTTP требуется IIS. Значит, следующей нашей задачей будет развертывание веб-сервера IIS. При помощи Диспетчера сервера устанавливаем роль Веб-сервер (IIS) со следующими службами ролей:
После установки служба веб-сервера может останавливаться с ошибкой:
Каталог, указанный для временных файлов конфигурации пула приложений, или отсутствует, или недоступен для службы активации Windows. Укажите существующий каталог и убедитесь, что флаги доступа установлены должным образом. Поле данных содержит номер ошибки.
После создания папки вручную и установки необходимых прав служба веб-сервера запускается стабильно.
Дальнейшие настройки выполняем при помощи Диспетчера служб IIS. Выбираем пункт Ограничения ISAPI и CGI.
Добавляем новое ограничение, указываем путь к файлу в нашем новом каталоге C:\inetpub\OLAP_HTTP\msmdpump.dll.
Добавляем новый пул приложений, которому присваиваем имя MSOLAP:
В диспетчере служб IIS в дереве разворачиваем пункт Сайты, в строке Default Web Site при помощи правой кнопки мыши добавляем приложение с именем MSOLAP:
Для появившегося приложения заходим в пункт Сопоставление обработчиков и выбираем Добавить сопоставление сценария:
Далее необходимо настроить аутентификацию. В разделе Проверка подлинности включаем настройку Анонимная проверка подлинности, все остальные способы аутентификации отключаем.
Попробуем подключиться к службам аналитики SQL Server из Excel:
В строке соединения указываем http://localhost/msolap/msmdpump.dll. Имя пользователя и пароль оставляем пустыми, поскольку была настроена анонимная проверка подлинности.
Подключение происходит успешно, выбираем куб Adventure Works для подключения:
При нажатии кнопки Далее мастер предложит сохранить файл подключения. Соглашаемся, этот файл нам еще понадобится для соединения с кубами из «1С:Предприятия».
А в Excel можно построить сводную таблицу по данным выбранного куба.
Если открыть сохраненный файл подключения в Блокноте, то можно увидеть атрибут ConnectionString:
В конфигураторе добавляем новый внешний источник данных, в него добавляем новый куб.
Заполняем строку подключения на основании указанного выше фрагмента файла подключения:
После успешного подключения будет открыт список кубов с таблицами измерений, полями и ресурсами. Отмечаем необходимые объекты:
Полученный в конфигурации куб можно использовать как источник данных для запроса, в системе компоновки данных, как источник для динамических списков.
Напишем отчет на СКД, базирующийся на запросе к загруженному кубу:
ВЫБРАТЬ
Adventure_Works.Product_Category_Category,
Adventure_Works.Customer_Customer_Customer,
Adventure_Works.Internet_Sales_Amount
ИЗ
ВнешнийИсточникДанных.ВнешнийИсточникДанных1.Куб.Adventure_Works КАК Adventure_Works
Определим один ресурс – Internet_Sales_Amount.
При выполнении отчета в пользовательском режиме получим следующий результат:
Сравним полученные итоги с аналогичным, сформированным в SQL Server Analysis Services:
Также формируем сводную таблицу в Excel:
Как видим, результаты получились одинаковыми.
PDF-версия статьи для участников группы ВКонтакте
Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.
Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.
Если Вы уже участник группы – нужно просто повторно авторизоваться в ВКонтакте, чтобы скрипт Вас узнал. В случае проблем решение стандартное: очистить кеш браузера или подписаться через другой браузер.
Введение
OLAP — это не отдельно взятый программный продукт, не язык программирования и даже не конкретная технология. Если постараться охватить OLAP во всех его проявлениях, то это совокупность концепций, принципов и требований, лежащих в основе программных продуктов, облегчающих аналитикам доступ к данным.
Для начала мы выясним, зачем аналитикам надо как-то специально облегчать
доступ к данным. Дело в том, что аналитики — это особые потребители
корпоративной информации. Задача аналитика — находить закономерности в
больших массивах данных. Поэтому аналитик не будет обращать внимания на отдельно взятый факт, что в четверг четвертого числа контрагенту Чернову была продана партия черных чернил — ему нужна информация о сотнях и тысячах подобных событий. Одиночные факты в базе данных могут заинтересовать, к примеру, бухгалтера или начальника отдела продаж, в компетенции которого находится сделка. Аналитику одной записи мало — ему, к примеру, могут понадобиться все сделки данного филиала или представительства за месяц, год. Заодно аналитик отбрасывает ненужные ему подробности вроде ИНН покупателя, его точного адреса и номера телефона, индекса контракта и тому подобного. В то же время данные, которые требуются аналитику для работы, обязательно содержат числовые значения — это обусловлено самой сущностью его деятельности.
Централизация и удобное структурирование — это далеко не все, что нужно
аналитику. Ему ведь еще требуется инструмент для просмотра, визуализации
информации. Традиционные отчеты, даже построенные на основе единого
хранилища, лишены одного — гибкости. Их нельзя «покрутить”, «развернуть” или «свернуть”, чтобы получить желаемое представление данных. Конечно, можно вызвать программиста, и он сделает новый отчет достаточно быстро — скажем, в течение часа. Получается, что аналитик может проверить за день не более двух идей. А ему (если он хороший аналитик) таких идей может приходить в голову по нескольку в час. И чем больше «срезов” и «разрезов” данных аналитик видит, тем больше у него идей, которые, в свою очередь, для проверки требуют все новых и новых «срезов”.В качестве такого инструмента и выступает OLAP.
Хотя OLAP и не представляет собой необходимый атрибут хранилища данных, он все чаще и чаще применяется для анализа накопленных в этом хранилище сведений.
Чтотакое OLAP- технологии
OLAP (On-Line Analytical Processing) –
-
это класс приложений и технологий, предназначенных для оперативной аналитической обработки многомерных данных (сбор, хранение, анализ) для анализа деятельности корпорации и прогнозирования будущего состояния с целью поддержки принятия управленческих решений.
-
набор технологий для оперативной обработки информации, включающих динамическое построение отчётов в различных разрезах, анализ данных, мониторинг и прогнозирование ключевых показателей бизнеса.
Технология OLAP применяется, чтобы упростить работу с многоцелевыми накопленными данными о деятельности корпорации в прошлом и не погрязнуть в их большом объеме, а также превратить набор количественных показателей в качественные, позволяет аналитикам, менеджерам и управляющим сформировать свое собственное видение данных, используя быстрый, единообразный, оперативный доступ к разнообразным формам представления информации. Такие формы, полученные на основании первичных данных, позволяют пользователю сформировать полноценное представление о деятельности предприятия.
OLAP-технология является альтернативой традиционным методам анализа данных, основанным на различных системах реализации SQL-запросов к реляционной БД. OLAP-системы играют важнейшую роль в анализе и планировании деятельности крупных предприятий и являются одним из направлений развития ИТ. В основу кладутся требования людей принимающих решения к предоставляемой информации, сложившейся индивидуальные особенности ведения дел и принятый механизм принятия решения. С точки зрения пользователя основное отличие OLAP-системы заключается: в предметной структурированности информации (именно предметной, а не технической). Работая с OLAP-приложением, пользователь применяет привычные категории и показатели – виды материалов и готовой продукции, регионы продаж, объем реализации, себестоимость, прибыль и т. п. А для того чтобы сформировать любой, даже довольно сложный запрос, пользователю не придется изучать SQL. При этом ответ на запрос будет получен в течение всего нескольких секунд. Кроме того, работая с OLAP-системой, экономист может пользоваться такими привычными для себя инструментами, как электронные таблицы или специальные средства построения отчетов.
Есть характеристики, которые должны соблюдаться во всех OLAP-продуктах, в которых и заключается идеал технологии. Это 5 ключевых определений, так называемый, тестFASMI:
Быстрый — означает, что система должна обеспечивать выдачу большинства ответов пользователям в пределах приблизительно пяти секунд. Даже если система предупредит, что процесс будет длиться существенно дольше, пользователи, могут отвлечься и потерять мысль, при этом качество анализа страдает. Такую скорость не просто достигнуть с большими количествами данных, особенно, если требуются специальные вычисления «на лету». Поставщики прибегают к широкому разнообразию методов, чтобы достигнуть этой цели, включая специализированные формы хранения данных, обширные предварительные вычисления, или же ужесточая аппаратные требования. Однако полностью оптимизированных решений на сегодняшний день нет. На первый взгляд может казаться удивительным, что при получении отчета за минуту, на который не так давно требовались дни, пользователь очень быстро начинает скучать во время ожиданий, и проект оказывается намного менее успешным, чем в случае мгновенного ответа, даже ценой менее детального анализа.
Разделяемой означает, что система дает возможность выполнять все требования защиты данных и реализовывать распределенный и одновременный доступ к данным для различных уровней пользователей. Система должна быть способна обработать множественные изменения данных своевременным, безопасным способом. Это — главная слабость многих OLAP продуктов, которые имеют тенденцию предполагать, что во всех приложениях OLAP требуется только чтение, и предоставляют упрощенные средства защиты.
Многомерной — ключевое требование. Если бы необходимо было определить OLAP одним словом, то выбрали бы его. Система должна обеспечить многомерное концептуальное представление данных, включая полную поддержку для иерархий и множественных иерархий, поскольку это определяет наиболее логичный способ анализировать бизнес. Минимальное число измерений, которые должны быть обработаны, не устанавливается, поскольку это также зависит от приложения, и большинство продуктов OLAP, имеет достаточное количество измерений для тех рынков, на которые они нацелены. И опять же, мы не определяем, какая основная технология базы данных должна использоваться, если пользователь получает действительно многомерное концептуальное представление информации. Эта особенность — сердцевина OLAP.
Информации. Необходимая информация должна быть получена там, где она необходима, независимо от ее объема и места хранения. Однако многое зависит от приложения. Мощность различных продуктов измеряется в терминах того, сколько входных данных они могут обрабатывать, но не сколько гигабайт они могут хранить. Мощность продуктов весьма различна — самые большие OLAP продукты могут оперировать, по крайней мере, в тысячу раз большим количеством данных по сравнению с самыми маленькими. По этому поводу следует учитывать много факторов, включая дублирование данных, требуемую оперативную память, использование дискового пространства, эксплуатационные показатели, интеграцию с информационными хранилищами и т. п.
Анализ означает, что система может справляться с любым логическим и статистическим анализом, характерным для данного приложения, и обеспечивает его сохранение в виде, доступном для конечного пользователя. Пользователь должен иметь возможность задавать новые специальные вычисления как часть анализа без необходимости программирования. То есть все требуемые функциональные возможности анализа должны обеспечиваться интуитивным способом для конечных пользователей. Средства анализа могли бы включать определенные процедуры, типа анализа временных рядов, распределения затрат, валютных переводов, поиска целей и др. Такие возможности широко отличаются среди продуктов, в зависимости от целевой ориентации.
Важно отметить, что OLAP один из способов реализации Business Intelligence, который является процессом превращения данных в знания, а знаний в действия бизнеса для получения выгоды. Является деятельностью конечного пользователя, которую облегчают различные аналитические и групповые инструменты и приложения, а также инфраструктура хранилища данных.
Способ представления
В основе OLAP-технологий лежит представление информации в виде OLAP-кубов.
OLAP-кубы содержат бизнес-показатели, используемые для анализа и принятия управленческих решений, например: прибыль, рентабельность продукции, совокупные средства (активы), собственные средства, заемные средства и т.д.
Бизнес-показатели хранятся в кубах не в виде простых таблиц, как в обычных системах учета или бухгалтерских программах, а в разрезах, представляющих собой основные бизнес-категории деятельности организации: товары, магазины, клиенты, время продаж и т. д.
Благодаря детальному структурированию информации OLAP-кубы позволяют оперативно осуществлять анализ данных и формировать отчёты в различных разрезах и с произвольной глубиной детализации. Отчёты могут создаваться аналитиками, менеджерами, финансистами, руководителями подразделений в интерактивном режиме для того, чтобы быстро получить ответы, на возникающие ежедневно вопросы, и принять правильное решение. При этом сотрудникам, для создания отчетов не нужно прибегать к услугам программистов, на что обычно уходит немало времени.
Из OLAP-куба может быть составлен обычный плоский отчёт. По столбикам и строчкам отчёта будут бизнес-категории (грани куба), а в ячейках показатели.
Этапы построения OLAP-системы
1. Хранилище данных
Хранилище данных является основой OLAP-системы. Процедуры загрузки с определённой периодичностью пополняют хранилище из различных источников (базы данных, документы Excel, Web и т.д.), выполняя проверку и предварительную обработку. Механизмы загрузки данных проектируются таким образом, чтобы хранилище содержало в хронологическом порядке в едином формате всю необходимую информацию о предметной области для поддержки принятия решений.
2. Многомерные OLAP-кубы
На основе хранилища данных строятся многомерные OLAP-кубы, позволяющие в реальном времени осуществлять анализ данных и формировать отчёты в различных разрезах и произвольной глубиной детализации.
При работе с OLAP-кубами пользователь оперирует привычными категориями и показателями: виды готовой продукции, материалы, регионы, время продаж, объём реализации, себестоимость, прибыль и т. п. Отчёты создаются сотрудниками в интерактивном режиме (нет необходимости прибегать к помощи программистов).
3. Система ключевых показателей (KPI) — показатели деятельности подразделения (предприятия), которые помогают организации в достижении стратегических и тактических (операционных) целей, создаётся на базе OLAP-кубов для мониторинга и оценки бизнес-процессов, а также для оповещения ответственных сотрудников о фактах отклонения.
4. Интеллектуальный анализ данных (Data Mining)
Модели интеллектуального анализа данных проектируются для автоматического прогнозирования наиболее важных показателей бизнеса, а также извлечения скрытых закономерностей из накопленной информации.
5. Доступ через web-интерфейс
Для работы с OLAP-системой могут быть использованы различные приложения, например Excel. Чаще всего доступ осуществляется через специализированный web-портал, позволяющий работать с OLAP-кубами и отчётами, обладающий административным интерфейсом и возможностью разграничения прав доступа к данным и инструментам.
Преимущества OLAP-систем
Ключевую роль в управлении компанией играет информация. Как правило, даже небольшие компании используют несколько информационных систем для автоматизации различных сфер деятельности. Получение аналитической отчётности в информационных системах, основанных на традиционных базах данных сопряжено с рядом ограничений:
-
Разработка каждого отчёта требует работы программиста;
-
Отчёты формируются очень медленно (зачастую несколько часов), замедляя при этом работу всей информационной системы;
-
Данные, получаемые от различных структурных элементов компании не унифицированы и часто противоречивы.
OLAP-системы, самой идеологией своего построения предназначены для анализа больших объёмов информации, позволяют преодолеть ограничения традиционных информационных систем. Создание OLAP-системы на предприятии позволит:
-
Интегрировать данные различных информационных систем, создав единую версию правды;
-
Проектировать новые отчеты несколькими щелчками мыши без участия программистов;
-
В реальном времени анализировать данные по любым категориям и показателям бизнеса на любом уровне детализации;
-
Производить мониторинг и прогнозирование ключевых показателей бизнеса.
Недостатки OLAP
Как и любая технология OLAP также имеет свои недостатки: высокие требования к аппаратному обеспечению, подготовке и знаниям административного персонала и конечных пользователей, высокие затраты на реализацию проекта внедрения (как денежные, так и временные, интеллектуальные).
Заключение
Эффект от правильной организации, стратегического и оперативного планирования развития бизнеса трудно заранее оценить в цифрах, но очевидно, что он в десятки и даже сотни раз может превзойти затраты на реализацию таких систем. Однако не следует и заблуждаться. Эффект обеспечивает не сама система, а люди с ней работающие. Поэтому не совсем корректны декларации типа: «система Хранилищ Данных и OLAP-технологий будет помогать менеджеру принимать правильные решения». Современные аналитические системы не являются системами искусственного интеллекта и они не могут ни помочь, ни помешать в принятии решения. Их цель своевременно обеспечить менеджера всей информацией необходимой для принятия решения в удобном виде. А какая информация будет запрошена и какое решение будет принято на её основе, зависит только от конкретного человека ее использующего
Список источников Применение OLAP-технологий для учетных систем на платформе 1С. — специалисты компании «Абис Софт»
Цель лекции
Изучив материал настоящей лекции, вы будете знать:
- что такое куб данных в 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. Пример куба данных
«Разрезание» куба
Даже трехмерный куб сложно отобразить на экране компьютера так, чтобы были видны значения интересующих мер. Что уж говорить о кубах с количеством измерений, большим трех. Для визуализации данных, хранящихся в кубе, применяются, как правило, привычные двумерные, т. е. табличные представления, имеющие сложные иерархические заголовки строк и столбцов.
Двумерное представление куба можно получить, «разрезав» его поперек вдоль одной или нескольких осей ( измерений ): мы фиксируем значения всех измерений, кроме двух, — и получаем обычную двумерную таблицу. В горизонтальной оси таблицы (заголовки столбцов) представлено одно измерение, в вертикальной (заголовки строк) — другое, а в ячейках таблицы — значения мер. При этом набор мер фактически рассматривается как одно из измерений: мы либо выбираем для показа одну меру (и тогда можем разместить в заголовках строк и столбцов два измерения ), либо показываем несколько мер (и тогда одну из осей таблицы займут названия мер, а другую — значения единственного «неразрезанного» измерения ).
Взгляните на рис. 26.2. Здесь изображен двумерный срез куба для одной меры — Unit Sales (продано штук) и двух «неразрезанных» измерений — Store (Магазин) и Время (Time).
Рис. 26.2. Двумерный срез куба для одной меры
На рис. 26.3 представлено лишь одно «неразрезанное» измерение — Store, но зато отображаются значения нескольких мер — Unit Sales (продано штук), Store Sales (сумма продажи) и Store Cost (расходы магазина).
Рис. 26.3. Двумерный срез куба для нескольких мер
Двумерное представление куба возможно и тогда, когда «неразрезанными» остаются и более двух измерений. При этом на осях среза (строках и столбцах) будут размещены два или более измерений «разрезаемого» куба — см. рис. 26.4.
Рис. 26.4. Двумерный срез куба с несколькими измерениями на одной оси
Метки
Значения, «откладываемые» вдоль измерений, называются членами или метками (members). Метки используются как для «разрезания» куба, так и для ограничения (фильтрации) выбираемых данных — когда в измерении, остающемся «неразрезанным», нас интересуют не все значения, а их подмножество, например, три города из нескольких десятков. Значения меток отображаются в двумерном представлении куба как заголовки строк и столбцов.
Иерархии и уровни
Метки могут объединяться в иерархии, состоящие из одного или нескольких уровней (levels). Например, метки измерения «Магазин» (Store) естественно объединяются в иерархию с уровнями:
All (Мир)
Country (Страна)
State (Штат)
City (Город)
Store (Магазин).
В соответствии с уровнями иерархии вычисляются агрегатные значения, например, объем продаж для США (уровень «Country») или для штата Калифорния (уровень «State»). В одном измерении можно реализовать более одной иерархии — скажем, для времени: {Год, Квартал, Месяц, День} и {Год, Неделя, День}.
Отметим, что иерархии могут быть сбалансированными (balanced), как, например, иерархия, представленная на рис. 26.5, а также иерархии, основанные на данных типа «дата-время», и несбалансированными (unbalanced). Типичный пример несбалансированной иерархии — иерархия типа «начальник-подчиненный» (ее можно построить, например, используя значения поля Salesperson исходного набора данных из рассмотренного выше примера), как показано на рис. 26.6.
Рис. 26.5. Иерархия в измерении, связанном с географическим положением клиентов
Рис. 26.6. Несбалансированная иерархия
Иногда для таких иерархий используется термин Parent-child hierarchy.
Существуют также иерархии, занимающие промежуточное положение между сбалансированными и несбалансированными (они обозначаются термином ragged — «неровный»). Обычно они содержат такие члены, логические «родители» которых находятся непосредственно на вышестоящем уровне (например, в географической иерархии есть уровни Country, City и State, но при этом в наборе данных имеются страны, не имеющие штатов или регионов между уровнями Country и City ( рис. 26.7)).
Рис. 26.7. «Неровная» иерархия
Отметим, что несбалансированные и «неровные» иерархии поддерживаются далеко не всеми 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.
Алексей Федоров,
Наталия Елманова, преподаватель УКЦ «Interface Ltd»,
КомпьютерПресс 9’2001
Средства чтения OLAP-данных в Microsoft Office
Манипуляция OLAP-данными в Microsoft Excel
Создание сводной таблицы с данными OLAP-кубов
Манипуляция отображением данных в сводной таблице
Создание сводных диаграмм с данными OLAP-кубов
Создание локальных OLAP-кубов
Заключение
В предыдущей части данной статьи (Введение в OLAP: часть 5. Создание многомерных баз данных) мы рассмотрели процесс создания многомерных баз данных для Microsoft Analysis Services и содержащихся в них объектов, а также ознакомились с простейшим средством просмотра сечений кубов, встроенным в Analysis Manager. Этот способ работы с OLAP-данными — не единственный (и далеко не самый удобный, по крайней мере для конечного пользователя) из возможных на сегодняшний день. Помимо него существует немало других средств просмотра этих данных — от приложений Microsoft Office и входящих в его состав компонентов до многочисленных средств просмотра OLAP-данных, предлагаемых сторонними производителями. Разработчики могут создавать собственные приложения для работы с OLAP-данными — как с применением компонентов Microsoft Office, так и без них.
Мы начнем с рассмотрения одного из самых простейших способов работы с OLAP-данными — использования Microsoft Excel. Остальные способы работы с OLAP-данными будут рассмотрены в следующих номерах журнала.
Средства чтения OLAP-данных в Microsoft Office
Прежде чем обсуждать возможности Microsoft Excel как OLAP-клиента, кратко остановимся на компонентах Microsoft Office, используемых для работы с OLAP-данными, — это позволит нам в дальнейшем избежать терминологической путаницы. Тем более что все эти компоненты содержат в своем названии словосочетание PivotTable.
Первым из компонентов Microsoft Office, предназначенных для создания OLAP-клиентов, является набор библиотек PivotTable Service. С одной стороны, он является составной частью Analysis Services и выполняет роль связующего звена между Analysis Services и их клиентами (не обязательно имеющими отношение к Microsoft Office). PivotTable Service может быть установлен отдельно на компьютер, на котором эксплуатируются какие-либо клиенты Analysis Services; для его установки в состав Analysis Services входит отдельный дистрибутив. С другой стороны, PivotTable Service входит и в состав Microsoft Office 2000/XP и при этом может быть использован не только для работы с данными Analysis Services, но и для создания и чтения локальных OLAP-кубов, не имеющих отношения к Analysis Services, как с помощью Microsoft Excel, так и без него.
Вторым компонентом, который может быть использован для просмотра OLAP-кубов, является служба, называемая PivotTable Reports, — средство создания сводных таблиц Microsoft Excel. Это средство позволяет получать, сохранять в кэше в оперативной памяти и отображать на листах рабочих книг двухмерные и трехмерные наборы агрегатных данных на основе данных из реляционных СУБД и рабочих книг Excel. PivotTable Reports входит в Excel начиная с версии 5.0, но возможность считывать с помощью него данные из OLAP-кубов Analysis Services, равно как и создавать локальные OLAP-кубы, впервые появилась в Excel 2000. Отметим, что средство создания сводных таблиц Excel использует библиотеки PivotTable Services.
И наконец, третьим компонентом, применяемым при создании OLAP-клиентов, является PivotTable List — элемент управления ActiveX, входящий в состав Microsoft Office Web Components и предназначенный для просмотра сечений OLAP-кубов. Применяется он главным образом на Web-страницах, а иногда и в обычных Windows-приложениях (о применении его в Delphi-приложениях см. приложение CD-ROM к КомпьютерПресс № 12’2000).
Выяснив, что представляют собой средства чтения OLAP-кубов Microsoft Office, мы можем перейти к более детальному рассмотрению процесса чтения и отображения OLAP-кубов с помощью Microsoft Excel.
Манипуляция OLAP-данными в Microsoft Excel
Как было отмечено выше, средства создания сводных таблиц Microsoft Excel хранят в кэше агрегатные данные, вычисленные на основе данных из реляционных СУБД или полученные от OLAP-серверов. Манипулируя сводной таблицей, пользователь может управлять отображением данных из этого кэша.
Прежде чем приступить к созданию примера, заметим, что посредством Microsoft Excel 2000 можно корректно отображать данные из OLAP-кубов, созданных с помощью Microsoft SQL Server 7.0 OLAP Services. Что касается OLAP-кубов, созданных с помощью Microsoft SQL Server 2000 Analysis Services, по большей части посредством Microsoft Excel 2000, то они также отображаются корректно, однако имеются и некоторые ограничения. Например, при создании локальных кубов OLAP или при сохранении сводной таблицы в виде Web-страницы с помощью соответствующих мастеров автоматически выбирается OLE DB-провайдер предыдущей версии (версии 7.0), не поддерживающий несбалансированные измерения. Это приводит к сообщениям об ошибках и к игнорированию таких измерений или даже всего источника данных.
При использовании же Microsoft Excel 2002 эти проблемы не возникают.
Создание сводной таблицы с данными OLAP-кубов
В качестве примера создадим сводную таблицу, содержащую данные OLAP-куба, созданного ранее (см. часть 5 этой статьи в КомпьютерПресс № 8’2001). Для этого запустим Microsoft Excel и из меню Data выберем PivotTable and PivotChart Report. После этого управление будет передано мастеру PivotTable and PivotChart Wizard. В первой диалоговой панели этого мастера укажем, что для построения сводной таблицы выбирается внешний источник данных, для чего выберем опцию External data source. Затем укажем, что это за источник, нажав кнопку Get Data в следующей диалоговой панели, что приведет к запуску приложения Microsoft Query. Далее выберем закладку OLAP Cubes и, если в операционной системе еще нет описания соответствующего источника данных, создадим его (рис. 1).
Рис. 1. Описание источника данных
В процессе создания источника данных укажем его имя, выберем OLE DB-провайдер (в нашем случае — Microsoft OLE DB Provider for OLAP Services 8.0, поскольку мы используем Microsoft SQL Server 2000 Analysis Services) и нажмем на кнопку Connect (рис. 2).
Рис. 2. Выбор провайдера данных
В диалоговой панели Multidimensional Connection укажем имя компьютера (если это локальный компьютер, можно использовать имя localhost), на котором расположен OLAP-сервер, а также данные для аутентификации пользователя, которые понадобятся только в том случае, если для связи с OLAP-сервером мы используем HTTP-протокол (рис. 3).
Рис. 3. Выбор OLAP-сервера
И наконец, выберем имя многомерной базы данных, в которой хранится OLAP-куб (рис. 4).
Рис. 4. Выбор многомерной базы данных
Определив источник данных, выберем куб, который мы будем отображать в сводной таблице (рис. 5).
Рис. 5. Выбор куба для отображения в сводной таблице
После этого можно нажать кнопку OK. В результате мы получим пустую сводную таблицу, вид которой в Excel 2000 показан на рис. 6.
Рис. 6. Сводная таблица в Excel 2000
Для дальнейших манипуляций нам потребуется панель инструментов PivotTable. В случае с Excel 2000 пользоваться ею удобнее, если она не закреплена у края окна Excel, а свободно перемещается по экрану, в противном случае некоторые нужные нам элементы этой панели окажутся недоступны.
Следует отметить, что, когда фокус ввода находится на самой сводной таблице (для чего достаточно щелкнуть по ней мышью), панель PivotTable в Excel 2000 содержит кнопки с названиями измерений и мер куба. Отметим, что они обозначаются пиктограммами разного вида и, если их названия не умещаются на кнопке, их можно увидеть на всплывающих подсказках.
При смещении фокуса ввода в другое место листа эти кнопки исчезают.
В Excel 2002 диалоговая панель PivotTable выглядит иначе — она не содержит кнопок с именами измерений и мер. Их список предоставляется в отдельной панели PivotTable Field List (рис. 7).
Рис. 7. Сводная таблица в Excel 2002
Теперь нам необходимо определить, какие из мер мы хотим отобразить в сводной таблице. Для этого достаточно перенести мышью кнопку (в случае Excel 2002 — соответствующий элемент из списка) с наименованием нужной меры в область данных (Data Area; на рис. 7 она обозначена надписью Drop Data Items Here). Результат этой манипуляции представлен на рис. 8.
Рис. 8. Выбор меры для отображения в сводной таблице
Теперь требуется определить, какие из полей будут участвовать в формировании строк, столбцов и страниц (иногда последние называются фильтрами). В общем случае сводная таблица является трехмерной, и можно считать, что третье измерение расположено перпендикулярно экрану, а мы наблюдаем сечения, параллельные плоскости экрана и определяемые тем, какая «страница» выбрана для отображения. Осуществить фильтрацию можно путем перетаскивания мышью соответствующих кнопок с панели инструментов PivotTable (в случае Excel 2002 — соответствующих элементов с панели PivotTable Field List) на области строк, столбцов и страниц сводной таблицы — Row Area, Column Area и Page Area. Результат этой манипуляции показан на рис. 9.
Рис. 9. Готовая сводная таблица
Итак, мы отобразили в сводной таблице Excel содержимое OLAP-куба. Теперь этим отображением можно манипулировать.
Манипуляция отображением данных в сводной таблице
Если нас интересуют более подробные данные, связанные с одним из членов одного из отображаемых измерений, можно дважды щелкнуть по ячейке с этим значением и отобразить члены следующего уровня данного измерения (эта операция называется drill-down). То, что получится, если дважды щелкнуть на ячейке A5, показано на рис. 10.
Рис. 10. Результат операции drill-down
Если же нас интересуют более подробные данные, нежели представленные в данный момент в сводной таблице, следует выбрать ячейку с именем соответствующего измерения (например, ячейку A4) и нажать на панели инструментов PivotTable кнопку Show Detail (рис. 11).
Рис. 11. Отображение следующего уровня иерархии измерения
При необходимости можно вручную определить, какие члены измерения должны быть отображены в сводной таблице; для этого можно нажать кнопку вывода соответствующего выпадающего списка в правой части ячейки с именем измерения (рис. 12).
Рис. 12. Выбор отображаемых членов измерения
Если в сводной таблице отображается несколько мер, они формируют отдельное дополнительное измерение Data. По умолчанию оно располагается на оси строк, но может быть перенесено и на ось столбцов (рис. 13).
Рис. 13. Отображение нескольких мер в сводной таблице
Если в сводной таблице оставить только одну меру, перенеся оставшиеся обратно на панель инструментов PivotTables, измерение Data исчезнет.
Отметим, что с помощью одного из доступных в Excel шаблонов оформления можно изменить оформление сводной таблицы. Кроме того, можно выбрать на панели инструментов PivotTables пункты меню PivotTable | Table Options или PivotTable | Field Settings и изменить другие параметры отображения данных в сводной таблице.
Применяя Excel в качестве OLAP-клиента, следует помнить, что объем данных, отображаемых в сводной таблице, ограничен — ведь все эти данные хранятся в оперативной памяти клиентского компьютера.
Создание сводных диаграмм с данными OLAP-кубов
При необходимости в Excel можно построить сводную диаграмму, синхронизированную со сводной таблицей. Для этого достаточно нажать соответствующую кнопку на панели инструментов PivotTables и, если нужно, отредактировать внешний вид диаграммы (рис. 14).
Рис. 14. Сводная диаграмма с данными OLAP-куба
Отметим, что с помощью панелей инструментов PivotTable и PivotTable FieldList, а также выпадающих списков на осях и легенде можно управлять отображением данных на сводной диаграмме, например выполнять операцию drill-down; при этом сводная таблица будет меняться синхронно с диаграммой.
Создание локальных OLAP-кубов
Как уже было отмечено выше, Microsoft Excel позволяет создавать локальные OLAP-кубы, представляющие собой подмножества данных серверных OLAP-кубов. Локальные кубы хранятся в файлах с расширением *.cub. Напомним, что для корректного создания локального куба на основе серверного куба, содержащего несбалансированные измерения, рекомендуется применять Microsoft Excel 2002. Поэтому все последующие примеры выполнены в этой версии Microsoft Excel.
Чтобы создать локальный OLAP-куб на основе серверного куба, следует на панели инструментов PivotTables выбрать пункт меню PivotTable | Offline OLAP в Excel 2002 (в Excel 2000 ему соответствовал пункт меню PivotTable | Client-Server Settings) и нажать кнопку Create offline data file (рис. 15; в Excel 2000 — Create Local Cube).
Рис. 15. Диалоговая панель Offline OLAP Settings
Далее следует выбрать измерения и их уровни, а также меры, которые будут присутствовать в локальном кубе (рис. 16).
Рис. 16. Выбор измерений и мер для локального куба
Помимо выбора измерений, их уровней и мер можно внести и другие ограничения в набор данных, который будет содержаться в локальном кубе, выбрав набор членов изменений, участвующих в его формировании (рис. 17).
Рис. 17. Выбор членов измерений для локального куба
Теперь осталось только сохранить локальный куб в файле с расширением *.cub. Отметим, что этот файл является отчуждаемым: его можно просматривать на любом компьютере, оснащенном как Microsoft Excel 2002, так и Microsoft Excel 2000, независимо от наличия на нем Microsoft SQL Server Analysis Services или их клиентской части.
Разработчики приложений, использующих описанную выше функциональность Microsoft Excel, могут обратиться к нашим ранее опубликованным статьям, посвященным этому вопросу (например, «Создание OLAP-клиентов с помощью Excel и Microsoft PivotTable Services»).
Заключение
Итак, мы рассмотрели один из простейших способов работы с OLAP-данными — применение в качестве OLAP-клиента приложений и компонентов Microsoft Office. Мы научились отображать OLAP-кубы c помощью средств создания сводных таблиц Microsoft Excel, манипулировать отображением данных в сводных таблицах, строить сводные диаграммы на основе OLAP-кубов, а также ознакомились с созданием локальных OLAP-кубов, содержащих подмножества данных серверного OLAP-куба.
* * *
Другие способы работы с OLAP-данными будут рассмотрены в следующих номерах журнала.
Обсудить на форуме | Написать вебмастеру |
Итак, продолжаем осваивать внутренности OLAP кубов (предыдущие статьи на эту тему: 1, 2, 3). На повестке дня актуальный вопрос: «А как можно достать OLAP куб программно?». Понимаю – теория-теорией, но если данные нельзя (или очень сложно) получать из кода, то грош цена таким технологиям. К счастью, здесь все очень просто – знаете ADO.NET? Ну, так это то же самое, только называется ADOMD.NET.
Как же так, скажите вы – еще одна библиотека, которую нужно изучать?! Вообщем-то, можно конечно и через OLEDB «доставать» куб (как-нибудь напишу, как это делать), но тогда теряется вся специфика многомерности, а сама библиотека ADOMD.NET достаточно простая, что я сейчас и покажу.
Итак, приступим.
Окружение
Как всегда начнем с окружения. Для сегодняшнего рецепта нам понадобится:
Если у вас нету Хабракуба, вы можете очень быстро его создать, прочитав следующую главу, остальным можно пропустить.
XMLA или быстро создаем Хабракуб
Если вы пользуетесь Microsoft SQL Server Management Studio, вы наверняка замечали ряд кнопочек для создания различных запросов, расположенный вверху:
Первые две кнопочки понятны, а вот следующие 3 скорее всего требуют пояснений:
- MDX Query – создает MDX запрос для выборки данных из куба (в первой статье из этой серии есть несколько примеров);
- DMX Query – нет, это не тавтология, DMX запросы предназначены для выборки данных из Data Mining структур;
- Нам понадобится именно последняя кнопка XMLA Query – XMLA — это, основанный на XML, язык управления аналитическими системами;
Например, в нашем случае, чтобы быстро создать Хабракуб, можно просто запустить XMLA запрос, скачав его отсюда.
Получилось? Теперь чтобы заполнить куб данными, скачайте мой старый SQL скрипт для создания Хабра-хранилища голосов. И запустите куб на процессинг — для этого подключите вашу Management Studio к Analysis Services, выберите Хабракуб а затем комманду Process в контекстном меню. Следует учесть, что, по-умолчанию, датасорс для куба – это локальное (local) хранилище HabraDW. Если вы хотите изменить строку подключения – откройте папку Data Sources вашего куба и измените параметры Habra DW.
Продолжаем…
ADOMD.NET
Библиотеку ADOMD.NET можно скачать со страницы Microsoft SQL Server 2008 Feature Pack:
Hello World, часть 1
Итак, первое, что хочется сделать – это вычитать определение куба, тоесть из каких измерений и иерархий он состоит.
Создаем консольный C# проект, добавляем референс на ADOMD.NET:
И используем следующий код:
// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = «provider=msolap;Data Source=(local);initial catalog=HabraCube;»;
mdConn.Open();
// iterate through cubes
foreach (CubeDef cube in mdConn.Cubes)
{
if (cube.Type != CubeType.Cube) continue;
Console.WriteLine(«*** Cube: » + cube.Name);
// iterate through dimensions
foreach (Dimension dimension in cube.Dimensions)
{
Console.WriteLine(«-> Dimension: » + dimension.Name);
// iterate through hierarchies
foreach (Hierarchy hierarchy in dimension.Hierarchies)
{
Console.WriteLine(«—> Hierarchy: » + hierarchy.Name);
// iterate through levels
foreach (Level level in hierarchy.Levels)
{
Console.WriteLine(«—> Level: » + level.Name);
}
}
}
}
}
* This source code was highlighted with Source Code Highlighter.
Результат:
Достаточно прозрачно, не правда ли?
Hello World, часть 2
А теперь запустим MDX запрос и вычитаем результат. Для этого используем следующий код:
// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = «provider=msolap;Data Source=(local);initial catalog=HabraCube;»;
mdConn.Open();
AdomdCommand mdCommand = mdConn.CreateCommand();
mdCommand.CommandText = mdx; // << MDX Query
// work with CellSet
CellSet cs = mdCommand.ExecuteCellSet();
// our method supports only 2-Axes CellSets
if (cs.Axes.Count != 2) return;
TupleCollection tuplesOnColumns = cs.Axes.Set.Tuples;
TupleCollection tuplesOnRows = cs.Axes.Set.Tuples;
// output column headers
Console.Write(«{0,-12}», «Item»);
for (int col = 0; col
{
Console.Write(«{0,-12}», tuplesOnColumns.Members.Caption);
}
Console.WriteLine();
// output rows
for (int row = 0; row
{
Console.Write(«{0,-12}», tuplesOnRows.Members.Caption);
// fill columns
for (int col = 0; col
{
Console.Write(«{0,-12}», cs.Cells.Value);