Создаем OLAP куб. Часть 1

    OLAP

    Продолжая тематику Многомерные кубы, OLAP и MDX и olap для маленькой компании, традиционно, предлагаю начать с простенького «Hello World» куба, который будет анализировать процессы и тенденции голосований на Хабре.

    Итак, давайте попробуем создать свою первую OLAP систему.
    Но, прежде чем, потирая руки, запускать Business Intelligence Studio, предлагаю вначале создать хранилище данных хабра-голосов, так называемый Data Warehouse.
    Зачем? Причин в этом несколько:
    • сама суть Data Warehouse-а хранить «очищенные» данные, готовые для анализа, поэтому даже его изначальная структура может сильно отличаться от структуры нашей хабра-OLTP базы данных
    • в HabraDW (так мы его назовем) мы вынесем только ту информацию, которая нам нужна будет для анализа, ничего лишнего
    • к Data Warehouse не накладываются требования нормализации. Даже наоборот, денормализировав некоторые данные можно добиться более понятной схемы для построения куба, а также скорости загрузки данных в куб

    Немного теории.


    По сути, Data Warehouse может быть:
    • чисто виртуальный (например, определенным как множество SELECT-ов или даже вызовов сложных хранимых процедур, которые каким-то образом определят входные данные для куба)
    • вполне реальным, то есть существовать физически на каком-то сервере (или серверах)
    В последнем случае, вы, скорее всего, захотите имплементировать ETL процессы (используя Integration Services или что-то еще), но это уже повод для другой, не менее интересной, статьи.

    Каким же должен быть Data Warehouse?


    Все очень просто – ваш Data Warehouse должен иметь структуру формы звездочки (star model) или снежинки (snowflake model) и состоять из фактов (facts) и измерений (dimensions).

    Факты – это фактические записи (records) о каком-то процессе, который мы хотим анализировать, например, процесс голосования на Хабра, или процесс изменения цены товара на бирже. Очень часто факты содержат какие-нибудь числовые данные, например, фактическое значение голоса или цены.

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

    Определив где у нас факты, а где измерения — очень просто построить модель звезды.

    Звезда.


    Звезда

    В центре указываем нашу таблицу фактов, а лучами выводим измерения.

    А теперь снежинка.


    Снежинка — это та же звезда, только измерения могут зависеть от измерений следующего уровня, а те в свою очередь могут включать еще уровни.

    Снежинка
    Каждая из этих моделей имеет свои достоинства и недостатки и собственно выбор модели должен базироваться на требованиях к дизайну куба, скорости загрузки данных, дискового пространства и т.д.
    Естественно, конечные Data Warehouse обычно намного сложнее и состоят из нескольких звезд или снежинок, которые могут совместно использовать общие измерения.

    HabraDW.


    Перейдем к собственно разработке нашего Data Warehouse-а.

    Наша цель – анализ тенденций голосования на Хабре, нахождение закономерностей и трендов.
    Основные тенденции, которые мы хотим определить:
    • в какое время года/месяца/недели голосуют лучше/хуже/чаще
    • как голосуют по пятницам и понедельникам (например)
    • как влияет на результат голосования наличие в посте слов Microsoft, или Карма
    • средняя активность пользователей, «пики» голосования
    • и т.п.
    Для наглядности, наша первая модель будет абсолютно простой – включим только то, что относится к голосованию и исключим все лишнее, включая время регистрации пользователей и факт того, кто именно запостил статью, а также время голосования (только дата) и остальные атрибуты (все эти данные можно будет включить в следующих статьях и попробовать анализировать более сложные вещи).

    В итоге, имеем следующие таблицы:
    • Таблица фактов FactHabravote – определяет кто, когда, за что и как именно проголосовал. Значение Vote в нашем случае будет +- 1, но тип поля позволяет расширить дельту голосами, например, +- 10
    • Измерение времени DimTime – определяет нужные для анализа атрибуты времени (значения и названия)
    • Измерение пользователей DimUser – определяет пользователей Хабра, пока только никнейм
    • Измерение постов DimPost – определяет посты, в нашем случае содержит заголовок и булевые поля, определяющие содержит ли пост слова Microsoft и Карма.

    Итоговая схема нашей звезды будет такой.


    HabraDW

    А здесь исходный SQL скрипт, который создает и наполняет (пока что только случайными данными) наше хранилище.

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

    Похожие публикации

    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

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

    Комментарии 21

      +2
      я только сейчас понял что тело «оборачивается переменными».
      с нетерпением жду продолжения
        +3
        Жаль что данные случайны — думаю многим было бы интересно пOLAPать реальную статистику Хабра.
          0
          да. был бы признателен за реальный пример.
          у меня просто есть данные вида
          дата — дата — дата — факт — факт — факт…
          работаю сводными таблицами… но очень хочется увидить все это в OLAPe как оно и с чем едят.
          и кстати плусующим… помогите человеку перенести из SQL в OLAP.
            +2
            «дата — дата — дата — факт — факт — факт» — это как? OLAP — это тип информационных систем. SQL — язык такой. OLAP система вполне может выполнять sql- запросы.
          0
          Спасибо! Интересно.
          Жду продолжения...)
            +3
            Ипическая сила! Вот это круто! Спасибо большое.
              0
              интересно продолжение… если окажется реально удобным надо будет переписать сбор статистики на проекте :)
                0
                Очередной раз спасибо за просвещение… теперь хоть в вики заглянул чтобы посмотреть что такое OLAP
                  0
                  хорошая статья, кратко и понятно
                  только я бы по больше раскрыл суть измерений, в частности, иерархические, так как в основном то они и используются
                    0
                    Иерархии полностью раскроются во время построения куба, хотя таблица DimTime уже сейчас «приоткрывает» нам конечный вид иерархии времени (год->месяц->день). Дальше — круче!
                    0
                    Звезда, снежинка — все это забавно, конечно =)
                    Но заметьте, ведь строить таким образом многомерные кубы в системе, в которой количество измерений заранее в принципе неизвестно, непреемлемо. Это не есть хороший метод, ИМХО.
                    Да и, думаю, вообще неинтересно и уныло использовать такие методы. Я хочу сказать, что много лучше было бы строить кубы на основе метаданных измерений, количество которых может быть произвольным, без использования таблицы для каждого измерения. А данные банально храняться в одной таблице, к которой обращаемся с помощью сгенерированных на основе взаимного расположения измерений и выбранных в них элементах SQL запросов.
                      +3
                      И при каждом запросе субд выполняет full scan одной большой таблицы?
                        0
                        Забегая наперед, скажу, что, как минимум, начиная с Analysis Services 2005 — это стало возможно. Другое дело, что не зная наперед о структуре ваших данных, куб не сможет преагрегировать значения, а также вы не сможете эфективно использовать кэш сервера. В результате, если построить куб на чисто «виртуальных» метаданных, то выгоды в скорости запросов будут совсем небольшие (для небольших измерений) или их совсем не будет (если размеры измерений превосходят 2-3 миллиона записей). Но, я часто вижу использование подобных «виртуальных» измерений на продакшн системах, особенно для маленьких, динамических измерений (порядка 10-100 членов).
                        0
                        Как я понял, из реальной базы данных при помощи представлений можно сделать «звезду» или «снежинку». Но насколько этот подход рационален?
                          0
                          Смотря с какой стороны посмотреть. Это, однозначно, экономит вам дисковое пространство, но лимитирует вас в гибкости и скорости загрузки данных в куб. Например, если вам нужно каким-то хитрым образом «очистить» данные перед загрузкой, ваше представление может не справится с задачей. Или если само представление будет очень сложным с точки зрения запроса, загрузка данных в куб будет занимать много времени.
                          Еще как аргумент — очень часто данные в куб берутся из нескольких источников, что делает невозможным использование только представлений.
                            +2
                            Бывает так, что к «Реально базе данных» вообще не подобраться. Например DBA какого нибудь ораклового сервера, где АБС-ка крутиться вам просто не разрешит создавать на реальной базе свои вьюшки и вообще выполнять какие-либо запросы. Мало ли чё вы там сджойните и как, а потом банк не сможет целый день работать. В таком случае можнл выгружать новые данные в плоские файлы, а оттуда забирать в хранилище.
                            0
                            все эти советы, зачем строить звезду снежинку, из разряда как поменьше работать не думаю о перформансе
                            метаданные=тормознутось
                            в этом и есть задача толкового разработчика — проектирование красивой схемы, а не драгэнддроп и тыканье мышкой
                              –2
                              Я честно говоря тоже не понял смысла сферического коня в вакууме — где же здесь куб.

                              Представляю, как быстро ляжет сервак, если данные о голосовании сделать из трех таблиц включая отдельную таблицу для таймстампа на 8 полей. Жесть!
                                +1
                                Реально отдельная «таблица для timestampa» сильно сокращает нагрузку на сервер. А куб на картинке хорошо просматривается.
                              0
                              Перезалейте изображения, пожалуйста.
                                0
                                Картинки не отображаются

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

                                Самое читаемое