Typically, extending Oracle BI Applications involves defining a custom domain in Configuration Manager, extending data warehouse and staging tables with the new domain column, creating custom domain SDE tasks, extending regular SDE and SIL ETL tasks, and extending the repository’s layers.
To extend Oracle BI Applications with custom source domains:
To create a custom domain and associate it with a Fact or Dimension Group, see About Working With Domains and Domain Mappings. Note the Domain Code value used. If the Domain Code is not entered correctly at any point, records associated with the domain can cause errors.
While not required, it is a good practice to use a prefix with Domain Codes, such as X_
, to easily distinguish custom codes from Oracle-supplied Domain Codes, for example, X_CUSTOM
.
Description of the illustration GUID-BBDB1796-B942-4336-AD27-E8CFA0EE2473-default.gif
Oracle recommends that the Domain column reflects the Domain Code. However, Domain columns should always have _CODE
as a suffix, for example, X_CUSTOM_CODE
.
If the source domain is extracted from a source table, create an ETL task to extract the domain members and load them into the W_DOMAIN_MEMBER_GS table. Follow the regular customization steps for creating a custom SDE task with the following additions:
Column | Expression | Notes |
---|---|---|
DOMAIN_CODE |
Hard-code the value assigned in Configuration Manager when the domain is registered, for example, X_CUSTOM. |
Not applicable. |
DOMAIN_TYPE_CODE |
'S' |
Not applicable. |
DOMAIN_MEMBER_CODE |
Map to the language independent value that identifies the domain member. |
|
DOMAIN_MEMBER_NAME |
Map to the language dependent value that corresponds to the short text name of the domain member. |
|
DOMAIN_MEMBER_DESCR |
Map to the language dependent value that corresponds to the long text description of the domain member. If there is no description type field, map to the same field as Name. |
|
DOMAIN_MEMBER_REF_CODE |
'__NOT_APPLICABLE__' |
Not applicable. |
LANGUAGE_CODE |
DOMAIN_MEMBER_MAP( 'LANGUAGE', OLTP Language Column, #DATASOURCE_NUM_ID, 'W_LANGUAGE' ) |
LANGUAGE is an example of a conformed domain. Map the LANGUAGE column to the appropriate language column in the OLTP. If OLTP table does not support multiple languages, seed using ‘#BIAPPS. LANGUAGE_BASE’ |
INTEGRATION_ID |
'Domain Code registered in Configuration Manager for this Domain' ||'~'|| OLTP Column that identifies the Domain member |
Concatenate the domain code with the domain member code, for example, X_CUSTOM. |
DATASOURCE_NUM_ID |
#DATASOURCE_NUM_ID |
Not applicable. |
<OLTP Language Column> IN (#LANGUAGE_LIST)
If the OLTP table does not support multiple languages, do not implement a filter on language.
Navigate to the appropriate 3 SDE General Domain Load Plan Component: Load Plans and Scenarios, BIAPPS Load Plan, SDE, Adaptor Code_version number, then 3 SDE General Domain version number.
Add a new Step. Have the step execute the custom Domain SDE’s scenario. Populate the Keywords property with DOMAIN=Value assigned in Configuration Manager when the domain is registered.
This step allows the Load Plan Generator to associate the Domain with a particular Fact or Dimension Group. When you generate a Load Plan, you specify the Fact Group to use to the Load Plan Generator. The Load Plan Generator includes any Domain ETL tasks that are stamped with a Domain Keyword that is associated with this Fact Group.
Note:
Because the out-of-the-box Load Plan is modified, patches override the customization. You might need to reapply any customizations after load plan patches are applied.Follow the regular customization methodology for extending the SDE and SIL ETL tasks to populate the Fact or Dimension table with the following addition.
In the SDE, set the mapping expression for the Domain column as:
DOMAIN_DEFAULT_UNASSIGNED(<OLTP Column>)
In the SIL, set the mapping expression for the Domain column as:
DOMAIN_DEFAULT_NOT_APPLICABLE(<Staging Table Column>)
DOMAIN_DEFAULT_NOT_APPLICABLE(X_CUSTOM_CODE)
Other than exposing the custom Domain column in the Fact or Dimension table, do not make other changes.
LOOKUP()
function if MLS required.
Create a new logical column. For Names, map to "Core"."Lookup - Domain Source"."Domain Member Name" while map Descriptions to "Core"."Lookup - Domain Source"."Domain Member Description"
Set the Column Source as Derived from existing columns using an expression.
Lookup(DENSE "Core"."Lookup - Domain Source"."Domain Member Name" , '<Domain Code registered in BIACM>', <Logical Column mapped to physical column>, VALUEOF(NQ_SESSION."USER_LANGUAGE_CODE") , <Logical column that maps to physical DATASOURCE_NUM_ID column>)
Expose the logical Name and Description columns in the Presentation Layer as required.