Extend BI Applications with custom source domains.
See About Working With Domains and Domain Mappings for more information on how to create a custom domain and associate it with a Fact or Dimension Group. 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 BI Applications Configuration Manager when the domain was registered, for example, X_CUSTOM. |
|
DOMAIN_TYPE_CODE |
'S' |
|
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__' |
|
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 Configuratin 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 |
<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 was 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 tell the Load Plan Generator which Fact Group to use. 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 may 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, no other changes are required.
LOOKUP()
function if MLS required.
Create a new logical column. For ‘Names’, map to "Core"."Lookup - Domain Source"."Domain Member Name" while ‘Descriptions’ are mapped 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.