Working with OBIA-CM and Domain Member mappings

One of the first tasks of the any Oracle BI Application project is to configure the module to be loaded within OBIA-CM. After defining the module (offering) the to be configured parameters (data load parameters and reporting parameters) and the domain member mappings are displayed on the right side of the Overview Page e.g. https://xxxobidev.opc.oracleoutsourcing.com/biacm/:

image

When clicking on one of the bars of the module for the unmapped domain members a comprehensive listing of all domains with at least one unmapped source domain member is shown e.g. 6 domains for Procurement & Spend Analytics:

image

Clicking on edit allows to open the respective domain (with at least one unmapped source domain member) and work on the mapping of the members to target domain members.

image

The step of mapping unmapped values and also confirming out-of-the-box is important because predefined metrics (within the metadata of the .rpd) rely on the specified target domain member for calculations. While the Web UI of OIBA-CM is comfortable to perform these configuration work, there is not an option to export a list of:

  • mapped source to target domain members
  • unmapped source domain members

to e.g. Excel in order share it with a Functional Consultant of the Fusion Applications Cloud team. There is only one export option to export the unmapped source domain names (with at least one unmapped member) from the page, after clicking on the bar chart:

image

The below Technote describes the approach to use the internal tables of OBIA-CM to generate a list with the mapped source to target domain members:

When connecting the internal schema of OBIA-CM (typically in a schema similar like BI11G_BIACOMP) the provided sql can be executed:

--SQL for mapped source and target values
SELECT      dtrg.domain_code trg_domain_code,
dsrc.domain_code src_domain_code,
m.*
FROM
c_domain_member_map m,
c_domain dsrc,
c_domain dtrg,
c_domain_plv_rel plv
WHERE
m.src_domain_key = dsrc.domain_key
AND   m.trg_domain_key = dtrg.domain_key
AND   m.src_domain_key = plv.domain_key
AND   m.domain_plv_key = plv.domain_plv_key
--reduce data-set to a certain Domain Name and Datasource:
AND dsrc.domain_code = 'AP_HOLDS'
AND m.datasource_num_id=100;

The above SQL has been extended to only select records for a single domain, in this case AP_HOLDS and a single data source of Fusion Applications Cloud with the Datasource Number 100 for illustration purposes.

image

The same information can also be accessed from the OBIA-CM web frontend when clicking on the manage domain mappings and Hierarchies link on the left hand side of the menu and search for a domain e.g. ‘AP_HOLDS’:

image

While the provided sql (of the technote), allows to access the mapped domain members, there is no sql that allows to export the unmapped domain members. Due to the complexity and structure of the data model, it is not feaseable to alter the sql statement to also show the unmapped domain name and unmapped domain member name, since the intersection table is only filled for mapped domain members.

The attached sql provides an altered version of the sql to give some insight into the unmapped members of a given domain. Thus, it may be used in conjunction with the second screenshot (which gives the domain names having unmapped members). The attached sql can also be altered by removing the hardcoded filter to a single domain (‘AP_HOLDS’ have been used) , but then all unmapped members will be listed without the source domain name they belong to. The datamodel has also been described in the additional ER Diagram for the altered sql version.

As a working suggestion, it is recommended to make screenshots of the domains having unmapped members and send these to the functional consultant of the Fusion team as preparation. In a follow-up meeting the unmapped domains may be mapped by walking thourgh the list in the OBIA-CM.

Exporting & Importing BICC from one environment to another

Within BICC the PVO is associated with the Oracle BI Applications (OBIA) offering:

E.g. the below example shows that CrmAnalyticsAM.ActivitiesAM.ActivityObjectives is associated with: Human Resource, Sales and Service. Thus, the PVO will be included in these OBIA modules and a text file containing the data will be placed on UCM:

image

The configuration/customizations done in BICC can also be exported using the top right menu:

image

which will provide a zip file e.g. exportCust_2019-02-06_09_57_58.zip

The zip file contains multiple .csv file which export the configuration of BICC:

image

However, none of these backup files will contain the assocation between a PVO and a out-of-the-box (seeded) OBIA offering like Human Resource, Sales or Service. These backup files would only contain a PVO which is associated with a newly (custom) created offering:

image

In practical terms this means, that in order to export/import the association of PVOs with offerings from one environment to another, a new offerings needs to be created and that all the PVOs that should be included must be manually added to this new offering.

Thus, there is not a possibility to deploy all customization conducted in BICC from one environment to another.

Other customization like deselecting attributes from PVOs (to not include them in the BICC extract) are part of the export/import functionality and thus can be deployed from one environment to another.

ODI Studio: working with multiple environments in BI Apps

With ODI, a security configuration file (jps-config-jse.xml) and a wallet file (cwallet.sso) is needed to establish the connection to the WORK or MASTER Repository. These files can be retrieved from the ODI Server machine and need to be copied to each client Machine e.g. the Laptop that should connect to the Repository. With Oracle BI Applications, there is typically a multi environment setup like DEV, TEST and PRODUCTION. Each of these environments comes with its own security configuration and wallet file. These files need to be placed in the client directory of ODI e.g.:

C:\Oracle\ODI_Studio\oracledi\client\odi\bin

There the patch to the security configuration files needs to be specified within the ODI Configuration file (odi.conf) with the following syntax:

AddVMOption -Doracle.security.jps.config=./jps-config-jse.xml

image

Since, each environment comes with its own security configuration file and wallet file you may need to replace these files to make the proper connection to the respective environment. Thus, the easiest approach is to create sub folders for each environment and copy and replace the files and needed:

image

The wallet file contains and encrypted code to make the database connection, while the security config file contains information on how to authenticate the User e.g. against a Weblogic component.

image

working with BICC to resolve extract issues

in order to extract data from Oracle Fusion Applications Cloud, BICC needs to be used. This is part of every Fusion Applications Cloud and is typically reachable by extending the URL with /biacm e.g.

https://pod-environment.fa.datacenter.oraclecloud.com/biacm/

When logging into BICC with above example URL, the following screen is shown as starting page:

image

This is indeed the Review Cloud Extract Configuration page of the top right navigation menu and allows to click on a PVO (Data Store) to further check the configuration of the PVO in terms of associated offerings (OBIA modules) and to be selected attributes (columns) of the PVO (Public View Object).

When starting with a OBIA implementation project, upon the first initial login, the required OBIA modules need to be enabled with the Global Data Store List from the navigation menu:

image

The offerings (OBIA module) can be selected from the drop down menu and the respective PVOs can be enabled for extract:

image

This will enable the PVO for use within BICC and the extraction process. In the background these PVOs are created, additionally they will also be associated with the respective OBIA Modules, hence for an OBIA implementation of Human resources, only the required PVOs for HR are extracted from Fusion Cloud.

However, the BICC extracts may fail due to errors which require to execute some tasks within BICC, to fix the Errors as workaround. These tasks include:

  • remove a PVO from an associated offering (OBIA module)
  • deselect columns for a PVO

The following section shows a step-by-step approach to fix these errors within BICC:

remove PVO from offering

A typical reason to remove a PVO from an offering may be due to the following error:

ORA-00942: table or view does not exist 

image

This error can be resolved by navigating to the start page of BICC or the Review Cloud Extract Configuration link from the navigation menu. The PVO name of the error log can be copied to the Data store field to search. To configure the PVO and the association with the offering the icon right to the name or the name itself can be clicked:

image

On the page of the PVO click edit:

image

On the following page, the associated offerings is listed in the (right) selected menu box. The associated offering can be removed from the PVO, to exclude it from the next extract run of the OBIA module (offering):

image

After removing the associated offering from the PVO, the customization can be saved and on the following screen of PVO summary, the PVO can be closed by clicking “Done” to get back to the Review PVO page.

The above method of customization, will permanently remove the PVO from the offering (OBIA Module). When scheduling a subsequent extract run, the removed PVO will not be part of the offering. This can be validated when creating a new extract (from Manage Extract Schedules) and selecting the offering from the drop-down menu:

image

The removed PVO will no longer be part of a extract run:

image

This Data Store List screen can also be used for temporary test of deselecting a PVO from a offering (OBIA module) extract. Typically, the OBIA module is selected from the offering drop-down menu and the enabled for Extract checkbox in the header of the table is clicked to enable all associated PVOs for extract, but an individual PVO can also be deselected for the list, to only affect this specific run (e.g. for temporary testing purposes). Thus, a PVO can be removed from a offering for all subsequent runs or just an specific run (using the Data Store List of the Manage schedule screen).

deselect columns from a PVO

Another common error are missing columns from a PVO e.g. because they are obsolete after an upgrade or not used anymore. The following error is show within the BICC logs:

FscmTopModelAM.PjtProjectPlanAM.ProjectPlanIntegrationPVO – Missing columns – [DeliverableTypesBPEOLastUpdateDate1, ProjPlanLinePEOLastUpdateDate2]

image

or:

ORA-00904: “COLLABORATIONENTRIESHISTPEO”.”COLLAB_ENTRY_VERSION”: invalid identifier

image

In the latter case, the physical column first needs to be mapped to a attribute (field) of the PVO in order to deselect the attribute from the select list. This is most reliably done by create an SR wth Oracle Support. After the attribute has been identified, the attribute can be deseleted from the PVO by going back to the Review Cloud Extract Configuration screen and searching for the respective PVO. Once the PVO name has been clicked (like in above section) the PVO summary page is shown. From this page the PVO can be edited by clicking “Edit”:

image

After clicking next to the select column page, the configuration of columns is shown for the PVO:

image

First, it is recommended to make the highlighted columns visibile to check if an attribute is used for the select list, as primary key or within the incremental filter. The attribute name can then be used to search for the attribute which needs to be deselected:

image

After deselecting the checkbox e.g. of Select List, the PVO needs to be saved. These changes will then be saved for the next extract run with BICC where the  deselected attribute will not be part of the select statement and thus not be retrieved. Since, this column hasn’t been available and caused the extract issue, the issue has been fixed by also deselecting the attribute from the PVO definition.

extracting data from Oracle Fusion cloud for Oracle BI Applications with BICC

the data extract from Oracle Fusion cloud is managed by the Fusion BI Cloud Connector (BICC). This console can be access by the following typical URL: https://customername.fa.datacenterregion.oraclecloud.com/biacm/

For the login a dedicated user is recommended e.g. BICC_ADMIN, since this user will be used to create the extract. According to the documentation, this user must be assigned with the following application roles in order  to inherit the required permissions for running the extract:

  • ORA_ASM_APPLICATION_IMPLEMENTATION_ADMIN_ABSTRACT
  • OBIA_EXTRACTTRANSFORMLOAD_RWD

This user is typically created by the Fusion team and given to the BI team. The BI team can validate the assigned roles by logging in to the following url:
https://customername.fa.datacenterregion.oraclecloud.com/cs/

and clicking on the user name in the top right corner. This will list all the assigned application roles to the user:

image

After validating the user, the Oracle BI Application modules need to be selected within BICC for extraction. This can be done by clicking on the calender icon of the top right menu and clicking on Global Data Store List.

image

This opens the page to enable the Oracle BI Application product offerings / modules within BICC for Fusion cloud extract. The offering must be selected from the drop down list and then be enabled for extract.

image

After all required modules have been selected, the modules can be extracted to UCM (this is the bundled file storage of any Fusion Cloud instance and may be used by multiple Fusion modules e.g. the data import to Fusion Cloud itself). The extract can be configured by clicking on Manage Extract Schedules on the top right menu.

image

This opens the schedule of extracts pane. A extract job can be created from this pane:

image

A new extract can be created by clicking the plus icon on the top pane. This will open a guided workflow, which allows to select the modules which should be extracted. E.g. in an initial project phase you may only choose to extract (run) a single module extract, while you have actived multiple modules. For example the project consists of Financials, Supply Chain, Procurement, but only the Procurement module is scheduled for extract.)

imageThen selecting immediate as recurrence setting the job will be run after saving it.

In case of errors with the extract, the log can be downloaded by clicking the help icon in the top right area of the screen:

image

and by clicking download latest log:

image

The log file may then provide further insight into any issues with the extract:

image

For further reference, please check: