Populating User Defined Foreign Keys
This section describes the steps to extend out-of-the-box facts with custom dimension. Create the custom dimension and load it. Customize the fact load to use the custom dimension and populate the custom dimension key.
Important! Before performing these tasks, complete the steps mentioned in the
Custom Dimensions section.
The section includes the following:
Creating CM Views
To create a mapping used to wrap the existing custom dimension:
1. In the Oracle Data Integrator client, navigate to the Customization project.
2. Right-click Mappings and click New Mapping.
3. Enter “CM_D_ARREARS_UDDX1_VW” in the Name field.
“CM_D_ARREARS_UDDX1_VW” is taken as example. Replace it with the custom name.
4. Enter “Override out of the box UDDX1 view for arrears fact” in Description.
5. Click the Mapping tab at the bottom of the page to go to the Edit Mapping page.
6. On the left pane, navigate to Models > Customizations > UDX Dimension.
7. Select and drag the CM_D_ARREARS_UDDX1 custom dimension into the Logical Tab section.
8. In Property Inspector window, modify the Name to ‘UDDX1’.
9. On the left pane, navigate to Models > Oracle Utilities BI > {Product Flag} > Dimensions.
The naming convention of the UDDX view is B1_D_<FACT NAME>_UDDX1_VW to populate the UDD1_KEY of the fact.
10. Select the View, Drag, and Drop in the Logical tab section.
Example: If the fact name is CF_ARREARS, to populate UDD1_KEY, the view name would be B1_D_ARREARS_UDDX1_VW. To populate UDD2_KEY, the view name would be B1_D_ARREARS_UDDX2_VW.
11. Map the Target columns with Custom dimension.
Note for Type I dimensions! Use 01-Jan-1900 as EFF_START_DTTM and 01-Jan-4000 as EFF_END_DTTM.
12. Navigate to Physical tab.
13. Click the target table.
14. In Properties Inspector in Integration Knowledge Module, select “IKM BI View Generation” from the IKM Selector drop-down list. Do not modify the remaining fields.
15. Click Save to save the changes.
16. Execute the mapping and go to the Operator to view the status.
The job executes successfully and the view is created.
![](../OUAW-DEV/images/Creating_CM_Views_10.png)
17. Verify the view data by executing the following query in SQL Developer. The data from the view and the custom dimension should match.
select *
from {Target}.uddx view
Creating CM Mappings
This section describes the process of extending CF_ARREARS for User Defined Foreign Key.
Note: In this example, assume ‘CCB1’ to be the context defined for Oracle Utilities Customer Care and Billing source attached to Oracle Utilities Analytics Warehouse.
To create a CM mapping:
1. Login to the Oracle Data Integrator client.
2. On the Designer tab, navigate to Projects > User Customizations > <product_name> > Facts > Mapping.
In this example, 'CCB' is the product.
3. Right-click Mapping and select New Mapping from the menu.
4. In the New Mapping window, enter the name of UDX in the Name field.
Example: CM_CF_ARREARS_UDX
5. Unselect the Create Empty Dataset checkbox.
6. Click the Logical tab to view the table structure.
7. From the Models section, drag and drop the UDX and replication tables in the designer pane.
8. Join the UDX and replication table.
9. Drag and drop the UDX_CF_ARREARS target data store.
10. Select UDX as the target table and select the appropriate key on the UDX table as defined in the CF_ARREARS fact. The logic to populate UDX should be taken care in the mapping accordingly.
11. On the Physical tab, select the optimization context.
12. Select the target table (UDX_CF_ARREARS) and select IKM BI Direct Load from the Integration Knowledge Module drop-down list.
13. On the Options tab, set DML_OPERATION to UPDATE instead of MERGE.
14. Unselect the CREATE_TARG_TABLE option since the UDX table is already created.
The custom mapping is successfully created. A custom package can be created using this mapping. For instructions, refer to the
Creating CM Packages section.
Creating CM Packages
To create a custom package for the existing custom mapping:
1. Login to the Oracle Data Integrator client.
2. Navigate to Designer > User Customizations > <product_name> > Fact > Packages.
In this example, 'CCB' is the product name.
3. Right-click Packages and select New Package from the menu.
4. In the Package Editor window, enter the name of UDX in the Name field.
Example: CM_PKG_CM_CF_ARREARS
5. Click the Diagram tab at the bottom of the editor.
6. From the Global Objects section, drag and drop the 'B1_JOB_ID' variable into the editor.
7. Change 'B1_JOB_ID' to declare the variable.
8. Drag and drop the CM mapping (existing mapping) into the editor and connect them in sequence.
9. Click Save and close the package editor window.
10. Navigate to the Packages folder and expand it. The new package is shown.
11. Right-click the package and select Generate Scenario.
12. Enter the scenario name and click OK.
13. Select the startup variables and click OK.
14. In the Projects section, navigate to User Customizations > CCB > Fact > Packages.
15. Expand the package created.
The scenario object created is shown.
Configuring CM Scenarios
To configure the user extension procedure for a fact:
1. Login to the Oracle Utilities Analytics Administration user interface.
2. Click the ETL Configuration tab and click Job Configuration.
3. Enter the procedure name in the User Exit Procedure field and click Go to filter the data.
Example: CM_CF_ARREARS_UDX
![](../OUAW-DEV/images/Configure_CM_Procedure_1.png)
4. Click the edit icon to edit the details.
5. Set the
User Exit Procedure (example: CM_CF_ARREARS_UDX) and click
Save.
![](../OUAW-DEV/images/Configure_CM_Procedure_2.png)
6. Set the Entity Active Flag to Yes to enable the job.
7. Monitor the job execution and verify the data is in the final fact.