Use of Date data type in Oracle DV

DV allows to make use of individual elements of any Date or Datetime column. Thus, there is no need to calculate the Year portion of a Date as an additional column. For this to work correclty the data type must also be of Date or Datetime on the Database. When importing a text (csv) file to a Oracle Database, a Date may be defined as a text (varchar) field by default. In order to make use of this DV Feature, the data type should be changed to Date data type when importing e.g. with SQL Developer. Otherwise, the field containing the Date may just be displayed with a blue “A” like any other attribute, but not with the purple “clock” to identify a true Date field.



DV: Displaying data as % (Percentage) Of other column in graphs…

To display data as percentage of, can be defined within the Visualization properties for Tables and pivot Tables. However, the percentage of does not exist for all Visualitzations such as Bar Graphs and thus requires the calculation of these percentage values. Other Visuals like Pie Chart or Donut do support to display the data as Percentage Of.

Taking the following baseline example with Car Accidents by Body Type and if drinking has been involved:

image The percentage Of can be displayed for each column using the Value Tap of the Visualization properties:


This will result in the following view for the same table (as above), showing the percentage of values:


The above setting will only show the percentage of with respect to the same column (distribution within the same column, as black arrow below) and not as percentage of Body Type (Green Arrow below)!


The property section, does have the possibility to change the relation for displaying the percentage to other columns, e.g. Body Type, but this is not calculating correctly within DV (no further inverstigation on this Issue):


As mentioned at the beginning, for some Graphs like Bar Graphs, there is no option to display the data as percentage of values, since this is not the best visualisation to represent distributions and thus the Bar Graphs will only show the absolute values, like:


In order to display the correct Percentage Of in a table or in a graph, it is need to calculated as Percentage Of before hand. The formula for calculating the percentage is:

percentage value = SUM (occurences)  / COUNT (overall)


In order to calculate the correct sum for the occurences, the formula for decoding the attribute field into 1 and 0 has to be done on the data preparation step (on the prepare tab). Otherwise, the SUM will not calculate correctly when using the “My Calculations”, since it will not apply the calculation on each row, but only on the aggregated result as clearly shown below with the bottom red box:


This is because the Case When Formula is not evaluated row-by-row, but only applied after aggregation. Hence, the decoding of values for calculating the percentage must be placed within the data preparation step:


After this preparation, the formula can be defined as shown below (Please note the multiplication with 1.00 to convert to a decimal number to represent the percentage):


This gives the intended result for correct Percentage Of values within a Bar Chart and Table View:


Oracle’s Modern Business Experience Conference in Las Vegas, 2019

At the end of last Month the Oracle MBX Conference was held for two days in Las Vegas. The Keynote was held by SVP Steve Miranda. The Replay of the keynote is available at: MBX Conference

The starting theme of the keynote was the was presented by Charlene Li, who described the current wave of disruption by companies which are focusing on digital transformation. For companies it is not only important to manage the business of the current customers, but also need to know were future customer are going to be. This digital transformation does require a business strategy, but this strategy requires a solid culture of change and improvement within the company. Thus, a solid company culture will always be the prerequisite of any business strategy.

Digital Transformation also requires agility and speed of innovation and this may not be achieved by traditional large scale enterprise system, which are installed on-Premise like eBS, Peoplesoft or Siebel.

Agility and speed of innovation are the core principles of Cloud Applications, as explained by Steve Miranda who answered the Questions of: Why Cloud? And Why Oracle?.

While for the development of traditional on-Premise Applications Market Advisory Boards and Feedback circles were held with customers, the feedback was limited: since a customer may not always be on the current release e.g. a Year behind the current release and a feedback circle was only once a quartet. Thus, the feedback was eventually more anecdotal then factual. With a Cloud Application, all 17.000 customers are updated within a fixed timeframe to the same release (by Oracle) and while Oracle cannot access the data, the development team can assess the adoption and usage of product features. Thus, the feedback loop is much shorter and additionally there is not so much a incentive to focus on features which are easier to market, but features which are really used by the customer.

And Why use Oracle Cloud?

Even thought the customer may only work with the Application, the Application is built on top of a layer of Hardware, Operating System, Virtualization, Middleware and other technology stacks. Oracle owns all technology layers below the Application and can create a tightly integrated Cloud (technology stack). From a breath of perspective, Oracle is probably the only company to offer a HR, ERP and CRM Cloud Services. Also, Oracle offers innovative solutions at the top end of Chatbots and Digital Assistance; The current Cloud may be extend by integration with Amazon’s Alexa to allow Managers to make voice requests or enter into conversations for approve HR request within their approval flow of the HCM Cloud, also expense receipts can be photographed with the smartphone and the expense form will be pre-filled based on picture recognition as demonstrated on stage.




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.