Adding Custom Tables for OUAF-Based Source Applications
Most of the tables related to tables used for populating the out-of-the-box star schemas are listed in the metadata configuration “Source Tables”. It is possible that the table required to be extended is not listed.
To include the table to be extended in the source tables list:
1. Create a procedure CM_<PROD_FLG>_CREATE_METADATA. Replace <PROD_FLG> with the appropriate edge product code.
For example: CCB/NMS/MDM/MWM/WAM
2. Create a new task for each metadata entry into B1_OBJECT_MAP. The tasks within the procedure should have the logical schema set to “Metadata”.
B1_OBJECT_MAP requires two entries - one entry mapping the MO to a custom view and the second entry mapping the custom view to the target custom fact or dimension.
Step 3 creates the first entry and step 4 creates the second entry.
3. Add an entry in B1_OBJECT_MAP setting SOURCE_OBJECT_NAME as the MO name and TARGET_OBJECT_NAME as the target fact or dimension, which has attributes loaded from this table.
These should be written as merge statements so that the existing rows are skipped and only new rows are added. If metadata requires corrections, use the update clause of the merge statement. The schema names should not be hardcoded.
For example: The following merge statement sets the tables under a maintenance object in Oracle Utilities Customer Care and Billing for inclusion in the replication process.
• Source Product Flag is the product flag of the source. In this example, it is 'CCB' for Oracle Utilities Customer Care and Billing.
• Source Object Name is the source maintenance object. In this example, the tables are included under the Budget Review maintenance object. It is specified as 'BUD REVIEW' which is the maintenance object code for Budget Review in Oracle Utilities Customer Care and Billing.
• Target Object Name is the ETL view that uses the tables of this maintenance object. In this example, CM_TEST_VW is specified as dummy value.
• Object Type Flag is the type of object to be replicated. In this example, replicating the entire Budget Review MO is specified; hence 'MO' has been specified.
merge
into b1_object_map tgt
using (select 'CCB' prod_flg
, 'BUD REVIEW' source_object_name
, 'CM_TEST_VW' target_object_name
, 1 seq
, 'MO' object_type_flg
from dual ) tgt_val
on ( tgt.prod_flg = tgt_val.prod_flg
and tgt.source_object_name = tgt_val.source_object_name
and tgt.target_object_name = tgt_val.target_object_name
and tgt.seq = tgt_val.seq)
when not matched
then insert
(
tgt.object_map_id
, tgt.prod_flg
, tgt.source_object_name
, tgt.target_object_name
, tgt.seq
, tgt.object_type_flg
, tgt.char_entity_flg
, tgt.upd_dttm
, tgt.upd_user
, tgt.owner_flg
)
values
(
b1_object_map_seq.nextval
, tgt_val.prod_flg
, tgt_val.source_object_name
, tgt_val.target_object_name
, tgt_val.seq
, tgt_val.object_type_flg
, null
, sysdate
, sys_context('userenv', 'os_user')
,'B1');
4. Run the following Insert statement to specify that CM_TEST_VW ETL view populates the target CM_F_FT.
merge
into b1_object_map tgt
using (select 'CCB' prod_flg
, 'CM_TEST_VW' source_object_name
, 'CM_F_FT' target_object_name
, 1 seq
, 'PRVW' object_type_flg
from dual ) tgt_val
on ( tgt.prod_flg = tgt_val.prod_flg
and tgt.source_object_name = tgt_val.source_object_name
and tgt.target_object_name = tgt_val.target_object_name
and tgt.seq = tgt_val.seq)
when not matched
then insert
(
tgt.object_map_id
, tgt.prod_flg
, tgt.source_object_name
, tgt.target_object_name
, tgt.seq
, tgt.object_type_flg
, tgt.char_entity_flg
, tgt.upd_dttm
, tgt.upd_user
, tgt.owner_flg
)
values
(
b1_object_map_seq.nextval
, tgt_val.prod_flg
, tgt_val.source_object_name
, tgt_val.target_object_name
, tgt_val.seq
, tgt_val.object_type_flg
, null
, sysdate
, sys_context('userenv', 'os_user')
,'B1');
5. Create the CM_<PROD_FLG>_CREATE_METADATA package.
a. Add the procedure created in step 1.
b. Add the B1_CFG_METADATA scenario and then add the B1_CFG_INSTANCE_JOBS scenario.
c. After migrating the CM Project to a new environment, execute the custom procedure CM_<PROD_FLG>_CREATE_METADATA after adding the product instance.
This job should be executed in the context for the product.
Executing this package in the appropriate context ensures that the required tables are present in the metadata configuration tables. For instructions, refer to the
Enabling Replication section.
These instructions are applicable to all source applications except Oracle Utilities Network Management System, which does not use Oracle Utilities Application Framework (OUAF).
Note: For more details, refer to the Mapped Objects section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.