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.
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:
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 Analytics Interactive Demo
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/:
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, 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.
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.
Accenture’s Digital Boardroom with Oracle Analytics Cloud
Oracle Analytics for Gartner 2019 BakeOff
The submission of Oracle for the Yearly contents held by Gartner, where multiple BI Vendors can analyze the same (public available) data-set using there own Analytics Software.
Short feature video (also available on Youtube):
Spending visibility with OCI for Oracle Cloud Budget
Oracle is monitoring & analyzing internet connectivity of service provider in every geography
Oracle’s Internet Intelligence team measures and monitors the internet, analyzing the connectivity of every network and service provider in every geographic market, enabling us to quickly identify outages, routing issues, and security incidents.
Oracle Analytics Day by Day Demo