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:
The following Article is written as of April 2018. Since, the Cloud is evolving rapidly, it may not apply in full, in future.
The aim of the Article is to demonstrate the possibilities of connecting to Oracle Fusion Applications (FA) Cloud (via OTBI) without any data extraction (ETL). Thus, only using declarative approaches offered by Visual Analzer (VA) of Oracle Business Intelligence Cloud Service (BICS) / Oracle Analytics Cloud (OAC) and the downloadable version of VA, the Data Visualization (DV) Desktop (DVD). The following diagram re-iterates the scope of this Article:
The following Version and respective features are used as baseline for this Article (as available of April 2018)
|Oracle Fusion Applications Cloud / OTBI||R13|
Oracle Fusion Applications (FA) Cloud comes bundled with OTBI, which provides pre-built BI Subject Areas to access the data stored in FA:
However, OTBI does have some limitations when it comes to extensibility (no access to metadata model) and adding of additional data sources (limited to data connection of respective FA instance).
The Oracle Cloud offering for “stand-alone” analytics are BICS (and in the future OAC). Hence, there maybe the need to connect from BICS/OAC to Fusion Applications. This is possible by two general Options:
- using data extraction / ETL from the Cloud
- declaring the connection (without any data movement)
- DV Desktop
- VA from BICS
- VA from OAC
This Article only focuses on the latter three options (declaring the connection details without any data movement).
Connecting from DV Desktop to Oracle Fusion Applications (FA) via OTBI
DV Desktop represents a offline Version of Visual Analyzer and can be downloaded from OTN. Once installed, it can be used to create a Connection to Oracle Fusion Applictions from the top right create Menu:
One of the Options from the “select connection type” list is “Oracle Application”:
This “Oracle Application” is the reference to Oracle Fusion Application’s Cloud via the OTBI URL !
It will not work with only the Oracle Fusion Application Cloud URL e.g. https://<IdentityDomain>.fs.em3.oraclecloud.com/homePage/faces/FuseWelcome , but only the respective OTBI URL e.g.: https://<IdentityDomain>.fa.em3.oraclecloud.com/analytics
If the URL is entered incorrectly, e.g. when using a trailing “/” it will not save the connection:
Once the correct OTBI URL, Username and Password are entered and saved, the connection is added to the list of available Data Connections:
As stated before: These “Oracle Applications” connections will only work with the respective OTBI URL, since the metadata (such as subject areas or Report from Catalog) is retrieved from this URL only. Once the connection is created, the Subject areas are available and the Analysis can be created in DVD:
Connecting from Visual Analyzer (VA) of BICS to Oracle Fusion Applications (FA) via OTBI
The equivalent of DVD for the web is Visual Analyzer (VA), which is part of OBIEE 12c as-well as BICS (and OAC). The same connection to Fusion Applications Cloud can also be created within VA of BICS. To do this, the VA Projects needs to be selected from the BICS (Classic) Home Page:
Within VA, click on “Add Connection” under the “Add new Data Source”:
And choose “Oracle Application” as connection type once again:
For the web version, the list of available data connections is also much smaller (only Oracle Applications and Oracle Database) compared to DVD. The connection details need to be entered in a similar way for VA e.g. as shown below:
After the Connection was saved successfully, the Connection can be used to create a Visual Analysis. However, VA of BICS will only provide the possibility to access saved Reports, but not the Subject area’s itself (compared to DVD)!
Connecting from Visual Analyzer (VA) of OAC to Oracle Fusion Applications (FA) via OTBI
The steps for connecting from VA of OAC to Oracle Fusion Applications (FA) are very similar to VA of BICS. Select VA from the OAC Homepage:
And create the connection in the same way. After successfully creation of the connection, it is listed as Connection in the Data list:
VA of OAC is not limited to create Analysis on predefined Reports (compared with VA of BICS). It is also possible to create an Analysis based on the subject area (similar to DVD):
The screenshot shows, the data preview from the Subject areas:
Important for Reports and Dashboards
(as of April 2018) The possibility of connecting from BICS/OAC to Oracle Fusion Applications (FA) Cloud is only available with VA. This is not (yet) available for Reports and Dashboards:
For Reports and Dashboards only a Database Connection can be added, but not a Oracle Applications connection.
- Oracle Oracle Applications connection Type refers to the OTBI URL of Oracle FA
- The possibility of connecting from BICS/OAC to Oracle FA is limited to VA
- VA can be used to connect to OTBI, but cannot be used to connect to the underlying database
- VA (via DVCS) can be used to connect to OBIEE .