Extending BI Applications with Custom Source Domains

Extend BI Applications with custom source domains.

  1. Define a custom domain in Configuration Manager.

    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 GUID-BBDB1796-B942-4336-AD27-E8CFA0EE2473-default.gif follows
    Description of the illustration GUID-BBDB1796-B942-4336-AD27-E8CFA0EE2473-default.gif

  2. Extend Data Warehouse and Staging tables with 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.

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

      		  
    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 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.
  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 RPD Physical Layer.

    Other than exposing the custom Domain column in the Fact or Dimension table, no other changes are required.

  6. Extend the RPD 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 ‘Descriptions’ are mapped 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 RPD Presentation Layer.

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