logo

12 июн. 2011 г.

BIEE 11g: writeback + ввод плановых показателей

Сегодня я расскажу о своем опыте использования механизма обратной записи – writeback – в OracleBI 11g.
Представим, что вам нужно реализовать в OBIEE возможность ввода плановых показателей – сумма и количество проданного товара – еженедельно на неделю вперед. Причем товарный справочник у вас немаленький, где-то нужно вводить планы по каждой конкретной товарной позиции, где-то достаточно задать плановое значение на категорию/подкатегорию товара, а уже это значение разобьется на показатели потоварно (пропорционально стоимости товарной позиции).
Далее я расскажу как добиться подобного на примере схемы SH.

За основу возьмем репозиторий SH под OracleBI 11g.
Создадим 3 таблицы с плановыми показателями – каждая на основании таблицы SH.SALES.
Первая таблица будет содержать плановые показатели в разрезе категорий товара, вторая – в разрезе подкатегорий товара, третья – в разрезе товарных позиций.
Для простоты примера будем считать, что плановые показатели нам нужны лишь в разрезах календаря и товарного справочника.
Создадим таблицу категорий товара и таблицу плановых показателей в разрезе календаря и категорий товара.

create table PROD_CATEGORIES as
select t.prod_category_id, t.prod_category, t.prod_category_desc
from PRODUCTS t
group by t.prod_category_id, t.prod_category, t.prod_category_desc;
alter table PROD_CATEGORIES add primary key (prod_category_id);

create table PLAN_SALES_PROD_CAT
(
prod_category_id NUMBER not null,
time_id DATE not null,
quantity_sold NUMBER(10,2) not null,
amount_sold NUMBER(10,2) not null
);
alter table PLAN_SALES_PROD_CAT
add constraint PLAN_SALES_PROD_CAT_PROD_FK foreign key (PROD_CATEGORY_ID)
references PROD_CATEGORIES (PROD_CATEGORY_ID);
alter table PLAN_SALES_PROD_CAT
add constraint PLAN_SALES_PROD_CAT_TIME_FK foreign key (TIME_ID)
references TIMES (TIME_ID);

create index PLAN_SALES_PROD_CAT_PROD_NIX on PLAN_SALES_PROD_CAT (PROD_CATEGORY_ID);
create index PLAN_SALES_PROD_CAT_TIME_NIX on PLAN_SALES_PROD_CAT (TIME_ID);


Создадим таблицу подкатегорий товара и таблицу плановых показателей в разрезе календаря и подкатегорий товара.

create table PROD_SUBCATEGORIES as
select t.prod_category_id, t.prod_category, t.prod_category_desc,
t.prod_subcategory_id, t.prod_subcategory, t.prod_subcategory_desc
from PRODUCTS t
group by t.prod_category_id, t.prod_category, t.prod_category_desc,
t.prod_subcategory_id, t.prod_subcategory, t.prod_subcategory_desc;
alter table PROD_SUBCATEGORIES add primary key (prod_subcategory_id);

create table PLAN_SALES_PROD_SUBCAT
(
prod_subcategory_id NUMBER not null,
time_id DATE not null,
quantity_sold NUMBER(10,2) not null,
amount_sold NUMBER(10,2) not null
);
alter table PLAN_SALES_PROD_SUBCAT
add constraint PLAN_SALES_PROD_SUBCAT_PROD_FK foreign key (PROD_SUBCATEGORY_ID)
references PROD_SUBCATEGORIES (PROD_SUBCATEGORY_ID);
alter table PLAN_SALES_PROD_SUBCAT
add constraint PLAN_SALES_PROD_SUBCAT_TIME_FK foreign key (TIME_ID)
references TIMES (TIME_ID);

create index PLN_SALES_PROD_SUBCAT_PROD_NIX on PLAN_SALES_PROD_SUBCAT (PROD_SUBCATEGORY_ID);
create index PLN_SALES_PROD_SUBCAT_TIME_NIX on PLAN_SALES_PROD_SUBCAT (TIME_ID);


Создадим таблицу плановых показателей в разрезе календаря и товарных позиций.

create table PLAN_SALES_PROD
(
prod_id NUMBER not null,
time_id DATE not null,
quantity_sold NUMBER(10,2) not null,
amount_sold NUMBER(10,2) not null
);
alter table PLAN_SALES_PROD
add constraint PLAN_SALES_PROD_PROD_FK foreign key (PROD_ID)
references PRODUCTS (PROD_ID);
alter table PLAN_SALES_PROD
add constraint PLAN_SALES_PROD_TIME_FK foreign key (TIME_ID)
references TIMES (TIME_ID);

create index PLN_SALES_PROD_PROD_NIX on PLAN_SALES_PROD (PROD_ID);
create index PLN_SALES_PROD_TIME_NIX on PLAN_SALES_PROD (TIME_ID);


Импортируем эти таблицы в наш репозиторий.

С помощью созданных таблиц мы будем ОТОБРАЖАТЬ в BI плановые показатели. Но чтобы позволить пользователям ЗАДАВАТЬ данные для плановых показателей – создадим еще одну таблицу на физ. слое репозитория

В отличие от таблиц просмотра плановых показателей, которые связаны с таблицам измерений строго через внешние ключи

Таблицу ввода показателей мы свяжем с измерениями через кросс-джойн:


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


Теперь настроим бизнес слой репозитория.
Рассмотрим логическую таблицу Products



Аналогично настроим таблицу фактов – Plan Salesfacts


Для таблицы фактов Input Table зададим свойство ее единственного столбца – Dummy Input – writeable.
Также не забудем задать тип агрегации – сумма – для всех наших столбцов-показателей: как для просмотра, так и для ввода.


Дополним презентационный слой.


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


Напоследок создадим новый экземпляр ConnectionPool’а, который будет применяться только для writeback’а. Назовем его WriteBack_Connection_Pool.
На этом настройка репозитория завершена.

Укажем, что все пользователи могут использовать writeback:
В меню BI – Administration - Manage Privileges - Write Back


Создадим XML шаблон, определяющий insert/update введенных пользователями показателей в таблицы БД. Так как у нас используется кросс-джойн между всеми таблицами измерений и таблицей ввода показателей (по сути – dual-таблица с одной строкой), то на любую комбинацию измерений мы получим строку во writeback-запросе, а это значит, что нам потребуется создать update-инструкцию. Также следует учесть, что данных в настоящей таблице с плановыми показателями может и не быть, поэтому в update-инструкции следует применять оператор MERGE.
На практике мне пришлось указать и insert-конструкцию, думаю это баг.

Итак, создадим в папке
Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb\customMessages
Файл MyWritebackTest.xml со следующим содержимым:

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
<WebMessage name="SetPlanSalesProdCat">
<XML>
<writeBack connectionPool="WriteBack_Connection_Pool">
<insert>MERGE INTO PLAN_SALES_PROD_CAT dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select pc.prod_category_id from PROD_CATEGORIES pc where pc.prod_category = '@{c_PROD_CAT}' ) AS prod_category_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_category_id = dst.prod_category_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_category_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_category_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </insert>
<update>MERGE INTO PLAN_SALES_PROD_CAT dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select pc.prod_category_id from PROD_CATEGORIES pc where pc.prod_category = '@{c_PROD_CAT}' ) AS prod_category_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_category_id = dst.prod_category_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_category_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_category_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name="SetPlanSalesProdSubcat">
<XML>
<writeBack connectionPool="WriteBack_Connection_Pool">
<insert>MERGE INTO PLAN_SALES_PROD_SUBCAT dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select pc.prod_subcategory_id from PROD_SUBCATEGORIES pc where pc.prod_subcategory = '@{c_PROD_SUBCAT}' and pc.prod_category = '@{c_PROD_CAT}' ) AS prod_subcategory_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_subcategory_id = dst.prod_subcategory_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_subcategory_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_subcategory_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </insert>
<update>MERGE INTO PLAN_SALES_PROD_SUBCAT dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select pc.prod_subcategory_id from PROD_SUBCATEGORIES pc where pc.prod_subcategory = '@{c_PROD_SUBCAT}' and pc.prod_category = '@{c_PROD_CAT}' ) AS prod_subcategory_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_subcategory_id = dst.prod_subcategory_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_subcategory_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_subcategory_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name="SetPlanSalesProdDtl">
<XML>
<writeBack connectionPool="WriteBack_Connection_Pool">
<insert>MERGE INTO PLAN_SALES_PROD dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select p.prod_id from PRODUCTS p where p.prod_name = '@{c_PROD_NAME}' and p.prod_subcategory = '@{c_PROD_SUBCAT}' and p.prod_category = '@{c_PROD_CAT}' ) AS prod_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_id = dst.prod_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </insert>
<update>MERGE INTO PLAN_SALES_PROD dst
USING (SELECT ( select min(t.time_id) from TIMES t where t.calendar_month_desc = '@{c_MONTH_DESC}' ) AS time_id,
( select p.prod_id from PRODUCTS p where p.prod_name = '@{c_PROD_NAME}' and p.prod_subcategory = '@{c_PROD_SUBCAT}' and p.prod_category = '@{c_PROD_CAT}' ) AS prod_id
FROM DUAL) src
ON ( src.time_id = dst.time_id AND src.prod_id = dst.prod_id )
WHEN MATCHED THEN
UPDATE SET dst.quantity_sold = @{c_QNT_SOLD},
dst.amount_sold = @{c_AMOUNT_SOLD}
WHEN NOT MATCHED THEN
INSERT
( time_id, prod_id, quantity_sold, amount_sold )
VALUES
( src.time_id, src.prod_id, @{c_QNT_SOLD}, @{c_AMOUNT_SOLD} ) </update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>


Рестартуем Presentation Server.

Далее создадим ансвер, который позволит вводить плановые показатели в разрезе товарных позиций:
Включим в ансвер столбцы месяца календаря, категории, подкатегории и наименования товара, а также столбцы плановых показателей (сначала следует вывести столбец Dummy Input, поскольку именно у него задано свойство Writeable на логическом уровне репозитория; указать в свойствах столбца в ансвере, что он является writeable, а затем следует изменить его формулу – ifnull("Plan Salesfacts"."Dummy Input", 0)+"Plan Salesfacts"."Amount Sold" – тем самым мы добъемся одновременного отображения существующих данных в плановых таблицах с возможностью получить в ансвере набор всех возможных пересечений таблиц-измерений ).
Добавим фильтры по месяцу, по категории и подкатегории.


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


Наконец перейдем на вкладку «Advanced». Где зададим свойства columnID для каждого столбца ансвера в соответствии с нашим XML шаблоном обратной записи.

Не забудьте нажать кнопку «Apply XML» после внесения изменений.
У меня получился вот такой XML-код данного ансвера:

<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201008230" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
<saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""SH"" withinHierarchy="true">
<saw:columns>
<saw:column xsi:type="saw:regularColumn" columnID="c_MONTH_DESC">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Calendar"."Calendar Month Desc"</sawx:expr></saw:columnFormula>
<saw:displayFormat>
<saw:formatSpec suppress="suppress" wrapText="true" interaction="none"/></saw:displayFormat>
<saw:columnHeading>
<saw:displayFormat>
<saw:formatSpec interaction="none"/></saw:displayFormat></saw:columnHeading></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c_PROD_CAT">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Prod Category"</sawx:expr></saw:columnFormula></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c_PROD_SUBCAT">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Prod Subcategory"</sawx:expr></saw:columnFormula></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c_PROD_NAME">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Prod Name"</sawx:expr></saw:columnFormula></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c_AMOUNT_SOLD">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">ifnull("Plan Salesfacts"."Dummy Input", 0) + "Plan Salesfacts"."Amount Sold"</sawx:expr></saw:columnFormula>
<saw:displayFormat>
<saw:formatSpec suppress="repeat" wrapText="true">
<sawwb:writeBack xmlns:sawwb="com.siebel.analytics.web/writeback/v1" required="false" xsi:type="sawwb:editBox" inputSize="10" inputHeight="1"/></saw:formatSpec></saw:displayFormat>
<saw:tableHeading>
<saw:caption fmt="text">
<saw:text>Plan Salesfacts</saw:text></saw:caption></saw:tableHeading>
<saw:columnHeading>
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat>
<saw:caption fmt="text">
<saw:text>Amount Sold</saw:text></saw:caption></saw:columnHeading></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c_QNT_SOLD">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">ifnull("Plan Salesfacts"."Dummy Input", 0) + "Plan Salesfacts"."Qnt Sold"</sawx:expr></saw:columnFormula>
<saw:displayFormat>
<saw:formatSpec suppress="repeat" wrapText="true">
<sawwb:writeBack xmlns:sawwb="com.siebel.analytics.web/writeback/v1" required="false" xsi:type="sawwb:editBox" inputSize="10" inputHeight="1"/></saw:formatSpec></saw:displayFormat>
<saw:tableHeading>
<saw:caption fmt="text">
<saw:text>Plan Salesfacts</saw:text></saw:caption></saw:tableHeading>
<saw:columnHeading>
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat>
<saw:caption fmt="text">
<saw:text>Qnt Sold</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
<saw:filter>
<sawx:expr xsi:type="sawx:logical" op="and">
<sawx:expr xsi:type="sawx:comparison" op="equal">
<sawx:expr xsi:type="sawx:sqlExpression">"Calendar"."Calendar Month Desc"</sawx:expr>
<sawx:expr xsi:type="xsd:string">2002-12</sawx:expr></sawx:expr>
<sawx:expr xsi:type="sawx:special" op="prompted">
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Prod Category"</sawx:expr></sawx:expr>
<sawx:expr xsi:type="sawx:special" op="prompted">
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Prod Subcategory"</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
<saw:views currentView="2">
<saw:view xsi:type="saw:compoundView" name="compoundView!1">
<saw:cvTable>
<saw:cvRow>
<saw:cvCell viewName="titleView!1">
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow>
<saw:cvRow>
<saw:cvCell viewName="tableView!1">
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
<saw:view xsi:type="saw:titleView" name="titleView!1"/>
<saw:view xsi:type="saw:tableView" name="tableView!1">
<saw:displayFormat>
<saw:formatSpec>
<sawwb:writeBack xmlns:sawwb="com.siebel.analytics.web/writeback/v1" xsi:type="sawwb:tableProperties" enabled="true" toggleTableMode="true" templateName="SetPlanSalesProdDtl" useTemplate="true" buttonPos="right"/></saw:formatSpec></saw:displayFormat>
<saw:edges>
<saw:edge axis="page" showColumnHeader="true"/>
<saw:edge axis="section"/>
<saw:edge axis="row" showColumnHeader="true">
<saw:edgeLayers>
<saw:edgeLayer type="column" columnID="c_MONTH_DESC"/>
<saw:edgeLayer type="column" columnID="c_PROD_CAT"/>
<saw:edgeLayer type="column" columnID="c_PROD_SUBCAT"/>
<saw:edgeLayer type="column" columnID="c_PROD_NAME"/>
<saw:edgeLayer type="column" columnID="c_AMOUNT_SOLD"/>
<saw:edgeLayer type="column" columnID="c_QNT_SOLD"/></saw:edgeLayers></saw:edge>
<saw:edge axis="column"/></saw:edges></saw:view></saw:views></saw:report>


Опробуем его.



Похожим образом создаем ансверы для ввода плановых показателей в разрезе подкатегорий товара и в разрезе категорий товара. У каждого из них указываем свой XML шаблон обратной записи, а также переопределяем на столбцах иерархии товара свойства Interaction: вместо свойство Drill (по умолчанию) задаем переход к сохраненному содержимому BI. Таким образом эмулируем дрилл-даун с возможностью writeback на каждом его уровне детализации.



Надеюсь, эта статья сэкономит вам не один час времени! Также вы можете скачать созданные в рамках этого примера RPD-файл и веб-каталог (созданные в примере ансверы находятся в папке /Shared Folders/Writeback Sample)

P.S. Ввиду очевидности реализации не стал рассматривать здесь механизм пропорционального разбиения суммы планового показателя верхнего уровня: например, вы задали плановую сумму продаж по категории товара и хотите, чтобы эта сумма автоматически распределилась на все подкатегории и затем на все входящие в нее товары пропорционально цене. Реализовать подобное можно с помощью триггеров на таблицы PLAN… и небольшого количества PL/SQL кода.

13 комментариев:

  1. Большое спасибо за столь подробное описание процесса настройки обратной записи для OBI. Как раз в скорости предстоит это проделывать самому, теперь будет намного легче я думаю.

    ОтветитьУдалить
  2. На здоровье =)
    Будут вопросы - пишите в личку.

    ОтветитьУдалить
  3. Подскажите, пожалуйста, как добавить Error detail в сообщение об ошибке Writeback.

    ОтветитьУдалить
  4. Добрый день, Юлия!
    Очень интересный и непростой вопрос...
    Попробую решить!

    ОтветитьУдалить
  5. Юлия, небольшое уточнение: верно я понимаю, что вы хотите, чтобы в окошке сообщения об ошибке Writeback была также какая-то дополнительная информация, позволяющая понять причину ошибки?

    ОтветитьУдалить
  6. Большое спасибо за описание!
    Есть ли возможность загрузить плановые показатели файлом?
    Эту же проблему можно переформулировать немного по-другому: как загрузить список в Oracle BI (например, список идентификаторов клиентов, чтобы потом их использовать для вычисления суммы покупок за период, например)

    ОтветитьУдалить
  7. Добрый день, Михаил!
    Вопрос неоднозначен - лучше напишите мне письмом. Обсудим детально.
    Скажу лишь, что сейчас я совсем отказался от Writeback в OracleBI из-за ряда причин (сложность настройки; сложность поддержки, сложность отладки; общее неудобство работы...)
    Для ввода плановых показателей использую веб-приложение с набором java-апплетов - по функционалу похоже на Excel, но в вебе.

    ОтветитьУдалить
  8. Добрый день, Сергей!
    В какой момент выполняется ? Вопрос в том, как сделать добавление новой записи(строки)?

    ОтветитьУдалить
    Ответы
    1. Добрый день, Дмитрий!
      "В какой момент выполняется" - не понял вопроса...
      А добавление новой записи происходит за счет использовании команды MERGE.

      Удалить
    2. Написал insert в треугольных скобках, он ушел в "тэг")

      Есть таблица: id, date$start, date$end
      Через интерфейс могу только изменить данные в любой строке таблицы.
      Проблема с добавлением новой записи, куда кликнуть на интерфейсе, чтобы создать новую строку с возможностью заполнить данные.

      Удалить
  9. Этот комментарий был удален автором.

    ОтветитьУдалить
  10. Здравствуйте, Михаил!
    Подскажите, есть ли возможность добавить две команды в один тег. Например, при изменении данных в строке, чтобы сначала выполнялся update, а затем insert. Это нужно для историчности.

    ОтветитьУдалить
  11. Я не "Михаил", хнык-хнык...

    Посмотрите тут - http://www.sql.ru/forum/900025

    ОтветитьУдалить