CC BY
147
УДК 681.3
А. П. Кирпичников, А. Л. Осипова, И. С. Ризаев
ПОВЫШЕНИЕ АНАЛИТИЧЕСКИХ ВОЗМОЖНОСТЕЙ БАЗ ДАННЫХ
Ключевые слова: база данных, хранилище данных, язык запросов, SQL, OLAP.
Рассматриваются меры повышения аналитических возможностей баз и хранилищ данных. Показано, что для повышения функциональных возможностей целесообразно строить системы с применением технологий OLAP и Data Mining.
Keywords: a database, storehouse of the data, language of inquiries, SQL, OLAP.
Measures of increase of analytical possibilities of bases and storehouses of the data are considered. It is shown that for increase of functionality it is expedient to build systems with application of technologies OLAP and Data Mining.
Введение
Важным фактором в современных рыночных условиях является оперативное принятие деловых решений. Однако многие предприятия сталкиваются с таким препятствием, как большой объем и высокая сложность данных. Решением данной проблемы может стать создание системы поддержки принятия решений (СППР) на основе хранилищ данных. СППР - это системы, обладающие средствами ввода, хранения и анализа данных, с целью принятия решений. Существующие системы управления базами данных, ориентированные на реляционные модели данных, обладают рядом недостатков. Практика использования реляционных баз данных показала неэффективность их применения для полноценного анализа информации.
Язык запросов “SQL”, широко применяемый в базах данных [1], не подходит для статистического анализа, для анализа динамики изменения данных во времени. Для расширения функциональных возможностей: статистического анализа, извлечения информации из архивных данных, принятия решений и др. целесообразно строить системы с применением OLAP технологий и Data Mining. Такая технология основана на использовании концепции хранилищ данных [2].
Переход к концепции хранилищ данных
Хранилище данных “data warehouse” (ХД), предназначено для интеграции данных из различных источников, очистки, трансформации и подготовки данных для оперативной аналитической обработки (рис.1).
Рис. 1 -Аналитические процессы на основании хранилищ данных
Данные ХД могут быть не нормализованы и представлены в виде многомерной модели. Такая модель может позволить упростить использование данных и ускорить выполнение запросов. Это значит, что ХД должны содержать агрегированные данные. Обычно многомерные модели представляют в виде куба с многими измерениями (рис.2).
Формальное описание многомерных моделей данных
Перейдем к формальному описанию используемой многомерной модели данных. Основными понятиями данной модели являются: гиперкуб данных (Data Hypercube), измерение (Dimension), метки (Members), ячейка (Cell), мера (Measure). Гиперкуб данных содержит одно или более измерений и представляет собой упорядоченный набор ячеек. Ячейка может быть пустой (не содержать данных) или содержать значение показателя - меру [3].
Для получения доступа к данным пользователю необходимо указать одну или несколько ячеек путем выбора значений измерений, которым соответствуют необходимые ячейки. Процесс выбора значений измерений будем называть фиксацией меток, а множества выбранных значений измерений - множеством фиксированных меток.
Пусть D = {di, d2, • • •, dn } - множество измерений гиперкуба.
Mdt = {mi ,m2. ,•••,mfr. }, i = 1,•••,n - множество меток измерения d., где k. - количество меток измерения
di.
M = Md1 uMd2 u...uMdn - множество меток гиперкуба,
D с D - множество фиксированных измерений,
Me M - множество фиксированных меток.
Гиперкуб данных обозначим как множество ячеек H {D, M), соответствующее множествам D, M. Подмножество гиперкуба данных, соответствующее множествам фиксированных значений D , M будем обозначать, как H {р , M ). Каждой ячейке гиперкуба
данных h е H соответствует единственно возможный (Таблица фактов окружена таблицами измерений:
набор меток измерений Mh с M . Time - время, Branch - отделение, Item - т°вар,
Customer - клиент)
Рис. 2 - Гиперкуб данных
Множество мер гиперкуба H (d, m ) обозначим, как V(H). Над гиперкубом данных можно производить операции манипулирования данными: операцию "среза", "вращения", "свертки и детализации", а также получение агрегированных значений.
Способы реализации многомерных моделей данных
Выделяют три основных способа реализации многомерных моделей: MOLAP, ROLAP, HOLAP. В настоящее время более широко используется модель ROLAP, которая реализуется или по схеме «звезда», или по схеме «снежинка». Схема «звезда» представляет модель, в центре которой находится таблица фактов (с детальными данными), окруженная справочными данными, помещенными в таблицы размерностей. Основными составляющими схемы «звезда» являются денормализованная таблица фактов (Table Fact) и множество таблиц измерений (Dimension Tables).
На рис.3 представлена схема «звезда» для данных, связанных с продажей товаров.
Time
Branch
Time key
Day
Month
Year
Fact Table
Item
Item_key
Item name
Type
Supplier
Time key
Item_key
Branch_key
Customer key Quantity key
Money Sold
Branch key
Branch name
Branch type
Customer
Customer key City
Country
Рис. 3 - Схема «Звезда» для данных, связанных с продажей товаров
С помощью данной модели можно вычислить объем продаж и прибыли (sales-amount, price_quantity), используя таблицы измерений, которые содержат статистические данные.
Таблица фактов содержит сведения об объектах или событиях, совокупность которых предназначена для анализа. На основании числовых полей, содержащихся в такой таблице, получают агрегированные данные. Таблицы фактов могут быть чрезвычайно большими, что может вызвать проблемы, связанные с сопровождением, копированием и другими служебными операциями. Таблицы измерений обычно занимают меньший объем памяти, поскольку они существенно меньше таблицы фактов. Они содержат неизменяемые или редко изменяемые данные. Скорость роста таблицы измерений незначительна по сравнению со скоростью роста таблицы фактов.
Примерами являются клиенты или продукты, часто состоящие из одного или нескольких иерархий. Иерархии представляют логические структуры, которые используются в качестве средств организации данных. Иерархия может быть использована для определения группировки данных. Например, в таблице TIME иерархия представляет собой группировки данных на уровне месяца (MONTH), на уровне квартала (QUARTER) и на уровне года (YEAR). Таблицы измерений содержат неизменяемые или редко изменяемые данные.
Одним из вариантов схемы «звезда» является схема «снежинка», в котором каждое измерение может иметь свои собственные таблицы измерений. В этом случае отдельные таблицы фактов создаются для возможных сочетаний уровней обобщения различных измерений. Таблицы измерений в схеме «снежинка» могут находиться в нормализованном виде.
Применение схемы «снежинка» может позволить получить более высокую производительность и экономию пространства. Но за счет более сложной структуры может привести к снижению эффективности обработки данных.
Пусть имеется таблица фактов Sales (Продажи).
Sales(product_id,time_id, channel_id, cudtomer_id, sales_amount, price_quantity)
Передача данных в ХД требует выполнения процессов “extract, transform, load”. Это вызывает ряд проблем, какие данные из оперативных источников загрузить в ХД, определить время копирования данных из источников, как проводить интеграцию данных из многих неоднородных источников, определить данные для группирования, решить проблему безопасности и др.
В системе баз данных (хранилищ данных) для повышения эффективности обработки данных используется процедура, называемая материализованным представлением.
Материализованное представление как способ повышения эффективности обработки данных
Материализованное представление
(MATERIALIZED VIEWS) - это тип представления, которое сохраняет строки данных, полученные в результате выполнения SQL - запроса. Обычно применяются для предварительного вычисления и хранения агрегированных данных, такие как сумма или средние значения. Применение материализованных представлений может резко повысить эффективность запросов, а также значительно уменьшить нагрузку на систему. Это объясняется тем, что в этом случае потребуется меньшее количество логических чтений для удовлетворения запросов, чем тот же запрос работает в среде базовых таблиц [4].
При использовании же хранилищ данных, информация является стабильной и неизменной во времени при выполнении запроса. Группирование значений производится операторами CUBE и ROLLUP. При хранении сгруппированных (суммарных) данных в таблицах время ответа на запросы будет уменьшено.
В язык SQL введены специальные операторы для группирования и анализа данных в ХД. Это CUBE и ROLLUP, как расширение оператора GROUP BY.
Приведем пример анализа данных, позволяющего найти количество продаж для двух отделений ('Direct Sales', 'Internet'), осуществленных в сентябре 2006 в отношении SALES. При этом эти отделения могут находиться в разных странах.
SELECT SUM(price_quantity) SALES$
FROM sales, customers, times, branches WHERE sales.time_key=times.time_key AND sales.customer_key=customers.customer_key AND customers.city_key=countries. Countries.city_key AND sales.branch_key= branchs.branch_key AND branchs.branch_name IN ('Direct Sales', 'Internet') AND times.month = 9 AND times.year= 2006 AND customer.country IN ('UK', 'US');
Для организации подобного запроса потребуется 4 операции соединения “JOIN”. В случае применения больших таблиц понадобиться значительное время выполнения запроса. Кроме того, надо иметь в виду, что таблицы могут обновляться во время выполнения запроса, что означает, что ответ не будет верным на все 100 процентов.
Если мы правильно используем материализованное представление, то запрос будет иметь следующий вид:
SELECT SUM(price_quantity) SALES$
FROM view_sales
WHERE branch_name IN ('Direct Sales', 'Internet') AND month = 9 AND year= 2006 AND country IN ('UK', 'US');
В этом случаи все операции соединения исчезают и остаются только логические операции.
Для создания материализованных представлений нужно определить те столбцы, которые всегда участвуют в запросах. И для нашего примера можно написать
create materialized view mview_1 as
select country, branch_name , month, year,
sum(sales_amount) camount , sum(price_quantity) squantity
from sales,times,customers,countries , branchs where sales.time_key = times.time_key and
sales.customer_key = customers. customer_key and customers.city_key = countries.city_key and
sales.branch__key = branchs. branch _key
group by cube country, branch_name , month, year);
Результаты таких исследований представлены в таблице 1 и на рисунке 4.
Таблица 1 - Сравнение выборки данных в ХД
А В С
1016271 2 0.0..1
2032542 5 0.0..1
3047791 7 0.0..1
4063040 8 0.0..1
5078289 11 0.0..1
6093538 12 0.0..1
7108787 13 0.0..1
9139285 17 0.0..1
А - количество записей в хранилище данных;
В - время(сек.) в ХД без материализованных представлений;
С - время (сек.) в ХД с материализованным представлением.
На рис. 4 видно, что без использования материализованных представлений время обработки значительно увеличивается с увеличением числа строк, и практически остается неизменным (меньше 1 сек.) при использовании материализованных представлений.
Рис. 4 - График зависимости времени обработки запроса (в сек.) от числа строк
Заключение
Показано, что в случае обработки больших объемов информации целесообразен переход от традиционных баз данных к концепции хранилищ данных с использованием технологий OLAP и Data Mining.
Дается формализованное представление многомерных моделей данных, структура которых может быть реализована по схеме «звезда» или «снежинка».
В системе баз данных (хранилищ данных) для повышения эффективности обработки данных используется процедура, называемая материализованным представлением. Применение таких процедур может позволить значительно повысить эффектив-
ность обработки данных, что и получено экспериментальным путем.
Результаты экспериментальных исследований проведены в среде СУБД Oracle и представлены в виде таблицы и графика.
Литература
1. Ризаев И.С., Яхина З.Т. Базы данных//Учебное пособие, Изд-во Казан.гос.техн.ун-та, 2008. 240 с.
2. Inmon W.N. Building the Data Warehouse// Third Edition Published by John Wiley & Sons, Inc. 2002. 428 c.
3. RALF Kimball, Margy Ross. The Data Warehouse Toolkit// Second Edition Published by Wiley & Sons Inc, 2002. 446 c.
4. Ризаев И.С., Рахал Я. Интеллектуальный анализ данных// Казань. Изд-во МОиН. 2011. 172 с.
© А. П. Кирпичников - д-р физ.-мат. наук, проф., зав. каф. интеллектуальных систем и управления информационными ресурсами КНИТУ, [email protected]; А. Л. Осипова - ст. препод. каф. автоматизированных систем обработки информации и управления КНИТУ-КАИ; И. С. Ризаев - канд. техн. наук, проф. той же кафедры, [email protected]; [email protected].
