OBIA 7964/11.1.1.8.1 – E-Business UDA Vs DFF and OBIA Customization Best Practices – Part II (UDA)

Oracle Business Intelligence Application does not provide Universal Defined Attributes (UDA) attributes and DFF (Descriptive Flex Field) for reporting in out of box reporting tables. UDA and DFF allow users of Oracle to define elements specific to their business. As these are important business information which customer would like to include in the reporting. So customization is needed to include UDA and DFF in standard reporting arena.  Though Oracle promotes no customization policy but I believe it is impossible to implement OBIA without any customization.  The bare minimum customization which I have seen is to get the DFF and UDA into reporting structures.

This series of blogs is an attempt to cover definitions of UDA/DFF, similarities, differences and OBIA best practices to deal with UDA and DFF customization.   This blog covers best practices around inclusion of UDA in reporting structures.

Category 1 Customization

For UDA attributes extraction and loading process, the data for newly added column will flow through a separate path in the existing mapping as recommended by   Oracle.  It is a category 1 customization as depicted below.

                                                         

Generic ETL Assumptions

For discussion purpose I am iterating generic ETL assumptions which are applicable to any standard E-Business OBIA customization.

• Data in the BAW is based on Source System (e-Business Suite R12.1.1.3).
• The name of new columns should start with a X_ as per Oracle standard naming convention for OBIA Category 1 customization.
• The name of new tables should start with WC_ as per Oracle standard naming convention for OBIA Category 2 customization.
• New folders need to be created to hold all the customized components, one for SDE and another for SIL. As for example: CUSTOM_SDE, CUSTOM_SIL
• All the extension logic and additional transformations inside a mapping should follow same route through the mapping as X_CUSTOM that marks a safe path.
• All the dependencies for each impacted mapping like Workflows, Session should be refreshed accordingly to reflect the modifications.
• All the custom Mapping, Mapplets, Workflows and Session names are prefixed with ‘XX_’, to identify them as custom components.
• A new task is created in DAC with name prefixed as ‘XX_’ for each custom workflows created.
• No data quality is being handled

Major Steps UDA customization

Oracle has provided some best practices for customizing the BAW environment. The end to end process of customization for UDA is shown below.

The generic steps and best practices to be followed for any customizations in BAW environment are mentioned as below:

• Columns which are added in the existing tables in BAW should have their name start with X_, e.g.  X_UDA_COST_CC_NO
• Create a new folder to hold the impacted components like Mappings, Mapplets, Source and Target definitions. Example: CUSTOM_SDE.
• The Mappings, Mapplets, Source and Target which are impacted for this customization need to be copied from the original folder to this custom folder.
• Workflows and sessions of these impacted mappings need to be refreshed or may be dropped and recreated again.
• Table metadata need to be updated for the addition of columns.
• Source qualifier of Mapplets present in SDE mappings need to updated for these new columns. The underlying source qualifier query and port need to be updated by adding the new columns to them.
• In SDE mapping the new columns should follow the safe path that is through the X_CUSTOM path.
• For SIL mappings a custom folder need to be created first like CUSTOM_SILOS. Similarly all the impacted SIL mappings and their corresponding Mapplets need to be copied in this custom folder.
• In SIL maps, the source qualifier need to be updated with new columns and the column should propagate through the X_CUSTOM path before passed through the filter transformation and finally loaded to the target definition.
• The new folders need to be registered in DAC.
• The modified tables are then updated in DAC.
• New DAC task need to be created for custom workflows. These tasks need to synchronized and added to a related subject area and finally added to an execution plan.

Example – Project Dimension

The UDA attributes can be with value set and without value set.  Both type of UDA attributes are added in existing dimension table in BAW environment by extending them.

The UDA attribute data is would be sourced from EBS base tables e.g. PA_PROJECTS_ERP_EXT_B, EGO_FND_DSC_FLX_CTX_EXT, FND_LOOKUP_VALUES etc which are set of tables which holds UDA attribute data and lookup values if any.

The newly added columns are also added in the existing mappings which are used to load the impacted dimension table. The newly added column is mapped by adding a separate new custom expression EXP_X_UDA.

The UDA attributes (with value set and without value set)) which are configured in source table PA_PROJECTS_ERP_EXT_B (for value set UDA need to refer table FND_LOOKUP_VALUES also) are populated in dimension table W_PROJECT_DS and then W_PROJECT_D.

Inference

The UDA attributes (with value set and without value set)) can be extracted and loaded by applying category 1 customization. The key is to make sure the general and specific best practices are followed to support future upgrades.