Агрегированные таблицы в Oracle BI Suite

Автор: Егор Демьянов (все статьи)

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

Рассмотрим использование агрегированных таблиц в Oracle BI Suite EE. В качестве примера взята схема SH из Oracle Database, в которой хранятся продажи некоторого гипотетического магазина. На базе этой схемы я настроил репозиторий, с использованием которого можно повторить описанный далее пример.

Физическая модель имеет форму снежинки:

Логическая модель еще проще:

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

• TIMES – Year(5)/Quarter(20)/Month(60)/Day(1826);
• CUSTOMERS – Region(6)/Country(23)/City(620)/Customer(55500);
• CHANNELS – Total(1)/Class(3)/Channel(5);
• PRODUCTS – Category(5)/Product(72).

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

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

create table times_a
as
select distinct
end_of_cal_month calendar_month_id,
calendar_month_desc,
calendar_quarter_desc,
calendar_year
from times;

В таблице PRODUCTS хранятся записи о конкретных товарах, нам же требуется таблица с записями о категориях:

create table products_a
as
select distinct
prod_category,
prod_category_id
from products;

Данные о странах уже лежат в отдельной таблице COUNTRIES. Наконец создадим агрегированную таблицу фактов:

create table sales_a
as
select
P.prod_category_id,
C.country_id,
T.calendar_month_id,
channel_id,
sum(quantity_sold) quantity_sold,
sum(amount_sold) amount_sold
from sales S
join customers C on (S.cust_id=C.cust_id)
join times T on (S.time_id=T.time_id)
join products P on (S.prod_Id=P.prod_id)
group by prod_category_id, c.country_id, t.calendar_month_id, channel_id;

Теперь импортируем созданные таблицы и настраиваем внешние ключи на физическом уровне, чтобы получить следующую модель:

Переходим к настройке логической модели. Перетаскиваем физическую таблицу PRODUCTS_A на папку Sources для логической таблицы PRODUCTS.

Затем открываем свойства источника PRODUCTS_A и на вкладке Content указываем, что данные из этого источника соответствуют уровню иерархии Category:

Проделываем то же самое с физической таблицей TIMES_A и логической таблицей TIMES, указав уровень Month. Затем, выполняем аналогичную операцию с физической таблицей SALES_A и логической таблицей SALES, также указав соответствующие уровни иерархий:

Если все сделано правильно, и Consistency Check Manager не выдает ошибок и предупреждений, то посмотрим, как используются наши агрегаты. Заходим в Oracle Answers под пользователем, для которого включено логирование на уровне=2 и выполняем следующий запрос:

Теперь открываем NQQuery.log, и видим, что созданному Answer соответствует такой sql-запрос:

select T2470.PROD_CATEGORY as c1,
T708.COUNTRY_REGION as c2,
sum(T2500.AMOUNT_SOLD) as c3
from
PRODUCTS_A T2470,
COUNTRIES T708,
SALES_A T2500
where ( T708.COUNTRY_ID = T2500.COUNTRY_ID and T2470.PROD_CATEGORY_ID = T2500.PROD_CATEGORY_ID )
group by T708.COUNTRY_REGION, T2470.PROD_CATEGORY
order by c1, c2

Вуаля! Данные читаются не из детальной таблицы фактов SALES, а из агрегата SALES_A.

В этом примере мы создали один агрегат, но подобным образом можно сделать несколько агрегатов, для разных комбинаций уровней иерархий. При наличии нескольких агрегатов может возникнуть ситуация, когда Oracle BI Server для обработки запроса сможет использовать любой из нескольких подходящих агрегатов, и должен будет выбрать какой-то один. Предположим, что в нашем примере мы создали еще один агрегат SALES_A2 для уровней TIMES(Year), CUSTOMERS(City), CHANNELS(Total), PRODUCT(Category). Для получения объема продаж по годам и регионам BI Server может использовать как SALES_A, так и SALES_A2. Стоимость использования того или иного источника оценивается с использованием количества элементов на уровнях иерархий.

Потенциальное количество строк для получения требуемого результата из SALES_A оценивается как (60 месяцев * 23 страны * 5 каналов * 5 категорий) = 34500. Для SALES_A2 потенциальное количество строк оценивается (5 лет * 620 городов * 5 категорий)=15500. Таким образом BI Server решает, что выгоднее использовать SALES_A2.В принципе, не произойдет ничего фатального, если не указывать количество элементов для уровней иерархий. Вопрос только в том, насколько эффективно будут использоваться агрегаты. Для автоматической оценки количества элементов можно использовать команду Estimate Levels в контекстном меню для измерений.

По этой теме также можно почитать:

• Правила агрегации в OBI
• Кэширование в OBI
• Write Back

Для удобства отслеживания новых публикаций на сайте рекомендую подписаться на рассылку или подписаться на канал RSS.

Если вы нашли в сети интересные ссылки на ресурсы по технологиям хранилищ данных, OLAP, CRM или data mining, и хотите поделиться ими с другими, присылайте их. Я с удовольствием размещу их на этом сайте.

[AD]