Хранилища данных и средства бизнес-анализа в Oracle

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

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

Хранилище данных Хранилище данных (англ. Data Warehouse) предметно- ориентированная информационная база данных, специально разработанная и предназначенная для подготовки отчётов и бизнес-анализа с целью поддержки принятия решений в организации. Строится на базе систем управления базами данных и систем поддержки принятия решений. Данные, поступающие в хранилище данных, как правило, доступны только для чтения. Данные из OLTP-системы копируются в хранилище данных таким образом, чтобы построение отчётов и OLAP-анализ не использовал ресурсы транзакционной системы и не нарушал её стабильность. Как правило, данные загружаются в хранилище с определённой периодичностью, поэтому актуальность данных может несколько отставать от OLTP-системыангл.база данных систем управления базами данных систем поддержки принятия решенийOLTPOLAP

Подсистема анализа может быть построена на основе: подсистемы информационно-поискового анализа на базе реляционных СУБД и статических запросов с использованием языка SQL; подсистемы оперативного анализа. Для реализации таких подсистем применяется технология оперативной аналитической обработки данных OLAP, использующая концепцию многомерного представления данных; подсистемы интеллектуального анализа, реализующие методы и алгоритмы Data Mining.

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

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

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

Проблематика построения хранилищ данных Организация многоуровневых справочников метаданных. Конечным пользователям СППР необходимы метаданные, описывающие структуру хранящихся в ХД данных, а также инструменты их визуализации.

Витрины данных Сокращение затрат на проектирование и разработку ХД может быть достигнуто путем создания витрин данных (ВД). ВД - это упрощенный вариант ХД, содержащий только тематически объединенные данные.

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

OLAP (англ. online analytical processing, аналитическая обработка в реальном времени) англ.

OLAP Это технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу. Основоположник термина OLAP Эдгар Кодд, предложил в 1993 году «12 законов аналитической обработки в реальном времени».Эдгар Кодд 1993 году

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

Категории данных в ХД детальные данные - данные, переносимые непосредственно из OLTP- подсистем. Соответствуют элементарным событиям, фиксируемым в OLTP- системах. Подразделяются на: – измерения - наборы данных, необходимые для описания событий (товар, продавец, покупатель, магазин, … ); – факты - данные, отражающие сущность события (количество проданного товара, сумма продаж, …); агрегированные (обобщенные) данные - данные, получаемые на основании детальных путем суммирования по определенным измерениям; метаданные - данные о данных, содержащихся в ХД. Могут описывать: – объекты предметной области, информация о которых содержится в ХД; – категории пользователей, использующих данные в ХД; – места и способы хранения данных; – действия, выполняемые над данными; – время выполнения различных действий над данными; – причины выполнения различных действий над данными.

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

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

Иерархия измерений OLAP-кубов Каждое из измерений OLAP-куба может быть представлено в виде иерархической структуры. Например, измерение "Регион" может иметь следующие уровни иерархии: "страна - федеральный округ - область - город - район". Некоторые измерения могут иметь несколько уровней иерархического представления, например измерение "время" - представление "год - квартал - месяц - день" и представление "год - неделя - день". Точно так же в рамках измерения "География" можно ввести уровни "Страна", "Регион", "Область" и "Город".

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

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

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

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

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

Пример

Таблица фактов (FactInternetSales)

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

Таблицы измерений

Основные способы реализации многомерной модели MOLAP, ROLAP, HOLAP

MOLAP MOLAP (Multidimensional OLAP) - для реализации многомерной модели используются многомерные БД. При этом данные хранятся в виде упорядоченных многомерных массивов. Физически данные хранятся в "плоских" файлах, при этом куб представляется в виде одной плоской таблицы, в которую построчно вписываются все комбинации элементов всех измерений с соответствующими им значениями мер. Измерения Меры Магазин ВремяПоставщик ТоварЕдиницы товара Стоимость товара Иванов Картофель Иванов Морковь Иванов Картофель Петров Морковь 20025

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

Недостатки MOLAP за счет денормализации и предварительно выполненной агрегации объем данных в многомерной БД, как правило, соответствует (по оценке Кодда) в 2, раз меньшему объему исходных детализированных данных; в подавляющем большинстве случаев информационный гиперкуб является сильно разреженным, а поскольку данные хранятся в упорядоченном виде, неопределенные значения удается удалить только за счет выбора оптимального порядка сортировки, позволяющего организовать данные в максимально большие непрерывные группы. Кроме того, оптимальный с точки зрения хранения разреженных данных порядок сортировки, скорее всего, не будет совпадать с порядком, который чаще всего используется в запросах. Поэтому в реальных системах приходится искать компромисс между быстродействием и избыточностью дискового пространства, занятого базой данных; многомерные БД чувствительны к изменениям в многомерной модели. Например, при добавлении нового измерения приходится изменять структуру всей БД, что влечет за собой большие затраты времени.

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

ROLAP ROLAP (Relational OLAP) - для реализации многомерной модели используются реляционные БД. В настоящее время распространены две основные схемы реализации многомерного представления данных с помощью реляционных таблиц: схема "звезда" и схема "снежинка«. Если каждое измерение содержится в одной таблице, такая схема хранилища данных носит название "звезда" (star schema). Если же хотя бы одно измерение содержится в нескольких связанных таблицах, такая схема хранилища данных носит название "снежинка" (snowflake schema). Дополнительные таблицы измерений в такой схеме, обычно соответствующие верхним уровням иерархии измерения и находящиеся в соотношении "один ко многим" в главной таблице измерений, соответствующей нижнему уровню иерархии, иногда называют консольными таблицами (outrigger table).

Пример схемы данных "звезда"

Пример схемы данных "снежинка"

Использование реляционных БД имеет следующие достоинства в большинстве случаев корпоративные ХД реализуются средствами реляционных СУБД, и инструменты ROLAP позволяют производить анализ непосредственно над ними. При этом размер хранилища не является таким критичным параметром, как в случае MOLAP; в случае переменной размерности задачи, когда изменения в структуру измерений приходится вносить достаточно часто, ROLAP-системы с динамическим представлением размерности являются оптимальным решением, т. к. в них такие модификации не требуют физической реорганизации БД; реляционные СУБД обеспечивают значительно более высокий уровень защиты данных и хорошие возможности разграничения прав доступа.

Недостатки ROLAP Главный недостаток ROLAP по сравнению с многомерными СУБД - меньшая производительность. Для обеспечения производительности, сравнимой с MOLAP, реляционные системы требуют тщательной проработки схемы базы данных и настройки индексов. Только при использовании схем типа "звезда" производительность хорошо настроенных реляционных систем может быть приближена к производительности систем на основе многомерных баз данных.

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

Аналитические и статистические функции В Oracle они могут быть следующих видов: функции ранжирования; статистические функции для плавающего интервала; функции подсчета долей; статистические функции LAG/LEAD с запаздывающим/опережающим аргументом; статистические функции (линейная регрессия и т. д.).

Функции ранжирования "Раздать сотрудникам места по мере убывания или возрастания их зарплат": SELECT ename, sal, ROW_NUMBER ( ) OVER ( ORDER BY sal DESC ) AS row_number_desc, ROW_NUMBER ( ) OVER ( ORDER BY sal ) AS row_number_asc, RANK ( ) OVER ( ORDER BY sal ) AS rank, DENSE_RANK ( ) OVER ( ORDER BY sal ) AS dense_rank FROM emp ;

Результат

Статистические функции для плавающего интервала 'Растущий итог' выплат на зарплату по мере приема сотрудников на работу SELECT ename, sal, SUM ( sal ) OVER ( ORDER BY hiredate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_over_range FROM emp ;

Результат

Функции подсчета долей "Доли зарплаты сотрудников в общей сумме зарплат« SELECT ename, sal, RATIO_TO_REPORT ( sal ) OVER ( ) AS ratio_to_report FROM emp ;

Результат

Статистические функции LAG/LEAD с запаздывающим/опережающим аргументом «Изменение зарплаты сотрудника по отношению к предшественнику по мере приема на работу» SELECT ename, sal, sal - LAG ( sal, 1 ) OVER ( ORDER BY hiredate ) delta FROM emp ;

Результат

Статистические функции «Три из имеющихся видов регрессии для оценки взаимозависимости значений в столбцах» SELECT REGR_SLOPE ( sal, comm ) AS slope, REGR_AVGX ( sal, comm ) AS avgsal, REGR_AVGY ( sal, comm ) AS avgcomm FROM emp

Результат

Разворачивание данных в столбцы указанием PIVOT Сочетание SELECT … FROM … PIVOT … позволяет развернуть данные одного столбца в отдельные столбцы конечного результата.

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