The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Including Fusion-on-Cloud Extensions in BIAPPS - Part 2

Authors: "Guna Vasireddy - Oracle" and "Yadukrishna Munugoti - Oracle "

The blog explains how to get Fusion DFFs into BI Apps when Fusion is On Premise. This blog explains how you can do the same if Fusion Applications is on Cloud.

When using BI Apps for Fusion (Cloud adaptor) , certain Flex VOs are already seeded out of the box. The steps to follow for getting Fusion Flex Extensions would depend on whether the Flex VO is already seeded for extraction Out of the Box (OOTB) or not. The first blog on this topic covered the steps for cases where the VO is already seeded. This blog covers the use-case where the VO is not seeded.

Extract and Load a Custom Flex VO (VO not seeded):

When using BI Apps for Fusion (Cloud adaptor) , certain Flex VOs are already seeded out of the box. The steps to follow for getting Fusion Flex Extensions into BI Apps
would depend on whether the Flex VO is already seeded or not. The below steps apply when the VO is not seeded by default (so the VO does not get extracted by  default). Step-wise instructions as below:

  1. Do the required extensions in Fusion Applications
  2. Run BI Extender to get the VO/Flexfields into RPD. Documentation (Section 2.3.6). Note if the VO/Columns do not come into the RPD, then we cannot extract data from it.
  3. Enable extraction for that custom VO into BICC. BICC does a select * for such VOs which are added as custom VOs. So if the VO is very wide and you are having extraction issues, consider disabling certain fields as BI enabled (refer Documentation (Section 2.3.5) on how to disable and run BI extender to make the changes effective). (Note: if you turn off BI Enabled, then the column will not appear in OTBI reports as well). Run an extract for that VO. The mdcsv file that is generated will help define the VO/Columns in ODI. Refer to BICC documentation for more detail.
  4. On ODI side, we need to essentially load the flat files ( data csv file and the .pecsv files) into the SDS and then take the flow all the way to the warehouse.
    1. Create the VO in the ODI BI Sever model (for example " Oracle Fusion XX HCM"). Tip: In Fusion 10, the extractor provides mdcsv files which will contain the metadata of the VO columns. Refer to that mdcsv file for metadata (Column Name, Datatypes etc) when you are manually adding the columns in ODI. Do not add the column flexfields (Like Column Short Name), when adding the columns. The RKM step (step #3) below will take care of that. You will need to add all columns that appear in the mdcsv file. 

    2. Define Primary key on Data stores. Also update the ODI key flex field values with the count of number of key columns that you have defined under the Key. So for example, if the primary key is based on 2 columns, set the flexfield(ODI Number of Unique Columns) against the ODI Constraint to 2

    3. Run the RKM against BI Server model in "REFRESH_SDS_FLEXFIELDS" mode to populate the VO/Column ODI flexfields. Choose introspect_source =false since we can't connect to the source directly. Set Mask property as relevant. For Example for the VO that is ending with FLEX_BI_BaseWorkerAsgDFF_VI, the property would be set to “%.FLEX_BI_BaseWorkerAsgDFF_VI”  as shown below. This is to prevent attempting to refresh the model for all the VOs inadvertently.

    4. Create File data store under corresponding Oracle Fusion XX Replication Stage Files model which is a FILE technology model, by  duplicating newly created VO definition under Oracle Fusion XX HCM model and moving it to the Oracle Fusion XX Replication Stage Files model. By doing this way , you can retain all the required date store flex fields.

      Example :
      Duplicating newly created VO definition under Oracle Fusion XX HCM model. The below screenshot shows it for one CRM VO.

      Move Duplicated object from Oracle Fusion XX HCM model to Oracle Fusion XX Replication Stage Files model  as shown below:

      Set file data store name and alias appropriately. In example below they are set as FILE_CRMANALYTICSAM_ACTIVITIESAM_ACTIVITY and its alias as FILE_CRMANALYTICSAM_ACTIVITIESAM_ACTIVITY. This will change as per the VO name.

      Set resource name as file_crmanalyticsam_activitiesam_activity#CLOUD_FILE_BATCH_ID.csv (Please note that the file resource name should be in lowercase).

      Go to Files tab and update the format section as shown below.

      Go to Columns tab and make sure that all columns data type is set to String and Length is set to 4000 as shown below. This is only for the File VO and not the actual VO.

      Save the changes.

    5. Create a copy of an existing FTS Mapping folder and then edit the interface, package etc to point to the new VO. Make sure you copy the FTS map from the right adaptor version. So if you are loading custom VO for Fusion 10 then take the copy of a map from Fusion 10 adaptor only and not Fusion 9.

    6. FTS Interface is a one to one mapping. Refer to an existing interface and create the new one accordingly. The package will also include a call to ODI procedure to load PE csv file (Primary extract or deleted file extract). Change the parameters being passed to it accordingly. Below screenshot shows the steps to change in the package. You will first need to create the interface before changing the package.

      As per the customization guidelines, Package, scenario names should ends with "CUSTOM" word. For Example, if you are creating a Package & scenario for a custom VO "CrmAnalyticsAM.ActivitiesAM.Activity" then your package name should be  "FTS_FUSION_CrmAnalyticsAM.ActivitiesAM.Activity_Custom" and your Scenario name should be "SDE_FUSION_10_ADAPTOR_FTS_FUSION_CRMANALYTICSAM_ACTIVITIESAM_ACTIVITY_CUSTOM "



    7. At this point it should be like regular Customization. Create/Customize SDE/SIL/PLP mappings to take the new VO/columns through to the warehouse tables.

    8. Run the Generate SDS DDL to create the table in the SDS(this is also part of the Regular load plan and it will get created then if you don’t explicitly run it).

    9. Include the Custom Maps into the generated Load plan at appropriate places (i.e. FTS, SDE, SIL etc).

    10. No additional steps are required to download the files from UCM. All files that are extracted and present in UCM will automatically get  downloaded when running the ODI Load Plan. So assuming you have included the custom VO/Column for extract, nothing else needs to be done for download.

Refer to BIAPPS Customization Documentation for generic guidelines around customization.

Refer to BIAPPS Configure Fusion Source in BIAPPS Installation Guide for details around Flex VOs that are supported out of the box.(this varies with BIAPPS release, so ensure you are looking up the right release).

The third blog in this series explains how to troubleshoot common issues when trying to  work with Fusion Flex Extensions.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.