Сегодня я расскажу о своем опыте использования механизма обратной записи – 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 кода.
Сообщения
