Правила агрегации в OBI

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

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

Итак, случай первый. Пару месяцев назад я делал демонстрационный пример для одной компании: мне нужно было настроить в OBI модель с замерами различных показателей нефтяных скважин (дебет жидкости, обводненность и т.д.) Я построил на логическом уровне таблицу фактов MEASUREMENTS c одним показателем Qp (дебет жидкости), образмерил её таблицами DM_TIME и DM_WELL (скважины). Затем я наполнил таблицы тестовыми данными:

Замеры по одной и той же скважине за разные дни должны усредняться. Замеры, проведенные в один и тот же день для разных скважин, должны суммироваться. Для настройки правил агрегации я открываю в BI Administration Tool свойства логической колонки Qp, на вкладке Aggregation устанавливаю галочку Based on dimensions, и устанавливаю правило агрегации AVG для измерения DM_WELLDim, и SUM для DM_TIMEDim.

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

Здесь итоги по дням (значения в нижей строчке) считаются верно. Также правильно посчитан общий итог (правая нижняя ячейка): 374 = AVG(250+230+240) + AVG(130+138)=240+134=374. А вот итог по скважинам (правый столбец) посчитан неправильно - выводится сумма вместо среднего. В других подобных экспериментах я получал подобные некорректные результаты, вплоть до ошибки [nQSError: 46036] Internal Assertion.

Для решения этой проблемы можно использовать следующий обходной маневр. Дело в том, что BI Server пускает один SQL-запрос к базе данных, которым получаются как детальные значения отчета, так и вычисляются итоги, и именно этот запрос генерируется неправильно. Можно в Administration Tool открыть на физическом уровне свойства базы данных, и снять опцию SUBTOTALLING_SUPPORTED. И тогда для вычисления итогов BI Server будет пускать к базе данных отдельный запрос, что легко проверить посмотрев в NQQuery.log.

В итоге после отключения опции мы получаем верный отчет.

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

create table TRANSACTIONS
(
DOCUMENT_IS INTEGER,
ACCOUNT_ID_FROM INTEGER,
ACCOUNT_ID_TO INTEGER,
AMOUNT INTEGER
);

Рассмотрим следующие детальные данные:

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

Для удовлетворения описанных требований поступим следующим образом. Для логической колонки AMOUNT установим правило агрегации SUM - это будет наша “Сумма по транзакциям”. Затем продублируем колонку AMOUNT (Right Click -> Duplicate), назовем новую колонку AMOUNT_BY_DOCUMENT, и для неё установим правило агрегации как MIN по всем измерениям, за исключением SUM по документам:

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

PS Все описанное проверялось на OBI 10.1.3.3.1 и Oracle Database 10.2.0.1

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

• Агрегированные таблицы в Oracle BI Suite
• Кэширование в OBI
• Write Back

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

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

[AD]