Только качественные посты
Смотрите видео к статье:
Сводная таблица:
Основное отличие Сводных таблиц – это наличие окна «Список полей сводной таблицы», из которого можно выбирать нужные поля и получать любую таблицу автоматически!
Как пользоваться
Откройте файл Excel, который подключен к OLAP-кубу, например «BIWEB»:
Теперь, что это означает и как этим пользоваться?
Перетащите нужные поля, чтобы получить, например, такую таблицу:
«Плюсики» позволяют детализировать отчет. В этом примере «Бренд» детализируется до «Сокращенных названий», а «Квартал» до «Месяца», т.е. так:
Что такое OLAP сегодня, в общем-то знает каждый специалист. По крайней мере, понятия «OLAP» и «многомерные данные» устойчиво связаны в нашем сознании. Тем не менее тот факт, что эта тема вновь поднимается, надеюсь, будет одобрен большинством читателей, т. к. для того, чтобы представление о чем-либо с течением времени не устаревало, нужно периодически общаться с умными людьми или читать статьи в хорошем издании.
Хранилища данных (место OLAP в информационной структуре предприятия)
Термин «OLAP» неразрывно связан с термином «хранилище данных» (Data Warehouse).
Приведем определение, сформулированное «отцом-основателем» хранилищ данных Биллом Инмоном: «Хранилище данных — это предметно-ориентированное, привязанное ко времени и неизменяемое собрание данных для поддержки процесса принятия управляющих решений».
Данные в хранилище попадают из оперативных систем (OLTP-систем), которые предназначены для автоматизации бизнес-процессов. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.
Зачем строить хранилища данных — ведь они содержат заведомо избыточную информацию, которая и так «живет» в базах или файлах оперативных систем? Ответить можно кратко: анализировать данные оперативных систем напрямую невозможно или очень затруднительно. Это объясняется различными причинами, в том числе разрозненностью данных, хранением их в форматах различных СУБД и в разных «уголках» корпоративной сети. Но даже если на предприятии все данные хранятся на центральном сервере БД (что бывает крайне редко), аналитик почти наверняка не разберется в их сложных, подчас запутанных структурах. Автор имеет достаточно печальный опыт попыток «накормить» голодных аналитиков «сырыми» данными из оперативных систем — им это оказалось «не по зубам».
Таким образом, задача хранилища — предоставить «сырье» для анализа в одном месте и в простой, понятной структуре. Ральф Кимбалл в предисловии к своей книге «The Data Warehouse Toolkit» пишет, что если по прочтении всей книги читатель поймет только одну вещь, а именно: структура хранилища должна быть простой, — автор будет считать свою задачу выполненной.
Есть и еще одна причина, оправдывающая появление отдельного хранилища — сложные аналитические запросы к оперативной информации тормозят текущую работу компании, надолго блокируя таблицы и захватывая ресурсы сервера.
На мой взгляд, под хранилищем можно понимать не обязательно гигантское скопление данных — главное, чтобы оно было удобно для анализа. Вообще говоря, для маленьких хранилищ предназначается отдельный термин — Data Marts (киоски данных), но в нашей российской практике его не часто услышишь.
OLAP — удобный инструмент анализа
Централизация и удобное структурирование — это далеко не все, что нужно аналитику. Ему ведь еще требуется инструмент для просмотра, визуализации информации. Традиционные отчеты, даже построенные на основе единого хранилища, лишены одного — гибкости. Их нельзя «покрутить», «развернуть» или «свернуть», чтобы получить желаемое представление данных. Конечно, можно вызвать программиста (если он захочет придти), и он (если не занят) сделает новый отчет достаточно быстро — скажем, в течение часа (пишу и сам не верю — так быстро в жизни не бывает; давайте дадим ему часа три). Получается, что аналитик может проверить за день не более двух идей. А ему (если он хороший аналитик) таких идей может приходить в голову по нескольку в час. И чем больше «срезов» и «разрезов» данных аналитик видит, тем больше у него идей, которые, в свою очередь, для проверки требуют все новых и новых «срезов». Вот бы ему такой инструмент, который позволил бы разворачивать и сворачивать данные просто и удобно! В качестве такого инструмента и выступает OLAP.
Хотя OLAP и не представляет собой необходимый атрибут хранилища данных, он все чаще и чаще применяется для анализа накопленных в этом хранилище сведений.
Компоненты, входящие в типичное хранилище, представлены на рис. 1.
Рис. 1. Структура хранилища данных
Оперативные данные собираются из различных источников, очищаются, интегрируются и складываются в реляционное хранилище. При этом они уже доступны для анализа при помощи различных средств построения отчетов. Затем данные (полностью или частично) подготавливаются для OLAP-анализа. Они могут быть загружены в специальную БД OLAP или оставлены в реляционном хранилище. Важнейшим его элементом являются метаданные, т. е. информация о структуре, размещении и трансформации данных. Благодаря им обеспечивается эффективное взаимодействие различных компонентов хранилища.
Подытоживая, можно определить OLAP как совокупность средств многомерного анализа данных, накопленных в хранилище. Теоретически средства OLAP можно применять и непосредственно к оперативным данным или их точным копиям (чтобы не мешать оперативным пользователям). Но мы тем самым рискуем наступить на уже описанные выше грабли, т. е. начать анализировать оперативные данные, которые напрямую для анализа непригодны.
Определение и основные понятия OLAP
Для начала расшифруем: OLAP — это Online Analytical Processing, т. е. оперативный анализ данных. 12 определяющих принципов OLAP сформулировал в 1993 г. Е. Ф. Кодд — «изобретатель» реляционных БД. Позже его определение было переработано в так называемый тест FASMI, требующий, чтобы OLAP-приложение предоставляло возможности быстрого анализа разделяемой многомерной информации (подробнее).
Fast (Быстрый) — анализ должен производиться одинаково быстро по всем аспектам информации. Приемлемое время отклика — 5 с или менее.
Analysis (Анализ) — должна быть возможность осуществлять основные типы числового и статистического анализа, предопределенного разработчиком приложения или произвольно определяемого пользователем.
Shared (Разделяемой) — множество пользователей должно иметь доступ к данным, при этом необходимо контролировать доступ к конфиденциальной информации.
Multidimensional (Многомерной) — это основная, наиболее существенная характеристика OLAP.
Information (Информации) — приложение должно иметь возможность обращаться к любой нужной информации, независимо от ее объема и места хранения.
OLAP = многомерное представление = Куб
OLAP предоставляет удобные быстродействующие средства доступа, просмотра и анализа деловой информации. Пользователь получает естественную, интуитивно понятную модель данных, организуя их в виде многомерных кубов (Cubes). Осями многомерной системы координат служат основные атрибуты анализируемого бизнес-процесса. Например, для продаж это могут быть товар, регион, тип покупателя. В качестве одного из измерений используется время. На пересечениях осей — измерений (Dimensions) — находятся данные, количественно характеризующие процесс — меры (Measures). Это могут быть объемы продаж в штуках или в денежном выражении, остатки на складе, издержки и т. п. Пользователь, анализирующий информацию, может «разрезать» куб по разным направлениям, получать сводные (например, по годам) или, наоборот, детальные (по неделям) сведения и осуществлять прочие манипуляции, которые ему придут в голову в процессе анализа.
В качестве мер в трехмерном кубе, изображенном на рис. 2, использованы суммы продаж, а в качестве измерений — время, товар и магазин. Измерения представлены на определенных уровнях группировки: товары группируются по категориям, магазины — по странам, а данные о времени совершения операций — по месяцам. Чуть позже мы рассмотрим уровни группировки (иерархии) подробнее.
Рис. 2. Пример куба
Даже трехмерный куб сложно отобразить на экране компьютера так, чтобы были видны значения интересующих мер. Что уж говорить о кубах с количеством измерений, большим трех? Для визуализации данных, хранящихся в кубе, применяются, как правило, привычные двумерные, т. е. табличные, представления, имеющие сложные иерархические заголовки строк и столбцов.
Двумерное представление куба можно получить, «разрезав» его поперек одной или нескольких осей (измерений): мы фиксируем значения всех измерений, кроме двух, — и получаем обычную двумерную таблицу. В горизонтальной оси таблицы (заголовки столбцов) представлено одно измерение, в вертикальной (заголовки строк) — другое, а в ячейках таблицы — значения мер. При этом набор мер фактически рассматривается как одно из измерений — мы либо выбираем для показа одну меру (и тогда можем разместить в заголовках строк и столбцов два измерения), либо показываем несколько мер (и тогда одну из осей таблицы займут названия мер, а другую — значения единственного «неразрезанного» измерения).
Взгляните на рис. 3 — здесь изображен двумерный срез куба для одной меры — Unit Sales (продано штук) и двух «неразрезанных» измерений — Store (Магазин) и Время (Time).
Рис. 3. Двумерный срез куба для одной меры
На рис. 4 представлено лишь одно «неразрезанное» измерение — Store, но зато здесь отображаются значения нескольких мер — Unit Sales (продано штук), Store Sales (сумма продажи) и Store Cost (расходы магазина).
Рис. 4. Двумерный срез куба для нескольких мер
Двумерное представление куба возможно и тогда, когда «неразрезанными» остаются и более двух измерений. При этом на осях среза (строках и столбцах) будут размещены два или более измерений «разрезаемого» куба — см. рис. 5.
Рис. 5. Двумерный срез куба с несколькими измерениями на одной оси
Значения, «откладываемые» вдоль измерений, называются членами или метками (members). Метки используются как для «разрезания» куба, так и для ограничения (фильтрации) выбираемых данных — когда в измерении, остающемся «неразрезанным», нас интересуют не все значения, а их подмножество, например три города из нескольких десятков. Значения меток отображаются в двумерном представлении куба как заголовки строк и столбцов.
Иерархии и уровни
Метки могут объединяться в иерархии, состоящие из одного или нескольких уровней (levels). Например, метки измерения «Магазин» (Store) естественно объединяются в иерархию с уровнями:
В соответствии с уровнями иерархии вычисляются агрегатные значения, например объем продаж для USA (уровень «Country») или для штата California (уровень «State»). В одном измерении можно реализовать более одной иерархии — скажем, для времени: <Год, Квартал, Месяц, День>и <Год, Неделя, День>.
Все, что говорилось выше про 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 во всех его проявлениях, то это совокупность концепций, принципов и требований, лежащих в основе программных продуктов, облегчающих аналитикам доступ к данным. Выясним, зачем аналитикам надо как-то специально облегчать доступ к данным.
Дело в том, что аналитики — это особые потребители корпоративной информации. Задача аналитика — находить закономерности в больших массивах данных. Поэтому аналитик не будет обращать внимания на отдельно взятый факт, что в четверг четвертого числа контрагенту Чернову была продана партия черных чернил — ему нужна информация о сотнях и тысячах подобных событий. Одиночные факты в базе данных могут заинтересовать, к примеру, бухгалтера или начальника отдела продаж, в компетенции которого находится сделка. Аналитику одной записи мало — ему, к примеру, могут понадобиться все сделки данного филиала или представительства за месяц, год. Заодно аналитик отбрасывает ненужные ему подробности вроде ИНН покупателя, его точного адреса и номера телефона, индекса контракта и тому подобного. В то же время данные, которые требуются аналитику для работы, обязательно содержат числовые значения — это обусловлено самой сущностью его деятельности.
Итак, аналитику нужно много данных, эти данные являются выборочными, а также носят характер «набор атрибутов — число". Последнее означает, что аналитик работает с таблицами следующего типа:
Здесь "Страна", "Товар", "Год" являются атрибутами или измерениями, а "Объем продаж" — тем самым числовым значением или мерой. Задачей аналитика, повторимся, является выявление стойких взаимосвязей между атрибутами и числовыми параметрами. Посмотрев на таблицу, можно заметить, что ее легко можно перевести в три измерения: по одной из осей отложим страны, по другой — товары, по третьей — годы. А значениями в этом трехмерном массиве у нас будут соответствующие объемы продаж.
Трехмерное представление таблицы. Серым сегментом показано, что для Аргентины в 1988 году данных нет
Вот именно такой трехмерный массив в терминах OLAP и называется кубом. На самом деле, с точки зрения строгой математики кубом такой массив будет далеко не всегда: у настоящего куба количество элементов во всех измерениях должно быть одинаковым, а у кубов OLAP такого ограничения нет. Тем не менее, несмотря на эти детали, термин "кубы OLAP" ввиду своей краткости и образности стал общепринятым. Куб OLAP совсем не обязательно должен быть трехмерным. Он может быть и двух-, и многомерным — в зависимости от решаемой задачи. Особо матерым аналитикам может понадобиться порядка 20 измерений — и серьезные OLAP-продукты именно на такое количество и рассчитаны. Более простые настольные приложения поддерживают где-то 6 измерений.
Измерения OLAP-кубов состоят из так называемых меток или членов (members). Например, измерение "Страна" состоит из меток "Аргентина", "Бразилия", "Венесуэла" и так далее.
Должны быть заполнены далеко не все элементы куба: если нет информации о продажах резиновых изделий в Аргентине в 1988 году, значение в соответствующей ячейке просто не будет определено. Совершенно необязательно также, чтобы приложение OLAP хранило данные непременно в многомерной структуре — главное, чтобы для пользователя эти данные выглядели именно так. Кстати именно специальным способам компактного хранения многомерных данных, "вакуум" (незаполненные элементы) в кубах не приводят к бесполезной трате памяти.
Однако куб сам по себе для анализа не пригоден. Если еще можно адекватно представить или изобразить трехмерный куб, то с шести — или девятнадцатимерным дело обстоит значительно хуже. Поэтому перед употреблением из многомерного куба извлекают обычные двумерные таблицы. Эта операция называется "разрезанием" куба. Термин этот, опять же, образный. Аналитик как бы берет и "разрезает" измерения куба по интересующим его меткам. Этим способом аналитик получает двумерный срез куба и с ним работает. Примерно так же лесорубы считают годовые кольца на спиле.
Соответственно, "неразрезанными", как правило, остаются только два измерения — по числу измерений таблицы. Бывает, "неразрезанным" остается только измерение — если куб содержит несколько видов числовых значений, они могут откладываться по одному из измерений таблицы.
Если еще внимательнее всмотреться в таблицу, которую мы изобразили первой, можно заметить, что находящиеся в ней данные, скорее всего, не являются первичными, а получены в результате суммирования по более мелким элементам. Например, год делится на кварталы, кварталы на месяцы, месяцы на недели, недели на дни. Страна состоит из регионов, а регионы — из населенных пунктов. Наконец в самих городах можно выделить районы и конкретные торговые точки. Товары можно объединять в товарные группы и так далее. В терминах OLAP такие многоуровневые объединения совершенно логично называется иерархиями. Средства OLAP дают возможность в любой момент перейти на нужный уровень иерархии. Причем, как правило, для одних и тех же элементов поддерживается несколько видов иерархий: например день-неделя-месяц или день-декада-квартал. Исходные данные берутся из нижних уровней иерархий, а затем суммируются для получения значений более высоких уровней. Для того, чтобы ускорить процесс перехода, просуммированные значения для разных уровней хранятся в кубе. Таким образом, то, что со стороны пользователя выглядит одним кубом, грубо говоря, состоит из множества более примитивных кубов.
В этом заключается один из существенных моментов, которые привели к появлению OLAP — производительности и эффективности. Представим себе, что происходит, когда аналитику необходимо получить информацию, а средства OLAP на предприятии отсутствуют. Аналитик самостоятельно (что маловероятно) или с помощью программиста делает соответствующий SQL-запрос и получает интересующие данные в виде отчета или экспортирует их в электронную таблицу. Проблем при этом возникает великое множество. Во-первых, аналитик вынужден заниматься не своей работой (SQL-программированием) либо ждать, когда за него задачу выполнят программисты — все это отрицательно сказывается на производительности труда, повышаются штурмовщина, инфарктно-инсультный уровень и так далее. Во-вторых, один-единственный отчет или таблица, как правило, не спасает гигантов мысли и отцов русского анализа — и всю процедуру придется повторять снова и снова. В-третьих, как мы уже выяснили, аналитики по мелочам не спрашивают — им нужно все и сразу. Это означает (хотя техника и идет вперед семимильными шагами), что сервер корпоративной реляционной СУБД, к которому обращается аналитик, может задуматься глубоко и надолго, заблокировав остальные транзакции.
Концепция OLAP появилась именно для разрешения подобных проблем. Кубы OLAP представляют собой, по сути, мета-отчеты. Разрезая мета-отчеты (кубы, то есть) по измерениям, аналитик получает, фактически, интересующие его "обычные" двумерные отчеты (это не обязательно отчеты в обычном понимании этого термина — речь идет о структурах данных с такими же функциями). Преимущества кубов очевидны — данные необходимо запросить из реляционной СУБД всего один раз — при построении куба. Поскольку аналитики, как правило, не работают с информацией, которая дополняется и меняется "на лету", сформированный куб является актуальным в течение достаточно продолжительного времени. Благодаря этому, не только исключаются перебои в работе сервера реляционной СУБД (нет запросов с тысячами и миллионами строк ответов), но и резко повышается скорость доступа к данным для самого аналитика. Кроме того, как уже отмечалось, производительность повышается и за счет подсчета промежуточных сумм иерархий и других агрегированных значений в момент построения куба. То есть, если изначально наши данные содержали информацию о дневной выручке по конкретному товару в отдельно взятом магазине, то при формировании куба OLAP-приложение считает итоговые суммы для разных уровней иерархий (недель и месяцев, городов и стран).
Конечно, за повышение таким способом производительности надо платить. Иногда говорят, что структура данных просто "взрывается" — куб OLAP может занимать в десятки и даже сотни раз больше места, чем исходные данные.
Что такое метки конкретного измерения? Привести примеры.
Могут ли меры в кубе OLAP, содержать нечисловые значения.