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 кода.