Extending Oracle BI Applications with Custom Source Domains

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.

1. Define a custom domain in Configuration Manager.

   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

2. Extend data warehouse and staging tables with the new Domain column.

   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.

3. Create custom domain SDE tasks.

   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:

   1. Create custom interface and package for the target table, W_DOMAIN_MEMBER_GS. Follow these guidelines for the mapping expressions that populate the columns in W_DOMAIN_MEMBER_GS.

      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.

   2. Implement the filter expression.
      If the OLTP table supports multiple languages, implement the following filter:

      <OLTP Language Column> IN (#LANGUAGE_LIST)
      

      If the OLTP table does not support multiple languages, do not implement a filter on language.

   3. Use the IKM BIAPPS Oracle Incremental Update IKM.
   4. Add the Custom SDE to the Domain Load Plan Component.

      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.
4. Extend regular SDE and SIL ETL tasks to populate the new Domain column.

   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)

5. Extend the repository’s Physical Layer.

   Other than exposing the custom Domain column in the Fact or Dimension table, do not make other changes.

6. Extend the repository’s Logical Layer.
   1. Map the physical column to a logical column. Use in any filters or calculations as required.
   2. Implement 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.

      • Set the expression as follows (if you are exposing the Name):

        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>)
        

7. Extend the repository's Presentation Layer.

   Expose the logical Name and Description columns in the Presentation Layer as required.