Пользователь
0,0
рейтинг
6 августа 2009 в 05:26

Разработка → Многомерные кубы, OLAP и MDX

SQL*
OLAP Довольно давно являюсь обитателем Хабра, но так и не доводилось читать статьи на тему многомерных кубов, OLAP и MDX, хотя тема очень интересная и с каждым днем становится все более актуальной.
Не секрет, что за тот небольшой промежуток времени развития баз данных, электронного учета и онлайн систем, самих данных накопилось очень много. Теперь же интерес также представляет полноценный анализ архивов, а возможно и попытка прогнозирования ситуаций для подобных моделей в будущем.
С другой стороны, большие компании даже за несколько лет, месяцев или даже недель могут накапливать настолько большие массивы данных, что даже их элементарный анализ требует неординарных подходов и жестких аппаратных требований. Такими могут быть системы обработки банковских транзакций, биржевые агенты, телефонные операторы и т.д.
Думаю, всем хорошо известны 2 разных подхода построения дизайна баз данных: OLTP и OLAP. Первый подход (Online Transaction Processing — обработка транзакций в реальном времени) рассчитан на эффективный сбор данных в реальном времени, второй же (Online Analytical Processing – аналитическая обработка в реальном времени) нацелен именно на выборку и обработку данных максимально эффективным способом.

Давайте рассмотрим основные возможности современных OLAP кубов, и какие задачи они решают (за основу взяты Analysis Services 2005/2008):
  • быстрый доступ к данным
  • преагрегация
  • иерархии
  • работа с временем
  • язык доступа к многомерным данным
  • KPI (Key Performance Indicators)
  • дата майнинг
  • многоуровневое кэширование
  • поддержка мультиязычности

Итак, рассмотрим возможности OLAP кубов немного подробнее.

Немного подробнее о возможностях


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

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

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

Работа с временем
Так как в основном анализ данных происходит на временных участках, именно времени в OLAP системах выделено особое значение, а значит, просто определив для системы, где у нас тут время, в дальнейшем можно с легкостью пользоваться функциями типа Year To Date, Month To Date (период от начала года/месяца и до текущей даты), Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.

Язык доступа к многомерным данным
MDX (Multidimensional Expressions) — язык запросов для простого и эффективного доступа к многомерным структурам данных. И этим все сказано – внизу будет несколько примеров.

Key Performance Indicators (KPI)
Ключевые показатели эффективности — это финансовая и нефинансовая система оценки, которая помогает организации определить достижение стратегических целей. Ключевые показатели эффективности могут быть достаточно просто определены в OLAP системах и использоваться в отчетах.

Дата майнинг
Интеллектуальный анализ данных (Data Mining) — по сути, выявление скрытых закономерностей или взаимосвязей между переменными в больших массивах данных.
Английский термин «Data Mining» не имеет однозначного перевода на русский язык (добыча данных, вскрытие данных, информационная проходка, извлечение данных/информации) поэтому в большинстве случаев используется в оригинале. Наиболее удачным непрямым переводом считается термин «интеллектуальный анализ данных» (ИАД). Впрочем, это отдельная, не менее интересная тема для рассмотрения.

Многоуровневое кэширование
Собственно для обеспечения наиболее высокой скорости доступа к данным, кроме хитрых структур данных и преагрегаций, OLAP системы поддерживают многоуровневое кэширование. Кроме кэширования простых запросов, также кэшируются части вычитанных из хранилища данных, агрегированные значения, вычисленные значения. Таким образом, чем дольше работаешь с OLAP кубом, тем быстрее он, по сути, начинает работать. Также существует понятие «разогрев кэша» — операция, подготавливающая OLAP систему к работе с конкретными отчетами, запросами или всем вместе взятым.

Поддержка мультиязычности
Да-да-да. Как минимум Analysis Services 2005/2008 (правда, Enterprise Edition) нативно поддерживают мультиязычность. Достаточно привести перевод строковых параметров ваших данных, и клиенту, указавшему свой язык, будут приходить локализированные данные.

Многомерные кубы


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

Многомерный куб

Фактически, плоскость (или множество всех таких точек) и будет являться кубом, а, соответственно, Время, Товары и Покупатели – его измерениями.
Представить (и нарисовать) четырехмерный и более куб немного сложнее, но суть от этого не меняется, а главное, для OLAP систем совершенно неважно в скольких измерениях вы будете работать (в разумных пределах, конечно).

Немного MDX


Итак, в чем же прелесть MDX – скорее всего в том, что описывать нужно не то как мы хотим выбрать данные, а что именно мы хотим.
Например,

SELECT
{ [Measures].[Units] } ON COLUMNS,
{ [Time].[June, 2009], [Time].[July, 2009] } ON ROWS
FROM [Sales]
WHERE ([Product].[iPhone], [Country].[Mozambik])


* This source code was highlighted with Source Code Highlighter.



Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?

А вот чуть посложнее:

WITH MEMBER AverageSpend AS
[Measures].[Amount] / [Measures].[Transaction Count]
SELECT
{ AverageSpend } ON COLUMNS,
{ [Customer].[Sex].[Female], [Customer].[Sex].[Male] } ON ROWS
FROM [Sales]
WHERE ([Shop].[Apple])


* This source code was highlighted with Source Code Highlighter.


Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.

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

Заключение


На самом деле, данная статья очень мало покрывает даже базовых понятий, я бы назвал ее «appetizer» — возможность заинтересовать хабра-сообщество данной тематикой и развивать ее дальше. Что же касается развития – тут огромное непаханое поле, а я буду рад ответить на все интересующие вопросы.

P.S. Это мой первый пост об OLAP и первая публикацию на Хабре — буду очень признателен за конструктивный фидбек.
Update: Перенес в SQL, перенесу в OLAP как только разрешат создавать новые блоги.
Vitalii Vitko @Vitko
карма
63,7
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (34)

  • +4
    интересно, как вы сами заметили на хабре не было статей про OLAP, так что продолжайте и пишите еще
    • +8
      Спасибо! Планирую создать OLAP блог и написать еще пару статей о собственно перфомансе современных OLAP систем (думаю много кто будет удивлен :)), основам дизайна складов данных (Data Warehouse) ну и MDX — уникальнейшая вещь.
      • 0
        Статей и правда вроде не было, но совсем чуть-чуть этой темки коснулись тут на Хабре, в рамках темы «3D-визуализация БД». Блог только несколько несерьёзный для такой темы: Киберпанк :) Я там в комментах иллюстрацию разместил, имхо чуток более показательная, чем та, что выше. Может вам пригодиться в будущих статьях.
      • 0
        традиционный перевод Data Warehouse — хранилище данных. Склад данных это скорее data mart
        • 0
          Datamart по-русски обычно называют витриной данных. А wd — это хранилище, да.
  • 0
    Спасибо Vitko.
    Прочел от корки до корки.
    Если честно — не понял какой софт (кроме продуктов М$) позволяет работать с кубами.
    Если не сложно, то расскажите пожалуйста о плюсах и минусах альтернативных программных продуктах.

    • +1
      Да, я собственно планировал сделать описание продуктов в следующих публикациях. Если вкратце, то первые реализации OLAP были выпущены копанией Express (потом куплен Ораклом), потом появились Essbase и многие другие. Microsoft начал активно разрабатывать свои OLAP продукты сравнительно недавно (после покупки компании Panorama Software), а первая версия Analysis Services была выпущена вместе с SQL Server 7. Но по настоящему юзабельным этот продукт стал только с выпуском SQL Server 2005 (а стабильно начал работать только в последнем релизе, который идет вместе с SQL Server 2008). О плюсах, минусах и различиях постараюсь рассказать подробнее в скором времени.
      • –3
        ппц. Я уже лет пять работаю разработчиком ОЛАП, начиная с версии MS AS 2000, и фраза про нестабильность меня просто взбесила.
        • +3
          Не хотел никого задевать, но тут вопрос в том как определять стабильность. То что все работает — тут конечно не поспоришь, но то количество сервис паков/хот фиксов даже к 2005-им Analysis Services дает понять что продукт все еще активно развивается.
    • 0
      Посмотрите на Pentaho. OpenSource, и по возможностям вполне-вполне ничего.
  • 0
    Спасибо очень интересно! Статья очень напоминает выжимку из книги. Не подскажите от куда был взят график?
    • 0
      Такие графики есть в любой книжке про OLAP, DW, BI и прочее
  • +1
    Похоже на структуру чипа от первого терминатора.
  • 0
    Интересно узнать, что се это время занимался проектированием OLAP-кубов :) А есть ли кросс-платформенные решения, стыкующиеся, например с MySQL?
    • 0
      конечно
    • –1
      Все решения (во всяком случае о которых я знаю) стыкуються с максимально возможными источниками данных, тоесть создать куб вы можете используя SQL Server, Oracle, MySQL, Access, Excel, etc. Другое дело, что сам куб может обслуживать платформа Microsoft SQL Server или Oracle или, например, SAP.
      • 0
        Извините за возможную тупость вопроса, но с Microsoft SQL Server знаком очень поверхностно.

        Т.е. мне надо на отдельной машине установить Microsoft SQL Server, подключить MySQL как источник данных и можно пользоваться OLAP? А где в этом случае будут храниться все агрегированые/промежуточные даные, кэш и т.д.?
        • 0
          Вы абсолютно правы, а сам куб/агрегированные/промежуточные/кэш данные будут храниться на сервере Microsoft SQL — достаточно «поднять» Analysis Services, при этом, можно, например, установить режим ROLAP, при котором, фактические данные не копируются а берутся непосредственно с MySQL базы, правда скорость MDX запросов при этом очень ухудшается.
        • +1
          Через ODBC можно подключить куб к любому источнику данных для которого есть соответствующие драйвера. Хоть текстовые файлы, хоть dBase, что угодно.
          Сам куб будет храниться в файловой системе, если мы говорим о MOLAP. В базе данных SQL Server храненится только описания структуры куба. SQL Server и Analysis Services это вообще два совершенно разных продукта. MS AS 2000, например, можно установить вообще без установки SQL сервера.
          • 0
            Да, это полезное дополнение.
        • +3
          Автор забыл упомянуть кросплатформенный опенсурсный OLAP сервер Mondrian. Microsoft SQL Server — очень дорогая штука и работает только под одной ОС.
          Использую Mondrian c MySQL и PostgreSQL базами.
          • 0
            Супер! Сам не знал :) — спасибо за полезную информацию.
          • 0
            Простите за отступление и, возможно, тупой вопрос. Но каким образом генерируются сводные (pivot) таблицы в Mondrian? На основе моей схемы? Как вообще происходит подготовка внутренностей куба (набора сводных таблиц)? Про выборку, вроде, понятно…
            Заранее спасибо.
    • 0
      Обычно все данные выгружаются в одно хранилище. Там уже строится КУБ, всё кроссплатформенно.
      Если MySQL у вас является источником данных, то самое разумное — вытащить данные из него в другую БД-хранилище.
      • 0
        Возьмите любую биллинговую систему- данные постоянно меняются, а анализ идет постоянно и онлайн. Так что такой вариант не подойдет.
        • 0
          Analysis Services предоставляют возможность стоить из данных partitions — самые свежие данные можно напрямую брать из источника, не загружая в куб, тоесть если нужны отчеты по real time данным — можно построить соответствующий дизайн.
        • 0
          Реально если на биллинговой системе организовать Аналитическую отчётность, всё сразу накроется. Прикиньте десяток аналитиков постоянно запускающих запросы, где аггрегируются показатели за последние пол-года, год, а то и три года. Биллинг подождёт что ли? :)
          • 0
            Ну собственно для того и строятся кубы, чтобы это ускорить. Если десяток — это ладно, а есть же еще клиенты со своими отчетами…
            • 0
              Я про то, что многомерный анализ на сервере OLTP системы и на структуре данных заточенной под OLTP — это зло. Реально при сильно нагруженной биллинговой системе разумно заморочиться c change data capture и переливать данные в data warehouse в ориентированую на многомерный анализ структуру, а там уже заниматься анализом.
              • 0
                учитывая что OLTP как правило не одна, особо на ней ничего не построишь, все всегда льется в отдельный wh
  • 0
    Насчёт быстрого доступа к данным я бы поспорил. Основное отличие OLAP от OLTP в том, что типичные запросы работают с большим количеством записей, аж до сотен миллионов. И отчёты, которые строятся часами и etl-процессы идущие всю ночь — это не редкость, а скорее обыденность. Просто типичные модели данных для хранилища строятся так, чтобы сократить это время, вот и всё. Ну и реляционная теория никуда не девается. Просто некоторые вещи, типа нормализации зачастую не требуются, а наоборот возбраняются.
  • +1
    Нового почти не узнал, так как давненько вплотную работаю с аналитическими комплексами, но, так сказать, я материал для себя закрепил =)
    Вам бы хорошо рассмотреть различные продукты, предоставляющие возможность работы с многомерными даными, как платные, так и бесплатные. Может быть, выложить подборку разных библиотек для работы с «аналитикой» и т.д. Было бы очень полезно, иногда так лень самому искать =)
    Если говорить про продукты помимо Analysis Services, то для начала посоветую начать с большого аналитического комплекса «Прогноз» отечественного производства. Очень хорошая вещь, особенно в плане визуализации работы с OLAP и многомерными кубами.
    • 0
      Спасибо за наводку :)!
      • 0
        Pentaho получило приз как лучшее Open Source BI приложение по версии InfoWorld. Может вам будет интересно.

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