Уровни представления 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.

Технические аспекты многомерного хранения данных

Как уже говорилось выше, средства OLAP-анализа могут извлекать данные и непосредственно из реляционных систем. Такой подход был более привлекательным в те времена, когда OLAP-серверы отсутствовали в списках продуктов ведущих производителей СУБД. Но сегодня и Oracle, и Informix, и Microsoft предлагают полноценные OLAP-серверы, и даже те IT-менеджеры, которые не любят разводить в своих сетях "зоопарк" из ПО разных производителей, могут купить (точнее, обратиться с соответствующей просьбой к руководству компании) OLAP-сервер той же марки, что и основной сервер баз данных.

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

Но, как известно, за все надо платить. И за скорость обработки запросов к суммарным данным приходится платить увеличением объемов данных и времени на их загрузку. Причем увеличение объема может стать буквально катастрофическим - в одном из опубликованных стандартных тестов полный подсчет агрегатов для 10 Мб исходных данных потребовал 2,4 Гб, т. е. данные выросли в 240 раз! Степень "разбухания" данных при вычислении агрегатов зависит от количества измерений куба и структуры этих измерений, т. е. соотношения количества "отцов" и "детей" на разных уровнях измерения. Для решения проблемы хранения агрегатов применяются подчас сложные схемы, позволяющие при вычислении далеко не всех возможных агрегатов достигать значительного повышения производительности выполнения запросов.

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

MOLAP (Multidimensional OLAP) - и детальные данные, и агрегаты хранятся в многомерной БД. В этом случае получается наибольшая избыточность, так как многомерные данные полностью содержат реляционные.

ROLAP (Relational OLAP) - детальные данные остаются там, где они "жили" изначально - в реляционной БД; агрегаты хранятся в той же БД в специально созданных служебных таблицах.

HOLAP (Hybrid OLAP) - детальные данные остаются на месте (в реляционной БД), а агрегаты хранятся в многомерной БД.

Каждый из этих способов имеет свои преимущества и недостатки и должен применяться в зависимости от условий - объема данных, мощности реляционной СУБД и т. д.

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

Технология решения аналитических задач.

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

Но не всегда за текущими делами видны возможности, которые дают нам новые технологии. Поэтому в этой статье приведен (далеко не исчерпывающий) список сфер применения OLAP-технологий, взятых из реальной жизни и выдуманных исходя из здравого смысла. Некоторые из этих сфер являются классикой, некоторые выглядят слегка экзотично.

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

В качестве инструмента можно применить, например, OLAP-клиента «Контур Стандарт» компании Intersoft Lab.

Пример: Продажи

Ключевые вопросы коммерсанта: "Сколько штук продано", "На какую сумму продано" расширяются по мере усложнения бизнеса и накопления исторических данных до некоторого множества факторов, или разрезов: "..в Москве, в Сибири", "..в прошлом квартале, по сравнению с нынешним", "..через магазин А, по сравнению с магазином Б".

Ответы на подобные вопросы необходимы для принятия управленческих решений: об изменении ассортимента, цен, закрытии и открытии магазинов, филиалов, расторжении и подписании договоров с дилерами, проведения или прекращения рекламных кампаний и т.д.

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

Интересно, что эта таблица в целом соответствует обычному счету-фактуре, то есть данные в таком виде теоретически обязаны быть у любого предприятия.

Поля таблицы: Время, Категория товара, Товар, Регион, Продавец, Покупатель, Сумма, Количество.

Время. Как правило это несколько периодов: Год, Квартал, Месяц, Декада, Неделя, День. К счастью OLAP-инструменты автоматически вычисляют старшие периоды из даты и вычисляют итоги по ним.

Категория товара. Категорий может быть несколько, они отличаются для каждого вида бизнеса: Сорт, Модель, Вид упаковки и пр. Если продается только один товар или ассортимент очень невелик, то категория не нужна.

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

Регион. В зависимости от глобальности бизнеса под измерением Регион может иметься в виду Континент, Группа стран, Страна, Территория, Город, Район, Улица, Часть улицы. Конечно, если есть только одна торговая точка, это измерение отсутствует.

Продавец. Это измерение тоже зависит от структуры и масштабов бизнеса. Здесь может быть: Филиал, Магазин, Дилер, Менеджер по продажам. В некоторых случаях измерение отсутствует, например, когда продавец не влияет на объемы сбыта, магазин только один и так далее.

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

Важный вопрос - наличие данных. Если они есть, в любом виде, как Excel-таблица, в базе данных учетной системы, в виде структурированных отчетов филиалов ИТ-специалист сможет передать их OLAP-системе напрямую или с промежуточным преобразованием. Для этого OLAP-системы имеют специальные инструменты. Если этих данных нет, или они имеют недостаточную полноту и качество, OLAP не поможет. Но сбор, очистка и хранение данных это отдельная тема.

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

Факторный (структурный) анализ. Анализ структуры продаж для выявления важнейших составляющих в интересующем разрезе. Для этого удобно использовать диаграмму типа "Пирог", в более сложных случаях, когда иследуется сразу 3 измерения - "Столбцы". Например, в магазине "Дары моря" за квартал продажи рыбы = $100000, пива = $1000, хлеба = $500. Вывод: оборот магазина зависит только от рыбы (на самом деле быть может пиво необходимо для продажи рыбы, но это уже анализ зависимостей).

Анализ динамики. Выявление тенденций, сезонных колебаний. Наглядно динамику отображает график типа "Линия". Например, объемы продаж мойвы в течение года падали, а объемы продаж форели росли. Возможно, улучшилось благосотояние среднего покупателя, или изменился имидж магазина, а с ним и состав покупателей. Требуется провести корректировку ассортимента. Другой пример, в течение 3 лет летом снижается объем продаж пива темных сортов.

Анализ зависимостей. Сравнение объемов продаж разных товаров во времени для выявления необходимого ассортимента - "корзины". Для этого также удобно использовать график типа "Линия". Например, при удалении из ассортимента пива в течение первых двух месяцев обнаружилось падение продаж воблы.

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

Этими видами анализа возможности OLAP не исчерпываются. Например, применяя в качестве алгоритма вычисления промежуточных и окончательных итогов среднее арифметическое, или функции статистического анализа - дисперсия, среднее отклонение и т.д. можно получить самые изощренные виды аналитических отчетов.

Пример. Закупки

Задача обратно противоположная анализу продаж. Многие предприятия закупают комплектующие и материалы у поставщиков. Торговые предприятия закупают товары для перепродажи. Возможных задач при анализе закупок множество, от планирования денежных средств на основе прошлого опыта, до контроля за менеджерами, выбирающими поставщиков.

Один из примеров, когда анализ поставщиков дал предприятию огромные прибыли приводится в западной прессе. Пятизвездочная гостиничная сеть оформляла каждый номер своих отелей букетом роз и тратила на это огромные суммы. Когда компания приобрела OLAP-систему и проанализировала свои закупки и поставщиков, этот факт был осознан. Было принято решение отказаться от множества поставщиков, выбрать одного и потребовать от него скидок за объемы. В результате чего и были сэкономлены миллионы долларов.

Поля таблицы: Время, Категория товара, Товар, Регион, Поставщик, Покупатель, Сумма, Количество.

Поставщик. Это измерение может содержать название фирмы или быть иерархическим: Корпорация, Филиал и т.д.

Покупатель. Филиал, Отдел, Менеджер, выполняющие закупки.

Пример. Цены

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

Поля таблицы: Время, Категория товара, Товар, Регион, Количество в партии, Поставщик, Сумма.

Количество в партии. Это измерение отражает оптовые скидки поставщиков.

Пример. Маркетинг

Под маркетинговым анализом будем иметь ввиду только область анализа покупателей или клиентов-потребителей услуг. Для примера ограничимся розничной торговлей.

Задачей этого анализа является правильное позиционирование товара, выявление групп покупателей для целевой рекламы, оптимизация ассортимента. Например, если выясняется, что телефонами темно-серого цвета стоимостью более $500 пользуются исключительно мужчины старше 25 лет, то стоит изобразить в рекламе таких телефонов вместо стайки веселых девушек одного преуспевающего бизнесмена. Это очень грубый пример, известно, что маркетинговый анализ находится на грани между сложной наукой и малообъяснимым исскуством. Поэтому задача OLAP в данном случае - дать пользователю инструмент быстрого, со скоростью мысли, получения ответов на вопросы, интуитивно возникающие по ходу анализа данных.

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

Поля таблицы: Время, Образование, Профессия, Доходы, Пол, Возраст, Регион, Категория товара, Товар, Сумма, Количество

Для анализа применяются общие для OLAP технологии методы.

Факторный анализ. Анализ структуры покупателей в интересующем разрезе. Анализ структуры спроса.

Анализ динамики. Выявление тенденций, сезонных колебаний в разрезах характеристик покупателей.

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

Склад

Анализ структуры остатков на складе в разрезе видов товаров, складов, анализ сроков хранения товаров, анализ отгрузки по получателям и многие другие важные для предприятия виды анализов возможны при наличии в организации складского учета.

Шаблон анализа данных для склада (поля таблицы): Время, Регион, Склад, Категория товара, Товар, Сумма, Количество, Срок хранения в днях

Движение денежных средств

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

Поля таблицы: Время, Подразделение, Нал-безнал, Контрагент, Банк, Бизнес-операция, Валюта, Сумма

Бюджет

Одна из самых благодатных областей применения OLAP-технологий. Не даром ни одна современная система бюджетирования не считается завершенной без наличия в ее составе OLAP-инструментария для анализа бюджета. Большинство бюджетных отчетов легко строятся на основе OLAP-систем. При этом отчеты отвечают на очень широкую гамму вопросов: анализ структуры расходов и доходов, сравнение расходов по определенным статьям у разных подразделений, анализ динамики и тенденций расходов на определенные статьи, анализ себестоимости и прибыли.

В простейшем случае для анализа можно применять два измерения: Статья и Время и один факт: Сумма. Если расходы поставляются как отрицательные суммы, а доходы как положительные, то система покажет финансовый результат или прибыль как разницу доходов и расходов. Но значительно удобней иметь измерение Тип статьи = доход или расход. Это позволит проводить раздельный анализ доходов и расходов. При наличии некоторой иерархии статей появляется возможность обобщенного анализа. Большинство современных методик предлагают для крупных организаций вести бюджета в разрезе организационной структуры (по подразделениям) и в разрезе финансовой структуры (по центрам учета: центрам прибыли и центрам расходов).

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

Фактами служат только плановое значение статей или плановое и фактическое значение и отклонение от плана.

Поля таблицы: Время, Подразделение, Центр учета, Статья, Подстатья, Бизнес-операция, План, Факт, Отклонение

Бухгалтерские счета

Классический балансовый отчет, состоящий из номера счета и содержащий входящий остатки, обороты и исходящие остатки может быть отлично проанализирован в OLAP-системе.

Кроме того OLAP-система может автоматически и очень быстро вычислять консолидированные балансы многофилиальной организации, балансы за месяц, квартал и год, агрегированные балансы по иерархии счетов, аналитические балансы на основании аналитических признаков.

Фактами могут быть все шесть бухгалтерских значений счетов, или некоторые из них. Остатки могут быть свернутыми в сальдо или развернутыми на актив и пассив. Это определяется учетной политикой, конкретным видом анализа.

Однако, если с оборотами все просто - они должны суммироваться для вычисления оборотов за старший временной период, то при вычислении остатка могут возникнуть трудности. Остаток за старший временной период равен последнему остатку за младший, а не сумме остатков. Эта операция не свойственна OLAP-системам и как правило требует программирования алгоритма вычисления остатка. Для многих систем она недоступна вовсе. В OLAP-клиента Контур Стандарт такой алгоритм встроен.

Поля таблицы: Время, Подразделение, Счет, Признаки, Входящий актив, Входящий пассив, Дебет, Кредит, Исходящий актив, Исходящий пассив

Время. День, Месяц, Квартал, Год.

Счет. Это может быть иерархия счетов: Несколько уровней синтетических счетов, аналитические счета и субсчета.

Признаки. Аналитические признаки зависят от бизнеса и от возможностей бухгалтерской системы.

Финансовая отчетность

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

Некоторые страны уже перешли на такую технологию сбора данных. В некоторых российских контролирующих органах существуют планы перехода от ГОСТ-овских стандартов отчетов с многоэтажными шапками и алгоритмами типа "Итого, исключая строку 234 и включая строку 598 из отчета №987" к системе к сбору показателей и выпуску отчетов по OLAP-технологии.

В любом случае система внутренней отчетности предприятия не так консервативна и может быть перестроена в целях экономии средств на технические работы по созданию отчетов и получения возможностей многомерного оперативного анализа.

Поля таблицы: Время, Подразделение, Показатель, Единица измерения, Значение

Показатель. Это номер или название, одного показателя или некоторой иерархии показателей.

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

Результаты выборов

Количество голосов поданных за кандидатов в разных разрезах на разных выборах вычисляется постоянно и непрерывно. Удивительно, но для этого практически не применяются OLAP-инструменты. Хотя любой другой способ анализа и отчетности по результатом выборов в сотни раз дороже и медленней. Не говоря уже о том, что OLAP-отчеты покажут картинку красиво, так, что ее сразу можно опубликовать на сайте, в журнале и на телевидении.

Поля таблицы: Время, Рагион, Избирательная компания, Кандидат, Количество голосов

Избирательная компания. Название выборов и из даты, вида "Выборы в губернаторы 2000 года".

Кандидат. Избирательный блок или объединение, ФИО кандидата.

Кроме количества голосов могут вычисляться и финансовые показатели, такие как расходы на кандидатов, полученные пожертвования и взносы, средства государства, возврат средств. Творческий подход состоит в представлении фактов. Они могут находиьтся в одной колонке, т.е. это будет один факт, но с разными знаками приход и расход, тогда система будет вычислять остаток, или в разных колонках, т.е. фактов несколько.

Поля таблицы: Время, Регион, Избирательная компания, Кандидат, Тип движения средств, Направление движения средств, Приход, Расход, Возврат, Долг

Тип движения средств. Признак Приход-Расход.

Направление движения средств. Это справочник категорий прихода - расхода (пожертвования, расходы на рекламу, и др.).

Объемы производства

Это еще один пример статистического анализа. Таким образом можно анализировать объемы выращенного картофеля, выплавленной стали, сваренного пива и пр.

Фактом будет исследуемая величина - тонны, суммы, караты, в зависимости от единицы измерения продукта.

Поля таблицы: Время, Регион, Качество, Количество

Регион. В зависимости от задачи это Континент, Страна, Территория, Город, Район, Село, Производитель.

Качество. Характеристика произведенного товара - Сорт, Тип.

Потребление электроэнергии

Можно сказать с полной уверенностью, что задача многомерного анализа потребления электроэнергии решается со времен ГОЭЛРО. Иначе и быть не может, ведь для управления генерацией энергии, строительства линий электропередач, планирования мощностей электростанций нужно знать потребности в разрезе регионов, конкретных потребителей, времени.

Поля таблицы: Время, Регион, Потребитель, Количество