Managing Fusion Flex Extensions in Release 11.1.1.10.2

This section applies to the BI Applications Release 11.1.1.10.2 and to Fusion 10 and 11.

The warehouse tables, by default, carry a limited number of extension columns. For char type columns there are usually around 30 char columns in the warehouse tables that can be used for extensions. At times there could be cases where you end up with more than 30 columns. In such a case, only 30 columns would get mapped for extensions while the remaining columns get loaded in the SDS.

This section explains the current options available in such a case and provides a way by which you can control the columns that should get mapped rather than just let the algorithm decide which columns should get mapped.

Option 1: Using the BI Applications Enabled Flag on the Fusion side

This is the recommended approach.

If all the flexfields you defined on the VO are not relevant for BI Applications, you can restrict the flex extensions in BI Applications to only those that are relevant to BI Applications, by following these steps:

  1. Enable the BI Enabled option.

    When you define a flex field in Fusion, you see the BI Enabled option. Enabling this option ensures that this flex field gets extended to the repository and eventually flows into the BI Applications. If however some columns are not required for BI Applications, then you can disable this option for those columns.

    Description of GUID-279F0EE6-95EA-4747-AADE-92BEDDF1503E-default.gif follows
    Description of the illustration GUID-279F0EE6-95EA-4747-AADE-92BEDDF1503E-default.gif
  2. On editing the selected flex field, you can see the option for existing flexfields. Disable the option for the ones that are not required for BI Applications.

    Note:

    Disabling the BI Enabled option disables it for OTBI as well. Disable this option only if it is not required for OTBI as well as B IApplications. There is no current way to disable it only for BI Applications.

  3. After disabling the option, wait till the BI Extender runs again to update the repository. Post that schedule, run a full extract, and do a full load again. Drop the SDS tables and reset the warehouse (using the option in the Configuration Manager ) before running the full load.

Option 2: Specifying a Column Exclude List in ODI Repository:

If Option 1 is not feasible, then try this option. The Extension framework in BI Applications looks at the ODI repository to identify which columns are extension columns. A workaround is to add the extension columns you do not need in BI Application, to the ODI repository against the VO. This way the extension framework treats them as regular columns and not extension columns.

Based on your use case, follow either of these intructions:

  • You have access to the extended Fusion repository

  • You do not have access to the extended Fusion repository

If you have access to the Fusion source repository from your ODI environment, follow these steps:

  1. Run the Oracle BI Applications reverse knowledge module (RKM).

  2. Login to the ODI repository using the ODI studio and navigate to the Models tab and open Oracle Fusion 10 HCM model as shown below.

    Note:

    The Fusion Connection should be specified pointing to the BI Server with the extended repository.
    Description of GUID-6B70DB4E-3632-483C-B7DB-D128C69EF525-default.gif follows
    Description of the illustration GUID-6B70DB4E-3632-483C-B7DB-D128C69EF525-default.gif

  3. Set the Mask property to%.FLEX_BI_BaseWorkerAsgDFF_VI as shown below. This prevents attempting to refresh the model for all the VOs inadvertently.

  4. Click Reverse Engineer to bring all the VO’s extension columns into the model.
    Description of GUID-EDDA6CCE-4300-4002-8EB3-ECA20982AA90-default.gif follows
    Description of the illustration GUID-EDDA6CCE-4300-4002-8EB3-ECA20982AA90-default.gif

  5. Once you see all the extension columns against the VO’s definition, identify the extension columns that are needed for your business, and then delete those columns which you required from the data store.

    For Example, if SE_HRBP_ extension column is needed for your business, delete it and save the changes.

    For the remaining extension columns which you do not need and if you do not want the column to be populated or created in SDS, then use the flexfield OBI Populate Column in SDS to do so.

  6. Edit the flexfield value (by default the flexfield value is be set to Y) and set the value as N and save the changes.

If you do not have access to the fusion source repository from your ODI Environment, follow these steps:

In this case, since you do not have access to the fusion source environment, there is no way to run RKM to reverse engineer a VO to add all extensible columns to it. Hence you should add all unused extensible columns manually against a VO’s data store.

Note:

In Fusion 10, the extractor provides mdcsv files which will contain the metadata of the extended columns. Refer to that mdcsv file when you are manually adding the columns in the ODI.
  1. Login to the ODI repository using the ODI studio and navigate to the Models tab, find the data store, and open it as shown below.
    Description of GUID-9E444682-280D-4F5C-A8D3-BB7B83F86355-default.gif follows
    Description of the illustration GUID-9E444682-280D-4F5C-A8D3-BB7B83F86355-default.gif

  2. Click Add to add columns. Choose the correct data type, length and other options and save the changes.

    If you do not want the column to be populated or created in the SDS, then use the flexfield OBI Populate Column in SDS to do so.

  3. Edit the columns and go to the Flexfields tab. By default, the flexfield value is set to Y. Edit the flexfield value and set the value as N and save the changes.

  4. Run the RKM to update the newly added extensible column’s short name.

  5. Navigate to the Models tab and open the Oracle Fusion 10 HCM model

  6. Click the Reverse Engineer tab and set the Mask property to%.FLEX_BI_BaseWorkerAsgDFF_VI as shown below. This prevents attempting to refresh the model for all the VOs inadvertently.
    Description of GUID-ABE8AD33-2161-418D-9856-0466014D1F01-default.gif follows
    Description of the illustration GUID-ABE8AD33-2161-418D-9856-0466014D1F01-default.gif

  7. Set the INTROSPECT_SOURCE option to false, the USE_LOG option to false and enable the REFRESH_SDS_FLEXFIELDS mode by changing the option value to true.

  8. Click Reverse Engineerto bring all VO’s extension columns into the model.

    Note:

    If you have already loaded data, then you need to do a full load again. Reset the warehouse and make sure that all warehouse tables are empty and reload.

Option 3

If Option 1 and 2 are not possible, then use option 3. In this option you attempt to manually update the backend table that controls the mapping between the Source column and the target warehouse column. Identify a column that has already been mapped but not required and then run backend update statements to switch it to a column that should get mapped instead.

As an example, assume that CATEGORY10_ extension column is not required and you want to switch this mapping with INTERNATIONAL__STATUS_ extension column.

  1. Connect to BIApps warehouse schema using SQL tools.

  2. Run the following update script: 
    UPDATE W_ETL_FLEX_SQL SET FLEX_SRC_ATTRIB     ='INTERNATIONAL__STATUS_', 
ATTRIB_SQL_EXPRESSION='ASGDFF.INTERNATIONAL__STATUS_'WHERE FLEX_CODE     
='HRASG'  AND FLEX_SRC_TABLE      
='HcmTopModelAnalyticsGlobalAM.BaseWorkerAsgDFFBIAM.FLEX_BI_BaseWorkerAsgDFF_VI' AND FLEX_SRC_ATTRIB ='CATEGORY10_'
AND FLEX_ ATTRIB='ASSIGNMENT_ATTR14_CHAR' 
AND DATASOURCE_NUM_ID   =200;

COMMIT;

    Note:

    If the data is already loaded then you need to reload it to use the new mapping. Do not attempt to truncate all warehouse tables before doing the reload. Instead use the reset warehouse option in the Configuration Manager. Truncating all warehouse tables will truncate the mapping table as well.