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.


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:


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.


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:


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,
c_domain_member_map m,
c_domain dsrc,
c_domain dtrg,
c_domain_plv_rel plv
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.


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’:


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.