Основная таблица хранилища данных для Oracle8i Oracle Database Tips by Donald Burleson

 

Основным назначением хранилища данных является фиксация информации об относящихся к теме фактах. Эти факты обычно сохраняются во временном измерении.

Примеры обычно накапливаемых в хранилище фактов:

• Число продаваемых за день единиц продукции
• Средний цена продаж за месяц
• Количество произведенных за день предметов.

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

Таблица фактов создается для основных фактов и всех ключей, связанных с их классификацией. Из этой таблицы фактов мы видим, что основным фактом является number_of_units_sold. Кроме этого, мы видим другую ключевую информацию о продаже: дата, код магазина, в котором была сделана продажа, и число проданных SKU (stock keeping unit - номер хранения элемента). Сама по себе таблица фактов не дает нам так уж много полезной информации о продажах. Однако, если ее объединить с дополнительными атрибутами, создается возможность получить гораздо больше информации о продажах.

Давайте начинем это обсуждение с обзора основных методов классификации атрибутов.

Трактовка классификации атрибутов

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

Атрибут типа "выбери одно"

Вы используете атрибут "выбери одно" (сhoose-one attribute) в случае взаимо исключающих атрибутов, типа пола (мужской или женский), уровня образования, категории дохода и так далее. В большинстве случаев для представления этих атрибутов данных применяется столбец флажков и используются Oracle-ские средства проверки ограничений для контроля допустимых значений флажков. При проверке ограничений проверяется правильность входных столбцов в момент вставки строки. Например, вместо того, чтобы иметь приложение, проверяющее, что все экземпляры факта region имеют разрешенные значения Север, Юг, Восток или Запад, вы можете добавить к определению таблицы проверку ограничения, гарантирующего допустимость значений столбца region.

Атрибуты с конечными значениями

Такие (finite value attributes) атрибуты содержат конкретные значения, которые не могут быть охарактеризованы диапазоном значений, например, last_name (фамилия), street_address (название улицы) и phone_number (номер телефона).

Иерархические атрибуты

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

Практический пример классификации атрибутов

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

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

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

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

1. Иерархия атрибутов покупателей:

? Образование:

Среднее образование (колледж)

Степень бакалавра

Степень магистра

? Работа:

Профессионал

Информатика

2. Иерархия элементов списка товаров:

? Пищевые товары

Фрукты

Соки

Разлитые в бутылки соки

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

Например, для этой иерархии мы могли бы провести следующий анализ:

1. Перекрестные ссылки покупательских предпочтений при покупке фруктов с описанием работы покупателя
2. Перекрестные ссылки покупательских предпочтений при покупке соков и образования покупателя

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

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

В определение таблицы Oracle для таблицы фактов можно было бы включить следующие атрибуты заказчика:

...

CREATE TABLE FACT (
. . .
highest_education number(2)
CONSTRAINT highest_education_check
CHECK (highest_education between 0 and 20);
size_of_immediate_family number(2)
CONSTRAINT family_check
CHECK (size_of_immediate_family between 0 and 20);
number_of_children number(2)
CONSTRAINT children_check
CHECK (number_of_children between 0 and 20);
own_or_rent_home char(1)
CONSTRAINT own_check
CHECK (own_or_rent in 'U','O','R');
early_income_class number(1)
CONSTRAINT income_check
CHECK (yearly_income_class between 1 and 5);
 

Здесь мы использовали числовые сокращения, чтобы уменьшить размер таблицы фактов. В этом случае к таблице было бы добавлено 8 байтов, чтобы обеспечить быструю ссылку на информацию о типе заказчика, сделавшего транзакцию. Также заметим, что имеются условия для неизвестных значений для заказчика, как это имело бы место, если заказчик не пользовался VIP-картой ― "U" для неизвестных символьных значений и 0 для неизвестных числовых значений.

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

---

 

Проектирование иерархии атрибутов для хранилищ данных Oracle8i

(Hierarchical attribute design for an Oracle8i warehouse)

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

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

В нашем примере мы имеем следующие два метода для представления иерархии атрибутов продуктов:

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

   level_one char(1) CONSTRAINT level_one_check

   CHECK (level_one in (?F?,?N?);
   level_two char(1) CONSTRAINT level_two_check
   CHECK (level_two in (?A?,?V?,?P?,'t?);
   level_three char(1) CONSTRAINT level_three_check

   CHECK (level_three in (?U?,?B?,?C?);

   В нашем в высшей степени упрощенном примере, переменная level_one может принимать значения ?продовольственный? или ?непродовольственный?; переменная level_two может принимать значения ?животный?, ?растительный?, ?бумажный? или ?игрушка?; а переменная level_three может принимать значение ?U? для неклассифицированных (в тех случаях, когда уровень три классификации неприменим) объектов типа ?говядина?, или ?цыпленок?. Преимущество такого представления иерархии состоит в том, что при этом к каждой строке фактов прибавится только 3 байта. Обратная сторона медали состоит в том, что запросы являются загадочными для конечных пользователей. Конечные пользователи будут должны знать допустимые значения для каждого уровня классификации в их запросах. Например, чтобы сделать запрос о полном объеме продаж непродовольственных элементов заказчикам, которые зарабатывают более $ 50,000 в год, было бы необходимо, чтобы пользователь знал надлежащие значения флажка, как это показано ниже:

   SELECT sum(sale_amount) from FACT
   WHERE
   level_one = ?N?
   AND
   yearly_income_class > 3;

2. Присвойте описательное имя для каждого уровня в иерархии.

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

food_flag char(1)
CONSTRAINT food_check
CHECK (food_flag in (?Y?,?N?);
animal_or_vegetable_flag char(1)
CONSTRAINT animal_check
CHECK (animal_or_vegetable_flag in (?A?,?V?,?U?);
type_of_meat_flag char(1)
CONSTRAINT meat_check
CHECK (type_of_meat_flag in (?F?,?C?,?P?,?B?);
. . .

Преимущества этого подхода заключаются в том, что запросы могут быть сделаны очень наглядными. Например:

SELECT *
from
FACT
WHERE food_flag = ?N?
AND
yearly_income_class = 2;

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

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

Свертывание и итоги

Oracle8i предлагает множество инструментальных средств, которые помогают в иерархической классификации атрибутов таблицы фактов, но АБД должен принимать специальные планы для аккомодации этих иерархий.

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

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

Например, если мы назначаем отличающуюся внутреннюю категорию для каждого SKU, поисковая таблица могла бы выглядеть следующим образом:

CREATE TABLE CATEGORIES
(
category_name char(80),
category_number number
)

Теперь, когда мы классифицировали каждый товар в супермаркете уникальной категорией, определение дерева классификации могло бы быть примерно таким:

CREATE TABLE category_cross_reference
(
has_category number,
is_a_category number
)

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

На рис. D показано, на что могут быть похожи строки в этой таблице.

Рисунок D

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

SELECT
Has_categories
FROM
Category_cross_reference a,
Category_cross_reference b
WHERE
a.has_categories = b.is_a_category
AND
a.is_a_category = ?Fruit?;

Это - интересный SQL-запрос, потому что он соединяет таблицу саму с собой. Таким образом, мы можем ?обходить? рекурсивные отношения в обоих направлениях. Мы можем находить все категории, расположенные ниже указанной категории, или мы можем отобразить все элементы, расположенные выше указанной категории.

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

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

CREATE TABLE
Summary_juice_sales
AS
SELECT
Sum(sales)
FROM
Fact
WHERE
Sales.category in
(SELECT
Has_categories
FROM
Category_cross_reference a,
Category_cross_reference b
WHERE
a.has_categories = b.is_a_category
AND
a.is_a_category = ?Juice?
)
;

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

Использование таблиц итогов

В большинстве хранилищ данных Oracle8i для улучшения времена отклика подобные итоговые таблицы создаются предварительно. Секрет технологии хранилищ данных Oracle8i состоит в том, чтобы обеспечить конечных пользователей ?иллюзией? мгновенного времени отклика. Как бы быстр ни был Oracle, запросы к большому хранилищу информации, включающему миллионы строк, могут выполняться много часов.

Теперь мы создали дюжины, если не сотни, итоговых таблиц. Как же направить запросы конечных пользователей, чтобы они использовали эти возможности? Oracle8i обеспечивает два механизма для использования итоговых таблиц. Первый из них - это материализованные представления. В материализованном представлении синтаксический SQL-анализатор Oracle8i отыскивает любые ссылки на функции суммирования и автоматически переписывает запрос так, чтобы он использовал итоговую таблицу. Документация Oracle8i предлагает обширную информацию об этой методике. Другой популярный подход состоит в использовании переднего плана Oracle Express. При использовании Oracle Express можно идентифицировать итоговые таблицы и автоматически использовать их для запросов.

Заключение

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