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

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 DFF in reporting structures. 

Category 1 Customization

For DFF 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.

Category 2 Customization

A new dimension table WC_CODE_EXTN_D is created to store the DFF attributes which are configured in source tables used to load W_CODE_D. The new table contains the same unique key as per the existing dimension table W_CODE_D. The new table data can be fetched in the reports by snow-flaking it with W_CODE_D.

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

Oracle has provided some best practices for customizing the BAW environment. The end to end process of customization for DFF 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_DFF_LOCATION
• New tables which are added in BAW schema should have their name start with WC_, e.g. WC_CODE_EXTN_D
• 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 a execution plan.

Example – Dimension/Fact

 Dimension

Some of the DFF attributes (configured and non-configured) are added in existing dimension tables in BAW environment by extending them.

The data would be sourced from EBS base tables e.g. PA_TASKS, PA_PROJECT_TYPES_ALL etc. The newly added columns are also added in the existing Informatica mappings which are used to load the impacted dimension tables.

The newly added column is mapped by adding a separate mapping in the custom expression path (X_CUSTOM).

The DFF attributes (configured and non-configured) which are configured in source tables FND_FLEX_VALUES and FND_LOOKUP_VALUES are populated in a new custom dimension table WC_CODE_EXTN_D which is an extension of W_CODE_D.

This new dimension table will be used in reports by snow-flaking with W_CODE_D. The join condition will be based on four columns DATASOURCE_NUM_ID, SOURCE_CODE, CATEGORY and LANGUAGE _CODE.

Fact

The remaining DFF attributes (configured and non-configured) are added in existing fact tables in BAW environment. The newly added columns are also added in the existing Informatica mappings which are used to load the impacted fact tables.

Inference

 The DFF attributes (configured and non-configured) can be extracted and loaded by applying category 1  and category 2 customization . The key is to make sure the general and specific best practices are followed to support future upgrades.