Custom Materialized Views
A materialized view stores the aggregated data, helping the analytics to fetch data from the materialized view.
Note: OOTB materialized views are not provided for Oracle Utilities Network Management Systems and Oracle Utilities Work and Asset Management.
This section provides the steps to create a materialized view on custom facts:
Creating Mapping for Materialized View
To create mapping for a materialized view:
1. Login to the Oracle Data Integrator client.
2. On the Designer, navigate to the Models > User Customization > <product_name>.
In this example, ‘CCB’ is the product.
3. Create a Materialized View folder (if not already existing).
4. Right-click the mapping and select New Mapping from the menu.
5. On the mapping editor, enter the mapping name in the Name field.
6. Navigate to Models > Target.
Expand the model and drag the source table (dimension or fact) into the target area of the mapping editor.
7. Set up the appropriate join conditions between the source tables.
8. Navigate to Models > User Customization > CCB > Materialized View.
9. Drag and drop the materialized view to the mapping editor.
Note: Ensure the materialized view datastore is created in the Oracle Data Integrator model before creating the mapping.
10. Map the Target Table (Materialized View) columns with those of the source tables.
11. On the Flow tab, select “IKM BI Materialized View” from the Integration Knowledge Modules (IKM) drop-down list.
12. Click Save.
13. Run the mapping so that the materialized view is created in the database.
14. Reverse the materialized view in Oracle Data Integrator so that the data type is same in both Oracle Data Integrator and database.
Note: If the datastore structure is different in database and Oracle Data Integrator, then execute the materialized view in Upgrade mode. Then, modify the materialized view definition instead of refreshing it.
Creating Packages for Materialized View
To create a package for the new fact:
1. Login to the Oracle Data Integrator client.
2. Navigate to Designer > Models > User Customization > Materialized View > Packages.
3. Right-click Package and select New Package from the menu.
4. On the package editor, enter the package name in the Name field.
5. Click the Diagram tab at the bottom of the editor.
6. From the Global Objects section, drag the B1_JOB_ID variable into the editor.
Modify the B1_JOB_ID variable to declare a variable.
7. Drag and drop the mapping into the editor and connect them in sequence.
8. Click Save to save the changes and close the package editor.
Navigate to Packages and expand it. The new package is displayed.
9. Right-click Generate Scenario. Enter the scenario name and click OK.
10. In the Packages folder, verify if the scenario object generated is listed.
Configuring Entities for Materialized View
To configure a new entity for a custom materialized view:
1. Login to Oracle Utilities Analytics Warehouse Administration.
2. On the ETL Configuration tab, click Target Entity. Click Add.
3. Enter the fact job details.
4. Enter the materialized view name. Click Save to save the details.
Specifying Dependencies for Materialized View
This section provides a sample query to insert the dependency.
merge
into b1_object_map tgt
using (select 'CCB' prod_flg
, 'DIM1' source_object_name
, 'CM_MV' target_object_name
, 1 seq
, 'MVDP' 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');
Configuring Jobs for Materialized View
To configure a job for the custom fact:
1. Login to Oracle Utilities Analytics Warehouse Administration.
2. On the ETL Configuration tab, select Job Configuration.
3. Click Add to add the job details.
4. On the Job Addition page, select a product from the Source Product drop-down list, and then select the Instance Number.
5. Click the Search icon for the Target Entity field. Enter the fact name and click Go.
6. Click ID Value. On the Job Addition page, the target entity ID is populated.
7. Set the Slice Start Date/Time to “01-Jan-2000” or the extract date to which the source instance is configured. Click Add to create the Job Configuration entry.
8. Enable the job while saving the new entry.
Monitoring Job Execution
After configuring the job for customization and activating it, monitor the job execution using Oracle Utilities Analytics Warehouse Administration or SQL Developer.
To monitor the job execution using Oracle Utilities Analytics Warehouse Administration:
1. Login to Oracle Utilities Analytics Warehouse Administration.
2. On the ETL Job Execution tab, enter the fact name and click Go to filter the data.